ibdata1 grows exponentially when innodb_file_per_table is configured

Question

I Have installed a MySQL Cluster with InnoDB (innodb_file_per_table enabled subsequently), but since I switched to innodb_file_per_table, the file ibdata1 grows (2GB at month).

Is My my.cnf file is correct?

Why my ibdata1 is so big (22GB)?

How I can look what there is in the ibdata1?

Server Configuration:

• Debian 6 amd64

• mysql-client-5.1 5.1.61-0+squeeze1

My InnoDB Configuration File:

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#

innodb_data_home_dir            = /var/lib/mysql
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_log_group_home_dir       = /var/lib/mysql
innodb_file_per_table
innodb_buffer_pool_size         = 5G
innodb_additional_mem_pool_size = 48M
innodb_log_files_in_group       = 3
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 0
innodb_lock_wait_timeout        = 50
innodb_thread_concurrency       = 15 
innodb_flush_method             = O_DIRECT

Procedure:

1) Dump All DB's
2) Stop MySQL
3) Add "innodb_file_per_table"
4) Delete all ib* file
5) Start MySQL
6) Import All DB's

InnoDB Conf:

mysql> show variables like 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 50331648               |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 25165824000            |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    | /var/lib/mysql         |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | ON                     |
| innodb_flush_log_at_trx_commit          | 0                      |
| innodb_flush_method                     | O_DIRECT               |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 8388608                |
| innodb_log_file_size                    | 536870912              |
| innodb_log_files_in_group               | 3                      |
| innodb_log_group_home_dir               | /var/lib/mysql         |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_method                     | nulls_equal            |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 15                     |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+

Answer 1

ibdata1 can still grow despite innodb_file_per_table being enabled. Why ?

Question: If InnoDB tables and associative indexes are written to individual tablespace files (file extension .ibd), what information still needs to be written to ibdata1 ?

Answer: Here are the following classes of information written to InnoDB's system tablespace:

• Data Dictionary
• Double Write Buffer
  • Safety Net to Prevent Data Corruption
  • Helps Bypass OS for Caching
• Insert Buffer (Streamlines Changes to Secondary Indexes)
• Rollback Segments
• Undo Logs
• Click Here to see a Pictorial Representation of ibdata1

I have discussed this before:

• Dec 09, 2011 : What is the best way to reduce the size of ibdata in mysql?
• Apr 01, 2012 : Is innodb_file_per_table advisable?
• Mar 25, 2012 : Why does InnoDB store all databases in one file?

EPILOGUE

Given enough transactions, rollback segments and undo logs to support REPEATABLE READs can make ibdata1 grow. INSERTs and UPDATEs to InnoDB tables with Secondary Indexes can pile up in the Insert Buffer. The Double Write Buffer Provides a second level of data redundancy in the event of a crash and subsequent crash recovery on mysqld startup. The only thing that can never grow is the Data Dictionary (unless you have DDL statements to create new InnoDB tables).

Answer 2

Check innodb status for long running transactions as they can inflate ibdata1 file. If you changed to innodb_file_per_table after running for some time without then existing tables will probably still be writing there. innodb_file_per_table will continue to write to the global table space unless you 1. dump and reload, 2. no-op alter. New tables created after the config change will have their own table space.

Answer 3

With mysql 5.7 you can consider moving your undo logs to separate files: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_undo_logs

This will also give you the option to truncate those logs when you like: https://dev.mysql.com/doc/refman/5.7/en/truncate-undo-tablespace.html

In my.cnf this setting should do the trick:

innodb_undo_tablespaces=2

Unfortunately, this setting appears to be available only on fresh mysql installs. You cannot just restart your server with this setting, you will get an error if you do. But, since you will also probably want to shrink your ibdata1 file, you will need a full dump anyways.