Optimized my.cnf configuration for MySQL/MariaDB (on Ubuntu, CentOS, Almalinux etc. servers)

my.cnf

# === Optimized my.cnf configuration for MySQL/MariaDB (on Ubuntu, CentOS, Almalinux etc. servers) ===
#
# by Fotis Evangelou, developer of Engintron (engintron.com)
#
# ~ Updated September 2024 ~
#
#
# 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:
#
#    $ chown -R mysql:mysql /var/lib/mysql
#    $ service mysql restart
#
#    or use the shorthand command:
#    $ 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
# For maximum compatibility, just use:
#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                 = 16M   # UPD

low_priority_updates            = 1
concurrent_insert               = 2

# === Connection Settings ===
max_connections                 = 90    # 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          = 50000 # UPD
table_open_cache                = 50000 # 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, 512M or 1G 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_error_verbosity            = 1     # 1 for ERROR, 2 for ERROR, WARNING, 3 for ERROR, WARNING, INFORMATION (MySQL only)
                                        # Set to 1 to prevent flooding your mysql_error.log to GBs with deprecation warnings
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

I try since weeks, but can't get cpu down.

my.cnf

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = ::ffff:127.0.0.1
local-infile=0

performance_schema	= ON

wait_timeout = 31536000
max_allowed_packet=500M
max_connect_errors = 100000

table-definition-cache=10000
table_open_cache=1000

open_files_limit = 65536

max_heap_table_size             = 512M  # Increase to 256M or 512M if you have lots of temporary tables because of missing indices in JOINs
tmp_table_size                  = 512M

max_write_lock_count=16
join_buffer_size				= 256K
read_buffer_size                = 128K    # UPD
read_rnd_buffer_size            = 2M    # UPD
sort_buffer_size                = 2M    # UPD

low_priority_updates=1
concurrent_insert=ALWAYS

query_cache_type		= 0
query_cache_size		= 0
#query_cache_limit		= OFF
# for more write intensive setups, set to DEMAND or OFF

key_buffer_size = 50M
max_connections=60
thread_cache_size=100
thread_stack = 192K
log_warnings=2


innodb_thread_concurrency=0
innodb_buffer_pool_size=5G
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT

innodb_log_buffer_size=16M
innodb_log_file_size=1G
innodb_io_capacity=10000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_change_buffer_max_size=10
innodb_sort_buffer_size=4M

wait_timeout=80


log_error                       = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes   = ON     # Disabled on production
long_query_time                 = 1
slow_query_log                  = 1     # Disabled on production
log_queries_not_using_indexes	= ON
slow_query_log_file             = /var/lib/mysql/mysql_slow.log
log_slow_verbosity	= query_plan

For me I had to comment these lines out:

#pid_file                        = /var/run/mysqld/mysqld.pid
#socket                          = /var/run/mysqld/mysqld.sock

Then it started normally and I was able to access through phpmyadmin :)

Also for anyone using AlmaLinux, the my.cnf is located in:
etc/my.cnf

And if you're using MariaDB, I recommend re-adding this:

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

I'm running Mariadb 11.4.6 on Ubuntu (LEMP) Server 24.04, running on a Raspberry Pi 5 with 16GB of Ram. It boots from an M.2 NVMe 2280 Drive attached via a PCI-to-NVMe adapter.

The Pi's system specs are as follows:

2.4GHz quad-core 64-bit Arm Cortex-A76 CPU
512KB per-core L2 caches, 2MB L3 cache
VideoCore VII GPU
4Kp60 HEVC decode
LPDDR4X-4267 SDRAM
16GB RAM

The system software specs are as follows:

OS: Ubuntu 24.04.2 LTS
Web Server: Nginx v1.27.5 Mainline
         -- Nginx Modules: ngx_http_modsecurity_module.so, ngx_http_brotli_filter_module.so, ngx_http_brotli_static_module.so, 
PHP: v8.3.6
WordPress: v6.7.2
Mariadb: v11.4.6
Redis-server: v8.0.0
FastCGI Cacheing Enabled

I am running 3 Wordpress Websites off of the server. Each site is low traffic, getting about 80-120 hits per day. I have a page optimizing/minifying plugin working on each site, and FastCGI Caching is turned on for all three sites.

When it comes to anything related to Databases, including MariaDb Server, I am utterly clueless, and have no idea whatsoever how to optimize Maraidb. I found your post because when running sudo systemctl status mariadb.service I am getting the following warning:

mysqld[168550]: 2025-05-13 12:44:38 0 [Warning] Could not increase number of max_open_files to more than 32768 (request: 66071)

and searching for that warning lead me to your post.

I have figured out that I can modify the LimitNOFILE and LimitMEMLOCK parameters in the /usr/lib/systemd/system/mariadb.service file, to higher numbers in order to get rid of that warning. However, this warning raised a much bigger issue: My Mariadb Server is not properly optimized.

The obvious question that instantly comes to mind from this warning/issue, is "Does my server RAM Size have any correlation to what the LimitNOFILE and LimitMEMLOCK paramaters in /usr/lib/systemd/system/mariadb.service should be?

Then, the open ended, more general question is:
What settings (based off of the above system specs and software specs) would you recommend to fully Optimize Mariadb on my Raspberry Pi Server? Is there anything that you can recommend that I should be changing according to my system, in order to fully optimize MaraDb?

If you (or anybody out there in github land) have any example configs, hints, and/or relevant info on this, I would highly appreciate your knowledge and expertise. Thanks so much!