InnoDB’s tablespace can grow very large in a writeheavy environment. If transactions stay open for a long time (even if they’re not doing any work) and they’re using the default REPEATABLE READ transaction isolation level, InnoDB won’t be able to remove old row versions, because the uncommitted transactions will still need to be able to see them. InnoDB stores the old versions in the tablespace, so the it continues to grow as more data is updated. Sometimes the problem isn’t uncommitted transactions, but just the workload: the purge process is only a single thread, and it might not be able to keep up with the number of old row versions that need to be purged.
In either case, the output of SHOW INNODB STATUS can help you pinpoint the problem.Look at the first and second lines of the TRANSACTIONS section, which show the current transaction number and the point to which the purge has completed. If the difference is large, you may 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, 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 80157601 – 80154573 = 3028 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 may be many transactions that haven’t changed any data (conversely, a single transaction could have changed many rows).
If you have a lot of unpurged transactions 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 may 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 transaction 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, performance can decrease dramatically. --如果purge进程跟不上事务更新的速度,那么表和索引文件的大小就会因此增长的很快,而且存在多种版本,innodb在读取或者更新的时候就要进行各种判断,因而性能会急剧降低. Setting the innodb_max_purge_lag variable will slow down performance too, but it’s the lesser of the two evils.---相比而言,设置lag的值比不设置要好.
innodb的purge策略非产复杂,相比而言,在从库上,可以把事务隔离等级设置为read-uncommitted,绕开了innodb的mvcc,也就避免了purge的过程,从而提高性能.同时,对于复杂的事务,就需要读主库了.
