Benjamin Pflugmann <[EMAIL PROTECTED]> writes:
> Hello.
> 
> First, key_buffer=16M seems a bit low for me with 13 million rows
> (well, depends on the indexes, and how much free memory you have, of
> course).

1 gig, plenty free.

 
> If I am not mistaken, sort_buffer/tmp_table_size are the one mostly
> needed for this query, I think. Btw, you can get more info about the
> query with EXPLAIN. Please post the result of it (for both SELECTs),
> if you answer.

Query 1, works:  group by bayid, ds1

table   type    possible_keys   key     key_len ref     rows    Extra
detail_20020408 ALL     NULL    NULL    NULL    NULL    13083666        Using temporary

Query 2, bad: group by bayid, ds1, scid
table   type    possible_keys   key     key_len ref     rows    Extra
detail_20020408 ALL     NULL    NULL    NULL    NULL    13083666        Using temporary


> Another idea is that you have an index over the two fields you were
> talking, but not the additional field. In this case, the first query
> can run only from index, but the second needs to read from the table
> file. EXPLAIN should show.

Btw, this is a full table scan, no WHERE clause exists, so I'm not sure
where indexes would change the behaviour of this.

 
> Btw, please always post what you observe, e.g. the vmstat output.
> Maybe someone on the list can see something you didn't.

Query 1:
  procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 1  0  0    492   5084  91104 837016   0   0     4     4    3     3   1   1   1
 1  0  0    492   5084  91104 836632   0   0 11968     0 1178  2444  47   6  47
 2  0  0    492   5084  91104 835524   0   0  8228   714 1653  3283  36   6  58
 2  0  0    492   5096  91104 835248   0   0 12342     8 1205  2553  49   7  44
 2  0  0    492   5096  91104 836780   0   0 11220     0 1180  2477  47   7  47
 2  0  0    492   5096  91104 836764   0   0 10472   714 1729  3297  40   6  54
 1  0  0    492   5096  91104 836744   0   0 11220     0 1220  2609  49   5  46
 0  1  0    492   5096  91104 836728   0   0 10392   736 1599  3259  42   7  50

Query 2:
  procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 2  0  0    492   5060  91148 820528   0   0     4     4    4     3   1   1   1
 2  0  0    492   5096  91152 817160   0   0 16836     0 1276  2611  84  13   3
 4  0  0    492   5092  91164 817340   0   0 11232  3040 1168  2573  81  16   3
 3  0  0    492  16404  91168 820312   0   0  9736  2722 1776  4093  74  16  10
 2  1  1    492   5092  91168 834696   0   0 11976  7756 1183  2793  81  17   2
 0  2  1    492   5092  91168 834420   0   0  9734  8382 1950  3270  60  15  25
 2  1  1    492   5092  91168 833448   0   0 10476   164 1705  2571  45   7  47
 2  1  1    492   5092  91172 833172   0   0 13100  6382 1536  2897  78  18   3
 1  2  1    492   5092  91172 832888   0   0  9354  9148 2187  3468  46  14  41
 3  1  2    492   5092  91172 831904   0   0 11226  6210 1644  2567  60  11  28
 2  2  1    492   5092  91172 832900   0   0 13102  7286 1740  2507  75  19   6

You can see how very eradict the machine gets... Interactive typing even starts
to pause when you see the idle near zero, it's like something is thrashing
in the kernel but top yields no clues.

Cliff



---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to