Question Optmized values for MariaDB

[tanasis]

Server operating system version

AlmaLinux 8.10

Plesk version and microupdate number

18.0.64 Update #1

Hello,
I have a webserver with 150 websites.

I notice especially slow response of websites, especially when I'm logged into Wordpress admin. The server has no memory or CPU problems and the disks are fast. I notice that there is a lot of traffic (read & write) in MariaDB.

The total DISK READ is from 200MB / sec to 450MB / sec




Plesk's Performance Booster it says it's Optimized:





I have 62GB memory. About 40GB free...



Do you think, if I change innodb_buffer_pool_size to a bigger size, for example 512MB or bigger, it will be better for website performance ?

 

[pleskpanel]

That is a tiny, tiny, tiny, tiny innodb_buffer_pool_size and possibly causing you issues, especially with 150 websites-hungry WordPress websites.

Try running this command:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Take that value (let's assume it's 15), add about 13% to it and set that innodb_buffer_pool_size to 17G

Now set the innodb_log_file_size to 25% of that value and restart MariaDB. Your disk IO and site speeds should improve dramatically.

Take a look at this post for an idea of where to go from there: How large should be mysql innodb_buffer_pool_size?

 

[tanasis]

That is a tiny, tiny, tiny, tiny innodb_buffer_pool_size and possibly causing you issues, especially with 150 websites-hungry WordPress websites.

Try running this command:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Take that value (let's assume it's 15), add about 13% to it and set that innodb_buffer_pool_size to 17G

Now set the innodb_log_file_size to 25% of that value and restart MariaDB. Your disk IO and site speeds should improve dramatically.

Take a look at this post for an idea of where to go from there: How large should be mysql innodb_buffer_pool_size?

@pleskpanel

My result is 19.
So...
+ 13% = (21.47G) 22G will be the innodb_buffer_pool_size
and...
22G - 25% = (16.5G) 16G will be the innodb_log_file_size

Are these values safe for my memory and Swap?

 

[tanasis]

ChatGPT suggest me....

innodb_buffer_pool_size = 22G
innodb_log_file_size = 5G
innodb_io_capacity 1000-2000 and I set to 1450

Now I have

 

[pleskpanel]

ChatGPT has no actual understanding of the optimizing a database. It can only regurgitate answers based on what it collects around the Internet so if this is important to you, ChatGPT isn't going to be resource that makes sense here.

From the official MariaDB website, this post touches on recommended values:

2. InnoDB Log File Size

The redo logs make sure writes are fast and durable, and the InnoDB redo space size is important for write-intensive workloads. The logs’ size is determined by innodb_log-file-size. For best results, generally you’ll want to set a combined total size to be at least 1/4 (or even 1/2) of the InnoDB buffer pool size, or equal to one hour’s worth of log entries during peak load.

For your innodb_io_capacity question, this is a good resource and the official MariaDB page notes that in MariaDB the default is 200 but for an SSD array you could easily shoot for 1000 to 2000. If regular HDD, then definitely lower it.

Using this as a general guide, try starting with these and make sure to restart the MariaDB service:

innodb_buffer_pool_size = 22G
innodb_log_file_size = 6G
innodb_io_capacity = 2000

* I'll give ChatGPT's answer a little credit as it was close enough and you could have used the values that it provided but it's important to understand why it returned them.

 

[tanasis]

Thank you again for your answer.
My values are:

 

[tanasis]

@pleskpanel I need you help.





I have vm.swappiness = 60

I have 62G RAM , 32G swap and SQL innodv_buffer_pool_size 22G and innodb_log_file_size 6G.

Shall I have to change the vm.swappiness ?

 

[pleskpanel]

@pleskpanel I need you help.


View attachment 27214


I have vm.swappiness = 60

I have 62G RAM , 32G swap and SQL innodv_buffer_pool_size 22G and innodb_log_file_size 6G.

Shall I have to change the vm.swappiness ?

Given that you've now given MariaDB more memory and assuming that you have SSDs or faster, try lowering vm.swappiness to a value such as 1 and the evaluate ensure that after you restart, that the value correctly survives the restart:

# cat /proc/sys/vm/swappiness

Between the MariaDB changes above and this change, you should a difference compared to your initial MariaDB IO benchmarks.

 

[tanasis]

Given that you've now given MariaDB more memory and assuming that you have SSDs or faster, try lowering vm.swappiness to a value such as 1 and the evaluate ensure that after you restart, that the value correctly survives the restart:



Between the MariaDB changes above and this change, you should a difference compared to your initial MariaDB IO benchmarks.

@pleskpanel ,

If I leave it like that, will my swap be filled with the 22G I gave to SQL?
Should I try the vm.swappiness = 30 first?
Instead of reboot can I run sysctl -p ?

 

[pleskpanel]

If it's an SSD array (or NVMe), try 1 first and then evaluate after a period.

Personally I'd shoot for a reboot but you could use sysctl as an alternative.

 

[tanasis]

@pleskpanel
If I leave it like that (I won't but I want to know...) will my swap fill up because I gave 22G of memory to SQL?

 

[learning_curve]

FWIW / Other post readers.
At the time of adding this post, MariaDB 11.*.* is not yet supported by Plesk's Performance Booster.
This should change in due course, as Plesk provide new releases of Obsidian

 

[pleskpanel]

@pleskpanel
If I leave it like that (I won't but I want to know...) will my swap fill up because I gave 22G of memory to SQL?

Now that you've given MariaDB more memory (disk IO, even with SSDs, can be clogged), by changing swapiness the goal is to lower swap access thrashing and prioritize memory.

If you want to experiment without restarts of any kind, try:

# sudo sysctl vm.swappiness=1

Hopefully throughout the day you see an improvement in disk IO reads (you shouldn't see as high of a load from MariaDB), which helps to free up disk reads and writes for those 150 website files.

 

[tanasis]

Yesterday I set swappiness to 5.


Today Swp is bigger, 13.8G


Also MariaDB is bigger is swap:

What am I doing wrong ?

 

[pleskpanel]

Assuming that these are SSD drives, try setting your swapiness to 1 and this time, restart the server.

 

[tanasis]

@pleskpanel , please check Memory usage.
This screenshot is from Plesk Dashboard:


...and this from htop:


Does use swap why has free memory only 1GB?

 

[Bitpalast]

Does use swap why has free memory only 1GB?

Because Linux always uses available RAM for caching. It'll always fill the RAM up, even if it doesn't actually need it for current transactions. It will auto-clear RAM as it is necessary. The problem with your swap file could be that for very short time periods, your RAM is insufficient. This will cause swap file usage, but the swap file will not be cleared afterwards as that would be an unnecessary operation. So it is thinkable that the swap usage you are seeing is no current swap usage, but leftovers from a previous RAM excession.

 

[tanasis]

Because Linux always uses available RAM for caching. It'll always fill the RAM up, even if it doesn't actually need it for current transactions. It will auto-clear RAM as it is necessary. The problem with your swap file could be that for very short time periods, your RAM is insufficient. This will cause swap file usage, but the swap file will not be cleared afterwards as that would be an unnecessary operation. So it is thinkable that the swap usage you are seeing is no current swap usage, but leftovers from a previous RAM excession.

But it is growing 1GB per day...

 

[pleskpanel]

Better question is, has your website back-end performance improved and has the MariaDB IO dropped a bit? It will still be present but writing to disk directly is slower than memory

 

[tanasis]

After 5 days I have 9GB SQL in Swap


...and the swap from 14.2GB to 20.9G



Any advice ?