Identifying and Reducing Memory Usage
MemSQL keeps detailed accounting of how memory is being used. You can run the query
SHOW STATUS EXTENDED on an individual MemSQL instance to see this break down. You can also see this information in the MemSQL Ops web UI if you navigate to the status page for a MemSQL instance. You can do this from the cluster view by clicking into an individual host and then further clicking into an individual MemSQL node. See SHOW STATUS for more information.
The following are summary variables that describe overall memory use:
Total_server_memory: Tracks the server’s overall memory use. MemSQL will not let this value grow higher than
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
JSONthat are stored off row). Once
LOAD DATAoperations against the tables will receive the following error:
1720 - "Memory usage by MemSQL 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 MemSQL’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_memoryto be a large percentage of
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 MemSQL is much higher than your table memory usage, this cache may be the reason.
Buffer_manager_cached_memoryis caped at 25% of
maximum_memory. MemSQL will return freed memory to Linux once
Buffer_manager_cached_memoryis at 25% of
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.).
There are a few variables that describe memory used by components not directly related to running queries or storing data:
Alloc_durability_large: Tracks memory used to allocate transaction buffers to group commit rows to disk, as well as temporary buffers to write backups or take snapshots. Lowering the transaction buffer system variable will reduce the amount of memory used for these activities. There is one transaction buffer per physical database (each row in
SHOW DATABASES EXTENDED). Fore more information on how to lower the transaction buffer size, see Reducing Transaction Buffer.
Alloc_replication: Tracks memory for log files that are being replayed by replicating databases. If replication is caught up, this value should be low. If replay is behind, this can grow large (into the GBs).
Malloc_active_memory: Tracks memory allocated directly from the Linux OS and managed by the C runtime allocators (not MemSQL’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. MemSQL caches threads used to execute queries. Each thread has a 1 MB stack by default. This can be controlled by the
thread_stacksession variable, but it is recommended that you do not change this value. MemSQL will kill threads it hasn’t used for 24 hours which will free up stack memory (this can be controlled by the
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. MemSQL tables share a single row memory allocation amongst all indexes on a particular table. Variable-length columns are not stored in this allocator (
JSON, etc). Instead, they are stored in
Alloc_variablethat was previously discussed in this topic.
Alloc_deleted_version: Tracks memory used to mark rows as deleted in rowstore tables.
DELETEqueries in MemSQL 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).
Deleting Row Store Table Data When at the Memory Limit
If you are running near the memory limit, you may get ERROR 1712 indicating that you have
maximum_memory setting. Running
delete from tableName; in this situation may
also cause an ERROR 1712 for a large table because deleting data takes memory for each row
you delete, while the transaction containing the delete operation is running. To work around
this, you can repeatedly run a command that deletes a batch of
rows. For example, you could delete the oldest 10000 rows, then next oldest 10000
rows, and so on, either by hand or in a loop using a stored procedure, until you
have reduced memory usage sufficiently.
DELETE with a
LIMIT clause may be useful for the purpose
of batching deletes. If you have no further need for the data, or
you have another way to recover it, you can either truncate the table or
drop the table, since truncate and drop operations do not take any extra memory
Reducing Memory Use by Row Store Tables
If rowstore tables are using too much memory there are a few things you can do:
- Make sure all secondary indexes are actually needed. They are expensive (40 bytes per row).
- Make sure columns that are actually
NOT NULLare marked as
NOT NULL. Some types use an extra 4 bytes per nullable column to store the nullability state of the column (integer types for example).
- Avoid the
CHAR(N)column takes 3*N bytes of storage because its charset is
- If the workload is using
DECIMALand doesn’t need fixed point math, use
DOUBLEinstead. MemSQL does its best to use the least amount of memory possible for
DECIMAL, but a fixed point representation fundamentally needs more storage space then a floating point representation.
MemSQL’s memory allocators can become fragmented over time (especially if a large table is shrunk dramatically by deleting data randomly). There is no command currently available that will compact them, but running
ALTER TABLE ADD INDEXfollowed by
ALTER TABLE DROP INDEXwill do it.Warning
Caution should be taken with this work around. Plans will rebuild and the two
ALTERqueries are going to move all rows in the table twice, so this should not be used that often.
If you want more details on how much memory is used by each table, use the following:
SHOW TABLE STATUS has a
BuffMgr Memory Usecolumn. This includes memory use for all the components listed above in the rowstore allocator section, but broken down per table. If run on an aggregator, it will show how much memory is used across the entire cluster for the table. If run on a leaf, it will show how much memory the table is using in whichever partition database you are in when you run the command.
INFORMATION_SCHEMA.TABLE_STATISTICSlists how much data every sharded table is using on each partition in the cluster. This is a great way to check where data skew is coming from.
Columnstore Table Considerations
Columnstore tables (in MemSQL 4.1 and above) have a hidden rowstore table that buffers small inserts into the columnstore table in memory. When enough rows have accumulated in memory, the rows are removed from memory and converted to columnstore format in batch and pushed to disk. The memory use for this hidden rowstore table will show up the same way as a regular rowstore table.
Columnstore tables also store metadata about the files stored on disk in memory. Each file has a row stored in memory with some information about this file (max and min value of the column in this file, bitmap of rows that are deleted, etc.). The memory use for columnstore metadata is currently not broken out into a separate component, but it is included as rowstore memory use along with all other rowstore tables because metadata tables are implemented as hidden rowstore tables. The memory use for columnstore metadata should be small unless the columns are storing large values (the max and min value will be large in this case).