I know some memory
/ cache
/ open file
related params should be adjusted accordingly.
But after searching for quite a while, didn't found a comprehensive guide.
Say, mysql
8 is in use, running on Linux
, and trying to change max_connections
to 400, the database is using innodb
engine, could you give a list of other parameters, and their new values to adjust to, in common.
Best Answer
The main setting is
innodb_buffer_pool_size
. It should be set to about 70% of available RAM if you have more than 4GB of RAM.If you need more than the default 151 for
max_connections
it is worth looking into whether the client(s) are allowing too many connections. It is better to reduce the load from that size.A common problem is
open_files_limit
. It comes from the OS'sulimit
may be at a painfully low 1024. That should be fixed in the OS.There are no other "likely" settings to check on, especially with MySQL 8.0.
There are
STATUS
values that can help tuning busy systems. See this for guidance in that help: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning