mysql監(jiān)控調(diào)優(yōu)

1、運(yùn)行中的mysql狀態(tài)查看,對正在運(yùn)行的mysql進(jìn)行監(jiān)控，其中一個方式就是查看mysql運(yùn)行狀態(tài)。(1)QPS(每秒Query量)QPS = Questions(or Queries) / secondsmysql show global status like Question%;(2)TPS(每秒事務(wù)量)TPS = (Com_commit + Com_rollback) / secondsmysql show global status like Com_commit;mysql show global status like Com_rollback;(3)key Buffer 命中率

2、mysqlshow global status like key%;key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%(4)InnoDB Buffer命中率mysql show status like innodb_buffer_pool_read%;innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffe

3、r_pool_read_requests) * 100%(5)Query Cache命中率mysql show status like Qcache%;Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts ) * 100%;(6)Table Cache狀態(tài)量mysql show global status like open%;比較 open_tables 與 opend_tables 值(7)Thread Cache 命中率mysql show global status like Thread%;mysql show

4、 global status like Connections;Thread_cache_hits = (1 - Threads_created / connections ) * 100%(8)鎖定狀態(tài)mysql show global status like %lock%;Table_locks_waited/Table_locks_immediate=0.3% 如果這個比值比較大的話，說明表鎖造成的阻塞比較嚴(yán)重Innodb_row_lock_waits innodb行鎖，太大可能是間隙鎖造成的(9)復(fù)制延時量mysql show slave status查看延時時間(10) Tmp Ta

5、ble 狀況(臨時表狀況)mysql show status like Create_tmp%;Created_tmp_disk_tables/Created_tmp_tables比值最好不要超過10%，如果Created_tmp_tables值比較大，可能是排序句子過多或者是連接句子不夠優(yōu)化(11) Binlog Cache 使用狀況mysql show status like Binlog_cache%;如果Binlog_cache_disk_use值不為0 ，可能需要調(diào)大 binlog_cache_size大小(12) Innodb_log_waits 量mysql show statu

6、s like innodb_log_waits;Innodb_log_waits值不等于0的話，表明 innodb log buffer 因?yàn)榭臻g不足而等待性能指標(biāo)1 QPS計(jì)算(每秒查詢數(shù))針對MyISAM引擎為主的DBmysql show GLOBAL status like questions;+-+-+| Variable_name | Value |+-+-+| Questions | 2009191409 |+-+-+1 row in set (0.00 sec)mysql show global status like uptime;+-+-+| Variable_name |

7、Value |+-+-+| Uptime | 388402 |+-+-+1 row in set (0.00 sec)QPS=questions/uptime=5172，mysql自啟動以來的平均QPS，如果要計(jì)算某一時間段內(nèi)的QPS，可在高峰期間獲取間隔時間t2-t1，然后分別計(jì)算出t2和t1時刻的q值，QPS=(q2-q1)/(t2-t1)針對InnnoDB引擎為主的DBmysql show global status like com_update;+-+-+| Variable_name | Value |+-+-+| Com_update | 87094306 |+-+-+1 row

8、 in set (0.00 sec)mysql show global status like com_select;+-+-+| Variable_name | Value |+-+-+| Com_select | 1108143397 |+-+-+1 row in set (0.00 sec)mysql show global status like com_delete;+-+-+| Variable_name | Value |+-+-+| Com_delete | 379058 |+-+-+1 row in set (0.00 sec)mysql show global status

9、 like uptime;+-+-+| Variable_name | Value |+-+-+| Uptime | 388816 |+-+-+1 row in set (0.00 sec)QPS=(com_update+com_insert+com_delete+com_select)/uptime=3076,某一時間段內(nèi)的QPS查詢方法同上。2 TPS計(jì)算(每秒事務(wù)數(shù))mysql show global status like com_commit;+-+-+| Variable_name | Value |+-+-+| Com_commit | 7424815 |+-+-+1 row i

10、n set (0.00 sec)mysql show global status like com_rollback;+-+-+| Variable_name | Value |+-+-+| Com_rollback | 1073179 |+-+-+1 row in set (0.00 sec)mysql show global status like uptime;+-+-+| Variable_name | Value |+-+-+| Uptime | 389467 |+-+-+1 row in set (0.00 sec)TPS=(com_commit+com_rollback)/upt

11、ime=223 線程連接數(shù)和命中率mysql show global status like threads_%;+-+-+| Variable_name | Value |+-+-+| Threads_cached | 480 | /代表當(dāng)前此時此刻線程緩存中有多少空閑線程| Threads_connected | 153 | /代表當(dāng)前已建立連接的數(shù)量，因?yàn)橐粋€連接就需要一個線程，所以也可以看成當(dāng)前被使用的線程數(shù)| Threads_created | 20344 | /代表從最近一次服務(wù)啟動，已創(chuàng)建線程的數(shù)量| Threads_running | 2 | /代表當(dāng)前激活的（非睡眠狀態(tài)）線程

12、數(shù)+-+-+4 rows in set (0.00 sec)mysql show global status like Connections;+-+-+| Variable_name | Value |+-+-+| Connections | 381487397 |+-+-+1 row in set (0.00 sec)線程緩存命中率=1-Threads_created/Connections = 99.994%我們設(shè)置的線程緩存?zhèn)€數(shù)mysql show variables like %thread_cache_size%;+-+-+| Variable_name | Value |+-+-

13、+| thread_cache_size | 500 |+-+-+1 row in set (0.00 sec)根據(jù)Threads_connected可預(yù)估thread_cache_size值應(yīng)該設(shè)置多大，一般來說250是一個不錯的上限值，如果內(nèi)存足夠大，也可以設(shè)置成thread_cache_size值和threaads_connected值相同；或者通過觀察threads_created值，如果該值很大或一直在增長，可以適當(dāng)增加thread_cache_size的值；在休眠狀態(tài)下每個線程大概占用256KB左右的內(nèi)存，所以當(dāng)內(nèi)存足夠時，設(shè)置太小也不會節(jié)約太多內(nèi)存，除非該值已經(jīng)超過幾千。4 表緩

14、存mysql show global status like open_tables%;+-+-+| Variable_name | Value |+-+-+| Open_tables | 2228 |+-+-+1 row in set (0.00 sec)我們設(shè)置的打開表的緩存和表定義緩存mysql show variables like table_open_cache;+-+-+| Variable_name | Value |+-+-+| table_open_cache | 16384 |+-+-+1 row in set (0.00 sec)mysql show variables

15、 like table_defi%;+-+-+| Variable_name | Value |+-+-+| table_definition_cache | 2000 |+-+-+1 row in set (0.00 sec)針對MyISAM：mysql每打開一個表，都會讀入一些數(shù)據(jù)到table_open_cache 緩存 中，當(dāng)mysql在這個緩存中找不到相應(yīng)的信息時，才會去磁盤上直接讀取，所以該值要設(shè)置得足夠大以避免需要重新打開和重新解析表的定義，一般設(shè)置為max_connections的10倍，但最好保持在10000以內(nèi)。還有種依據(jù)就是根據(jù)狀態(tài)open_tables的值進(jìn)行設(shè)置，如果發(fā)

16、現(xiàn)open_tables的值每秒變化很大，那么可能需要增大table_open_cache的值。table_definition_cache 通常簡單設(shè)置為服務(wù)器中存在的表的數(shù)量，除非有上萬張表。針對InnoDB：與MyISAM不同，InnoDB的open table和open file并無直接聯(lián)系，即打開frm表時其相應(yīng)的ibd文件可能處于關(guān)閉狀態(tài)；故InnoDB只會用到table_definiton_cache，不會使用table_open_cache；其frm文件保存于table_definition_cache中，而idb則由innodb_open_files決定(前提是開啟了inno

17、db_file_per_table)，最好將innodb_open_files設(shè)置得足夠大，使得服務(wù)器可以保持所有的.ibd文件同時打開。5 最大連接數(shù)mysql show global status like Max_used_connections;+-+-+| Variable_name | Value |+-+-+| Max_used_connections | 1785 |+-+-+1 row in set (0.00 sec)我們設(shè)置的max_connections大小mysql show variables like max_connections%;+-+-+| Variabl

18、e_name | Value |+-+-+| max_connections | 4000 |+-+-+1 row in set (0.00 sec)通常max_connections的大小應(yīng)該設(shè)置為比Max_used_connections狀態(tài)值大，Max_used_connections狀態(tài)值反映服務(wù)器連接在某個時間段是否有尖峰，如果該值大于max_connections值，代表客戶端至少被拒絕了一次，可以簡單地設(shè)置為符合以下條件：Max_used_connections/max_connections=0.86 Innodb 緩存命中率mysql show global status l

19、ike innodb_buffer_pool_read%;+-+-+| Variable_name | Value |+-+-+| Innodb_buffer_pool_read_ahead_rnd | 0 | Innodb_buffer_pool_read_ahead | 268720 | /預(yù)讀的頁數(shù)| Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 480291074970 | /從緩沖池中讀取的次數(shù)| Innodb_buffer_pool_reads | 29912739

20、| /表示從物理磁盤讀取的頁數(shù)+-+-+5 rows in set (0.00 sec)緩沖池命中率 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)=99.994%如果該值小于99.9%，建議就應(yīng)該增大innodb_buffer_pool_size的值了，該值一般設(shè)置為內(nèi)存總大小的75%-85%，或者計(jì)算出操作系統(tǒng)所需緩存+mysql每個連接所需的內(nèi)存（例如排序緩沖和臨時表）+MyI

21、SAM鍵緩存，剩下的內(nèi)存都給innodb_buffer_pool_size，不過也不宜設(shè)置太大，會造成內(nèi)存的頻繁交換，預(yù)熱和關(guān)閉時間長等問題。7 MyISAM Key Buffer命中率和緩沖區(qū)使用率mysql show global status like key_%;+-+-+| Variable_name | Value |+-+-+| Key_blocks_not_flushed | 0 | Key_blocks_unused | 106662 | Key_blocks_used | 107171 | Key_read_requests | 883825678 | Key_reads

22、| 133294 | Key_write_requests | 217310758 | Key_writes | 2061054 |+-+-+7 rows in set (0.00 sec)mysql show variables like %key_cache_block_size%;+-+-+| Variable_name | Value |+-+-+| key_cache_block_size | 1024 |+-+-+1 row in set (0.00 sec)mysql show variables like %key_buffer_size%;+-+-+| Variable_na

23、me | Value |+-+-+| key_buffer_size | 134217728 |+-+-+1 row in set (0.00 sec)緩沖區(qū)的使用率=1-(Key_blocks_unused*key_cache_block_size/ key_buffer_size)=18.6%讀命中率=1-Key_reads /Key_read_requests=99.98%寫命中率=1-Key_writes / Key_write_requests =99.05%可看到緩沖區(qū)的使用率并不高，如果很長一段時間后還沒有使用完所有的鍵緩沖，可以考慮把緩沖區(qū)調(diào)小一點(diǎn)。鍵緩存命中率可能意義不大，因

24、為它和應(yīng)用相關(guān)，有些應(yīng)用在95%的命中率下就工作良好，有些則需要99.99%，所以從經(jīng)驗(yàn)上看，每秒的緩存未命中次數(shù)更重要，假設(shè)一個獨(dú)立磁盤每秒能做100個隨機(jī)讀，那么每秒有5個緩沖未命中可能不會導(dǎo)致I/O繁忙，但每秒80個就可能出現(xiàn)問題。每秒緩存未命中=Key_reads/uptime=0.338 臨時表使用情況mysql show global status like Created_tmp%;+-+-+| Variable_name | Value |+-+-+| Created_tmp_disk_tables | 19226325 | Created_tmp_files | 117 |

25、Created_tmp_tables | 56265812 |+-+-+3 rows in set (0.00 sec)mysql show variables like %tmp_table_size%;+-+-+| Variable_name | Value |+-+-+| tmp_table_size | 67108864 |+-+-+1 row in set (0.00 sec)可看到總共創(chuàng)建了56265812 張臨時表，其中有19226325 張涉及到了磁盤IO，大概比例占到了0.34，證明數(shù)據(jù)庫應(yīng)用中排序，join語句涉及的數(shù)據(jù)量太大，需要優(yōu)化SQL或者增大tmp_table_si

26、ze的值，我設(shè)的是64M。該比值應(yīng)該控制在0.2以內(nèi)。9 binlog cache使用情況mysql show status like Binlog_cache%; +-+-+| Variable_name | Value |+-+-+| Binlog_cache_disk_use | 15 | Binlog_cache_use | 95978256 |+-+-+2 rows in set (0.00 sec)mysql show variables like binlog_cache_size;+-+-+| Variable_name | Value |+-+-+| binlog_cache

27、_size | 1048576 |+-+-+1 row in set (0.00 sec)Binlog_cache_disk_use表示因?yàn)槲覀僢inlog_cache_size設(shè)計(jì)的內(nèi)存不足導(dǎo)致緩存二進(jìn)制日志用到了臨時文件的次數(shù)Binlog_cache_use 表示 用binlog_cache_size緩存的次數(shù)當(dāng)對應(yīng)的Binlog_cache_disk_use 值比較大的時候 我們可以考慮適當(dāng)?shù)恼{(diào)高 binlog_cache_size 對應(yīng)的值10 Innodb log buffer size的大小設(shè)置mysql show variables like %innodb_log_buffer

28、_size%;+-+-+| Variable_name | Value |+-+-+| innodb_log_buffer_size | 8388608 |+-+-+1 row in set (0.00 sec)mysql show status like innodb_log_waits;+-+-+| Variable_name | Value |+-+-+| Innodb_log_waits | 0 |+-+-+1 row in set (0.00 sec)innodb_log_buffer_size我設(shè)置了8M，應(yīng)該足夠大了；Innodb_log_waits表示因log buffer不足

29、導(dǎo)致等待的次數(shù)，如果該值不為0，可以適當(dāng)增大innodb_log_buffer_size的值。11 表掃描情況判斷mysql show global status like Handler_read%;+-+-+| Variable_name | Value |+-+-+| Handler_read_first | 19180695 | Handler_read_key | 30303690598 | Handler_read_last | 290721 | Handler_read_next | 51169834260 | Handler_read_prev | 1267528402 | H

30、andler_read_rnd | 219230406 | Handler_read_rnd_next | 344713226172 |+-+-+7 rows in set (0.00 sec)Handler_read_first：使用索引掃描的次數(shù)，該值大小說不清系統(tǒng)性能是好是壞Handler_read_key：通過key進(jìn)行查詢的次數(shù)，該值越大證明系統(tǒng)性能越好Handler_read_next：使用索引進(jìn)行排序的次數(shù)Handler_read_prev：此選項(xiàng)表明在進(jìn)行索引掃描時，按照索引倒序從數(shù)據(jù)文件里取數(shù)據(jù)的次數(shù)，一般就是ORDER BY . DESCHandler_read_rnd：該

31、值越大證明系統(tǒng)中有大量的沒有使用索引進(jìn)行排序的操作，或者join時沒有使用到indexHandler_read_rnd_next：使用數(shù)據(jù)文件進(jìn)行掃描的次數(shù)，該值越大證明有大量的全表掃描，或者合理地創(chuàng)建索引，沒有很好地利用已經(jīng)建立好的索引12 Innodb_buffer_pool_wait_freemysql show global status like Innodb_buffer_pool_wait_free;+-+-+| Variable_name | Value |+-+-+| Innodb_buffer_pool_wait_free | 0 |+-+-+1 row in set (0.00 sec)該值不為0表示buffer pool沒有空閑的空間了，可能原因是innodb_buffer_pool_size設(shè)置太大，可以適當(dāng)減少該值。13 join操作信息mysq