help : optimize MySQL

jlucho

• November 04, 2020 10:24

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 Jurassic Moderator

  • November 06, 2020 13:01

  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:

  0
• 

  jlucho

  • November 07, 2020 17:13

  What variables control these 2 alert messages? *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables ***

  0
• 

  cPRex Jurassic Moderator

  • November 09, 2020 12:53

  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.

  0

Please sign in to leave a comment.