Home / Database / Fix MySQL Binary Log Sync Delay

Database

Fix MySQL Binary Log Sync Delay

Resolve MySQL binary log synchronization delays causing replication lag and slow commits.

Published: Apr 4, 20269 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

Introduction

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.

Symptoms

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 ```

Common Causes

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

Step-by-Step Fix

Step 1: Check Current sync_binlog Setting

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

Variable_name	Value
sync_binlog	1

# 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"

Log_name	File_size
mysql-bin.000001	1024
mysql-bin.000002	1073741824

# 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'"

Variable_name	Value
max_binlog_size	1073741824

# 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'"

Variable_name	Value
Binlog_cache_disk_use	5000

# 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%'"

Variable_name	Value
binlog_group_commit_sync_delay	0
binlog_group_commit_sync_no_delay	0

# 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

Check	Command	Expected
sync_binlog	SHOW VARIABLES	Appropriate for storage
Disk latency	iostat -x 1	< 10ms for HDD, < 1ms SSD
Binlog cache	SHOW STATUS	Low disk use
Group commit	SHOW VARIABLES	Enabled
Replication lag	SHOW SLAVE STATUS	Seconds_Behind < 10

Verification

```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 ```

Prevention

To prevent MySQL binary log sync delay issues from recurring, implement these proactive measures:

1. Monitor Binary Log Performance

yaml

groups:
- name: mysql-binlog
  rules:
  - alert: MySQLBinlogSyncSlow
    expr: |
      mysql_binlog_sync_time_seconds > 0.5
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "MySQL binary log sync taking too long"

2. Configure Appropriate sync_binlog

```sql -- For high-write environments with SSD: SET GLOBAL sync_binlog = 100; -- Sync every 100 commits

-- For safety-critical environments: SET GLOBAL sync_binlog = 1; -- Sync every commit (slower)

-- Balance durability and performance based on requirements ```

3. Monitor Disk I/O Performance

```bash # Check disk I/O stats regularly iostat -x 5 5 | grep -E 'Device|mysql'

# Set alert for high disk wait time # iowait > 20% indicates disk bottleneck ```

Best Practices Checklist

[ ] Monitor binlog sync time with alerts
[ ] Set appropriate sync_binlog value
[ ] Use SSD storage for binlog files
[ ] Monitor disk I/O performance
[ ] Configure binlog expiration
[ ] Test recovery procedures regularly

[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)

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

MySQL Binary Log Sync Delay MySQL Binary Log Sync Delay Database MySQL Binary Log Sync Delay troubleshooting MySQL Binary Log Sync Delay fix Resolve MySQL binary log synchronization delays causing replication lag and slow commits Database Resolve MySQL binary log synchronization delays causing replication lag and slow commits

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Apr 4, 2026

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

Fix MySQL Binary Log Sync Delay

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Check Current sync_binlog Setting

Step 2: Measure Disk Sync Performance

Step 3: Check Binary Log Status

Step 4: Adjust sync_binlog for Performance

Step 5: Optimize Binary Log Rotation

Step 6: Optimize Binlog Cache

Step 7: Use Group Commit for Better Performance

Step 8: Optimize Disk I/O

Step 9: Fix Replication Lag from Binlog Delay

Step 10: Monitor Binlog Performance

MySQL Binlog Sync Checklist

Verification

Prevention

1. Monitor Binary Log Performance

2. Configure Appropriate sync_binlog

3. Monitor Disk I/O Performance

Best Practices Checklist

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

Fix MySQL Binary Log Sync Delay

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Check Current sync_binlog Setting

Step 2: Measure Disk Sync Performance

Step 3: Check Binary Log Status

Step 4: Adjust sync_binlog for Performance

Step 5: Optimize Binary Log Rotation

Step 6: Optimize Binlog Cache

Step 7: Use Group Commit for Better Performance

Step 8: Optimize Disk I/O

Step 9: Fix Replication Lag from Binlog Delay

Step 10: Monitor Binlog Performance

MySQL Binlog Sync Checklist

Verification

Prevention

1. Monitor Binary Log Performance

2. Configure Appropriate sync_binlog

3. Monitor Disk I/O Performance

Best Practices Checklist

Related Issues

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading