I am using MySQL 5.1 on a Windows Server 2008 R2 Machine with 8GB RAM.
I am updating 2 databases weekly. Both databases consist of MyISAM tables, and I run a script which performs the necessary INSERTs (No UPDATEs/DELETEs) into some tables (The table gp below has over 370,000,000 rows).
At some point during the mysql script execution (the whole process takes about 2.5 hours), I can see that the SELECT statements that are run concurrently with the INSERTs (BUT on different tables, and even in different databases) are "blocked" and just hang until the WHOLE inserting process finishes.
One insert will insert about 50-60 pairs of integers into the gp table and this can take up to 2-4 seconds (The whole process would have about 2000-4000 of each of these). So, if during the INSERTs, there are 10 SELECTs, all these get queued up as seen in the SHOW PROCESSLIST below.
Id User Host db Command Time State Info 35 root localhost:36954 db Query 1 update INSERT INTO db_2.gp VALUES(@g,669313116),(@g,...),... (@g is an integer variable and there would normally be about 50-60 pairs of values here) 42 root localhost:38019 db Query 113 Sending data SELECT * FROM db.g WHERE ... This SHOW PROCESSLIST has been run on a local script test run, but the same behaviour can be noticed on the LIVE server when it is being updated. I do not know why the SELECTs get blocked (above there is only 1 SELECT as it originated from localhost, but on the LIVE server, there may be many different SELECTs queued up from user queries).
In the above process list, it can be seen that the INSERT is being performed on the gp table in the database db_2, while the SELECT statement is being run against the g table in the database db. So, they are different tables (and in different databases), so I do not see why there is a locking issue here.
Is there any way I can make the INSERT not block the SELECTs? Please do not tell me to use InnoDB instead, as I have tried that and the site slowed down considerably. I need to keep using MyISAM.
