In the last episode (Apr 24), Ryan R. Tharp said:
> IO bound I believe, I've seen the controller hit 74MBps (averages
> around 30MBps) where the load average is always under 1.
>
> Here the queries from the quick benchmark test I put together:
>
> "seq 1000 row Select"=>"select * from <table> limit 1000",
> "seq 1000 row Select starting at random point"=>"select * from <table> limit
> $rand,1000",
> "ran 1000 row Select"=>"select * from <table> order by rand() limit 1000",
> "seq 1 row Select"=>"select * from <table> limit 1",
> "starting at ran 1 row Select"=>"select * from <table> limit $rand,1",
> "ran 1 row Select"=>"select * from <table> order by rand() limit 1",
None of the above queries can use indexes afaik. Query 4 doesn't need
them :) I bet you are doing a MASSIVE amount of full table scans.
Order by your autoincrement field on queries 1, 2, and 5. 5 and 6 are
the same query, btw, so don't use 6. I have never used order by
rand(), so I don't know if mysql is able to optimize query 3. What I
have done when customers request "random" ordering is create an integer
field, fill it with random data on insert, then "select * from table
order by randfield limit offset,xx". I start 'offset' at zero, then
increment it by the number of records requested every time a query is
made. That lets mysql use an index to position to the correct record
immediately, and prevents dupes.
> "1 row Insert Indexed columns"=>"insert into <table>
> (encCC,username,hitreferrerid) values ('deleteme','deleteme',".rand(1,100).")",
> "1 row Insert NonIndexed columns"=>"insert into <table>
> (city,state,intoprice) values ('deleteme','deleteme',".rand(1,100).")",
These should be fast.
> "1 row Update Indexed columns"=>"update <table> set encCC='DELETME',
> username='USERNAME', hitreferrerid=".rand(1,100)." where encCC='deleteme'",
> "1 row Update NonIndexed columns"=>"update <table> set city='DELETME',
> state='STATE', intoprice=".rand(1,100)." where city='deleteme' and state='deleteme'",
> "1 row Delete Indexed columns"=>"delete from <table> where encCC='DELETME'
> and username='USERNAME' limit 1",
> "1 row Delete NonIndexed columns"=>"delete from <table> where city='DELETME'
> and state='USERNAME' limit 1"
These should be fast, as long as you have the appropriate indexes on
your WHERE clause fields.
Are any queries showing up in your slow query log?
--
Dan Nelson
dnelson@stripped
