Mark Leith, on of the MySQL Support Team managers wrote some time ago a very nice utility I use often called Statpack.
My use of Statpack is very simple. Take two snaphots of SHOW GLOBAL STATUS and compare to produce a text based version of the statistics.
Over time I’ve grown to love it’s simplicity, but notice a number of shortcomings. Being open source there is always the ability to modify, improve and give back. This post is more about detailing those little annoyances that I’d like to improve, or see improved. It is also a means to collate points into one location that I often forget about over time.
I welcome any input, and specifically help in this open source venture.
Here is my wish list that I can currently remember. I do plan to action, time permitting.
- In Statement Activity, the total percentages are great to determine Read/Write ratio, however the ratio is for the Total, not for the period. I’m not sure how best to present, but would be good to know. See Exhibit 1 for a good example.
- When sections are not used, e.g. all ZERO values, then suppress for ease of reading. See Exhibit 2 for examples of Prepared Statements and Query Cache, where a simple line like No Prepared Statements activity, or Query Cache not enabled.
- Incorporating SHOW VARIABLES output. If this is included in one file, then adding some information may be very valuable when reviewing these audit files. For example in the InnoDB Buffer Pool show innodb_buffer_pool_size. In InnoDB Log Files show innodb_log_file_size, innodb_log_files_in_group, innodb_log_buffer_size. This can be used in most sections. See Exhibit 3 for an example.
- Incorporate a Date/Time in the report output. Again for historical purposes, at worst it could be the time the output is generated, however this is only an approximation. With SHOW GLOBAL STATUS output from my hourly.sh monitoring I include the following line before each SHOW STATUS output. ‘| date_time | 090611.161511 |’. Note to Drizzle Development team, please add date/time output to SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES.
- Negative numbers. There are occurrences when negative numbers occur, due to the wrapping of status numbers. See Exhibit 4 for example.
- Bug I’ve raised about divide by zero error when the same file (operator error) is specified.
Exhibit 1 – Percentages on Period/Total
SELECT: 56,019 28.11 281,456,428 (41.00%)
INSERT: 420,083 210.78 325,218,748 (47.38%)
UPDATE: 46 0.02 138,206 (0.02%)
DELETE: 48,590 24.38 79,170,553 (11.53%)
Exhibit 2 – Suppress sections
====================================================================================================
Prepared Statements
====================================================================================================
Prepared Statement Count: 0 0.00 0
PREPARE: 0 0.00 0
EXECUTE: 0 0.00 0
DEALLOCATE PREPARE: 0 0.00 0
Fetch Roundtrips: 0 0.00 0
Send Long Data: 0 0.00 0
====================================================================================================
Query Cache
====================================================================================================
QCache Hits / SELECT: 0.00%
QCache Hit/Qcache Insert: 0.00%
Qcache Hits/Invalidations: 0.00%
SELECTs: 13,503,876 3,798.56 4,298,170,239
Query Cache Hits: 0 0.00 0
Query Cache Inserts: 0 0.00 0
Queries Not Cached: 0 0.00 0
Cache Low Memory Prunes: 0 0.00 0
Total Cache Blocks: 0 0.00 0
Queries In Cache: 0 0.00 0
Cache Free Blocks: 0 0.00 0
Exhibit 3 – Including Variables
====================================================================================================
InnoDB Buffer Pool
====================================================================================================
Variables: innodb_buffer_pool_size = 10G, innodb_additional_mem_pool_size = 20M, innodb_file_per_table
Buffer Pool Read Efficiency: 99.89%
Data Read: 17G 4,892G
Data Written: 29M 6,167G
...
====================================================================================================
InnoDB Log Files
====================================================================================================
Variables: innodb_log_file_size=256M; innodb_log_files_in_group=2;innodb_log_buffer_size=1M;innodb_flush_log_at_trx_commit=2
Log Data Written: 1G 327G
Log Writes: 4,756 1.34 1,785,842
Log Write Requests: 4,195,878 1,180.28 773,981,459
Log Waits: 0 0.00 13
Exhibit 4- Negative Numbers
====================================================================================================
Index Usage
====================================================================================================
Index Efficiency: 51.09%
Full Index Scans: 7,056 0.08 142,549
Full Table Scans: 191,051 2.21 4,739,205
Full Join Scans: 374 0.00 9,601
Handler_read_first: 7,056 0.08 142,549 (0.00%)
Handler_read_key: 136,956,475 1,585.20 3,698,727,203 (47.29%)
Handler_read_next: 55,431,938 641.60 290,189,865 (3.71%)
Handler_read_prev: 210,418 2.44 7,283,443 (0.09%)
Handler_read_rnd: 58,241,824 674.12 1,862,365,188 (23.81%)
Handler_read_rnd_next: -1,682,312,703 -19,471.89 1,963,342,385 (25.10%)
A Full output Example
====================================================================================================
Uptime: 17 days 17 hours 22 mins Snapshot Period 1: 59 minute interval
====================================================================================================
Variable Delta/Percentage Per Second Total
====================================================================================================
Database Activity
====================================================================================================
Threads Connected: -5 115
Threads Running: -4 51
Questions: 24,190,027 6,804.51 7,366,339,778
Bytes Recieved: 1G 501K 522G
Bytes Sent: 11G 3M 2,808G
Aborted Clients: 0 0.00 237
Aborted Connects: 240 0.07 102,090
====================================================================================================
Statement Activity
====================================================================================================
SELECT: 13,503,876 3,798.56 4,298,170,239 (94.25%)
INSERT: 91,101 25.63 25,327,062 (0.56%)
UPDATE: 782,004 219.97 220,640,296 (4.84%)
DELETE: 9,674 2.72 2,485,643 (0.05%)
REPLACE: 0 0.00 4,980 (0.00%)
INSERT ... SELECT: 0 0.00 0 (0.00%)
REPLACE ... SELECT: 0 0.00 0 (0.00%)
Multi UPDATE: 0 0.00 0 (0.00%)
Multi DELETE: 0 0.00 0 (0.00%)
COMMIT: 46,422 13.06 13,700,478 (0.30%)
ROLLBACK: 0 0.00 13 (0.00%)
====================================================================================================
Prepared Statements
====================================================================================================
Prepared Statement Count: 0 0.00 0
PREPARE: 0 0.00 0
EXECUTE: 0 0.00 0
DEALLOCATE PREPARE: 0 0.00 0
Fetch Roundtrips: 0 0.00 0
Send Long Data: 0 0.00 0
====================================================================================================
Admin Commands
====================================================================================================
KILL: 0 0.00 21
FLUSH: 0 0.00 34
ANALYZE TABLE: 0 0.00 0
OPTIMIZE TABLE: 0 0.00 0
CHECK TABLE: 0 0.00 51
REPAIR TABLE: 0 0.00 0
====================================================================================================
Thread Cache
====================================================================================================
Thread Efficiency: 100.00%
Connections: 2,404,601 676.40 691,324,391
Threads Created: 0 0.00 1,015
====================================================================================================
Table Cache
====================================================================================================
table_cache Efficiency: 12.94%
Open Tables: 18 0.01 1,100
Opened Tables: 18 0.01 8,501
====================================================================================================
MyISAM Key Cache
====================================================================================================
Cache Read Efficiency: 75.56%
Cache Write Efficiency: 2.50%
Memory Used: 0B 24B
Memory Free: 0B 13K
Key Reads: 0 0.00 44
Key Read Requests: 0 0.00 179
Key Writes: 0 0.00 39
Key Write Requests: 0 0.00 39
Blocks Not Flushed: 0 0.00 0
====================================================================================================
InnoDB Buffer Pool
====================================================================================================
Buffer Pool Read Efficiency: 99.89%
Memory Total: 0B 11G
Memory Free: 0B 0B
Memory Data: -12,910,592B 10G
Memory Dirty: 245M 3G
Data Read: 17G 4,892G
Data Written: 29M 6,167G
Buffer Pool Reads: 864,166 243.08 234,058,329
Buffer Pool Read Requests: 674,921,081 189,851.22 213,974,254,498
Buffer Pool Write Requests: 34,337,556 9,658.95 5,907,928,452
====================================================================================================
InnoDB Log Files
====================================================================================================
Log Data Written: 1G 327G
Log Writes: 4,756 1.34 1,785,842
Log Write Requests: 4,195,878 1,180.28 773,981,459
Log Waits: 0 0.00 13
====================================================================================================
Query Cache
====================================================================================================
QCache Hits / SELECT: 0.00%
QCache Hit/Qcache Insert: 0.00%
Qcache Hits/Invalidations: 0.00%
SELECTs: 13,503,876 3,798.56 4,298,170,239
Query Cache Hits: 0 0.00 0
Query Cache Inserts: 0 0.00 0
Queries Not Cached: 0 0.00 0
Cache Low Memory Prunes: 0 0.00 0
Total Cache Blocks: 0 0.00 0
Queries In Cache: 0 0.00 0
Cache Free Blocks: 0 0.00 0
====================================================================================================
Index Usage
====================================================================================================
Index Efficiency: 96.38%
Full Index Scans: 0 0.00 3,685
Full Table Scans: 26,468 7.45 9,336,473
Full Join Scans: 0 0.00 0
Handler_read_first: 0 0.00 3,685 (0.00%)
Handler_read_key: 60,313,973 16,965.96 18,840,285,973 (14.08%)
Handler_read_next: 284,810,290 80,115.41 109,257,639,924 (81.68%)
Handler_read_prev: 2,524,541 710.14 822,756,210 (0.62%)
Handler_read_rnd: 10,268,347 2,888.42 3,230,517,410 (2.42%)
Handler_read_rnd_next: 780,215 219.47 1,612,883,644 (1.21%)
====================================================================================================
Temporary Space
====================================================================================================
tmp_table_size Efficiency: 2.10%
Memory Temp Tables: 26,467 7.45 9,335,030
Disk Temp Tables: 25,951 7.30 9,138,705
Temp Files: 2 0.00 601
====================================================================================================
Lock Contention
====================================================================================================
Percent of Locks Waited: 0.00%
Table Locks Waited: 0 0.00 230
Table Locks Immediate: 14,411,359 4,053.83 4,555,498,747
====================================================================================================
Sorting
====================================================================================================
Rows Sorted: 790,892 222.47 251,683,065
Sort Range: 3,767 1.06 983,825
Sort Scan: 25,952 7.30 9,137,325
Sort Merge Passes: 1 0.00 298
Full Range Joins: 0 0.00 0
20G additional memory pool?
Hey Ronald!
Thanks for the write up, I know we’ve talked about some of these in the past – and in fact, I’ve already started working on #2 (a variable to list the sections you want to print).
I’ll try and work on the others as time permits as well!
Cheers,
Mark