Are these settings good enough for Mariadb?

johannes said:
but its maybe a starting point against too much memory problem
Click to expand...
I didn't had a too much memory problem, but I didn't know that calculator yet. Interesting indeed if it is still valid for MariaDb. Thanks.

As for the default values... it's the same link I posted in #16. ;)
 
Hi All,
i'm sorry for reviving an old thread, but i'm very interested in this topic rn..
i've an instance of HumHub running with about 3k users registered, and just over 1k unique users a day taking about 2k actions a day (likes, comments, etc..)..

This was running on a standard 'out of the box' configuration, which was, err, slow..
(HumHub relies very heavily on MySQL)

i used this as a reference:
https://gist.github.com/fevangelou/fb72f36bbe333e059b66

And this script which seems quite useful:
https://github.com/BMDan/tuning-primer.sh


which seems quite useful,
here are the settings i went with for my.conf:
Code: 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
sql_mode=""
skip-log-bin
performance-schema=0
max_allowed_packet=268435456
open_files_limit=10000
key_buffer_size=24M
max_connections=120
table_open_cache=1024
local-infile=0
thread_cache_size=8
join_buffer_size=1M
innodb_use_native_aio=0
innodb_temp_data_file_path=ibtmp1:64M:autoextend:max:1G

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#added by me
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=4G
innodb_file_per_table=1
innodb_log_file_size=256M
innodb_log_buffer_size=64M
innodb_sort_buffer_size=4M
back_log=512
thread_cache_size=100
thread_stack=192K
join_buffer_size=4M
read_buffer_size=3M
read_rnd_buffer_size=4M
sort_buffer_size=4M
table_definition_cache=40000
table_open_cache=40000
open_files_limit=60000
max_heap_table_size=256M
tmp_table_size=256M


i have to wait a while to see how it goes, but it looks promising

Just fyi, the server is AlmaLinux 8, with 12GB RAM, 8x CPU

i'm eager to hear anyone's input on this..
 
Last edited:
Hi again, the above has helped me a lot on a high-traffic installation of HumHub!
i still have a couple of questions, though:
you don't change innodb_buffer_pool_chunk_size, but you do change innodb_buffer_pool_size and innodb_buffer_pool_instances..

on my server, i have a total of 12GB RAM, and 8x CPU cores
so, i set some conservative numbers:
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4

but i havent yet set innodb_buffer_pool_chunk_size..
so, i was thinking of doing the following:
innodb_buffer_pool_size=6G
innodb_buffer_pool_instances=6
innodb_buffer_pool_chunk_size=512M

Because i have several websites on
What do you think?
Does that mean i'm basically only ever going to use a maximum of 3GB, because 512M x 6 = 3G?

Does the number of innodb_buffer_pool_instances have any relation to the number of CPU cores?

Finally, the tool linked in the beginning of the article, called MySQLTuner-perl, calculates the maximum RAM MySQL/MariaDB will use as innodb_buffer_pool_size multiplied by innodb_buffer_pool_instances, but that does not seem to be correct, right?

Sorry for all the questions, any advice would be greatly appreciated!!!
 
Last edited:
@petersconsult No, I don't change innodb_buffer_pool_chunk_size even on servers with 300gb+ mysql and 50gb+ tables. but it's my experience.
"innodb_buffer_pool_size multiplied by innodb_buffer_pool_instances" - no, it works another way.
it multiplies each connection theoretical max_memory consumprion to max_connections, also it thinks that max_allowed_packet can be used in each connection, so if you have max_allowed_packet 256M and max_connections 100 - you will see 25gb+ (maybe 30gb due to another per-connection limits/pools/buffers). But this not actually will happen, I don't think that there will be 100 simultaneous connections to different databases that will upload their dumps or something similar. Mysqltuner also shows "reached max memory" that was really used since mysql started.
 
I'm going to jump in here, because I think this is interesting.

Generally, mariadb and mysql work pretty well with their defaults. In this case max_allowed_packet is 64MB. You've decided to change that value to 256MB.

Did you have some kind of indication that you needed 4X the default? I'm guess that you must have or why else would you have made the change? So it's safe to assume you need max_allowed_packet to be equal to 256MB.

I also see you set max_connections to 120. Assuming you have a server that hits 120 max_connections (or else why would you make it that value?) and you need max_allowed_packet to equal 256MB, you will hit 30GB on a server with only 12GB of memory. If you truly believe these are the correct settings, then you need more memory.

If this were my server, I would be worried with those settings, assuming I really needed max_allowed_packet to be 256MB and max_connections at 120.

Before anyone argues the server will never hit 120 simultaneous connections, then I'll say - then why is the value set to 120? Or max_allowed_packet will never be 256MB - again, then why set it that way?

Don't mind me, I'm only using logic.
 
Throwing in my two cents...

I can see increasing the max connections if it's actually needed. Settings need to be sized for resources available though.

But your not going to get there with just 12GB of RAM and 8 CPU cores, you will probably be swapping like crazy and become totally unresponsive if you randomly increase settings.

And I have seen no need to increase the max_allowed_packet on any of my web servers.

Would suggest using MySQLTuner (https://github.com/major/MySQLTuner-perl), to see how the default settings are working for you, and not guess on increasing values, it will give you suggestions on what you can do to improve the configuration. It will also warn you if you have configured things in a way that could be come a problem in the future. It's good to run periodically to see how things are going.

And it's also good to adjust your swappiness of your kernel. I would suggest a value of 1 or so (don't set it to zero). https://www.howtoforge.com/tutorial/linux-swappiness/
 
Yeah, thank you,
i noticed that earlier and commented the lines out..
This is my current config, which is much better than the original..
Code: 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
sql_mode=""
skip-log-bin
performance-schema=0
max_allowed_packet=268435456
#open_files_limit=10000
key_buffer_size=24M
max_connections=60
#table_open_cache=1024
local-infile=0
#thread_cache_size=8
#join_buffer_size=1M
innodb_use_native_aio=0
innodb_temp_data_file_path=ibtmp1:64M:autoextend:max:1G

#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#added by me
innodb_buffer_pool_instances=6
innodb_buffer_pool_size=6G
innodb_buffer_pool_chunk_size=512M
innodb_file_per_table=1
innodb_log_file_size=256M
innodb_log_buffer_size=64M
innodb_sort_buffer_size=4M
back_log=512
thread_cache_size=100
thread_stack=192K
join_buffer_size=4M
read_buffer_size=3M
read_rnd_buffer_size=4M
sort_buffer_size=4M
table_definition_cache=40000
table_open_cache=40000
open_files_limit=60000
max_heap_table_size=256M
tmp_table_size=256M
innodb_redo_log_capacity=768M

#troubleshooting
long_query_time=2
slow_query_log=0
slow_query_log_file=/var/log/mysql/mysql-log-slow-queries.log
log_queries_not_using_indexes=0
log_error=/var/log/mysql/mysql-error.log
 
You must log in or register to reply here.
Back
Top