On 1/13/11 1:21 PM, Steve Staples wrote:
table type is MyISAM, it is a "customer_account" table, which holds the email address, and the customer_id field, the queries that are constantly being queried is "select customer_id from customer_account where customer_email = '<email address>' and `status`='1';
Do you have a key on customer_email? If not, create one ASAP. What kind of writes happen on this table?
With MyISAM, serial-like behavior can occur under certain circumstances. All queries must obtain either a read lock or a write lock. A table can have multiple read locks simultaneously, but a write lock must have exclusive access. Write locks have higher priority than read locks.
What this means in practice is that if a query requests a write lock, it is placed at the front of the queue. Any currently-running queries are allowed to finish, but any new lock requests are queued. If the currently-running queries take very long to complete (and "very long" could be fractions of a second, depending on the application), then lots of queries will get queued up behind the write lock that is pending. All new read requests would need to wait for the write lock queue to clear out before they can get back to reading from the table again.
Usually, this results in inconsistent performance under high load. One solution is to switch to InnoDB, since it does row-level locking. This is not a perfect solution for every situation, and you should do some research before doing this. It's probably the best solution in the long term for you, but I can't guarantee that.
Another possibility would be to queue writes in a separate table (or memcache, or something like that). Then do the writes in batches.
Steve Meyers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
