way to prevent queries from waiting for table level lock

Question

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in MyISAM. (I've heard of using InnoDB instead of MyISAM, but we cannot change the engine in the near future).
We could spot it to an update-query which is performed when a moderator activates a comment on the articlesite. This is the process:

• update-query is processed SET status = 1 WHERE id = 5 (index is set)
• the cached files of the page are deleted

At this point the whole page becomes slow. The database itself is busy for minutes. I fetched the processlist a few times and saw about 60 entries of different select-queries, which were all on the state waiting for table level lock.

1. I don't unterstand why this update on the table article_comments can affect select-statements for table article to wait for table level lock. In processlist almost all waiting queries were from this table. I've read about the fact that updates/inserts are preferred to selects and that this can cause such problems, but the articles-table itself isn't updated when comments become activated, so the selects shouldn't wait. Did I missunterstand that?
2. Is there something besides changing to InnoDB to prevent this behaviour or at least to get a better balance? I'm very irritated about the fact that this problem did not appear before moving the database to the new server. I guess there is some misconfiguration but I don't know how to identify.

Answer 1

The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.

I wrote about pros and cons of using MyISAM vs InnoDB

• InnoDB vs MyISAM with many indexes (Jul 05, 2012)
• Which is faster, InnoDB or MyISAM? (May 03, 2012)
• Any gotchas at all with converting from MyISAM to InnoDB? (Jan 09, 2012)
• Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks? (Oct 07, 2011)
• Benefits of table level locking (Sep 14, 2011)
• What are the main differences between InnoDB and MyISAM? (Apr 14, 2011)

With regard to your current question, here is a possible scenario:

• article and article_comments are both MyISAM tables
• article_comments has one or more indexes with status as a column
• Index page updates for article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
• You have SELECT queries that perform JOINs between article and article_comments

In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)

Answer 2

At this point the whole page becomes slow. The database itself is busy for minutes.

Smells like you have a big Query_cache?

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 | -- Not over 50M
| query_cache_type             | DEMAND   | -- Only if using SQL_CACHE
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

For production systems with lots of writes, you may as well turn OFF the query_cache.

All entries in the query_cache for the given table are purged when any write occurs to that table. The bigger the QC, the slower this task is.

MyISAM uses "table level" locks. Reads and writes cannot occur at the same time (on the same table). Crude, but effective.