List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:December 29 2009 9:19am
Subject:Re: Why does this query take so long?
View as plain text  
Hi René, everybody!

René Fournier wrote:
> [[...]] However, even if the Index can't fit in memory (4GB of RAM, lots free), just
> reading it from disk should allow sub-millisecond response, no?

No chance!
Of course, performance of disk drives varies, but AFAIK typical values
are in the range of 7 - 10 ms per random access.
Assuming the (very unlikely) optimum case of one index access and one
data access, this would put you into the range of 15 - 20 ms just for
fetching the stuff from disk, not including any CPU time to traverse the
data structures etc.

Just do some math:
A disk with 7,200 rpm has 120 revolutions per second, so it needs a bit
more than 8 milliseconds per revolution.
Random access means you have to wait (on average) for half a revolution
(4 ms) until the desired block passes the disk head, and before that the
head needs to be positioned at the proper cylinder (the drive's data
sheet might give that time).

I guess that even with SSD you will not reach sub-millisecond response
times if the data is not in RAM.

> Strange thing is that I've used my laptop for benchmarking for the past five years
> and it's always produced results fairly typical or at least consistent in relation to our
> servers. This new thing is... new.

IMO, the most influential factor in single-user database benchmarks are
- disk performance
- RAM size for caches, cache replacement
- history, cache preloading

Their relative importance will vary, depending especially on data size.
As long as your data size is small enough that RAM differences between
server and laptop don't matter too much, performance on the laptop may
be a good prediction of that on the server.

With multi-user benchmarks, CPU performance, number of cores etc becomes
another important factor, again the relative weights will vary.


Joerg Bruehe,  MySQL Build Team,  Joerg.Bruehe@stripped
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

Why does this query take so long?René Fournier27 Dec
  • Re: Why does this query take so long?René Fournier28 Dec
    • Re: Why does this query take so long?René Fournier28 Dec
      • Re: Why does this query take so long?René Fournier28 Dec
        • RE: Why does this query take so long?Gavin Towey28 Dec
          • Re: Why does this query take so long?René Fournier29 Dec
            • Re: Why does this query take so long?Joerg Bruehe29 Dec