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!
Well done, Shlomi!
Thanks. The power of SQL is underrated (and I do feel free to abuse it).
Great query !
Thx
Cédric
Shlomi,
Nice post. I really enjoy they way your mind works. It helps me to be more creative in my work.
Cheers,
Alan
@Alan,
I’m honored.
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…… Read more »
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… Read more »