List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 25 2003 4:26am
Subject:Re: performance
View as plain text  
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
Thread
performanceRyan R. Tharp25 Apr
  • Re: performanceDan Nelson25 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • Re: performancecolbey25 Apr
    • Re: performanceDan Nelson25 Apr
  • Re: performancecolbey25 Apr
  • Re: performanceRyan R. Tharp25 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • Re: performancecolbey25 Apr
      • RE: performanceAdam Nelson25 Apr
        • Re: performanceJeremy Zawodny15 May
  • Re: performanceJonathan Disher25 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • Re: performancecolbey25 Apr
    • Re: performanceChristian Jaeger28 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • RE: performanceAdam Nelson28 Apr