What is using all my memory?

Hi, I’ve having some stability issues with our MemSQL cluster. I think it is running out of RAM.

In MemSQL studio, when I look at the nodes, the leaf is using 44GB (out of 69GB). We only have one lead. However, when I look at the database metrics, they are only using a combined 5GB of memory.

Some notes in case it’s helpful:

  • we are using pipelines to load some very wide data into Columnstore tables
  • we are running some queries over the columnstore tables to turn them into in-memory tables that look like this:
CREATE TEMPORARY TABLE clean.clean_table AS

SELECT * FROM (
  SELECT 
    *, 
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY time DESC) AS _rank
  FROM pipelines.columnar_table
) WHERE _rank = 1;

CREATE INDEX seq_index clean_table ( id );

In another thread, I read that SHOW STATUS EXTENDED could be helpful. Here is the output:

Aborted_clients	116
Aborted_connects	1
Bytes_received	248524107
Bytes_sent	83185261
Connections	374
Max_used_connections	34
Queries	11849
Questions	11849
Threads_cached	7
Threads_connected	27
Threads_created	34
Threads_running	6
Threads_background	5
Threads_shutdown	0
Threads_idle	0
Ready_queue	0
Idle_queue	0
Context_switches	374
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 (-5712352814678.774) MB
Uptime	2598
Prepared_stmt_count	0
Auto_attach_remaining_seconds	0
Data_directory	/var/lib/memsql/adefe188-e1b8-4113-855a-ab03a860cc77/data
Plancache_directory	/var/lib/memsql/adefe188-e1b8-4113-855a-ab03a860cc77/plancache
Transaction_logs_directory	/var/lib/memsql/adefe188-e1b8-4113-855a-ab03a860cc77/data/logs
Segments_directory	/var/lib/memsql/adefe188-e1b8-4113-855a-ab03a860cc77/data/columns
Snapshots_directory	/var/lib/memsql/adefe188-e1b8-4113-855a-ab03a860cc77/data/snapshots
Threads_waiting_for_disk_space	0
Aggregator_id	1
License	<Redacted>
License_version	4
License_capacity	131072 MB
License_expiration	0
Seconds_until_expiration	-1
License_key	<Redacted>
License_type	free
Maximum_cluster_capacity	131072 MB
Used_cluster_capacity	77225 MB
Query_compilations	412
Query_compilation_failures	0
Inflight_async_compilations	0
GCed_versions_last_sweep	0
Average_garbage_collection_duration	0 ms
Total_server_memory	953.0 (+4.1) MB
Total_io_pool_memory	0.2 MB
Free_io_pool_memory	0.1 (-0.1) MB
Alloc_thread_stacks	39.000 MB
Malloc_active_memory	400.660 (+3.397) MB
Malloc_transaction_cached_memory	323.750 (+323.750) MB
Buffer_manager_memory	123.0 (+123.0) MB
Buffer_manager_cached_memory	87.9 (+87.9) MB
Buffer_manager_unrecycled_memory	2.0 (+2.0) MB
Alloc_skiplist_tower	8.250 (+8.250) MB
Alloc_variable	4.375 (+4.375) MB
Alloc_table_primary	6.750 (+6.750) MB
Alloc_deleted_version	4.500 (+4.500) MB
Alloc_internal_key_node	4.375 (+4.375) MB
Alloc_hash_buckets	22.307 (+22.307) MB
Alloc_table_metadata_cache	0.125 (+0.125) MB
Alloc_unit_images	28.733 (+28.733) MB
Alloc_unit_ifn_thunks	1.465 (+1.465) MB
Alloc_object_code_images	11.165 (+11.165) MB
Alloc_compiled_unit_sections	5.997 (+5.997) MB
Alloc_databases_list_entry	0.125 (+0.125) MB
Alloc_plan_cache	0.125 (+0.125) MB
Alloc_warnings	0.500 (+0.500) MB
Alloc_durability_large	320.376 (+320.376) MB
Alloc_skynet_replication	0.250 (+0.250) MB
Alloc_sharding_partitions	0.125 (+0.125) MB
Alloc_protocol_packet	3.250 (+3.250) MB
Alloc_system_tasks	0.250 (+0.250) MB
Alloc_db_tasks	0.125 (+0.125) MB
Alloc_table_memory	50.557 (+50.557) MB
Alloc_variable_bucket_16	allocs:5333  alloc_MB:0.1  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_24	allocs:5738  alloc_MB:0.1  buffer_MB:0.2  cached_buffer_MB:0.0
Alloc_variable_bucket_32	allocs:1220  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_40	allocs:1178  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_48	allocs:510  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_56	allocs:318  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_64	allocs:286  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_72	allocs:379  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_80	allocs:291  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_88	allocs:273  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_104	allocs:586  alloc_MB:0.1  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_128	allocs:736  alloc_MB:0.1  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_160	allocs:581  alloc_MB:0.1  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_200	allocs:81  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_248	allocs:0  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.1
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:9  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_2288	allocs:0  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.1
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.1  cached_buffer_MB:0.1
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:0.1  cached_buffer_MB:0.1
Alloc_variable_cached_buffers	1.0 (+0.4) MB
Alloc_variable_allocated	0.7 MB
Successful_read_queries	10469
Successful_write_queries	124
Failed_read_queries	0
Failed_write_queries	0
Rows_returned_by_reads	34758
Rows_affected_by_writes	63232881
Execution_time_of_reads	221888 ms
Execution_time_of_write	984977 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

Screenshots from MemSQL Studio


On the leaf:

Aborted_clients	6166
Aborted_connects	0
Bytes_received	63537454732
Bytes_sent	64377888765
Connections	6518
Max_used_connections	337
Queries	137424
Questions	137424
Threads_cached	260
Threads_connected	219
Threads_created	337
Threads_running	1
Threads_background	1
Threads_shutdown	0
Threads_idle	142
Ready_queue	0
Idle_queue	0
Context_switches	31316
Context_switch_misses	241
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	5090
Prepared_stmt_count	0
Auto_attach_remaining_seconds	0
Data_directory	/var/lib/memsql/e2072da0-56e1-4f4b-8581-c5af8f055dfe/data
Plancache_directory	/var/lib/memsql/e2072da0-56e1-4f4b-8581-c5af8f055dfe/plancache
Transaction_logs_directory	/var/lib/memsql/e2072da0-56e1-4f4b-8581-c5af8f055dfe/data/logs
Segments_directory	/var/lib/memsql/e2072da0-56e1-4f4b-8581-c5af8f055dfe/data/columns
Snapshots_directory	/var/lib/memsql/e2072da0-56e1-4f4b-8581-c5af8f055dfe/data/snapshots
Threads_waiting_for_disk_space	0
License_version	4
License_capacity	131072 MB
License_expiration	0
Seconds_until_expiration	-1
License_type	free
Maximum_cluster_capacity	131072 MB
Query_compilations	1073
Query_compilation_failures	0
Inflight_async_compilations	0
GCed_versions_last_sweep	0
Average_garbage_collection_duration	37 ms
Total_server_memory	41290.4 MB
Total_io_pool_memory	0.1 MB
Free_io_pool_memory	0.0 MB
Alloc_thread_stacks	338.000 MB
Malloc_active_memory	1519.218 (-0.002) MB
Malloc_transaction_cached_memory	323.750 MB
Buffer_manager_memory	29479.0 MB
Buffer_manager_cached_memory	15765.8 MB
Buffer_manager_unrecycled_memory	7.5 MB
Alloc_skiplist_tower	1220.625 MB
Alloc_variable	7141.875 MB
Alloc_table_primary	5052.625 MB
Alloc_deleted_version	162.375 MB
Alloc_internal_key_node	76.250 MB
Alloc_hash_buckets	459.088 MB
Alloc_table_metadata_cache	2.125 MB
Alloc_unit_images	120.610 MB
Alloc_unit_ifn_thunks	3.071 MB
Alloc_object_code_images	30.497 MB
Alloc_compiled_unit_sections	19.719 MB
Alloc_databases_list_entry	4.000 MB
Alloc_plan_cache	0.375 MB
Alloc_warnings	17.750 MB
Alloc_replication_large	32.000 MB
Alloc_durability_large	9289.126 MB
Alloc_skynet_replication	0.375 MB
Alloc_sharding_partitions	0.125 MB
Alloc_log_replay	2.104 MB
Alloc_mmap_file	3072.000 MB
Alloc_protocol_packet	27.250 MB
Alloc_table_memory	14112.838 MB
Alloc_variable_bucket_16	allocs:59046887  alloc_MB:901.0  buffer_MB:979.5  cached_buffer_MB:1.1
Alloc_variable_bucket_24	allocs:68319298  alloc_MB:1563.7  buffer_MB:1858.6  cached_buffer_MB:0.2
Alloc_variable_bucket_32	allocs:41581226  alloc_MB:1269.0  buffer_MB:1542.2  cached_buffer_MB:1.9
Alloc_variable_bucket_40	allocs:7900808  alloc_MB:301.4  buffer_MB:347.6  cached_buffer_MB:1.2
Alloc_variable_bucket_48	allocs:2503494  alloc_MB:114.6  buffer_MB:177.8  cached_buffer_MB:2.0
Alloc_variable_bucket_56	allocs:1734785  alloc_MB:92.6  buffer_MB:114.8  cached_buffer_MB:0.0
Alloc_variable_bucket_64	allocs:970161  alloc_MB:59.2  buffer_MB:79.2  cached_buffer_MB:0.4
Alloc_variable_bucket_72	allocs:764852  alloc_MB:52.5  buffer_MB:69.6  cached_buffer_MB:0.4
Alloc_variable_bucket_80	allocs:586618  alloc_MB:44.8  buffer_MB:59.1  cached_buffer_MB:0.1
Alloc_variable_bucket_88	allocs:955217  alloc_MB:80.2  buffer_MB:118.0  cached_buffer_MB:1.9
Alloc_variable_bucket_104	allocs:3133111  alloc_MB:310.7  buffer_MB:477.8  cached_buffer_MB:1.9
Alloc_variable_bucket_128	allocs:933914  alloc_MB:114.0  buffer_MB:159.6  cached_buffer_MB:1.8
Alloc_variable_bucket_160	allocs:1065789  alloc_MB:162.6  buffer_MB:239.6  cached_buffer_MB:0.1
Alloc_variable_bucket_200	allocs:896801  alloc_MB:171.1  buffer_MB:298.2  cached_buffer_MB:2.1
Alloc_variable_bucket_248	allocs:529309  alloc_MB:125.2  buffer_MB:168.1  cached_buffer_MB:1.8
Alloc_variable_bucket_312	allocs:318023  alloc_MB:94.6  buffer_MB:146.1  cached_buffer_MB:1.9
Alloc_variable_bucket_384	allocs:138697  alloc_MB:50.8  buffer_MB:60.0  cached_buffer_MB:3.6
Alloc_variable_bucket_480	allocs:39856  alloc_MB:18.2  buffer_MB:20.5  cached_buffer_MB:1.9
Alloc_variable_bucket_600	allocs:58243  alloc_MB:33.3  buffer_MB:51.9  cached_buffer_MB:2.8
Alloc_variable_bucket_752	allocs:17658  alloc_MB:12.7  buffer_MB:25.5  cached_buffer_MB:1.6
Alloc_variable_bucket_936	allocs:14160  alloc_MB:12.6  buffer_MB:24.8  cached_buffer_MB:2.0
Alloc_variable_bucket_1168	allocs:11542  alloc_MB:12.9  buffer_MB:25.4  cached_buffer_MB:1.9
Alloc_variable_bucket_1480	allocs:8777  alloc_MB:12.4  buffer_MB:25.8  cached_buffer_MB:2.0
Alloc_variable_bucket_1832	allocs:5227  alloc_MB:9.1  buffer_MB:19.9  cached_buffer_MB:1.5
Alloc_variable_bucket_2288	allocs:4569  alloc_MB:10.0  buffer_MB:12.2  cached_buffer_MB:1.9
Alloc_variable_bucket_2832	allocs:1091  alloc_MB:2.9  buffer_MB:7.8  cached_buffer_MB:3.4
Alloc_variable_bucket_3528	allocs:585  alloc_MB:2.0  buffer_MB:4.6  cached_buffer_MB:2.6
Alloc_variable_bucket_4504	allocs:2102  alloc_MB:9.0  buffer_MB:14.9  cached_buffer_MB:1.6
Alloc_variable_bucket_5680	allocs:193  alloc_MB:1.0  buffer_MB:1.5  cached_buffer_MB:0.1
Alloc_variable_bucket_6224	allocs:1  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
Alloc_variable_bucket_7264	allocs:1  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.0
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:145  alloc_MB:2.0  buffer_MB:3.1  cached_buffer_MB:0.9
Alloc_variable_bucket_18696	allocs:0  alloc_MB:0.0  buffer_MB:1.4  cached_buffer_MB:1.4
Alloc_variable_bucket_21816	allocs:252  alloc_MB:5.2  buffer_MB:6.0  cached_buffer_MB:0.8
Alloc_variable_bucket_43648	allocs:0  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.1
Alloc_variable_bucket_130960	allocs:0  alloc_MB:0.0  buffer_MB:0.1  cached_buffer_MB:0.1
Alloc_variable_cached_buffers	49.1 MB
Alloc_variable_allocated	5651.5 MB
Successful_read_queries	53633
Successful_write_queries	8526
Failed_read_queries	318
Failed_write_queries	20
Rows_returned_by_reads	564048
Rows_affected_by_writes	160908633
Execution_time_of_reads	1027651 ms
Execution_time_of_write	43997390 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

Hi Scott,

I think the best information we have for interpreting this output is here:

This is how much memory all tables are using on your leaf:
Alloc_table_memory 14112.838 MB

There is a lot of cached memory (which is normal if you regularly run queries that need a lot of memory):
Buffer_manager_cached_memory 15765.8 MB

You’re also using up a good amount of memory for transaction_buffers:
Alloc_durability_large 9289.126 MB
This may mean you have a lot of partitions on your leaf or a lot of databases on your cluster. Either way, if your workload is mostly columnstore you can lower the transaction_buffer setting to get some of this memory back without much risk to write performance. Its a static memory buffer allocated with each partition used to commit to the transaction logs. Dropping it to 4mb or 8mb will likely have very little impact on your write performance but will save you 7 or 8 GB of memory per leaf.

Thank you so much.

This is cluster strictly for analytics workloads, which has infrequent but large writes using pipelines.

Are there good heuristics on how to tune a cluster like this? E.g. fewer partitions, less transaction_buffer?

Regarding limiting memory usage, I’d refer to Adam’s advice above. If you want the fastest possible query performance for running a single query at a time, you need to make sure to use one partition per core. Our parallelism model uses one thread per partition.

Some people reduce the number of partitions to, say, 1 partition to every 4 cores, if they want to allow more predictable response time under concurrent load. But this comes at the expense of the best possible response time under light load. This is kind of a crude form of resource governance. You can use resource governor instead to avoid making this kind of hard tradeoff.

Yeah, the number of partitions you want to use is a trade-off between throughput and latency (and also the types of queries you run). More partitions gives you higher parallelism for 1 query, but usually means you can run fewer of them at once.

For now I would just reduce the transaction_buffer size to free up some memory.