List:General Discussion« Previous MessageNext Message »
From:Steve Meyers Date:January 13 2011 9:37pm
Subject:Re: I/O read performance
View as plain text  
On 1/13/11 2:13 PM, Steve Staples wrote:
> On Thu, 2011-01-13 at 13:51 -0700, Steve Meyers wrote:
>> 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
>
> the only writes that happen, is when a customer has their "status"
> changed, password updates, or they change their name, OR when a new
> customer is created.   I would say a new customer get created (insert
> into) about 30 times per day, and a customer gets updated (update)
> prolly about 20 times per day, and customers getting deleted or
> suspended about 20 (there are rough guesses), and then customer searches
> are about 200-300 times per day, and then the other part that does all
> the searching, prolly about 100-200 per minute (or so).  I am really
> guessing here, but i am prolly  pretty close.
>
> the query that does the most, is:
> mysql>  EXPLAIN  SELECT customer_id FROM customer_account WHERE
> `full_login`='<email address>' ORDER BY `status` DESC LIMIT 1 \G
> *************************** 1. row ***************************
>             id: 1
>    select_type: SIMPLE
>          table: customer_account
>           type: ref
> possible_keys: NewIndex5,NewIndex4
>            key: NewIndex5
>        key_len: 202
>            ref: const
>           rows: 2
>          Extra: Using where
> 1 row in set (0.00 sec)
>
> NewIndex5 is 'full_login', 'status'
> NewIndex4 is 'full_login' (fulltext)
>
> maybe my indexes are out of line here...   maybe i was just doing this
> in haste, since the indexes are both kinda the same.
>
> I guess maybe it is just the sheer amount of queries per second/minute
> that is the issue?

That's not very many queries, honestly.  I'm not sure why you have a 
fulltext index on there, it's probably not really helping.

 From what you've told us, there really shouldn't be a problem.  I'd try 
using mk-query-digest to find out if there are queries running that you 
don't realize.

Steve Meyers
Thread
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