Difference between show status and show global status in mysql

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_sho

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

StackExchange Database Administrators Q#54337, answer score: 15