背景

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

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

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

一、原因

可能是因為MySQL在日志在每次事務(wù)提交時，都會將其寫入并刷新到磁盤，造成磁盤IO的高占用。

二、查看配置

通過在MySQL命令行運行以下命令：

show variables like 'sync_binlog';

可以看到：sync_binlog 的值為1。

該值意味著：啟用在提交事務(wù)之前將二進制日志同步到磁盤。這是最安全的設(shè)置，但是會造成磁盤的較高占用。

show variables like 'innodb_flush_log_at_trx_commit';

可以看到：innodb_flush_log_at_trx_commit 的值為1。

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

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

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

• 默認值：1
• 最小值：0
• 最大值：4294967295

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

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

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

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

可以通過更改默認值來獲得更好的性能，但隨后可能會在崩潰中丟失事務(wù)。

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

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

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

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

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

四、通過修改配置解決問題

注意：

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

更改配置可能會帶來更高的數(shù)據(jù)丟失風(fēng)險?。?！

可以通過以下兩條命令修改配置。 

set global sync_binlog=你希望的值;

set global innodb_flush_log_at_trx_commit=你希望的值;

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

MySQL的二進制日志記錄選項和變量

總結(jié)

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

最新評論