You restart ClickHouse. The server is slow to accept queries. Or the port opens, but some queries wait.
The other form is worse. A long query runs. You stop the server. The client gets an error and the work is lost.
Both problems come from server-wide settings. You set them in config.xml or config.d/. This article covers startup and shutdown.
Why it happens
Startup
A server can have tens of thousands of tables. Loading them takes time. In the old model, the server loaded every table before it opened its ports. Clients saw a dead server for minutes.
With async_load_databases on, the server opens its ports first. It loads tables in the background. This is asynchronous table loading.
A query on a loaded table runs at once. A query on a table that is not loaded yet must wait. The query waits for exactly that table, and the server loads that table with a higher priority. If the load job fails, the query gets the error.
In my first test, a query on an unloaded table finished in under 0.1 s. That test used 2000 empty tables. Then I used 8 tables with 5000 data parts each and one loader thread. Queries on those tables waited about 25 s.
The loader runs jobs in pools. In system.asynchronous_loader on 26.8, I saw three pools: ForegroundLoad, BackgroundLoad and BackgrndStartup. The foreground pool serves tables that queries need now.
Async loading does not make the total load faster. It lets the server answer earlier. In my test, the first answer came about 6 s after the restart. The last job finished about 4 s later.
Problems can start when many clients retry at the same time. Each query waits for a table. Waiting queries hold connections and memory. The setting max_waiting_queries is a limit on waiting queries.
This setting is not a general query queue. It applies only to queries that wait for tables that are still loading. It is not a queue for queries that wait for CPU or memory. The docs say waiting queries are not counted when some other query limits are checked, so those limits are not hit just after startup.
Shutdown
By default, the server does not wait for running queries. It cancels them. The client gets an error.
Two settings change this. shutdown_wait_unfinished_queries turns the wait on. shutdown_wait_unfinished sets how long to wait.
How to confirm
First, check the settings on your own server. Defaults change between releases.
SELECT name, value, default, changed, changeable_without_restart
FROM system.server_settings
WHERE name IN (
'async_load_databases',
'tables_loader_foreground_pool_size',
'tables_loader_background_pool_size',
'max_waiting_queries',
'shutdown_wait_unfinished_queries',
'shutdown_wait_unfinished'
)
ORDER BY name;The column changeable_without_restart shows Yes or No. On 26.8, only max_waiting_queries shows Yes.
Startup
Right after a restart, check the load progress with system.asynchronous_loader.
SELECT status, count() AS jobs
FROM system.asynchronous_loader
GROUP BY status
ORDER BY status;The status values are PENDING, OK, FAILED and CANCELED. In my test, the server answered while 3893 jobs were PENDING and 149 were OK. One table can have more than one job.
To see which jobs are waiting, run this query.
SELECT job, status, elapsed, pool
FROM system.asynchronous_loader
WHERE status != 'OK'
ORDER BY elapsed DESC
LIMIT 10;A job name looks like startup table load_test.t_1631. To see how jobs spread over pools, run this query.
SELECT pool, execution_pool, count() AS jobs
FROM system.asynchronous_loader
GROUP BY pool, execution_pool
ORDER BY jobs DESC;A quick test is SELECT 1. If it is fast but a query on one table is slow, that table is not loaded yet.
Shutdown
Run a long query. Stop the server during the query. Look at what the client gets.
With the default settings, the client gets HTTP 500 and error code 394, QUERY_WAS_CANCELLED. If the process is killed, the client sees a closed connection and no HTTP code.
The settings
Check each default on your own server with system.server_settings. The values below match the master branch (commit 9f79cc1, 30 October 2026) and my test on 26.8.
| Setting | Default | What it controls | Restart needed |
|---|---|---|---|
async_load_databases | true | Open ports first. Load tables in the background. | Yes |
tables_loader_foreground_pool_size | 0 (auto) | Threads for tables that queries wait for. | Yes |
tables_loader_background_pool_size | 0 (auto) | Threads for all other tables. | Yes |
max_waiting_queries | 0 (no limit) | Limit on queries that wait for tables that are loading. | No |
shutdown_wait_unfinished_queries | false | Wait for running queries before shutdown. | Yes |
shutdown_wait_unfinished | 120 seconds | How long to wait for running queries. | Yes |
How to fix it
1. Keep asynchronous loading on
Do not turn async_load_databases off on a server with many tables. If your server has it off, turn it on.
Create /etc/clickhouse-server/config.d/startup.xml:
<clickhouse>
<async_load_databases>true</async_load_databases>
</clickhouse>This setting needs a restart.
In four runs, the server answered in 5.3 to 6.0 s with async loading. It answered in 8.4 to 9.1 s without. All runs used 2000 empty tables. Test with your own table count.
2. Change the pool sizes only if loading is slow
Leave both pools at 0 first. Auto sizing works on most servers.
If loading is slow and the CPU is idle, the pool may be too small. Raise the pools in steps. Measure after each step.
<clickhouse>
<tables_loader_foreground_pool_size>16</tables_loader_foreground_pool_size>
<tables_loader_background_pool_size>16</tables_loader_background_pool_size>
</clickhouse>The value 16 is an example. It is not a recommendation. A large pool on slow disks adds IO load and gives no gain. These settings need a restart.
3. Test max_waiting_queries before you rely on it
The docs describe max_waiting_queries as a limit on concurrently waiting queries. A value of 0 means no limit.
In my test, it did not reject any query. I set the limit to 1. I sent 64 queries at once to tables that were still loading. All 64 waited about 25 s. All 64 succeeded. I could not make the limit reject a query, and I do not know why.
Do not use this setting as your only protection. Test it on your own tables first. Also limit how fast your clients retry during a restart.
If you want to try it, create /etc/clickhouse-server/config.d/waiting.xml:
<clickhouse>
<max_waiting_queries>100</max_waiting_queries>
</clickhouse>The value 100 is an example. This setting does not need a restart. In my test, the value changed from 2 to 50 within 8 s.
4. Wait for running queries at shutdown
Set both settings for planned restarts.
Create /etc/clickhouse-server/config.d/shutdown.xml:
<clickhouse>
<shutdown_wait_unfinished_queries>true</shutdown_wait_unfinished_queries>
<shutdown_wait_unfinished>300</shutdown_wait_unfinished>
</clickhouse>The value 300 is an example. Use your longest normal query time, plus a margin. Both settings need a restart. The new values apply to the next shutdown.
Your service manager also has a stop timeout. Make it longer than shutdown_wait_unfinished. If it is shorter, the manager kills the server and the wait is useless. In my test, docker stop -t 5 killed the server during a 30 second wait. The query was lost. Check these:
- systemd:
TimeoutStopSec. On my host,systemctl cat clickhouse-servershowsTimeoutStopSec=infinity. Check your unit file. - Docker:
docker stop -t. The default is 10 seconds. - Kubernetes:
terminationGracePeriodSeconds.
How to verify the fix
Startup
Restart the server. Run the loader query at once.
SELECT status, count() AS jobs
FROM system.asynchronous_loader
GROUP BY status
ORDER BY status;The server must answer while some jobs are still PENDING. Then repeat this query until it returns 0.
SELECT count() AS pending
FROM system.asynchronous_loader
WHERE status = 'PENDING';Check that no job failed.
SELECT job, status, exception
FROM system.asynchronous_loader
WHERE status IN ('FAILED', 'CANCELED');The query must return no rows.
Waiting limit
Confirm the value without a restart.
SELECT name, value
FROM system.server_settings
WHERE name = 'max_waiting_queries';To test the limit, use tables with thousands of data parts. Send more queries than the limit right after a restart. Check if any query fails. With small tables, queries do not wait long enough for a test.
Shutdown
Start a long query.
SELECT sleepEachRow(1) FROM numbers(15) SETTINGS max_block_size = 1 FORMAT Null;Stop the server while it runs. With the fix, the query must finish. In my test, the client got HTTP 200. The stop took about 15 s, because the server waited for the query. With the default settings, the stop took about 4 s and the query failed.
Risks and trade-offs
A long shutdown wait delays restarts. During an incident, you may need a fast restart. A 300 second wait can turn a short fix into a long outage.
You have three options in that case:
- Stop new traffic first, at the load balancer. Existing keep-alive connections can still send queries. Check that no queries run before you stop the server.
- Lower the wait for the next restart. This needs a restart to apply, so it does not help in the moment.
- Kill the server as a last step. Running queries are lost.
Use this query to check for running queries. It does not count itself. In my test, it returned 1 while the long query ran.
SELECT count() AS running
FROM system.processes
WHERE query_id != queryID();The first option is the best. Drain traffic, check, then restart.
Other risks:
- A wait that is longer than the service manager timeout has no effect.
- Waiting queries use memory and connections. Many retrying clients can still hurt a server that is loading tables.
- Very large pools can overload the disks during startup.
- With async loading, one table can answer slowly while others answer fast. Tell your application teams.
Startup on a server with many tables writes a lot of log data. Large logs can fill a disk.
Summary
- Keep
async_load_databaseson for servers with many tables. The server answers earlier, but total load time stays about the same. - Use
system.asynchronous_loaderto see load progress. CountPENDINGjobs to know when loading is done. max_waiting_queriesapplies only to queries that wait for loading tables. It changes without a restart. In my test it did not reject queries, so test it first.- Set
shutdown_wait_unfinished_queriestotruefor planned restarts. Make the service stop timeout longer thanshutdown_wait_unfinished. - A long wait delays restarts in an incident. Drain traffic first, then check for running queries.



