"SHOW STATUS ..." - Server Status Variables

MySQL Tutorials - Herong's Tutorial Examples

∟"SHOW STATUS ..." - Server Status Variables

This section describes Server Status Variables, which are counters used by MySQL server to provide statistics information about its running status.

What Are Server Status Variables? Server Status Variables are counters used by MySQL server to provide statistics information about its running status.

MySQL server supports two scopes for status variables:

Global - Counting globally from all user sessions.
Session (or Local) - Counting locally from the current user session only.

Here are some basic guidelines on how to view and manage status variables in Global and Session scopes.

1. "SHOW [...] STATUS" statement can be used to view both global value and session value with different syntaxes:

"SHOW VARIABLES ..." - Shows session values of matched Status Variables.
"SHOW GLOBAL VARIABLES ..." - Shows global values of matched Status Variables.
"SHOW SESSION VARIABLES ..." - Shows session values of matched Status Variables.
"SHOW LOCAL VARIABLES ..." - Shows session values of matched Status Variables.

For example:

mysql> show status like 'sort_scan';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Sort_scan     | 0     |
+---------------+-------+

mysql> show global status like 'sort_scan';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Sort_scan     | 5692  |
+---------------+-------+

2. "FLUSH STATUS" statement can be used to reset some (not all) Status Variables to 0.

Here are some interesting examples of Status Variables:

com_select - The number of times SELECT statement has been executed.
connections - The number of connection attempts (successful or not) to the MySQL server.
created_tmp_disk_tables - The number of internal on-disk temporary tables created by the server while executing statements.
created_tmp_tables - The number of internal temporary tables created by the server while executing statements.
global_connection_memory - The memory used by all user connections to the server. This memory is not calculated unless global_connection_memory_tracking is enabled.
handler_external_lock - The server increments this variable for each call to its external_lock() function, which generally occurs at the beginning and end of access to a table instance.
innodb_buffer_pool_bytes_data - The total number of bytes in the InnoDB buffer pool containing data.
innodb_row_lock_time_avg - The average time to acquire a row lock for InnoDB tables, in milliseconds..
innodb_row_lock_time_max - The maximum time to acquire a row lock for InnoDB tables, in milliseconds.
key_blocks_used - The number of used blocks in the MyISAM key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.
max_execution_time_exceeded - The number of SELECT statements for which the execution timeout was exceeded.
open_files - The number of files that are open.
open_tables - The number of tables that are open.
select_full_join - The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
select_range_check - The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the indexes of your tables.
select_scan - The number of joins that did a full scan of the first table.
slow_queries - The number of queries that have taken more than long_query_time seconds.
sort_scan - The number of sorts that were done by scanning the table.
table_locks_waited - The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
uptime - The number of seconds that the server has been up.