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.