Top Useful MySQL Variables, That you Might Want to Monitor

Max_used_connections:

Displays the maximum number of connection made at any instance of time.

mysql -p -e”show global status” | grep Max_used_connections

Threads_connected:

Indicates the total number of open connections to the server

mysql -p -e”show global status” | grep  Threads_connected

Created_tmp_disk_tables :

Temporary tables created on disk instead of memory.

Handler_read_first : Number a table handler made a request to read the first row of a table index. This indicates that the table is not properly indexed.

Innodb_buffer_pool_wait_free :

Number of times mysql had to wait for memory pages to be flushed. If high, memory buffer is not correctly configured for the number of writes the server is currently doing.

Key_reads :

**** Number of  times mysql had to perform filesystem access to fetch database indexes. If this variable is high, Mysql’s key cache is overloaded and should be increased.

Open_tables :

Number of tables that are currently open. If this is low and table_cache value is high, it is safe to reduce the cache size. If the value is high and close to table_cache, increasing table_cache will help.

Select_full_join :

Indicates the number of full joins that had to be performed for the queries. High value indicates that MySQL had to do full table join instead of using indexes. In this case a greater indexing is required for the corresponding tables.

Slow_queries :

**** Indicates the number of queries that are slower than usual. You can adjust the length of time to be called slow in my.cnf file.

Uptime :

**** Indicates the number of seconds since the service was last restarted.

Note: You can use the following command to view the variables :

mysqladmin variables -uroot -p”PASSWORD”

mysqladmin extended-status