Having 2 different environments, it seems that MemSQL uses a lot more RAM than the actual data is. For example if my tables have 5GB of data, MemSQL will end up using 20+ GB. Same happened with 200mb of data. it uses 800+ mb. Is this normal? If yes, I’d be interested to know why. I understand it needs memory to process and keep alive the entire engine, but 3x of the data seems a bit too big to me. Thank you!
We’ll need a bit more detailed information to answer. Check what show status extended shows on a leaf node.
The typical reason memory use is higher then data size is that SingleStore aggressively caches memory (if there is free memory available - i.e., the difference between Total_server_memory and maximum_memory setting is high). i.e., A bunch of memory is tracked against Buffer_manager_cached_memory.
There are more details here:
Hi Adam, thank you for your answer! Below you can see my status extended. This is the environment with ~200mb of table data. In memsql studio it shows 800+
| “Aborted_clients” | “606” |
|---|---|
| “Aborted_connects” | “0” |
| “Active_dedicated_admin_connections” | “0” |
| “Aggregator_id” | “1” |
| “Alloc_analyze” | “0.125 (+0.125) MB” |
| “Alloc_client_connection” | “1.000 (+1.000) MB” |
| “Alloc_compiled_unit_sections” | “3.825 (+3.825) MB” |
| “Alloc_databases_list_entry” | “0.250 (+0.250) MB” |
| “Alloc_deleted_version” | “3.250 (+3.250) MB” |
| “Alloc_hash_buckets” | “13.424 (+13.424) MB” |
| “Alloc_internal_key_node” | “1.875 (+1.875) MB” |
| “Alloc_object_code_images” | “5.412 (+5.412) MB” |
| “Alloc_plan_cache” | “0.125 (+0.125) MB” |
| “Alloc_protocol_packet” | “2.250 (+2.250) MB” |
| “Alloc_replication” | “0.375 (+0.375) MB” |
| “Alloc_sharding_partitions” | “0.125 (+0.125) MB” |
| “Alloc_skiplist_tower” | “6.750 (+6.750) MB” |
| “Alloc_table_memory” | “36.299 (+36.299) MB” |
| “Alloc_table_metadata_cache” | “0.250 (+0.250) MB” |
| “Alloc_table_primary” | “4.500 (+4.500) MB” |
| “Alloc_thread_stacks” | “21.000 (+1.000) MB” |
| “Alloc_unit_ifn_thunks” | “0.661 (+0.661) MB” |
| “Alloc_unit_images” | “14.610 (+14.610) MB” |
| “Alloc_variable” | “6.500 (+6.500) MB” |
| “Alloc_variable_allocated” | “0.5 MB” |
| “Alloc_variable_bucket_104” | "allocs:37 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_1168” | "allocs:3 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_11896” | "allocs:0 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.1 " |
| “Alloc_variable_bucket_128” | "allocs:40 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_14544” | "allocs:0 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.1 " |
| “Alloc_variable_bucket_1480” | "allocs:3 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_16” | "allocs:5736 alloc_MB:0.1 buffer_MB:0.2 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_160” | "allocs:50 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_18696” | "allocs:0 alloc_MB:0.0 buffer_MB:1.9 cached_buffer_MB:1.9 " |
| “Alloc_variable_bucket_200” | "allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_21816” | "allocs:0 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.1 " |
| “Alloc_variable_bucket_2288” | "allocs:0 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.1 " |
| “Alloc_variable_bucket_24” | "allocs:3702 alloc_MB:0.1 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_2832” | "allocs:4 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_312” | "allocs:5 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_32” | "allocs:842 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_3528” | "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_40” | "allocs:356 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_48” | "allocs:345 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_480” | "allocs:7 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_56” | "allocs:293 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_5680” | "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_64” | "allocs:439 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_72” | "allocs:331 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_752” | "allocs:2 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_80” | "allocs:258 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_88” | "allocs:63 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_bucket_9344” | "allocs:8 alloc_MB:0.1 buffer_MB:0.2 cached_buffer_MB:0.1 " |
| “Alloc_variable_bucket_936” | "allocs:7 alloc_MB:0.0 buffer_MB:0.1 cached_buffer_MB:0.0 " |
| “Alloc_variable_cached_buffers” | “3.0 MB” |
| “Auto_attach_remaining_seconds” | “0” |
| “Average_garbage_collection_duration” | “1 ms” |
| “Buffer_manager_cached_memory” | “4.1 (+4.1) MB” |
| “Buffer_manager_memory” | “34.5 (+34.5) MB” |
| “Buffer_manager_unrecycled_memory” | “3.0 (+3.0) MB” |
| “Bytes_received” | “187290334” |
| “Bytes_sent” | “45253591” |
| “Columnstore_ingest_management_active_queries” | “0” |
| “Columnstore_ingest_management_estimated_memory” | “0.000 MB” |
| “Columnstore_ingest_management_estimated_segments_to_flush” | “0” |
| “Columnstore_ingest_management_max_concurrency” | “0” |
| “Columnstore_ingest_management_queued_queries” | “0” |
| “Connections” | “755” |
| “Context_switches” | “881” |
| “Context_switch_misses” | “0” |
| “Data_directory” | “/var/lib/memsql/be8c543f-2174-48b1-9050-e33d19ad398b/data” |
| “Disk_space_reserved_for_secondary_index” | “0” |
| “Execution_time_of_reads” | “37811 ms” |
| “Execution_time_of_write” | “2005 ms” |
| “Failed_read_queries” | “0” |
| “Failed_write_queries” | “0” |
| “Free_io_pool_memory” | “0.1 MB” |
| “GCed_versions_last_sweep” | “0” |
| “Idle_queue” | “0” |
| “Inflight_async_compilations” | “0” |
| “Ingest_errors_disk_space_use” | “0 Bytes” |
| “License” | “BDdlNTI0ODMxMjFhMzQxOWViZDVjMzk5MzE3M2UzZWY4AAAAAAAAAAAAAAIAAAAAAAQwNgIZAKXOLABWhOh0G+kWcw33DF280rOsnTswrgIZALAhriWDj0IaknobDy86/hfjve7qmJ1qzQ==” |
| “License_capacity” | “131072 MB” |
| “License_expiration” | “0” |
| “License_key” | “7e52483121a3419ebd5c3993173e3ef8” |
| “License_type” | “free” |
| “License_version” | “4” |
| “Linux_resident_memory” | “332.125 (+332.125) MB” |
| “Linux_resident_shared_memory” | “67.700 (+67.700) MB” |
| “Malloc_active_memory” | “322.474 (+0.026) MB” |
| “Malloc_transaction_cached_memory” | “267.883 (+267.883) MB” |
| “Maximum_cluster_capacity” | “131072 MB” |
| “Max_used_connections” | “20” |
| “Plancache_directory” | “/var/lib/memsql/be8c543f-2174-48b1-9050-e33d19ad398b/plancache” |
| “Prepared_stmt_count” | “0” |
| “Queries” | “4455” |
| “Query_compilations” | “62” |
| “Query_compilation_failures” | “0” |
| “Questions” | “4455” |
| “Ready_queue” | “0” |
| “Rows_affected_by_writes” | “4045” |
| “Rows_returned_by_reads” | “238376” |
| “Row_lock_wait_time” | “28 ms” |
| “Seconds_until_expiration” | “-1” |
| “Segments_directory” | “/var/lib/memsql/be8c543f-2174-48b1-9050-e33d19ad398b/data/blobs” |
| “Snapshots_directory” | “/var/lib/memsql/be8c543f-2174-48b1-9050-e33d19ad398b/data/snapshots” |
| “Ssl_accepts” | “0” |
| “Ssl_accept_renegotiates” | “0” |
| “Ssl_callback_cache_hits” | “0” |
| “Ssl_cipher” | “” |
| “Ssl_cipher_list” | “” |
| “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_sessions_reused” | “0” |
| “Ssl_session_cache_hits” | “0” |
| “Ssl_session_cache_misses” | “0” |
| “Ssl_session_cache_mode” | “SERVER” |
| “Ssl_session_cache_overflows” | “0” |
| “Ssl_session_cache_size” | “20480” |
| “Ssl_session_cache_timeouts” | “0” |
| “Ssl_used_session_cache_entries” | “0” |
| “Ssl_verify_depth” | “0” |
| “Ssl_verify_mode” | “0” |
| “Ssl_version” | “” |
| “Successful_read_queries” | “2752” |
| “Successful_write_queries” | “1482” |
| “Threads_background” | “1” |
| “Threads_cached” | “1” |
| “Threads_connected” | “19” |
| “Threads_created” | “20” |
| “Threads_idle” | “0” |
| “Threads_running” | “1” |
| “Threads_shutdown” | “0” |
| “Threads_waiting_for_disk_space” | “0” |
| “Total_blobs_processed_for_fsync” | “0” |
| “Total_blobs_submitted_for_fsync” | “0” |
| “Total_dedicated_admin_connections” | “0” |
| “Total_io_pool_memory” | “0.8 MB” |
| “Total_server_memory” | “416.6 (+1.4) MB” |
| “Transaction_buffer_wait_time” | “0 ms” |
| “Transaction_logs_directory” | “/var/lib/memsql/be8c543f-2174-48b1-9050-e33d19ad398b/data/logs” |
| “Transaction_log_flush_wait_time” | “8 ms” |
| “Uptime” | “56935” |
| “Used_cluster_capacity” | “22788 MB” |
| “Workload_management_active_connections” | “0” |
| “Workload_management_active_queries” | “0” |
| “Workload_management_active_threads” | “0” |
| “Workload_management_queued_queries” | “0” |
Hi,
At that small of scale (200 mb of data) the extra memory your seeing is just default overhead of running the server. Most of it is coming from here:
“Malloc_transaction_cached_memory” “267.883 (+267.883) MB”
Which is some internal caching we do to speed up starting/committing transactions. We don’t really optimize singlestore that much for small memory foot prints (100s of mb of caching here and there is normal for us).
-Adam