Difference between show status and show global status in mysql

Question

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.

Answer 1

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 !!!

• Hackerman: Excellent as always @Rolando
• piepi: Note for future googlers using MySQL 8.0 and beyond : The INFORMATION_SCHEMA tables are deprecated in preference to the Performance Schema tables and are removed in MySQL 8.0. Ref : dev.mysql.com/doc/refman/5.7/en/… . Replace information_schema with performance_schema for the query to work in MySQL 8.0 .

Answer 2

Show status provides server status information and this is shown for each status variable each variables has it's meaning and values.

Status variables are cumulative values i.e. it keeps on increasing w.r.t. Time and hence you will always see a growth in values for status variables.

You can reset status variable values by executing Flush status command

• ursitesion: You have just described "show status" command instead both. I am curious to know about which one is useful and which one we should use for DB optimization purposes?
• Mahesh Patil: There is no difference between "Show status" and "Show global status", earlier in v5.0 MySQL has introduced session level status by "Show session status" to get session level stats instead of global values but so many DBA's against it as it does not help in any manner
• ursitesion: If there is no difference then why values of so much parameters are different?
• Mahesh Patil: Have you executed both queries at same time? You can file a bug report to MySQL for this bugs.mysql.com
• ursitesion: I executed both queries between 5 seconds interval.