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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
