Databases Reference
In-Depth Information
has completed. If the difference is large, you might have a lot of unpurged transactions.
Here's an example:
------------
TRANSACTIONS
------------
Trx id counter 0 80157601
Purge done for trx's n:o <0 80154573 undo n:o <0 0
The transaction identifier is a 64-bit number composed of two 32-bit numbers (it's a
hexadecimal number in newer versions of InnoDB), so you might have to do a little
math to compute the difference. In this case it's easy, because the high bits are just
zeros: there are 80,157,601 - 80,154,573 = 3,028 potentially unpurged transactions
(
innotop
can do this math for you). We said “potentially” because a large difference
doesn't necessarily mean there are a lot of unpurged rows. Only transactions that
change data will create old row versions, and there might be many transactions that
haven't changed any data (conversely, a single transaction could have changed many
rows).
If you have a large undo log and your tablespace is growing because of it, you can force
MySQL to slow down enough for InnoDB's purge thread to keep up. This might not
sound attractive, but there's no alternative. Otherwise, InnoDB will keep writing data
and filling up your disk until the disk runs out of space or the tablespace reaches the
limits you've defined.
To throttle the writes, set the
innodb_max_purge_lag
variable to a value other than
0
.
This value indicates the maximum number of transactions that can be waiting to be
purged before InnoDB starts to delay further queries that update data. You'll have to
know your workload to decide on a good value. As an example, if your average trans-
action affects 1 KB of rows and you can tolerate 100 MB of unpurged rows in your
tablespace, you could set the value to
100000
.
Bear in mind that unpurged row versions impact all queries, because they effectively
make your tables and indexes larger. If the purge thread simply can't keep up, perfor-
mance can decrease dramatically. Setting the
innodb_max_purge_lag
variable will slow
down performance too, but it's the lesser of the two evils.
10
In newer versions of MySQL, and even in older versions of Percona Server and MariaDB,
the purging process is significantly improved and separated from other internal house-
keeping tasks. You can even create multiple dedicated purge threads to do this back-
ground work more quickly. This is a better option than throttling the server, if you can
take advantage of it.
10. Note that the way this ought to be implemented is a topic of some debate; see MySQL bug 60776 for the
details.
