Mysql – INSERT in a remote server is far too slow, how to speed it up

insertMySQLremote

I need to load 480.000 rows into a remote database. The database can't be accessed locally, it's an Azure WebApp, so i can't load the SQL statements on the server.
I was wondering if there is a way to speed up things, as the process has been really slow, like 4500 rows in 25 minutes, which feels really like I'm doing something wrong.

I'm using

 mysql -uxxxx -pxxxx -hremoteapp-on-azure azure_db < .\wp_term_relationships.sql

to load and the sql file is

INSERT INTO `wp_43_term_taxonomy` VALUES ('2', '2', 'link_category', '', '0', '8');
INSERT INTO `wp_43_term_taxonomy` VALUES ('3', '3', 'post_tag', '', '0', '21');
INSERT INTO `wp_43_term_taxonomy` VALUES ('4', '4', 'post_tag', '', '0', '1423');
INSERT INTO `wp_43_term_taxonomy` VALUES ('5', '5', 'post_tag', '', '0', '21');
INSERT INTO `wp_43_term_taxonomy` VALUES ('6', '6', 'post_tag', '', '0', '106');
INSERT INTO `wp_43_term_taxonomy` VALUES ('16', '16', 'post_tag', '', '0', '41');
INSERT INTO `wp_43_term_taxonomy` VALUES ('18', '18', 'post_tag', '', '0', '154');
INSERT INTO `wp_43_term_taxonomy` VALUES ('20', '20', 'post_tag', '', '0', '279');
INSERT INTO `wp_43_term_taxonomy` VALUES ('21', '21', 'post_tag', '', '0', '41');
INSERT INTO `wp_43_term_taxonomy` VALUES ('25', '25', 'post_tag', '', '0', '429');

I've read about optimizing for INSERT, but in this case the query feel really slow. On my local machine the whole insert is taking 20 seconds, or less, not 20 minutes.

Should i somehow use multiple statements to avoid round trips?i could stream the file in PHP and submit it in 1000 INSERT chunks

EDIT – To be clear, I need to be sure i optimized everything I can optimize from my side as I have no control on the server

Best Answer

Build INSERT statements will up to 1000 rows each. Execute them in auto_commit=ON mode.

The former cuts down on round trips, plus other overhead in executing statements. The latter avoids sending BEGIN/COMMIT. Waiting for a million rows before committing, would slow things by building a huge "undo" log, then having to flush it.

Related Solutions

How to Get Status Messages During Large Re-Indexing Operation in MySQL 5.5

You need to monitor that progress outside of all mysql client programs.

For this example, let's say your MyISAM table is named workingset in the database mydata and the datadir is the default, /var/lib/mysql

Sidenote : If you are using Windows, run this SQL command to get your datadir

SHOW VARIABLES LIKE 'datadir';

You will have three files representing the table

/var/lib/mysql/mydata/workingset.frm
/var/lib/mysql/mydata/workingset.MYD
/var/lib/mysql/mydata/workingset.MYI

Try a temp table approach to loading the table workingset

use mydata
DROP TABLE IF EXIST workingsetload;
CREATE TABLE workingsetload LIKE workingset;
ALTER TABLE workingsetload DISABLE KEYS;
Perform 7 million row insert into workingsetload here, preferably LOAD DATA INFILE
ALTER TABLE workingsetload ENABLE KEYS;
ALTER TABLE workingset RENAME workingsetold;
ALTER TABLE workingsetload RENAME workingset;
DROP TABLE workingsetold;

During the load of the table workingsetload, there will be a temp table whose first five(5) characters are "#sql-". You can visually monitor in the Linux OS the progress of the loading of workingsetload like this:

cd /var/lib/mysql/mydata
watch -n 1 "ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}'"

You could script this in linux like this:

cd /var/lib/mysql/mydata
ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}' > /tmp/IndexRebuild.txt

You could then parse the columns accordingly and subtract the .MYD sizes to know when the data portion of the MyISAM table is complete. Subsequently, you could then parse the columns accordingly and subtract the .MYI sizes to know when the index portion of the MyISAM table is complete.

For Windows, you could script this using Perl (ActivePerl for Windows) or just have a DOS Batch loop over and over again.

Regardless of OS, once the "ls -l #sql-*.MY[ID]" or "if not exist #sql-*.MY*" comes back with nothing, the reload and indexing are complete.

Give it a Try !!!

UPDATE 2011-07-20 16:55 EDT

Monty has successfully implemented this feature in MariaDB. Check it out !!!

MySQL Load from Infile Stuck – Troubleshooting Guide

Your bulk insert buffer is 4G. That's great ... FOR MyISAM !!!

InnoDB does not use the bulk insert buffer.

You may need to have sqlalchemy throttle the load data infile calls into multiple transactions.

You may also want to disable innodb_change_buffering, setting it to inserts. Unfortunately, you cannot do SET GLOBAL innodb_change_buffering = 'inserts';. If you dom you may need to set it in my.cnf and restart mysql.

UPDATE 2012-07-13 16:53 EDT

I just noticed that you have two values in the my.cnf for innodb_buffer_pool_size. First one is 2385M, and the last one is 14G. If MySQL for Windows accepted 14G and you only have 12G of RAM, your server must be having a good old time swapping.

You can verify what the buffer pool size is with

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

UPDATE 2012-07-13 16:58 EDT

You may also want to check how full the buffer pool is with this

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;