MySQL Performance Tunning Part - 3

During last two post, I have covered the query cache and scripts, which will start improving perofrmance of repeat queries. So you will notice performance improvement.

If you look at the script output, it will give us details about the slow queries, if you have not enabled logging of slow queries, please enable it, so it will allow us to see what is causing problem (I should have describe this in my first blog).

You might see somthing like this, slow query logging is enabled and it logs the query if it takes more then 2 seconds.

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2 sec.
You have 35148 out of 63753279 that take longer than 2 sec. to complete
Your long_query_time seems to be fine

This is the parameters you would have to add:

log-slow-queries = /var/lib/mysql/slow-queries.log
long_query_time = 2

Once you have slow query log, you can start looking into the queries and findout how many queries are slow and its frequency. I would improve queries first which is being run almost every visit or page view of the site, even it is taking 3-4 seconds while there is a query which take 4 minutes but run only two-three times a day, most likely these long queries are for backend processing.

You can find out tools which helps to process these slow queries and give combined count for each queries which is slower.

http://www.experts-exchange.com/Programming/Languages/Scripting/CGI/Q_22064844.html

There are several cause of slow queries and most likely it is due to not having proper indexes created for given query.

To findout index is not properly, use mysql - EXPLAIN and it will give more details how many raw, it has to scan for given queries, it will also give details about posible fields to use for index to improve performance of the query.

If we do not have propery indexes in mysql table, mysql has to use internal sort which it write to disk and then gets the results which cause the performance issue.

Please read this artical more about indexing.

http://www.databasejournal.com/features/mysql/print.php/1382791

I will continue on this in my next post. I would like to see feedback and comments on my posts.

-Babu Satasiya