MySQL Global status difference using single query

August 12, 2011

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!

tags: , , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

7 Comments to "MySQL Global status difference using single query"

  1. Giuseppe Maxia wrote:

    Well done, Shlomi!

  2. shlomi wrote:

    Thanks. The power of SQL is underrated (and I do feel free to abuse it).

  3. Cédric wrote:

    Great query !
    Thx
    Cédric

  4. Alan Ledrowski wrote:

    Shlomi,

    Nice post. I really enjoy they way your mind works. It helps me to be more creative in my work.

    Cheers,
    Alan

  5. shlomi wrote:

    @Alan,
    I'm honored.

  6. Wilson Hauck wrote:

    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

  7. shlomi wrote:

    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
    ;
    

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org