1. Home
  2. Knowledge Base
  3. Examining CPU Killer Processes In ClickHouse

Examining CPU Killer Processes In ClickHouse

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.

Was this article helpful?

CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES

In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.