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 http://docs.memsql.com/docs/show-status for more information.
There are a few summary rows 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_memorymemory 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."and the trace log will show
"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 row store tables (memory for rows, indexes, variable length columns like VARCHAR or JSON that are stored off row). Once
maximum_table_memory, INSERT / UPDATE / LOAD DATA operations against the tables will receive 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 grabs memory from Linux in 128KB chunks and manages that memory out to memory allocators used by row store tables or by query execution. If your application makes heavy use of row store tables, it's normal for
Buffer_manager_managerto be a large percentage of
Buffer_manager_cached_memory: Tracks memory that was allocated by the Buffer Manager, but that is now cached. This memory is not currently being used for anything right now. It isn't returned to the operating system so that MemSQL can quickly access it when it needs memory in the future. If you are asking "My table memory use in Ops is XXXX MB, why is MemSQL using so more more memory than XXXX?", then this value is the first place to look.
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 row store 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 out snapshots or take backups. Lowering the transaction-buffer system variable will reduce the amount of memory used here. There is one transaction-buffer per physical database (each row in
SHOW DATABASES EXTENDED).
Alloc_replication: Tracks memory for log files that are being replayed by replicating databases. If replication is caught up there shouldn't be very much memory mapped here. If replay is behind, this can grow large (into the GBs).
Malloc_active_memory: Tracks memory allocated directly from linux 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 pretty low (1-2 GB) for most workloads. Column store tables, open connections, and memory for metadata about tables, columns, etc. are the biggest users.
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 (it can be controlled by the
thread_stacksession variable, but we don't recommend changing it). 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 has a set of allocators it uses to for various part of an index:
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 row store tables. MemSQL tables share a single row memory allocation amongst all indexes on a particular table (other databases copy the data in the key columns into secondary indexes, we don't). Variable length columns are not stored in this allocator (VARCHAR, VARBINARY, BLOB, TEXT, JSON, etc). They are stored in
Alloc_variableas describe above. The row stores a pointer to the variable length allocation.
Alloc_deleted_version: Memory used to mark rows as deleted in row store tables. DELETE queries in MemSQL don't free up memory when they commit. They mark rows as delete and the garbage collector will come along and free this memory up when its safe to do so (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 (could be a snapshot or a backup, or a long running query, etc.).
Alloc_hash_buckets: Memory used for HASH index buckets (by default 4 million buckets per index, which would use 32 MB).
Reducing Memory Use by Row Store Tables
If row store 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 NULL are 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 datatype. A CHAR(N) column takes 3*N bytes of storage because its charset is
utfmb3. Use VARCHAR instead.
- If the workload is using DECIMAL and doesn't need fixed point math, use DOUBLE instead. 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.
- Our memory allocators can become fragmented over time (especially if a large table is shrunk dramatically by deleting data randomly). There is no command that will compact them right now, but running a no-op ALTER TABLE will do it (ALTER TABLE add index, followed by ALTER TABLE drop index). This is a pretty heavy weight work around (plans will rebuild and the two ALTERs are going to move all moves in the table twice), so probably shouldn't be used that often.
If you want a breakdown of how much memory is used by each table:
SHOW TABLE STATUShas a "
BuffMgr Memory Use" column. This includes memory use for all the components listed above in the row store 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 your in when you run the command.
INFORMATION_SCHEMA.TABLE_STATISTICSshows a breakdown of 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 row store 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 seperate component, but it shows up as rowstore memory use along with all other row store tables (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).