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.
- 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;
- 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.
- 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
