Statpack Version 2

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

24 thoughts on “Statpack Version 2”

  1. 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

    Reply

  3. 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!

    Reply

  4. 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

    Reply

  5. 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..

    Reply

  7. 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

    Reply

  9. 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

    Reply

  10. 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)

    Reply
  11. Pingback: MySAR: A sar-like Utility for MySQL | Pythian Group Blog
  12. Pingback: Unknown locale for statpack & maatkit | MySQL Expert | MySQL Performance | MySQL Scalability
  13. Pingback: Unknown locale for statpack & maatkit « PlanetMysql.ru – инÑ?оÑ?маÑ?ия о СУÐ?Ð? MySQL
  14. Pingback: MySql Statistics Aggregator | Solution Expert Online Blogs
  15. Pingback: My ‘Hourly’ MySQL Monitor Script Version 0.05 | MySQL Expert | MySQL Performance | MySQL Consulting

Got something to say?