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

2. 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.
3. 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 user activities in ClickHouse, 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.

To monitor the Logical Reads, Physical Reads, Logical Writes, and Session CPU in ClickHouse, you can use the following SQL script:

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.