Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

1 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;