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.