0

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.

3

The INSERT isn't "blocking" the SELECT, in the example process list you gave. What's almost certainly happening is that the INSERT is taking a whole pile of I/O, starving the SELECT of the disk accesses it needs to finish sending the data to the client.

  • Hi, thanks for your answer...if that is the case, is there a way I can prevent this from happening? – user3480610 Aug 5 '15 at 7:11
  • @user3480610 Put the databases on seperate disks might help. – Reaces Aug 5 '15 at 7:42
  • @user3480610 Putting some sleep calls in your import procedure/script may help as well; give the server literally some pause to write its data. – JvO Aug 9 '15 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.