ClickHouse DB::Exception: Cannot fsync

The "DB::Exception: Cannot fsync" error in ClickHouse means the server was unable to flush buffered data from the operating system's page cache to the underlying storage device. The CANNOT_FSYNC error code signals a critical I/O problem: data that ClickHouse believed was being persisted may not have actually reached the physical disk. This is one of the more serious storage-related errors because it directly threatens data durability.

Impact

An fsync failure has significant implications:

• Data that was written but not fsynced may be lost if the system crashes or loses power
• Insert operations will fail, as ClickHouse cannot guarantee durability
• Merge operations will be aborted, potentially leaving fragmented parts
• The server may refuse to continue writing to the affected volume to prevent silent data loss
• Trust in data integrity is undermined until the storage issue is resolved

Common Causes

1. Disk hardware failure or degraded RAID array
2. Full disk -- the filesystem cannot complete pending writes
3. Storage device disconnected (network-attached storage going offline)
4. Filesystem corruption that prevents write completion
5. Kernel I/O errors related to the block device driver
6. Using a filesystem or storage backend that does not properly support fsync (some container overlayfs configurations)
7. Power loss without battery-backed write cache on the storage controller

Troubleshooting and Resolution Steps

1. Check for disk errors in system logs:

   dmesg | grep -i "error\|i/o\|fault\|scsi\|ata"
   journalctl -k --since "1 hour ago"
   

   I/O errors here confirm a hardware or driver-level problem.

2. Verify disk space:

   df -h /var/lib/clickhouse
   df -i /var/lib/clickhouse
   

   A full disk will cause fsync to fail. Free up space immediately.

3. Check disk health:

   smartctl -H /dev/sda
   smartctl -a /dev/sda | grep -i "reallocated\|pending\|uncorrect"
   

   Non-zero values for reallocated sectors, pending sectors, or uncorrectable errors indicate a failing drive.

4. Verify the filesystem is writable:

   mount | grep /var/lib/clickhouse
   touch /var/lib/clickhouse/test_write && rm /var/lib/clickhouse/test_write
   

5. Test fsync directly:

   dd if=/dev/zero of=/var/lib/clickhouse/test_fsync bs=4k count=1 conv=fsync && rm /var/lib/clickhouse/test_fsync
   

   If this fails, the problem is at the OS/storage level, not ClickHouse.

6. For container environments, check the storage driver: Overlay filesystems may not reliably support fsync. Use bind-mounted volumes from a real filesystem for ClickHouse data:

   volumes:
     - /data/clickhouse:/var/lib/clickhouse
   

7. Replace failing hardware and restore data from replicas or backups before bringing the node back online.

Best Practices

• Use storage with battery-backed or power-loss-protected write caches to ensure fsync reliability
• Monitor disk SMART metrics and replace drives at the first sign of degradation
• Avoid running ClickHouse on overlayfs or other layered filesystems in production
• Keep at least 15% free disk space to prevent write failures
• Use ext4 or xfs with default mount options for predictable fsync behavior
• Maintain multiple replicas so that an fsync failure on one node does not compromise data availability
• Regularly test backup and restore procedures

Frequently Asked Questions

Q: What exactly does fsync do?
A: The fsync system call forces the operating system to flush all buffered writes for a file to the physical storage device. Until fsync completes, written data exists only in memory and could be lost on crash or power failure. ClickHouse uses fsync to ensure data durability.

Q: Can I disable fsync in ClickHouse to avoid this error?
A: ClickHouse has a fsync_after_insert setting that can be turned off, but doing so means inserts are not guaranteed to survive a crash. This is only appropriate for non-critical data or when using replicated tables where other replicas provide durability.

Q: Is this error more common on certain storage types?
A: Yes. Network-attached storage, some cloud disk configurations, and container overlay filesystems are more prone to fsync failures than local SSDs with proper write-back cache protection.

Q: After fixing the disk issue, do I need to check my data?
A: Yes. Run CHECK TABLE on affected tables to verify data integrity. For replicated tables, compare part checksums across replicas. Any parts that failed fsync should be re-fetched or restored from backup.