MySQL – Some stats to watch

If you’re staring at the screen, wondering what to look at to indicate something needs tuning in MySQL, run this:

 

show global status where Variable_name
like ‘created_tmp_disk_tables’
OR Variable_name like ‘handler_read_first’
OR Variable_name like ‘innodb_buffer_pool_wait_free’
OR Variable_name like ‘key_reads’
OR Variable_name like ‘max_used_connections’
OR Variable_name like ‘open_tables’
OR Variable_name like ‘select_full_join’
OR Variable_name like ‘slow_queries’
OR Variable_name like ‘uptime’;

 

reference: http://www.techrepublic.com/blog/opensource/10-mysql-variables-that-you-should-monitor/56

Variable

What it represents

Why you should monitor it

Threads_connected
This variable indicates the total number of clients that have currently open connections to the server. It provides real-time information on how many clients are currently connected to the server. This can help in traffic analysis or in deciding the best time for a server re-start.
Created_tmp_disk_tables
This variable indicates the number of temporary tables that have been created on disk instead of in-memory. Accessing tables on disk is typically slower than accessing the same tables in memory. So queries that use the CREATE TEMPORARY TABLE syntax are likely to be slow when this value is high.
Handler_read_first
This variable indicates the number of times a table handler made a request to read the first row of a table index. If MySQL is frequently accessing the first row of a table index, it suggests that it is performing a sequential scan of the entire index. This indicates that the corresponding table is not properly indexed.
Innodb_buffer_pool_wait_free
This variable indicates the number of times MySQL has to wait for memory pages to be flushed. If this variable is high, it suggests that MySQL’s memory buffer is incorrectly configured for the amount of writes the server is currently performing.
Key_reads
This variable indicates the number of filesystem accesses MySQL performed to fetch database indexes. Performing filesystem reads for database indexes slows query performance. If this variable is high, it indicates that MySQL’s key cache is overloaded and should be reconfigured.
Max_used_connections
This variable indicates the maximum number of connections MySQL has had open at the same time since the server was last restarted. This value provides a benchmark to help you decide the maximum number of connections your server should support. It can also help in traffic analysis.
Open_tables
This variable indicates the number of tables that are currently open. This value is best analyzed in combination with the size of the table cache. If this value is low and the table_cache value is high, it’s probably safe to reduce the cache size without affecting performance. On the other hand, if this value is high and close to the table_cachevalue, there is benefit in increasing the size of the table cache.
Select_full_join
This variable indicates the number of full joins MySQL has performed to satisfy client queries. A high value indicates that MySQL is being forced to perform full table joins (which are performance-intensive) instead of using indexes. This suggests a need for greater indexing of the corresponding tables.
Slow_queries
This variable indicates the number of queries that have taken longer than usual to execute. A high value indicates that many queries are not being optimally executed. A necessary next step would be to examine the slow query log and identify these slow queries for optimization.
Uptime
This variable indicates the number of seconds since the server was last restarted. This value is useful to analyze server uptime, as well as to generate reports on overall system performance. A consistent low value indicates that the server is being frequently restarted, thereby causing frequent interruptions to client service.
Posted in MySQL

Leave a Reply