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
