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
