MySQL優(yōu)化服務(wù)器配置

我們配置了MySQL的變量，如何知道使用情況，是否需要調(diào)整變量大小，MySQL的系統(tǒng)變量和狀態(tài)變量經(jīng)常是有對應(yīng)的，比如系統(tǒng)變量Max_connections，在狀態(tài)變量是Max_used_connections。這里我們結(jié)合showvariables和showstatus來查看。1?1配置基礎(chǔ)知識Mysql的變量分為系統(tǒng)變量和自定義變量，系統(tǒng)變量又分為全局變量和會(huì)話變量；下面的語句顯示了幾種不同的設(shè)置會(huì)話和全局變量的方法：SETsort_buffer_size=<value>;SETGLOBALsort_buffer_size=<value>;SET@@sort_buffer_size:=<value>;SET@@session.sort_buffer_size:=<value>;SET@@global.sort_buffer_size:=<value>;SET@a:=<value>;注意：單個(gè)@表示自定義變量，@@表示系統(tǒng)變量。1.2通用調(diào)優(yōu)原則鍵緩存key_buffer_sizeMyISAM鍵緩存也被叫做鍵緩沖區(qū)。這里假設(shè)只使用MyISAM存儲引擎，key_buffer_size的值應(yīng)該占到所有保留內(nèi)存的25%到50%。計(jì)算緩存命中率：100-((Key_reads*100)/Key_read_requests)mysql>showglobalstatuslike'Key%';計(jì)算緩存使用情況：100-((Key_blocks_unused*key_cache_block_size)*100/key_buffer_size)mysql>showglobalstatuslike'Key%';mysql>showvariableslike'key%';計(jì)算每秒內(nèi)緩存未命中的數(shù)量：Key_reads/Uptimemysql>showglobalstatuslike'Key%';mysql>showglobalstatuslike'Uptime';或者使用mysqladmin計(jì)算每10秒未命中的次數(shù)：$mysqladminextended-status-r-i10|grepKey_reads在決定給鍵緩存分配多少內(nèi)存的時(shí)候，知道MyISAM索引使用了多少磁盤空間會(huì)比較有幫助。沒有必要讓鍵緩存大于數(shù)據(jù)的大小。如果使用Unix，可以使用下面的命令找出存儲索引的文件大?。?du-sch、find/path/to/mysql/data/directory/-name"*.MYI”、InnoDB緩沖池innodb_buffer_pool_^-izeMySQL手冊建議在專用服務(wù)器上把80%的物理內(nèi)存分配給緩沖池。mysql>showvariableslike'innodb_buffer%';mysql>showstatuslike'innodb_buffer%';mysql>showengineinnodbstatus;或者使用innotop工具來監(jiān)視InnoDB緩沖池的性能和內(nèi)存使用情況。Binlog_cache_disk_use和binlog_cache_usemysql>showglobalstatuslike'binlog_cache%';mysql>showglobalvariableslike'binlog_cache%';如果binlog_cache_disk_use和binlog_cache_use之間的比率很大，那么就應(yīng)該增加binlog_cache_size的值。大部分事務(wù)最好都落在二進(jìn)制日志緩存里面，但是偶爾有一個(gè)發(fā)生的磁盤上也無妨。沒有非常確定方式可以減少二進(jìn)制日志緩存未中。最好的辦法是增加binlog_cache_size并且觀察緩存未中率是否下降了。一旦未中率下降到了某一個(gè)點(diǎn)，就不會(huì)再從加大緩存中受益。假設(shè)每秒的未中是一個(gè)，并且增加了緩存的大小。讓未中減少了到每分鐘一次。這已經(jīng)足夠好了，不可能再讓它下降，即使是下降了，也不會(huì)再從中得到更多的好處，所以還不如把內(nèi)存節(jié)約下來做別的事情。線程緩存7%reod_cocHe_sizeThread_cache_size定義了MySQL能在緩存中保存的線程數(shù)量。除非服務(wù)器有很多連接請求，否則就不需要改變這個(gè)變量的值?？梢酝ㄟ^觀察threads_created變量的值，以確定線程緩沖是否足夠的。設(shè)置這個(gè)變量不會(huì)立即生效，生效被延遲到了下一次線程關(guān)閉的時(shí)候。在那時(shí)，MySQL檢查緩存中是否有空間存儲線程。如果是，它會(huì)把線程緩存起來，供另外一個(gè)連接使用。如果不是，它會(huì)直接結(jié)束掉線程。在這種情況下，緩存中線程的數(shù)量，以及線程緩存使用的內(nèi)存數(shù)量不會(huì)立即就下降。只有當(dāng)新連接為了使用線程把它從緩存中移走的時(shí)候才會(huì)看到下降。（MySQL只有在連接關(guān)閉的時(shí)候才會(huì)把線程加入緩存，也只有在創(chuàng)建新連接的時(shí)候才從緩存中移除線程。）mysql>showglobalvariableslike'Thread%';mysql>showstatuslike'Thread%';表緩存table_open_cache設(shè)置這個(gè)變量不會(huì)立即生效，要等到下一個(gè)線程打開表的時(shí)候才會(huì)生效。當(dāng)它生效的時(shí)候，MySQL會(huì)檢查變量的值。如果值大于緩存中表的數(shù)量，線程就可以把新打開的表插入到緩存中。如果值小于緩存中表的數(shù)量，MySQL就會(huì)把從緩存中刪除掉沒有使用的表。mysql>showglobalvariableslike'table_open_cache';mysql>showglobalstatuslike'open%tables%';如果opened_tables的值很大或者正在上升，那就說明表緩存不夠大，應(yīng)該增加系統(tǒng)變量table_cache的值（在MySQL中是table_open_cache）。將表緩存變得很大的唯一壞處就是在有很多MylSAM表的時(shí)候，它會(huì)導(dǎo)致較長的關(guān)閉時(shí)間，因?yàn)橐獩_刷鍵數(shù)據(jù)塊，而且表要被標(biāo)記為不再打開。出于同樣的原因，它也會(huì)導(dǎo)致FLUSHTABLESWITHREADLOCK需要較長時(shí)間才能完成。查詢緩存羿e邛_cocAe_sizemysql>showvariableslike'query_cache%';mysql>showstatuslike'qcache%';在服務(wù)器啟動(dòng)的時(shí)候，MySQL會(huì)為查詢緩存一次性分配變量所定義數(shù)量的內(nèi)存。如果更新了變量（即使是把值設(shè)置為當(dāng)前值），MySQL會(huì)立即刪除掉所有緩存的查詢，重新把緩存設(shè)置為定義的大小，并且重新初始化緩存的內(nèi)存。1.3檢測MySQL服務(wù)器狀態(tài)變量Abo^rted_clientsmysql>showglobalstatuslike'Aborted_clients';如果這個(gè)變量隨時(shí)間增加，那么就要確定是否優(yōu)雅地關(guān)閉了連接。如果不是，那就要檢查網(wǎng)絡(luò)性能，并且檢查max_allowed-packet配置變量，超過了max_allowed_packet的查詢會(huì)被強(qiáng)制地中斷。Aborted_connectsmysql>showglobalstatuslike'Aborted_connects';這個(gè)值應(yīng)該接近于0.不是的話，就可能是網(wǎng)絡(luò)問題。有幾個(gè)被中斷的連接是正常的。例如，當(dāng)某些人試著從錯(cuò)誤的主機(jī)連接、使用了錯(cuò)誤的用戶名和密碼，或者定義了無效的數(shù)據(jù)庫，就會(huì)發(fā)生這樣的情況。Bytes_received和bytes_sentmysql>showglobalstatuslike'Bytes%';這兩個(gè)值可以幫助你考察問題是有發(fā)送服務(wù)器的數(shù)據(jù)過多引起的，還是從服務(wù)器讀取的數(shù)據(jù)太多引起的。它們也許會(huì)指出代碼中其他的問題，比如查詢提取了超出自己需要的數(shù)據(jù)。Com_*mysql>showglobalstatuslike'Com_%';應(yīng)該注意不要讓諸如Com_rollback這樣不常見的變量的值超過預(yù)期值。一種檢查合理的值的快捷的方式是innotop的命令總結(jié)模式。Connectionsmysql>showglobalstatuslike'Connections';這個(gè)變量表示了連接意圖的數(shù)量（不是當(dāng)前連接的數(shù)量，它是threads_connected）。如果它的值快速增加，例如，每秒幾百，那么就應(yīng)該檢查連接次或調(diào)整操作系統(tǒng)的網(wǎng)絡(luò)堆棧。Created_fmp_disk_fablesmysql>showglobalstatuslike'%tmp%';如果這個(gè)值較高，有兩件事情可能發(fā)生了錯(cuò)誤：查詢在選擇BLOB或TEXT列的時(shí)候創(chuàng)建了臨時(shí)表；tmp_table_size和max_heap_table_size可能不夠大。Create_fmpjablesmysql>showglobalstatuslike'%tmp%';該值較高的唯一處理辦法是優(yōu)化查詢。Handler_read_rnd_nextmysql>showglobalstatuslike'handler_read%';Handler_read_rnd_next/handler_read_rnd顯示了全表掃描的大致平均值。如果值較大，那么就應(yīng)該優(yōu)化架構(gòu)、索引和查詢。Max_used_connectionsmysql>showglobalvariableslike'Max_connections';mysql>showglobalstatuslike'Max_used_connections';如果該值和max_connections相同，那么可能是max_connections被設(shè)置的過低或者最大負(fù)載超過了服務(wù)器的上限。但是不要假設(shè)應(yīng)該增加max_connections。它是保證服務(wù)器不會(huì)被太多負(fù)載壓垮的警戒線。如果看到需求激增，那么就應(yīng)該檢查應(yīng)用程序的行為是不是正常、服務(wù)器調(diào)優(yōu)是否正確、服務(wù)器架構(gòu)是否設(shè)計(jì)良好。這都比簡單地增加max_connections要好。Open_filesmysql>showglobalvariableslike'open_files_limit';mysql>showglobalstatuslike'Open_files';注意它不應(yīng)該和open_file_limit的值接近。如果接近了，那么就應(yīng)該增加open_files_limit。Select_full_joinmysql>showglobalstatuslike'Select_full_join';全聯(lián)接是無索引聯(lián)接，它是真正的性能殺手。最好能避免全聯(lián)接，即使是每分鐘一次也太多了。如果聯(lián)接沒有索引，那么最好能優(yōu)化查詢和索引。Select_full_range_joinmysql>showglobalstatuslike'Select_full_range_join';如果該變量過高，那么就說明運(yùn)行了許多使用了范圍查詢聯(lián)接表。范圍查詢比較慢，同時(shí)也是一個(gè)較好的優(yōu)化點(diǎn)。Select_range_pheckmysql>showglobalstatuslike'Select_range_check';該變量記錄了在聯(lián)接時(shí)，對每一行數(shù)據(jù)重新檢查索引的查詢計(jì)劃的數(shù)量，它的性能開銷很大。如果該值較高或正在增加，那么就意味著一些查詢沒有找到好索引。Slowjaunchjhreadsmysql>showglobalstatuslike'Slow_launch_threads';該變量較大說明了某些因素正在延遲聯(lián)接的新線程。它說明了服務(wù)器有一些問題，但是它并沒有說明真正的原因。它通常表示系統(tǒng)過載，導(dǎo)致操作系統(tǒng)不能給新創(chuàng)建的線程分配時(shí)間片。Sort_merge^assesmysql>showglobalstatuslike'Sort_merge_passes';該變量較大說明應(yīng)該增加sort_buffer_size，也許僅僅只是為某些查詢。檢查查詢并且查明哪一個(gè)導(dǎo)致了文件排序。最好的辦法是優(yōu)化查詢。Table_locks_waitedmysql>showglobalstatuslike'Table_locks_waited';該變量顯示了有多少表被鎖住了并且導(dǎo)致了服務(wù)器級的鎖等待（等待存儲引擎鎖，比如innodb的行級鎖，不會(huì)使該變量增加）。如果這個(gè)值較高并且正在增加，那么就說明了嚴(yán)重的并發(fā)瓶頸。這時(shí)候就應(yīng)該考慮下面的優(yōu)化手段：使用InnoDB或另外的使用了行級鎖定的存儲引擎;手動(dòng)對大表進(jìn)行分區(qū)或者使用MySQL5.1或以上版本的內(nèi)建分區(qū)機(jī)制;優(yōu)化查詢；啟用并發(fā)插入或者對鎖定設(shè)置進(jìn)行優(yōu)化。MySQL不會(huì)顯示等待時(shí)間是多長。在寫本書的時(shí)候，最好的辦法也許是使用微秒粒度的慢速查詢?nèi)罩尽?.4每連接（per-connections）設(shè)置調(diào)優(yōu)除非確信自己是正確的，否則就不要全局性地增加每連接設(shè)置的值。即使不需要某些緩存，它們也會(huì)一次性地被分配好。所以龐大的全局設(shè)置可能會(huì)成為巨大的浪費(fèi)。相反，應(yīng)該是查詢需要的時(shí)候才增加設(shè)置的值。對于平時(shí)需要保持較小值，只在需要