ClickHouse DB::Exception: Limit exceeded

The "DB::Exception: Limit exceeded" error in ClickHouse is a general-purpose error that appears when any of various resource limits are hit during query execution or server operation. The LIMIT_EXCEEDED error code acts as a catch-all for situations where a specific resource constraint has been breached, and the full error message usually provides details about which limit was exceeded.

Impact

The immediate effect is that the current operation is terminated. Because this is a generic error code, the impact varies widely depending on the specific limit that was reached. It could affect individual queries, data insertions, DDL operations, or background processes. Identifying the exact limit from the error message is crucial for determining severity and appropriate resolution.

Common Causes

1. Query resource quotas configured for the user or profile being exceeded
2. Server-wide limits on concurrent queries, connections, or memory being reached
3. Table-level limits such as maximum partitions or parts being hit
4. File descriptor limits at the OS level being exhausted
5. Network bandwidth or connection pool limits in distributed setups
6. Custom quotas that restrict total rows read, bytes read, or execution time over a period

Troubleshooting and Resolution Steps

1. Read the full error message carefully. The LIMIT_EXCEEDED error always includes details about the specific limit:

   DB::Exception: Limit exceeded: rows read 1000001, max rows 1000000
   

2. Check user quotas and limits:

   SELECT * FROM system.quotas;
   SELECT * FROM system.quota_usage;
   SELECT * FROM system.settings_profile_elements;
   

3. Check server-level limits:

   SELECT * FROM system.settings
   WHERE name IN ('max_concurrent_queries', 'max_connections');
   
   -- Check current usage
   SELECT * FROM system.metrics
   WHERE metric IN ('Query', 'TCPConnection', 'HTTPConnection');
   

4. Check OS-level limits if the error mentions file descriptors or system resources:

   # Check ClickHouse process limits
   cat /proc/$(pidof clickhouse-server)/limits
   # Check open file descriptors
   ls /proc/$(pidof clickhouse-server)/fd | wc -l
   

5. Increase the specific limit once identified. For user quotas:

   ALTER QUOTA my_quota FOR INTERVAL 1 hour MAX rows_read = 10000000000;
   

   For server settings, update the configuration file or set at the session level.

6. Review and optimize the workload rather than simply raising limits:

   • Are queries reading more data than necessary?
   • Are there too many concurrent connections from the application pool?
   • Can the workload be distributed more evenly over time?

Best Practices

• Always read the complete error message -- LIMIT_EXCEEDED is generic, and the details matter.
• Implement layered limits: server-level for overall protection, profile-level for workload classes, and quota-level for individual users.
• Monitor resource usage trends to anticipate when limits will be reached, rather than reacting to errors.
• Document the limits configured in your environment so that developers and operators know what constraints are in place.
• Test workload changes in a staging environment where limits mirror production.
• Use ClickHouse's quota system to enforce fair usage in multi-tenant environments.

Frequently Asked Questions

Q: Why does ClickHouse use a generic LIMIT_EXCEEDED error instead of a specific one?
A: ClickHouse has many specific error codes for common limit types (TOO_MANY_ROWS, TOO_MANY_BYTES, etc.). LIMIT_EXCEEDED is used for cases that do not have a dedicated error code, or when a generic limit mechanism like quotas triggers the error. The full message always contains the specific limit details.

Q: How do I find which setting or quota is causing the limit?
A: The error message includes the resource name and the limit value. Cross-reference this with system.settings, system.quotas, and system.settings_profile_elements to identify where the limit is configured.

Q: Can quotas accumulate over time and cause this error intermittently?
A: Yes. Quotas with time intervals (e.g., per hour) track cumulative usage. A user might succeed with individual queries but hit the quota after running many queries within the interval. Check system.quota_usage to see current consumption against limits.

Q: Is it safe to increase limits without investigating the root cause?
A: It depends. Limits exist to protect system stability. Increasing them without understanding why they were hit can lead to resource exhaustion, affecting all users. Always investigate the cause first and increase limits only when the workload legitimately requires more resources.