2

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's MyISAM and apparently that number is unreliable (but it's a big table, anyway). The database is backed up daily using mysqldump.

Currently, restoring the database from this dump file takes ~10 hours, and is only going to get worse. I'm using the --skip-extended-insert option because restoration will fail with a single multi-valued insert statement for all rows in the table. My understanding is that max_allowed_packet can be extended up to 1GB, but that's still not enough.

I thought that this would be a common enough problem and might have a generally accepted solution but I'm having trouble finding anything. So, my questions are:

  • Does mysqldump have an option that allows multi-valued inserts to be batched for each table e.g. instead of creating a single insert, create multiple inserts for n rows
  • If not, is there some other utility/alternative to accomplish this, or do I have to roll my own backup script that queries for table rows in batches of n using the limit clause and manually create the insert statements myself?

MySQL v5.5.40
Tables are a mixture of MyISAM and InnoDB. The backup takes place locally.

1

Your issue may involve the configuration settings for either OS or MySQL

HISTORICAL EXAMPLES

Years ago when I worked at a website firm, I used --skip-extended-insert to load a DB Server that had 2GB RAM. That made a whole world of sense to me because the server I dumped it from had 64GB RAM and the client wanted the data on a smaller machine.

At my current placement of employment, an outside vendor dumped data from SQL Server and produced a mysqldump compatible dumpfile. The problem with that dump file was each table had a single insert with all the rows. One table had 3.5 million. They could not be loaded into in-house VM will less than 8GB RAM configured. Shockingly, the solution to that was to scp the file to may laptop, load it into MySQL 5.5.37 on my laptop, mysqldump from it, send the new mysqldump back to the Linux CentOS VM, and load the mysqldump.

BTW, the 3.5 million row table loaded as a single insert into my Windows 7 laptop in 4.5 hours without changing any MySQL settings. To be honest with you, I did believe that "shot in the dark" would work because I did not reconfigure mysqldump on the laptop to work with the dump. My guess is that it probably loaded because the undo log in ibdata1 just grew on disk and held everything.

You should not have to live to skip-extended-insert if you dump on reload on the same machine.

SUGGESTIONS

While I was typing my answer, I saw you inject the following into the question

According to the a query I ran, the table clocks in at around 1.7GB, but it's MyISAM and apparently that number is unreliable (but it's a big table, anyway).

SUGGESTION #1

If that one MyISAM table has 1.7GB and has the 11 million rows, my suggestion would be for you to increase the size of the bulk insert buffer. The bulk insert buffer is for MyISAM usage only and supports extended inserts. Set bulk_insert_buffer_size to 256M.

Go to my.cnf or my.ini and add this

bulk_insert_buffer_size = 256M

Then, login to MySQL and run

mysql> SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

SUGGESTION #2

As for your InnoDB, you need to use a bigger log buffer. Set it to 64M by adding this to my.cnf

innodb_log_buffer_size = 64M

You must restart MySQL to use that new value.

SUGGESTION #3

You may also need bigger InnoDB Logs by increasing innodb_log_file_size. This require manual intervention as follows:

STEP 01: Run this command as root

mysql> SET GLOBAL index_fast_shutdown = 0;

STEP 02: Shutdown mysql

  • Linux : service mysql stop
  • Windows (as Administrator) : net stop mysql

STEP 03: Add this option to my.cnf or my.ini

innodb_log_file_size = 1G

STEP 04: In the OS, goto the folder where ib_logfile0 and ib_logfile1 are located and run

Linux

mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

Windows

rename ib_logfile0 ib_logfile0.bak
rename ib_logfile1 ib_logfile1.bak

STEP 05: Start mysql (the log files get recreated so start will take an extra 1-2 min)

  • Linux : service mysql start
  • Windows (as Administrator) : net start mysql

After making one or more of these suggested changes, try dumping and reloading. It goes go much smoother. Start with just SUGGESTION #1 and try it. Try the other other two to see if it improves more.

SUMMARY

You need these options for your target MySQL DB Server in your my.cnf or my.ini

bulk_insert_buffer_size = 256M
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
max_allowed_packet = 1G

GIVE IT A TRY !!!

  • Thanks for the detailed answer. Is this solution scalable? My tables will continue to grow at the rate that, in another year, my large table could 50% larger. The size limit that MySQL imposes on single inserts confuses me. Is it a hard upper limit of 1GB with no exceptions? And what does that limit relate to in terms of the insert statement (e.g. the size of the large table dump file as produced by mysqldump?) – RTF Nov 16 '14 at 15:27
  • My answer is not really an issue of scaling based on the large tables. This is more of a join maximizing of mysqld and hardware. Your issue then becomes whether all the table data is part of your working set and if any of that data can be archived away. It then just becomes a matter of doing mysqldump on real data and ignoring archive data. If all the rows of that large MyISAM are accessed frequently, you should look into partitioning that MyISAM table (dba.stackexchange.com/questions/6607/…) – RolandoMySQLDBA Nov 16 '14 at 15:36
  • Another suggestion would be to ignore dumping that one MyISAM table. Remember that a MyISAM table can be moved to another machine by just copying the .frm, .MYD, and .MYI files. If moving the three files to a Linux machine, remember to run chown mysql:mysql on those three files before acccesing it. BTW you can move those three MyISAM files from Linux to Linux, Linux to Windows, and Windows and Linux. – RolandoMySQLDBA Nov 16 '14 at 15:38
  • That's an interesting suggestion about just copying the raw files. I actually had to do something similar recently to restore a database, but it's my understanding that InnoDB is the one that allows simply copying the .frm, .MYD and .MYI files. I thought MyISAM requires the ibdata file too? – RTF Nov 16 '14 at 15:46
  • 1
    Please read the following tag info : InnoDB (dba.stackexchange.com/tags/innodb/info) and MyISAM (dba.stackexchange.com/tags/myisam/info) – RolandoMySQLDBA Nov 16 '14 at 15:52
1

Does mysqldump have an option that allows multi-valued inserts to be batched for each table?

Yes. net_buffer_length - The initial size of the buffer for client/server communication.

  • net_buffer_length (dev.mysql.com/doc/refman/5.6/en/…) is only for TCP/IP communication. It does not explicitly control the number of rows for doing extended inserts. It may increase the number of rows but you cannot make it a fixed number of rows. Setting it for a mysqldump can possibly make the mysqldump more difficult (or at least sluggish) to reload in another environment as I stated in my answer. – RolandoMySQLDBA Jan 24 '15 at 22:15
  • Please note in my answer that I expanded the max_allowed_packet to 1G. The MySQL Packet will expand from the initial setting of net_buffer_length bytes in size to max_allowed_packet, making the mysqldump reload more tolerable. – RolandoMySQLDBA Jan 24 '15 at 22:17
  • You still get a +1 because as long as the mysqldump is reloaded into a similar environment or back to the same DB Server it came from, changing the net_buffer_length would not be as risky. – RolandoMySQLDBA Jan 24 '15 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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