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;
andSHOW 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 asSHOW 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:
Please note the output:
Look at four variables:
What does this tell you?
SHOW PROCESSLIST
has been run 488,955 times since mysqld startedSHOW PROCESSLIST
has been run 15 times in my current sessionYou can compare the other 86 varibales and interpret them the same way.
Give it a Try !!!