MySQL my.cnf performance tuning recommendations

Question

Most of the day, the performance of the server is good, but once per day it stops responding totally even I can't access via SSH, and I have to restart it to make it up again.

I have a dedicated server with the following configuration:

Server Information

16 Intel(R) Core(TM) i9-9900K CPU @ 3.60GHz
64GB Ram
Linux Centos7 / cPanel/WHM + MySQL

MySQLTuner results and recommendations

Reduce your overall MySQL memory footprint for system stability

Temporary table size is already large - reduce result set size

Reduce your SELECT DISTINCT queries without LIMIT clauses

How can I implement these improvements?

[!!] Maximum possible memory usage: 56.9G (90.76% of installed RAM)

Why the maximum possible memory was increased in that way? Is there a value that needs to be adjusted?

my.cnf file:

[mysqld]
performance_schema = ON
local-infile=0
skip-name-resolve

join_buffer_size=2M
read_buffer_size=2M
tmp_table_size=1G
max_heap_table_size=1G
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

innodb_buffer_pool_size=3800M
innodb_log_file_size=512M
innodb_buffer_pool_instances=4
innodb_flush_log_at_trx_commit = 2
innodb_flush_method= O_DIRECT
max_allowed_packet=100M
open_files_limit=40000
default-storage-engine=InnoDB
#innodb_file_per_table=1
#innodb-thread-concurrency=8
#innodb_lock_wait_timeout = 100
max_connections=200
query_cache_type=0
query_cache_size=0
query_cache_limit=1G
# thread_concurrency= 32 
thread_cache_size= 8
key_buffer_size=256M
#Slow Query Log
slow_query_log = ON
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2
table_open_cache=3000
[mysqldump]
max_allowed_packet=4G

How could I improve or fix this configuration?

Any suggestions? Thanks folks.

Answer 1

Your innodb_buffer_pool_size is almost absurdly small for a server of that size.

innodb_flush_log_at_trx_commit=2 is dangerous.

innodb_file_per_table=1 you definitely don't want commended out. Rebuild any tables that aren't in separate tablespaces.

query_cache_limit=1G makes no sense with query cache disabled.

Various buffer sizes other than innodb_buffer_pool_size - you should probably leave them at default settings. Comment them out. Especially join_buffer_size and sort_buffer_size.

Do you use MyISAM? If not, reduce key_buffer_size down to something tiny (enough to cover the size of all .MYI files in your database.