Important variables of a MySQL server

Whether you are using a MySQL server or a cluster, one thing is sure among users. To optimize your MySQL server as much as possible. Something that MySQL's developers know about and are giving us access to a series of comprehensive variables, that one can use to monitor in realtime the status and performance of a MySQL server.

Those variables are accessible via SHOW STATUS command. Below you can find a list of 10 important variables aiding us to draw conclusions about the status of our MySQL server.

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

7. 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. (Remember: this is one of the checks executed by mysqltuner script)

8. 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.

9. 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.

10. 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.

Source