Mysql – Difference between show status and show global status in thesql

MySQLmysql-5.1statistics

When I execute "show status" and "show global status" in MySQL at interval of 5 seconds , I got set of 291 results each time. But, value of some parameters are different. Below are the list of those parameters:

Status Variable_name    Value   Gloabl status Variable_name Value
Aborted_clients      2350   Aborted_clients 2347
Binlog_cache_use 1494076    Binlog_cache_use 1493386
Bytes_received  180     Bytes_received  14514902897
Bytes_sent  8124        Bytes_sent  73596494244
Com_admin_commands 0        Com_admin_commands 847561
Com_alter_table 0       Com_alter_table 478
Com_begin   0       Com_begin   1009
Com_change_db   1       Com_change_db   8127737
Com_commit  0       Com_commit  1004
Com_create_table    0   Com_create_table    235
Com_create_trigger  0   Com_create_trigger  25
Com_create_view 0       Com_create_view 4
Com_delete  0       Com_delete  417012
Com_delete_multi    0   Com_delete_multi    218
Com_drop_table  0       Com_drop_table  239
Com_drop_view   0       Com_drop_view   8
Com_insert  0       Com_insert  109750
Com_insert_select   0   Com_insert_select   14
Com_kill    0       Com_kill    104
Com_lock_tables 0       Com_lock_tables 233
Com_optimize    0       Com_optimize    1
Com_rollback    0       Com_rollback    4
Com_select  0       Com_select  4955791
Com_set_option  1       Com_set_option  9473284
Com_show_collations 0   Com_show_collations 3109
Com_show_create_table   0   Com_show_create_table   1181
Com_show_create_trigger 0   Com_show_create_trigger 25
Com_show_databases  0   Com_show_databases  5
Com_show_engine_status  0   Com_show_engine_status  9538
Com_show_fields 0       Com_show_fields 516
Com_show_function_status 0  Com_show_function_status    4
Com_show_grants 0       Com_show_grants 4
Com_show_keys   0       Com_show_keys   60
Com_show_open_tables    1   Com_show_open_tables    26714
Com_show_plugins    0   Com_show_plugins    2
Com_show_procedure_status 0     Com_show_procedure_status   4
Com_show_processlist    0   Com_show_processlist    27338
Com_show_status 2       Com_show_status 16508
Com_show_table_status   0   Com_show_table_status   940
Com_show_tables 0       Com_show_tables 7
Com_show_triggers   0   Com_show_triggers   235
Com_show_variables  0   Com_show_variables  3135
Com_stmt_close  0       Com_stmt_close  279
Com_stmt_execute    0   Com_stmt_execute    284
Com_stmt_prepare    0   Com_stmt_prepare    284
Com_unlock_tables   0   Com_unlock_tables   233
Com_update  0       Com_update  967361
Com_update_multi    0   Com_update_multi    2272
Connections 8132537     Connections 8129927
Created_tmp_disk_tables 0   Created_tmp_disk_tables 349226
Created_tmp_files   63684   Created_tmp_files   63645
Created_tmp_tables  1   Created_tmp_tables  3893159
Handler_commit  0       Handler_commit  7480404
Handler_delete  0       Handler_delete  75596
Handler_prepare 0       Handler_prepare 2151290
Handler_read_first  0   Handler_read_first  29815473
Handler_read_key    0   Handler_read_key    1217425496
Handler_read_next   0   Handler_read_next   3826008161
Handler_read_prev   0   Handler_read_prev   2418519
Handler_read_rnd    0   Handler_read_rnd    9075133
Handler_read_rnd_next   81  Handler_read_rnd_next   3683107601
Handler_rollback    0   Handler_rollback    21610
Handler_update  0       Handler_update  17784530
Handler_write   80      Handler_write   591666282
Innodb_buffer_pool_pages_dirty 58 Innodb_buffer_pool_pages_dirty 67
Innodb_buffer_pool_pages_flushed 1161267 Innodb_buffer_pool_pages_flushed 1160463
Innodb_buffer_pool_read_requests 2863173268 Innodb_buffer_pool_read_requests 2855722562
Innodb_buffer_pool_write_requests 47910334 Innodb_buffer_pool_write_requests 47904489
Innodb_data_fsyncs 284362   Innodb_data_fsyncs 284205
Innodb_data_writes 3562289  Innodb_data_writes 3560356
Innodb_data_written 3802343424   Innodb_data_written 3774550528
Innodb_dblwr_pages_written 1161267 Innodb_dblwr_pages_written   1160463
Innodb_dblwr_writes 38015   Innodb_dblwr_writes 37994
Innodb_log_write_requests 7780429 Innodb_log_write_requests 7778131
Innodb_log_writes 2336822    Innodb_log_writes  2335731
Innodb_os_log_fsyncs 208319  Innodb_os_log_fsyncs   208204
Innodb_os_log_written 98765824   Innodb_os_log_written  97323008
Innodb_pages_written 1161267     Innodb_pages_written   1160463
Innodb_row_lock_time 137380  Innodb_row_lock_time   137349
Innodb_row_lock_waits 5392   Innodb_row_lock_waits  5386
Innodb_rows_deleted 75654    Innodb_rows_deleted    75596
Innodb_rows_inserted 7773757     Innodb_rows_inserted   7773709
Innodb_rows_read 3816403633  Innodb_rows_read   3808082321
Innodb_rows_updated 916125   Innodb_rows_updated    915711
Key_blocks_unused 422872     Key_blocks_unused  422915
Key_read_requests 320523319  Key_read_requests  320497057
Key_reads   6024194      Key_reads  6024151
Key_write_requests 7668445   Key_write_requests 7668386
Last_query_cost  10.499      Last_query_cost    0
Open_tables 194      Open_tables    193
Opened_files    1628563      Opened_files   1628148
Opened_table_definitions 0   Opened_table_definitions   5639
Opened_tables   0        Opened_tables  490561
Qcache_free_blocks 58892     Qcache_free_blocks 60662
Qcache_free_memory 240024696     Qcache_free_memory 245087632
Qcache_hits 30351231     Qcache_hits    30338589
Qcache_inserts  3605606      Qcache_inserts 3602774
Qcache_not_cached 1339432    Qcache_not_cached  1338776
Qcache_queries_in_cache 77058    Qcache_queries_in_cache    75273
Qcache_total_blocks 213544   Qcache_total_blocks    211643
Queries 62827552                 Queries    62802229
Questions   5        Questions  62659646
Select_full_join    0    Select_full_join   63072
Select_full_range_join  0    Select_full_range_join 31
Select_range    0        Select_range   378850
Select_scan 1        Select_scan    2750215
Slow_queries    0        Slow_queries   26778
Sort_merge_passes   0    Sort_merge_passes  33570
Sort_range  0        Sort_range 366065
Sort_rows   0        Sort_rows  8796695
Sort_scan   0       Sort_scan   449489
Table_locks_immediate 19683336  Table_locks_immediate   19666285
Threads_cached  196     Threads_cached  193
Threads_connected 7         Threads_connected 10
Uptime  205696              Uptime  205606
Uptime_since_flush_status 205696 Uptime_since_flush_status 205606

Since both of the queries are same then why so much parameters have different values? Please note that I am using MySQL 5.1 community edition. I have only one active database in this dedicated Windows 2008 DB server. Please let me know if you need any other information.

Best Answer

There is most definitely a difference between SHOW STATUS; and SHOW GLOBAL STATUS;

  • SHOW GLOBAL STATUS; will give you status variables that have updated since mysqld started for all sessions that are connected or have ever been connected.
  • SHOW STATUS; will give you status variables that have updated within your session. The command can also be expressed as SHOW SESSION STATUS; (As the MySQL Documentation says, it displays the status values for the current connection).

To physically show the difference, the information_schema database has them separated as

  • INFORMATION_SCHEMA.GLOBAL_STATUS
  • INFORMATION_SCHEMA.SESSION_STATUS

These information_schema tables have been around since MySQL 5.1.12.

Why the difference?

To thoroughly demonstrate the difference, let me run an INNER JOIN of these tables to show which values are different. Here is the query:

SELECT a.variable_name,a.variable_value,b.variable_value
FROM information_schema.global_status A INNER join information_schema.session_status B
USING (variable_name) WHERE A.variable_value <> B.variable_value;

Please note the output:

mysql> SELECT A.variable_name,A.variable_value,B.variable_value
    -> FROM information_schema.global_status A INNER join information_schema.session_status B
    -> USING (variable_name) WHERE A.variable_value <> B.variable_value;
+---------------------------+----------------+----------------+
| variable_name             | variable_value | variable_value |
+---------------------------+----------------+----------------+
| BYTES_RECEIVED            | 123641576598   | 7757           |
| BYTES_SENT                | 149888451047   | 300001         |
| COM_ADMIN_COMMANDS        | 121915         | 0              |
| COM_ALTER_TABLE           | 111            | 0              |
| COM_BEGIN                 | 1              | 0              |
| COM_CALL_PROCEDURE        | 530            | 0              |
| COM_CHANGE_DB             | 1623           | 1              |
| COM_COMMIT                | 19220          | 0              |
| COM_CREATE_FUNCTION       | 4              | 0              |
| COM_CREATE_PROCEDURE      | 126            | 0              |
| COM_CREATE_TABLE          | 354            | 0              |
| COM_DEALLOC_SQL           | 924            | 0              |
| COM_DELETE                | 64668          | 0              |
| COM_DELETE_MULTI          | 19             | 0              |
| COM_DROP_FUNCTION         | 4              | 0              |
| COM_DROP_PROCEDURE        | 148            | 0              |
| COM_DROP_TABLE            | 238            | 0              |
| COM_EXECUTE_SQL           | 945            | 0              |
| COM_INSERT                | 1182379        | 0              |
| COM_INSERT_SELECT         | 40673          | 0              |
| COM_KILL                  | 68             | 0              |
| COM_LOAD                  | 22386          | 0              |
| COM_LOCK_TABLES           | 2              | 0              |
| COM_OPTIMIZE              | 2              | 0              |
| COM_PREPARE_SQL           | 948            | 0              |
| COM_REPAIR                | 8              | 0              |
| COM_REPLACE               | 34737          | 0              |
| COM_ROLLBACK              | 13             | 0              |
| COM_SELECT                | 1107225018     | 65             |
| COM_SET_OPTION            | 602159         | 0              |
| COM_SHOW_BINLOGS          | 8              | 0              |
| COM_SHOW_CHARSETS         | 12             | 0              |
| COM_SHOW_COLLATIONS       | 100            | 0              |
| COM_SHOW_CREATE_DB        | 6              | 0              |
| COM_SHOW_CREATE_FUNC      | 2453           | 0              |
| COM_SHOW_CREATE_PROC      | 5684           | 0              |
| COM_SHOW_CREATE_TABLE     | 1313           | 0              |
| COM_SHOW_DATABASES        | 275            | 0              |
| COM_SHOW_EVENTS           | 1              | 0              |
| COM_SHOW_FIELDS           | 13666          | 1              |
| COM_SHOW_FUNCTION_STATUS  | 362            | 0              |
| COM_SHOW_KEYS             | 494            | 0              |
| COM_SHOW_PLUGINS          | 2              | 0              |
| COM_SHOW_PROCEDURE_STATUS | 361            | 0              |
| COM_SHOW_PROCESSLIST      | 488943         | 15             |
| COM_SHOW_SLAVE_STATUS     | 4              | 0              |
| COM_SHOW_STATUS           | 12315          | 10             |
| COM_SHOW_STORAGE_ENGINES  | 30             | 0              |
| COM_SHOW_TABLE_STATUS     | 320            | 0              |
| COM_SHOW_TABLES           | 584            | 0              |
| COM_SHOW_TRIGGERS         | 2              | 0              |
| COM_SHOW_VARIABLES        | 190            | 1              |
| COM_STMT_CLOSE            | 924            | 0              |
| COM_STMT_EXECUTE          | 945            | 0              |
| COM_STMT_PREPARE          | 948            | 0              |
| COM_TRUNCATE              | 522            | 0              |
| COM_UNLOCK_TABLES         | 2              | 0              |
| COM_UPDATE                | 496041         | 0              |
| COM_UPDATE_MULTI          | 625            | 0              |
| CREATED_TMP_DISK_TABLES   | 16772          | 40             |
| CREATED_TMP_TABLES        | 34336          | 63             |
| HANDLER_COMMIT            | 1109540769     | 0              |
| HANDLER_DELETE            | 12775993       | 0              |
| HANDLER_EXTERNAL_LOCK     | 2228108102     | 24             |
| HANDLER_PREPARE           | 2155764        | 0              |
| HANDLER_READ_FIRST        | 23586          | 9              |
| HANDLER_READ_KEY          | 18285349400    | 0              |
| HANDLER_READ_LAST         | 13000          | 0              |
| HANDLER_READ_NEXT         | 72142303428    | 0              |
| HANDLER_READ_PREV         | 3000146        | 0              |
| HANDLER_READ_RND          | 1261418742     | 156            |
| HANDLER_READ_RND_NEXT     | 12320861765    | 7845           |
| HANDLER_ROLLBACK          | 269376         | 0              |
| HANDLER_UPDATE            | 2596924399     | 0              |
| HANDLER_WRITE             | 8200421074     | 8241           |
| LAST_QUERY_COST           | 0.000000       | 21.399123      |
| LAST_QUERY_PARTIAL_PLANS  | 0              | 3              |
| OPENED_TABLE_DEFINITIONS  | 2482           | 0              |
| OPENED_TABLES             | 3619           | 0              |
| QUESTIONS                 | 1110214247     | 97             |
| SELECT_FULL_JOIN          | 615            | 9              |
| SELECT_RANGE              | 243635         | 0              |
| SELECT_SCAN               | 47851          | 53             |
| SLOW_QUERIES              | 29290          | 50             |
| SORT_MERGE_PASSES         | 6              | 0              |
| SORT_RANGE                | 179956         | 0              |
| SORT_ROWS                 | 321609927      | 156            |
| SORT_SCAN                 | 1829           | 39             |
| TABLE_OPEN_CACHE_HITS     | 1109365721     | 13             |
| TABLE_OPEN_CACHE_MISSES   | 1669           | 0              |
+---------------------------+----------------+----------------+
90 rows in set (0.03 sec)

mysql>

Look at four variables:

| BYTES_RECEIVED            | 123641576598   | 7757           |
| BYTES_SENT                | 149888451047   | 300001         |
| COM_ADMIN_COMMANDS        | 121915         | 0              |
| COM_SHOW_PROCESSLIST      | 488955         | 15             |

What does this tell you?

  • mysqld received 123,641,576,598 bytes (115.15GB) from all DB Connections since mysqld started
  • The session I ran the query with received 7,757 bytes (a little over 7K) in my current session
  • mysqld sent 149,888,451,047 bytes (139.59GB) from all DB Connections since mysqld started
  • The session I ran the query with sent 300,001 bytes (a little under 297K) in my current session
  • There have been 121,915 administrative commands that have run since mysqld started
  • There have benn 0 administrative commands that have run in my current session
  • The command SHOW PROCESSLIST has been run 488,955 times since mysqld started
  • The command SHOW PROCESSLIST has been run 15 times in my current session

You can compare the other 86 varibales and interpret them the same way.

Give it a Try !!!