# Identifying and Reducing Memory Usage SingleStore keeps detailed accounting of how memory is being used. Find memory usage with the `MV_MEMORY_USAGE` information schema view or the `SHOW STATUS EXTENDED` command. ## MV\_MEMORY\_USAGE The [MV\_MEMORY\_USAGE](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/mv-memory-usage.md) information schema view provides fine-grained memory usage information. Use `MV_MEMORY_USAGE` to monitor and identify the most significant memory consumers, including running processes, tables in memory, or internal data structures of running queries. ## Introductory Example This example shows how to use `MV_MEMORY_USAGE` to break down and understand memory usage at a fine-grained level. In `MV_MEMORY_USAGE`, groups and sub-groups provide a memory usage hierarchy. Group 1 indicates the total memory used by the server. Group 10 is a subgroup of group 1, and groups 200, 500 are subgroups of group 10. Memory usages (`ALLOC_PROFILE`s) with the same ID are disjoint and can be aggregated. > **📝 Note**: Do not aggregate across different groups (different IDs). Aggregating across different group IDs results in double counting. The following query displays the total amount of memory used on node 1. The predicate `GROUP_ID = 1` indicates the `ALLOC_PROFILE` `Total_server_memory`, which displays total memory used by the server. ```sql SELECT * FROM information_schema.MV_MEMORY_USAGE WHERE NODE_ID = 1 AND GROUP_ID = 1; ``` ```output *** 1. row *** NODE_ID: 1 ALLOC_BYTES: 298188800 ALLOC_PROFILE: Total_server_memory OWNER: {"SERVER":"MEMSQLD"} OWNER_TYPE: SERVER GROUP_ID: 1 SUBGROUP_IDS: {"subgroup_ids":[10]} AGGREGATE_COUNT: 1 ``` To obtain a breakdown of the total memory used by the server (`Total_server_memory`), use the IDs from the `SUBGROUP_IDS` field. The following query uses the predicate `GROUP_ID=10`, where `10` is from the `SUBGROUP_IDS` from the preceding query, to show memory usage for each of those subgroups. The `ORDER BY ALLOC_BYTES DESC` clause sorts by memory usage to identify the top memory users. ```sql SELECT * FROM information_schema.MV_MEMORY_USAGE WHERE NODE_ID = 1 AND GROUP_ID = 10 ORDER BY ALLOC_BYTES DESC LIMIT 3; ``` ```output *** 1. row *** NODE_ID: 1 ALLOC_BYTES: 159776768 ALLOC_PROFILE: Total_tracked_mmap_memory OWNER: {"SERVER":"MEMSQLD"} OWNER_TYPE: SERVER GROUP_ID: 10 SUBGROUP_IDS: NULL AGGREGATE_COUNT: 1 *** 2. row *** NODE_ID: 1 ALLOC_BYTES: 69638456 ALLOC_PROFILE: Malloc_active_memory OWNER: {"SERVER":"MEMSQLD"} OWNER_TYPE: SERVER GROUP_ID: 10 SUBGROUP_IDS: {"subgroup_ids":[500]} AGGREGATE_COUNT: 1 *** 3. row *** NODE_ID: 1 ALLOC_BYTES: 60948480 ALLOC_PROFILE: Buffer_manager_memory OWNER: {"SERVER":"MEMSQLD"} OWNER_TYPE: SERVER GROUP_ID: 10 SUBGROUP_IDS: {"subgroup_ids":[200]} AGGREGATE_COUNT: 1 ``` To break down memory used by `Malloc_active_memory`, query the subgroup of `Malloc_active_memory`, ID `500`, using the following query. ```sql SELECT * FROM information_schema.MV_MEMORY_USAGE WHERE NODE_ID = 1 AND GROUP_ID = 500 ORDER BY ALLOC_BYTES DESC; ``` To break down memory used by `Buffer_manager_memory`, query the subgroup of `Buffer_manager_memory`, ID `200`, using the following query. ```sql SELECT * FROM information_schema.MV_MEMORY_USAGE WHERE NODE_ID = 1 AND GROUP_ID = 200 ORDER BY ALLOC_BYTES DESC; ``` > **❗ Important**: It is acceptable to aggregate memory groups with the same ID. Do not aggregate across different groups. Aggregating across different groups results in double counting. ## Remarks * The value of `ALLOC_BYTES` may be negative. A negative `ALLOC_BYTES` indicates the entity has freed memory that was allocated by other threads. A garbage collector is an example of an entity that frees memory allocated by other threads. * Refer to [MV\_MEMORY\_USAGE](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/mv-memory-usage.md) for additional information. ## Groups The following query lists the `ALLOC_PROFILE`s and their group ID. ```sql SELECT GROUP_CONCAT(DISTINCT ALLOC_PROFILE) AS AllocProfileList, GROUP_ID FROM information_schema.MV_MEMORY_USAGE GROUP BY GROUP_ID ORDER BY GROUP_ID; ``` ## Examples ## Example 1 - Find Total Memory Usage by Node Find the total memory usage (`Total_server_memory`) of each node in a cluster using the following query. The cluster in this example has 3 nodes - 1 aggregator and 2 leaf nodes. `GROUP_ID = 1` indicates `Total_server_memory`. ```sql SELECT * FROM information_schema.MV_MEMORY_USAGE WHERE GROUP_ID = 1; ``` ```output *** 1. row *** NODE_ID: 1 ALLOC_BYTES: 219938816 ALLOC_PROFILE: Total_server_memory OWNER: {"SERVER":"MEMSQLD"} OWNER_TYPE: SERVER GROUP_ID: 1 SUBGROUP_IDS: {"subgroup_ids":[10]} AGGREGATE_COUNT: 1 *** 2. row *** NODE_ID: 2 ALLOC_BYTES: 221380608 ALLOC_PROFILE: Total_server_memory OWNER: {"SERVER":"MEMSQLD"} OWNER_TYPE: SERVER GROUP_ID: 1 SUBGROUP_IDS: {"subgroup_ids":[10]} AGGREGATE_COUNT: 1 *** 3. row *** NODE_ID: 3 ALLOC_BYTES: 221380608 ALLOC_PROFILE: Total_server_memory OWNER: {"SERVER":"MEMSQLD"} OWNER_TYPE: SERVER GROUP_ID: 1 SUBGROUP_IDS: {"subgroup_ids":[10]} AGGREGATE_COUNT: 1 ``` ## Example 2 - Find Top Types of Memory Usage by Node Example 1 shows the total memory used by node. The following query breaks down that memory usage. In Example 1, group 10 is shown as the subgroup of group 1 (`Total_server_memory`). Thus the following query uses that ID in the predicate (`GROUP_ID=10`) to show a breakdown of the results from Example 1. ```sql SELECT * FROM information_schema.MV_MEMORY_USAGE WHERE GROUP_ID = 10 ORDER BY NODE_ID, ALLOC_BYTES DESC; ``` ```output +---------+-------------+---------------------------+----------------------+------------+----------+------------------------+-----------------+ | NODE_ID | ALLOC_BYTES | ALLOC_PROFILE | OWNER | OWNER_TYPE | GROUP_ID | SUBGROUP_IDS | AGGREGATE_COUNT | +---------+-------------+---------------------------+----------------------+------------+----------+------------------------+-----------------+ | 1 | 177995776 | Total_tracked_mmap_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | NULL | 1 | | 1 | 27131904 | Buffer_manager_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | {"subgroup_ids":[200]} | 1 | | 1 | 10069424 | Malloc_active_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | {"subgroup_ids":[500]} | 1 | | 1 | 7340032 | Total_thread_stack_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | NULL | 1 | | 1 | 786432 | Total_io_pool_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | NULL | 1 | | 2 | 177340416 | Total_tracked_mmap_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | NULL | 1 | | 2 | 28049408 | Buffer_manager_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | {"subgroup_ids":[200]} | 1 | | 2 | 8393952 | Malloc_active_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | {"subgroup_ids":[500]} | 1 | | 2 | 8388608 | Total_thread_stack_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | NULL | 1 | | 2 | 786432 | Total_io_pool_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | NULL | 1 | | 3 | 177340416 | Total_tracked_mmap_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | NULL | 1 | | 3 | 28180480 | Buffer_manager_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | {"subgroup_ids":[200]} | 1 | | 3 | 8388608 | Total_thread_stack_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | NULL | 1 | | 3 | 8366984 | Malloc_active_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | {"subgroup_ids":[500]} | 1 | | 3 | 655360 | Total_io_pool_memory | {"SERVER":"MEMSQLD"} | SERVER | 10 | NULL | 1 | +---------+-------------+---------------------------+----------------------+------------+----------+------------------------+-----------------+ ``` ## Example 3 - Find Memory Usage by Node Using Aggregation The following query aggregates memory usage for all items with ID 10. The results closely match the results from Example 1. Small differences occur due to the timing of the queries and memory usage changing in a running system. This query demonstrates aggregation within a group. ```sql SELECT NODE_ID, SUM(ALLOC_BYTES) FROM information_schema.MV_MEMORY_USAGE WHERE GROUP_ID = 10 GROUP BY NODE_ID; ``` ```output +---------+------------------+ | node_id | sum(ALLOC_BYTES) | +---------+------------------+ | 1 | 221360784 | | 3 | 221528896 | | 2 | 221949104 | +---------+------------------+ ``` ## Example 4 - Find the User Tables Using the Most Memory Find the top two user tables that use the most memory on a particular node, node 1 in this example. ```sql SELECT * FROM information_schema.MV_MEMORY_USAGE WHERE NODE_ID = 1 AND OWNER_TYPE LIKE 'USER_TABLE' ORDER BY ALLOC_BYTES DESC LIMIT 2; ``` ```output +---------+-------------+-------------------------+------------------------------------------+------------+----------+--------------+-----------------+ | NODE_ID | ALLOC_BYTES | ALLOC_PROFILE | OWNER | OWNER_TYPE | GROUP_ID | SUBGROUP_IDS | AGGREGATE_COUNT | +---------+-------------+-------------------------+------------------------------------------+------------+----------+--------------+-----------------+ | 1 | 8519680 | Buffer_memory_for_table | {"DATABASE":"XDB_0","TABLE":"my_table2"} | USER_TABLE | 400 | NULL | 1 | | 1 | 7208960 | Buffer_memory_for_table | {"DATABASE":"XDB_0","TABLE":"Xtab1"} | USER_TABLE | 400 | NULL | 1 | +---------+-------------+-------------------------+------------------------------------------+------------+----------+--------------+-----------------+ ``` ## Example 5 - Find the Top Three Malloc Memory Users Find the top three tasks or processes that are using the most Malloc memory on node 1. ``` SELECT NODE_ID, ALLOC_BYTES, OWNER FROM information_schema.MV_MEMORY_USAGE WHERE ALLOC_PROFILE LIKE 'Malloc' AND NODE_ID = 1 ORDER BY ALLOC_BYTES DESC LIMIT 3; ``` ```output +---------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | NODE_ID | ALLOC_BYTES | OWNER | +---------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 161122592 | {"ACTIVITY":"memsqld_main","ACTIVITY_TRACKING_ID":null,"AGGREGATOR_ACTIVITY":"memsqld_main","DATABASE":null,"RAW_TASK":null,"THREAD_ID":1176552,"TYPE":"System"} | | 1 | 17881984 | {"ACTIVITY":"Unclassified_malloc_total","ACTIVITY_TRACKING_ID":null,"AGGREGATOR_ACTIVITY":null,"DATABASE":null,"RAW_TASK":null,"THREAD_ID":-1,"TYPE":null} | | 1 | 185888 | {"ACTIVITY":"ReplLogContainer::BottomlessUploadLogChunksThreadFn","ACTIVITY_TRACKING_ID":null,"AGGREGATOR_ACTIVITY":"ReplLogContainer::BottomlessUploadLogChunksThreadFn","DATABASE":null,"RAW_TASK":null,"THREAD_ID":1176585,"TYPE":"System"} | +---------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ ``` ## SHOW STATUS EXTENDED The `SHOW STATUS EXTENDED` command displays a list of engine variable values as described in the following sections. ## Summary variables The following are summary variables that describe overall memory use: * `Total_server_memory`: Tracks the server’s overall memory use. SingleStore does not let this value grow higher than `maximum_memory`. When `Total_server_memory` reaches `maximum_memory`, memory allocations start failing. Queries then fail with the error `1712 - "Not enough memory available to complete the current request. The request was not processed."` In addition, the trace log shows the following: `"Nonfatal buffer manager memory allocation failure. The maximum_memory parameter (XXXXX MB) has been reached."` * `Alloc_table_memory`: Tracks the memory stored inside of all rowstore tables (memory for rows, indexes, variable-length columns like `VARCHAR` or `JSON` that are stored off row). Once `Alloc_table_memory` reaches `maximum_table_memory`, `INSERT`, `UPDATE`, and `LOAD DATA` operations against the tables receive the following error: `1720 - "Memory usage by SingleStore for tables (XXXXX MB) has reached the value of 'maximum_table_memory' global variable (YYYYY MB). This query cannot be executed.".` * `Buffer_manager_memory`: Tracks memory that is allocated by the Buffer Manager for SingleStore’s built-in memory allocators. The Buffer Manager is a component that consumes memory from the Linux OS in 128KB blocks and manages that memory out to memory allocators used by rowstore tables or by query execution. If the application makes heavy use of rowstore tables, it’s normal for `Buffer_manager_memory` to be a large percentage of `Total_server_memory`. * `Buffer_manager_cached_memory`: Tracks memory that was allocated by the Buffer Manager, but is now cached and not in use. If the overall memory usage for SingleStore is much higher than the table memory usage, this cache may be the reason. `Buffer_manager_cached_memory` is capped at 25% of `maximum_memory`. SingleStore returns freed memory to Linux once `Buffer_manager_cached_memory` is at 25% of `maximum_memory`. For more information, see [Configuring Memory Limits](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory.md). * `Alloc_query_execution`: Tracks memory allocated by currently executing queries for sorts, hash tables, result tables, etc. If no queries are running, this value should be 0. * `Alloc_variable`: Tracks memory allocated for variable-length columns inside rowstore tables, or for other variable-length memory allocations inside query execution (i.e. temporary allocations inside of string expressions, etc.). ## Row store variables Row store has a set of allocators it uses for various part of an index. These values can be helpful when determining rowstore table size. * `Alloc_skiplist_towers`: Tracks memory used by the towers for skiplist indexes. Each skiplist index uses on average 40 bytes of memory per row using this allocator. The exact amount of memory per row is probabilistic. It depends on the randomized tower height of the particular row. * `Alloc_table_primary`: Tracks memory used for on-row data for rowstore tables. SingleStore tables share a single row memory allocation amongst all indexes on a particular table. Variable-length columns are not stored in this allocator (`VARCHAR`, `VARBINARY`, `BLOB`, `TEXT`, `JSON`, etc). Instead, they are stored in `Alloc_variable` that was previously discussed in this topic. * `Alloc_deleted_version`: Tracks memory used to mark rows as deleted in rowstore tables. `DELETE` queries in SingleStore don’t free up memory when they commit. They mark rows as deleted and the garbage collector frees this memory up when it is safe to do so (i.e. no query or operation is using the deleted row anymore). If this number is large, it means the garbage collector is behind or some operation is preventing the garbage collector from physically freeing the memory used by deleted rows. Examples of this could be a snapshot or a backup, or a long running query, etc. * `Alloc_hash_buckets`: Tracks memory used for HASH index buckets (by default 4 million buckets per index, which would use 32 MB). ## Other variables There are a few variables that describe memory used by components not directly related to running queries or storing data: * `Alloc_replication`: Tracks the amount of memory used during replication. * `Malloc_active_memory`: Tracks memory allocated directly from the Linux OS and managed by the C runtime allocators (not SingleStore’s built-in memory allocators that use the Buffer Manager). The memory use here should be approximately 1-2 GBs for most workloads. Column store tables, open connections, and memory for metadata about tables, columns, etc. are the biggest consumers of memory. * `Alloc_thread_stacks`: Tracks memory used by thread stacks. SingleStore caches threads used to run queries. Each thread has a 1 MB stack by default. This can be controlled by the `thread_stack` session variable, but SingleStore does not recommend changing this value. SingleStore kills threads it hasn’t used for 24 hours which frees up stack memory (this can be controlled by the `idle_thread_lifetime_seconds` variable). ## In this section * [Deleting Row Store Table Data When at the Memory Limit](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/identifying-and-reducing-memory-usage/deleting-row-store-table-data-when-at-the-memory-limit.md) * [Reducing Memory Use by Row Store Tables](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/identifying-and-reducing-memory-usage/reducing-memory-use-by-row-store-tables.md) * [Columnstore Table Considerations](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/identifying-and-reducing-memory-usage/columnstore-table-considerations.md) * [Understanding Memory and Disk Usage with Studio](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/identifying-and-reducing-memory-usage/understanding-memory-and-disk-usage-with-studio.md) * [Investigating Memory Usage Discrepancies](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/identifying-and-reducing-memory-usage/investigating-memory-usage-discrepancies.md) *** Modified at: May 11, 2026 Source: [/db/v9.1/reference/troubleshooting-reference/identifying-and-reducing-memory-usage/](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/identifying-and-reducing-memory-usage/) (An index of the documentation is available at /llms.txt)