Skip to main content

ClickHouse Administration and Troubleshooting

Restarting ClickHouse​

To restart ClickHouse, use the systemctl command depending on your operating system:

sudo systemctl restart clickhouse-server

Using clickhouse-client​

clickhouse-client is a REPL shell for interacting with ClickHouse. It allows you to execute queries against the ClickHouse server, manage databases, tables, and other objects. clickhouse-client supports interactive mode and script mode.

Key Features of clickhouse-client​

Interactive Mode​

The clickhouse-client command can be executed without specifying any parameters. Launching the command without parameters will start a shell where you can enter and execute SQL queries in real time.

Script Mode​

clickhouse-client can accept SQL queries from files or other input streams and execute them. This is useful for automating tasks or executing large SQL queries.

Example with passing a query through a parameter:

clickhouse-client --query="SELECT * FROM table_name"

Example with passing a query through standard input:

clickhouse-client < script.sql

Connecting to Remote Servers​

It is also possible to connect to remote ClickHouse servers by specifying the host, port, user, and password as follows:

clickhouse-client --host=remote_host --port=9000 --user=username --password=secret_password

Output Formats​

clickhouse-client supports various data output formats, such as CSV, JSON, and others. This allows for flexible work with data output depending on the task.

Example:

clickhouse-client --query="SELECT * FROM table_name" --format=CSV

Troubleshooting ClickHouse​

Log Files​

ClickHouse logs can be useful for diagnosing problems and analyzing server performance. Log files are located in the /var/log/clickhouse-server/ directory by default.

To monitor the latest logs, execute:

tail -f /var/log/clickhouse-server/clickhouse-server.log

To view error logs, execute:

tail -f /var/log/clickhouse-server/clickhouse-server.err.log

Analyzing System Tables​

ClickHouse system tables contain a lot of useful information for monitoring and diagnosing server performance. The main tables are listed below:

  • system.metrics - shows the current values of internal server metrics, such as the number of active connections and queries.

    SELECT * FROM system.metrics;

  • system.parts - contains information about parts of MergeTree tables, including their size and status.

    SELECT
        table,
        partition,
        name,
        active,
        bytes_on_disk
    FROM system.parts
    WHERE active = 1;

  • system.replicas - contains information about the replication status.

    SELECT
        database,
        table,
        is_leader,
        is_readonly,
        total_replicas,
        active_replicas
    FROM system.replicas;

  • system.query_log - contains information about all executed queries, including their duration and possible errors.

    SELECT
        query,
        event_time,
        query_duration_ms,
        exception
    FROM system.query_log
    ORDER BY event_time DESC
    LIMIT 10;

Common Errors​

Error Checking ClickHouse Connection​

In the /app/opensearch/jdk/lib/security/default.policy file, make sure the getClassLoader permission is present.

The file should include the following line:

permission java.lang.RuntimePermission "getClassLoader";

Add it to the end of the file as follows if this permission is missing:

grant {
    permission java.lang.RuntimePermission "getClassLoader";
};

After making changes, restart the opensearch service on the cluster nodes where the setting was updated.