Memory usage explanation

We continues to see increasing memory usage without knowing the exact cause behind. I seems like the usage is not following data insertion.

One leaf with 47 GB memory available claims 37 GB usage. However, from the SHOW STATUS EXTENDED the Alloc_table_memory is only around 24 GB which I guess is the sum of Alloc_skiplist_tower + Alloc_variable + Alloc_table_primary. If this is the data store usage, what is consuming the last 13 GB?

I’ve dropped all plancache, so the only thing left I can think of is full text indexes?

+-----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                             | Value                                                                                                                                            |
+-----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Aborted_clients                                           | 207                                                                                                                                              |
| Aborted_connects                                          | 0                                                                                                                                                |
| Bytes_received                                            | 229088122449                                                                                                                                     |
| Bytes_sent                                                | 204658320676                                                                                                                                     |
| Connections                                               | 306                                                                                                                                              |
| Max_used_connections                                      | 109                                                                                                                                              |
| Queries                                                   | 30412909                                                                                                                                         |
| Questions                                                 | 30412909                                                                                                                                         |
| Threads_cached                                            | 84                                                                                                                                               |
| Threads_connected                                         | 80                                                                                                                                               |
| Threads_created                                           | 109                                                                                                                                              |
| Threads_running                                           | 1                                                                                                                                                |
| Threads_background                                        | 1                                                                                                                                                |
| Threads_shutdown                                          | 0                                                                                                                                                |
| Threads_idle                                              | 55                                                                                                                                               |
| Ready_queue                                               | 0                                                                                                                                                |
| Idle_queue                                                | 0                                                                                                                                                |
| Context_switches                                          | 7515021                                                                                                                                          |
| Context_switch_misses                                     | 34                                                                                                                                               |
| Workload_management_queued_queries                        | 0                                                                                                                                                |
| Workload_management_active_queries                        | 0                                                                                                                                                |
| Workload_management_active_threads                        | 0                                                                                                                                                |
| Workload_management_active_connections                    | 0                                                                                                                                                |
| Columnstore_ingest_management_queued_queries              | 0                                                                                                                                                |
| Columnstore_ingest_management_active_queries              | 0                                                                                                                                                |
| Columnstore_ingest_management_estimated_segments_to_flush | 0                                                                                                                                                |
| Columnstore_ingest_management_estimated_memory            | 0.000 MB                                                                                                                                         |
| Uptime                                                    | 1861900                                                                                                                                          |
| Prepared_stmt_count                                       | 0                                                                                                                                                |
| Auto_attach_remaining_seconds                             | 0                                                                                                                                                |
| Data_directory                                            | /var/lib/memsql/ae6ff459-d1a5-47e2-b1a3-8f60593ec6c2/data                                                                                        |
| Plancache_directory                                       | /var/lib/memsql/ae6ff459-d1a5-47e2-b1a3-8f60593ec6c2/plancache                                                                                   |
| Transaction_logs_directory                                | /var/lib/memsql/ae6ff459-d1a5-47e2-b1a3-8f60593ec6c2/data/logs                                                                                   |
| Segments_directory                                        | /var/lib/memsql/ae6ff459-d1a5-47e2-b1a3-8f60593ec6c2/data/columns                                                                                |
| Snapshots_directory                                       | /var/lib/memsql/ae6ff459-d1a5-47e2-b1a3-8f60593ec6c2/data/snapshots                                                                              |
| Threads_waiting_for_disk_space                            | 0                                                                                                                                                |
| Maximum_cluster_capacity                                  | 131072 MB                                                                                                                                        |
| Query_compilations                                        | 1856                                                                                                                                             |
| Query_compilation_failures                                | 0                                                                                                                                                |
| Inflight_async_compilations                               | 0                                                                                                                                                |
| GCed_versions_last_sweep                                  | 8                                                                                                                                                |
| Average_garbage_collection_duration                       | 1 ms                                                                                                                                             |
| Total_server_memory                                       | 36644.6 MB                                                                                                                                       |
| Total_io_pool_memory                                      | 0.1 MB                                                                                                                                           |
| Free_io_pool_memory                                       | 0.0 MB                                                                                                                                           |
| Alloc_thread_stacks                                       | 110.000 MB                                                                                                                                       |
| Malloc_active_memory                                      | 415.706 (+0.001) MB                                                                                                                              |
| Malloc_transaction_cached_memory                          | 323.750 MB                                                                                                                                       |
| Buffer_manager_memory                                     | 35755.1 MB                                                                                                                                       |
| Buffer_manager_cached_memory                              | 11006.5 (+1.8) MB                                                                                                                                |
| Buffer_manager_unrecycled_memory                          | 0.0 MB                                                                                                                                           |
| Alloc_skiplist_tower                                      | 8487.750 MB                                                                                                                                      |
| Alloc_variable                                            | 4157.500 MB                                                                                                                                      |
| Alloc_table_primary                                       | 12028.875 MB                                                                                                                                     |
| Alloc_deleted_version                                     | 49.250 MB                                                                                                                                        |
| Alloc_internal_key_node                                   | 5.000 MB                                                                                                                                         |
| Alloc_hash_buckets                                        | 19.274 MB                                                                                                                                        |
| Alloc_table_metadata_cache                                | 0.500 MB                                                                                                                                         |
| Alloc_unit_images                                         | 91.854 MB                                                                                                                                        |
| Alloc_unit_ifn_thunks                                     | 3.090 MB                                                                                                                                         |
| Alloc_object_code_images                                  | 30.900 MB                                                                                                                                        |
| Alloc_compiled_unit_sections                              | 18.333 MB                                                                                                                                        |
| Alloc_databases_list_entry                                | 0.125 MB                                                                                                                                         |
| Alloc_plan_cache                                          | 0.250 MB                                                                                                                                         |
| Alloc_warnings                                            | 6.875 (-0.250) MB                                                                                                                                |
| Alloc_replication_large                                   | 8.000 MB                                                                                                                                         |
| Alloc_durability_large                                    | 192.251 MB                                                                                                                                       |
| Alloc_skynet_replication                                  | 0.375 MB                                                                                                                                         |
| Alloc_sharding_partitions                                 | 0.125 MB                                                                                                                                         |
| Alloc_log_replay                                          | 0.341 MB                                                                                                                                         |
| Alloc_mmap_file                                           | 3072.000 MB                                                                                                                                      |
| Alloc_client_connection                                   | 1.750 (-1.500) MB                                                                                                                                |
| Alloc_protocol_packet                                     | 9.875 MB                                                                                                                                         |
| Alloc_global_func                                         | 0.250 MB                                                                                                                                         |
| Alloc_profile_stats                                       | 0.125 MB                                                                                                                                         |
| Alloc_table_memory                                        | 24747.649 MB                                                                                                                                     |
| Alloc_variable_allocated                                  | 3571.2 MB                                                                                                                                        |
| Successful_read_queries                                   | 15563158                                                                                                                                         |
| Successful_write_queries                                  | 4632328                                                                                                                                          |
| Failed_read_queries                                       | 12444                                                                                                                                            |
| Failed_write_queries                                      | 18489                                                                                                                                            |
| Rows_returned_by_reads                                    | 377416490                                                                                                                                        |
| Rows_affected_by_writes                                   | 197002482                                                                                                                                        |
| Execution_time_of_reads                                   | 356083733 ms                                                                                                                                     |
| Execution_time_of_write                                   | 29745514 ms                                                                                                                                      |
| Transaction_buffer_wait_time                              | 0 ms                                                                                                                                             |
| Transaction_log_flush_wait_time                           | 0 ms                                                                                                                                             |
| Row_lock_wait_time                                        | 150287 ms                                                                                                                                        |
| Ssl_accept_renegotiates                                   | 0                                                                                                                                                |
| Ssl_accepts                                               | 0                                                                                                                                                |
| Ssl_callback_cache_hits                                   | 0                                                                                                                                                |
| Ssl_client_connects                                       | 0                                                                                                                                                |
| Ssl_connect_renegotiates                                  | 0                                                                                                                                                |
| Ssl_ctx_verify_depth                                      | 18446744073709551615                                                                                                                             |
| Ssl_ctx_verify_mode                                       | 0                                                                                                                                                |
| Ssl_default_timeout                                       | 0                                                                                                                                                |
| Ssl_finished_accepts                                      | 0                                                                                                                                                |
| Ssl_finished_connects                                     | 0                                                                                                                                                |
| Ssl_session_cache_hits                                    | 0                                                                                                                                                |
| Ssl_session_cache_misses                                  | 0                                                                                                                                                |
| Ssl_session_cache_overflows                               | 0                                                                                                                                                |
| Ssl_session_cache_size                                    | 20480                                                                                                                                            |
| Ssl_session_cache_timeouts                                | 0                                                                                                                                                |
| Ssl_sessions_reused                                       | 0                                                                                                                                                |
| Ssl_used_session_cache_entries                            | 0                                                                                                                                                |
| Ssl_verify_depth                                          | 0                                                                                                                                                |
| Ssl_verify_mode                                           | 0                                                                                                                                                |
| Ssl_cipher                                                |                                                                                                                                                  |
| Ssl_cipher_list                                           |                                                                                                                                                  |
| Ssl_version                                               |                                                                                                                                                  |
| Ssl_session_cache_mode                                    | SERVER                                                                                                                                           |
+-----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+

Based on the above output, Buffer_manager_cached_memory could be the one. Is it possible to clear this cache?

Perhaps this is related to another topic: MemSQL holding ( >10 GB )RAM in columnstore

I just had to execute trigger full gc flush to release some memory and bring our cluster back to a healthy state.

This indeed released Malloc_transaction_cached_memory usage and reduced the overall memory usage to match data storage.

I’m aware this cache will rebuild automatically, so this isn’t a durable solution. I guess we then have to increase hardware memory?

Updated: I just found an inefficient query was the root cause. The query consumed a lot of memory, causing the Buffer_manager_memory to increase (~10 GB) until reaching maximum_memory and failing the query. The memory is then reallocated to Buffer_manager_cached_memory.

Yeah, flushing Buffer_manager_cached_memory is almost always counter-productive. The cache should manage itself. It only fills up if memory is actively being allocated and freed by either queries for by inserts and deletes into rowstore tables. If MemSQL start to run out of memory for other things the cache will shrink on its own.

Your memory state looks reasonably healthy to me (you do have a lot of cached memory).

You can check the plancache to see if you have queries using a lot of memory. It has an average memory use column.