Hi. Well, your are right, indexes are not used at all, so ignore everything about key_buffer and indexes I said, regarding this issue (but key_buffer=16MG seems small with 1GB anyhow ;-)
Depending on how often you need the query, and how much columns are involved, you may want to consider to add a key over all involved columns, which would avoid reading the data file, but read the smaller key only, instead (if it's not clear why that would help, see the EXPLAIN link below and search for the explanation of "using index"). Hm. From vmstat output it looks like the second query is disk-bound on writing. Most probably writing temporary table(s) as EXPLAIN tells so (compare http://www.mysql.com/doc/E/X/EXPLAIN.html). http://www.mysql.com/doc/T/e/Temporary_files.html tells, for sorting in GROUP BY, the maximum needed disk space is: (length of what is sorted + sizeof(database pointer)) * number of matched rows * 2 So if the first query needs about 5MB (~31000 rows), the second one needs about 30 times of that, namely 150MB (~1000000 rows). If your columns are larger (I presumed about 20 bytes above), it gets worse. >From the vmstat output I guess this is rather ~25MB resp. ~650MB for your case (of course, that is only a very rough guess). tmp_table_size determines the maximum size of an in-memory temporary table. If a table gets bigger, it automatically converts it to a disk table. I don't know whether the sorting table for GROUP BY qualifies for an in-memory table, but how about setting (at least temporarily) tmp_table_size to a real huge value (say 500MB) and re-run the queries and look at the vmstat output. "bo" should be real small at least for the first query, if in-memory tables are used. Well, to sum up: Your first query mainly seems to be bound on disk read speed (your table is about 1GB?). The second one needs a much larger tmp table (which it also has to sort!) and "trashes" your disk, that's probably why the machine feels unresponsive. For now, I have no further ideas than playing with "tmp_table_size" and trying to use an index over all column. Anybody else? Bye, Benjamin. On Tue, Apr 09, 2002 at 09:09:30PM -0400, [EMAIL PROTECTED] wrote: > Benjamin Pflugmann <[EMAIL PROTECTED]> writes: [...] > 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 [...] > > 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 > 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 > 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 [...] -- [EMAIL PROTECTED] --------------------------------------------------------------------- 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
