Atomic Database Engine

Atomic Database Engine

In version 20.5 ClickHouse first introduced database engine=Atomic.

Since version 20.10 it is a default database engine (before engine=Ordinary was used).

Those 2 database engine differs in a way how they store data on a filesystem, and engine Atomic allows to resolve some of the issues existed in engine=Ordinary.

engine=Atomic supports

  • non-blocking drop table / rename table
  • tables delete (&detach) async (wait for selects finish but invisible for new selects)
  • atomic drop table (all files / folders removed)
  • atomic table swap (table swap by “EXCHANGE TABLES t1 AND t2;")
  • rename dictionary / rename database
  • unique automatic UUID paths in FS and ZK for Replicated

FAQ

Q. Data is not removed immediately

A. UseDROP TABLE t SYNC;

Or use parameter (user level) database_atomic_wait_for_drop_and_detach_synchronously:

SET database_atomic_wait_for_drop_and_detach_synchronously = 1;

Also, you can decrease the delay used by Atomic for real table drop (it’s 8 minutes by default)

cat /etc/clickhouse-server/config.d/database_atomic_delay_before_drop_table.xml
<yandex>
    <database_atomic_delay_before_drop_table_sec>1</database_atomic_delay_before_drop_table_sec>
</yandex>

Q. I cannot reuse zookeeper path after dropping the table.

A. This happens because real table deletion occurs with a controlled delay. See the previous question to remove the table immediately.

With engine=Atomic it’s possible (and is a good practice if you do it correctly) to include UUID into zookeeper path, i.e. :

CREATE ...
ON CLUSTER ...
ENGINE=ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}/', '{replica}')

See also: https://github.com/ClickHouse/ClickHouse/issues/12135#issuecomment-653932557

It’s very important that the table will have the same UUID cluster-wide.

When the table is created using ON CLUSTER - all tables will get the same UUID automatically. When it needs to be done manually (for example - you need to add one more replica), pick CREATE TABLE statement with UUID from one of the existing replicas.

set show_table_uuid_in_table_create_qquery_if_not_nil=1 ;
SHOW CREATE TABLE xxx; /* or SELECT create_table_query FROM system.tables WHERE ... */

Q. Should I use Atomic or Ordinary for new setups?

All things inside clickhouse itself should work smoothly with Atomic.

But some external tools - backup tools, things involving other kinds of direct manipulations with clickhouse files & folders may have issues with Atomic.

Ordinary layout on the filesystem is simpler. And the issues which address Atomic (lock-free renames, drops, atomic exchange of table) are not so critical in most cases.

Ordinary Atomic
filesystem layout very simple more complicated
external tool support
(like clickhouse-backup)
good / mature limited / beta

some DDL queries (DROP / RENAME) may

hang for a long time (waiting for some other things)

yes 👎 no 👍
Possibility to swap 2 tables

rename
a to a_old,
b to a,

a_old to b;

Operation is not atomic, and
can break in the middle (while chances are low).

EXCHANGE TABLES t1 AND t2

Atomic, have no intermediate states.

uuid in zookeeper path

Not possible to use.

The typical pattern is to add version suffix to zookeeper path when you need to create
the new version of the same table.

You can use uuid in zookeeper paths.
That requires some extra care when you expand the cluster, and makes zookeeper paths harder to map to real table.

But allows to to do any kind of manipulations on tables (rename, recreate with same name etc).

Materialized view without TO syntax

(!we recommend using TO syntax always!)

.inner.mv_name

The name is predictable, easy to match with MV.

.inner_id.{uuid}

The name is unpredictable, hard to match with MV (maybe problematic for MV chains, and similar scenarios)

Using Ordinary by default instead of Atomic

---
title: "cat /etc/clickhouse-server/users.d/disable_atomic_database.xml "
linkTitle: "cat /etc/clickhouse-server/users.d/disable_atomic_database.xml "
description: >
    cat /etc/clickhouse-server/users.d/disable_atomic_database.xml
---
<?xml version="1.0"?>
<yandex>
    <profiles>
        <default>
            <default_database_engine>Ordinary</default_database_engine>
        </default>
    </profiles>
</yandex>

Other sources

Presentation https://youtu.be/1LVJ_WcLgF8?t=2744

https://github.com/ClickHouse/clickhouse-presentations/blob/master/meetup46/database_engines.pdf


How to Convert Atomic to Ordinary

How to Convert Atomic to Ordinary

Last modified 2021.08.12 : General corrections and updates. (5969b688)