MemSQL Helios does not support setting the engine variables that are discussed in this topic.
MemSQL has two engine variables that control its memory usage. Both are measured in megabytes.
MemSQL will not allocate more than
maximum_memory megabytes of memory. If a
SELECT query’s memory allocations put the memory usage by MemSQL 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.
MemSQL will not allow writes to any table once the cumulative memory in use by all tables in MemSQL reaches
maximum_table_memory (MemSQL will become read-only).
DELETE queries will still be allowed even once the limit is reached.
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.
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 MemSQL reaches
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.
This value cannot be modified by users.
MemSQL 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_memoryis capped at 25% of the
- Buffers will only be cached if the
total_server_memoryis less than 90% of the
For more information on
total_server_memory, see the Summary Variables topic.