mysql mysqlreport_mysqltuner 及mysqlreport 性能测试的监控及分析工具

mysqltuner and mysqlreport 工具使用

1.2mysqltuner.pl

本工具建议定期运行,发现目前MYSQL数据库存在的问题及修改相关的参数.

1.2.1安装

直接下载

[root@even ~]# wgethttp://mysqltuner.pl/mysqltuner.pl

[root@even ~]# chmod +x mysqltuner.pl

[root@even ~]# cp

mysqltuner.pl /home/mysql/

1.2.2生成的报告如下

[root@xx xxx]#

./mysqltuner.pl

>>MySQLTuner 1.0.0 - Major Hayden

>>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.57-community-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics

-------------------------------------------

[--] Status: -Archive -BDB -Federated

+InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 18G (Tables:

3395)

[--] Data in MRG_MYISAM tables: 218M

(Tables: 11)

[--] Data in InnoDB tables: 3G (Tables:

449)

[--] Data in MEMORY tables: 95M (Tables:

15)

[!!] Total fragmented tables: 188

-------- Performance Metrics

-------------------------------------------------

[--] Up for: 8d 0h 22m 0s (7B q [10K qps],

3M conn, TX: 6131B, RX: 2280B)

[--] Reads / Writes: 62% / 38%

[--] Total buffers: 1.5G global + 96.2M per

thread (1000 max threads)

[!!] Maximum possible memory usage: 95.5G

(1226% of installed RAM)

[OK] Slow queries: 0% (8K/7B)

[!!] Highest connection usage: 100%(1001/1000)

[OK] Key buffer size / total MyISAM

indexes: 512.0M/6.5G

[OK] Key buffer hit rate: 100.0% (6B cached

/ 301K reads)

[OK] Query cache efficiency: 99.2% (7B

cached / 7B selects)

[!!] Query cache prunes per day: 6074

[OK] Sorts requiring temporary tables: 0%

(2 temp sorts / 3M sorts)

[!!] Joins performed without indexes: 2029

[!!] Temporary tables created on disk: 33%

(2M on disk / 7M total)

[OK] Thread cache hit rate: 99% (1K created

/ 3M connections)

[!!] Table cache hit rate: 0% (2K open /

222K opened)

[OK] Open file limit used: 35% (2K/8K)

[OK] Table locks acquired immediately: 97%

(226M immediate / 230M locks)

[!!] InnoDB data size / buffer pool:

3.9G/256.0M

-------- Recommendations

-----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Reduce your overall MySQL memory footprint for system stability

Reduce or eliminate persistent connections to reduce connection usage

Increasing the query_cache size over 128M may reduce performance

Adjust your join queries to always utilize indexes

Temporary table size is already large - reduce result set size

Reduce your SELECT DISTINCT queries without LIMIT clauses

Increase table_cache gradually to avoid file

descriptor limits

Variables to adjust:

***

MySQL's maximum memory usage is dangerously high ***

***

Add RAM before increasing MySQL buffer variables ***

max_connections (> 1000)

wait_timeout (< 3600)

interactive_timeout (< 3600)

query_cache_size (> 512M) [see warning above]

join_buffer_size (> 32.0M, or always use indexes with joins)

table_cache (> 2000)

innodb_buffer_pool_size

(>= 3G)

分析本报告：

(１)链接数要求１０００，每一个链接需要的内存是９５Ｍ左右，需要总的内存是９６g，目前本机的内存只有８ＧHighest connection usage: 100%(1001/1000)

建议链接数大于１０００，最大值是１００１

(２)Key buffer size / total MyISAM

indexes: 512.0M/6.5G建议调整成６.５Ｇ左右

(３)Temporary tables created on

disk: 33% (2M on disk / 7M total)说明了需要调整tmp_table_size大小的

(４)系统整体建议的参数为：

max_connections (>

1000)

wait_timeout (< 3600)

interactive_timeout (<

3600)

query_cache_size (>

512M) [see warning above]

join_buffer_size (>

32.0M, or always use indexes with joins)

table_cache (> 2000)

innodb_buffer_pool_size

(>= 3G

1.1Mysqlreport

1.1.1安装

下载地址

Chmod 777 mysqlreport

./mysqlreport

1.1.2使用

生成的报告如下

[root@xxx xxx]#

./mysqlreport --flush-status

Use of uninitialized value in

multiplication (*) at ./mysqlreport line 829.

Use of uninitialized value in formline at

./mysqlreport line 1227.

MySQL 5.1.57-community-uptime 8 0:40:50Wed Nov 13 10:08:06 2013

__ Key

_________________________________________________________________

Buffer used27.52M of 512.00M%Used:5.37

Current97.83M%Usage:19.11

Write hit99.55%

Read hit100.00%

__ Questions ___________________________________________________________

Total7.13G10.3k/s

QC

Hits7.03G10.1k/s%Total:98.58

Com_6.82G9.8k/s95.69

-Unknown6.81G9.8k/s95.52

DMS85.78M123.7/s1.20

COM_QUIT3.38M4.9/s0.05

Slow 1 s8.44k0.0/s0.00%DMS:0.01Log:ON

DMS85.78M123.7/s1.20

SELECT53.86M77.6/s0.7662.79

DELETE19.56M28.2/s0.2722.81

INSERT9.83M14.2/s0.1411.46

UPDATE1.64M2.4/s0.021.91

REPLACE883.42k1.3/s0.011.03

Com_6.82G9.8k/s95.69

admin_comma6.81G9.8k/s95.52

set_option5.99M8.6/s0.08

change_db3.19M4.6/s0.04

__ SELECT and Sort

_____________________________________________________

Scan3.93M5.7/s %SELECT:7.31

Range8.95M12.9/s16.61

Full join2.03k0.0/s0.00

Range check00/s0.00

Full rng join48.99k0.1/s0.09

Sort scan2.17M3.1/s

Sort range947.47k1.4/s

Sort mrg pass20.0/s

__ Query Cache

_________________________________________________________

Memory usage215.91M of 512.00M%Used:42.17

Block Fragmnt15.00%

Hits7.03G10.1k/s

Inserts5.77M8.3/s

Insrt:Prune118.57:18.3/s

Hit:Insert1217.91:1

__ Table Locks

_________________________________________________________

Waited4.68M6.7/s%Total:2.02

Immediate226.55M326.6/s

__ Tables

______________________________________________________________

Open2000 of 2000%Cache: 100.00

Opened241.76k0.3/s

__ Connections

_________________________________________________________

Max used1001 of 1000%Max: 100.10

Total3.38M4.9/s

__ Created Temp

________________________________________________________

Disk table2.45M3.5/s

Table4.85M7.0/sSize: 256.0M

File2.06k0.0/s

__ Threads _____________________________________________________________

Running2 of781

Cached21 of500%Hit:99.96

Created1.28k0.0/s

Slow00/s

__ Aborted _____________________________________________________________

Clients8.91k0.0/s

Connects2.39k0.0/s

__ Bytes

_______________________________________________________________

Sent6.14T8.9M/s

Received2.29T3.3M/s

__ InnoDB Buffer Pool

__________________________________________________

Usage256.00M of 256.00M%Used: 100.00

Read hit100.00%

Pages

Free0%Total:0.00

Data16.13k98.45 %Drty:0.00

Misc2541.55

Latched0.00

Reads5.26G7.6k/s

From file60.56k0.1/s0.00

Ahead Rnd10010.0/s

Ahead Sql12420.0/s

Writes10.89M15.7/s

Flushes163.88k0.2/s

Wait Free00/s

__ InnoDB Lock

_________________________________________________________

Waits1580.0/s

Current0

Time acquiring

Total401132 ms

Average2538 ms

Max21434 ms

__ InnoDB Data, Pages, Rows

____________________________________________

Data

Reads71.09k0.1/s

Writes1.37M2.0/s

fsync1.30M1.9/s

Pending

Reads0

Writes0

fsync0

Pages

Created8.49k0.0/s

Read169.24k0.2/s

Written163.88k0.2/s

Rows

Deleted237.02k0.3/s

Inserted255.62k0.4/s

Read1.77G2.5k/s

Updated180.99k0.3/s