Clickhouse async insert settings implementation

Question

How to correctly using async insert settings in clickhouse?

I am following the documentation on clickhouse website here: https://clickhouse.com/docs/en/optimize/asynchronous-inserts to use async inserts.

I wanted to apply async insert settings at user level. I used approach mentioned here https://clickhouse.com/docs/en/optimize/asynchronous-inserts#enabling-asynchronous-inserts , verified that async insert is enabled from system.settings table in clickhouse.

Now when I insert into tables using this user logged in on the server, doing it via console only using INSERT INTO table() values () command. It inserts the record into table but this insertion log doesn't appear in asynchronous_insert_log table.

When can this happen? What all logs I can use to debug this.

Update: When I do INSERT INTO Table SETTINGS async_insert=1 wait_for_async_insert=1 values (), then it actually shows up in asynchronous_insert_log table else not.

Answer 1

This enables async inserts at the user level:

ALTER USER default SETTINGS async_insert = 1

But that doesn't enable the logging. You have to configure the asynchronous_insert_log by adding something like the following in a new file in config.d:

<clickhouse>
    <asynchronous_insert_log>
        <database>system</database>
        <table>asynchronous_insert_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
        <max_size_rows>1048576</max_size_rows>
        <reserved_size_rows>8192</reserved_size_rows>
        <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
        <flush_on_crash>false</flush_on_crash>
        <engine>Engine = MergeTree PARTITION BY event_date ORDER BY event_time TTL event_date + INTERVAL 30 day</engine>
    </asynchronous_insert_log>
</clickhouse>

More details in the docs: https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#server_configuration_parameters-asynchronous_insert_log