global_status_diff
NAME
global_status_diff: Status variables difference over time, with interpolation and extrapolation per time unitTYPE
ViewDESCRIPTION
global_status_diff takes two samples of GLOBAL STATUS, 10 seconds apart (within the view's query) and prints out the difference between the two samples, along with interpolated/extrapolated change per second/minute, respectively.
Measuring changes in GLOBAL STATUS is essential to any MySQL monitoring scheme. For example, the change in com_select presents the number of issues SELECT queries. Knowing the rate of queries (e.g. number of SELECTs per second) is key information to understanding server behavior and analyzing its performance.
global_status_diff provides with possibly the simplest status sampling code, as it allows one to query such information from within MySQL. Other tools require external applications/scripts to execute.
The view utilizes INFORMATION_SCHEMA.GLOBAL_STATUS and calculates the change for all variables. For some variables this does not make sense (examples are: rpl_status, slave_running, open_tables etc.). It is up to the user of this view to isolate desired variables.
Querying this view takes 10 seconds to complete. In between the first and second samples the view's query will be sleeping.
STRUCTURE
mysql> DESC common_schema.global_status_diff; +------------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+---------------+------+-----+---------+-------+ | variable_name | varchar(64) | YES | | NULL | | | variable_value_0 | longtext | YES | | NULL | | | variable_value_1 | varchar(1024) | YES | | NULL | | | variable_value_diff | double | YES | | NULL | | | variable_value_psec | double | YES | | NULL | | | variable_value_pminute | double | YES | | NULL | | +------------------------+---------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- variable_name: name of global status variable
- variable_value_0: first sample value
- variable_value_1: second sample value
- variable_value_diff: difference between the two samples
- variable_value_psec: average change in value per second of execution
- variable_value_pminute: estimated (via extrapolation) average change in value per minute of execution
EXAMPLES
Get status difference for varios InnoDB write metrics:
mysql> SELECT * FROM common_schema.global_status_diff WHERE variable_name LIKE 'innodb_%write%'; +-----------------------------------+------------------+------------------+---------------------+---------------------+------------------------+ | variable_name | variable_value_0 | variable_value_1 | variable_value_diff | variable_value_psec | variable_value_pminute | +-----------------------------------+------------------+------------------+---------------------+---------------------+------------------------+ | innodb_buffer_pool_write_requests | 1000528622 | 1000528683 | 61 | 6.1 | 366 | | innodb_data_pending_writes | 0 | 0 | 0 | 0 | 0 | | innodb_data_writes | 100335216 | 100335247 | 31 | 3.1 | 186 | | innodb_dblwr_writes | 603031 | 603032 | 1 | 0.1 | 6 | | innodb_log_write_requests | 338838621 | 338838633 | 12 | 1.2 | 72 | | innodb_log_writes | 69311204 | 69311213 | 9 | 0.9 | 54 | | innodb_os_log_pending_writes | 0 | 0 | 0 | 0 | 0 | +-----------------------------------+------------------+------------------+---------------------+---------------------+------------------------+
Show complete samples analysis:
mysql> SELECT * FROM common_schema.global_status_diff; +---------------------------------------+------------------+------------------+---------------------+---------------------+------------------------+ | variable_name | variable_value_0 | variable_value_1 | variable_value_diff | variable_value_psec | variable_value_pminute | +---------------------------------------+------------------+------------------+---------------------+---------------------+------------------------+ | aborted_clients | 2276049 | 2276059 | 10 | 1 | 60 | | aborted_connects | 72 | 72 | 0 | 0 | 0 | | binlog_cache_disk_use | 0 | 0 | 0 | 0 | 0 | | binlog_cache_use | 0 | 0 | 0 | 0 | 0 | | bytes_received | 48240316982 | 48240364869 | 47887 | 4788.7 | 287322 | | bytes_sent | 400087906753 | 400090499674 | 2592921 | 259292.1 | 15557526 | | com_admin_commands | 0 | 0 | 0 | 0 | 0 | | com_assign_to_keycache | 0 | 0 | 0 | 0 | 0 | | com_alter_db | 0 | 0 | 0 | 0 | 0 | | com_alter_db_upgrade | 0 | 0 | 0 | 0 | 0 | | com_alter_event | 0 | 0 | 0 | 0 | 0 | | com_alter_function | 0 | 0 | 0 | 0 | 0 | | com_alter_procedure | 0 | 0 | 0 | 0 | 0 | | com_alter_server | 0 | 0 | 0 | 0 | 0 | | com_alter_table | 2 | 2 | 0 | 0 | 0 | | com_alter_tablespace | 0 | 0 | 0 | 0 | 0 | | com_analyze | 102952 | 102952 | 0 | 0 | 0 | | com_backup_table | 0 | 0 | 0 | 0 | 0 | | com_begin | 0 | 0 | 0 | 0 | 0 | | com_binlog | 0 | 0 | 0 | 0 | 0 | | com_call_procedure | 0 | 0 | 0 | 0 | 0 | | com_change_db | 3762413 | 3762422 | 9 | 0.9 | 54 | ... many more rows ... +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Just look at the number of selects:
global_status_diff_clean and global_status_diff_nonzero build upon this view for further common usage.mysql> SELECT * FROM common_schema.global_status_diff WHERE variable_name = 'com_select'; +---------------+------------------+------------------+---------------------+---------------------+------------------------+ | variable_name | variable_value_0 | variable_value_1 | variable_value_diff | variable_value_psec | variable_value_pminute | +---------------+------------------+------------------+---------------------+---------------------+------------------------+ | com_select | 44977723 | 44977764 | 41 | 4.1 | 246 | +---------------+------------------+------------------+---------------------+---------------------+------------------------+