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 |
+-----------------------------------------+------------------------+
Best Answer
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 toibdata1
?Answer: Here are the following classes of information written to InnoDB's system tablespace:
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).