The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

updating my.cnf

Discussion in 'Workarounds and Optimization' started by netentropy, Apr 3, 2011.

  1. netentropy

    netentropy Member

    Joined:
    Mar 16, 2011
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    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?
     
  2. k-planethost

    k-planethost Well-Known Member

    Joined:
    Sep 22, 2009
    Messages:
    199
    Likes Received:
    4
    Trophy Points:
    18
    Location:
    Athens Greece
    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
     
  3. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    There is a default for /etc/my.cnf that contains the following if you don't already have one set on the machine:

    Code:
    [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:

    Code:
    [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:

    Code:
    /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
     
  4. netentropy

    netentropy Member

    Joined:
    Mar 16, 2011
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    But aren't there default values for things like

    table_cache

    record_buffer

    sort_buffer_size
     
  5. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    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:

    Code:
    mysqladmin var | grep variablename
    For example, for sort_buffer_size, you'd do:

    Code:
    mysqladmin var | grep sort_buffer_size
    The return for that will be if you haven't changed the default:

    Code:
    # 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.
     
  6. netentropy

    netentropy Member

    Joined:
    Mar 16, 2011
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    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?
     
  7. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    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.
     
Loading...

Share This Page