-
-
Save fevangelou/fb72f36bbe333e059b66 to your computer and use it in GitHub Desktop.
# === 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 |
Ок
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.
Hello @fevangelou ,
I am quite fresh on Mysql optimization, and really expect your professional guide based on the following service info, mysqltuner, and the my.cnf in used:
8 GB Memory / 4 AMD vCPUs / 160 GB Disk /Ubuntu 22.04 (LTS) x64
mysqltuner
>> MySQLTuner 2.1.7
* Jean-Marie Renouard <jmrenouard@gmail.com>
* Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 10.6.14-MariaDB-1:10.6.14+maria~ubu2204-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file doesn't exist
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 2.9G (Tables: 502)
[--] Data in MEMORY tables: 0B (Tables: 1)
[--] Data in Aria tables: 32.0K (Tables: 1)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 3h 14m 54s (15M q [153.221 qps], 15K conn, TX: 224G, RX: 2G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory : 7.8G
[--] Max MySQL memory : 1.4G
[--] Other process memory: 0B
[--] Total buffers: 632.0M global + 7.6M per thread (100 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.4G (17.59% of installed RAM)
[OK] Maximum possible memory usage: 1.4G (17.49% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (82/15M)
[!!] Highest connection usage: 100% (101/100)
[OK] Aborted connections: 0.52% (80/15307)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 15633
[OK] Temporary tables created on disk: 7% (169K on disk / 2M total)
[OK] Thread cache hit rate: 98% (206 created / 15K connections)
[OK] Table cache hit rate: 99% (19M hits / 19M requests)
[!!] table_definition_cache (400) is less than number of tables (795)
[OK] Open file limit used: 0% (60/500K)
[OK] Table locks acquired immediately: 100% (334 immediate / 334 locks)
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] No MyISAM table(s) detected ....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 384.0M / 2.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375%): 36.0M * 1 / 384.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 3 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.91% (2735358498 hits / 2737839948 total)
[!!] InnoDB Write Log efficiency: 61.14% (50001 hits / 81784 total)
[OK] InnoDB log waits: 0.00% (0 waits / 131785 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/352.0K
[!!] Aria pagecache hit rate: 93.9% (2M cached / 159K reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=OFF
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
max_connections (> 100)
wait_timeout (< 60)
interactive_timeout (< 28800)
skip-name-resolve=OFF
join_buffer_size (> 256.0K, or always use indexes with JOINs)
table_definition_cache (400) > 795 or -1 (autosizing if supported)
performance_schema=ON
innodb_buffer_pool_size (>= 2.9G) if possible.
innodb_log_file_size should be (=96M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
my.cnf
# MariaDB database server configuration file.
# Optimized by WordOps 3.16.3
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# 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.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = localhost
#
# * Fine Tuning
#
max_connections = 100
connect_timeout = 5
wait_timeout = 60
max_allowed_packet = 64M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M
max_heap_table_size = 32M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size = 16M
open-files-limit = 500000
table_open_cache = 16000
myisam_sort_buffer_size = 128M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit = 128K
query_cache_size = 0
# for more write intensive setups, set to DEMAND or OFF
query_cache_type = 0
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings = 2
#
# Enable the slow query log to see queries with especially long duration
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit = 1000
log_slow_verbosity = query_plan
#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#report_host = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
#log_bin = /var/log/mysql/mariadb-bin
#log_bin_index = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog = 1
#expire_logs_days = 10
#max_binlog_size = 100M
transaction_isolation = READ-COMMITTED
binlog_format = ROW
# slaves
#relay_log = /var/log/mysql/relay-bin
#relay_log_index = /var/log/mysql/relay-bin.index
#relay_log_info_file = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine = InnoDB
# you can't just change log file size, requires special procedure
innodb_log_file_size = 36M
innodb_buffer_pool_size = 290M
innodb_log_buffer_size = 72M
innodb_file_per_table = 1
innodb_open_files = 500000
innodb_io_capacity = 500000
innodb_flush_method = O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
[mysqldump]
quick
quote-names
max_allowed_packet = 64M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completion
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
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
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.