MemSQL holding ( >10 GB )RAM in columnstore

jilani.m · August 23, 2019, 12:31pm

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?

adam · August 23, 2019, 5:50pm

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

jilani.m · August 24, 2019, 5:12am

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

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

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

jilani.m · August 24, 2019, 5:37am

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.

adam · August 26, 2019, 2:55am

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?

jilani.m · August 26, 2019, 5:01am

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.