Monitoring Key Activities by ClickHouse Users

Introduction

Monitoring key user activity in ClickHouse is quite essential to system health and performance. In this article, we discuss means of doing this, and simple SQL scripts that may be helpful for this purpose.

How to monitor key activities by ClickHouse users

To monitor key activities by ClickHouse users, you can use the following methods.

  1. System table: The system table “system.events” in ClickHouse contains information about all user-initiated actions on the database. You can use the following SQL query to monitor user activities:
SELECT * FROM system.events ORDER BY event_time DESC LIMIT 10;
  1. Logs: The logs generated by ClickHouse can also be used to monitor user activities. The logs can be found in the logs directory of the ClickHouse installation and are saved in a format that is easily readable by human.
  2. Performance schema: ClickHouse provides a performance schema that includes a table for monitoring user activities. The “system.processes” table provides information about current user connections, including the user name, query type, and query time. You can use the following SQL query to monitor user activities:
SELECT * FROM system.processes ORDER BY query_start_time DESC LIMIT 10;

It is important to monitor user activities in ClickHouse to ensure proper database security and performance.

To monitor the ClickHouse users activities, you can use the system table system.processes. This table provides information about the current running queries, user information, and resource usage.

SELECT

user,

query_id,

query,

start_time,

progress,

memory_usage

FROM system.processes

ORDER BY start_time DESC;

This script will return the following information for each running query:

  • user: The username of the user who is executing the query.
  • query_id: The unique identifier of the query.
  • query: The text of the query being executed.
  • start_time: The start time of the query.
  • progress: The progress of the query, represented as a percentage.
  • memory_usage: The memory usage of the query, in bytes.

You can use this script to periodically check the user activities and monitor the performance of the queries.

SQL Script to Monitor Key Activities by ClickHouse Users

SELECT query_id, client_hostname, user, database, sum(rows_read) as logical_reads, 
       sum(rows_read_physical) as physical_reads, sum(rows_written) as logical_writes, 
       sum(cpu_time) as session_cpu
FROM system.processes 
GROUP BY query_id, client_hostname, user, database 
ORDER BY session_cpu DESC;

This script retrieves the query_id, client_hostname, user, database, sum of rows_read as logical_reads, sum of rows_read_physical as physical_reads, sum of rows_written as logical_writes, and sum of cpu_time as session_cpu from the system.processes table and groups them by query_id, client_hostname, user, and database. Finally, the results are ordered by session_cpu in descending order to show the top consumers of CPU time.

Conclusion

Please use the above scripts and runbooks in your ClickHouse instance to keep a close eye on user activity.

If you would like to read more about Monitoring in ClickHouse, do consider reading the following articles

About Shiv Iyer 222 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.