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 狀態的變數，可以看到這個變數
的作用。
(-->表示要調整的值) 根據實體記憶體設定規則如下：
1G ---> 8
2G ---> 16
3G ---> 32
>3G ---> 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
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 狀態的變數，可以看到這個變數
的作用。
(-->表示要調整的值) 根據實體記憶體設定規則如下：
1G ---> 8
2G ---> 16
3G ---> 32
>3G ---> 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/30936525/viewspace-2097984/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- nagios 外掛引發的 資料庫告警iOS資料庫
- 配置資料庫非同步I/O引數資料庫非同步
- 使用SQL來分析資料庫引數（二)SQL資料庫
- MySQL資料庫引數MySql資料庫
- Ceph配置引數分析
- 調整資料庫引數資料庫
- 顯示資料庫所有引數資料庫
- Oracle 資料庫引數調整Oracle資料庫
- MySQL資料庫高併發下的引數配置優化案例MySql資料庫優化
- 天翼雲RDS資料庫如何修改資料庫引數資料庫
- 資料庫易混淆引數名以及引數檔案啟動資料庫到nomount狀態資料庫
- DM7修改資料庫引數資料庫
- postgresql資料庫重要引數說明SQL資料庫
- Mysql 5.5 設定資料庫引數MySql資料庫
- MySQL 5.7資料庫引數優化MySql資料庫優化
- 合理配置TimesTen記憶體資料庫Hash索引的PAGES引數記憶體資料庫索引
- oracle10g data guard 主備資料庫配置引數說明Oracle資料庫
- DB2資料庫管理器配置引數SRVCON_AUTHDB2資料庫
- Oracle12c中配置例項引數和修改容器資料庫（CDB）及可插拔資料庫（PDB）Oracle資料庫
- nagios-新增oracle資料庫監控iOSOracle資料庫
- JDBC連線資料庫時，Oracle9i的連線引數配置JDBC資料庫Oracle
- 達夢資料庫引數調整方法資料庫
- oracle 資料庫兩種引數檔案Oracle資料庫
- RCU-資料庫初始化引數資料庫
- Oracle資料庫系統中的引數Oracle資料庫
- Oracle資料庫中的系統引數Oracle資料庫
- 關於資料庫標識類引數資料庫
- 將rac資料庫改為單機資料庫需要修改的引數資料庫
- 正式上崗前的準備：怎麼檢視資料庫引數配置資訊資料庫
- 導數時資料庫hang住分析資料庫
- 汽車之家車型_車系_配置引數資料抓取
- 在開啟dbcc連線資料庫檢視資料庫配置引數時提示SQL1337錯誤資料庫SQL
- PostgreSQL DBA(1) - 資料庫引數設定#1SQL資料庫
- PostgreSQL DBA(2) - 資料庫引數設定#2SQL資料庫
- 關於修改資料庫引數的測試資料庫
- Oracle資料庫系統中的引數（續）Oracle資料庫
- [Q]怎麼檢視資料庫引數 zt資料庫
- 獲取資料庫中所有隱藏引數資料庫