# === Optimized my.cnf configuration for MySQL/MariaDB (on Ubuntu, CentOS, Almalinux etc. servers) === | |
# | |
# by Fotis Evangelou, developer of Engintron (engintron.com) | |
# | |
# ~ Updated December 2021 ~ | |
# | |
# | |
# The settings provided below are a starting point for a 8-16 GB RAM server with 4-8 CPU cores. | |
# If you have different resources available you should adjust accordingly to save CPU, RAM & disk I/O usage. | |
# | |
# The settings marked with a specific comment or the word "UPD" (after the value) | |
# should be adjusted for your system by using database diagnostics tools like: | |
# | |
# https://github.com/major/MySQLTuner-perl | |
# or | |
# https://github.com/BMDan/tuning-primer.sh | |
# | |
# Run either of these scripts before optimizing your database, at least 1 hr after the optimization & finally | |
# at least once a day for 3 days (without restarting the database) to see how your server performs and if you need | |
# to re-adjust anything. The more MySQL/MariaDB runs without restarting, the more usage data it gathers, so these | |
# diagnostics scripts will report in mode detail how MySQL/MariaDB performs. | |
# | |
# | |
# IMPORTANT NOTE: If there is NO comment after a setting value, then 99,9% of the times you won't need to adjust it. | |
# | |
# | |
# --- THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING --- | |
# | |
# If any terminal commands are mentioned, make sure you execute them as "root" user. | |
# | |
# If MySQL or MariaDB cannot start (or restart), then perform the following actions. | |
# | |
# 1. If the server had the stock database configuration and you added or updated any | |
# "innodb_log_*" settings (as suggested below), then execute these commands ONLY | |
# the first time you apply this configuration: | |
# | |
# $ rm -rvf /var/lib/mysql/ib_logfile* | |
# $ chown -R mysql:mysql /var/lib/mysql | |
# $ service mysql restart | |
# | |
# or use the shorthand command: | |
# $ rm -rvf /var/lib/mysql/ib_logfile*; chown -R mysql:mysql /var/lib/mysql; service mysql restart | |
# | |
# 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is | |
# properly configured. A good example of a "clean" /etc/hosts file is something like this: | |
# | |
# 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 | |
# ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 | |
# 1.2.3.4 hostname.domain.tld hostname # <-- Replace accordingly! | |
# | |
# Finally restart the database service: | |
# | |
# $ service mysql restart | |
# | |
# 3. If the database service cannot restart even after the first 2 steps, make sure the database data folder | |
# (common for either MySQL or MariaDB) "/var/lib/mysql" is owned by the "mysql" user AND group. | |
# Additionally, the folder itself can have 0751 or 0755 file permissions. To fix it, simply do this: | |
# $ chown -R mysql:mysql /var/lib/mysql | |
# $ chmod 0755 /var/lib/mysql | |
# | |
# Finally restart the database service: | |
# | |
# $ service mysql restart | |
# | |
# | |
# ~ FIN ~ | |
[mysql] | |
port = 3306 | |
socket = /var/run/mysqld/mysqld.sock | |
[mysqld] | |
# === Required Settings === | |
basedir = /usr | |
bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections | |
datadir = /var/lib/mysql | |
#default_authentication_plugin = mysql_native_password # Enable in MySQL 8+ or MariaDB 10.6+ for backwards compatibility with common CMSs | |
max_allowed_packet = 256M | |
max_connect_errors = 1000000 | |
pid_file = /var/run/mysqld/mysqld.pid | |
port = 3306 | |
skip_external_locking | |
skip_name_resolve | |
socket = /var/run/mysqld/mysqld.sock | |
tmpdir = /tmp | |
user = mysql | |
# === SQL Compatibility Mode === | |
# Enable for b/c with databases created in older MySQL/MariaDB versions | |
# (e.g. when using null dates) | |
#sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES | |
# Crappy SQL queries/schema? Go bold! | |
#sql_mode = "" | |
# === InnoDB Settings === | |
default_storage_engine = InnoDB | |
innodb_buffer_pool_instances = 4 # Use 1 instance per 1GB of InnoDB pool size - max is 64 | |
innodb_buffer_pool_size = 4G # Use up to 70-80% of RAM | |
innodb_file_per_table = 1 | |
innodb_flush_log_at_trx_commit = 0 | |
innodb_flush_method = O_DIRECT | |
innodb_log_buffer_size = 16M | |
innodb_log_file_size = 1G | |
innodb_sort_buffer_size = 4M # UPD - Defines how much data is read into memory for sorting operations before writing to disk (default is 1M / max is 64M) | |
innodb_stats_on_metadata = 0 | |
#innodb_use_fdatasync = 1 # Only (!) for MySQL v8.0.26+ | |
#innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file | |
#innodb_thread_concurrency = 4 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better | |
# contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check | |
# the overall load produced by MySQL/MariaDB. | |
innodb_read_io_threads = 64 | |
innodb_write_io_threads = 64 | |
#innodb_io_capacity = 2000 # Depends on the storage tech - use 2000 for SSD, more for NVMe | |
#innodb_io_capacity_max = 4000 # Usually double the value of innodb_io_capacity | |
# === MyISAM Settings === | |
# The following 3 options are ONLY supported by MariaDB & up to MySQL 5.7 | |
# Do NOT un-comment on MySQL 8.x+ | |
#query_cache_limit = 4M # UPD | |
#query_cache_size = 64M # UPD | |
#query_cache_type = 1 # Enabled by default | |
key_buffer_size = 24M # UPD | |
low_priority_updates = 1 | |
concurrent_insert = 2 | |
# === Connection Settings === | |
max_connections = 100 # UPD - Important: high no. of connections = high RAM consumption | |
back_log = 512 | |
thread_cache_size = 100 | |
thread_stack = 192K | |
interactive_timeout = 180 | |
wait_timeout = 180 | |
# For MySQL 5.7+ only (disabled by default) | |
#max_execution_time = 90000 # Set a timeout limit for SELECT statements (value in milliseconds). | |
# This option may be useful to address aggressive crawling on large sites, | |
# but it can also cause issues (e.g. with backups). So use with extreme caution and test! | |
# More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time | |
# For MariaDB 10.1.1+ only (disabled by default) | |
#max_statement_time = 90 # The equivalent of "max_execution_time" in MySQL 5.7+ (set above) | |
# The variable is of type double, thus you can use subsecond timeout. | |
# For example you can use value 0.01 for 10 milliseconds timeout. | |
# More info at: https://mariadb.com/kb/en/aborting-statements/ | |
# === Buffer Settings === | |
# Handy tip for managing your database's RAM usage: | |
# The following values should be treated carefully as they are added together and then multiplied by your "max_connections" value. | |
# Other options will also add up to RAM consumption (e.g. tmp_table_size). So don't go switching your "join_buffer_size" to 1G, it's harmful & inefficient. | |
# Use one of the database diagnostics tools mentioned at the top of this file to count your database's potential total RAM usage, so you know if you are within | |
# reasonable limits. Remember that other services will require enough RAM to operate properly (like Apache or PHP-FPM), so set your limits wisely. | |
join_buffer_size = 4M # UPD | |
read_buffer_size = 3M # UPD | |
read_rnd_buffer_size = 4M # UPD | |
sort_buffer_size = 4M # UPD | |
# === Table Settings === | |
# In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit | |
# to be overriden (also see comment next to open_files_limit). | |
# E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html | |
# and for MariaDB check: https://mariadb.com/kb/en/library/systemd/ | |
table_definition_cache = 40000 # UPD | |
table_open_cache = 40000 # UPD | |
open_files_limit = 60000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's | |
# open files limit usually set in /etc/sysctl.conf and /etc/security/limits.conf | |
# In systemd managed systems this limit must also be set in: | |
# - /etc/systemd/system/mysql.service.d/override.conf (for MySQL 5.7+ in Ubuntu) or | |
# - /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+ in CentOS) or | |
# - /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB) | |
# otherwise changing open_files_limit will have no effect. | |
# | |
# To edit the right file execute: | |
# $ systemctl edit mysql (or mysqld or mariadb) | |
# and set "LimitNOFILE=" to something like 100000 or more (depending on your system limits for MySQL) | |
# or use "LimitNOFILE=infinity" for MariaDB only. | |
# Finally merge the changes with: | |
# $ systemctl daemon-reload; systemctl restart mysql (or mysqld or mariadb) | |
max_heap_table_size = 128M # Increase to 256M or 512M if you have lots of temporary tables because of missing indices in JOINs | |
tmp_table_size = 128M # Use same value as max_heap_table_size | |
# === Search Settings === | |
ft_min_word_len = 3 # Minimum length of words to be indexed for search results | |
# === Binary Logging === | |
disable_log_bin = 1 # Binary logging disabled by default | |
#log_bin # To enable binary logging, uncomment this line & only one of the following 2 lines | |
# that corresponds to your actual MySQL/MariaDB version. | |
# Remember to comment out the line with "disable_log_bin". | |
#expire_logs_days = 1 # Keep logs for 1 day - For MySQL 5.x & MariaDB before 10.6 only | |
#binlog_expire_logs_seconds = 86400 # Keep logs for 1 day (in seconds) - For MySQL 8+ & MariaDB 10.6+ only | |
# === Error & Slow Query Logging === | |
log_error = /var/lib/mysql/mysql_error.log | |
log_queries_not_using_indexes = 0 # Disabled on production | |
long_query_time = 5 | |
slow_query_log = 0 # Disabled on production | |
slow_query_log_file = /var/lib/mysql/mysql_slow.log | |
[mysqldump] | |
# Variable reference | |
# For MySQL 5.7+: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html | |
# For MariaDB: https://mariadb.com/kb/en/library/mysqldump/ | |
quick | |
quote_names | |
max_allowed_packet = 1024M |
@feanglou
Maybe you can design a new version for 2 CPU 4 RAM?
Google Cloud now elected cPanel and have optimized machines via cPanel recommendation:
https://docs.cpanel.net/installation-guide/system-requirements-centos/
they sell now KVM servers on very good machines, which is be very useful with
Engintron and a new my.cnf optimization for small machines can be very helpfull.
Updated with better defaults and new performance related additions for MySQL 8.
That's not actual RAM usage. See this https://serverfault.com/questions/238302/memory-usage-numbers-in-top-htop for additional info.
As for your config, 500 connections is probably too much (especially for 24 GBs of RAM). Use a tool like https://github.com/major/MySQLTuner-perl or https://github.com/BMDan/tuning-primer.sh/ to see how much InnoDB data you have so you can adjust your innodb_buffer_pool_size & innodb_buffer_pool_instances accordingly and how many connections you've ever had the most to adjust max_connections as well.
Config updated with new tool references for DB diagnostics, minor changes in defaults and additional details in open_files_limit.
500 connections is probably too much
Plz help, how to calculate max_connections ?
We have 2 server:
- server - db
- server - php + nginx. We have an avalanche load of about 10k users in 30 minutes. Nginx set max connection +- 100k
It's really all in the config. You just have to read through it carefully.
Use https://github.com/BMDan/tuning-primer.sh to see historical max connections and adjust accordingly. Pay attention to what is outputs. If you restarted MySQL the last hour, you'll see max connections for that timespan. So let MySQL run for a few days and then check how many connections are reported.
Ок
thank you
New version of the config released.
Binary logging is now disabled by default, "innodb_sort_buffer_size" has been bumped to 4M as a better default value, "default_authentication_plugin" is referenced (but commented by default - read the comments there), new performance related comments added in the buffers section.
@fevangelou
is very excessive...
I don't know if people buy machines for this kind of configuration...
before 5 years... i get it.
but, today nobody gonna buy machines 4CPU 8RAM... :/
@locksmithunit What do you mean ? Cloud is frequent. I have a lot of small instances that don't have a lot of rams. And I prefer to have a smaller count of core but stronger. If the configuration is not quite perfect for you, you can edit it, comments are here to read :)
@MaximeMichaud what is a small machine for you?
Do you think 2 CPUs can run this thing?
THIS CONFIGURATION WILL WORK ONLY IF YOU HAVE 4 CPUS AND 8 RAM.
BTW, JUST A REMINDER.
MySQL and MariaDB said CLEARLY:
The default configuration is the best and the faster configuration.
if you will see the config when you download one of them, you will see a lot of options uncomment.
For example:
cPanel in the TWICK offer optimization of max_packet 256
MySQL 5.7 offers the max_packet 256 uncomment after installation. ( and note the default is better and faster )
256 is wrong...
is slower.
When I start using the default configuration of MariaDB with a bit of adjustment from this file of @fevangelou
I GOT A MUCH BETTER AND FASTER RESULT, IN MUCH LESS CPU AND RAM.
A good optimized Website today or even APP running on MariaDB tables... not reach more than 30M - 40M DATABASE SPACE.
This excessive pool ( 4G ) together with :
max_allowed_packet = 256M
with extreme big buffers:
join_buffer_size = 4M # UPD
read_buffer_size = 3M # UPD
read_rnd_buffer_size = 4M # UPD
sort_buffer_size = 4M # UPD
IT IS INSANE!
Unneccery excessive configuration...
will cause more problems from benefits. ( especially on small machines UNDER 4 CPU )
I used this configuration a lot in the past.
When I moved to GCP on a 2X2 machine I found out it is useless... even make it worse.
and the last thing is not about "editing it" is about adjusting it to what you use.
I believe if you follow this file you have Engintron as well...
This guy knows better than us, what a 2x2 machine needs with his Nginx.
@locksmithunit I did not have any problem with this configuration. 2vcore & 2Go or more (max 32gb) with some adjustments. I'm running at least hundreds of hosts with this configuration. Excessive usage is probably true. Every website is unique.
I'm not using bloat like cpanel/plesk, everything is handmade. Custom compiled NGINX.
MySQL and MariaDB said CLEARLY:
The default configuration is the best and the faster configuration.****
SOURCE? i'M curious about what they "say" about it.
@MaximeMichaud "They" is the Manufacturer:
MySQL (Inside my.cnf config after a fresh installation)
and MariaDB as well.
and "handmade" is not what meant to be.
you not MariaDB
and not MySQL.
you in 2021, these people know the best their software.
they design it in this way, the default is the best! ( even Engintron and cPanel himself )
you not "OPTIMIZE" windows on your PC.
WHY?
because Microsoft does it better than you.
the same thing is MariaDB and MySQL.
CONFIGURATION NEEDS TO BE MINOR!
AND WE TALKING ON 2X2 NOT MORE.
btw,
please run mysqltuner.pl and give use your output with this configuration on a 2x2 machine, please...
and please don't give me that a minute after you restart your MySQL.
give me data after a day or 12 hours.
YOU WILL SEE - This configuration is not for regular machines or small ones.
YOU WASTING YOUR TIME AND RESOURCES ON YOUR SERVER.
OK, you should go out and breathe the air a little bit.
@MaximeMichaud i understand you got hurt...
I was got hurt too... i actually wrong with over-optimizing... ( not only MySql and Engintron )
I think is better if you bring us out-put of mysqltuner.pl and talk facts. ( Again, i don't think is reliable 100% )
but this... you misleading yourself + a lot of people actually not understand.
after all its advice for you, take it, try it.
or keep misleading yourself.
Good Luck.
@locksmithunit Speak for yourself you have no sources, no number. Everything is working like it should be on my side.
Like you said, don't mislead anyone, and keep this gist clean.
Good luck for yourself.
Question,
how long to mariadb to stabilize on the memory usage? can i want to know if there is a memory leak or normal increase.
also MySQLTuner and tuning-primer not saying same memory usage.
for example -
Mysql tuner -
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 11h 35m 11s (20M q [488.994 qps], 156K conn, TX: 377G, RX: 4G)
[--] Reads / Writes: 96% / 4%
[--] 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: 29.9G (23.83% of installed RAM)
[OK] Maximum possible memory usage: 67.1G (53.54% of installed RAM)
Tuner primer -
MEMORY USAGE
Max Memory Ever Allocated : 15.62 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 norms
i just restarted mariadb at night and here a sample of memory building up -
4105634 mysql 20 0 21.4g 4.1g 24576 S 4.7 3.3 0:08.90 mariadbd
4105634 mysql 20 0 21.4g 4.8g 25728 S 4.7 3.8 2:13.62 mariadbd
4105634 mysql 20 0 25.9g 8.7g 26480 S 15.0 6.9 56:52.91 mariadbd
4105634 mysql 20 0 26.2g 8.8g 26480 S 3.7 7.0 58:37.38 mariadbd
4105634 mysql 20 0 26.7g 9.3g 26480 S 10.6 7.4 66:38.53 mariadbd
here is my.cnf -
`
[mysqld]
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
##`
Server using cpanel, with mariadb 10.5
Dear @dandidan2, you must have confused me for cPanel support...
Hey @fevangelou thanks for the response,
i didnt confuse just wanted to ask, primer and mysql tuner saying diffrent memory usage so i do not know whos wrong or right :O
and just wonder how long(uptime for mariadb) takes to memory usage to be stabillize.
@MaximeMichaud
all this talking you didn't put your DATA from mysqltuner as i asked...
I sure have a reason for that.
No worries, if you use Engintron, now it's shipped with MySQLtuner,
be much easier for you to put the DATA here.
Good luck.
Hi, if I understand so I can download and replace with these or edit my config file and want to test 3 Days if all is working fine?
I doesn't must do anything if all works good, correct?
More or less, yes.
What would be the ideal configuration for a server with 2 processors of 32 cores (ie 64 cores) and 256 gb ram
Mysql 8
Not Work for me. The bank's performance got worse. I used the tools available in the links below to help me adjust the bank:
Hello, this has helped me a lot on a high-traffic installation of HumHub!
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=4GB
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=6GB
innodb_buffer_pool_instances=6
innodb_buffer_pool_chunk_size=512MB
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 512MB x 6 = 3GB?
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 this 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, and thank you for this primer!!
What is your Server/System configuration ? Much much ram and processor you are using. Could you please provide it ?
Note
innodb_log_file_size and innodb_log_files_in_group are deprecated in MySQL 8.0.30. These variables are superseded by innodb_redo_log_capacity. For more information, see Section 15.6.5, “Redo Log”.
They may be deprecated, but -according to the docs- they still work IF you don't specifically have a value set for innodb_redo_log_capacity.
Well I am on CentOS 8 and could not get this to work together with Galera. I tried it in /etc/my.cnf and /etc/my.cnf.d/
CentOS8 does not have an /etc/mysql/ directory unfortunately, we have the /etc/my.cnf or /etc/my.cnd.d/server.cnf, client.cnf etc.