List:General Discussion« Previous MessageNext Message »
From:Steve Meyers Date:January 13 2011 8:51pm
Subject:Re: I/O read performance
View as plain text  
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
I/O read performanceSteve Staples13 Jan
  • Re: I/O read performanceJohnny Withers13 Jan
  • Re: I/O read performanceMichael Dykman13 Jan
    • Re: I/O read performanceSteve Staples13 Jan
      • Re: I/O read performanceSteve Meyers13 Jan
        • Re: I/O read performanceSteve Staples13 Jan
          • Re: I/O read performanceSteve Meyers13 Jan
          • Re: I/O read performanceReindl Harald13 Jan
            • Re: I/O read performanceSteve Meyers14 Jan
re: I/O read performancePeter Brawley13 Jan