Re: Monitoring large resultsets

[Jerome Benezech]

I was more looking for queries retrieving large resultsets than slow queries.
There are many problems, one of which is that the php code is joomla and therefore extremely poorly optimized on the SQL side.
I had enabled the slow log and although this is useful and points to needed optimization, slow queries have a lot to do with good indexing and corresponding syntax. 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.

To explain myself a bit more. The MySQL server has a cache size of 2GB but there is a lot of pruning and the free memory goes up and down a lot.
On the php size, I see php processes holding over 100MB of memory.
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.