0

I have server with 20cpu, 40 core th, 96GB ram, 2x3.8TB Nvme disk. This server for shared hosting, and about 1000 account hosted.

Current my configuration on my.cnf

[client-server] !includedir /etc/my.cnf.d

[mysqld]
max_connections=150
log-error = /var/lib/mysql/myerrnet.err
performance-schema = 0
default-storage-engine = MyISAM
innodb_file_per_table = 1
query_cache_size =0
query_cache_type =0
max_allowed_packet=268435456
max_heap_table_size=20M
tmp_table_size =20M
open_files_limit=50000
local-infile=0
wait_timeout = 50
interactive_timeout=50
connect_timeout = 50
#max_allowed_packet = 8M
max_connect_errors = 50
join_buffer_size=256K

####Disable Strick Mode ####
sql_mode=NO_ENGINE_SUBSTITUTION


[mysqldump]
quick
max_allowed_packet = 512M

And after run mysqltunner i got this:

Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 20M)
    max_heap_table_size (> 20M)
    table_open_cache (> 2000)
    table_definition_cache(400) > 91182 or -1 (autosizing if supported)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 19.2G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

What best configuration should i put on my.cnf ?

0

default-storage-engine = MyISAM

Better to be in InnoDB.

You currently have 19GB of InnoDB data, but no setting for innodb_buffer_pool_size? That VARIABLE should be perhaps 70G for that server.

To dig deeper, let's get a more thorough analysis of variables, plus look for slow queries. See http://mysql.rjweb.org/doc.php/mysql_analysis

|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.