Hi Simon, This messages was sent yesterday but it did not reach the mailing list. Resent today.
Great answer, thank you very much. Indeed, it seems that is working. I tried both: jemalloc and tcmalloc. See the graph below. - jemalloc, was implemented first, around 10:30. Memory increases almost like before. In 90 minutes, memory usage increased from ~20% to 52% (the same as the default system malloc) - tcmalloc, implemented around 12:00. This time, much better. Memory usage started to increase very slowly and around 4pm it stabilized at 47% (approx 4Gb Ram from total 7.8) According to my calculation and to mysqlcalculator.com, this is the expected usage (between 3.6Gb-4Gb). [image: Screenshot from 2025-04-03 17-20-25.png] In my case, jemalloc was not efficient. I found a short explanation here: https://www.managedserver.eu/Improve-mysql-and-mariadb-performance-with-memory-allocators-like-jemalloc-and-tcmalloc/ *"If your database has memory fragmentation problems, Jemalloc is the best choice.If you need to reduce latency in multi-threaded workloads, TCMalloc can offer superior benefits."* Before change: MariaDB [mysql]> show global variables like '%malloc%'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | version_malloc_library | *system* | +------------------------+--------+ 1 row in set (0.001 sec) MariaDB [mysql]> I used libtcmalloc.so.4 by installing google-perftools (apt-get install google-perftools) After change: MariaDB [mysql]> show global variables like '%malloc%'; Connection id: 327 Current database: mysql +------------------------+--------------------------+ | Variable_name | Value | +------------------------+--------------------------+ | version_malloc_library | *tcmalloc gperftools 2.10* | +------------------------+--------------------------+ 1 row in set (0.022 sec) MariaDB [mysql]> And today, the memory usage is 45% (3.87GB) ... pretty stable. Just a quick question regarding jemalloc. After it was implemented, show global variables like '%malloc%'; was still showing the system (like default). I could not find if is normal or the output should be something like MariaDB [mysql]> show global variables like '%malloc%'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | version_malloc_library | *jemalloc-something* | +------------------------+--------+ Did you implemented jemalloc on your side? If yes, how is looking the output of show global variables like '%malloc%';? Regards, Catal On Thu, Apr 3, 2025 at 5:43 PM catal <[email protected]> wrote: > Hi Simon, > > Great answer. Indeed, it seems that is working. I tried both: jemalloc and > tcmalloc. See the graph below. > - jemalloc, was implemented first, around 10:30. Memory increases almost > like before. In 90 minutes, memory usage increased from ~20% to 52% (the > same as the default system malloc) > - tcmalloc, implemented around 12:00. This time, much better. Memory usage > started to increase very slowly and around 4pm it stabilized at 47% (approx > 4Gb Ram from total 7.8) > According to my calculation and to mysqlcalculator.com, this is the > expected usage (around 4Gb). > > [image: Screenshot from 2025-04-03 17-20-25.png] > > In my case, jemalloc was not efficient. I found a short explanation here: > > https://www.managedserver.eu/Improve-mysql-and-mariadb-performance-with-memory-allocators-like-jemalloc-and-tcmalloc/ > > > *"If your database has memory fragmentation problems, Jemalloc is the best > choice.If you need to reduce latency in multi-threaded workloads, TCMalloc > can offer superior benefits."* > > I used libtcmalloc.so.4 by installing google-perftools (apt-get install > google-perftools) > > MariaDB [mysql]> show global variables like '%malloc%'; > Connection id: 327 > Current database: mysql > > +------------------------+--------------------------+ > | Variable_name | Value | > +------------------------+--------------------------+ > | version_malloc_library | tcmalloc gperftools 2.10 | > +------------------------+--------------------------+ > 1 row in set (0.022 sec) > > MariaDB [mysql]> > > Now I am continuing to monitor it. I'll come back tomorrow with more data. > > Regards, > Catal > > On Thu, Apr 3, 2025 at 8:40 AM Simon Avery <[email protected]> > wrote: > >> Hi Catal >> >> >> >> From experience, in most cases where a mariadb server uses more memory >> than expected, especially if it’s consistently growing to the point of >> oomkiller taking it out, the culprit has been the default malloc handler on >> the OS. I’ve found this both on various flavours of EL (Centos and Rocky, >> 6-9) as well as Debian 12. >> >> As it’s a bit of a FAQ, I jotted down how we solve it by using jemalloc >> or tcmalloc, both of which work well with MariaDb on linux. >> >> >> >> https://digdilem.org/2025/03/solving-excess-memory-usage-in-mariadb/ >> >> I would suggest trying that before going further. It’s a quick and cheap >> fix, and if it doesn’t work, easy to revert. >> >> Regards, Simon >> >> >> See below: >> Operating system: "Debian 12.10 (bookworm)" >> >> MariaDB Version: 10.11.11-MariaDB-0+deb12u1-log >> >> >> >> The server was configured (limited) to use for InnoDB -> max 3GB Ram >> >> >> >> MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'; >> +-------------------------+------------+ >> | Variable_name | Value | >> +-------------------------+------------+ >> | innodb_buffer_pool_size | 3221225472 | >> +-------------------------+------------+ >> 1 row in set (0.001 sec) >> >> >> >> But, on that server *we are running also MySam, Memory * and Innodb, in >> total, ~70 databases (8 MySam, 1 MEMORY, and the rest are InnoDB) >> >> >> >> MariaDB [mysql]> select count(distinct db) from db; >> +--------------------+ >> | count(distinct db) | >> +--------------------+ >> | 73 | >> +--------------------+ >> 1 row in set (0.001 sec) >> MariaDB [mysql]> >> >> >> >> SYSTEM 4CPU, 7.8GB RAM >> >> >> >> root@adsns-db-node0:~# df -h >> Filesystem Size Used Avail Use% Mounted on >> udev 3.8G 0 3.8G 0% /dev >> tmpfs 776M 764K 775M 1% /run >> /dev/sda1 150G 48G 97G 34% / >> tmpfs 3.8G 0 3.8G 0% /dev/shm >> tmpfs 5.0M 0 5.0M 0% /run/lock >> /dev/sda15 241M 138K 241M 1% /boot/efi >> tmpfs 776M 0 776M 0% /run/user/0 >> root@adsns-db-node0:~# >> >> I found some articles on internet regarding /dev/shm, which could >> influence mysql performance. >> >> In my case /dev/shm is not used even defined, so the system could use the >> whole available RAM - 7.8GB RAM, so I excluded this as possible cause. >> >> >> >> MariaDB [(none)]> SHOW VARIABLES LIKE '%buffer_size%'; >> +----------------------------+-----------+ >> | Variable_name | Value | >> +----------------------------+-----------+ >> | aria_pagecache_buffer_size | 134217728 | >> | aria_sort_buffer_size | 268434432 | >> | bulk_insert_buffer_size | 8388608 | >> | innodb_log_buffer_size | 16777216 | >> | innodb_sort_buffer_size | 1048576 | >> | join_buffer_size | 4194304 | >> | key_buffer_size | 10485760 | >> | mrr_buffer_size | 262144 | >> | myisam_sort_buffer_size | 134216704 | >> | preload_buffer_size | 32768 | >> | read_buffer_size | 131072 | >> | read_rnd_buffer_size | 262144 | >> | sort_buffer_size | 2097152 | >> +----------------------------+-----------+ >> 13 rows in set (0.001 sec) >> >> >> >> Traffic is not high, just 123 queries per second. >> >> >> >> MariaDB [(none)]> status; >> -------------- >> mysql Ver 15.1 Distrib 10.11.11-MariaDB, for debian-linux-gnu (x86_64) >> using EditLine wrapper >> >> Connection id: 877596 >> Current database: >> Current user: root@localhost >> SSL: Not in use >> Current pager: stdout >> Using outfile: '' >> Using delimiter: ; >> Server: MariaDB >> Server version: 10.11.11-MariaDB-0+deb12u1-log Debian 12 >> Protocol version: 10 >> Connection: Localhost via UNIX socket >> Server characterset: utf8mb4 >> Db characterset: utf8mb4 >> Client characterset: utf8mb3 >> Conn. characterset: utf8mb3 >> UNIX socket: /run/mysqld/mysqld.sock >> Uptime: 7 days 3 hours 24 min 6 sec >> >> Threads: 78 Questions: 75901186 Slow queries: 4891595 Opens: 12573592 >> Open tables: 2000 Queries per second avg: 123.007 >> -------------- >> >> MariaDB [(none)]> >> >> >> >> Even I am restarting mariadb, after a short time, the memory usage is 85% >> >> >> >> root@adsns-db-node0:~# free -g >> total used free shared buff/cache >> available >> Mem: 7 6 0 0 0 >> 1 >> Swap: 0 0 0 >> root@adsns-db-node0:~# >> >> >> >> The swap is not used, just the memory, which is ok. >> >> CPU usage is less than ~10%, so is ok. >> >> >> >> See below, output of top. >> >> %Cpu(s): 2.2 us, 0.7 sy, 0.0 ni, 96.2 id, 0.4 wa, 0.0 hi, 0.4 si, >> 0.0 st >> MiB Mem : 7750.8 total, 161.4 free, 6351.8 used, 712.6 >> buff/cache >> MiB Swap: 0.0 total, 0.0 free, 0.0 used. 1399.0 avail >> Mem >> >> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ >> COMMAND >> 1555028 mysql 20 0 7752424 6.5g 15476 S 10.6 85.2 188:29.16 >> mariadbd >> >> >> >> root@adsns-db-node0:~# pstree |grep maria >> |-mariadbd---51*[{mariadbd}] >> root@adsns-db-node0:~# >> >> >> >> All 51 childs of mariadb are using 85% from the system memory (each child >> ~1.6% of total RAM). >> >> >> >> I found a mysql calculator and changing innodb_buffer_pool_size - its >> default value 8M - with 3G, I got ~3.7GB ... still not 6.6G (aprox 85% from >> total RAM). >> >> See: https://www.mysqlcalculator.com/ >> >> >> >> 5. Why this difference, from 3.7GB up to 6.6GB? >> >> 6. Where could it be the rest of 2GB which is used by mariadb (from 3.7GB >> up to 6.6GB)? >> >> >> >> Any help will be appreciated. >> >> Catal >> >> >> >> >> >> *From:* cata via discuss <[email protected]> >> *Sent:* 02 April 2025 15:27 >> *To:* [email protected] >> *Subject:* [MariaDB discuss] it is normal to have a memory usage of 85%? >> >> >> >> Hi everybody, >> >> >> >> I am new here and I am confronted with a situation, so I will start with >> the questions first: >> >> >> >> 1. It is normal that on my server, mariadb to use almost 85% of available >> ram or something is wrong? >> >> 2. In case I want to limit the whole application (mariadb) to not use >> more than 5.5 GB RAM from total of 7.8GB it could be possible - without >> affecting it's performance? >> >> 3. Is anything to optimize (change) now? If yes, which variable? >> >> 4. Do we need more RAM for this server or actual RAM could be enough? >> >
_______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
