our replication slave is generating ~300GB temporary files while running relatively simple inserts.
Slave: 5.0.85-win32, also tried with 5.1.57-win64, same DB
binlog_cache_size = 10M
~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.