Summary of MySQL System Configuration Parameter Optimization [in continuous update]

During my project, I have made some adjustments and Optimizations to MySQL system configuration parameters, such as connection timeout disconnection, SQL exceeding the buffer length limit, and memory table size adjustment, here we will sort out the summary one by one so that similar problems can be quickly solved in the future.

1. How can I solve the problem of MySQL connection timeout disconnection?

Solution:
Add or modify the following two variables in the [mysqld] section of the my. CNF file:

 
Wait_timeout =28800Interactive_timeout=28800
 

The default value is 8 hours (8*3600 = 28800 seconds), which can be changed to 24 hours or 30 days as needed.

2. How can I solve the problem of executing an SQL statement exceeding the maximum length of the buffer?

If the SQL statement is too large or the statement contains blob or longblob fields, the preceding problem may occur. For example, processing image data.
Solution:
Add or modify the following variables in the my. CNF file:

 
Max_allowed_packet = 10 m (set to the desired size)
 

The max_allowed_packet parameter is used to control the maximum length of its communication buffer.

3. How to modify the memory table size of MySQL?

1) Add the parameter-O max_heap_table_size = 32 m when starting MySql in the startup Option
2) modify my. CNF and add max_heap_table_size = 32 m to the [mysqld] segment.
3) modify it in the mysql client tool:

 mysql> set max_heap_table_size =  32777216  ; query OK,   0  rows affected ( 0.00   Sec) mysql  show variables like  '  % heap %   ' ;  + ------------------- + ---------- + | variable_name | value | + --------------------- + ---------- + | max_heap_table_size |  32777216  | + ------------------- + ---------- +  1  row  in  set ( 0.00  Sec)