Articles in this section

See more

Getting a Granular View of Memory Consumption in SingleStore

{question}

How can I obtain a more detailed view of what is consuming memory in Singlestore?

{question}

{answer}

SingleStore maintains a detailed account of memory usage. Memory usage is monitored within their respective memory allocators, which can all be located by executing SHOW STATUS EXTENDED on the node in question or INFORMATION_SCHEMA.MV_GLOBAL_STATUS, where all memory allocators for all nodes can be seen.

Understanding Singlestore's Memory Allocators

The Total_server_memory value in INFORMATION_SCHEMA.MV_GLOBAL_STATUS and SHOW STATUS EXTENDED are composed of several individual memory allocators, which represent different internal memory usage categories in SingleStore. These allocators follow a hierarchical structure:

  • Total_server_memory includes:

    • Alloc_thread_stacks

    • Malloc_active_memory

    • Buffer_manager_memory

    • Total_io_pool_memory

    • Alloc_replication_large

    • Alloc_durability_large

    • Alloc_mmap_memory

    • Alloc_compiled_unit_sections

    • Alloc_object_code_images

    • Alloc_unit_ifn_thunks

    • Alloc_unit_images

  • Buffer_manager_memory is a subset of Total_server_memory, and itself includes:

    • Buffer_manager_cached_memory

    • Alloc_query_execution

    • Alloc_table_memory

  • Alloc_table_memory, in turn, is made up of:

    • Alloc_skiplist_tower

    • Alloc_variable

    • Alloc_large_variable

    • Alloc_table_primary

    • Alloc_deleted_version

    • Alloc_internal_key_node

    • Alloc_hash_buckets

    • Alloc_table_autostats

Additionally, memory usage related to internal tables (such as system metadata and statistics, including pipeline metadata, bottomless database metadata, and more) is tracked in the INFORMATION_SCHEMA.INTERNAL_TABLE_STATISTICS view. The memory consumption from those internal tables is included in the Alloc_table_memory allocator.

Understanding Singlestore's memory allocator hierarchy enables you to identify the largest memory consumers, guiding your efforts on where to start troubleshooting memory issues.

 

Reviewing Memory Allocators

  • Click the respective drop-downs below to see the full query text.
    • Note: The query below includes a column for overall internal metadata memory usage, shown as METADATA_MEM_GB. This value is computed using INFORMATION_SCHEMA.INTERNAL_TABLE_STATISTICS and joined by host, so you can quickly see how much memory is used by internal system tables.
▼  All Memory Allocators 
SELECT
    mvgs.NODE_ID,
    mvgs.IP_ADDR AS HOST,
    mvgs.PORT,
    mvgs.NODE_TYPE,
    INTERNAL.METADATA_MEM_GB,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_table_autostats' THEN mvgs.VARIABLE_VALUE END) AS Alloc_table_autostats,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_thread_stacks' THEN mvgs.VARIABLE_VALUE END) AS Alloc_thread_stacks,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Malloc_active_memory' THEN mvgs.VARIABLE_VALUE END) AS Malloc_active_memory,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_large_variable' THEN mvgs.VARIABLE_VALUE END) AS Alloc_large_variable,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_hash_buckets' THEN mvgs.VARIABLE_VALUE END) AS Alloc_hash_buckets,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_mmap_memory' THEN mvgs.VARIABLE_VALUE END) AS Alloc_mmap_memory,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_compiled_unit_sections' THEN mvgs.VARIABLE_VALUE END) AS Alloc_compiled_unit_sections,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_unit_ifn_thunks' THEN mvgs.VARIABLE_VALUE END) AS Alloc_unit_ifn_thunks,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_unit_images' THEN mvgs.VARIABLE_VALUE END) AS Alloc_unit_images,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_replication_large' THEN mvgs.VARIABLE_VALUE END) AS Alloc_replication_large,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_durability_large' THEN mvgs.VARIABLE_VALUE END) AS Alloc_durability_large,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_object_code_images' THEN mvgs.VARIABLE_VALUE END) AS Alloc_object_code_images,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_skiplist_tower' THEN mvgs.VARIABLE_VALUE END) AS Alloc_skiplist_tower,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_variable' THEN mvgs.VARIABLE_VALUE END) AS Alloc_variable,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_table_primary' THEN mvgs.VARIABLE_VALUE END) AS Alloc_table_primary,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_deleted_version' THEN mvgs.VARIABLE_VALUE END) AS Alloc_deleted_version,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_internal_key_node' THEN mvgs.VARIABLE_VALUE END) AS Alloc_internal_key_node,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_variable_cached_buffers' THEN mvgs.VARIABLE_VALUE END) AS Alloc_variable_cached_buffers,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_variable_allocated' THEN mvgs.VARIABLE_VALUE END) AS Alloc_variable_allocated,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Buffer_manager_memory' THEN mvgs.VARIABLE_VALUE END) AS Buffer_manager_memory,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_table_memory' THEN mvgs.VARIABLE_VALUE END) AS Alloc_table_memory,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_query_execution' THEN mvgs.VARIABLE_VALUE END) AS Alloc_query_execution,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Buffer_manager_cached_memory' THEN mvgs.VARIABLE_VALUE END) AS Buffer_manager_cached_memory,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_background_tasks' THEN mvgs.VARIABLE_VALUE END) AS Alloc_background_tasks,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Total_server_memory' THEN mvgs.VARIABLE_VALUE END) AS Total_server_memory
FROM
    information_schema.MV_GLOBAL_STATUS mvgs
JOIN (
    SELECT
        HOST,
        SUM(memory_use)/1024/1024/1024 AS METADATA_MEM_GB
    FROM information_schema.INTERNAL_TABLE_STATISTICS
    GROUP BY 1
) AS INTERNAL ON mvgs.IP_ADDR = INTERNAL.HOST
WHERE
    mvgs.VARIABLE_NAME IN (
        'Alloc_table_autostats',
        'Alloc_thread_stacks',
        'Malloc_active_memory',
        'Alloc_large_variable',
        'Alloc_hash_buckets',
        'Alloc_mmap_memory',
        'Alloc_compiled_unit_sections',
        'Alloc_unit_ifn_thunks',
        'Alloc_unit_images',
        'Alloc_replication_large',
        'Alloc_durability_large',
        'Alloc_object_code_images',
        'Alloc_skiplist_tower',
        'Alloc_variable',
        'Alloc_table_primary',
        'Alloc_deleted_version',
        'Alloc_internal_key_node',
        'Alloc_variable_cached_buffers',
        'Alloc_variable_allocated',
        'Buffer_manager_memory',
        'Alloc_table_memory',
        'Alloc_query_execution',
        'Buffer_manager_cached_memory',
        'Alloc_background_tasks',
        'Total_server_memory'
    )
GROUP BY
    mvgs.NODE_ID,
    mvgs.IP_ADDR,
    mvgs.PORT,
    mvgs.NODE_TYPE,
    INTERNAL.METADATA_MEM_GB
ORDER BY
    HOST;

 

▼  Alloc_table_memory and it's subset memory allocators 
SELECT
    mvgs.NODE_ID,
    mvgs.IP_ADDR AS HOST,
    mvgs.PORT,
    mvgs.NODE_TYPE,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_skiplist_tower' THEN mvgs.VARIABLE_VALUE END) AS Alloc_skiplist_tower,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_variable' THEN mvgs.VARIABLE_VALUE END) AS Alloc_variable,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_large_variable' THEN mvgs.VARIABLE_VALUE END) AS Alloc_large_variable,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_table_primary' THEN mvgs.VARIABLE_VALUE END) AS Alloc_table_primary,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_deleted_version' THEN mvgs.VARIABLE_VALUE END) AS Alloc_deleted_version,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_internal_key_node' THEN mvgs.VARIABLE_VALUE END) AS Alloc_internal_key_node,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_hash_buckets' THEN mvgs.VARIABLE_VALUE END) AS Alloc_hash_buckets,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_table_autostats' THEN mvgs.VARIABLE_VALUE END) AS Alloc_table_autostats,
    MAX(CASE WHEN mvgs.VARIABLE_NAME = 'Alloc_table_memory' THEN mvgs.VARIABLE_VALUE END) AS Alloc_table_memory,
    INTERNAL.METADATA_MEM_GB
FROM
    information_schema.MV_GLOBAL_STATUS mvgs
JOIN (
    SELECT
        HOST,
        SUM(memory_use)/1024/1024/1024 AS METADATA_MEM_GB
    FROM information_schema.INTERNAL_TABLE_STATISTICS
    GROUP BY 1
) AS INTERNAL ON mvgs.IP_ADDR = INTERNAL.HOST
WHERE
    mvgs.VARIABLE_NAME IN (
        'Alloc_skiplist_tower',
        'Alloc_variable',
        'Alloc_large_variable',
        'Alloc_table_primary',
        'Alloc_deleted_version',
        'Alloc_internal_key_node',
        'Alloc_hash_buckets',
        'Alloc_table_autostats',
        'Alloc_table_memory'
    )
GROUP BY
    mvgs.NODE_ID,
    mvgs.IP_ADDR,
    mvgs.PORT,
    mvgs.NODE_TYPE,
    INTERNAL.METADATA_MEM_GB
ORDER BY
    HOST;
 
 
 

{answer}

Related articles