•  

Announcement

Announcement Module
Collapse
No announcement yet.

5.0 speed variation when using "order by"

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • 5.0 speed variation when using "order by"

    #1
    I have an order by query that seems to be unpredictable in MySQL 5.0.

    The table has about 400K rows. There are 3 columns of interest: 2 integer (i1 and i2), one varchar (name). I have a separate index on both of the integer columns.
    The query is: "select i1,i2,name from table order by i1". Explain indicates the filesort algorithm is in use. (The table is in fact presorted by i1, but we don't assume that).

    For timing I'm doing

    time mysql -ABN -e 'select i1,i2,name from table order by i1' database > /dev/null

    On MySQL 4.x the time is consistently between 2.5 and 3.5 seconds, even with a good amount of load on the machine.

    On MySQL 5.0 the time varies much more. 1 in 10 measurements take about a minute. And we have no load at all on the 5.0 machine.

    My OS is 64 bit Linux 2.6.9-34.0.2.ELsmp. We've tried several different installations of MySQL 5.

    Any feedback appreciated, thanks. We use this logic a fair amount and want to decide whether to rewrite it.
    Tags: None
  • #2
    Heather,

    Did you check EXPLAIN statements for MySQL 5.0 are they always the same ?

    Next I'd check Handler_xxx values from "SHOW LOCAL STATUS" in MySQL 5.0 these show what exactly was done during query execution on lower level.

    If they are different you might have different executions using different plans for MySQL 5.0 which is possible in some cases.

    This of course assumes you're running test on idle box so side load can't be the issue.

    Comment

    • #3
      Thanks very much. I am indeed seeing a correlation in Handler_read_rnd_next and Handler_write. When I have a typical query (2.5 - 3.5 seconds), those values increment by 10 or so. (I see they are a rolling sum). When I have a slow query (50 seconds or so), they increment by over 100.

      It also looks like if I pause a second or 2 between sending queries, the performance stays at the desired level (2.5 - 3.5 seconds). It seems I can mostly get the long queries by sending a query just after the first one finished. So perhaps the filesort is doing some cleanup in the time just after the first query returns, and somehow I am interrupting it by sending the second query so soon.

      We don't have to worry about this case in our application logic. Still, it isn't something I saw in my 4.0 testing.

      Heather

      Comment

      Working...
      X