Short Version:
INSERT INTO SELECT into a table with fulltext index with the same data takes sometimes 25 seconds and sometimes 2500 seconds. We have no idea where this huge gap is coming from.
Long Version:
I have a problem with a cronjob which imports new data into an import table and copies this data via an INSERT INTO SELECT statement to the production tables. I have split the tables because of the time an update takes with a mysql fulltext index – it seems to be faster to insert the data into this table with one INSERT INTO SELECT than with many single insert statements
The cron to import new data is running every 5 minutes. There is a function that checks if an instance of the cron is running to disallow parallel running of the script. Usually there are about 500 new records with every cron call. In the night at 1-2 am there are a lot of more new data (about 5.000 – 15.000 new records) and the cron is running much more longer than 5 minutes.
When the cron is running long in the night and while tracking the performance of these queries I detected that the performance of the INSERT INTO SELECT statement is very (!) slow. To copy about 15.000 new records (with a filesize of about 30 MB) the query takes more than 2.500 seconds!
The query is:
INSERT IGNORE INTO mentiondata
SELECT * FROM mentionimport
WHERE id <= 1203780;
I profile the query and with the following result:
2012-10-31 06:52:06 Queryprofile: {
"starting":"0.000036",
"checking permissions":"0.000003",
"Opening tables":"0.000132",
"System lock":"0.000003",
"Table lock":"0.000007",
"init":"0.000041",
"optimizing":"0.000007",
"statistics":"0.000023",
"preparing":"0.000005",
"executing":"0.000002",
"Sending data":"999.999999",
"end":"0.000017",
"query end":"0.000005",
"freeing items":"1.458159",
"logging slow query":"0.000050",
"cleaning up":"0.000007"}
In the process-list the sending-data was over 2.500 – in the profile it is just 999.999999. Maybe this is the profiler-limit – whatever…
The really strange thing is: When I try to reproduce the problem via deleting the records from the fulltext-table (DELETE FROM mentiondata WHERE id >= 1203780;) and starting the copy process manually it just takes about 25 seconds!!!
So I don’t get it and I really need help! I don’t understand why there is such a performance-difference between the same query! I checked the mysql-processlist while the cron-copy-statement was running – there are no other queries which lock tables or something. There’s just the single copy-query in the processlist – and a “sending data” with more than 2.500 seconds. There is no other cron or any other tasks which influence the performance of the server running. It seems that the mysql-server slows down every night or that the sql-query takes an extreme long time when the connection was opened a long time before the insert statement takes place (to insert the data into the copy import tables).
Are there any status-variables I can check why mysql is so slow? Is there a possibility to check why these queries are so slow? Here some server-variables for info:
bulk_insert_buffer_size: 268435456
key_buffer_size: 536870912
query_cache_size: 536870912
Thanks for any help!
Timo
