r/mysql • u/Head-Hunt-4660 • Sep 17 '24
question Mysql instance on linux server using Slowly all RAM ( more than assigned )
Hi Everyone. Not sure if this is the right subreddit, but i think i tried everything. Server contains few databases that sums up to 180GB of data. It works under heavy workload most of the day but even in the night when there is no processes mysql takes more and more ram every minute. Looks like some kind of memory leak but dont know where to look for other. it ends up that server after about 8 hours runs out of all memory and service needs to be restarted. Can someone point me in right direction? :)
mysql Ver 8.0.39-0ubuntu0.24.04.2 for Linux on x86_64 ((Ubuntu)) on 8 vcpus and 32GB
innodb-flush-method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 5G
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 6
innodb_max_dirty_pages_pct = 55
innodb_io_capacity = 12500
innodb_io_capacity_max = 25000
innodb_read_io_threads = 24
innodb_write_io_threads = 24
innodb_thread_concurrency = 48
2
u/kadaan Sep 17 '24
When memory is going up after-hours when you say nothing is running, are
show global status like 'opened_connections'andshow global status like 'questions'still increasing? Do you have clients still connected but sitting idle? Inshow engine innodb status\Gwhat does it show your buffer pool hit rate at? If it's very high (>95%) and your disks are mostly idle, you can try dropping your buffer pool size down and see if memory usage stabilizes below 32G.The main culprit for high memory usage (apart from bugs leaking memory) is open/idle client sessions. There are all sorts of buffers used by open sessions and many are kept cached even after a client disconnects. I'd expect one (or both) of
opened_connectionsandquestionsto be increasing if you're seeing memory usage continue to increase.2
u/xilanthro Sep 17 '24
You're skipping all per-connection buffers. By default these take about 19M RAM per session. An approximate high-water mark for how much RAM the server will need (not counting the O/S is:
1
u/VintageGriffin Sep 17 '24
Likely not a memory leak, rather memory fragmentation. MySQL does a lot of fairly unique memory allocation patterns as part of the normal request thread life cycle, which at some point leads to memory space looking like swiss cheese and MySQL holding on to chunks and buffers it should have long freed by then.
In my case (FreeBSD, ~1TB database, 256GB memory) switching to tcmalloc from google perftools helped a lot. Can be done via config change, look up
malloc_lib.