MySQL data insert test

Recently I needed to duplicate huge portion of data (about 8 GB) on MySQL for testing purpose. So I was wondering how to do it in the fastest possible way using mysqldump utility.

In mysqldump there are two ways how to export data:

• normal inserts = one INSERT command contains only one record
• extended inserts = one INSERT command combine many records (default mysqldump behaviour)

I tested to import 2M records (cca 800 MB) and here is the result:

Normal INSERTs

• Export
  • /usr/local/mysql/bin/mysqldump --extended-insert=FALSE --user=user --password=password -P3906 -hserver db table | gzip -9 -c > dump_table_no-extended.gz
• Import
  • zcat dump_table_no-extended.gz | /usr/local/mysql/bin/mysql -h server --user=user --password=password -P3906 db
• Result
  • 19 min

Extended INSERTs

• Export
  • /usr/local/mysql/bin/mysqldump --extended-insert --user=user --password=password -P3906 -hserver db table | gzip -9 -c > dump_table_extended.gz
• Import
  • zcat dump_table_extended.gz | /usr/local/mysql/bin/mysql -h server --user=user --password=password -P3906 db
• Result
  • 1:26 min

Disable keys

Further, I tested if there is any improvement when I disable keys during import as MySQL rebuilds index tree every time it inserts a new record. 

Extended INSERTs + DISABLE KEYS

• Export
  • /usr/local/mysql/bin/mysqldump --extended-insert --no-data --user=user --password=password -P3906 -hserver db table | gzip -9 -c > dump_table_extended-structure.gz
  • /usr/local/mysql/bin/mysqldump --extended-insert --no-create-info --user=user --password=password -P3906 -hserver db table | gzip -9 -c > dump_table_extended-data.gz
• Import
  • zcat dump_table_extended-structure.gz | /usr/local/mysql/bin/mysql -h server --user=user --password=password -P3906 db
  • ALTER TABLE table DISABLE KEYS;
  • zcat dump_table_extended-data.gz | /usr/local/mysql/bin/mysql -h server --user=user --password=password -P3906 db
  • ALTER TABLE table ENABLE KEYS;
• Result
  • 1:20 min

Note

By default, mysqldump generates only one INSERT command per table, resulting in one (very long) line of inserted data for each table that got dumped. So I wondered if it is OK to import huge data set using extended INSERTs due to possible memory issue. Fortunately, mysqldump has a limit for one INSERT set by option net_buffer_lenght which is 16384 bytes. This will ensure that big table is actually dumped into many extended INSERT commands.

Conclusion

The best and simplest way is to use default mysqldump settings, e.g. with extended inserts.

Further, I found that its better to uncompress file, open mysql console and use "source dump_table.sql". It's faster than using "zcat dump_table.gz | mysql .....".