Don’t forget about SHOW PROFILES
It seems that a lot of people want to try to improve MySQL performance by focusing on server status counters and configuration variables. Looking at counters, and “tuning the server,” is better than nothing, but only barely. You care first and foremost about how long it takes to execute a query, not about how many of this-and-that the server performs or about how big or small this-and-that buffer is. What you really need is timing information.
You can use the slow query log to find timing information about queries, and then you can examine those queries with SHOW PROFILES to see the timing information about the query’s execution itself.
This concept is very simple and absolutely fundamental: if you care about time (and you do!), then measure and optimize time. But it’s so often overlooked or misunderstood.
The addition of SHOW PROFILES was a major step forward in the ability to optimize server and application performance. (Thanks Jeremy Cole!) As time passes and people upgrade their servers, it’s becoming more common to see it in production, which is an enormous help. Now that the differences between the Community and Enterprise versions of the server have been erased, it will be available in all future server versions, which is great news.
While I agree SHOW PROFILES is valuable to gleen some more information about a query, regardless of knowing about the MySQL Source code, and you get better timing then the MySQL client 1/10ms response, SHOW PROFILES exposes internal messaging which is sometimes a bit misleading.
I wonder if for 5.1, MySQL actually took the time to clean up some of the messaging displayed by SHOW PROFILES, or if they added some more data points to expose some more instrumentation to the end user.
Ronald Bradford
31 May 09 at 8:40 pm
The way I see it there are two roles that may or may not overlap depending on the size/complexity of your mysql deployment. The first role is responsible for the health of the server and uses SHOW STATUS, SHOW USER_STATISTICS and SHOW TABLE_STATISTICS to determine system performance and resource consumption over time. The second role is concerned with the performance of individual statements over time.
On a large deployment, the first role doesn’t have time to do the tasks (query tuning) of the second role. There is time to identity accounts that have performance problems and occasionally time to identify problem queries after they have become a problem. But there is no time to tune queries before the fact.
On other deployments, both roles may be done by the same people.
I don’t use SHOW PROFILES. I frequently identify accounts with performance problems and occasionally identiy the problem queries, but I have neither the time nor the privileges to fix those queries.
Mark Callaghan
31 May 09 at 11:35 pm
The “Query Profiler” feature of the latest version of SQLyog Enterprise does all the manual book-keeping required for getting SHOW PROFILE data for an individual query. The user gets to see the profiling info along with the result-set.
Rohit Nadhani
1 Jun 09 at 12:11 am
It’s important to note that SHOW PROFILES is not reliable when looking at CPU timings on a production machine – as they are *process* wide (and hence include CPU time for other threads executing whilst you run the profile).
So don’t rely on anything but the plain timings (which are correct), such as CPU times, context switches etc. on a production instance. :)
Mark Leith
1 Jun 09 at 7:45 am