scott
August 24, 2019, 6:21am
1
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
scott
August 24, 2019, 6:55am
2
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
adam
August 26, 2019, 3:36am
3
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
scott
August 26, 2019, 2:28pm
4
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?
hanson
August 27, 2019, 11:24pm
5
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.
adam
August 29, 2019, 5:35pm
6
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.
