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.

migrated from stackoverflow.com Jul 18 '12 at 15:02

This question came from our site for professional and enthusiast programmers.

  • 1
    Enable general logging and watch for JOIN statements between these tables. When you SELECT it creates an implicit READ LOCK. Since MYISAM doesn't support ROW LEVEL locking, it locks at the table level. It's probably the case that this locking was happening on the old server but no one was watching? Compare your my.cnf line for line between hosts and especially make sure your key_buffer is tuned properly. – randomx Jul 18 '12 at 17:19
  • We had several other performance problems on the old server, and often watched the processlist. There were mainly many sleeping processes, but we never noticed waiting ones (I saw this info generally the first time on this new server). My fellow copied the old my.cnf and adjusted the values to the new existing hardware, but there weren't many entries. I also compared the outputs of "SHOW VARIABLES" but didn't really know for what to look. We'll recheck the keybuffer tomorrow, thanks for your comment. – 32bitfloat Jul 18 '12 at 20:06
  • We recently had a similar problem. Initially our key_buffer_size was set to 1GB. Increasing that to 10GB reduced the problem. – Haluk Jul 26 '12 at 6:09
  • @Rick James, thank you. You saved me a lot of trouble today. Do you have a wish list in Amazon or somewhere else? :) I set up query_cache_limit to 1024. There is no lock problem now. I did it in variables at first from mysql client. set global query_cache_limit = 1024; Now I will write it to my.cnf. This solution got me the time to plan innodb migration without any stress so thank you. – user24323 May 29 '13 at 10:38

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

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)

  • Thanks for your answer (and the links), your scenario is real. I did not realize that the most of select article indeed join the comments-table (or in other words, saw the incomplete statements in processlist of phpmyadmin). Do you know a short term solution to prevent the multiple waiting queries? I already tried it with "UPDATE LOW_PRIORITY" in the specific statement but that didn't made noticeable changes. We truly will change to innodb in future but I wonder if there's a way to get an improvement currently. – 32bitfloat Jul 18 '12 at 20:20
  • Ultimate solution : Convert tables to InnoDB. See my post dba.stackexchange.com/a/9422/877 on how to convert everything MyISAM into InnoDB – RolandoMySQLDBA Jul 18 '12 at 20:31

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.

  • 1
    Well, yes. We have about 64M cachesize. Thanks for this information which was new to me, however, we had the same value on the old server where we did not notice the tablelockings. We've already started to move to InnoDB but this fact remains as a mystery... – 32bitfloat Jul 24 '12 at 7:57

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.