Skip to main content

SingleStoreDB v8.1

Identifying and Reducing Memory Usage

An example of a typical question about memory usage is, "I only have 10GB of tables but I'm using 67GB of RAM? What is that additional memory being used for?"

SingleStoreDB keeps detailed accounting of how memory is being used. You can run the command SHOW STATUS EXTENDED on an individual SingleStoreDB instance to see a detailed break down of memory usage. You can also see this information in the Dashboard of the Studio UI, or if you navigate to the status page for a SingleStoreDB instance. You can do this from the cluster view by clicking into an individual host and then further clicking into an individual SingleStoreDB node.

Summary variables

The following are summary variables that describe overall memory use:

  • Total_server_memory: Tracks the server’s overall memory use. SingleStoreDB will not let this value grow higher than maximum_memory. When Total_server_memory reaches maximum_memory, memory allocations will start failing. Queries will 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 will show 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 will receive the following error: 1720 - "Memory usage by SingleStoreDB 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 SingleStoreDB’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 your 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 you notice that your overall memory usage for SingleStoreDB is much higher than your table memory usage, this cache may be the reason. Buffer_manager_cached_memory is capped at 25% of maximum_memory. SingleStoreDB will return freed memory to Linux once Buffer_manager_cached_memory is at 25% of maximum_memory. For more information, see Configuring Memory Limits.

  • 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. SingleStoreDB 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 SingleStoreDB don’t free up memory when they commit. They mark rows as deleted and the garbage collector frees this memory up when its 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 SingleStoreDB’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. SingleStoreDB caches threads used to execute queries. Each thread has a 1 MB stack by default. This can be controlled by the thread_stack session variable, but it is recommended that you do not change this value. SingleStoreDB will kill threads it hasn’t used for 24 hours which will free up stack memory (this can be controlled by the idle_thread_lifetime_seconds variable).

In this section: