I’ve finally gotten around to adding the last little things that I wanted to do to version 2 of Statpack – my python script for aggregating SHOW [GLOBAL] STATUS output.
Now it connects to a running instance, and allows you to gather statistics from there to generate reports.
Here are the options it supports:
[markleith@medusa:~/Development/statpack/statpack-v2] $ ./statpack.py
Error: No arguments supplied
usage: ./statpack.py [list of arguments]Non-interactive mode (aggregate txt files containing SHOW STATUS snapshots):
-f --files List of statistics files to aggregate (--files=stat1.txt[, stat2.txt..])
Argument must be first within list of arguments, on it's ownInteractive mode (connect to running MySQL server for SHOW STATUS snapshots):
-h --host Host for MySQL server to connect to in interactive mode
-P --port Port the MySQL server is running on
-S --socket UNIX domain socket to use (instead of -h and -P)
-u --user User to connect to the MySQL server with
-p --password Password for the user
-d --defaults-file Defaults file to read options from (reads [client] group by default)
-i --interval Length of each collection interval
-c --interval-count Count of intervals to collect and aggregate
-s --status-file File to output raw SHOW STATUS data toAll modes:
-r --report-file File to output generated report(s) to
As you can see – I’ve also added in the option to output the report to a file – with –report-file and if getting statistics form a running server, dumping the full output of SHOW STATUS to a file as well, for further analysis later – with –status-file. Each of these options expects a file name (or full path and name if not to the working directory).
I’ve tidied up the output formats for the ‘byte’ based values based, and added in a thread cache section, and changed the locale setting to the machines default, based on some feedback from Ronald Bradford.
Finally – I incorporated a patch from David Lowless – which allows Statpack to run on older versions of Python as well (by not using a fill character within center() or rjust()) – thanks David!
Here’s an up to date example:
====================================================================================================
Uptime: 19 hours 22 mins Snapshot Period 9: 0 minute interval
====================================================================================================
Variable Delta/Percentage Per Second Total
====================================================================================================
Database Activity
====================================================================================================Threads Connected: 0 8
Threads Running: 0 2
Questions: 45705 1523.50 1001384
Bytes Recieved: 8K 284B 79M
Bytes Sent: 6K 236B 6M
Aborted Clients: 0 0.00 2
Aborted Connects: 0 0.00 0====================================================================================================
Statement Activity
====================================================================================================SELECT: 0 0.00 811 (0.10%)
INSERT: 9 0.30 436 (0.05%)
UPDATE: 881 29.37 20152 (2.51%)
DELETE: 0 0.00 149 (0.02%)
REPLACE: 0 0.00 0 (0.00%)
INSERT … SELECT: 881 29.37 19900 (2.48%)
REPLACE … SELECT: 35147 1171.57 761532 (94.84%)
Multi UPDATE: 0 0.00 0 (0.00%)
Multi DELETE: 0 0.00 0 (0.00%)
COMMIT: 0 0.00 12 (0.00%)
ROLLBACK: 0 0.00 0 (0.00%)====================================================================================================
Prepared Statements
====================================================================================================Prepared Statement Count: Status variable not available in your MySQL version
PREPARE: 0 0.00 1526
EXECUTE: 0 0.00 763
DEALLOCATE PREPARE: 0 0.00 1526
Fetch Roundtrips: 0 0.00 0
Send Long Data: 0 0.00 0====================================================================================================
Admin Commands
====================================================================================================KILL: 0 0.00 0
FLUSH: 0 0.00 0
ANALYZE TABLE: 0 0.00 0
OPTIMIZE TABLE: 0 0.00 0
CHECK TABLE: 0 0.00 0
REPAIR TABLE: 0 0.00 0====================================================================================================
Thread Cache
====================================================================================================Thread Efficiency: 100.00%
Connections: 0 0.00 386
Threads Created: 0 0.00 206====================================================================================================
Table Cache
====================================================================================================table_cache Efficiency: 75.74%
Open Tables: 0 0.00 128
Opened Tables: 0 0.00 168====================================================================================================
MyISAM Key Cache
====================================================================================================Cache Read Efficiency: 99.95%
Cache Write Efficiency: 80.11%
Memory Used: 360B 8K
Memory Free: -563B 96K
Key Reads: 563 18.77 16051
Key Read Requests: 1998473 66615.77 33089888
Key Writes: 10433 347.77 193650
Key Write Requests: 39790 1326.33 973579
Blocks Not Flushed: 881 29.37 1231====================================================================================================
InnoDB Buffer Pool
====================================================================================================Buffer Pool Read Efficiency: 82.50%
Memory Total: 0B 8M
Memory Free: 0B 7M
Memory Data: 0B 448K
Memory Dirty: 0B 0B
Data Read: 0B 2M
Data Written: 0B 1K
Buffer Pool Reads: 0 0.00 21
Buffer Pool Read Requests: 0 0.00 119
Buffer Pool Write Requests: 0 0.00 0====================================================================================================
InnoDB Log Files
====================================================================================================Log Data Written: 0B 512B
Log Writes: 0 0.00 1
Log Write Requests: 0 0.00 0
Log Waits: 0 0.00 0====================================================================================================
Query Cache
====================================================================================================QCache Hits / SELECT: 0.00%
QCache Hit/Qcache Insert: 0.00%
Qcache Hits/Invalidations: 0.00%
SELECTs: 0 0.00 811
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: 98.52%
Full Index Scans: 0 0.00 155
Full Table Scans: 1 0.03 790
Full Join Scans: 0 0.00 0
Handler_read_first: 0 0.00 155 (0.00%)
Handler_read_key: 101996 3399.87 2150257 (3.22%)
Handler_read_next: 5035431 167847.70 63675799 (95.30%)
Handler_read_prev: 0 0.00 0 (0.00%)
Handler_read_rnd: 32139 1071.30 751630 (1.12%)
Handler_read_rnd_next: 1985 66.17 238846 (0.36%)====================================================================================================
Temporary Space
====================================================================================================tmp_table_size Efficiency: 100.00%
Memory Temp Tables: 880 29.33 19825
Disk Temp Tables: 0 0.00 0
Temp Files: 0 0.00 5====================================================================================================
Lock Contention
====================================================================================================Percent of Locks Waited: 0.00%
Table Locks Waited: 0 0.00 0
Table Locks Immediate: 891 29.70 27668====================================================================================================
Sorting
====================================================================================================Rows Sorted: 879 29.30 19035
Sort Range: 0 0.00 0
Sort Scan: 879 29.30 19035
Sort Merge Passes: 0 0.00 0
Full Range Joins: 0 0.00 0
The script is available here: http://www.markleith.co.uk/dl/statpack-v2.tar.gz