MemSQL holding ( >10 GB )RAM in columnstore

Hey,
I’m running memSQL in a 48 core server with a RAM of 72 GB,
After inserting 100CR rows, memSQL holding just 2GB (Checked in the TOP command ),

But, after executing 10s of queries, RAM holds by memSQL process crossing 10GB, and not getting normal.

What is the issue? and How to check what causing the memory holding?

MemSQL will cache up to 25% of maximum_memory in its memory allocators to avoid need to go to linux for memory. It will show up as “Buffer_manager_cached_memory” memory on any leaves in your cluster (Connect to a leaf node and run SHOW STATUS EXTENDED to check). This is likely what your seeing. This memory can be re-used for storing rows in rowstore tables as well as for query executions (hash tables, sorts, will use up this memory before going to the OS).

Hey adam, Thanks for the reply
Below the result of SHOW STATUS EXTENDED

SHOW STATUS EXTENDED
-> ;
±----------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±----------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------+
| Aborted_clients | 22 |
| Aborted_connects | 0 |
| Bytes_received | 62283504 |
| Bytes_sent | 61637580 |
| Connections | 29 |
| Max_used_connections | 20 |
| Queries | 41 |
| Questions | 41 |
| Threads_cached | 16 |
| Threads_connected | 4 |
| Threads_created | 20 |
| Threads_running | 2 |
| Threads_background | 2 |
| Threads_shutdown | 0 |
| Threads_idle | 0 |
| Ready_queue | 0 |
| Idle_queue | 0 |
| Context_switches | 29 |
| Context_switch_misses | 0 |
| 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 | 2705 |
| Prepared_stmt_count | 0 |
| Auto_attach_remaining_seconds | 0 |
| Data_directory | /var/lib/memsql/af5e0466-f375-43ba-82f6-4ef0ef83a746/data |
| Plancache_directory | /var/lib/memsql/af5e0466-f375-43ba-82f6-4ef0ef83a746/plancache |
| Transaction_logs_directory | /var/lib/memsql/af5e0466-f375-43ba-82f6-4ef0ef83a746/data/logs |
| Segments_directory | /var/lib/memsql/af5e0466-f375-43ba-82f6-4ef0ef83a746/data/columns |
| Snapshots_directory | /var/lib/memsql/af5e0466-f375-43ba-82f6-4ef0ef83a746/data/snapshots |
| Threads_waiting_for_disk_space | 0 |
| Aggregator_id | 1 |
| License | BDM3YzZhMjllY2Q1NjQ3N2I5YmY5NTVkZWI3MjFkMjFjhiKDXQAAAAAAAAAAAAAAAAkwNAIYF6AmuGbjWp55WsMl/owMGgMbJkwYceXGAhgUmaO/YWTKjUsghA5Y13TM7e8Cto7POCwAAA== |
| License_version | 4 |
| License_capacity | 18446744073709551615 units |
| Used_instance_license_units | 0 |
| License_expiration | 1568875142 |
| Seconds_until_expiration | 2311724 |
| License_key | 37c6a29ecd56477b9bf955deb721d21c |
| License_type | enterprise |
| Maximum_cluster_capacity | 18446744073709551615 units |
| Used_cluster_capacity | 8 units |
| Query_compilations | 1 |
| Query_compilation_failures | 0 |
| Inflight_async_compilations | 0 |
| GCed_versions_last_sweep | 0 |
| Average_garbage_collection_duration | 0 ms |
| Total_server_memory | 568.0 (+568.0) MB |
| Total_io_pool_memory | 0.2 (+0.2) MB |
| Free_io_pool_memory | 0.1 (+0.1) MB |
| Alloc_thread_stacks | 22.000 (+22.000) MB |
| Malloc_active_memory | 370.421 (+370.421) MB |
| Malloc_transaction_cached_memory | 323.750 (+323.750) MB |
| Buffer_manager_memory | 28.6 (+28.6) MB |
| Buffer_manager_cached_memory | 9.4 (+9.4) MB |
| Buffer_manager_unrecycled_memory | 3.0 (+3.0) MB |
| Alloc_skiplist_tower | 4.625 (+4.625) MB |
| Alloc_variable | 3.125 (+3.125) MB |
| Alloc_table_primary | 3.000 (+3.000) MB |
| Alloc_deleted_version | 2.250 (+2.250) MB |
| Alloc_internal_key_node | 2.000 (+2.000) MB |
| Alloc_hash_buckets | 13.208 (+13.208) MB |
| Alloc_table_metadata_cache | 0.125 (+0.125) MB |
| Alloc_unit_images | 3.065 (+3.065) MB |
| Alloc_unit_ifn_thunks | 0.200 (+0.200) MB |
| Alloc_object_code_images | 1.244 (+1.244) MB |
| Alloc_compiled_unit_sections | 0.750 (+0.750) MB |
| Alloc_databases_list_entry | 0.125 (+0.125) MB |
| Alloc_plan_cache | 0.125 (+0.125) MB |
| Alloc_warnings | 0.125 (+0.125) MB |
| Alloc_durability_large | 128.188 (+128.188) MB |
| Alloc_skynet_replication | 0.250 (+0.250) MB |
| Alloc_sharding_partitions | 0.125 (+0.125) MB |
| Alloc_protocol_packet | 0.375 (+0.375) MB |
| Alloc_table_memory | 28.208 (+28.208) MB |
| Alloc_variable_bucket_16 | allocs:2457 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_24 | allocs:1869 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_32 | allocs:307 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_40 | allocs:125 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_48 | allocs:95 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_56 | allocs:30 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_64 | allocs:11 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_72 | allocs:32 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_104 | allocs:3 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_128 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_160 | allocs:1 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_312 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_384 | allocs:3 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_480 | allocs:6 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_600 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_752 | allocs:1 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_936 | allocs:5 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_1168 | allocs:1 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_1480 | allocs:3 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_1832 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_2832 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_3528 | allocs:1 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_4504 | allocs:0 alloc_MB:0.0 buffer_MB:0.2 cached_buffer_MB:0.2 |
| Alloc_variable_bucket_5680 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_cached_buffers | 0.2 (+0.2) MB |
| Alloc_variable_allocated | 0.1 MB |
| Successful_read_queries | 31 |
| Successful_write_queries | 0 |
| Failed_read_queries | 0 |
| Failed_write_queries | 0 |
| Rows_returned_by_reads | 5104417 |
| Rows_affected_by_writes | 0 |
| Execution_time_of_reads | 286390 ms |
| Execution_time_of_write | 0 ms |
| Transaction_buffer_wait_time | 0 ms |
| Transaction_log_flush_wait_time | 0 ms |
| Row_lock_wait_time | 0 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 |
±----------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------+
140 rows in set (0.00 sec)

from the result it show the Buffer_manager_cached_memory just took 9MB.

The previous one in the MASTER node,

sharing the LEAF node’s result here,

±----------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±----------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------+
| Aborted_clients | 9 |
| Aborted_connects | 0 |
| Bytes_received | 182181580 |
| Bytes_sent | 260171088 |
| Connections | 811 |
| Max_used_connections | 1469 |
| Queries | 184080 |
| Questions | 184080 |
| Threads_cached | 1434 |
| Threads_connected | 803 |
| Threads_created | 1469 |
| Threads_running | 1 |
| Threads_background | 1 |
| Threads_shutdown | 0 |
| Threads_idle | 768 |
| Ready_queue | 0 |
| Idle_queue | 0 |
| Context_switches | 4835 |
| Context_switch_misses | 54 |
| 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 | 59093 |
| Prepared_stmt_count | 0 |
| Auto_attach_remaining_seconds | 0 |
| Data_directory | /var/lib/memsql/0e062b82-8cad-4699-a32c-10a79740304a/data |
| Plancache_directory | /var/lib/memsql/0e062b82-8cad-4699-a32c-10a79740304a/plancache |
| Transaction_logs_directory | /var/lib/memsql/0e062b82-8cad-4699-a32c-10a79740304a/data/logs |
| Segments_directory | /var/lib/memsql/0e062b82-8cad-4699-a32c-10a79740304a/data/columns |
| Snapshots_directory | /var/lib/memsql/0e062b82-8cad-4699-a32c-10a79740304a/data/snapshots |
| Threads_waiting_for_disk_space | 0 |
| License | BDM3YzZhMjllY2Q1NjQ3N2I5YmY5NTVkZWI3MjFkMjFjhiKDXQAAAAAAAAAAAAAAAAkwNAIYF6AmuGbjWp55WsMl/owMGgMbJkwYceXGAhgUmaO/YWTKjUsghA5Y13TM7e8Cto7POCwAAA== |
| License_version | 4 |
| License_capacity | 18446744073709551615 units |
| Used_instance_license_units | 8 |
| License_expiration | 1568875142 |
| Seconds_until_expiration | 2251055 |
| License_key | 37c6a29ecd56477b9bf955deb721d21c |
| License_type | enterprise |
| Maximum_cluster_capacity | 18446744073709551615 units |
| Query_compilations | 1 |
| Query_compilation_failures | 0 |
| Inflight_async_compilations | 0 |
| GCed_versions_last_sweep | 0 |
| Average_garbage_collection_duration | 1 ms |
| Total_server_memory | 4917.6 (+4917.6) MB |
| Total_io_pool_memory | 0.1 (+0.1) MB |
| Free_io_pool_memory | 0.0 MB |
| Alloc_thread_stacks | 1470.000 (+1470.000) MB |
| Malloc_active_memory | 1084.189 (+1084.189) MB |
| Malloc_transaction_cached_memory | 973.464 (+973.464) MB |
| Buffer_manager_memory | 1728.8 (+1728.8) MB |
| Buffer_manager_cached_memory | 1441.0 (+1441.0) MB |
| Buffer_manager_unrecycled_memory | 0.0 MB |
| Alloc_skiplist_tower | 24.000 (+24.000) MB |
| Alloc_variable | 30.250 (+30.250) MB |
| Alloc_table_primary | 17.250 (+17.250) MB |
| Alloc_deleted_version | 11.250 (+11.250) MB |
| Alloc_internal_key_node | 7.125 (+7.125) MB |
| Alloc_hash_buckets | 37.473 (+37.473) MB |
| Alloc_table_metadata_cache | 0.750 (+0.750) MB |
| Alloc_unit_images | 7.290 (+7.290) MB |
| Alloc_unit_ifn_thunks | 0.411 (+0.411) MB |
| Alloc_object_code_images | 3.017 (+3.017) MB |
| Alloc_compiled_unit_sections | 1.762 (+1.762) MB |
| Alloc_databases_list_entry | 0.250 (+0.250) MB |
| Alloc_plan_cache | 0.125 (+0.125) MB |
| Alloc_warnings | 96.000 (+96.000) MB |
| Alloc_replication_large | 8.000 (+8.000) MB |
| Alloc_durability_large | 576.626 (+576.626) MB |
| Alloc_skynet_replication | 0.375 (+0.375) MB |
| Alloc_sharding_partitions | 0.125 (+0.125) MB |
| Alloc_log_replay | 0.008 (+0.008) MB |
| Alloc_mmap_file | 3072.000 (+3072.000) MB |
| Alloc_protocol_packet | 100.250 (+100.250) MB |
| Alloc_table_memory | 127.348 (+127.348) MB |
| Alloc_variable_bucket_16 | allocs:10930 alloc_MB:0.2 buffer_MB:5.2 cached_buffer_MB:1.9 |
| Alloc_variable_bucket_24 | allocs:2164 alloc_MB:0.0 buffer_MB:1.2 cached_buffer_MB:0.8 |
| Alloc_variable_bucket_32 | allocs:2418 alloc_MB:0.1 buffer_MB:4.2 cached_buffer_MB:1.9 |
| Alloc_variable_bucket_40 | allocs:225 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_48 | allocs:2507 alloc_MB:0.1 buffer_MB:1.2 cached_buffer_MB:0.4 |
| Alloc_variable_bucket_56 | allocs:345 alloc_MB:0.0 buffer_MB:0.4 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_64 | allocs:238 alloc_MB:0.0 buffer_MB:0.2 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_72 | allocs:56 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_88 | allocs:19 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_104 | allocs:7 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_128 | allocs:23 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_160 | allocs:5 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_312 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_384 | allocs:3 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_480 | allocs:5 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_600 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_752 | allocs:1 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_936 | allocs:5 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_1168 | allocs:1 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_1480 | allocs:3 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_1832 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_2832 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_3528 | allocs:1 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_4504 | allocs:0 alloc_MB:0.0 buffer_MB:1.6 cached_buffer_MB:1.6 |
| Alloc_variable_bucket_5680 | allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 |
| Alloc_variable_bucket_7264 | allocs:0 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.1 |
| Alloc_variable_bucket_9344 | allocs:0 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.1 |
| Alloc_variable_bucket_11896 | allocs:0 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.1 |
| Alloc_variable_bucket_14544 | allocs:0 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.1 |
| Alloc_variable_bucket_18696 | allocs:0 alloc_MB:0.0 buffer_MB:1.2 cached_buffer_MB:1.2 |
| Alloc_variable_bucket_21816 | allocs:0 alloc_MB:0.0 buffer_MB:0.2 cached_buffer_MB:0.2 |
| Alloc_variable_bucket_26184 | allocs:0 alloc_MB:0.0 buffer_MB:2.1 cached_buffer_MB:2.1 |
| Alloc_variable_bucket_32728 | allocs:0 alloc_MB:0.0 buffer_MB:2.4 cached_buffer_MB:2.4 |
| Alloc_variable_bucket_43648 | allocs:0 alloc_MB:0.0 buffer_MB:3.1 cached_buffer_MB:3.1 |
| Alloc_variable_bucket_65472 | allocs:0 alloc_MB:0.0 buffer_MB:2.1 cached_buffer_MB:2.1 |
| Alloc_variable_bucket_130960 | allocs:0 alloc_MB:0.0 buffer_MB:2.0 cached_buffer_MB:2.0 |
| Alloc_variable_cached_buffers | 20.2 (+20.2) MB |
| Alloc_variable_allocated | 0.5 MB |
| Successful_read_queries | 120710 |
| Successful_write_queries | 2301 |
| Failed_read_queries | 0 |
| Failed_write_queries | 0 |
| Rows_returned_by_reads | 756608 |
| Rows_affected_by_writes | 0 |
| Execution_time_of_reads | 1057347 ms |
| Execution_time_of_write | 14995578 ms |
| Transaction_buffer_wait_time | 0 ms |
| Transaction_log_flush_wait_time | 0 ms |
| Row_lock_wait_time | 0 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 |
|
153 rows in set (0.00 sec)

If you can explain why its taking memory for different things,
I read the documentation and didn’t understand it well.

There is some information to help you interpret these results here:

Your leaf is using 5 GB of Memory:
| Total_server_memory | 4917.6 (+4917.6) MB |

Most of it is here:

  • | Buffer_manager_cached_memory | 1441.0 (+1441.0) MB |
    This is memory I referred to above

  • | Alloc_thread_stacks | 1470.000 (+1470.000) MB |

  • | Malloc_transaction_cached_memory | 973.464 (+973.464) MB |
    This is memory for the thread pool on the leaf. If will grow and shrink overtime depending on how many concurrent queries the leaf typically runs at once.

Where are you getting the 10 GB number from?

When we run the TOP command on the server, there are two instance of memSQL of which one is utilizing 10 GB of RAM and another 1 GB.