Nagios数据库参数配置分析

Nagios数据库参数配置分析

---

MySQL 5.1.66             uptime 24 11:19:10     Fri Dec 26 09:54:55 2014


__ Key _________________________________________________________________
Buffer used     6.54M of   8.00M  %Used:  81.75
  Current       8.00M            %Usage: 100.00       //使用值相当高
Write hit      28.28%
Read hit       88.68%

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
调整key_buffer_size值 大小：128M

1.单个key_buffer的大小不能超过4G，如果设置超过4G，就有可能遇到bug:
2.建议key_buffer设置为物理内存的1/4(针对MyISAM引擎)，甚至是物理内存的30%~40%，
  如果key_buffer_size设置太大，系统就会频繁的换页，降低系统性能。
  因为MySQL使用操作系统的缓存来缓存数据，所以我们得为系统留够足够的内存；在很多情况下数据要比索引大得多。
3.如果机器性能优越，可以设置多个key_buffer,分别让不同的key_buffer来缓存专门的索引

深入地优化key_buffer_size，
使用"show status"来查看"Key_read_requests, Key_reads, Key_write_requests  以及Key_writes "，
以调整到更适合你的应用的大小，Key_reads/Key_read_requests的大小正常情况下得小于0.01

| Key_read_requests      | 5262843294 |
| Key_reads              | 596037039  |       Key_reads/Key_read_requests 0.1相当不合理       

3.如果Handler_read_rnd太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥键的作用
4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率
5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基于内存的临时表代替基于磁盘的  
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


__ Questions ___________________________________________________________
Total         848.10M   401.1/s
  DMS         817.00M   386.4/s  %Total:  96.33         //    Data manipulation statements
  Com_         29.51M    14.0/s            3.48
  COM_QUIT      3.76M     1.8/s            0.44
  -Unknown      2.17M     1.0/s            0.26
Slow 10 s         112     0.0/s            0.00  %DMS:   0.00  Log: OFF       //慢查询少

DMS           817.00M   386.4/s           96.33
  INSERT      551.05M   260.6/s           64.97         67.45
  SELECT      128.04M    60.6/s           15.10         15.67
  DELETE      102.56M    48.5/s           12.09         12.55
  UPDATE       35.23M    16.7/s            4.15          4.31
  REPLACE     109.88k     0.1/s            0.01          0.01
Com_           29.51M    14.0/s            3.48      //COM_ 这个类别代表着所有 MySQL 所执行过的指令，越小越好
  change_db    21.34M    10.1/s            2.52
  set_option    3.51M     1.7/s            0.41
  show_tables   2.42M     1.1/s            0.29




+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
数据库写读比例大概是64:15
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



__ SELECT and Sort _____________________________________________________
Scan           14.15M     6.7/s %SELECT:  11.05
Range           9.32M     4.4/s            7.28
Full join       6.17M     2.9/s            4.82
Range check         0       0/s            0.00
Full rng join     190     0.0/s            0.00
Sort scan       3.57M     1.7/s
Sort range    261.63k     0.1/s
Sort mrg pass      63     0.0/s



+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
注意Scan 与 Full Join。Scan 指的是有多少 SELECT statements 造成 MySQL 需要进行 Full Table Scan。
Full Join 的意思与 Scan 差不多，但它是适用在多个 Tables 相互 Join 在一起的情况
越小越好

进行scan占全部select的11.05%      Full Join占4.82%
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




__ Table Locks _________________________________________________________
Waited        212.94k     0.1/s  %Total:   0.02     //代表 MySQL 需要等待以取得 table lock 的次数
Immediate     856.95M   405.3/s             //表示 MySQL 不需要等待即可立刻取得 table lock 的次数


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



__ Tables ______________________________________________________________
Open               64 of   64    %Cache: 100.00           //table_cache已使用100%
Opened          1.93M     0.9/s


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
table_cache必须增大。 目前： table_open_cache =64    调整，2000

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



__ Connections _________________________________________________________
Max used           83 of 5000      %Max:   1.66
Total           3.76M     1.8/s


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
nagios数据库连接数不多，可以调低一些 500够了
max_connections=500
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



__ Created Temp ________________________________________________________
Disk table     39.99k     0.0/s
Table           6.15M     2.9/s    Size:  16.0M
File              131     0.0/s


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 Disk table 的值最好是三者中最小的一个。
 当暂时性的数据表被建立在硬盘中，表示此数据表没有办法被放进 RAM 里面（因为 tmp_table_size 的值设得不够大）。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


__ Threads _____________________________________________________________
Running             1 of   17
Cached              0 of    0      %Hit:      0
Created         3.76M     1.8/s
Slow                0       0/s


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
thread_cache_size 的值需要调大，实际中 thread_cache_size 为0
 根据调查发现以上服务器线程缓存thread_cache_size没有进行设置，或者设置过小,
 这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,
 那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果
 缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个
 值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量
 的作用。
 (--&gt表示要调整的值)  根据物理内存设置规则如下：
     1G  ---&gt 8
     2G  ---&gt 16
     3G  ---&gt 32
    >3G  ---&gt 64
优化方法：
1、mysql> set global thread_cache_size=64
2、编辑/etc/my.cnf 更改/添加
thread_concurrency = 64

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



__ Aborted _____________________________________________________________
Clients           160     0.0/s
Connects           10     0.0/s

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
异常连接
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


__ Bytes _______________________________________________________________
Sent          166.30G   78.7k/s
Received      558.75G  264.3k/s

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

__ InnoDB Buffer Pool __________________________________________________
Usage           2.62G of   4.00G  %Used:  65.40
Read hit      100.00%
Pages
  Free         90.70k            %Total:  34.60        // Free指的是缓存中的总页数, 剩余的页, 占总的34.60%
  Data        170.85k                     65.18 %Drty:   0.19       //Data是指缓存中, 存储索引数据的页的数量
  Misc            590                      0.23
  Latched                                  0.00
Reads          49.93G   23.6k/s       //代表从缓存里, 总共读取了多少M的数据.
  From file     3.71k     0.0/s            0.00
  Ahead Rnd        26     0.0/s      //表示随机预读的次数.
  Ahead Sql        13     0.0/s      //表示全表扫描时, sql预读的次数.
Writes          7.77G    3.7k/s           //表示写入缓存的总大小
Flushes        24.43M    11.6/s           //表示缓存数据更新到硬盘的大小.
Wait Free           0       0/s           //表示等待可写入数据的页的次数.


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
innodb_buffer_pool_size=20G
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



__ InnoDB Lock _________________________________________________________
Waits          481233     0.2/s
Current             0
Time acquiring
  Total       7054915 ms
  Average          14 ms
  Max           11949 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads         4.34k     0.0/s
  Writes      555.01M   262.5/s
  fsync       548.99M   259.6/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created     165.72k     0.1/s
  Read          5.14k     0.0/s
  Written      24.43M    11.6/s

Rows
  Deleted     115.26M    54.5/s
  Inserted    125.63M    59.4/s
  Read         17.01G    8.0k/s
  Updated     196.17M    92.8/s

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1383795/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29500582/viewspace-1383795/