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:
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?
[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.