有時會碰到這樣的情況,一條 SQL 在平時執(zhí)行沒問題,很快。但是突然某個時間執(zhí)行的就會很慢,而且這種場景并不能復現(xiàn),只能隨機發(fā)送的。
SQL 執(zhí)行突然變慢的原因
在之前講解 MySQL Redo log 時,說到了 WAL 機制,為了保證 MySQL 更新的速度,在進行更新操作時,先將更新內(nèi)容寫入 redo log,后續(xù)系統(tǒng)空閑時,再將 redo log 的內(nèi)容應(yīng)用到磁盤。
當內(nèi)存數(shù)據(jù)頁(redo log)和磁盤數(shù)據(jù)頁內(nèi)容不一致時,將該內(nèi)存也稱為 “臟頁”。將內(nèi)存數(shù)據(jù)寫入到磁盤后,數(shù)據(jù)一致,內(nèi)存頁稱為 "干凈頁"。
在內(nèi)存數(shù)據(jù)寫入磁盤時,這個過程稱為 flush 過程。SQL 突然執(zhí)行變得很慢,性能下降。原因就可能和 flush 操作有關(guān)。
因為在進行 flush 操作時,更新操作會等待 redo log 的寫入。
引起 flush 操作的原因
場景一:redo log 日志已經(jīng)記滿。這時系統(tǒng)會停止更新操作,將 check point 向前推進,讓 redo log 留出空間可以繼續(xù)寫。
這里假設(shè) CP 到 CP‘ 間隙已經(jīng)寫入到磁盤,這部分就變成了干凈頁,此時 write pos 就可以寫入這部分區(qū)域了。
場景二:系統(tǒng)內(nèi)存不足,需要新的內(nèi)存頁時,發(fā)現(xiàn)內(nèi)存不夠用了,就需要淘汰一些數(shù)據(jù)頁。如果淘汰時,這時數(shù)據(jù)頁時臟頁,就要將臟頁寫到磁盤。
這時有個問題是,命名 redo log 中的內(nèi)容已經(jīng)被記錄到日志中了,假如內(nèi)存滿了,直接刪除不就可以嗎?下次讀入時,再把 redo log 日志中的內(nèi)容應(yīng)用到磁盤。
沒有選擇直接清空內(nèi)存,是從性能考慮的,因為在查詢數(shù)據(jù)時,有兩種情況:
所以這樣效率比較高。
場景三:MySQL 會在系統(tǒng)空閑時,進入 flush 操作。
場景四:在 MySQL 正常關(guān)閉時,會把內(nèi)存臟頁 flush 到磁盤上。
引起 flush 對性能的影響
對于第三,四場景來說,是比較正常的情況,不需要考慮性能問題。
對于第一種場景,InnoDB 會盡量避免,因為在這種情況下,整個系統(tǒng)不再接受更新。
但有時出現(xiàn)人為的配置錯誤,比如內(nèi)存為 128 GB,innodb_io_capacity 設(shè)置為 20000 的實例。通常建議將 redo log 設(shè)置成 4 個 1GB 的文件。但由于配置錯誤,設(shè)置成 100M 的文件。
這里由于 redo log 設(shè)置的太小,很快就會被寫滿。write pos 一直追著 check point. 這時,系統(tǒng)只能停止所有更新,推進 checkpoint.
表現(xiàn)就是,磁盤 IO 很小,但是出現(xiàn)間歇性的性能下降。
對于第二種場景,內(nèi)存不夠用的情況,InnoDB 會用緩沖池(buffer pool)管理內(nèi)存
內(nèi)存頁在緩沖池中會有三種狀態(tài):
每個數(shù)據(jù)頁頭部有LSN,8字節(jié),每次修改都會變大。
對比這個 LSN 跟 checkpoint 的 LSN,比checkpoint小的一定是干凈頁
由于 InnoDB 的策略是盡可能使用內(nèi)存,所以對于長時間運行的庫來說,未被使用的頁面很少。
當發(fā)現(xiàn)想讀入的數(shù)據(jù)頁沒有在內(nèi)存中時,必須到緩沖池申請數(shù)據(jù)頁。并會把最久不用得數(shù)據(jù)頁從內(nèi)存中淘汰
如果是干凈頁,直接釋放使用
如果是臟頁,必須先刷盤,變成干凈頁才能復用
當時,如果在下面的情況進行刷臟頁,會明顯影響性能:
要淘汰的臟頁太多,導致查詢響應(yīng)時間較長。
日志寫滿,更新被阻塞。
為了解決這個問題,InnoDB 使用控制臟頁比例的機制,來避免上面的情況。
InooDB 控制刷臟頁的策略
在 InnoDB 中,通過 innodb_io_capacity 參數(shù),來告訴 InnoDB 目前主機的磁盤能力是多少,這個值建議設(shè)置成磁盤的 IOPS.
可以通過 fio 這個工具來測試:
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
由于 innodb_io_capacity
導致的性能問題很常見,比如有時系統(tǒng)吞吐量(TPS)很低,寫入很慢,但是磁盤 IO 并不高。就有可能是該參數(shù)設(shè)置的不正確。例如,innodb_io_capacity
的值設(shè)置的很低,但是磁盤用的 SSD,導致 InooDB 認為系統(tǒng)能力很差,所以刷臟頁特別慢。造成臟頁累計,影響查詢和更新性能。
InnoDB 在刷盤時主要考慮兩個因素:
會通過這兩個因素單獨先算出兩個數(shù)字。
innodb_max_dirty_pages_pct
臟頁比例上限,默認 75%.
InnoDB 會根據(jù)臟頁的比例(M),算出范圍在 0 - 100 的數(shù)字。,過程稱為 F1(M)
# M 臟頁比例 F1(M) { if M>=innodb_max_dirty_pages_pct then return 100; return 100*M/innodb_max_dirty_pages_pct; }
除此之外,InnoDB 每次寫入日志都會有一個序號 N. 然后根據(jù) N 再算出一個 0 到 100 的數(shù)字,這個計算過程稱為 F2(N)
N: 當前寫入的序號和 checkpoint 對應(yīng)序號之間的差值。
最后,根據(jù) F1(M)和 F2(N)兩個值,取其中較大的值為 R,之后引擎就可以按照 innodb_io_capacity * R 來控制刷臟頁的速度。
所以無論是在查詢,需要加載數(shù)據(jù)到內(nèi)存數(shù)據(jù)頁,而淘汰臟頁。還是更新時,導致刷盤操作都有可能造成 MySQL 的性能下降。
為了避免這種情況,要合理的設(shè)置 innodb_io_capacity
的值,平時要多關(guān)注臟頁比例,不讓其接近 75%.
其中臟頁比例可以通過下面的方式獲?。?/p>
mysql> use performance_schema; mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty'; select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'; select @a/@b;
初次之外,在一個查詢操作進行時,如果需要 flush 臟頁的話,如果這個該臟頁的鄰居也是臟頁的話,就會把這個鄰居一起刷掉,如果恰好旁邊還是臟頁的話,就會一直連坐。這時導致 flush 過慢的原因。
可以通過 innodb_flush_neighbors
來控制該行為,值為 1 打開上述機制,為 0 則關(guān)閉。
對于機械硬盤來說,是可以減少很多隨機 IO ,因為機械硬盤 IOPS 一般就幾百,減少隨機 IO 就意味著性能提升。
但如果用 SSD 這類 IOPS 較高的設(shè)備,IOPS 往往不是瓶頸,關(guān)閉就好,減少 SQL 語句的響應(yīng)時間。
在 8.0 中,已經(jīng)默認是 0 了.
總結(jié)
這篇中,主要介紹了 WAL 時的 flush 操作可能會造成 MySQL 突然的性能下降。
引起的原因一般是由于內(nèi)存不夠?qū)е碌模M而可以通過設(shè)置合適的 innodb_io_capacity
參數(shù),來控制 InnoDB flush 的過程。
以上就是MySQL性能突然下降的原因的詳細內(nèi)容,更多關(guān)于MySQL性能下降的資料請關(guān)注腳本之家其它相關(guān)文章!
標簽:惠州 公主嶺 阿里 呼和浩特 牡丹江 沈陽 合肥 天津
巨人網(wǎng)絡(luò)通訊聲明:本文標題《MySQL性能突然下降的原因》,本文關(guān)鍵詞 MySQL,性能,突然,下降,的,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。