Managing Memory min read

SingleStore DB has two engine variables that control its memory usage. Both are measured in megabytes.

maximum_memory

SingleStore DB will not allocate more than maximum_memory megabytes of memory. If a SELECT query’s memory allocations put the memory usage by SingleStore DB over maximum_memory, query execution stops and an error message is generated.

Query compilations whose allocations exceed this limit will also terminate the server. See Code Generation for more information on query compilations. By default, maximum_memory is set to 90% of the physical memory on the host machine if no swap is enabled, and 100% of physical memory on the machine if swap is enabled.

maximum_table_memory

SingleStore DB will not allow writes to any table once the cumulative memory in use by all tables in SingleStore DB reaches maximum_table_memory (SingleStore DB will become read-only). SELECT and DELETE queries will still be allowed even once the limit is reached. UPDATE, INSERT , CREATE TABLE, ALTER TABLE, CREATE INDEX or DROP INDEX statements will fail with an error message once the limit has been reached. Query compilations are also disallowed once the limit is reached.

This setting is designed to allow SELECT queries to allocate temporary memory for sorting, hash group-by, and so on. The maximum_table_memory must be set to a value lower then maximum_memory. By default, maximum_table_memory is set to 90% of maximum_memory, which translates to about 80% of physical memory on the host machine.

If the maximum_table_memory limit has been reached, DELETE queries can still be executed to remove data from the table; however large DELETE queries may fail if the memory used by SingleStore DB reaches maximum_memory.

Caution should be taken as DELETE queries allocate extra memory to mark rows as deleted. For rowstore tables, this equates to roughly 40 + 8*number_of_indexes bytes per deleted row. For columnstore tables, the memory usage will be lower because of how rows are marked to be deleted (roughly num_rows_in_table/8 bytes if you delete a row in every segment file in the table).

If the table is narrow, such as containing a small number of int columns, DELETE queries will show up as a relatively large spike in memory usage compared to the size of the table.

The memory for a deleted row is reclaimed after the transaction commits and the memory is freed asynchronously by the garbage collector. For more information, see the DELETE reference topic.

Replicating databases will pause if memory use reaches maximum_table_memory while replicating data. If memory becomes available again - say some data is deleted - replication will automatically continue.

buffer_manager_cached_memory

SingleStore DB caches memory (allocated by the Buffer Manager) that is not in use, instead of returning it to the system. The buffer_manager_cached_memory variable keeps a track of this cached memory. Let’s say a query consumes XXX GBs of memory for execution. Once the query execution completes, instead of being returned to the system (Linux), this memory is stored as the Buffer Manager cached memory. Thereafter, if some other query needs memory, it will get that memory from the cache. This process avoids system call/contention over access to memory, which can be substantial. The size of memory reserved for each node in the cache can be viewed in the output of the SHOW STATUS EXTENDED command for each node. For example,

SHOW STATUS EXTENDED LIKE 'buffer_manager_cached%';
****
+------------------------------+---------+
| Variable_name                |  Value  |
+------------------------------+---------+
| Buffer_manager_cached_memory | 80.0 MB |
+------------------------------+---------+

buffer_manager_cached_memory has following limitations:

• buffer_manager_cached_memory is capped at 25% of the maximum_memory.
• Buffers will only be cached if the total_server_memory is less than 90% of the maximum_memory

For more information on buffer_manager_cached_memory and total_server_memory, see the Summary Variables topic.

Was this Article Helpful?

Thanks For Your Feedback!