Question MySQL eating ram as devil

[ic3_2k]

Server operating system version

Almalinux 8.9

Plesk version and microupdate number

v18.0.57_build1800231218.09

Hello,

I installed plesk for a customer with default values 2 or 3 months ago.

Yesterday that customer sent me a message about swap being used at 100%, the server has 32Gb of ram, which only 3 of them being used


After searching a little bit found that only 1Gb of swap is available, don't know why during the default system install only 1Gb was detinated to swap partition....

Searched a little bot more to find the origin of this swap use and also found the MySQL with default 128Mb of innodb_buffer_pool_size, so after running mysqltuner I tweaked the values a little bit:

innodb_buffer_pool_size = 20G
table_definition_cache = 5000
table_open_cache = 35000
join_buffer_size = 20M
innodb_log_file_size=4G
innodb_buffer_pool_instances=20
open_files_limit = 10240
performance_schema=ON
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
local-infile=0
# added by Plesk Performance Booster
!include /etc/db-performance.cnf


But after changing this values the usage of MySQL's rocketskyed to the 100% asigned:


when before this changes the memory used by mysql was over 1Gb:


Can someone explain why this happens, and how to solve this problem?

Regards, ic3_2k

 

[Dave W]

innodb_buffer_pool_size = 20G << so it set up a pool in RAM of 20GB.

Help! Linux ate my RAM!

swap should ideally be about 50% of RAM.....ish.....

 

[ic3_2k]

innodb_buffer_pool_size = 20G << so it set up a pool in RAM of 20GB.

Help! Linux ate my RAM!

swap should ideally be about 50% of RAM.....ish.....

Sorry, maybe I explained myself badly and having two questions in same post does not help at all, tried to put as mas info as I could but...
The swap is now 50% of ram size, that is ok

Before tweaking (inno_db_pool_size=128Mb) the values of resources usage for this server was perfect at first looking:
3%CPU usage, CPU load 0.8, 97%RAM Avalilable (3Gb used, 17Gb cached, 9Gb free), and when in details it showed that MySQL was only between 1.2Gb to 1.6Gb.
With these consumptions, and regardless of the initial size of the swap partition, the fact is that the swap was being used illogically, eventually filling the swap partition completely. Investigating the issue, we concluded that it was because of MySQL. Following common practices the innodb_buffer_pool_size was increased to 2/3 of the total RAM.

After this change, and according to the information from the same link you provided, memory usage has become dangerously high, with 24.4 GB used, 4.2 GB cached, and only 1.3 GB available. Additionally, MySQL usage has increased from just over one gigabyte to 21.8 GB.

Can someone help me to improve this? MySQLTunner says it is ok, isn't it?

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 20.0G ) under limit for 64 bits architecture: (17179869184.0G )
[OK] InnoDB buffer pool / data size: 20.0G / 13.6G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50%): 5.0G * 2 / 20.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 20
[--] Number of InnoDB Buffer Pool Chunk: 160 for 20 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.80% (157965781 hits / 158279849 total)
[OK] InnoDB Write Log efficiency: 93.49% (125701 hits / 134460 total)
[OK] InnoDB log waits: 0.00% (0 waits / 8759 writes)

The only configuration related tweaks mysqltunner say now are:
Variables to adjust:
skip-name-resolve=ON
join_buffer_size (> 20.0M, or always use indexes with JOINs)
key_buffer_size (~ 24M)
innodb_log_file_size should be (=2G) if possible, so InnoDB total log file size equals 25% of buffer pool size.

 

[Dave W]

A rule of thumb for an innodb_buffer_pool_size of 50-75% of RAM is fine for small servers that are just running MySQL or MariaDB

If you are running other services then you are going to squeeze them into the remaining RAM.

innodb_buffer_pool at 20GB is Huuuuuuuge!...you are essentially assigning 20GB of your RAM to the database server.

 

[ic3_2k]

Ok, so what will be your recommendation of pool size for it?

there are 7 ecommere websites, one with 6Gb DB, other with 4GB DB another 2GB, one with 1.5Gb, one with 0.5GB, and another 3 databases of few hundreds of Mb each

 

[ic3_2k]

I think 20Gb for MySQL is not so high..

Calculating InnoDB Buffer Pool Size For Your MySQL Server

In this post, you'll learn 2 methods of calculating InnoDB buffer pool size, examine pros and cons, and how to compute a optimum value based on system RAM size.

scalegrid.io

Actual usage of rest of services:

Apache-FPM usage: 800Mb
Nginx: 19Mb
Plesk: 4Mb
Email: 11.9Mb

(update: swap usage went down from full swap usage to stable usage of 200Mb)

 

[ic3_2k]

so, please what do you recomend?