The impact of long-running transactions on Linkbench QPS: InnoDB vs RocksDB

I have been using Linkbench and LinkbenchX to compare performance for MySQL and MongoDB. Previous results were shared for a cached database and a small server. Here I describe the impact of a long-running transaction on QPS for InnoDB and RocksDB storage engines in MySQL. The summary is that a long-running transaction caused a big drop in QPS for InnoDB and RocksDB.

The long-running transaction was created by executing BEGIN and then a short running SELECT statement in one session. After the SELECT statement completed the session remained open while a 1-hour linkbench test was run. If you take logical backups from an active database server, then you care about the performance impact from a long-running transaction.

The InnoDB result surprised me as I didn't expect the drop to be that large. The test used repeatable read for InnoDB and a long-running transaction in this case means there is a long-open snapshot that blocks purge and the history list length grows during this test which also means that the disk space for undo grows.

RocksDB also uses a snapshot per transaction so a long-running transaction should imply a long-open snapshot. Read the section on Gets, Iterators and Snapshots to understand the difference between snapshots and iterators in RocksDB. Both of them prevent some old data from being removed from the database, kind of like InnoDB purge. But RocksDB is more clever than InnoDB about dropping old data so we don't have a pending feature request like 74919. But a long-running transaction still means that queries encounter more tombstones which means more overhead.

Show me the graph

This shows linkbench QPS from 3 1-hour runs for MySQL+InnoDB and MySQL+RocksDB. The first and third 1-hour runs are done without a long-running transaction. The middle 1-hour run is done with a long-running transaction and QPS for InnoDB drops quickly and for RocksDB drops less quickly. Fortunately it recovers as soon as the long-running transaction is closed. The tests used the cached database configuration (maxid1=20M) as described in a previous post and were done after ~12 1-hour linkbench query tests had already been completed.

There are 8 types of statements used by linkbench. By far the most frequent is GET_LINKS_LIST which is handled by doing a short range scan on a secondary index for the MySQL implementation. That is also the statement type most impacted by the long-running transaction as the average response time increased by ~1.5X for RocksDB and ~7X for InnoDB.