Fix MySQL Binary Log Sync Delay

What's Actually Happening

MySQL binary log sync delays occur when the binlog disk sync operation takes too long, blocking transaction commits and causing replication lag. This happens when sync_binlog is set to a low value (like 1) on slow storage.

The Error You'll See

Slow commits:

```bash $ mysql -e "SHOW PROCESSLIST"

Id User Command Time State Info 123 app Query 5 Waiting for binlog sync INSERT INTO ... 124 app Query 4 Waiting for binlog sync UPDATE ... 125 app Query 3 Waiting for binlog sync DELETE ... ```

Replication lag:

```bash $ mysql -e "SHOW SLAVE STATUS"

Seconds_Behind_Master: 45 Relay_Master_Log_File: mysql-bin.000123 Exec_Master_Log_Pos: 456789 ```

Disk I/O bottleneck:

```bash $ iostat -x 1

Device await svctm %util sda 50.00 10.00 99.00 # High await time ```

Why This Happens

1. 1.sync_binlog=1 - Every transaction requires disk sync
2. 2.Slow disk storage - HDD or network storage with high latency
3. 3.High write volume - Many concurrent transactions
4. 4.Binlog rotation - Large binlog files slow rotation
5. 5.Disk contention - Other processes competing for disk I/O
6. 6.No write cache - Disk cache disabled or bypassed

Step 1: Check Current sync_binlog Setting

```bash # Check sync_binlog value mysql -e "SHOW VARIABLES LIKE 'sync_binlog'"

# Check binlog format mysql -e "SHOW VARIABLES LIKE 'binlog_format'"

# Check innodb_flush_log_at_trx_commit mysql -e "SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'"

# Recommended combination for durability: # sync_binlog=1 + innodb_flush_log_at_trx_commit=1 = ACID compliant # But slow on non-SSD storage ```

Step 2: Measure Disk Sync Performance

```bash # Check disk write performance dd if=/dev/zero of=/var/lib/mysql/test.tmp bs=1M count=100 conv=fdatasync

# Check disk latency iostat -x 1 10 | grep sda

# Measure fsync performance # MySQL provides tool: cd /usr/share/mysql/mysql-test ./mtr --suite=engines/funcs --do-tests=fsync

# Or use sysbench sysbench fileio --file-num=1 --file-size=1G --file-test-mode=rndwr prepare sysbench fileio --file-num=1 --file-size=1G --file-test-mode=rndwr run

# Check average fsync time # Should be < 1ms for SSD, may be 10-50ms for HDD ```

Step 3: Check Binary Log Status

```bash # List binary logs mysql -e "SHOW BINARY LOGS"

# Check current binlog position mysql -e "SHOW MASTER STATUS"

# Check binlog cache size mysql -e "SHOW VARIABLES LIKE 'binlog_cache_size'"

# Check binlog cache usage mysql -e "SHOW STATUS LIKE 'Binlog_cache%'"

# Check disk usage df -h /var/lib/mysql ```

Step 4: Adjust sync_binlog for Performance

```bash # For SSD storage with battery-backed cache: # Keep sync_binlog=1 for durability

# For HDD or network storage: # Adjust to balance durability vs performance

# Option 1: sync_binlog=0 (OS handles sync) mysql -e "SET GLOBAL sync_binlog=0"

# Option 2: sync_binlog=N (sync every N transactions) mysql -e "SET GLOBAL sync_binlog=100"

# For permanent change in my.cnf: [mysqld] sync_binlog=100 # Sync every 100 transactions innodb_flush_log_at_trx_commit=2 # Flush every second

# Trade-offs: # sync_binlog=1 + flush_log=1: Maximum durability, slowest # sync_binlog=100 + flush_log=2: Good performance, risk up to 100 transactions # sync_binlog=0 + flush_log=0: Fastest, risk on crash

# Restart to apply permanently systemctl restart mysql ```

Step 5: Optimize Binary Log Rotation

```bash # Check binlog file size limit mysql -e "SHOW VARIABLES LIKE 'max_binlog_size'"

# Smaller binlog files = faster rotation mysql -e "SET GLOBAL max_binlog_size=268435456" # 256MB

# In my.cnf: [mysqld] max_binlog_size=256M

# Expire old binlogs automatically mysql -e "SET GLOBAL binlog_expire_logs_seconds=604800" # 7 days

# Or legacy setting: mysql -e "SET GLOBAL expire_logs_days=7"

# Purge old binlogs manually mysql -e "PURGE BINARY LOGS BEFORE '2026-04-10 00:00:00'" mysql -e "PURGE BINARY LOGS TO 'mysql-bin.000100'" ```

Step 6: Optimize Binlog Cache

```bash # Check binlog cache size mysql -e "SHOW VARIABLES LIKE 'binlog_cache_size'"

# Default is 32KB, increase for large transactions mysql -e "SET GLOBAL binlog_cache_size=131072" # 128KB

# In my.cnf: [mysqld] binlog_cache_size=128K

# Check if cache disk usage is high mysql -e "SHOW STATUS LIKE 'Binlog_cache_disk_use'"

# If disk use is high, increase cache mysql -e "SET GLOBAL binlog_cache_size=262144" # 256KB

# Monitor cache usage mysqladmin ext -i10 | grep Binlog_cache ```

Step 7: Use Group Commit for Better Performance

```bash # MySQL 5.6+ has binlog group commit

# Check group commit settings mysql -e "SHOW VARIABLES LIKE 'binlog_group_commit%'"

# Add delay to group more transactions together mysql -e "SET GLOBAL binlog_group_commit_sync_delay=1000" # 1ms delay

# This allows more transactions to be grouped for single fsync

# Maximum number of groups per sync mysql -e "SET GLOBAL binlog_group_commit_sync_no_delay_count=10"

# In my.cnf: [mysqld] binlog_group_commit_sync_delay=1000 binlog_group_commit_sync_no_delay_count=10

# Trade-off: Small latency increase for better throughput ```

Step 8: Optimize Disk I/O

```bash # Use dedicated disk for binlogs # Mount separate disk at /var/lib/mysql

# Check current mount mount | grep /var/lib/mysql

# Use SSD for binlogs if possible # Or HDD with write cache enabled

# Linux I/O scheduler (deadline or noop for SSD) cat /sys/block/sda/queue/scheduler echo deadline > /sys/block/sda/queue/scheduler

# Or permanently in /etc/rc.local: echo deadline > /sys/block/sda/queue/scheduler

# Check writeback cache cat /proc/sys/vm/dirty_ratio cat /proc/sys/vm/dirty_background_ratio

# Increase for better write performance (risk on crash) sysctl vm.dirty_ratio=20 sysctl vm.dirty_background_ratio=10 ```

Step 9: Fix Replication Lag from Binlog Delay

```bash # Check replication status mysql -e "SHOW SLAVE STATUS"

# If Seconds_Behind_Master is high:

# Check relay log position mysql -e "SHOW SLAVE STATUS" | grep -E "Relay_|Exec_"

# Check slave disk performance iostat -x 1 | grep sda

# Adjust slave sync settings mysql -e "SET GLOBAL sync_binlog=0" # On slave

# Check slave is processing binlogs mysql -e "SHOW PROCESSLIST" | grep "system user"

# Check network latency ping master-server

# If network is slow, use compression # In my.cnf on slave: [mysqld] slave_compressed_protocol=1 ```

Step 10: Monitor Binlog Performance

```bash # Create monitoring script cat << 'EOF' > /usr/local/bin/monitor_binlog.sh #!/bin/bash mysql -e " SHOW STATUS LIKE 'Binlog_cache_disk_use'; SHOW STATUS LIKE 'Binlog_cache_use'; SHOW STATUS LIKE 'Binlog_%'; SHOW PROCESSLIST; " | grep -E "Binlog|sync"

# Check disk I/O iostat -x 1 1 | grep -E "Device|$(df /var/lib/mysql | tail -1 | cut -d'/' -f3)" EOF

chmod +x /usr/local/bin/monitor_binlog.sh

# Monitor in loop watch -n 5 /usr/local/bin/monitor_binlog.sh

# Use pt-ioprofile from Percona Toolkit pt-ioprofile /var/lib/mysql --profile-process=mysqld

# Check MySQL error log for sync warnings tail -f /var/log/mysql/error.log | grep -i sync ```

MySQL Binlog Sync Checklist

Verify the Fix

```bash # After adjusting sync_binlog settings

# 1. Check sync time improved mysql -e "SHOW STATUS LIKE 'Innodb_os_log%'" // Innodb_os_log_fsyncs should decrease

# 2. Monitor commit latency mysqladmin ext -i10 | grep Com_commit

# 3. Check processlist - no waiting for sync mysql -e "SHOW PROCESSLIST" | grep sync // Should show no or few waiting processes

# 4. Verify replication lag reduced mysql -e "SHOW SLAVE STATUS" | grep Seconds_Behind // Should be near 0

# 5. Test disk performance sysbench fileio run // Improved fsync times

# 6. Check binlog rotation is smooth mysql -e "SHOW BINARY LOGS" // Regular rotation without gaps ```

Related Issues

• [Fix MySQL Replication Lag](/articles/fix-mysql-replication-lag)
• [Fix MySQL Slow Transaction Commit](/articles/fix-mysql-slow-transaction-commit)
• [Fix MySQL Disk I/O Bottleneck](/articles/fix-mysql-disk-io-bottleneck)