Monday, July 30, 2007

 

MySQL Lock Contention

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: ,


Comments:
Easy....IBM DB2
 
@anon DB2 = US $30,000 (DB2 Enterprise v8.1, 4CPU edition), MySQL=free. Not that easy!
 
Row level locking will probably be cheaper than replication.
 
@Clovis For row level locking I need to use InnoDB which uses around 30% more storage than MyISAM. Also replication will provide backup.
 
Won't replication by definition use double the space?
As for the backup argument, you should still be able to use replication with InnoDB as well can't you? My point is that any system that uses table level locking has a major bottleneck that will clog at some point.
 
This comment has been removed by a blog administrator.
 
Who knows where to download XRumer 5.0 Palladium?
Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!
 
This comment has been removed by a blog administrator.
 
I recently stumbled upon this little ****. Turned out that table locking is a big issue and we should definitely expect a profiling tool for MyISAM locks in MySQL 6.0??? Monty, what'd you say?

Check my war story on table locking:
http://devoluk.com/mysql-myisam-table-lock-issue.html
 
Post a Comment

Links to this post:

Create a Link



<< Home

This page is powered by Blogger. Isn't yours?