ClickHouse DB::Exception: Cannot create pipe

The "DB::Exception: Cannot create pipe" error in ClickHouse occurs when the pipe() system call fails. Pipes are used internally for inter-process communication, particularly when ClickHouse needs to communicate with child processes for features like executable UDFs or external dictionary sources. The CANNOT_PIPE error typically points to file descriptor exhaustion or system resource limits being reached.

Impact

This error prevents ClickHouse from establishing communication channels with child processes. Any functionality that relies on pipes -- such as executable table functions, executable UDFs, and some external integrations -- will be unavailable. The failure usually does not affect standard query processing that does not involve subprocess communication.

Common Causes

1. The file descriptor limit for the ClickHouse process has been exhausted
2. The system-wide file descriptor limit (/proc/sys/fs/file-max) has been reached
3. A file descriptor leak in ClickHouse or in external programs it communicates with
4. Extremely high concurrency with many simultaneous subprocess-based operations
5. Container or cgroup limits restricting the number of open files

Troubleshooting and Resolution Steps

1. Check file descriptor usage for the ClickHouse process:

   cat /proc/$(pidof clickhouse-server)/limits | grep "open files"
   ls /proc/$(pidof clickhouse-server)/fd | wc -l
   

   If usage is near the limit, that is likely the cause.

2. Check system-wide file descriptor usage:

   cat /proc/sys/fs/file-nr
   

   The three values shown are: allocated, free, and maximum. If allocated is close to maximum, the system is under pressure.

3. Look for file descriptor leaks:

   # Check what types of FDs ClickHouse has open
   ls -la /proc/$(pidof clickhouse-server)/fd | awk '{print $NF}' | sort | uniq -c | sort -rn | head -20
   

4. Increase the file descriptor limit:

   # Via systemd override
   sudo systemctl edit clickhouse-server
   # Add: LimitNOFILE=500000
   sudo systemctl restart clickhouse-server
   

   Or in /etc/security/limits.conf:

   clickhouse soft nofile 500000
   clickhouse hard nofile 500000
   

5. Increase the system-wide limit if needed:

   sudo sysctl fs.file-max=2097152
   # Make persistent in /etc/sysctl.conf
   

6. Review ClickHouse system metrics:

   SELECT metric, value FROM system.metrics WHERE metric LIKE '%FileOpen%' OR metric LIKE '%File%';
   

Best Practices

• Set the file descriptor limit for ClickHouse to at least 100,000, and higher for deployments with many tables or subprocess-based features.
• Monitor file descriptor usage as part of routine system health checks.
• Ensure external programs used with executable UDFs properly close file descriptors and do not leak resources.
• Avoid spawning an excessive number of concurrent subprocess-based operations by limiting parallelism in queries that use executable table functions.

Frequently Asked Questions

Q: What does the pipe() system call do and why does ClickHouse need it?
A: The pipe() call creates a unidirectional data channel used for communication between processes. ClickHouse uses pipes to send data to and receive results from child processes, such as those running executable UDFs.

Q: Can this error affect normal SELECT queries?
A: Only if those queries reference executable table functions or UDFs. Standard queries against regular tables do not use pipes and are not affected by CANNOT_PIPE.

Q: I increased the file descriptor limit but still see the error. What else could it be?
A: Check whether the system-wide limit (fs.file-max) is also a bottleneck. Additionally, verify that the new limit has taken effect by checking /proc/$(pidof clickhouse-server)/limits after restarting ClickHouse.

Q: Could this indicate a bug in ClickHouse?
A: While possible, it is uncommon. In most cases, CANNOT_PIPE is caused by resource exhaustion at the OS level. If you consistently see this error with low file descriptor usage, consider filing a bug report with ClickHouse.