Mysql – Queries are taking long time to execute

myisamMySQL

I have large DB around 25 GBs and the most busy table is around 9.5 GBs, database is increasing @ 10% per month, I am using mysql with MyISAM engine.

Problem started 3 days back, there is long list of queries in mysql status showing table level lock, it's taking long time to open the application. To keep the application running I set wait timeout = 8. Application is running but not executing long queries.

Kill    22  DELAYED localhost   eximstats   Delayed insert  3   Waiting for INSERT  ---
Kill    155 DELAYED localhost   eximstats   Delayed insert  58  Waiting for INSERT  ---
Kill    23123   office_eoffice  localhost   office_nw   Query   0   Sending data    
select data_type, fund from transaction where ID = '539329' AND productCode = 'RC11' AND folioNumber
Kill    23124   common  localhost   common  Sleep   0   --- ---
Kill    31879   office_eoffice  localhost   office_nw   Query   10  Sending data    
SELECT * FROM transaction WHERE transDate = (SELECT MIN(transDate) FROM transaction WHERE ID = '4473
Kill    32289   eximstats   localhost   eximstats   Sleep   3   --- ---
Kill    32635   office_eoffice  localhost   office_nw   Query   0   statistics  
SELECT * FROM clients_map WHERE map_from = '434301' AND arn_id = '396'
Kill    32643   common  localhost   common  Sleep   0   --- ---
Kill    32644   root    localhost   None    Sleep   0   --- ---
Kill    32645   root    localhost   mysql   Query   0   --- 
SHOW PROCESSLIST
Kill    32651   common  localhost   common  Query   0   statistics  
select nav, nav_date from nav_new where productCode='CB87' and nav_date ='2004-10-11' order by nav_

What is the permanent and long term solution?

Best Answer

Your PROCESSLIST

Look back the processlist in your post. I see you are using INSERT DELAYED. I have always felt it was invented as a band-aid to squeeze some write performance out of MyISAM. Note that the first line of the INSERT DELAYED Documentation says

The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that can be used for certain kinds of tables (such as MyISAM). When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

For those using MySQL 5.6, INSERT DELAYED IS DEPRECATED:

As of MySQL 5.6.6, INSERT DELAYED is deprecated, and will be removed in a future release. Use INSERT (without DELAYED) instead.

You should consider using INSERT instead of INSERT DELAYED. Why? From the Documentation:

INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it.

What is the permanent and long term solution?

You need to get away from MyISAM. Any kind of writing you do against a MyISAM table will always require a full table lock. Don't get me wrong. There are rare occasions when MyISAM is better than, and could even outperform, InnoDB :

At times, MyISAM could also have a slight edge in read performance over InnoDB. Why? Disk I/O wise, MyISAM has a slight edge with ROW_FORMAT=Fixed because you only interact with one file, the .MYD file. The row size is completely predictable because VARCHAR fields are treated as CHAR. This will reduce access time for data retrieval.

In the short term, just ditch INSERT DELAYED. It seems that your write load has increased in overhead beyond what MyISAM can handle.

After doing that, give it some time. If you still see perform issues, it may be time to switch all MyISAM tables to InnoDB (See my old post When to switch from MyISAM to InnoDB?)

Related Question