MySQueaL Resources

SHOW STATUS Statement

This MySQL statement displays status information and variables from the server.

This MySQL statement displays status information and variables from the MySQL server. You can reduce the number of variables shown with the LIKE clause, based on a naming pattern for the variable name. Similarly, the WHERE clause may be used to refine the results set.

Here is an example of how you can use this MySQL statement with the LIKE clause:

SHOW STATUS LIKE '%log%';

+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Binlog_cache_disk_use        | 0     | 
| Binlog_cache_use             | 0     | 
| Com_show_binlog_events       | 0     | 
| Com_show_binlogs             | 0     | 
| Com_show_engine_logs         | 0     | 
| Innodb_log_waits             | 0     | 
| Innodb_log_write_requests    | 0     | 
| Innodb_log_writes            | 1     | 
| Innodb_os_log_fsyncs         | 3     | 
| Innodb_os_log_pending_fsyncs | 0     | 
| Innodb_os_log_pending_writes | 0     | 
| Innodb_os_log_written        | 512   | 
| Tc_log_max_pages_used        | 0     | 
| Tc_log_page_size             | 0     | 
| Tc_log_page_waits            | 0     | 
+------------------------------+-------+

The results show any system variable in which the variable name has the word log in it. This is a new server installation, so the results have small or zero values. If you wanted to eliminate the InnoDB logs from the results, you could use the WHERE clause like so:

SHOW STATUS 
WHERE Variable_name LIKE '%log%' 
AND Variable_name NOT LIKE '%Innodb%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Binlog_cache_disk_use  | 0     | 
| Binlog_cache_use       | 0     | 
| Com_show_binlog_events | 0     | 
| Com_show_binlogs       | 0     | 
| Com_show_engine_logs   | 0     | 
| Tc_log_max_pages_used  | 0     | 
| Tc_log_page_size       | 0     | 
| Tc_log_page_waits      | 0     | 
+------------------------+-------+

Notice that when using the WHERE clause, the field name in the results must be given. In this case, the field name Variable_name is given. You could also give the field name Value to limit the results to entries of a certain value or range of values.

SHOW GLOBAL STATUS 
WHERE Variable_name LIKE '%log%' 
AND Variable_name LIKE '%Innodb%' 
AND Value > 100;
 
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Innodb_os_log_written | 512   | 
+-----------------------+-------+

In this example we are looking for log entries for InnoDB with a value over 100. The results consist of just one entry.

You can change many variables at server startup using options for the MySQL server daemon. You can change some of them while the daemon is running with the SET statement, without having to restart the server.