weird Mariadb memory usage
We made small tweaks on my.cnf according to mysql tuner, and tuning primer, after mariadb got locktables and frooze with default cpanel my.cnf file,
but i can't really figure out when the memory will be stabilize usage of mariadb or mariadb 10.5.13 of cpanel or if there a memory leak in cpanel mariadb 10.5.
Here the result tuning primer -
Here a sample of resources from top since start up until 5 days and 8 hours uptime of mariadb -
if im trying to determine the memory usage via service status the following result -
now i just see so many indiffrent current memory usage, and for example tuning primer returns -
but mysql tuner returns -
attaching my.cnf configurations -
But as you can see, top command returns 24.9gb ram usage, and service status returns 37.4GB ram usage? and tuning primer saying max memory is 16.85GB, but mysql tuner says 67.1GB? I'm completly lost in this one.. when the memory will build up until getting a stabilize memory usage? why tuning primer, and mysql tuner memory usage is so different? why the current usage of the server itself for mariadb service diffrent from top command? is it possible mariadb 10.5 of cpanel package has memory leak issue? how long takes sql memory usage to stabilize after reset to the sql service? Server specs: Intel Xeon W-2295 128gb ECC memory NVME hard drives OS - Cloudlinux 8 Could anyone share his tought regards it? Thanks!
------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5d 8h 19m 28s (226M q [491.299 qps], 1M conn, TX: 4653G, RX: 49G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory : 125.3G
[--] Max MySQL memory : 67.1G
[--] Other process memory: 0B
[--] Total buffers: 15.4G global + 264.8M per thread (200 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 34.3G (27.34% of installed RAM)
[OK] Maximum possible memory usage: 67.1G (53.54% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (78/226M)
[OK] Highest usage of available connections: 36% (73/200)
[OK] Aborted connections: 0.02% (447/1823123)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 56M sorts)
[!!] Joins performed without indexes: 815533
[!!] Temporary tables created on disk: 87% (26M on disk / 30M total)
[OK] Thread cache hit rate: 99% (73 created / 1M connections)
[OK] Table cache hit rate: 99% (286M hits / 287M requests)
[OK] table_definition_cache(37000) is upper than number of tables(36204)
[OK] Open file limit used: 69% (51K/74K)
[OK] Table locks acquired immediately: 99% (170M immediate / 170M locks)Here the result tuning primer -
WORKER THREADS
Current thread_cache_size = 200
Current threads_cached = 72
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 200
Current threads_connected = 1
Historic max_used_connections = 73
The number of used connections is 36% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 1.46 G
Current InnoDB data space = 10.79 G
Current InnoDB buffer pool free = 24 %
Current innodb_buffer_pool_size = 15.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 15.76 G
Configured Max Per-thread Buffers : 1.71 G
Configured Max Global Buffers : 15.14 G
Configured Max Memory Limit : 16.85 G
Physical Memory : 125.34 G
Max memory limit seem to be within acceptable normsHere a sample of resources from top since start up until 5 days and 8 hours uptime of mariadb -
mysql 20 0 21.4g 4.1g 24576 S 4.7 3.3 0:08.90 mariadbd
mysql 20 0 21.4g 4.8g 25728 S 4.7 3.8 2:13.62 mariadbd
mysql 20 0 25.9g 8.7g 26480 S 15.0 6.9 56:52.91 mariadbd
mysql 20 0 26.2g 8.8g 26480 S 3.7 7.0 58:37.38 mariadbd
mysql 20 0 26.7g 9.3g 26480 S 10.6 7.4 66:38.53 mariadbd
mysql 20 0 26.7g 9.3g 26480 S 6.3 7.4 76:34.20 mariadbd
mysql 20 0 26.9g 9.6g 26480 S 10.0 7.7 80:31.37 mariadbd
mysql 20 0 26.9g 9.8g 26480 S 10.3 7.8 95:36.03 mariadbd
mysql 20 0 27.4g 10.2g 26480 S 8.0 8.1 96:28.67 mariadbd
mysql 20 0 27.7g 12.4g 26496 S 2.7 9.9 127:20.78 mariadbd
mysql 20 0 27.7g 15.0g 26496 S 11.3 12.0 129:35.15 mariadbd
mysql 20 0 27.7g 16.3g 26492 S 4.7 13.0 130:34.33 mariadbd
mysql 20 0 27.7g 16.3g 26492 S 22.9 13.0 135:47.21 mariadbd
mysql 20 0 27.7g 16.4g 26460 S 13.3 13.1 182:57.25 mariadbd
mysql 20 0 27.7g 16.9g 26436 S 5.0 13.5 186:18.78 mariadbd
mysql 20 0 28.2g 17.4g 26436 S 27.9 13.9 220:29.14 mariadbd
mysql 20 0 28.2g 17.4g 26436 S 10.3 13.9 240:20.25 mariadbd
mysql 20 0 28.6g 17.8g 26436 S 15.9 14.2 242:50.85 mariadbd
mysql 20 0 28.6g 17.9g 26436 S 6.6 14.2 244:48.40 mariadbd
mysql 20 0 29.0g 18.3g 26436 S 5.0 14.6 271:35.53 mariadbd
mysql 20 0 29.4g 18.5g 26480 S 11.6 14.7 327:44.08 mariadbd
mysql 20 0 29.8g 18.9g 26480 S 21.6 15.1 328:47.99 mariadbd
mysql 20 0 29.8g 18.9g 26480 S 3.7 15.1 340:27.10 mariadbd
mysql 20 0 29.8g 18.9g 26480 S 7.0 15.1 360:15.26 mariadbd
mysql 20 0 30.2g 19.6g 26460 S 3.6 15.7 397:31.67 mariadbd
mysql 20 0 31.4g 20.1g 26460 S 6.6 16.1 424:18.82 mariadbd
mysql 20 0 31.8g 20.6g 26460 S 1.7 16.5 434:33.15 mariadbd
mysql 20 0 31.8g 20.7g 26460 S 12.3 16.5 474:25.23 mariadbd
mysql 20 0 32.1g 21.1g 26444 S 9.6 16.9 501:38.46 mariadbd
mysql 20 0 32.1g 21.1g 26448 S 14.0 16.9 509:05.98 mariadbd
mysql 20 0 32.5g 21.6g 26448 S 10.3 17.2 524:06.26 mariadbd
mysql 20 0 33.0g 22.0g 26448 S 7.0 17.6 527:11.55 mariadbd
mysql 20 0 33.5g 22.5g 25612 S 29.9 17.9 578:12.81 mariadbd
mysql 20 0 33.5g 22.5g 25612 S 2.3 18.0 580:04.96 mariadbd
mysql 20 0 34.4g 23.4g 25044 S 17.9 18.7 647:25.39 mariadbd
mysql 20 0 35.3g 24.5g 25272 S 10.0 19.5 753:34.30 mariadbd
mysql 20 0 35.7g 24.9g 25272 S 10.0 19.9 766:17.57 mariadbdif im trying to determine the memory usage via service status the following result -
service mariadb status
Redirecting to /bin/systemctl status mariadb.service
? mariadb.service - MariaDB 10.5.13 database server
Active: active (running) since Mon 2022-01-03 03:03:15 IST; 5 days ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 87 (limit: 820947)
Memory: 37.4Gnow i just see so many indiffrent current memory usage, and for example tuning primer returns -
Configured Max Memory Limit : 16.85 G
but mysql tuner returns -
[OK] Maximum reached memory usage: 34.3G (27.34% of installed RAM)
[OK] Maximum possible memory usage: 67.1G (53.54% of installed RAM)attaching my.cnf configurations -
performance-schema=0
#innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
open_files_limit=74000
innodb_file_per_table=1
unix_socket=OFF
max_connections=200
#wait_timeout=600
#interactive_timeout=600
##
innodb_buffer_pool_size=15G
max_heap_table_size=128M
tmp_table_size=128M
#max_connections=400
table_open_cache=37000
table_definition_cache=37000
thread_cache_size=200
key_buffer_size=128M
sort_buffer_size=1M
read_buffer_size=4M
read_rnd_buffer_size=512k
join_buffer_size=3M
##But as you can see, top command returns 24.9gb ram usage, and service status returns 37.4GB ram usage? and tuning primer saying max memory is 16.85GB, but mysql tuner says 67.1GB? I'm completly lost in this one.. when the memory will build up until getting a stabilize memory usage? why tuning primer, and mysql tuner memory usage is so different? why the current usage of the server itself for mariadb service diffrent from top command? is it possible mariadb 10.5 of cpanel package has memory leak issue? how long takes sql memory usage to stabilize after reset to the sql service? Server specs: Intel Xeon W-2295 128gb ECC memory NVME hard drives OS - Cloudlinux 8 Could anyone share his tought regards it? Thanks!
-
@Lolfust - if MySQL Governor resolved the issue, that would usually indicate a problem with a particular domain using too many resources. If you'd like to submit a ticket to our team we'd be happy to check your specific system for any issues. 0 -
@Lolfust - if MySQL Governor resolved the issue, that would usually indicate a problem with a particular domain using too many resources. If you'd like to submit a ticket to our team we'd be happy to check your specific system for any issues.
it already been resolved by switching to the mysql govern, i had a ticket with cPanel in this matter around the ticket time, they tried to suggest settings, but for some reason the memory were keep pilling up even more than mysql tuner said. Soon im setting up a new server around same setup but with Mariadb 10.6 i will ran test on the new server on idle and see if the memory still ramping up without any load on the server or been fixed(i have previously reported a bug on mariadb 10.5 on cPanel with mysql folder premission security issue on server reboots at first the support tought huh?! and after few replies they dig into the check and found some weird issue with the mariadb package installed changing the premissions on every reboot). but once you go mysql govern you never go back ^^0
Please sign in to leave a comment.
Comments
4 comments