Mostly Harmless

Lock contention can be a serious performance issue with MySQL if both read and write happen concurrently on the same table. BlogScope has tables with around 100 million rows, stored in the MyISAM storage engine. Usual workload consists of 2-3 updates (mainly INSERT) per second and several SELECT operations. Since MyISAM provides only table-level locking, every operation locks the complete table. Although SELECT operations can take place in parallel, it turns out that a long running SELECT can block the complete application. After investigating a bit in why a SELECT was blocking other SELECT operations, I found an explanation in MySQL docs.

A client issues a SELECT that takes a long time to run. Another client then issues an UPDATE on the same table. This client waits until the SELECT is finished.Another client issues another SELECT statement on the same table. Because UPDATE has higher priority than SELECT, this SELECT waits for the UPDATE to finish, and for the first SELECT to finish.

This basically means that if there is any long running SELECT query (e.g., sequential scan), the whole application will just PAUSE for hours. This also means that you can not take backups (using mysqldump), as not only the process willing to write, but even those just reading will be blocked. Fortunately, a fix is available: set the priority of SELECT higher than UPDATE. In this case, the second SELECT statement in the preceding scenario would execute before the UPDATE statement, and would not need to wait for the first SELECT to finish. To set low priority for UPDATES, add the following to /etc/my.cnf

low_priority_updates=1

and execute the following

set global LOW_PRIORITY_UPDATES=1;

While setting updates to be of low priority fixes the locking issue, I don't really like it. In this case, the UPDATE may never actually get a chance to execute if the load is too high. In my opinion, the best solution is to have a replica server. A master MySQL server where all the updates take place, and a slave for all the reads. Once we install our new hardware, I will setup a replica server for BlogScope as well.

Labels: mysql, performance

# posted by nileshbansal @ 3:21 am - -   -bookmark at -del.icio.us   -