When looking at a set of SQL statements in isolation with tools such as the slow query log, processlist and tcpdump/mk-query-digest it is easy to identify queries that are slow.
It is more difficult however to identify the frequency of the query, and whether the slow query is actually acceptable in your overall system design.
I very simple technique to help you is to comment your queries. For example:
SELECT /* 10m cache */ ...;
When I’m working for a client, this process enables me to realize the purpose of the query more quickly and to help prioritize which queries I need to improve first. This does not mean I simply ignore these less frequent queries, however my choices for reviewing and indexing queries on given tables is adjusted generally for OLTP queries first, and batch second.
As with all practices it is important to implement across your full code base.
Hi Roland,
mk-query-digest provides with a lot of valuable informaion per query, such as the number of times it has appeared, plus the total amount (and percentage) of time it has consumed (and in relation to queries found by mk-query-digest).
It also let’s you know the average, the 95% point, and much more.
So, you are able to concentrate on queries which ran for longer time or more frequently, as you wish.
I’m noting all this because from your post it may appear that mk-query-digest does nothing but show the slow queries, which is not the case.
regards
apologies, misspelled your name (again!)
Just a note on bottlenecks. Improving anything that isn’t a bottleneck often results in compounding the bottleneck. So it is important to understand what a true bottleneck is. “An
hour lost at a bottleneck is an hour lost for the entire system. An hour saved at a non-bottleneck
is a mirage”. [Goldratt 1984]. http://www.iimcal.ac.in/research/download/msom_bottleneckakc.pdf