mysql innodb_file_per_table=1_ibdata1在innodb_file_per_table=1时增加的快，可能是因为undo[转]...

In this Document

APPLIES TO:

MySQL Server - Version 4.0 and later

Information in this document applies to any platform.

SYMPTOMS

In spite of using innodb_file_per_table the shared tablespace (ibdata1) keeps growing. It would be expected when not storing data in the shared tablespace that the shared tablespace would not continue to grow.

CAUSE

Other than encountering a bug (see the reference list for known bugs), the most common reason for the shared tablespace to grow in size when using innodb_file_per_table is a large UNDO log. Due to InnoDB's multiversion concurrency control (MVCC)it is necessary to keep an old copy of the data when a table is updated or rows are deleted. This allows existing transactions to keep seeing the original value which for example is necessary when using the REPEATABLE READ transaction isolation level. The UNDO log is always stored in the shared tablespace irrespectively of the value of innodb_file_per_table.

One symptom of the need for a large UNDO log is the output of SHOW ENGINE INNODB STATUS showing a large value for the history list length, transactions with many undo log entries, or long running transactions:

The output of SHOW ENGINE INNODB STATUS shows that some long running transactions are present:

------------

TRANSACTIONS

------------

Trx id counter 702

Purge done for trx's n:o < 0 undo n:o < 0

History list length 48760495

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 701, ACTIVE (PREPARED) 498 sec

mysql tables in use 1, locked 1

81154 lock struct(s), heap size 7240120, 1081153 row lock(s), undo log entries 1000000

MySQL thread id 14, OS thread handle 0x7f7b641c4700, query id 1120 localhost myuser query end

UPDATE t1 SET valc = REPEAT('A', 1002)

---TRANSACTION 6F8, ACTIVE 3152 sec

MySQL thread id 18, OS thread handle 0x7f7b53f55700, query id 1124 localhost myuser

SHOW ENGINE INNODB STATUS

Trx read view will not see trx with id >= 6F9, sees < 6F9

In this case TRANSACTION 701 has 1 million entries in the UNDO log and TRANSACTION 6F8 has been active for close to an hour (without doing anything). All of the changes made by any transaction (such as TRANSACTION 701) during those 3152 seconds will have the previous version of the data stored in the history list. The size of the history list can be seen in between the header of the TRANSACTIONS section and the actual list of transaction. In this case the History list length is more than 48 million pages long, all of which is stored in the shared tablespace.

Transactions that are active for a long time with an open read view is particularly likely in the following cases and something to watch out for:

When disabling autocommit, i.e. autocommit = OFF.

When making a backup with mysqldump using the single-transaction option for a large database.

Note: even if you are using innodb_file_per_table, tables that were created before setting innodb_file_per_table will remain in the shared tablespace until the table is rebuild.

SOLUTION

The solution to avoid the shared tablespace to grow is:

Make the transactions as small as possible.

Ensure all transactions are committed or rolled back as soon as they are completed, i.e. do not rely on the connection timing out to have a read view automatically rolled back.

REFERENCES

BUG:11766227- INNODB PURGE LAG MUCH WORSE FOR 5.5.8 VERSUS 5.1

https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table

NOTE:1326051.1- How to Check Whether innodb_log_file_size is Big Enough, InnoDB Redo Log

BUG:11764746- 57611: IBDATA FILE AND CONTINUOUS GROWING UNDO LOGS

BUG:13847885- PURGING STALLS WHEN PURGE_SYS->N_PAGES_HANDLED OVERFLOWS

https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction

https://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_repeatable-read

http://en.wikipedia.org/wiki/Multiversion_concurrency_control

https://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_autocommit

https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

NOTE:1341548.1- How to Reclaim Unused Space and Decrease InnoDB ibdata1 Data File Size

NOTE:1475296.1- MySQL: InnoDB History List Length and the Shared Tablespace (ibdata1) Keeps Growing Under Heavy Workloads, Before Version 5.5.8, 5.1.53 or 5.0.70