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
In interactive mode, with -h, -u, -p:
Python 2.5.1, MySQLdb 1.2.2-1000 on OSX. MySQL 4.1.21-pro.
Traceback (most recent call last):
File “statpack.py”, line 567, in
main()
File “statpack.py”, line 561, in main
getLiveStats()
File “statpack.py”, line 223, in getLiveStats
varList[i][1] = varList[i][2]
IndexError: list index out of range
Hi Peter,
Thanks for reporting this – I’ll take a look at what the problem is!
Cheers,
Mark
Problem was related to 4.1 versions not having all variables to hand. Fixed by ignoring this index error in this case.
Have updated the the tar file within this article to the new version.
Thanks again Peter!
Great work Mark.
I found a problem when trying for the syntax (and also to not run in interactive mode)
Error: MySQLdb module required for interactive usage
Please download and install from:
http://sourceforge.net/projects/mysql-python
In my instances I’m analyzing files from another server, I don’t see a need in my case to have the MySQL Python connector.
On the request list now, would be to consider variables not shown (in my case Com_set_option and Com_admin_commands are very high on site, but not reflected).
I’d also like if the files includes SHOW GLOBAL VARIABLES along with SHOW GLOBAL STATUS we could provide some figures (e.g. Query Cache Size, Tmp_table_size, table_cache, innodb_buffer_pool_size for example) against efficiency percentages could provide a more rounded report, especially when looking at historical data (say 3 weeks later after applying some changes)
Of course how long is a piece of string
Hi Ronald,
Yes the new version does require MySQLdb to be able to connect to a running server. However as you noted this is not required if just reading files.
I’ve moved the import (and error if not available) in to getLiveStats() so that it does not affect only using files.
I’m not sure where I’d fit in the set and admin commands within the report – would you want this in a new section, or within a current one(s)?
I’d also like to add in the global variables as well – but need to think through how I would do it, I’m not sure yet whether I would just add a new option to read the GLOBAL VARIABLES once, or add the variables in to the array of status variables, and attempt to find them within each and every loop that happens for status vars as well.
It would make sense to do them only once, but perhaps you want to see if they have also changed between snapshots as well..?
Feedback on this from you guys would be great..
I’m trying to run it in non-interactive mode, but its still asking for MySQLdb. Version 2.1 of statpack. Python 2.4.3.
% ./statpack.py –files=../hourly.log
Error: MySQLdb module required for interactive usage
Please download and install from:
http://sourceforge.net/projects/mysql-python
Mark,
I had to do some hacking to get it to work with Version 4.0
It was crashing with Multi-Update – varList[10]
I don’t know enough Python to know if this is the best way, but at least it works for me now.
Ronald
diff statpack.py /home/rbradford/bin/statpack.py
354c354
varList[58][j+1]+varList[10][j+1]+varList[12][j+1]+ \
356,359d355
print reportLine(p, 10, j,’Multi UPDATE’, 0) + \
381,383d375
Bytes Recieved: should be Bytes Received:
‘i’ before ‘e’ except after ‘c’
If the 2 status files are indeed the same (user error), then scripts throws a divide by zero message.
Would be nice to protect against this dumb user practice.
macmarvin:statpack-v2 rbradfor$ ./statpack.py –files=status.start,status.end
Number of Snapshots: 2
====================================================================================================
Uptime: 2 hours 20 mins Snapshot Period 1: 0 minute interval
====================================================================================================
Variable Delta/Percentage Per Second Total
====================================================================================================
Database Activity
====================================================================================================
Threads Connected: 0 3
Threads Running: 0 1
Traceback (most recent call last):
File “./statpack.py”, line 570, in
main()
File “./statpack.py”, line 555, in main
genReport(statPeriods)
File “./statpack.py”, line 342, in genReport
print reportLine(p, 2, j,’Questions’, 0)
File “./statpack.py”, line 282, in reportLine
float_format((float(varList[varId][j+1]) – varList[varId][j])/p).rjust(25)
ZeroDivisionError: float division
Traceback (most recent call last):
File “./statpack.py”, line 571, in
main()
File “./statpack.py”, line 565, in main
getLiveStats()
File “./statpack.py”, line 179, in getLiveStats
conn = MySQLdb.connect (**connString)
File “build/bdist.linux-x86_64/egg/MySQLdb/__init__.py”, line 74, in Connect
File “build/bdist.linux-x86_64/egg/MySQLdb/connections.py”, line 170, in __init__
TypeError: an integer is required
Fix: line 85
connString[“port”] = p
connString[“port”] = int(p)
Is Statpack V2 is compatible with MySQL 5.5 ?
Yes it should be. If there any problems let me know though. 🙂
cannot download the statpack. download link is broken
Hi Mark,
Is the version 2 available for download? The given link doesnt seem to have it.
It can’t download at
http://www.markleith.co.uk/dl/statpack-v2.tar.gz
where is it? cannot be downloaded
Download link not working
where can i download statpack. The listed URL is not working
Statpack is available here now:
https://github.com/MarkLeith/statpack