http://netkiller.sourceforge.net/monitoring/index.html
1.11.1. Analysis and Optimization
1.11.1.1. mytop - top like query monitor for MySQL
sudo apt-get install mytop
mytop --host=172.16.0.7 --user=monitor --password=your_passwd
1.11.1.2. mtop - MySQL terminal based query monitor
http://mtop.sourceforge.net/
sudo apt-get install mtop
mtop --host=172.16.0.6 --dbuser=monitor --password=your_passwd
mkill
mkill -sl 180 -fi 'select.*from bad_table' > /var/log/mkill.out 2> /var/log/mkill.kill
1.11.1.4. mysqlreport - A friendly report of important MySQL status values
# yum install mysqlreport -y
wget hackmysql.com/scripts/mysqlreport
[root@database ~]# mysqlreport --user root --password chen
Use of uninitialized value in multiplication (*) at /usr/bin/mysqlreport line 829.
Use of uninitialized value in formline at /usr/bin/mysqlreport line 1227.
MySQL 5.0.77-log uptime 28 23:42:33 Sat Apr 10 18:15:44 2010
__ Key _________________________________________________________________
Buffer used 6.54M of 8.00M %Used: 81.75
Current 1.49M %Usage: 18.58
Write hit 97.65%
Read hit 99.81%
__ Questions ___________________________________________________________
Total 2.22M 0.9/s
DMS 1.91M 0.8/s %Total: 86.16
Com_ 249.93k 0.1/s 11.25
COM_QUIT 63.68k 0.0/s 2.87
-Unknown 6.26k 0.0/s 0.28
Slow 10 s 52 0.0/s 0.00 %DMS: 0.00 Log: OFF
DMS 1.91M 0.8/s 86.16
SELECT 1.17M 0.5/s 52.81 61.29
INSERT 276.13k 0.1/s 12.43 14.43
DELETE 264.78k 0.1/s 11.92 13.84
UPDATE 158.14k 0.1/s 7.12 8.26
REPLACE 41.74k 0.0/s 1.88 2.18
Com_ 249.93k 0.1/s 11.25
set_option 89.09k 0.0/s 4.01
change_db 59.71k 0.0/s 2.69
show_create 28.57k 0.0/s 1.29
__ SELECT and Sort _____________________________________________________
Scan 161.33k 0.1/s %SELECT: 13.76
Range 6.47k 0.0/s 0.55
Full join 1.56k 0.0/s 0.13
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 34.03k 0.0/s
Sort range 21.98k 0.0/s
Sort mrg pass 733 0.0/s
__ Table Locks _________________________________________________________
Waited 56 0.0/s %Total: 0.00
Immediate 2.15M 0.9/s
__ Tables ______________________________________________________________
Open 64 of 64 %Cache: 100.00
Opened 159.20k 0.1/s
__ Connections _________________________________________________________
Max used 36 of 200 %Max: 18.00
Total 63.75k 0.0/s
__ Created Temp ________________________________________________________
Disk table 32.80k 0.0/s
Table 63.69k 0.0/s Size: 32.0M
File 319 0.0/s
__ Threads _____________________________________________________________
Running 1 of 1
Cached 0 of 0 %Hit: 0
Created 63.75k 0.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 128 0.0/s
Connects 130 0.0/s
__ Bytes _______________________________________________________________
Sent 23.89G 9.5k/s
Received 6.36G 2.5k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 8.00M of 8.00M %Used: 100.00
Read hit 99.99%
Pages
Free 0 %Total: 0.00
Data 511 99.80 %Drty: 0.00
Misc 1 0.20
Latched 0.00
Reads 1.54M 0.6/s
From file 135 0.0/s 0.01
Ahead Rnd 4 0.0/s
Ahead Sql 6 0.0/s
Writes 868.00k 0.3/s
Flushes 1.56k 0.0/s
Wait Free 0 0/s
__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 194 0.0/s
Writes 628 0.0/s
fsync 323 0.0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 534 0.0/s
Read 201 0.0/s
Written 1.56k 0.0/s
Rows
Deleted 0 0/s
Inserted 423.82k 0.2/s
Read 1.27M 0.5/s
Updated 0 0/s
1.11.1.5. mysqltuner - MySQL configuration assistant
# mysqltuner
>> MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0
-------- Security Recommendations -------------------------------------------
[!!] User 'root' has no password set.
[!!] User 'root' has no password set.
[!!] User 'root' has no password set.
[!!] User '' has no password set.
[!!] User '' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 18m 55s (42 q [0.037 qps], 7 conn, TX: 27K, RX: 1K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (45% of installed RAM)
[OK] Slow queries: 0% (0/42)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/89.0K
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 4 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 76% (23 open / 30 opened)
[OK] Open file limit used: 4% (46/1K)
[OK] Table locks acquired immediately: 100% (19 immediate / 19 locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
1.11.4. Monitoring MySQL with SNMP
mysql-snmp - monitoring MySQL with SNMP
原文出处：Netkiller 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。
