Re: Question on slow queries

[Rick James]

> MQ> I can drop some backup tables i think in order to lower DB size.

If you don't touch the table, none of it will get into the buffer_pool -- which is a "cache". It is quite normal to have lots more data (on disk) than buffer_pool space. As long as one does not try to read it all, the cache may work nicely.

If there is any swapping, that can be deadly for performance. It is better to decrease the buffer_pool to avoid swapping.

> MQ> Hmm how can i find out? (I/O bound)

Windows: Resource Monitor (from Task Manager)
Unix: iostat.

> MQ> I use the PHP framework Kohana; The show columns can depend on their ORM class that resolves dependencies between objects (hasmany etc)

Sloppy, and costly. Complain to Kohana.

> MQ> I think is the update that pt-query-digest chose. That query just updated the number of logins the user made and it's run when the user log in.

I hope it is not logging in for each query !

> I _think_ that this says that it is not stumbling over itself:
> 0.03 QPS, 0.01x concurrency

If I read those number correctly, they mean
0.03 queries per second (a very low number)
0.01x concurrency -- very infrequently have two things going on at once.
When concurrency is high (dozens), the system can act like a freeway in rush hour.

> 64.27 QPS, 0.18x concurrency

That seems more 'normal'. 64 qps is not bad, unless the queries are slow. Still, concurrency does not seem to be a problem.

> 8915s in 14 hours?

That sounds like it is nowhere near to consuming the CPU.

Puzzling.