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
nl-x
  • 111
  • 4

1 Answers1

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.

Károly Nagy
  • 3,020
  • 1
  • 13
  • 13
  • 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 07 '16 at 14:50
  • And where can I read about how to truncate the tablespace (management) ? – nl-x Apr 07 '16 at 14:51
  • Innodb tablespace truncate (and other parameters here as well): http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_undo_log_truncate – Károly Nagy Apr 07 '16 at 14:54
  • A post about the feature: http://mysqlserverteam.com/online-truncate-of-innodb-undo-tablespaces/ – Károly Nagy Apr 07 '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 07 '16 at 14:57
  • --single-transaction is only used during dumping and does not make the dump itself any different. So that can't be it. Because after dump, I delete everything including ibdata1, and then read the dump back in. – nl-x Apr 07 '16 at 15:00
  • I am now in the process of reading back in the dump without turning autocommit off. That makes reading the dump back in slower... but maybe now it won't rely that much on ibdata1 so much. – nl-x Apr 07 '16 at 15:01
  • --single-transaction makes the dump "consistent". That is, tables that are relate to each other will be logically dumped at exactly the same time. – Rick James Apr 08 '16 at 02:56