MySQL Tuning

[hmaddy]

On mysql Tuning these are the recommendations.

Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 24M)
max_heap_table_size (> 24M)
key_buffer_size (~ 1M)
innodb_log_file_size should be (=64M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.


How to change all these values on server.

existing my.cnf details are

[mysqld]
performance-schema = 0
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_buffer_pool_size = 500217728
max_allowed_packet = 268435456
open_files_limit = 40000
innodb_file_per_table = 1

skip-name-resolve=0
join_buffer_size=300M
tmp_table_size=24M
max_heap_table_size=24M
table_definition_cache=4000
performance_schema=ON
key_buffer_size=2M

 

[Zhenyapan]

MySQL's maximum memory usage is dangerously high - it's due to max_allowed_packet 256M multiplied by max_connections
but I think situation when all connections will simultaneously use this limit not possible.
tmp_table_size and max_heap_table_size - depends on your average table size and amount of RAM you can spend for it.

 

[BillyS]

The default for join_buffer_size is 256K, yours is set to 300M. Unless you have a special case for setting it that high. See here:

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

dev.mysql.com

Unless a Block Nested-Loop or Batched Key Access algorithm is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change the session setting to a larger value only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.