Mysql – Aggregating high volumes of data from many MySQL servers

As long as the traveling user will be adding, updating, and deleting records and can guarantee that no one else is touching in the master, there is something you can try.

What you want to do is setup MySQL Circular Replication (Master/Master) between Master and LapTop. Once you have this esatblished and the time has come for the traveling user (TVU) to leave for the week, here is what to do:

Step 1 : Run STOP SLAVE; on the Master

Step 2 : Run STOP SLAVE; on the LapTop

Step 3 : TVU inserts, updates, and deletes on the LapTop

Step 4 : Make sure TVU does not delete or update data that the Master will have worked on during the week.

Step 5 : When the TVU returns, run START SLAVE; on the Master to reconnect the Master to the LapTop. All inserts, updates, and deletes that occurred on the LapTop during the week will transmit over to the Master. Run SHOW SLAVE STATUS\G every couple of minutes on the Master and look for Seconds_Behind_Master going to 0. When it does, the data from the LapTop will be sync'd over to the Master.

Step 6 : (OPTIONAL) Run START SLAVE; on the LapTop to bring all the inserts, updates, and deletes the Master did during the week. Run SHOW SLAVE STATUS\G every couple of minutes on the LapTop and look for Seconds_Behind_Master going to 0. When it does, the data from the Master will be sync'd over to the LapTop.

The only real danger is if you delete or update data on the LapTop that still needs to be on the Master. If you forget that, step 5 will make that data change or disappear unintentionally. As long as you work with a new set of data on the LapTop only, there should be no problem.

You should test this between a DevServer and a LapTop you are using for Development only.

UPDATE 2011-07-24 21:42 EDT

Here is another thing you can try:

Step 1 : Setup mysql on the LapTop with no initial data on the laptop and no binary logging

Step 2 : mysqldump the data out of the Master and into the LapTop

Step 3 : Add this to my.ini on the LapTop

[mysqld]
log-bin=mysql-bin

Step 4 : Activate binary logging without a mysql restart on the LapTop

SET GLOBAL SQL_LOG_BIN = 0;

or just restart mysql

Step 5 : Perform inserts, updates, and deletes on the LapTop (Make sure they do not conflict with needed data on the Master)

Step 6 : run this

mysqlbinlog mysql-bin.0* > ThisWeeksChanges.sql

Step 7 : Run this on the Master

mysql> source ThisWeeksChanges.sql

Probably you have an ordinary "cold cache" case.

When you INSERT 10,000 rows into a table, the rows need to be added to the appropriate place(s) in the "data". Also, 10,000 entries need to be added to each index's BTree. (How many indexes do you have? Please provide SHOW CREATE TABLE.)

If you have AUTO_INCREMENT, then the rows will be "appended" to the table; this is not a caching issue. If, on the other hand, you have a UUID index, or some other 'random' index, then the "inserts" into the index will be random -- involving read-modify-write. The read and the write are cached. But, if the system were "cold" when you did the first 10,000, a lot of reads were probably necessary. Multiply that by how many indexes you have.

How big is the table? If it is small enough to fit into innodb_buffer_pool_size (if InnoDB) or small enough for the indexes to fit into key_buffer_size (if MyISAM), then soon all the index blocks will be cached, and the inserts will speed up.

If the table is too big for the cache, then the 'random' indexes will continue to hit the disk (and be slow). AUTO_INCREMENT will continue to be fast.

5 seconds is about 500 reads from a commodity spinning drive. So, I would guess that you have a modest sized table. Since the next 10K rows go in <1sec (<100 disk hits), I will guess that it fits in cache so far.

I often recommend 100-1000 for chunk size. This is partially because 5 sec can be a problem. You may as well COMMIT after each chunk, since the benefit of delaying it is minor. Furthermore, if you wait "too long" to commit, the 'transaction' could overflow the log_file, leading to an inefficiency.

A chunk size of 100 is about 10% slower than the theoretical maximum. 1000 is about 1% slower than max.

If you have replication, keep in mind that the 5 seconds will interfere with anything else being replicated. (Another argument against 10K.)