友善交流技术...

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

上一篇 / 下一篇  2013-11-15 16:41:49 / 个人分类:mysql

mysqltuner and mysqlreport 工具使用

1.2    mysqltuner.pl

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

1.2.1 安装

直接下载

[root@even ~]# wget http://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 <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.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)

 

分析本报告:

(1)  链接数要求1000,每一个链接需要的内存是95M左右,需要总的内存是96g,目前本机的内存只有8G Highest connection usage: 100% (1001/1000)

建议链接数大于1000,最大值是1001

(2)  Key buffer size / total MyISAM indexes: 512.0M/6.5G  建议调整成6.5G左右

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

(4)  系统整体建议的参数为:

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

1.1.1 安装

下载地址

http://hackmysql.com/mysqlreport

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:50      Wed Nov 13 10:08:06 2013

 

__ Key _________________________________________________________________

Buffer used   27.52M of 512.00M %Used:  5.37

 Current     97.83M           %Usage: 19.11

Write hit     99.55%

Read hit     100.00%

 

__ Questions ___________________________________________________________

Total          7.13G  10.3k/s

 QC Hits      7.03G  10.1k/s %Total: 98.58

 Com_         6.82G   9.8k/s          95.69

 -Unknown     6.81G   9.8k/s          95.52

 DMS         85.78M  123.7/s           1.20

 COM_QUIT     3.38M    4.9/s           0.05

Slow 1 s       8.44k    0.0/s           0.00 %DMS:  0.01 Log: ON

DMS           85.78M  123.7/s           1.20

 SELECT      53.86M   77.6/s           0.76        62.79

 DELETE      19.56M   28.2/s           0.27        22.81

 INSERT       9.83M   14.2/s           0.14        11.46

 UPDATE       1.64M    2.4/s           0.02         1.91

 REPLACE    883.42k    1.3/s           0.01         1.03

Com_           6.82G   9.8k/s          95.69

 admin_comma  6.81G   9.8k/s          95.52

 set_option   5.99M    8.6/s           0.08

 change_db    3.19M    4.6/s           0.04

 

__ SELECT and Sort _____________________________________________________

Scan           3.93M    5.7/s %SELECT:  7.31

Range          8.95M   12.9/s          16.61

Full join      2.03k    0.0/s           0.00

Range check        0      0/s           0.00

Full rng join 48.99k    0.1/s           0.09

Sort scan      2.17M    3.1/s

Sort range   947.47k    1.4/s

Sort mrg pass      2    0.0/s

 

__ Query Cache _________________________________________________________

Memory usage 215.91M of 512.00M %Used: 42.17

Block Fragmnt 15.00%

Hits           7.03G  10.1k/s

Inserts        5.77M    8.3/s

Insrt:Prune 118.57:1    8.3/s

Hit:Insert 1217.91:1

 

__ Table Locks _________________________________________________________

Waited         4.68M    6.7/s %Total:  2.02

Immediate    226.55M  326.6/s

 

__ Tables ______________________________________________________________

Open            2000 of 2000   %Cache: 100.00

Opened       241.76k    0.3/s

 

__ Connections _________________________________________________________

Max used        1001 of 1000     %Max: 100.10

Total          3.38M    4.9/s

 

__ Created Temp ________________________________________________________

Disk table     2.45M    3.5/s

Table          4.85M    7.0/s   Size: 256.0M

File           2.06k    0.0/s

 

__ Threads _____________________________________________________________

Running            2 of 781

Cached            21 of 500     %Hit: 99.96

Created        1.28k    0.0/s

Slow               0      0/s

 

__ Aborted _____________________________________________________________

Clients        8.91k    0.0/s

Connects       2.39k    0.0/s

 

__ Bytes _______________________________________________________________

Sent           6.14T   8.9M/s

Received       2.29T   3.3M/s

 

__ InnoDB Buffer Pool __________________________________________________

Usage        256.00M of 256.00M %Used: 100.00

Read hit     100.00%

Pages

 Free             0           %Total:  0.00

 Data        16.13k                    98.45 %Drty:  0.00

 Misc           254                     1.55

 Latched                                 0.00

Reads          5.26G   7.6k/s

 From file   60.56k    0.1/s           0.00

 Ahead Rnd     1001    0.0/s

 Ahead Sql     1242    0.0/s

Writes        10.89M   15.7/s

Flushes      163.88k    0.2/s

Wait Free          0      0/s

 

__ InnoDB Lock _________________________________________________________

Waits            158    0.0/s

Current            0

Time acquiring

 Total       401132 ms

 Average       2538 ms

 Max          21434 ms

 

__ InnoDB Data, Pages, Rows ____________________________________________

Data

 Reads       71.09k    0.1/s

 Writes       1.37M    2.0/s

 fsync        1.30M    1.9/s

 Pending

   Reads          0

   Writes         0

   fsync          0

 

Pages

 Created      8.49k    0.0/s

 Read       169.24k    0.2/s

 Written    163.88k    0.2/s

 

Rows

 Deleted    237.02k    0.3/s

 Inserted   255.62k    0.4/s

 Read         1.77G   2.5k/s

 Updated    180.99k    0.3/s


TAG:

 

评分:0

我来说两句

Open Toolbar