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.

Summary variables

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. When Total_server_memory reaches 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 VARCHAR or JSON that are stored off row). Once Alloc_table_memory reaches maximum_table_memory, INSERT, UPDATE, and LOAD DATA operations 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_memory to be a large percentage of Total_server_memory.

• 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_memory is caped at 25% of maximum_memory. MemSQL will return freed memory to Linux once Buffer_manager_cached_memory is at 25% of maximum_memory.

• 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.).

Other variables

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_stack session 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 idle_thread_lifetime_seconds variable).

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 (VARCHAR, VARBINARY, BLOB, TEXT, JSON, etc). Instead, they are stored in Alloc_variable that was previously discussed in this topic.

• Alloc_deleted_version: Tracks memory used to mark rows as deleted in rowstore tables. DELETE queries 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 exceeded the 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 per row.

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 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.

• 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 INDEX followed by ALTER TABLE DROP INDEX will do it.

  Warning

  Caution should be taken with this work around. Plans will rebuild and the two ALTER queries 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 Use column. 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_STATISTICS lists 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).

Was this Article Helpful?

Thanks For Your Feedback!