www.Tutorialsforu.info

So we came op with slow query log parser tool which works with adjusted slow query log format and which gives samples of queries after aggregation. Here is how its output looks like:

SQL:

1. ### 3579 Queries

2. ### Total time: 3.348823, Average time: 0.000935686784017883

3. ### Taking 0.000269 to 0.130820 seconds to complete

4.

5. ### Rows analyzed 1 - 1

6. SELECT id FROM forum WHERE id=XXX;

7.

8. SELECT id FROM forum WHERE id=12345;

As you can see it also prints minimum and maximum execution times so you will be able to see if only in certain cases query takes long time to execute, for example if plan is different based on constants.

How to use this tool set ?

First be aware this patch to MySQL is not official and should be used with caution. We think it is pretty safe but it surely did not get as much battle testing as rest of MySQL Server. Good thing is - you do not have to run patched version all the time. You can just start it for a few hours to generate you query log and get back to unpatched version.

It is best if you generate this log for all your queries with long_query_time=0 so if serious portion of you load comes from very simple queries you would not lose this kind of info. Yes this will reduce your performance a bit and will require plenty of disk space which is another reason you might not wish to run it in this mode all the time. Happily you can change long_query_time without restarting server so it is easy to get sample of all queries for some period of time and then get back to logging only very slow queries.

Once you have created full log - parse it and check queries using EXPLAIN starting from most impacted onces. After you've implemented changes - repeat. Changes may help to one queries but hurt others, for example adding indexes often help SELECT queries but slow down INSERT/UPDATE ones.

Final Note: You do not have to have patched MySQL for these utilities to work. they are designed to handle standard slow query log format as well.