CLICKHOUSE CONFIGURATION
FILES (config.xml, users.xml,
confid.d, users.d)
31 August 2023Posted on by Faruk Çevik
ClickHouse configuration files consist of either xml or yaml files. If a special location is
not specified during ClickHouse Server installation, all clickhouse server
configurations /etc/clickhouse-server/config.xml are included in the
file. Additionally, user configurations users.xml are also in the same directory. These
configurations can be divided into sub-configuration files, their directories are;
/etc/clickhouse-server/config.xml --> Server Config
/etc/clickhouse-server/users.xml --> User Config
/etc/clickhouse-server/config.d/ --> Server Setting Config
/etc/clickhouse-server/users.d/ --> User Settings Config
Regarding the server configuration config.xml, all configurations can be included in the
directory, some configurations can be included in the directory config.d, and some
features of the user configurations users.d can also be included in the directory. For
example, it can be included in XML tags config.xml within Server Network-related
configurations, or it can be hosted in a separate file under the directory.
<network>..</network> config.d network.xml
For example;
$ cat /etc/clickhouse-server/config.d/listen.xml
<clickhouse>
<listen_host>0.0.0.0</listen_host>
</clickhouse>
$ cat /etc/clickhouse-server/config.d/macros.xml
<clickhouse>
<macros>
<cluster>mycluster</cluster>
<shard>02</shard>
<replica>clicknode02</replica>
<layer>01</layer>
</macros>
</clickhouse>
While changing some parameters requires a database restart, changing some does not
require a database restart. To generalize, config.xml changes config.d made to the
configuration files under the and directory require a restart, while configuration changes
made to the files under the users.xml and directory do not require a restart. This is not
certain and there are exceptions users.d
It is used to remove a Configuration remove and to replace it with another replace. For
example, to remove the default user;
<users>
<default remove="remove">
</default>
It can sometimes be thought-provoking which parameter config.xml and config.d
directory it is under and which parameters users.xml or users.d directory it is under. In
order to distinguish this; system.parameters The table can be looked at, it is related
to system.parameters the parameters users.xml and directory that are in the table, if
not, they should be searched under the and directory. Let's look at this from the web
interface. Since our topic is config.xml, web port is written in the config.xml file user.d
config.xml config.d
<http_port>8123</http_port>
USER Configuration
User configurations can be made through configuration files as well as with SQL
statements. system.settingsWe can see all configurations through the user
configurations table we mentioned above. So, in general, what settings can we make
with User Configurations?
Database server access can be filtered via User Network Settings IP and Hostaname
(FQDN). This filter can be done via users.xml, profile or SQL;
IP: IP address or netmask.
0.0.0.0/0 –> IPv4, ::/0 –> IPv6
Host: DNS resolved hostname
Host Regexp (Regular Expression)
CREATE USER IF NOT EXISTS btl
IDENTIFIED WITH SHA256_PASSWORD BY 'parola123'
HOST IP '192.168.100.1/24' SETTINGS readonly=1;
ALTER USER zyn HOST REGEXP 'clicknode[12345].farukcevik.com';
Although it is not mandatory to generate a sha256 password when creating a user
with Secure Password users.xml or SQL statements, it is recommended. The
following bash can be used to generate sha256 password on Linux
# echo -n "parola123" | sha256sum| tr -d '-'
Set Quota While Quota can be created with SQL statements and set to the user,
it can also be defined in the xml created for the relevant user via users.xml or
under users.d.
Use Profile While Profile can be created with SQL statements and set to the
user, it can also be defined in the xml created for the relevant user via users.xml
or under users.d.
The most commonly used profile settings are given below, these settings should be
configured according to your system specifications.
1. readonly
2. max_execution_timeIt is the maximum running time limit of a process.
3. max_bytes_before_external_group_byIt is the MAX amount of Memory that a single
process can use for sorting. (ORDER BY)
4. max_bytes_before_external_sortIt is the MAX amount of memory allocated for
sorting operations.
Database Restrictions
Enable Remote Authentication
To see which values have been changed regarding users parameters in a cluster;
SELECT name,value FROM system.settings where changed=1;
SELECT
name,
value
FROM system.settings
WHERE changed = 1
Query id: e32a7cc7-e581-40ad-83f9-3edd08960b68
┌─name────────────────────────────────────────┬─value────────┐
│ max_threads │ 48 │
│ connect_timeout_with_failover_ms │ 1000 │
│ receive_timeout │ 3000 │
│ send_timeout │ 3000 │
│ distributed_directory_monitor_batch_inserts │ 1 │
│ load_balancing │ random │
│ distributed_aggregation_memory_efficient │ 1 │
│ aggregation_memory_efficient_merge_threads │ 16 │
│ log_queries │ 1 │
│ max_bytes_before_external_group_by │ 80000000000 │
│ max_memory_usage │ 322122547200 │
│ low_cardinality_max_dictionary_size │ 100000 │
│ max_partitions_per_insert_block │ 300 │
│ materialize_ttl_after_modify │ 0 │
│ background_pool_size │ 48 │
│ background_move_pool_size │ 16 │
│ background_fetches_pool_size │ 16 │
│ background_schedule_pool_size │ 128 │
└─────────────────────────────────────────────┴──────────────┘
18 rows in set. Elapsed: 0.005 sec.
