updating my.cnf

[netentropy]

Is there a local value for my.cnf or do we just update /etc/my.cnf

Can this be done per domain or is it server wide on a VPS?

And finally, is there a default my.cnf in case we do not like adjustments?

[k-planethost]

it is a for the server and not per domain
default no there is not you have to spend some time runing myscl tuner to see what is suggest

[cPanelTristan]

There is a default for /etc/my.cnf that contains the following if you don't already have one set on the machine:

[mysqld]
set-variable = max_connections=500
safe-show-database

These are taken from the WHM default copy at /usr/local/cpanel/whostmgr/my.cnf during cPanel server installation. If you have an existing /etc/my.cnf at the time of installation, then the /etc/my.cnf won't be overwritten with the above settings. It only uses those if the /etc/my.cnf didn't exist.

Now, safe-show-database is deprecated as it is now the default anyway so that isn't needed, and the modern way to list max_connections = 500 doesn't have the variable setting listed, so you should change those baseline values to this instead:

[mysqld]
max_connections=500

You may want to add additional items into the /etc/my.cnf and I'd recommend running a script called mysqltuner.pl for recommendations on what to change. It can be run by issuing the following in root SSH on the machine:

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

Please note that you would want to wait 24 hours since the last MySQL restart before running this script. Any changes made to /etc/my.cnf file do require a MySQL restart, so please be aware of that being the case.

Finally, I have a presentation on MySQL optimization that I made at the cPanel conference October of last year which might be of use:

MySQL Optimization | cPanel Video Site

[netentropy]

But aren't there default values for things like

table_cache

record_buffer

sort_buffer_size

[cPanelTristan]

Yes, there are always default values for all variables that exist on the system. You can see the current default value for a variable by running:

mysqladmin var | grep variablename

For example, for sort_buffer_size, you'd do:

mysqladmin var | grep sort_buffer_size

The return for that will be if you haven't changed the default:

# mysqladmin var | grep sort_buffer_size
| myisam_sort_buffer_size       | 8388608 |
| sort_buffer_size              | 2097144 |

The fact these all already have defaults is why I try to hit several times in my presentation that you only need variables in /etc/my.cnf that are going to be changed from the default first of all. Next, you shouldn't change a variable from the default unless you need to do so. The goal isn't to put in as many variables as you possibly can into /etc/my.cnf but to change only those that will improve performance in your set environment.

[netentropy]

I get this and thank you!

To clarify two points:

1) It seems my host has defaults already overwritten because my my.cnf is well populated with changes. Is this Cpanel normal behavior or something my host has pre-configured somehow?

2) In this case of #1 , would you suggest (after backup) putting a blank my.cnf and the writing only what needs to be changed? Also is a blank my.cnf ok?

[cPanelTristan]

1) If you have values other than those I noted as the defaults, then your host changed the values from the cPanel default ones.

2) Without seeing what you already have in /etc/my.cnf file, it's difficult to say you should overwrite the existing copy. If you wanted to provide what is in the file right now, then I'd be happy to provide an answer to that question.