Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Optimized my.cnf configuration for MySQL/MariaDB (on Ubuntu, CentOS, Almalinux etc. servers)
# === 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
@TheFrisianClause
Copy link

TheFrisianClause commented Aug 22, 2021

Unfortunately this config does not work as of MariaDB 10.6, I tested it myself the service won't start. I think this is also due to that some files have been split up under /etc/my.cnf.d/ for some reason.

I am putting this file in /etc/my.cnf and I also have Galera configurations applied in /etc/my.cnf.d/server.cnf which results in errors when provisioning with Ansible.

@wazhanudin
Copy link

wazhanudin commented Aug 22, 2021

In my case, I had two server running with MariaDB 10.6.4, both of them had no problem with this configuration. The only thing I change is bind address to 0.0.0.0 (for remote access)

However, mine located at /etc/mysql/my.cnf

@MaximeMichaud
Copy link

MaximeMichaud commented Aug 22, 2021

This configuration work perfectly fine with MariaDB 10.6
I have been upgrading 31 (At this day) database servers completely independent that were using this configuration.
I had absolutely no problem.

@TheFrisianClause
Copy link

TheFrisianClause commented Aug 22, 2021

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.

@locksmithunit
Copy link

locksmithunit commented Aug 26, 2021

@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.

@fevangelou
Copy link
Author

fevangelou commented Nov 11, 2021

Updated with better defaults and new performance related additions for MySQL 8.

@null-it-vav
Copy link

null-it-vav commented Nov 25, 2021

Hi, my mariadb server use 8 CPU and 23 RAM
Database size +- 4gb use only innodb tables

I set innodb_buffer_pool_instances = 10
innodb_buffer_pool_size = 10G

max_connections = 500
Снимок экрана 2021-11-25 в 10 28 15

in htop i see the ram usage 15.6G + - this is normal?

@fevangelou
Copy link
Author

fevangelou commented Nov 25, 2021

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.

@fevangelou
Copy link
Author

fevangelou commented Nov 25, 2021

Config updated with new tool references for DB diagnostics, minor changes in defaults and additional details in open_files_limit.

@null-it-vav
Copy link

null-it-vav commented Nov 27, 2021

500 connections is probably too much
Plz help, how to calculate max_connections ?

We have 2 server:

  1. server - db
  2. server - php + nginx. We have an avalanche load of about 10k users in 30 minutes. Nginx set max connection +- 100k

@fevangelou
Copy link
Author

fevangelou commented Nov 27, 2021

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.

@null-it-vav
Copy link

null-it-vav commented Nov 27, 2021

Ок
thank you 👍

@fevangelou
Copy link
Author

fevangelou commented Dec 16, 2021

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.

@locksmithunit
Copy link

locksmithunit commented Dec 17, 2021

@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... :/

@MaximeMichaud
Copy link

MaximeMichaud commented Dec 18, 2021

@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 :)

@locksmithunit
Copy link

locksmithunit commented Dec 21, 2021

@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.

@MaximeMichaud
Copy link

MaximeMichaud commented Dec 22, 2021

@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.

@locksmithunit
Copy link

locksmithunit commented Dec 22, 2021

@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.

@MaximeMichaud
Copy link

MaximeMichaud commented Dec 22, 2021

OK, you should go out and breathe the air a little bit.

@locksmithunit
Copy link

locksmithunit commented Dec 22, 2021

@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.

@MaximeMichaud
Copy link

MaximeMichaud commented Dec 22, 2021

@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.

@dandidan2
Copy link

dandidan2 commented Jan 3, 2022

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

@fevangelou
Copy link
Author

fevangelou commented Jan 3, 2022

Dear @dandidan2, you must have confused me for cPanel support...

@dandidan2
Copy link

dandidan2 commented Jan 3, 2022

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.

@locksmithunit
Copy link

locksmithunit commented Jan 3, 2022

@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.

@JensGoro
Copy link

JensGoro commented Feb 10, 2022

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?

@fevangelou
Copy link
Author

fevangelou commented Feb 11, 2022

More or less, yes.

@DEXENG
Copy link

DEXENG commented Apr 2, 2022

What would be the ideal configuration for a server with 2 processors of 32 cores (ie 64 cores) and 256 gb ram

Mysql 8

@lucaslz
Copy link

lucaslz commented Apr 14, 2022

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:

https://github.com/major/MySQLTuner-perl

https://github.com/BMDan/tuning-primer.sh/

@petersphilo
Copy link

petersphilo commented Oct 20, 2022

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!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment