1

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.

0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.