MySQL Replication: Insert generating VERY huge temporary tables

Question

our replication slave is generating ~300GB temporary files while running relatively simple inserts.

Our setup:
Master: 5.0.45-0.sles10.x86_64,

Slave: 5.0.85-win32, also tried with 5.1.57-win64, same DB

Slave configuration:

max_connections=100
query_cache_size=1M
table_cache=2048
tmp_table_size=32M
thread_cache_size=8
binlog_cache_size = 10M
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=69M
key_buffer_size=55M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=8M
innodb_additional_mem_pool_size=16M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=16M
innodb_buffer_pool_size=512M
innodb_thread_concurrency=10
datadir=D:\MySQLrepl\
innodb_data_home_dir=D:\MySQLrepl\
innodb_data_file_path=ibdata1:33266M;ibdata2:31484M;ibdata3:52988M;ibdata4:123492M;ibdata5:100M:autoextend
innodb_log_group_home_dir=D:\MySQLrepl\
innodb_log_files_in_group=2
innodb_log_file_size=128M
log-bin=mysql-bin

Database information:
~360 GB total data size
~50 GB data size for affected database, rest in different database
Storage engine is innoDB for all tables
Database was transfered to slave with MySQL Enterprise Backup 3.5.2
sernumbers_results contains around 317 million rows, size is around 17,7 GB, rowid is the primary key.

One of the queries generating the problem:

INSERT INTO sernumbers_results_2009 SELECT * FROM sernumbers_results WHERE rowid>(SELECT rowid FROM sernumbers_results_2009 order by rowid desc limit 1) ORDER BY rowid LIMIT 10000

What this should do, according to my colleagues logic, is to copy results from 2009 to a separate table bit by bit. He has a good excuse for this, he is french ;)
He also says that running the insert queries was fast on the server and indeed: the SELECT-part from that query runs in 0.18s without any problems, so it seems that there is a problem with the insert bit. It seems that all databases are converted to temporary MyISAM-tables before the query is applied. It also seems that there is little to none back-conversion because the server is starting with the next temporary table almost immediately when the one before has reached it's maximum size.

I'm really totally clueless here, so any help or suggestion is really appreciated.

Answer 1

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!