Sign inTry Free

Identifying and Reducing Memory Usage

On this page

SingleStore maintains a detailed account of memory usage.

Query the MV_MEMORY_USAGE information schema view or run the SHOW STATUS EXTENDED command to view memory usage.

MV_MEMORY_USAGE

The MV_MEMORY_USAGE 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_PROFILEs) 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.

SELECT *
FROM information_schema.MV_MEMORY_USAGE
WHERE NODE_ID = 1
  AND GROUP_ID = 1;
*** 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.

SELECT *
FROM information_schema.MV_MEMORY_USAGE
WHERE NODE_ID = 1
  AND GROUP_ID = 10
ORDER BY ALLOC_BYTES DESC
LIMIT 3;
*** 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.

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.

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 for additional information.

Groups

The following query lists the ALLOC_PROFILEs and their group ID.

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.

SELECT *
FROM information_schema.MV_MEMORY_USAGE
WHERE GROUP_ID = 1;
*** 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.

SELECT *
FROM information_schema.MV_MEMORY_USAGE
WHERE GROUP_ID = 10
ORDER BY NODE_ID, ALLOC_BYTES DESC;
+---------+-------------+---------------------------+----------------------+------------+----------+------------------------+-----------------+
| 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.

SELECT NODE_ID, SUM(ALLOC_BYTES)
FROM information_schema.MV_MEMORY_USAGE
WHERE GROUP_ID = 10
GROUP BY NODE_ID;
+---------+------------------+
| 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.

SELECT *
FROM information_schema.MV_MEMORY_USAGE
WHERE NODE_ID = 1 AND OWNER_TYPE LIKE 'USER_TABLE'
ORDER BY ALLOC_BYTES DESC LIMIT 2;
+---------+-------------+-------------------------+------------------------------------------+------------+----------+--------------+-----------------+
| 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;
+---------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 summary variables describe overall memory usage.

Total_server_memory

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

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

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

Buffer_manager_cached_memory tracks cached memory blocks allocated by the Buffer Manager that are currently not in use, but still mapped. Keeping these blocks cached allows later allocations to reuse them without requesting memory from the operating system. Table garbage collection and query execution commonly add freed memory to this cache. Other freed Buffer Manager memory can also be added when it is safe to reuse. Buffer_manager_unrecycled_memory reflects the memory that may still be referenced until the check is complete.

While Total_server_memory is at or below 90% of maximum_memory or at least 10 GB of free memory remains, Buffer_manager_cached_memory is capped at 25% of maximum_memory on each node. When the cache size reaches the capped limit, additional freed memory blocks (or buffers) are returned to the operating system. Buffers that are already cached are still mapped and left in place, therefore crossing the capped limit does not trim the cache. The cache shrinks when a subsequent allocation uses the cached buffer, and it can get unmapped if another allocation may otherwise exceed maximum_memory.

When Total_server_memory is above 90% of maximum_memory and less than 10 GB of free memory remains, newly freed buffers are cached only until the cache size reaches approximately 1% of maximum_memory (vs. the default 25%). Existing cached buffers are not trimmed when this limit applies.

As cached memory stays mapped, Total_server_memory includes Buffer_manager_cached_memory. After a large rowstore DELETE, Alloc_table_memory can fall while Total_server_memory stays high because the freed memory blocks are still held as unrecycled or cached memory.

Refer to buffer_manager_cached_memory for more information.

Alloc_query_execution

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

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.).

Alloc_vector_index

Alloc_vector_index specifies the total size of vector indexes currently residing in memory. The vector index cache loads indexes when they are used and evicts them using an LRU policy when the cache reaches its capacity.

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

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

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

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

Alloc_hash_buckets tracks memory used for HASH index buckets (by default 4 million buckets per index, which would use 32 MB).

Other variables

A few variables describe memory used by components that are not directly related to running queries or storing data.

Alloc_replication

Alloc_replication tracks the amount of memory used during replication.

Malloc_active_memory

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

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

Last modified:

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In