MySQL Settings

[andyb4u]

Hi,

We use an offloaded MySQL server. I noticed in the https://assets.nagios.com/downloads/nag ... Server.pdf document a section about recommended settings:

The following parameters are OPTIONAL however they are implemented as part of a default Nagios XI installation and hence it would be beneficial to also implement on the offloaded DB server:
innodb_file_per_table=1
query_cache_size=16M
query_cache_limit=4M
tmp_table_size=64M
max_heap_table_size=64M
key_buffer_size=32M
table_open_cache=32

When we oflloaded our MySQL a few years back these settings weren't mentioned in the documentation at time but I am hoping to implement them now.

I'm not a MySQL expert but I was able to check the settings we currently have set on our MySQL server:

innodb_file_per_table=0
query_cache_size=0
query_cache_limit=1M
tmp_table_size=16M
max_heap_table_size=16M
key_buffer_size =134M
table_open_cache=400

Most of the parameters I'd be increasing to the recommended settings but I just wanted to get any opinions on the last two key_buffer_size and table_open_cache. I'd be decreasing these to the recommended settings. I do not know what they mean and I just wanted to get some opinions if it would be safe for me change them to the recommended settings.

Any advice would be much appreciated.

[lmiltchev]

Our recommendations are not "set in stone". These are just "guidelines" per se. The values that you set for key_buffer_size and table_open_cache would depend on your specific environment, and available resources on the server.

I would not recommend decreasing these values before reading a bit more on the topic:

https://dev.mysql.com/doc/refman/8.0/en ... uffer_size

https://dev.mysql.com/doc/refman/5.6/en ... open_cache