Re: Monitoring large resultsets

[Aftab Khan]

>For example, a lot of the queries I see in the log are select count(id) which return only 1 int row but take 6s when the server is busy.

We don't know how did you process slow query log, try this:

mysqldumpslow -s t -t 5 /path/to/slow_query_log

Provide use with EXPLAIN SELECT for slow queries and SHOW CREATE TABLE info and wrap out it inside [ CODE ] .... [/ CODE ] without spaces inside brackets[].

>I suspect some queries are retrieving huges resultsets. Cache is pruned because it cannot hold results of that size. Php holds the results array in memory.

To help find how much memory is allocated to per-thread/global buffers and see how much data is sent/received over the time etc..

Provide us output of:

SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;