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).
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.
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, please always post what you observe, e.g. the vmstat output.
Maybe someone on the list can see something you didn't.
Hops that helps. Mostly shots in the dark.
Bye,
Benjamin.
On Tue, Apr 09, 2002 at 06:35:57PM -0400, [EMAIL PROTECTED] wrote:
> I have one query, that basically reads from one table of 13 million rows,
> groups it by two fields, and inserts into a rollup table. The result set
> ends up being about 31,000 rows. This runs in about 2 minutes which is
> acceptable. However, I add an additional group by field, which will probably
> result in a million results and mysql goes to a crawl. It doesn't even
> max out one cpu as the former query does. Instead vmstat reports a process
> blocked and in wait, while mysql goes down to hardly any cpu. The box lags a
> tad bit like it's I/O related, but the I/O isn't abnormal. As soon as I use
> mysqladmin to kill the query everything is back to normal. I'll note the load
> goes above 3.0 when this is running but none of it's in CPU ...
>
> I realize this is very vague, bu I'm just shooting in the dark here wondering
> if someone has seen similar oddities, and it might be resolvable by tuning
> some of the parameters.
>
> 3.23.49, precompiled from mysql.com
> 2.4.9-21smp Linux
> key_buffer=16M table_cache=128 sort_buffer=8M
> record_buffer=2M tmp_table_size=16M
>
> I've tried increasing these but didn't help.
--
[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
