MySQL的Innodb緩存相關(guān)優(yōu)化
-
admin
-
無論是對于哪一種數(shù)據(jù)庫來說，緩存技術(shù)都是提高數(shù)據(jù)庫性能的關(guān)鍵技術(shù)，物理磁盤的訪問速度永 遠(yuǎn)都會(huì)與內(nèi)存的訪問速度永遠(yuǎn)都不是一個(gè)數(shù)量級的。通過緩存技術(shù)無論是在讀還是寫方面都可以大大提 高數(shù)據(jù)庫整體性能。
Innodb_buffer_pool_size 的合理設(shè)置
Innodb 存儲(chǔ)引擎的緩存機(jī)制和 MyISAM 的最大區(qū)別就在于 Innodb 不僅僅緩存索引，同時(shí)還會(huì)緩存實(shí) 際的數(shù)據(jù)。所以，完全相同的數(shù)據(jù)庫，使用 Innodb 存儲(chǔ)引擎可以使用更多的內(nèi)存來緩存數(shù)據(jù)庫相關(guān)的信 息，當(dāng)然前提是要有足夠的物理內(nèi)存。這對于在現(xiàn)在這個(gè)內(nèi)存價(jià)格不斷降低的時(shí)代，無疑是個(gè)很吸引人 的特性。
innodb_buffer_pool_size 參數(shù)用來設(shè)置 Innodb 最主要的 Buffer(Innodb_Buffer_Pool)的大小，也 就是緩存用戶表及索引數(shù)據(jù)的最主要緩存空間，對 Innodb 整體性能影響也最大。無論是 MySQL 官方手冊 還是網(wǎng)絡(luò)上很多人所分享的 Innodb 優(yōu)化建議，都簡單的建議將 Innodb 的 Buffer Pool 設(shè)置為整個(gè)系統(tǒng) 物理內(nèi)存的 50% ～ 80% 之間。如此輕率的給出此類建議，我個(gè)人覺得實(shí)在是有些不妥。
不管是多么簡單的參數(shù)，都可能與實(shí)際運(yùn)行場景有很大的關(guān)系。完全相同的設(shè)置，不同的場景下的 表現(xiàn)可能相差很大。就從 Innodb 的 Buffer Pool 到底該設(shè)置多大這個(gè)問題來看，我們首先需要確定的是 這臺(tái)主機(jī)是不是就只提供 MySQL 服務(wù)？MySQL 需要提供的的最大連接數(shù)是多少？MySQL 中是否還有 MyISAM 等其他存儲(chǔ)引擎提供服務(wù)？如果有，其他存儲(chǔ)引擎所需要使用的 Cache 需要多大？
假設(shè)是一臺(tái)單獨(dú)給 MySQL 使用的主機(jī)，物理內(nèi)存總大小為 8G，MySQL 最大連接數(shù)為 500，同時(shí)還使用 了 MyISAM 存儲(chǔ)引擎，這時(shí)候我們的整體內(nèi)存該如何分配呢？內(nèi)存分配為如下幾大部分：a) 系統(tǒng)使用，假設(shè)預(yù)留 800M；b) 線程獨(dú)享，約 2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB)，組成大概如下:sort_buffer_size：1MB join_buffer_size：1MB read_buffer_size：1MB read_rnd_buffer_size：512KB thread_statck：512KBc) MyISAM Key Cache，假設(shè)大概為 1.5GB；d) Innodb Buffer Pool 最大可用量：8GB - 800MB - 2GB - 1.5GB = 3.7GB；
假設(shè)這個(gè)時(shí)候我們還按照 50%～80%的建議來設(shè)置，最小也是 4GB，而通過上面的估算，最大可用值 在 3.7GB 左右，那么很可能在系統(tǒng)負(fù)載很高當(dāng)線程獨(dú)享內(nèi)存差不多出現(xiàn)極限情況的時(shí)候，系統(tǒng)很可能就 會(huì)出現(xiàn)內(nèi)存不足的問題了。而且上面還僅僅只是列出了一些使用內(nèi)存較大的地方，如果進(jìn)一步細(xì)化，很 可能可用內(nèi)存會(huì)更少。上面只是一個(gè)簡單的示例分析，實(shí)際情況并不一定是這樣的，這里只是希望大家了解，在設(shè)置一些 參數(shù)的時(shí)候，千萬不要想當(dāng)然，一定要詳細(xì)的分析可能出現(xiàn)的情況，然后再通過不斷測試調(diào)整來達(dá)到自 己所處環(huán)境的最優(yōu)配置。就我個(gè)人而言，正式環(huán)境上線之初，我一般都會(huì)采取相對保守的參數(shù)配置策 略。上線之后，再根據(jù)實(shí)際情況和收集到的各種性能數(shù)據(jù)進(jìn)行針對性的調(diào)整。
當(dāng)系統(tǒng)上線之后，我們可以通過 Innodb 存儲(chǔ)引擎提供給我們的關(guān)于 Buffer Pool 的實(shí)時(shí)狀態(tài)信息作 出進(jìn)一步分析，來確定系統(tǒng)中 Innodb 的 Buffer Pool 使用情況是否正常高效：sky@localhost : example 08:47:54> show status like 'Innodb_buffer_pool_%';+-----------------------------------+-------+| Variable_name | Value |+-----------------------------------+-------+| Innodb_buffer_pool_pages_data | 70 || Innodb_buffer_pool_pages_dirty | 0 || Innodb_buffer_pool_pages_flushed | 0 || Innodb_buffer_pool_pages_free | 1978 || Innodb_buffer_pool_pages_latched | 0 || Innodb_buffer_pool_pages_misc | 0 || Innodb_buffer_pool_pages_total | 2048 || Innodb_buffer_pool_read_ahead_rnd | 1 || Innodb_buffer_pool_read_ahead_seq | 0 || Innodb_buffer_pool_read_requests | 329 || Innodb_buffer_pool_reads | 19 || Innodb_buffer_pool_wait_free | 0 || Innodb_buffer_pool_write_requests | 0 |+-----------------------------------+-------+
從上面的值我們可以看出總共 2048 pages，還有 1978 是 Free 狀態(tài)的僅僅只有 70 個(gè) page 有數(shù)據(jù)， read 請求 329 次，其中有 19 次所請求的數(shù)據(jù)在 buffer pool 中沒有，也就是說有 19 次是通過讀取物理 磁盤來讀取數(shù)據(jù)的，所以很容易也就得出了 Innodb Buffer Pool 的 Read 命中率大概在為：(329 - 19)/ 329 * 100% = 94.22%。
當(dāng)然，通過上面的數(shù)據(jù)，我們還可以分析出 write 命中率，可以得到發(fā)生了多少次 read_ahead_rnd，多少次 read_ahead_seq，發(fā)生過多少次 latch，多少次因?yàn)?Buffer 空間大小不足而產(chǎn) 生 wait_free 等等。
單從這里的數(shù)據(jù)來看，我們設(shè)置的 Buffer Pool 過大，僅僅使用 70 / 2048 * 100% = 3.4%。
在 Innodb Buffer Pool 中，還有一個(gè)非常重要的概念，叫做“預(yù)讀”。一般來說，預(yù)讀概念主要是 在一些高端存儲(chǔ)上面才會(huì)有，簡單來說就是通過分析數(shù)據(jù)請求的特點(diǎn)來自動(dòng)判斷出客戶在請求當(dāng)前數(shù)據(jù) 塊之后可能會(huì)繼續(xù)請求的數(shù)據(jù)快。通過該自動(dòng)判斷之后，存儲(chǔ)引擎可能就會(huì)一次將當(dāng)前請求的數(shù)據(jù)庫和 后面可能請求的下一個(gè)（或者幾個(gè)）數(shù)據(jù)庫一次全部讀出，以期望通過這種方式減少磁盤 IO 次數(shù)提高 IO 性能。在上面列出的狀態(tài)參數(shù)中就有兩個(gè)專門針對預(yù)讀：Innodb_buffer_pool_read_ahead_rnd，記錄進(jìn)行隨機(jī)讀的時(shí)候產(chǎn)生的預(yù)讀次數(shù)； Innodb_buffer_pool_read_ahead_seq，記錄連續(xù)讀的時(shí)候產(chǎn)生的預(yù)讀次數(shù)；
innodb_log_buffer_size 參數(shù)的使用
顧名思義，這個(gè)參數(shù)就是用來設(shè)置 Innodb 的 Log Buffer 大小的，系統(tǒng)默認(rèn)值為 1MB。Log Buffer 的主要作用就是緩沖 Log 數(shù)據(jù)，提高寫 Log 的 IO 性能。一般來說，如果你的系統(tǒng)不是寫負(fù)載非常高且以 大事務(wù)居多的話，8MB 以內(nèi)的大小就完全足夠了。
我們也可以通過系統(tǒng)狀態(tài)參數(shù)提供的性能統(tǒng)計(jì)數(shù)據(jù)來分析 Log 的使用情況：
sky@localhost : example 10:11:05> show status like 'innodb_log%';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| Innodb_log_waits | 0 || Innodb_log_write_requests | 6 || Innodb_log_writes | 2 |+---------------------------+-------+通過這三個(gè)狀態(tài)參數(shù)我們可以很清楚的看到 Log Buffer 的等待次數(shù)等性能狀態(tài)。
當(dāng)然，如果完全從 Log Buffer 本身來說，自然是大一些會(huì)減少更多的磁盤 IO。但是由于 Log 本身是 為了保護(hù)數(shù)據(jù)安全而產(chǎn)生的，而 Log 從 Buffer 到磁盤的刷新頻率和控制數(shù)據(jù)安全一致的事務(wù)直接相關(guān)， 并且也有相關(guān)參數(shù)來控制（innodb_flush_log_at_trx_commit），所以關(guān)于 Log 相關(guān)的更詳細(xì)的實(shí)現(xiàn)機(jī) 制和優(yōu)化在后面的“事務(wù)優(yōu)化”中再做更詳細(xì)的分析，這里就不展開了。
innodb_additional_mem_pool_size 參數(shù)理解
innodb_additional_mem_pool_size 所設(shè)置的是用于存放 Innodb 的字典信息和其他一些內(nèi)部結(jié)構(gòu)所 需要的內(nèi)存空間。所以我們的 Innodb 表越多，所需要的空間自然也就越大，系統(tǒng)默認(rèn)值僅有 1MB。當(dāng) 然，如果 Innodb 實(shí)際運(yùn)行過程中出現(xiàn)了實(shí)際需要的內(nèi)存比設(shè)置值更大的時(shí)候，Innodb 也會(huì)繼續(xù)通過 OS 來申請內(nèi)存空間，并且會(huì)在 MySQL 的錯(cuò)誤日志中記錄一條相應(yīng)的警告信息讓我們知曉。
從我個(gè)人的經(jīng)驗(yàn)來看，一個(gè)常規(guī)的幾百個(gè) Innodb 表的 MySQL，如果不是每個(gè)表都是上百個(gè)字段的 話，20MB 內(nèi)存已經(jīng)足夠了。當(dāng)然，如果你有足夠多的內(nèi)存，完全可以繼續(xù)增大這個(gè)值的設(shè)置。實(shí)際上， innodb_additional_mem_pool_size 參數(shù)對系統(tǒng)整體性能并無太大的影響，所以只要能存放需要的數(shù)據(jù)即 可，設(shè)置超過實(shí)際所需的內(nèi)存并沒有太大意義，只是浪費(fèi)內(nèi)存而已。
Double Write Buffer
Double Write Buffer 是 Innodb 所使用的一種較為獨(dú)特的文件 Flush 實(shí)現(xiàn)技術(shù)，主要做用是為了通 過減少文件同步次數(shù)提高 IO 性能的情況下，提高系統(tǒng) Crash 或者斷電情況下數(shù)據(jù)的安全性，避免寫入的 數(shù)據(jù)不完整。
一般來說，Innodb 在將數(shù)據(jù)同步到數(shù)據(jù)文件進(jìn)行持久化之前，首先會(huì)將需要同步的內(nèi)容寫入存在于表空間中的系統(tǒng)保留的存儲(chǔ)空間，也就是被我們稱之為 Double Write Buffer 的地方，然后再將數(shù)據(jù)進(jìn) 行文件同步。所以實(shí)質(zhì)上，Double Write Buffer 中就是存放了一份需要同步到文件中數(shù)據(jù)的一個(gè)備份， 以便在遇到系統(tǒng) Crash 或者主機(jī)斷電的時(shí)候，能夠校驗(yàn)最后一次文件同步是否準(zhǔn)確的完成了，如果未完 成，則可以通過這個(gè)備份來繼續(xù)完成工作，保證數(shù)據(jù)的正確性。
那這樣 Innodb 不是又一次增加了整體 IO 量了嗎？這樣不是可能會(huì)影響系統(tǒng)的性能么？這個(gè)完全不用 太擔(dān)心，因?yàn)?Double Write Buffer 是一塊連續(xù)的磁盤空間，所有寫入 Double Write Buffer 的操作都是 連續(xù)的順序?qū)懭氩僮?，與整個(gè)同步過程相比，這點(diǎn) IO 消耗所占的比例是非常小的。為了保證數(shù)據(jù)的準(zhǔn)確 性，這樣一點(diǎn)點(diǎn)性能損失是完全可以接受的。
實(shí)際上，并不是所有的場景都需要使用 Double Write 這樣的機(jī)制來保證數(shù)據(jù)的安全準(zhǔn)確性，比如當(dāng) 我們使用某些特別文件系統(tǒng)的時(shí)候，如在 Solaris 平臺(tái)上非常著名的 ZFS 文件系統(tǒng)，他就可以自己保證文 件寫入的完整性。而且在我們的 Slave 端，也可以禁用 Double Write 機(jī)制。
Adaptive Hash Index
在 Innodb 中，實(shí)現(xiàn)了一個(gè)自動(dòng)監(jiān)測各表索引的變化情況的機(jī)制，然后通過一系列的算法來判定如果 存在一個(gè) Hash Index 是否會(huì)對索引搜索帶來性能改善。如果 Innodb 認(rèn)為可以通過 Hash Index 來提高檢 索效率，他就會(huì)在內(nèi)部自己建立一個(gè)基于某個(gè) B-Tree 索引的 Hash Index，而且會(huì)根據(jù)該 B-Tree 索引的 變化自行調(diào)整，這就是我們常說的 Adaptive Hash Index。當(dāng)然，Innodb 并不一定會(huì)將整個(gè) B-Tree 索引 完全的轉(zhuǎn)換為 Hash Index，可能僅僅只是取用該 B-Tree 索引鍵一定長度的前綴來構(gòu)造一個(gè) Hash Index。
Adaptive Hash Index 并不會(huì)進(jìn)行持久化存放在磁盤上面，僅僅存在于 Buffer Pool 中。所以，在每 次 MySQL 剛啟動(dòng)之后是并不存在 Adaptive Hash Index 的，只有在停工服務(wù)之后，Innodb 才會(huì)根據(jù)相應(yīng) 的請求來構(gòu)建。
Adaptive Hash Index 的目的并不是為了改善磁盤 IO 的性能，而是為了提高 Buffer Pool 中的數(shù)據(jù) 的訪問效率，說的更淺顯一點(diǎn)就是給 Buffer Pool 中的數(shù)據(jù)做的索引。所以，Innodb 在具有大容量內(nèi)存（可以設(shè)置大的 Buffer Pool）的主機(jī)上，對于其他存儲(chǔ)引擎來說，會(huì)存在一定的性能優(yōu)勢。
關(guān)注：1
帖子：23
