10 MySQL variables that you should monitor

Regardless of whether you're running a single MySQL server or a cluster of multiple servers, one thing you are always interested in is squeezing the maximum performance out of your system. MySQL's developers were well aware of this, and so they provided a fairly comprehensive list of performance variables that you can monitor in real time to check the health and performance of your MySQL server.

These variables are accessible via the SHOW STATUS command. In Table A, we've listed 10 of the most important performance variables you should monitor when using MySQL, and we explain which particular attribute each of them reflects.

Table A

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_cache value, 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.