DB response is too slow

What can be done to identify开发者_JAVA百科 the reason for DB slowness?

When i ran the query in the morning it ran quickly & i got the output.

When i run the same query after 1 hr, it took more than 2mins.

What can be checked to identify this slowness?

All the tables are properly indexed.

If it's just a single query which is running slowly, EXPLAIN SELECT... as mentioned by arex1337 may help you see the reason.

It would also be worth looking at the output of e.g. vmstat on the box whilst running the query to see what it's doing - you should be able to get a feel for whether the machine is swapping, IO-bound, CPU-bound etc.

Check also with top to look for any rogue processes hogging CPU time.

Finally, if the machine is using RAID, it's possible that, if a drive has failed, the RAID array could be in a degraded state, which could make disc access slower (this is only applicable in certain RAID configurations, but worth considering and ruling out).

You can use EXPLAIN <your query> to get information about how MySQL executes your query. Maybe you get some hints about why it's slow.

EXPLAIN SELECT ... FROM ... WHERE ...;

Also, maybe you just have a slow query, and it was fast the second time because the result was cached?