help : optimize MySQL

[jlucho]

Hello guys

I have a server : dual xeon 2430 + 64Gb ram + HDD disk, cPanel and MySQL 5.7.32


I have followed the MySQLtuner recommendations and I have had the following alert :

Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 64M)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
innodb_buffer_pool_size (>= 472.7M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

My my.cnf configuration is :

[mysqld]
performance-schema=0
log-error=/var/lib/mysql/myhostname.err
performance-schema=ON
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine = MyISAM
local-infile=0
myisam_sort_buffer_size = 64M

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#MAX
max_allowed_packet = 268435456
max_heap_table_size = 256M
max_connections = 600
max_user_connections=1000

#TABLE
table_open_cache = 9000
table_definition_cache=12000
#table_cache=1024

#READ
read_buffer_size = 64M
read_rnd_buffer_size = 64M

#QUERY
query_cache_size = 64M
query_cache_type = 1
query_cache_limit = 3M


open_files_limit=10000
tmp_table_size = 256M
thread_cache_size=640
key_buffer_size=512M
thread_cache_size = 384
#thread_concurrency = 4
wait_timeout = 20
connect_timeout = 10

[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M

#### Per connection configuration ####
sort_buffer_size = 1M
join_buffer_size = 1M
thread_stack = 192K

[isamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M

[mysqld_safe]

[mysqldump]
quick
max_allowed_packet = 16M

#innodb stuff
innodb_file_per_table = 1
innodb_buffer_pool_size = 134217728
innodb_buffer_pool_size=3G
innodb_log_file_size=512M
innodb_file_per_table=1
innodb_buffer_pool_instances=3

#join_buffer=1M
#innodb_buffer_pool_instances = 8
#innodb_buffer_pool_size = 8G
#innodb_log_file_size = 1G
#innodb_file_per_table = 1
#interactive_timeout = 100

Please, I would like to ask you for any suggestions to improve the performance of mysql, what values should I modify?

 

[cPRex]

Based on that information alone, it's not possible to provide details on how to optimize the system. I have found MySQLTuner to provide reliable information if the MySQL service has been up and running for a while before it is run.

If you are experiencing slowness it would be good to identify where that is coming from before trying to make changes. For example, are you seeing slow queries when running the "mysqladmin proc status" command on the system? Are the sites slower than you expect?

The best answer to this question is that there isn't going to be a simple copy and paste solution that will optimize your sites well, as that work should be handled by someone that is familiar with your sites and server settings. It may be a good idea to review the list of MySQL configuration variables at the link below to see what options are available to see what you can control on the system:

MySQL :: MySQL 5.7 Reference Manual :: 5.1.7 Server System Variables

dev.mysql.com

 

[jlucho]

What variables control these 2 alert messages?

*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***

 

[cPRex]

That is most likely related to the "key_buffer" value in /etc/my.cnf.

Do you know how long MySQL was up and running before you ran the tuner script? It should be at least 24 hours in order for that to pull accurate data.