ClickHouse DB::Exception: Unknown load balancing mode

The UNKNOWN_LOAD_BALANCING error appears when the load_balancing setting is assigned a value that ClickHouse does not recognize. You will see a message like DB::Exception: Unknown load balancing mode: 'invalid_value'. The load_balancing setting determines how ClickHouse selects replicas when executing distributed queries, and it accepts only a specific set of predefined values.

Impact

The query or session that specifies the invalid value will fail. If the invalid load_balancing value is defined in a user profile, all distributed queries for affected users will fail until the configuration is corrected. This can effectively disable distributed query functionality for those users, impacting dashboards, reports, and applications that depend on distributed tables.

Common Causes

1. Typo in the setting value -- Writing 'round_robbin' instead of 'round_robin', or 'nearest_hostname' instead of 'nearest_hostname'.
2. Using a value from documentation for a different version -- Some load balancing modes were added in later releases.
3. Confusing with other enum settings -- Using values like 'throw' or 'break' that belong to overflow mode settings.
4. Case sensitivity -- Values must be in the expected case (lowercase with underscores).

Troubleshooting and Resolution Steps

1. Review the valid load_balancing values: ClickHouse supports the following modes:

   • 'random' -- (Default) Select a random replica for each query. Distributes load evenly over time.
   • 'nearest_hostname' -- Prefer replicas whose hostname is most similar to the current server's hostname. Useful when replicas are co-located in the same rack or datacenter.
   • 'hostname_levenshtein_distance' -- Same as nearest_hostname (alias in some versions).
   • 'in_order' -- Try replicas in the order they are defined in the config. The first available replica is used.
   • 'first_or_random' -- Use the first replica in the config if available; otherwise pick randomly from the remaining replicas.
   • 'round_robin' -- Cycle through replicas in order across successive queries.

2. Set the correct value:

   -- Check the current value
   SELECT name, value FROM system.settings WHERE name = 'load_balancing';
   
   -- Set a valid value
   SET load_balancing = 'random';
   

3. Fix configuration files if needed:

   grep -rn 'load_balancing' /etc/clickhouse-server/users.xml /etc/clickhouse-server/users.d/
   

   Update any incorrect values:

   <profiles>
       <default>
           <load_balancing>random</load_balancing>
       </default>
   </profiles>
   

4. Verify the setting is supported in your version:

   SELECT version();
   

   For example, 'round_robin' was added in ClickHouse 21.4. Using it on an older version will produce this error.

5. Reload configuration after fixing:

   SYSTEM RELOAD CONFIG;
   

Best Practices

• Use 'random' (the default) for most workloads -- it provides good load distribution without configuration overhead.
• Use 'nearest_hostname' in multi-datacenter deployments to minimize cross-datacenter traffic by preferring local replicas.
• Use 'in_order' when you want deterministic replica selection, such as directing reads to a specific replica for cache locality.
• Use 'first_or_random' to designate a preferred replica while maintaining failover capability.
• Document the chosen load balancing mode and the reasoning behind it in your team's runbook.

Frequently Asked Questions

Q: What happens if the selected replica is down?
A: ClickHouse automatically falls back to another replica, regardless of the load balancing mode. The mode only determines the preference order; it does not prevent failover.

Q: Does load_balancing affect non-distributed queries?
A: No. The setting only matters when querying distributed tables or when ClickHouse needs to select among replicas. Local queries on a single server are not affected.

Q: Can I set different load balancing modes for different queries?
A: Yes. You can set it at the query level using the SETTINGS clause:

SELECT * FROM distributed_table
SETTINGS load_balancing = 'in_order';

Q: What is the difference between 'in_order' and 'first_or_random'?
A: With 'in_order', replicas are tried strictly in the order defined in the config. With 'first_or_random', the first replica in the config is preferred, but if it is unavailable, a random replica is chosen from the rest (rather than trying the second, third, etc. in sequence). 'first_or_random' is useful when you want one primary replica but do not care about the fallback order.