ApsaraDB for ClickHouse:View memory usage

Sample environment

In this topic, the s-2-r-0 node is used as the basic environment. In actual use, modify the parameters based on your business requirements. If you do not know your node name, you can use one of the following ways to obtain the node name:

• You can obtain the node name on the Cluster Monitoring tab in the ApsaraDB for ClickHouse console. For more information about how to go to the Cluster Monitoring tab page, see View cluster monitoring information.

• You can execute the following SQL statement to obtain the names of all nodes in your cluster:

  SELECT * FROM system.clusters;

View and release system memory

Memory usage directly affects the system performance and stability. You can view and analyze data in the system.asynchronous_metrics table to identify the operations or data that occupies a large amount of memory and then release memory based on the data analysis.

• View memory usage

  View the memory usage of the s-2-r-0 node.

  SELECT *,
         formatReadableSize(value)
  FROM clusterAllReplicas('default', system.asynchronous_metrics)
  WHERE (metric like '%Cach%'
    OR metric like '%Mem%')
    AND substring(hostname(), 38, 8) = 's-2-r-0'
  ORDER BY metric;

  ┌─metric───────────────────────┬───value─────┬─formatReadableSize(value)─┐
  │ CGroupMemoryTotal            │ 34359738368 │ 32.00 GiB                 │
  │ CGroupMemoryUsed             │  4641132544 │ 4.32 GiB                  │
  │ CompiledExpressionCacheBytes │       16384 │ 16.00 KiB                 │
  │ CompiledExpressionCacheCount │           2 │ 2.00 B                    │
  │ FilesystemCacheBytes         │           0 │ 0.00 B                    │
  │ FilesystemCacheFiles         │           0 │ 0.00 B                    │
  │ HashTableStatsCacheEntries   │           8 │ 8.00 B                    │
  │ HashTableStatsCacheHits      │      528011 │ 515.64 KiB                │
  │ HashTableStatsCacheMisses    │          15 │ 15.00 B                   │
  │ IndexMarkCacheBytes          │           0 │ 0.00 B                    │
  │ IndexMarkCacheFiles          │           0 │ 0.00 B                    │
  │ IndexUncompressedCacheBytes  │           0 │ 0.00 B                    │
  │ IndexUncompressedCacheCells  │           0 │ 0.00 B                    │
  │ MMapCacheCells               │           0 │ 0.00 B                    │
  │ MarkCacheBytes               │        7968 │ 7.78 KiB                  │
  │ MarkCacheFiles               │          24 │ 24.00 B                   │
  │ MemoryCode                   │   390758400 │ 372.66 MiB                │
  │ MemoryDataAndStack           │ 14373392384 │ 13.39 GiB                 │
  │ MemoryResident               │   761221120 │ 725.96 MiB                │
  │ MemoryShared                 │   377688064 │ 360.19 MiB                │
  │ MemoryVirtual                │ 18072178688 │ 16.83 GiB                 │
  │ OSMemoryAvailable            │ 32480075776 │ 30.25 GiB                 │
  │ OSMemoryCached               │  4239949824 │ 3.95 GiB                  │
  │ OSMemoryFreePlusCached       │ 32480169984 │ 30.25 GiB                 │
  │ OSMemoryFreeWithoutCached    │ 28240220160 │ 26.30 GiB                 │
  │ OSMemoryTotal                │ 32881352704 │ 30.62 GiB                 │
  │ QueryCacheBytes              │           0 │ 0.00 B                    │
  │ QueryCacheEntries            │           0 │ 0.00 B                    │
  │ UncompressedCacheBytes       │      349487 │ 341.30 KiB                │
  │ UncompressedCacheCells       │         114 │ 114.00 B                  │
  └──────────────────────────────┴─────────────┴───────────────────────────┘

  The following table describes the key parameters for memory usage analysis.

  Parameter	Description
  MarkCacheBytes	The size of the mark cache. The size is measured in bytes. ApsaraDB for ClickHouse uses the mark cache to store index marks within data files.
  UncompressedCacheBytes	The size of the uncompressed data cache. The size is measured in bytes. In most cases, the data of ApsaraDB for ClickHouse is compressed in the stored procedure to save storage space and improve I/O efficiency. The uncompressed data cache stores data blocks that are read from disks and decompressed. In this way, the same data blocks can be directly retrieved from the memory the next time, eliminating the need to read and decompress the data blocks from disks again. This further improves query performance.

• Clear caches

  After you analyze the memory usage, you can execute the following SQL statements to clear caches.

  Important

  • ApsaraDB for ClickHouse does not support clearing the caches of a single node. The following commands will be executed as distributed DDL statements to clear the caches of all nodes in a cluster.

  • Cache clearing affects system performance and may slow down SQL query execution. Proceed with caution.

  Clear the mark cache.

  SYSTEM DROP MARK CACHE;

  Clear the uncompressed data cache.

  SYSTEM DROP UNCOMPRESSED CACHE;

View the memory usage of merge operations

The merge operations of ApsaraDB for ClickHouse are performed to merge data parts to improve query performance and reduce storage usage. Merge operations are background processes that are executed regularly and may consume significant memory.

Understanding the memory usage of merge operations can help you identify whether the merge operations cause high memory consumption in the system. You can view data in the system.merges table to understand the memory usage of the merge operations.

• View the total memory used by merge operations

  View the total memory used by merge operations on the s-2-r-0 node.

  SELECT
    formatReadableSize (sum(memory_usage))
  FROM
    clusterAllReplicas ('default', system.merges)
  WHERE
    substring(hostname (), 38, 8) = 's-2-r-0';

• View the memory usage of merge operations within a specific period of time

  View the memory usage of merge operations on the s-2-r-0 node during the period from 01:15:00 on September 17, 2023 to 01:30:00 on September 17, 2023.

  SELECT *
  FROM   clusterAllReplicas ('default', system.part_log)      -- Select records from the system.part_log table
  WHERE (event_type = 'MergeParts')                           -- Find events for which the value of the event_type parameter is MergeParts
    AND (event_time >= '2023-09-17 01:15:00')                 -- The events occurred at or after 01:15:00 on September 17, 2023
    AND (event_time <= '2023-09-17 01:30:00')                 -- The events occurred at or before 01:30:00 on September 17, 2023
    AND substring(hostname (), 38, 8) = 's-2-r-0'
  ORDER BY peak_memory_usage DESC                             -- Sort results by peak_memory_usage in descending order
  LIMIT 1    

  Parameters

  Parameter	Description
  event_type	The type of the event. You can specify different event types to obtain the information about different events. Execute the following SQL statement to obtain the event types of ApsaraDB for ClickHouse: SELECT DISTINCT event_type FROM clusterAllReplicas ('default', system.part_log) where substring(hostname (), 38, 8) = 's-2-r-0';
  event_time	The time when the event occurred.

View the memory used by data parts stored in memory

In ApsaraDB for ClickHouse, the data parts stored in memory allow for query performance improvement. The memory usage of these data parts is an important performance metric of ApsaraDB for ClickHouse. You can better manage and allocate system resources and implement system optimization by monitoring the memory usage of data parts stored in memory.

The system.parts system table provides information about the partitions and data parts of tables. You can use the system table to view the memory usage details of the data parts stored in memory.

View the size of the data parts stored in memory on the s-2-r-0 node.

SELECT
  sum(data_uncompressed_bytes)
FROM
  clusterAllReplicas ('default', system.parts)
WHERE
  part_type = 'InMemory'
  AND substring(hostname (), 38, 8) = 's-2-r-0';

View the total memory used by dictionaries

In ApsaraDB for ClickHouse, dictionaries are an important data structure used to optimize query performance, especially in cases of dimension tables and foreign keys. You can view data in the system.dictionaries table to understand the loading status and memory usage of each dictionary.

View the memory usage of dictionaries on the s-2-r-0 node.

SELECT 
  formatReadableSize(sum(bytes_allocated))
FROM clusterAllReplicas('default', system.dictionaries)
WHERE substring(hostname(), 38, 8) = 's-2-r-0';

View the memory usage of tables that use different engines

In ApsaraDB for ClickHouse, the memory occupied by tables that use engines such as Memory, Set, and Join is limited. You can monitor the memory usage of these tables to prevent errors or exceptions caused by out-of-memory (OOM).

View the memory usage of tables that use the Memory, Set, and Join engines.

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

View the memory usage of queries

You can analyze the memory usage of queries within a specific period of time. This helps identify possible memory bottlenecks or abnormal memory usage patterns within a specific period of time.

In ApsaraDB for ClickHouse, the system.query_log table records the details of queries. This table can help you audit and analyze the usage information about your database and understand query performance to troubleshoot potential issues.

View the total memory used by running queries

View the total memory used by running queries on the s-2-r-0 node.

SELECT formatReadableSize(sum(memory_usage)) -- Total memory used by running queries
FROM clusterAllReplicas('default', system.processes)
WHERE substring(hostname(), 38, 8) = 's-2-r-0';

View the memory usage of running queries

View the memory usage of running queries on the s-2-r-0 node.

-- Memory usage of running queries
SELECT initial_query_id,
       elapsed,                               --Time consumed by queries
       formatReadableSize(memory_usage),      --Memory used
       formatReadableSize(peak_memory_usage), --Requested memory
       query                                  --Query details
FROM clusterAllReplicas('default', system.processes)
WHERE substring(hostname(), 38, 8) = 's-2-r-0'
ORDER BY peak_memory_usage DESC
LIMIT 10;

View the memory usage of historical queries

View the memory usage of the queries executed on the s-2-r-0 node during the period from 17:00:00 on January 5, 2024 to 17:40:00 on January 5, 2024.

-- Memory usage of historical queries
SELECT type,
       event_time,
       initial_query_id,
       formatReadableSize(memory_usage),
       query
FROM clusterAllReplicas('default', system.query_log)
WHERE (event_time >= '2024-01-05 17:00:00')
  AND (event_time <= '2024-01-05 17:40:00')
  AND substring(hostname(), 38, 8) = 's-2-r-0'
ORDER BY memory_usage DESC
LIMIT 10;