Month: June 2007

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 own

Interactive 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 to

All 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