Statpack v2

There are some standard tools in every MySQL DBA's back pocket, these include maatkit, mysqlreport and mysqlsla. I have a new addition to this: statpack (http://www.markleith.co.uk/?p=21)

Some sample output from a statpack run below, its basically mysqlreport on steroids:

Uptime: 13 days 19 hours 38 mins Snapshot Period 1: 1 minute interval

Variable Delta/Percentage Per Second Total

Database Activity

Threads Connected: -3 7
Threads Running: 1 3
Questions: 16225 270.42 474675563
Bytes Recieved: 3M 53K 85G
Bytes Sent: 12M 211K 283G
Aborted Clients: 0 0.00 388
Aborted Connects: 0 0.00 20

Statement Activity

SELECT: 2802 46.70 58682451 (27.78%)
INSERT: 792 13.20 28820100 (13.65%)
UPDATE: 3928 65.47 122513991 (58.01%)
DELETE: 34 0.57 1149582 (0.54%)
REPLACE: 0 0.00 0 (0.00%)
INSERT ... SELECT: 3 0.05 35344 (0.02%)
REPLACE ... SELECT: 0 0.00 8130 (0.00%)
Multi UPDATE: 0 0.00 0 (0.00%)
Multi DELETE: 0 0.00 0 (0.00%)
COMMIT: 0 0.00 0 (0.00%)
ROLLBACK: 0 0.00 0 (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 0
FLUSH: 0 0.00 42
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: 2663 44.38 82619783
Threads Created: 0 0.00 5954

Table Cache

table_cache Efficiency: 6.38%
Open Tables: 0 0.00 166
Opened Tables: 0 0.00 2602

MyISAM Key Cache

Cache Read Efficiency: 99.85%
Cache Write Efficiency: 100.00%
Memory Used: 0B 1K
Memory Free: 0B 24K
Key Reads: 43 0.72 3729750
Key Read Requests: 14988 249.80 2450849322
Key Writes: 0 0.00 0
Key Write Requests: 2750 45.83 332785226
Blocks Not Flushed: 0 0.00 0

InnoDB Buffer Pool

Buffer Pool Read Efficiency: 100.00%
Memory Total: 0B 11G
Memory Free: 0B 16K
Memory Data: -16384B 10G
Memory Dirty: -10846208B 635M
Data Read: 16K 18G
Data Written: 29K 534G
Buffer Pool Reads: 1 0.02 616928
Buffer Pool Read Requests: 3124152 52069.20 254236495280
Buffer Pool Write Requests: 15353 255.88 840731818

InnoDB Log Files

Log Data Written: 875K 42G
Log Writes: 88 1.47 1741444
Log Write Requests: 1814 30.23 98611292
Log Waits: 0 0.00 290

Query Cache

QCache Hits / SELECT: 20.94%
QCache Hit/Qcache Insert: 22.08%
Qcache Hits/Invalidations: 0.00%
SELECTs: 2802 46.70 58682451
Query Cache Hits: 677 11.28 15538785
Query Cache Inserts: 2705 45.08 54845475
Queries Not Cached: 100 1.67 4012367
Cache Low Memory Prunes: 0 0.00 0
Total Cache Blocks: 1927 32.12 2485
Queries In Cache: 966 16.10 1234
Cache Free Blocks: -5 -0.08 6

Index Usage

Index Efficiency: 87.15%
Full Index Scans: 7 0.12 319550
Full Table Scans: 3 0.05 455243
Full Join Scans: 0 0.00 0
Handler_read_first: 7 0.12 319550 (0.00%)
Handler_read_key: 1141243 19020.72 64011142245 (45.98%)
Handler_read_next: 771503 12858.38 35703062748 (25.64%)
Handler_read_prev: 35861 597.68 21627355350 (15.53%)
Handler_read_rnd: 2140 35.67 941350035 (0.68%)
Handler_read_rnd_next: 202418 3373.63 16945119930 (12.17%)

Temporary Space

tmp_table_size Efficiency: 70.92%
Memory Temp Tables: 66 1.10 1046198
Disk Temp Tables: 16 0.27 304216
Temp Files: 12 0.20 291641

Lock Contention

Percent of Locks Waited: 0.00%
Table Locks Waited: 0 0.00 0
Table Locks Immediate: 12266 204.43 306815764

Sorting

Rows Sorted: 2626 43.77 7321582931
Sort Range: 177 2.95 3204483
Sort Scan: 40 0.67 616546
Sort Merge Passes: 6 0.10 230395
Full Range Joins: 0 0.00 0