To examine CPU-intensive processes in a ClickHouse database, you can use the system table system.processes
to view information about currently running queries and their resource usage.
You can run the following query to view the top CPU-intensive queries:
SELECT query_id, query, max_memory_usage, elapsed, read_rows, written_rows, memory_usage FROM system.processes WHERE query != '' ORDER BY max_memory_usage DESC LIMIT 10;
You can also use the system.metrics
table to view performance metrics for specific queries and identify which queries are causing high CPU usage.
You can also use the system.events
table to view information about events related to the query execution, such as if it was killed due to high CPU usage.
You can also use the system.threads
table to view information about running threads and their resource usage.
You can also use the system.metrics
table to monitor the cpu usage and other metric of the host where the clickhouse server is running.
It’s also a good practice to monitor the system resource usage with tools like top, htop, or sar.
Please note that you will need to have the appropriate permissions to access these system tables and execute these queries.
Here are some examples of how to use the system tables in ClickHouse to examine CPU-intensive processes:
system.processes
:
SELECT query_id, query, max_memory_usage, elapsed, read_rows, written_rows, memory_usage FROM system.processes WHERE query != '' ORDER BY max_memory_usage DESC LIMIT 10;
This query will return the top 10 queries that have used the most memory, ordered by memory usage in descending order. The query_id
column can be used to identify the specific query, while the query
column shows the actual query text. The elapsed
column shows the total time taken to execute the query.
system.metrics
:
SELECT * FROM system.metrics WHERE metric like 'clickhouse_query_cpu_%' ORDER BY value DESC LIMIT 10;
This query will return the top 10 queries that have used the most CPU time, ordered by CPU usage in descending order. The metric
column can be used to identify the specific query, while the value
column shows the total CPU time used by that query.
system.events
:
SELECT * FROM system.events WHERE event='KILL QUERY' ORDER BY event_time DESC LIMIT 10;
This query will return the top 10 queries that have been killed due to high CPU usage, ordered by event time in descending order. The query_id
column can be used to identify the specific query, while the event_time
column shows when the event occurred.
system.threads
:
SELECT * FROM system.threads WHERE is_current_query ORDER BY current_memory_usage DESC LIMIT 10;
This query will return the top 10 running threads that are executing a query, ordered by memory usage in descending order. The current_query_id
column can be used to identify the specific query, while the current_memory_usage
column shows the amount of memory used by that thread.
system.metrics
:
SELECT * FROM system.metrics WHERE metric like 'system.cpu.%' ORDER BY value DESC LIMIT 10;
This query will return the top 10 metrics related to the CPU usage, ordered by the value in descending order. The metric
column can be used to identify the specific metric, while the value
column shows the metric value.