Skip to content

Instantly share code, notes, and snippets.

Last active June 7, 2023 15:19
Star You must be signed in to star a gist
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 (
# ~ 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:
# or
# 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.
# 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:
# localhost localhost.localdomain localhost4 localhost4.localdomain4
# ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
# 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 ~
port = 3306
socket = /var/run/mysqld/mysqld.sock
# === Required Settings ===
basedir = /usr
bind_address = # Change to 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/
port = 3306
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)
# 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:
# 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:
# === 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:
# and for MariaDB check:
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
# Variable reference
# For MySQL 5.7+:
# For MariaDB:
max_allowed_packet = 1024M
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.

Copy link

Maybe you can design a new version for 2 CPU 4 RAM?
Google Cloud now elected cPanel and have optimized machines via cPanel recommendation:

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.

Copy link

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

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?

Copy link

That's not actual RAM usage. See this for additional info.

As for your config, 500 connections is probably too much (especially for 24 GBs of RAM). Use a tool like or 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.

Copy link

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

Copy link

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

Copy link

It's really all in the config. You just have to read through it carefully.

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

Copy link

thank you 👍

Copy link

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.

Copy link

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

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

Copy link

locksmithunit commented Dec 21, 2021

@MaximeMichaud what is a small machine for you?
Do you think 2 CPUs can run this thing?


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

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

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.

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.

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.
because Microsoft does it better than you.
the same thing is MariaDB and MySQL.


please run 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.

Copy link

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

Copy link

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

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.

Copy link

dandidan2 commented Jan 3, 2022

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



Server using cpanel, with mariadb 10.5

Copy link

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

Copy link

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.

Copy link

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.

Copy link

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?

Copy link

More or less, yes.

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

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:

Copy link

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:

but i havent yet set innodb_buffer_pool_chunk_size..
so, i was thinking of doing the following:

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

Copy link

What is your Server/System configuration ? Much much ram and processor you are using. Could you please provide it ?

Copy link

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

Copy link

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.

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