背景

在之前的主從同步過(guò)程中（Mysql的多級(jí)復(fù)制），從數(shù)據(jù)庫(kù)Z存在磁盤IO占用過(guò)高的問(wèn)題。

磁盤IO在同步期間占用率達(dá)到100%，且數(shù)據(jù)存在滯后，不能實(shí)現(xiàn)實(shí)時(shí)更新。

從數(shù)據(jù)庫(kù)的磁盤為機(jī)械硬盤，讀寫性能相對(duì)于固態(tài)硬盤要差一點(diǎn)。

一、原因

可能是因?yàn)镸ySQL在日志在每次事務(wù)提交時(shí)，都會(huì)將其寫入并刷新到磁盤，造成磁盤IO的高占用。

二、查看配置

通過(guò)在MySQL命令行運(yùn)行以下命令：

show variables like 'sync_binlog';

可以看到：sync_binlog 的值為1。

該值意味著：?jiǎn)⒂迷谔峤皇聞?wù)之前將二進(jìn)制日志同步到磁盤。這是最安全的設(shè)置，但是會(huì)造成磁盤的較高占用。

show variables like 'innodb_flush_log_at_trx_commit';

可以看到：innodb_flush_log_at_trx_commit 的值為1。

該值意味著：日志會(huì)在每次事務(wù)提交時(shí)寫入并刷新到磁盤。

三、配置參數(shù)含義

sync_binlog：控制MySQL服務(wù)器將二進(jìn)制日志同步到磁盤的頻率

• 默認(rèn)值：1
• 最小值：0
• 最大值：4294967295

sync_binlog=0：禁用 MySQL 服務(wù)器將二進(jìn)制日志同步到磁盤。相反，MySQL服務(wù)器依賴于操作系統(tǒng)不時(shí)將二進(jìn)制日志刷新到磁盤，就像它對(duì)任何其他文件所做的那樣。此設(shè)置提供最佳性能，但在發(fā)生電源故障或操作系統(tǒng)崩潰時(shí)，服務(wù)器可能已提交尚未同步到二進(jìn)制日志的事務(wù)。

sync_binlog=1：啟用在提交事務(wù)之前將二進(jìn)制日志同步到磁盤。這是最安全的設(shè)置，但由于磁盤寫入次數(shù)增加，可能會(huì)對(duì)性能產(chǎn)生負(fù)面影響。如果發(fā)生電源故障或操作系統(tǒng)崩潰，二進(jìn)制日志中缺少的事務(wù)僅處于就緒狀態(tài)。這允許自動(dòng)恢復(fù)例程回滾事務(wù)，從而保證二進(jìn)制日志中不會(huì)丟失任何事務(wù)。

sync_binlog=N，其中 N 是 0 或 1 以外的值：收集二進(jìn)制日志提交組后，二進(jìn)制日志將同步到磁盤。如果發(fā)生電源故障或操作系統(tǒng)崩潰，服務(wù)器可能已提交尚未刷新到二進(jìn)制日志的事務(wù)。由于磁盤寫入次數(shù)增加，此設(shè)置可能會(huì)對(duì)性能產(chǎn)生負(fù)面影響。值越高，性能越高，但數(shù)據(jù)丟失的風(fēng)險(xiǎn)也會(huì)增加。

• innodb_flush_log_at_trx_commit：控制提交操作的嚴(yán)格 ACID 合規(guī)性與重新排列并批量完成與提交相關(guān)的 I/O 操作時(shí)可能實(shí)現(xiàn)的更高性能之間的平衡。
• 默認(rèn)值為1
• 有效值為：0、1、2

可以通過(guò)更改默認(rèn)值來(lái)獲得更好的性能，但隨后可能會(huì)在崩潰中丟失事務(wù)。

• innodb_flush_log_at_trx_commit=1 ：完全符合 ACID 所必需的。日志在每次事務(wù)提交時(shí)寫入并刷新到磁盤。
• innodb_flush_log_at_trx_commit=0：每秒將日志寫入磁盤并刷新一次。尚未刷新其日志的事務(wù)可能會(huì)在崩潰中丟失。
• innodb_flush_log_at_trx_commit=2：在每次事務(wù)提交后寫入日志，并每秒刷新一次到磁盤。尚未刷新其日志的事務(wù)可能會(huì)在崩潰中丟失。

對(duì)于設(shè)置 0 和 2，不能 100% 保證每秒一次刷新。

由于 DDL 更改和其他內(nèi)部活動(dòng)導(dǎo)致獨(dú)立于innodb_flush_log_at_trx_commit設(shè)置刷新日志，刷新可能會(huì)更頻繁地發(fā)生，有時(shí)由于計(jì)劃問(wèn)題而降低刷新頻率。

如果每秒刷新一次日志，則崩潰時(shí)最多可能會(huì)丟失一秒鐘的事務(wù)。

如果刷新日志的頻率高于或低于每秒一次的頻率，則可能丟失的事務(wù)量會(huì)相應(yīng)地變化。

四、通過(guò)修改配置解決問(wèn)題

注意：

這種解決辦法是在犧牲數(shù)據(jù)庫(kù)安全的前提下，提高磁盤的性能?。?！

更改配置可能會(huì)帶來(lái)更高的數(shù)據(jù)丟失風(fēng)險(xiǎn)?。?！

可以通過(guò)以下兩條命令修改配置。 

set global sync_binlog=你希望的值;

set global innodb_flush_log_at_trx_commit=你希望的值;

更多設(shè)置請(qǐng)參考：

MySQL的二進(jìn)制日志記錄選項(xiàng)和變量

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論