0

After having set innodb_file_per_table=1, dumping and dropping all db's, deleting ibdata1, and restarting mysql ... My ibdata1 file is small at last...

However, directly after reading in my dump, and seeing that all tables now all have their own big ibd files (approx 15GB), but still the ibdata1 is over 2.6 GB again...

The way I dump my data is with:

mysqldump --tab="D:\Mysql Databases\dumps\dump\%%a" --opt --single-transaction --no-autocommit -u root -p

This dumps to .sql (DDL / table structures) and .txt (actual data) files. These files I can use to recover with with:

create database myDb;
use myDb;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
SOURCE *.sql; -- for each table
COMMIT;
SET autocommit=0;
LOAD DATA INFILE '*.txt'; -- for each table
COMMIT;
SET autocommit=1;
SET foreign_key_checks=1;
SET unique_checks=1;

Why is the ibdata1 file growing again after enabling innodb_file_per_table and having succesfully shrunk ibdata1? And is there a way now to reclaim again this data, seen as all ibd data is already present per table?

Can I for example copy the small ibdata1 after recreating the database with the *.sql files, and placing back that ibdata1 file after reading in the data from the *.txt files?

Here are the non-default items in my.ini:

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=151
tmp_table_size=256M
thread_cache_size=20
myisam_max_sort_file_size=1G
myisam_sort_buffer_size=32M
key_buffer_size=64M
innodb_log_buffer_size=8M
innodb_buffer_pool_size=6G
innodb_log_file_size=1G
innodb_thread_concurrency=16
innodb_open_files=1000
join_buffer_size=2M
max_allowed_packet=16M
group_concat_max_len = 10M
bulk_insert_buffer_size=512M
collation-server = utf8_unicode_ci
1
  • How much RAM?.. – Rick James Apr 8 '16 at 2:52
0

Ibdata1 file holds a lot of information and allocates space for different innodb functionality (double write buffer, undo logs, tablespace header, etc.). Therefore even with innodb_file_per_table you cannot drop/replace the ibdata1 file.

One common reason why ibdata can grow is because it has the undo space. If you have long running transactions the previous version of rows can pile up in the undo log easily. More about this can be read on person's blog: https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

In MySQL 5.7 there are a lot of improvements around undo tablespace management including truncation and separate files to hold this.

8
  • So could it have to do with what I suspected... turning autocommit off during the reading of the dump? ... Is that what is causing the transactions to pile up ? ... – nl-x Apr 7 '16 at 14:50
  • And where can I read about how to truncate the tablespace (management) ? – nl-x Apr 7 '16 at 14:51
  • Innodb tablespace truncate (and other parameters here as well): dev.mysql.com/doc/refman/5.7/en/… – Károly Nagy Apr 7 '16 at 14:54
  • A post about the feature: mysqlserverteam.com/online-truncate-of-innodb-undo-tablespaces – Károly Nagy Apr 7 '16 at 14:54
  • mysqldump manages it's own transactions with --single-transaction as you specified in the command line. You can try without and see how big your ibdata1 file becomes. – Károly Nagy Apr 7 '16 at 14:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.