Data Management

Understanding the performance statistics from MySQL's SHOW STATUS command

The SHOW STATUS command in MySQL offers tons of in-depth technical data to help you understand things like memory usage, cache performance and resource allocation. Learn more about how to interpret these statistics.

Beyond the basic SHOW STATUS statistics I described earlier, the command also offers more technical data which can be used to assess things like memory usage, cache performance, and resource allocation. Here are some of the important variables in this category.

Open_tables, Opened_tables

This statistic shows you the number of tables currently open, while the Opened_tables variables shows the number of tables opened since the server was last restarted. These values provide a good indication of whether your table cache is of the right size; a high value for Opened_tables indicates that your cache should be larger.

Slow_queries

This statistic indicates the number of queries that have taken longer than a predefined per-query time limit. A large value here indicates that the server is not able to process queries at the speed it should and is a cause for concern (although you should make this determination after checking the slow query log, which contains the actual query strings, to see if it's the queries themselves that are unusually long or complex).

Select_scan

This statistic shows the number of joins which required a full scan of the first table named in the join. Since a full table scan is time- and resource-intensive, a high value here means that your queries are operating inefficiently and perhaps require further optimization.

Select_full_join

This statistic reveals the total number of joins performed without making use of indexes. An index speeds up table searches and is advisable on all fields that are queried frequently. Therefore, a high value here means that MySQL is not using indexes and is therefore taking longer to build a result set. The problem can be corrected (or at least mitigated) by indexing important fields of the join.

Qcache_hits

This statistic shows the number of accesses to the new MySQL query cache. The query cache stores the results of frequently-used queries so as to speed up response time; a high value here means that MySQL is working efficiently by using the cache instead of rebuilding result sets every time the same query is fired.


Read more about performance statistics in MySQL

"Understanding real-time performance statistics in MySQL"

"How to use the SHOW STATUS command in MySQL"

"How to examine and kill MySQL client processes"

"Specialized status commands in MySQL"


Editor's Picks

Free Newsletters, In your Inbox