Memory Limit Configuration¶
MemSQL has two system 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.
If an UPDATE, INSERT, DELETE, CREATE TABLE, ALTER TABLE, CREATE INDEX or DROP INDEX statement’s memory allocations put the memory usage by MemSQL over maximum_memory, the server will terminate. See the section below on maximum_table_memory for how to avoid allowing write queries to exceeded maximum_memory.
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 the amount of physical memory on the host machine.
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). SELECT queries will still be allowed even once the limit is reached. UPDATE, INSERT, DELETE, 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, etc. 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.
If the maximum_table_memory limit has been reached, running DELETE queries is blocked. DELETEs allocate extra memory to mark the row deleted. The memory for a deleted row is reclaimed asynchronously by a garbage collector. If you want to remove some data once the maximum_table_memory has been reached the best practice is to
- FLUSH TABLES WITH READ LOCK to prevent writes to all tables in the database from other connections
- SET GLOBAL maximum_table_memory = <higher value> to raise the maximum_table_memory limit
- Run the DELETE statements as needed to reduce memory usage. The delete statement will increase memory use until the transaction commits and the rows are garbage collected so avoid running queries which delete a large number of rows in a single transaction.
- Run OPTIMIZE TABLE on any tables that were modified to force garbage collection
- Restore maximum_table_memory to its previous value
- UNLOCK TABLES