Have just read MySQL Global status difference using MySQL procedures / functions, by Andres Karlsson. Have commented, but realized I did not provide with a direct answer. In the comment, I suggested checking out a solution based on views, found in common_schema. But the solution in common_schema is split into two views, due to the fact views cannot handle derived tables subqueries.
Well, here’s a single query to do that: it checks GLOBAL_STATUS twice, 10 seconds apart in the following sample. It uses SLEEP() to actually wait between the two reads. Yes, you can do that with a query.
The following query shows all GLOBAL_STATUS values that have changed during the sample period.
[UPDATE] query updated to work with MySQL 5.6 optimizer
SELECT STRAIGHT_JOIN LOWER(gs0.VARIABLE_NAME) AS variable_name, gs0.VARIABLE_VALUE AS variable_value_0, gs1.VARIABLE_VALUE AS variable_value_1, (gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE) AS variable_value_diff, (gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE) / 10 AS variable_value_psec, (gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE) * 60 / 10 AS variable_value_pminute FROM ( SELECT VARIABLE_NAME, VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS UNION ALL SELECT '', SLEEP(10) FROM DUAL ) AS gs0 JOIN ( SELECT VARIABLE_NAME, VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS ) gs1 USING (VARIABLE_NAME) WHERE gs1.VARIABLE_VALUE != gs0.VARIABLE_VALUE ; +-----------------------------------+------------------+------------------+---------------------+---------------------+------------------------+ | variable_name | variable_value_0 | variable_value_1 | variable_value_diff | variable_value_psec | variable_value_pminute | +-----------------------------------+------------------+------------------+---------------------+---------------------+------------------------+ | aborted_clients | 2210669 | 2210686 | 17 | 1.7 | 102 | | bytes_received | 53259933210 | 53260211104 | 277894 | 27789.4 | 1667364 | | bytes_sent | 351130988015 | 351132884956 | 1896941 | 189694.1 | 11381646 | | com_change_db | 3760546 | 3760584 | 38 | 3.8 | 228 | | com_delete | 6774784 | 6774801 | 17 | 1.7 | 102 | | com_insert | 52743750 | 52744012 | 262 | 26.2 | 1572 | | com_insert_select | 13362650 | 13362740 | 90 | 9 | 540 | | com_select | 51722818 | 51723107 | 289 | 28.9 | 1734 | | com_set_option | 108564134 | 108564754 | 620 | 62 | 3720 | | com_show_collations | 3760530 | 3760568 | 38 | 3.8 | 228 | | com_show_processlist | 366078 | 366082 | 4 | 0.4 | 24 | | com_show_status | 366047 | 366051 | 4 | 0.4 | 24 | | com_show_variables | 3760535 | 3760573 | 38 | 3.8 | 228 | | com_update | 6271283 | 6271324 | 41 | 4.1 | 246 | | connections | 3781382 | 3781420 | 38 | 3.8 | 228 | | created_tmp_disk_tables | 983223 | 983224 | 1 | 0.1 | 6 | | created_tmp_tables | 9134044 | 9134126 | 82 | 8.2 | 492 | | handler_commit | 125798040 | 125798688 | 648 | 64.8 | 3888 | | handler_delete | 6849562 | 6849578 | 16 | 1.6 | 96 | | handler_read_first | 5332451 | 5332498 | 47 | 4.7 | 282 | | handler_read_key | 373910509 | 373912469 | 1960 | 196 | 11760 | | handler_read_next | 850122025 | 850170403 | 48378 | 4837.8 | 290268 | | handler_read_rnd | 255104660 | 255105932 | 1272 | 127.2 | 7632 | | handler_read_rnd_next | 992505444 | 992549948 | 44504 | 4450.4 | 267024 | | handler_update | 27930283 | 27930465 | 182 | 18.2 | 1092 | | handler_write | 2051582925 | 2051602416 | 19491 | 1949.1 | 116946 | | innodb_buffer_pool_pages_data | 77232 | 77243 | 11 | 1.1 | 66 | | innodb_buffer_pool_pages_dirty | 626 | 645 | 19 | 1.9 | 114 | | innodb_buffer_pool_pages_flushed | 38429812 | 38430032 | 220 | 22 | 1320 | | innodb_buffer_pool_pages_misc | 4294922063 | 4294922052 | -11 | -1.1 | -66 | | innodb_buffer_pool_read_requests | 1933796064 | 1933871603 | 75539 | 7553.9 | 453234 | | innodb_buffer_pool_reads | 11360212 | 11360214 | 2 | 0.2 | 12 | | innodb_buffer_pool_write_requests | 1074109722 | 1074115394 | 5672 | 567.2 | 34032 | | innodb_data_fsyncs | 5583880 | 5583905 | 25 | 2.5 | 150 | | innodb_data_read | 3339489280 | 3339501568 | 12288 | 1228.8 | 73728 | | innodb_data_reads | 11796492 | 11796494 | 2 | 0.2 | 12 | | innodb_data_writes | 105587582 | 105588145 | 563 | 56.3 | 3378 | | innodb_data_written | 3721600000 | 3727315968 | 5715968 | 571596.8 | 34295808 | | innodb_dblwr_pages_written | 38429812 | 38430032 | 220 | 22 | 1320 | | innodb_dblwr_writes | 596503 | 596506 | 3 | 0.3 | 18 | | innodb_log_write_requests | 380978894 | 380981368 | 2474 | 247.4 | 14844 | | innodb_log_writes | 74407604 | 74407990 | 386 | 38.6 | 2316 | | innodb_os_log_fsyncs | 2310799 | 2310807 | 8 | 0.8 | 48 | | innodb_os_log_written | 2905292800 | 2906502656 | 1209856 | 120985.6 | 7259136 | | innodb_pages_created | 1341584 | 1341593 | 9 | 0.9 | 54 | | innodb_pages_read | 13117652 | 13117654 | 2 | 0.2 | 12 | | innodb_pages_written | 38429812 | 38430032 | 220 | 22 | 1320 | | innodb_rows_deleted | 6849552 | 6849568 | 16 | 1.6 | 96 | | innodb_rows_inserted | 43787980 | 43788207 | 227 | 22.7 | 1362 | | innodb_rows_read | 4289845136 | 4289919560 | 74424 | 7442.4 | 446544 | | innodb_rows_updated | 24119627 | 24119809 | 182 | 18.2 | 1092 | | key_read_requests | 41262330 | 41262338 | 8 | 0.8 | 48 | | open_files | 7 | 5 | -2 | -0.2 | -12 | | opened_files | 4212920 | 4212924 | 4 | 0.4 | 24 | | questions | 253158874 | 253160331 | 1457 | 145.7 | 8742 | | select_full_join | 546 | 547 | 1 | 0.1 | 6 | | select_range | 721945 | 721947 | 2 | 0.2 | 12 | | select_scan | 12828865 | 12828989 | 124 | 12.4 | 744 | | sort_range | 170971 | 170973 | 2 | 0.2 | 12 | | sort_rows | 255175383 | 255176655 | 1272 | 127.2 | 7632 | | sort_scan | 534078 | 534080 | 2 | 0.2 | 12 | | table_locks_immediate | 142673687 | 142674454 | 767 | 76.7 | 4602 | | threads_cached | 7 | 8 | 1 | 0.1 | 6 | | threads_connected | 5 | 10 | 5 | 0.5 | 30 | | threads_created | 840486 | 840509 | 23 | 2.3 | 138 | +-----------------------------------+------------------+------------------+---------------------+---------------------+------------------------+
Some values don’t make sense to do difference on (e.g. threads_connected), since they present with momentary status and are not incrementing as others (e.g. threads_created).
Happy SQLing!
Schlomi,
Is there an updated script?
I am seeing smaller gs1 values than gs0 on most values. HANDLER_READ_KEY is a key example.
MYSQL v 5.6.17
Thanks,
Wilson
If you need it, my testing results follow, containing
SHOW GLOBAL STATUS before, run the script, SHOW GLOBAL STATUS after.
“mysql -u … -p… <querytest.bat"
# trackglobalstatusvariables.sql 01/09/2015 wlh
select VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.global_status ORDER BY VARIABLE_NAME;
VARIABLE_NAME VARIABLE_VALUE
ABORTED_CLIENTS 0
ABORTED_CONNECTS 0
BINLOG_CACHE_DISK_USE 0
BINLOG_CACHE_USE 0
BINLOG_STMT_CACHE_DISK_USE 0
BINLOG_STMT_CACHE_USE 0
BYTES_RECEIVED 13408914
BYTES_SENT 197931947
COMPRESSION OFF
COM_ADMIN_COMMANDS 353
COM_ALTER_DB 0
COM_ALTER_DB_UPGRADE 0
COM_ALTER_EVENT 0
COM_ALTER_FUNCTION 0
COM_ALTER_PROCEDURE 0
COM_ALTER_SERVER 0
COM_ALTER_TABLE 0
COM_ALTER_TABLESPACE 0
COM_ALTER_USER 0
COM_ANALYZE 297
COM_ASSIGN_TO_KEYCACHE 0
COM_BEGIN 7
COM_BINLOG 0
COM_CALL_PROCEDURE 3
COM_CHANGE_DB 453
COM_CHANGE_MASTER 0
COM_CHECK 0
COM_CHECKSUM 0
COM_COMMIT 6
COM_CREATE_DB 0
COM_CREATE_EVENT 0
COM_CREATE_FUNCTION 0
COM_CREATE_INDEX 0
COM_CREATE_PROCEDURE 0
COM_CREATE_SERVER 0
COM_CREATE_TABLE 2
COM_CREATE_TRIGGER 0
COM_CREATE_UDF 0
COM_CREATE_USER 0
COM_CREATE_VIEW 0
COM_DEALLOC_SQL 0
COM_DELETE 100
COM_DELETE_MULTI 0
COM_DO 0
COM_DROP_DB 0
COM_DROP_EVENT 0
COM_DROP_FUNCTION 0
COM_DROP_INDEX 0
COM_DROP_PROCEDURE 0
COM_DROP_SERVER 0
COM_DROP_TABLE 4
COM_DROP_TRIGGER 0
COM_DROP_USER 0
COM_DROP_VIEW 0
COM_EMPTY_QUERY 0
COM_EXECUTE_SQL 0
COM_FLUSH 2
COM_GET_DIAGNOSTICS 0
COM_GRANT 0
COM_HA_CLOSE 0
COM_HA_OPEN 0
COM_HA_READ 0
COM_HELP 0
COM_INSERT 156
COM_INSERT_SELECT 2
COM_INSTALL_PLUGIN 0
COM_KILL 0
COM_LOAD 0
COM_LOCK_TABLES 0
COM_OPTIMIZE 297
COM_PRELOAD_KEYS 0
COM_PREPARE_SQL 1
COM_PURGE 0
COM_PURGE_BEFORE_DATE 0
COM_RELEASE_SAVEPOINT 0
COM_RENAME_TABLE 2
COM_RENAME_USER 0
COM_REPAIR 352
COM_REPLACE 1
COM_REPLACE_SELECT 0
COM_RESET 1
COM_RESIGNAL 0
COM_REVOKE 0
COM_REVOKE_ALL 0
COM_ROLLBACK 0
COM_ROLLBACK_TO_SAVEPOINT 0
COM_SAVEPOINT 0
COM_SELECT 42871
COM_SET_OPTION 921
COM_SHOW_BINLOGS 2
COM_SHOW_BINLOG_EVENTS 0
COM_SHOW_CHARSETS 0
COM_SHOW_COLLATIONS 0
COM_SHOW_CREATE_DB 0
COM_SHOW_CREATE_EVENT 0
COM_SHOW_CREATE_FUNC 0
COM_SHOW_CREATE_PROC 0
COM_SHOW_CREATE_TABLE 1
COM_SHOW_CREATE_TRIGGER 0
COM_SHOW_DATABASES 0
COM_SHOW_ENGINE_LOGS 0
COM_SHOW_ENGINE_MUTEX 0
COM_SHOW_ENGINE_STATUS 0
COM_SHOW_ERRORS 0
COM_SHOW_EVENTS 0
COM_SHOW_FIELDS 1464
COM_SHOW_FUNCTION_CODE 0
COM_SHOW_FUNCTION_STATUS 0
COM_SHOW_GRANTS 1
COM_SHOW_KEYS 11
COM_SHOW_MASTER_STATUS 2
COM_SHOW_OPEN_TABLES 0
COM_SHOW_PLUGINS 0
COM_SHOW_PRIVILEGES 0
COM_SHOW_PROCEDURE_CODE 0
COM_SHOW_PROCEDURE_STATUS 0
COM_SHOW_PROCESSLIST 7
COM_SHOW_PROFILE 0
COM_SHOW_PROFILES 0
COM_SHOW_RELAYLOG_EVENTS 0
COM_SHOW_SLAVE_HOSTS 0
COM_SHOW_SLAVE_STATUS 2
COM_SHOW_STATUS 14
COM_SHOW_STORAGE_ENGINES 0
COM_SHOW_TABLES 20
COM_SHOW_TABLE_STATUS 0
COM_SHOW_TRIGGERS 0
COM_SHOW_VARIABLES 11
COM_SHOW_WARNINGS 0
COM_SIGNAL 0
COM_SLAVE_START 0
COM_SLAVE_STOP 0
COM_STMT_CLOSE 0
COM_STMT_EXECUTE 0
COM_STMT_FETCH 0
COM_STMT_PREPARE 1
COM_STMT_REPREPARE 0
COM_STMT_RESET 0
COM_STMT_SEND_LONG_DATA 0
COM_TRUNCATE 1
COM_UNINSTALL_PLUGIN 0
COM_UNLOCK_TABLES 0
COM_UPDATE 295
COM_UPDATE_MULTI 2452
COM_XA_COMMIT 0
COM_XA_END 0
COM_XA_PREPARE 0
COM_XA_RECOVER 0
COM_XA_ROLLBACK 0
COM_XA_START 0
CONNECTIONS 3027
CONNECTION_ERRORS_ACCEPT 0
CONNECTION_ERRORS_INTERNAL 0
CONNECTION_ERRORS_MAX_CONNECTIONS 0
CONNECTION_ERRORS_PEER_ADDRESS 0
CONNECTION_ERRORS_SELECT 0
CONNECTION_ERRORS_TCPWRAP 0
CREATED_TMP_DISK_TABLES 2993
CREATED_TMP_FILES 1095
CREATED_TMP_TABLES 11864
DELAYED_ERRORS 0
DELAYED_INSERT_THREADS 0
DELAYED_WRITES 0
FLUSH_COMMANDS 2
HANDLER_COMMIT 17579
HANDLER_DELETE 1
HANDLER_DISCOVER 0
HANDLER_EXTERNAL_LOCK 176762
HANDLER_MRR_INIT 0
HANDLER_PREPARE 0
HANDLER_READ_FIRST 52491
HANDLER_READ_KEY 37895073
HANDLER_READ_LAST 7
HANDLER_READ_NEXT 49975990
HANDLER_READ_PREV 2970
HANDLER_READ_RND 1160121
HANDLER_READ_RND_NEXT 87132198
HANDLER_ROLLBACK 6
HANDLER_SAVEPOINT 0
HANDLER_SAVEPOINT_ROLLBACK 0
HANDLER_UPDATE 14527
HANDLER_WRITE 4002309
INNODB_AVAILABLE_UNDO_LOGS 128
INNODB_BUFFER_POOL_BYTES_DATA 331300864
INNODB_BUFFER_POOL_BYTES_DIRTY 262144
INNODB_BUFFER_POOL_DUMP_STATUS not started
INNODB_BUFFER_POOL_LOAD_STATUS Buffer pool(s) load completed at 150109 2:01:41
INNODB_BUFFER_POOL_PAGES_DATA 20221
INNODB_BUFFER_POOL_PAGES_DIRTY 16
INNODB_BUFFER_POOL_PAGES_FLUSHED 12244
INNODB_BUFFER_POOL_PAGES_FREE 110600
INNODB_BUFFER_POOL_PAGES_MISC 251
INNODB_BUFFER_POOL_PAGES_TOTAL 131072
INNODB_BUFFER_POOL_READS 10615
INNODB_BUFFER_POOL_READ_AHEAD 289
INNODB_BUFFER_POOL_READ_AHEAD_EVICTED 0
INNODB_BUFFER_POOL_READ_AHEAD_RND 226
INNODB_BUFFER_POOL_READ_REQUESTS 277812886
INNODB_BUFFER_POOL_WAIT_FREE 0
INNODB_BUFFER_POOL_WRITE_REQUESTS 1256008
INNODB_DATA_FSYNCS 5838
INNODB_DATA_PENDING_FSYNCS 0
INNODB_DATA_PENDING_READS 0
INNODB_DATA_PENDING_WRITES 0
INNODB_DATA_READ 178720768
INNODB_DATA_READS 12220
INNODB_DATA_WRITES 17718
INNODB_DATA_WRITTEN 563079168
INNODB_DBLWR_PAGES_WRITTEN 12244
INNODB_DBLWR_WRITES 922
INNODB_HAVE_ATOMIC_BUILTINS ON
INNODB_LOG_WAITS 0
INNODB_LOG_WRITES 518
INNODB_LOG_WRITE_REQUESTS 427331
INNODB_NUM_OPEN_FILES 261
INNODB_OS_LOG_FSYNCS 632
INNODB_OS_LOG_PENDING_FSYNCS 0
INNODB_OS_LOG_PENDING_WRITES 0
INNODB_OS_LOG_WRITTEN 161802752
INNODB_PAGES_CREATED 9368
INNODB_PAGES_READ 10903
INNODB_PAGES_WRITTEN 12244
INNODB_PAGE_SIZE 16384
INNODB_ROWS_DELETED 1
INNODB_ROWS_INSERTED 7919
INNODB_ROWS_READ 137973193
INNODB_ROWS_UPDATED 8147
INNODB_ROW_LOCK_CURRENT_WAITS 0
INNODB_ROW_LOCK_TIME 0
INNODB_ROW_LOCK_TIME_AVG 0
INNODB_ROW_LOCK_TIME_MAX 0
INNODB_ROW_LOCK_WAITS 0
INNODB_TRUNCATED_STATUS_WRITES 0
KEY_BLOCKS_NOT_FLUSHED 5
KEY_BLOCKS_UNUSED 3817
KEY_BLOCKS_USED 175
KEY_READS 5888
KEY_READ_REQUESTS 2573951
KEY_WRITES 3
KEY_WRITE_REQUESTS 1278818
LAST_QUERY_COST 0.000000
LAST_QUERY_PARTIAL_PLANS 0
MAX_USED_CONNECTIONS 13
NOT_FLUSHED_DELAYED_ROWS 0
OPENED_FILES 16812
OPENED_TABLES 4234
OPENED_TABLE_DEFINITIONS 2383
OPEN_FILES 146
OPEN_STREAMS 0
OPEN_TABLES 586
OPEN_TABLE_DEFINITIONS 508
PERFORMANCE_SCHEMA_ACCOUNTS_LOST 0
PERFORMANCE_SCHEMA_COND_CLASSES_LOST 0
PERFORMANCE_SCHEMA_COND_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_DIGEST_LOST 0
PERFORMANCE_SCHEMA_FILE_CLASSES_LOST 0
PERFORMANCE_SCHEMA_FILE_HANDLES_LOST 0
PERFORMANCE_SCHEMA_FILE_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_HOSTS_LOST 0
PERFORMANCE_SCHEMA_LOCKER_LOST 0
PERFORMANCE_SCHEMA_MUTEX_CLASSES_LOST 0
PERFORMANCE_SCHEMA_MUTEX_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_RWLOCK_CLASSES_LOST 0
PERFORMANCE_SCHEMA_RWLOCK_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_SESSION_CONNECT_ATTRS_LOST 0
PERFORMANCE_SCHEMA_SOCKET_CLASSES_LOST 0
PERFORMANCE_SCHEMA_SOCKET_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_STAGE_CLASSES_LOST 0
PERFORMANCE_SCHEMA_STATEMENT_CLASSES_LOST 0
PERFORMANCE_SCHEMA_TABLE_HANDLES_LOST 0
PERFORMANCE_SCHEMA_TABLE_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_THREAD_CLASSES_LOST 0
PERFORMANCE_SCHEMA_THREAD_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_USERS_LOST 0
PREPARED_STMT_COUNT 0
QCACHE_FREE_BLOCKS 2
QCACHE_FREE_MEMORY 185662760
QCACHE_HITS 40382
QCACHE_INSERTS 10689
QCACHE_LOWMEM_PRUNES 0
QCACHE_NOT_CACHED 2362
QCACHE_QUERIES_IN_CACHE 5081
QCACHE_TOTAL_BLOCKS 10359
QUERIES 135745
QUESTIONS 58488
SELECT_FULL_JOIN 147
SELECT_FULL_RANGE_JOIN 0
SELECT_RANGE 841
SELECT_RANGE_CHECK 456
SELECT_SCAN 7251
SLAVE_HEARTBEAT_PERIOD 0.000
SLAVE_LAST_HEARTBEAT
SLAVE_OPEN_TEMP_TABLES 0
SLAVE_RECEIVED_HEARTBEATS 0
SLAVE_RETRIED_TRANSACTIONS 0
SLAVE_RUNNING OFF
SLOW_LAUNCH_THREADS 0
SLOW_QUERIES 2
SORT_MERGE_PASSES 0
SORT_RANGE 1609
SORT_ROWS 1130883
SORT_SCAN 1585
SSL_ACCEPTS 0
SSL_ACCEPT_RENEGOTIATES 0
SSL_CALLBACK_CACHE_HITS 0
SSL_CIPHER
SSL_CIPHER_LIST
SSL_CLIENT_CONNECTS 0
SSL_CONNECT_RENEGOTIATES 0
SSL_CTX_VERIFY_DEPTH 0
SSL_CTX_VERIFY_MODE 0
SSL_DEFAULT_TIMEOUT 0
SSL_FINISHED_ACCEPTS 0
SSL_FINISHED_CONNECTS 0
SSL_SERVER_NOT_AFTER
SSL_SERVER_NOT_BEFORE
SSL_SESSIONS_REUSED 0
SSL_SESSION_CACHE_HITS 0
SSL_SESSION_CACHE_MISSES 0
SSL_SESSION_CACHE_MODE NONE
SSL_SESSION_CACHE_OVERFLOWS 0
SSL_SESSION_CACHE_SIZE 0
SSL_SESSION_CACHE_TIMEOUTS 0
SSL_USED_SESSION_CACHE_ENTRIES 0
SSL_VERIFY_DEPTH 0
SSL_VERIFY_MODE 0
SSL_VERSION
TABLE_LOCKS_IMMEDIATE 63083
TABLE_LOCKS_WAITED 0
TABLE_OPEN_CACHE_HITS 77287
TABLE_OPEN_CACHE_MISSES 2095
TABLE_OPEN_CACHE_OVERFLOWS 0
TC_LOG_MAX_PAGES_USED 0
TC_LOG_PAGE_SIZE 0
TC_LOG_PAGE_WAITS 0
THREADS_CACHED 8
THREADS_CONNECTED 5
THREADS_CREATED 13
THREADS_RUNNING 3
UPTIME 24524
UPTIME_SINCE_FLUSH_STATUS 24524
# 2014 12 17 from
# http://code.openark.org/blog/mysql/mysql-global-status-difference-using-single-query
# by Shlomi Noach
SELECT STRAIGHT_JOIN
LOWER(gs0.VARIABLE_NAME) AS variable_name,
gs0.VARIABLE_VALUE AS variable_value_0,
gs1.VARIABLE_VALUE AS variable_value_1,
(gs1.VARIABLE_VALUE – gs0.VARIABLE_VALUE) AS variable_value_diff,
(gs1.VARIABLE_VALUE – gs0.VARIABLE_VALUE) / 10 AS variable_value_psec,
(gs1.VARIABLE_VALUE – gs0.VARIABLE_VALUE) * 60 / 10 AS
variable_value_pminute
FROM
(
SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM
INFORMATION_SCHEMA.GLOBAL_STATUS
UNION ALL
SELECT
'',
SLEEP(10)
FROM DUAL
) AS gs0
JOIN INFORMATION_SCHEMA.GLOBAL_STATUS gs1 USING (VARIABLE_NAME)
WHERE
gs1.VARIABLE_VALUE != gs0.VARIABLE_VALUE
;variable_name variable_value_0 variable_value_1 variable_value_diff variable_value_psec variable_value_pminute
handler_read_key 37899565 37899472 -93 -9.3 -558
handler_read_next 49981999 49981864 -135 -13.5 -810
handler_read_rnd_next 87132729 87132726 -3 -0.3 -18
handler_write 4003180 4002836 -344 -34.4 -2064
innodb_buffer_pool_read_requests 277840496 277840009 -487 -48.7 -2922
innodb_rows_read 137979561 137979448 -113 -11.3 -678
key_read_requests 2574333 2574323 -10 -1 -60
key_write_requests 1279015 1279010 -5 -0.5 -30
# trackglobalstatusvariables.sql 01/09/2015 wlh
select VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.global_status ORDER BY VARIABLE_NAME;
VARIABLE_NAME VARIABLE_VALUE
ABORTED_CLIENTS 0
ABORTED_CONNECTS 0
BINLOG_CACHE_DISK_USE 0
BINLOG_CACHE_USE 0
BINLOG_STMT_CACHE_DISK_USE 0
BINLOG_STMT_CACHE_USE 0
BYTES_RECEIVED 13436284
BYTES_SENT 198118269
COMPRESSION OFF
COM_ADMIN_COMMANDS 353
COM_ALTER_DB 0
COM_ALTER_DB_UPGRADE 0
COM_ALTER_EVENT 0
COM_ALTER_FUNCTION 0
COM_ALTER_PROCEDURE 0
COM_ALTER_SERVER 0
COM_ALTER_TABLE 0
COM_ALTER_TABLESPACE 0
COM_ALTER_USER 0
COM_ANALYZE 297
COM_ASSIGN_TO_KEYCACHE 0
COM_BEGIN 7
COM_BINLOG 0
COM_CALL_PROCEDURE 3
COM_CHANGE_DB 453
COM_CHANGE_MASTER 0
COM_CHECK 0
COM_CHECKSUM 0
COM_COMMIT 6
COM_CREATE_DB 0
COM_CREATE_EVENT 0
COM_CREATE_FUNCTION 0
COM_CREATE_INDEX 0
COM_CREATE_PROCEDURE 0
COM_CREATE_SERVER 0
COM_CREATE_TABLE 2
COM_CREATE_TRIGGER 0
COM_CREATE_UDF 0
COM_CREATE_USER 0
COM_CREATE_VIEW 0
COM_DEALLOC_SQL 0
COM_DELETE 100
COM_DELETE_MULTI 0
COM_DO 0
COM_DROP_DB 0
COM_DROP_EVENT 0
COM_DROP_FUNCTION 0
COM_DROP_INDEX 0
COM_DROP_PROCEDURE 0
COM_DROP_SERVER 0
COM_DROP_TABLE 4
COM_DROP_TRIGGER 0
COM_DROP_USER 0
COM_DROP_VIEW 0
COM_EMPTY_QUERY 0
COM_EXECUTE_SQL 0
COM_FLUSH 2
COM_GET_DIAGNOSTICS 0
COM_GRANT 0
COM_HA_CLOSE 0
COM_HA_OPEN 0
COM_HA_READ 0
COM_HELP 0
COM_INSERT 156
COM_INSERT_SELECT 2
COM_INSTALL_PLUGIN 0
COM_KILL 0
COM_LOAD 0
COM_LOCK_TABLES 0
COM_OPTIMIZE 297
COM_PRELOAD_KEYS 0
COM_PREPARE_SQL 1
COM_PURGE 0
COM_PURGE_BEFORE_DATE 0
COM_RELEASE_SAVEPOINT 0
COM_RENAME_TABLE 2
COM_RENAME_USER 0
COM_REPAIR 352
COM_REPLACE 1
COM_REPLACE_SELECT 0
COM_RESET 1
COM_RESIGNAL 0
COM_REVOKE 0
COM_REVOKE_ALL 0
COM_ROLLBACK 0
COM_ROLLBACK_TO_SAVEPOINT 0
COM_SAVEPOINT 0
COM_SELECT 43017
COM_SET_OPTION 921
COM_SHOW_BINLOGS 2
COM_SHOW_BINLOG_EVENTS 0
COM_SHOW_CHARSETS 0
COM_SHOW_COLLATIONS 0
COM_SHOW_CREATE_DB 0
COM_SHOW_CREATE_EVENT 0
COM_SHOW_CREATE_FUNC 0
COM_SHOW_CREATE_PROC 0
COM_SHOW_CREATE_TABLE 1
COM_SHOW_CREATE_TRIGGER 0
COM_SHOW_DATABASES 0
COM_SHOW_ENGINE_LOGS 0
COM_SHOW_ENGINE_MUTEX 0
COM_SHOW_ENGINE_STATUS 0
COM_SHOW_ERRORS 0
COM_SHOW_EVENTS 0
COM_SHOW_FIELDS 1464
COM_SHOW_FUNCTION_CODE 0
COM_SHOW_FUNCTION_STATUS 0
COM_SHOW_GRANTS 1
COM_SHOW_KEYS 11
COM_SHOW_MASTER_STATUS 2
COM_SHOW_OPEN_TABLES 0
COM_SHOW_PLUGINS 0
COM_SHOW_PRIVILEGES 0
COM_SHOW_PROCEDURE_CODE 0
COM_SHOW_PROCEDURE_STATUS 0
COM_SHOW_PROCESSLIST 7
COM_SHOW_PROFILE 0
COM_SHOW_PROFILES 0
COM_SHOW_RELAYLOG_EVENTS 0
COM_SHOW_SLAVE_HOSTS 0
COM_SHOW_SLAVE_STATUS 2
COM_SHOW_STATUS 14
COM_SHOW_STORAGE_ENGINES 0
COM_SHOW_TABLES 20
COM_SHOW_TABLE_STATUS 0
COM_SHOW_TRIGGERS 0
COM_SHOW_VARIABLES 11
COM_SHOW_WARNINGS 0
COM_SIGNAL 0
COM_SLAVE_START 0
COM_SLAVE_STOP 0
COM_STMT_CLOSE 0
COM_STMT_EXECUTE 0
COM_STMT_FETCH 0
COM_STMT_PREPARE 1
COM_STMT_REPREPARE 0
COM_STMT_RESET 0
COM_STMT_SEND_LONG_DATA 0
COM_TRUNCATE 1
COM_UNINSTALL_PLUGIN 0
COM_UNLOCK_TABLES 0
COM_UPDATE 295
COM_UPDATE_MULTI 2453
COM_XA_COMMIT 0
COM_XA_END 0
COM_XA_PREPARE 0
COM_XA_RECOVER 0
COM_XA_ROLLBACK 0
COM_XA_START 0
CONNECTIONS 3030
CONNECTION_ERRORS_ACCEPT 0
CONNECTION_ERRORS_INTERNAL 0
CONNECTION_ERRORS_MAX_CONNECTIONS 0
CONNECTION_ERRORS_PEER_ADDRESS 0
CONNECTION_ERRORS_SELECT 0
CONNECTION_ERRORS_TCPWRAP 0
CREATED_TMP_DISK_TABLES 3023
CREATED_TMP_FILES 1095
CREATED_TMP_TABLES 11954
DELAYED_ERRORS 0
DELAYED_INSERT_THREADS 0
DELAYED_WRITES 0
FLUSH_COMMANDS 2
HANDLER_COMMIT 17721
HANDLER_DELETE 1
HANDLER_DISCOVER 0
HANDLER_EXTERNAL_LOCK 178392
HANDLER_MRR_INIT 0
HANDLER_PREPARE 0
HANDLER_READ_FIRST 52548
HANDLER_READ_KEY 39333513
HANDLER_READ_LAST 7
HANDLER_READ_NEXT 51893735
HANDLER_READ_PREV 2970
HANDLER_READ_RND 1188763
HANDLER_READ_RND_NEXT 87310306
HANDLER_ROLLBACK 6
HANDLER_SAVEPOINT 0
HANDLER_SAVEPOINT_ROLLBACK 0
HANDLER_UPDATE 14528
HANDLER_WRITE 4107667
INNODB_AVAILABLE_UNDO_LOGS 128
INNODB_BUFFER_POOL_BYTES_DATA 331300864
INNODB_BUFFER_POOL_BYTES_DIRTY 262144
INNODB_BUFFER_POOL_DUMP_STATUS not started
INNODB_BUFFER_POOL_LOAD_STATUS Buffer pool(s) load completed at 150109 2:01:41
INNODB_BUFFER_POOL_PAGES_DATA 20221
INNODB_BUFFER_POOL_PAGES_DIRTY 16
INNODB_BUFFER_POOL_PAGES_FLUSHED 12244
INNODB_BUFFER_POOL_PAGES_FREE 110600
INNODB_BUFFER_POOL_PAGES_MISC 251
INNODB_BUFFER_POOL_PAGES_TOTAL 131072
INNODB_BUFFER_POOL_READS 10615
INNODB_BUFFER_POOL_READ_AHEAD 289
INNODB_BUFFER_POOL_READ_AHEAD_EVICTED 0
INNODB_BUFFER_POOL_READ_AHEAD_RND 226
INNODB_BUFFER_POOL_READ_REQUESTS 286869234
INNODB_BUFFER_POOL_WAIT_FREE 0
INNODB_BUFFER_POOL_WRITE_REQUESTS 1256009
INNODB_DATA_FSYNCS 5839
INNODB_DATA_PENDING_FSYNCS 0
INNODB_DATA_PENDING_READS 0
INNODB_DATA_PENDING_WRITES 0
INNODB_DATA_READ 178720768
INNODB_DATA_READS 12220
INNODB_DATA_WRITES 17719
INNODB_DATA_WRITTEN 563079680
INNODB_DBLWR_PAGES_WRITTEN 12244
INNODB_DBLWR_WRITES 922
INNODB_HAVE_ATOMIC_BUILTINS ON
INNODB_LOG_WAITS 0
INNODB_LOG_WRITES 519
INNODB_LOG_WRITE_REQUESTS 427331
INNODB_NUM_OPEN_FILES 261
INNODB_OS_LOG_FSYNCS 633
INNODB_OS_LOG_PENDING_FSYNCS 0
INNODB_OS_LOG_PENDING_WRITES 0
INNODB_OS_LOG_WRITTEN 161803264
INNODB_PAGES_CREATED 9368
INNODB_PAGES_READ 10903
INNODB_PAGES_WRITTEN 12244
INNODB_PAGE_SIZE 16384
INNODB_ROWS_DELETED 1
INNODB_ROWS_INSERTED 7919
INNODB_ROWS_READ 140088931
INNODB_ROWS_UPDATED 8147
INNODB_ROW_LOCK_CURRENT_WAITS 0
INNODB_ROW_LOCK_TIME 0
INNODB_ROW_LOCK_TIME_AVG 0
INNODB_ROW_LOCK_TIME_MAX 0
INNODB_ROW_LOCK_WAITS 0
INNODB_TRUNCATED_STATUS_WRITES 0
KEY_BLOCKS_NOT_FLUSHED 6
KEY_BLOCKS_UNUSED 3816
KEY_BLOCKS_USED 175
KEY_READS 6085
KEY_READ_REQUESTS 2666521
KEY_WRITES 3
KEY_WRITE_REQUESTS 1327406
LAST_QUERY_COST 0.000000
LAST_QUERY_PARTIAL_PLANS 0
MAX_USED_CONNECTIONS 13
NOT_FLUSHED_DELAYED_ROWS 0
OPENED_FILES 16932
OPENED_TABLES 4234
OPENED_TABLE_DEFINITIONS 2383
OPEN_FILES 146
OPEN_STREAMS 0
OPEN_TABLES 586
OPEN_TABLE_DEFINITIONS 508
PERFORMANCE_SCHEMA_ACCOUNTS_LOST 0
PERFORMANCE_SCHEMA_COND_CLASSES_LOST 0
PERFORMANCE_SCHEMA_COND_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_DIGEST_LOST 0
PERFORMANCE_SCHEMA_FILE_CLASSES_LOST 0
PERFORMANCE_SCHEMA_FILE_HANDLES_LOST 0
PERFORMANCE_SCHEMA_FILE_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_HOSTS_LOST 0
PERFORMANCE_SCHEMA_LOCKER_LOST 0
PERFORMANCE_SCHEMA_MUTEX_CLASSES_LOST 0
PERFORMANCE_SCHEMA_MUTEX_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_RWLOCK_CLASSES_LOST 0
PERFORMANCE_SCHEMA_RWLOCK_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_SESSION_CONNECT_ATTRS_LOST 0
PERFORMANCE_SCHEMA_SOCKET_CLASSES_LOST 0
PERFORMANCE_SCHEMA_SOCKET_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_STAGE_CLASSES_LOST 0
PERFORMANCE_SCHEMA_STATEMENT_CLASSES_LOST 0
PERFORMANCE_SCHEMA_TABLE_HANDLES_LOST 0
PERFORMANCE_SCHEMA_TABLE_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_THREAD_CLASSES_LOST 0
PERFORMANCE_SCHEMA_THREAD_INSTANCES_LOST 0
PERFORMANCE_SCHEMA_USERS_LOST 0
PREPARED_STMT_COUNT 0
QCACHE_FREE_BLOCKS 1
QCACHE_FREE_MEMORY 185359552
QCACHE_HITS 40382
QCACHE_INSERTS 10802
QCACHE_LOWMEM_PRUNES 0
QCACHE_NOT_CACHED 2395
QCACHE_QUERIES_IN_CACHE 5194
QCACHE_TOTAL_BLOCKS 10584
QUERIES 135894
QUESTIONS 58636
SELECT_FULL_JOIN 148
SELECT_FULL_RANGE_JOIN 0
SELECT_RANGE 853
SELECT_RANGE_CHECK 456
SELECT_SCAN 7312
SLAVE_HEARTBEAT_PERIOD 0.000
SLAVE_LAST_HEARTBEAT
SLAVE_OPEN_TEMP_TABLES 0
SLAVE_RECEIVED_HEARTBEATS 0
SLAVE_RETRIED_TRANSACTIONS 0
SLAVE_RUNNING OFF
SLOW_LAUNCH_THREADS 0
SLOW_QUERIES 3
SORT_MERGE_PASSES 0
SORT_RANGE 1637
SORT_ROWS 1159056
SORT_SCAN 1614
SSL_ACCEPTS 0
SSL_ACCEPT_RENEGOTIATES 0
SSL_CALLBACK_CACHE_HITS 0
SSL_CIPHER
SSL_CIPHER_LIST
SSL_CLIENT_CONNECTS 0
SSL_CONNECT_RENEGOTIATES 0
SSL_CTX_VERIFY_DEPTH 0
SSL_CTX_VERIFY_MODE 0
SSL_DEFAULT_TIMEOUT 0
SSL_FINISHED_ACCEPTS 0
SSL_FINISHED_CONNECTS 0
SSL_SERVER_NOT_AFTER
SSL_SERVER_NOT_BEFORE
SSL_SESSIONS_REUSED 0
SSL_SESSION_CACHE_HITS 0
SSL_SESSION_CACHE_MISSES 0
SSL_SESSION_CACHE_MODE NONE
SSL_SESSION_CACHE_OVERFLOWS 0
SSL_SESSION_CACHE_SIZE 0
SSL_SESSION_CACHE_TIMEOUTS 0
SSL_USED_SESSION_CACHE_ENTRIES 0
SSL_VERIFY_DEPTH 0
SSL_VERIFY_MODE 0
SSL_VERSION
TABLE_LOCKS_IMMEDIATE 63874
TABLE_LOCKS_WAITED 0
TABLE_OPEN_CACHE_HITS 78078
TABLE_OPEN_CACHE_MISSES 2095
TABLE_OPEN_CACHE_OVERFLOWS 0
TC_LOG_MAX_PAGES_USED 0
TC_LOG_PAGE_SIZE 0
TC_LOG_PAGE_WAITS 0
THREADS_CACHED 8
THREADS_CONNECTED 5
THREADS_CREATED 13
THREADS_RUNNING 3
UPTIME 24534
UPTIME_SINCE_FLUSH_STATUS 24534
Here’s the updated query, also updated within text:
SELECT STRAIGHT_JOIN LOWER(gs0.VARIABLE_NAME) AS variable_name, gs0.VARIABLE_VALUE AS variable_value_0, gs1.VARIABLE_VALUE AS variable_value_1, (gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE) AS variable_value_diff, (gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE) / 10 AS variable_value_psec, (gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE) * 60 / 10 AS variable_value_pminute FROM ( SELECT VARIABLE_NAME, VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS UNION ALL SELECT '', SLEEP(10) FROM DUAL ) AS gs0 JOIN ( SELECT VARIABLE_NAME, VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS ) gs1 USING (VARIABLE_NAME) WHERE gs1.VARIABLE_VALUE != gs0.VARIABLE_VALUE ;Shlomi, Should this query work with Maria DB 10.2.17 cpu has 6 cores. Results observed looks like this in my version.
+————————-+————-+————-+———-+———+———–+———————+
| variable_name | begin | end | diff | psec | pminute | NOW() |
+————————-+————-+————-+———-+———+———–+———————+
| created_tmp_disk_tables | 1237840 | 1237839 | -1 | -0 | -6 | 2018-11-06 17:51:02 |
| created_tmp_tables | 2524365 | 2524364 | -1 | -0 | -6 | 2018-11-06 17:51:02 |
| handler_tmp_write | 29418294952 | 29418294469 | -483 | -48 | -2898 | 2018-11-06 17:51:02 |
| memory_used | 2300318008 | 2297220056 | -3097952 | -309795 | -18587712 | 2018-11-06 17:51:02 |
| open_files | 111 | 109 | -2 | -0 | -12 | 2018-11-06 17:51:02 |
| opened_files | 4951963 | 4951959 | -4 | -0 | -24 | 2018-11-06 17:51:02 |
+————————-+————-+————-+———-+———+———–+———————+
6 rows in set (10.03 sec)
It looks like query picked up gs1 values before picking up gs0 values.
For my version of your base query, please visit http://www.mysqlservertuning.com, select Utility Scripts+ and download GLOBALSTATUSAGE10SEC.SQL for testing. After add to cart, checkout for $0.00 Order/Invoice total, please.
Thank you for your utilities.