1. Home
  2. Knowledge Base
  3. ClickHouse DBA
  4. Examining Memory Killer Processes In ClickHouse

Examining Memory Killer Processes In ClickHouse

Memory usage, like many other metrics, is a value that must be constantly monitored. Continuously examining the tables, indexes or processes that use memory will help you to know your system better and allow you to make the necessary interventions quickly. 

In this article, we will share with you different methods by which you can detect processes that use memory.

 

asynchronous_metrics

The first method is checking asynchronous_metrics table. This table contains metrics that are calculated periodically in the background. For example, the amount of RAM in use;

SELECT
    *,
    formatReadableSize(value)
FROM system.asynchronous_metrics
WHERE (metric LIKE '%Cach%') OR (metric LIKE '%Mem%')
ORDER BY metric ASC
FORMAT PrettyCompactMonoBlock

Query id: 4de5abc3-5eca-484b-a096-43ccce5c8492

┌─metric────────────────────┬──────value─┬─formatReadableSize(value)─┐
│ MMapCacheCells            │          0 │ 0.00 B                    │
│ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ MarkCacheFiles            │        138 │ 138.00 B                  │
│ MemoryCode                │  323928064 │ 308.92 MiB                │
│ MemoryDataAndStack        │ 7301906432 │ 6.80 GiB                  │
│ MemoryResident            │  427020288 │ 407.24 MiB                │
│ MemoryShared              │  176488448 │ 168.31 MiB                │
│ MemoryVirtual             │ 7974686720 │ 7.43 GiB                  │
│ OSMemoryAvailable         │ 1617231872 │ 1.51 GiB                  │
│ OSMemoryBuffers           │  170713088 │ 162.80 MiB                │
│ OSMemoryCached            │  896454656 │ 854.93 MiB                │
│ OSMemoryFreePlusCached    │ 1390579712 │ 1.30 GiB                  │
│ OSMemoryFreeWithoutCached │  494125056 │ 471.23 MiB                │
│ OSMemoryTotal             │ 2087923712 │ 1.94 GiB                  │
│ UncompressedCacheBytes    │          0 │ 0.00 B                    │
│ UncompressedCacheCells    │          0 │ 0.00 B                    │
└───────────────────────────┴────────────┴───────────────────────────┘

asynchronous_metric_log

Another table is asynchronous_metric_log. This table contains the historical values for system.asynchronous_metrics, which are saved once per minute. Enabled by default. asynchronous_metric_log table can return thousands of rows. So it will be useful to use WHERE condition.

SELECT
    event_time,
    metric,
    value,
    formatReadableSize(value)
FROM system.asynchronous_metric_log
WHERE (event_time > (now() - 600)) AND ((metric LIKE '%Cach%') OR (metric LIKE '%Mem%')) AND (value != 0)
ORDER BY
    metric ASC,
    event_time ASC
FORMAT PrettyCompactMonoBlock

Query id: 1acbe1ba-a9d9-4697-b66c-da6af01706e8

┌──────────event_time─┬─metric────────────────────┬──────value─┬─formatReadableSize(value)─┐
│ 2022-11-16 16:26:48 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:49 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:50 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:51 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:52 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:53 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:54 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:55 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:56 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:57 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │
│ 2022-11-16 16:26:58 │ MarkCacheBytes            │    1642752 │ 1.57 MiB                  │

 

system.tables

Another method is checking the system.tables.

System tables provide information about the following:

  • Server states, processes, and environment.
  • Server’s internal processes.

System tables:

  • Located in the system database.
  • Available only for reading data.
  • Can’t be dropped or altered but can be detached.

Most of the system tables store their data in RAM. A ClickHouse server creates such system tables at the start. So it is good to check system.tables. The following example is checks system.tables which is using “Memory”, “Set” and “Join” engines.

SELECT
    database,
    name,
    formatReadableSize(total_bytes)
FROM system.tables
WHERE engine IN ('Memory','Set','Join');

 

system.parts

parts table contains lots of information. In the following example, we will check only parts which are related to memory usage.

  • primary_key_bytes_in_memory (UInt64) – The amount of memory (in bytes) used by primary key values.
  • primary_key_bytes_in_memory_allocated (UInt64) – The amount of memory (in bytes) reserved for primary key values.
SELECT
    sumIf(data_uncompressed_bytes, part_type = 'InMemory') AS memory_parts,
    formatReadableSize(sum(primary_key_bytes_in_memory)) AS primary_key_bytes_in_memory,
    formatReadableSize(sum(primary_key_bytes_in_memory_allocated)) AS primary_key_bytes_in_memory_allocated
FROM system.parts

Query id: 2498c3f4-404e-42ae-9608-bde83faaedf0

┌─memory_parts─┬─primary_key_bytes_in_memory─┬─primary_key_bytes_in_memory_allocated─┐
│            0 │ 635.54 KiB                  │ 4.80 MiB                              │
└──────────────┴─────────────────────────────┴───────────────────────────────────────┘

 

system.merges

merges table contains information about merges and part mutations currently in process for tables in the MergeTree family.

  • memory_usage (UInt64) — Memory consumption of the merge process.
SELECT formatReadableSize(sum(memory_usage))
FROM system.merges

Query id: 903a9f36-8c19-4b00-b589-bf3682b7be70

┌─formatReadableSize(sum(memory_usage))─┐
│ 0.00 B                                │
└───────────────────────────────────────┘

CLI command;

for i in `seq 1 600`; do clickhouse-client --empty_result_for_aggregation_by_empty_set=0 -q "select (select 'Merges: \
'||formatReadableSize(sum(memory_usage)) from system.merges), (select \
'Processes: '||formatReadableSize(sum(memory_usage)) from system.processes)";\
sleep 3;  done 



Merges: 0.00 B	Processes: 42.05 KiB
Merges: 0.00 B	Processes: 42.05 KiB
Merges: 0.00 B	Processes: 42.05 KiB
Merges: 0.00 B	Processes: 42.05 KiB

 

system.processes

This system table is used for implementing the SHOW PROCESSLIST query.

  • memory_usage (UInt64) – Amount of RAM the request uses. It might not include some types of dedicated memory. See the max_memory_usage setting.

 

SELECT formatReadableSize(sum(memory_usage))
FROM system.processes

Query id: 572849c8-c99b-400b-bbac-2408e1e99aa0

┌─formatReadableSize(sum(memory_usage))─┐
│ 0.00 B                                │
└───────────────────────────────────────┘

You can also run the following query.

  • elapsed (Float64) – The time in seconds since request execution started.
SELECT
    initial_query_id,
    elapsed,
    formatReadableSize(memory_usage),
    formatReadableSize(peak_memory_usage),
    query
FROM system.processes
ORDER BY peak_memory_usage DESC
LIMIT 10;

To find expensive queries by Memory /available RAM usage

SELECT
    system.processes.client_hostname,
    system.processes.client_name,
    system.processes.query,
    system.processes.memory_usage,
    system.processes.read_rows
FROM system.processes
WHERE system.processes.peak_memory_usage > (
    SELECT avg(system.processes.peak_memory_usage)
    FROM system.processes
)
FORMAT Vertical

Query id: 4cc975a4-442a-4082-88d9-faa024a51b54

Row 1:
──────
client_hostname: clickhouse01
client_name:     ClickHouse
query:           select system.processes.client_hostname,system.processes.client_name,
system.processes.query,
       system.processes.memory_usage, system.processes.read_rows
       from system.processes
       where system.processes.peak_memory_usage > (
           select avg(system.processes.peak_memory_usage)
           from system.processes
           ) format Vertical;
memory_usage:    14176
read_rows:       1

 

system.query_log

Contains information about executed queries, for example, start time, duration of processing, error messages.

  • memory_usage (UInt64) — Memory consumption by the query.
SELECT
    type,
    event_time,
    initial_query_id,
    formatReadableSize(memory_usage),
    query
FROM system.query_log
WHERE (event_date >= today()) AND (event_time >= (now() - 7200))
ORDER BY memory_usage DESC
LIMIT 1
FORMAT Vertical

Query id: b79da0ae-f908-41d7-8ac8-f9f40660ea95

Row 1:
──────
type:                             QueryFinish
event_time:                       2022-11-16 16:26:52
initial_query_id:                 4e4e9a67-45ec-46d8-af75-46d0e2ce1694
formatReadableSize(memory_usage): 5.10 MiB
query:                            SELECT DISTINCT arrayJoin(extractAll(name, '[\\w_]{2,}')) AS res FROM (SELECT * FROM viewIfPermitted(SELECT name FROM system.functions ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT name FROM system.table_engines ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT name FROM system.formats ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT name FROM system.table_functions ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT name FROM system.data_type_families ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT name FROM system.merge_tree_settings ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT name FROM system.settings ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT cluster FROM system.clusters ELSE null('cluster String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT macro FROM system.macros ELSE null('macro String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT policy_name FROM system.storage_policies ELSE null('policy_name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT concat(func.name, comb.name) AS x FROM system.functions AS func CROSS JOIN system.aggregate_function_combinators AS comb WHERE is_aggregate ELSE null('x String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT name FROM system.databases LIMIT 10000 ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT DISTINCT name FROM system.tables LIMIT 10000 ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT DISTINCT name FROM system.dictionaries LIMIT 10000 ELSE null('name String')) UNION ALL SELECT * FROM viewIfPermitted(SELECT DISTINCT name FROM system.columns LIMIT 10000 ELSE null('name String'))) WHERE notEmpty(res)

 

Another example with query_log.

select
    ql.event_time as "Query Date and Time",
    ql.query_kind as "Type of the Query",
    ql.query as "Query",
    ql.query_duration_ms/1000 as "Query Duration in Seconds",
    ql.memory_usage as "Memory Usage",
    ql.written_rows as "Total Number of Rows Written ",
    ql.read_rows as "Total Number of Rows Read"
        from
            system.query_log as ql
    where (ql.event_time > (now() - 3600))
order by "Query Duration in Seconds" desc;

 

system.dictionaries

Contains information about dictionaries.

echo "         Merges      Processes       PrimaryK       TempTabs          Dicts"; \
for i in `seq 1 600`; do clickhouse-client --empty_result_for_aggregation_by_empty_set=0  -q "select \
(select leftPad(formatReadableSize(sum(memory_usage)),15, ' ') from system.merges)||
(select leftPad(formatReadableSize(sum(memory_usage)),15, ' ') from system.processes)||
(select leftPad(formatReadableSize(sum(primary_key_bytes_in_memory_allocated)),15, ' ') from system.parts)|| \
(select leftPad(formatReadableSize(sum(total_bytes)),15, ' ') from system.tables \
 WHERE engine IN ('Memory','Set','Join'))||
(select leftPad(formatReadableSize(sum(bytes_allocated)),15, ' ') FROM system.dictionaries)
"; sleep 3;  done 

         Merges      Processes       PrimaryK       TempTabs          Dicts
         0.00 B         0.00 B      21.36 MiB       1.58 GiB     911.07 MiB
         0.00 B         0.00 B      21.36 MiB       1.58 GiB     911.07 MiB
         0.00 B         0.00 B      21.35 MiB       1.58 GiB     911.07 MiB
         0.00 B         0.00 B      21.36 MiB       1.58 GiB     911.07 MiB

 

system.query_thread_log

Contains information about threads that execute queries, for example, thread name, thread start time, duration of query processing.

Find top 5 most expensive queries by memory usage.

  • memory_usage (Int64) — The difference between the amount of allocated and freed memory in context of this thread.
  • peak_memory_usage (Int64) — The maximum difference between the amount of allocated and freed memory in context of this thread.
  • read_rows (UInt64) — Number of read rows.
  • read_bytes (UInt64) — Number of read bytes.
  • written_rows (UInt64) — For INSERT queries, the number of written rows. For other queries, the column value is 0.
  • written_bytes (UInt64) — For INSERT queries, the number of written bytes. For other queries, the column value is 0.
select
    system.query_thread_log.query,
system.query_thread_log.query_start_time_microseconds,
    system.query_thread_log.query_duration_ms,system.query_thread_log.read_rows,
    system.query_thread_log.written_rows, system.query_thread_log.memory_usage
from
    system.query_thread_log
where system.query_thread_log.memory_usage >
      (select avg(system.query_thread_log.memory_usage)
from
    system.query_thread_log
)
limit 5;

 

 

Was this article helpful?
Yes No

Related Articles

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.

256 Chapman Road STE 105-4, Newark, New Castle, Delaware, 19702, United States | ©2024 ChistaDATA Inc. All rights reserved. | ClickHouse is a trademark of ClickHouse Corporation. All other trademarks are property of their respective owners. Other product or company names mentioned may be trademarks or trade names of their respective owner.