MySQL Replication: Insert generating VERY huge temporary tables

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 befo

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 !!!

StackExchange Database Administrators Q#3610, answer score: 4