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.

tuning mysql

Discussion in 'Workarounds and Optimization' started by martin.henits, Aug 5, 2010.

  1. martin.henits

    martin.henits Member

    Joined:
    Aug 5, 2010
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    Hi all,

    I need to tune some settings related to mysql.
    things like connection timeout, etc.

    Can I change these things using cPanel or should I edit /etc.mycnf manually?

    cheers
     
  2. cPanelJared

    cPanelJared Technical Analyst
    Staff Member

    Joined:
    Feb 25, 2010
    Messages:
    1,842
    Likes Received:
    18
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Edit the file manually

    You will need to log into the shell as root and use an editor such as vi or nano to edit /etc/my.cnf. If you use nano, be sure to use the -w (or --nowrap) switch on the command line to prevent a serious word-wrapping issue:

    Code:
    # nano -w /etc/my.cnf
    I prefer to use vi, but use whichever editor you are comfortable with. There is no provision in the WebHost Manager or cPanel for editing /etc/my.cnf.

    Before making any changes, I strongly recommend that you back up the file, so that you can restore it in case the service does not restart or any other problem happens:

    Code:
    # cp /etc/my.cnf /root/
    You will need to restart the service in order for the changes to take effect. Use either the following command:

    Code:
    # /scripts/restartsrv_mysql
    or, in the WebHost Manager, click Main >> Restart Services >> SQL Server (MySQL).
     
  3. martin.henits

    martin.henits Member

    Joined:
    Aug 5, 2010
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    wrong file?

    Hi,

    I opened the /etc/my.cnf file
    its content was just:
    [mysqld]
    skip-innodb

    that's all!

    it seems that thet mysql installed by cPanel uses another config file
     
  4. cPanelJared

    cPanelJared Technical Analyst
    Staff Member

    Joined:
    Feb 25, 2010
    Messages:
    1,842
    Likes Received:
    18
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    That is the correct file

    The MySQL installation provided by cPanel uses /etc/my.cnf. The default my.cnf file is very basic and only contains a bare minimum of directives. If you need other directives, you will need to add them. This is the correct file to edit.
     
    #4 cPanelJared, Aug 5, 2010
    Last edited: Aug 5, 2010
  5. arunsv84

    arunsv84 Well-Known Member

    Joined:
    Oct 20, 2008
    Messages:
    373
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
  6. nilesh_kolte

    nilesh_kolte Well-Known Member

    Joined:
    Apr 13, 2006
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Pune ,India
    cPanel Access Level:
    Root Administrator
    Hello,

    Use the following Config for my.cnf.

    1. cp -ar /etc/my.cnf /root

    2. nano /etc/my.cnf

    3. Remove the whatever entry are there.

    4. Add the following.

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    max_user_connections=25
    max_connections=500
    interactive_timeout=10
    wait_timeout=10
    connect_timeout=10
    thread_cache_size=128
    key_buffer=16M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=1024
    record_buffer=1M
    sort_buffer_size=2M
    read_buffer_size=2M
    myisam_sort_buffer_size=64M
    old-passwords = 1

    [mysql.server]
    user=mysqlbasedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192

    [mysqldump]
    quickmax_allowed_packet=16M

    [mysql]
    no-auto-rehash


    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [mysqlhotcopy]
    interactive-timeout


    And restart mysql on server.
     
  7. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Don't use the code above. Every server and every environment is unique. You cannot just copy and paste someone else my.cnf

    use a tuning tool like mysqltuner

    Make sure your mysql server is running for at least 48 hours. Preferentially 1 week.

    Login as root and type

    Code:
    wget mysqltuner.pl
    perl mysqltuner.pl
    This will analyse your current usage and gives advices for the changes that need to be done on your server.

    Then edit my.cnf apply those changes and restart mysql.
     
  8. B12Org

    B12Org Well-Known Member

    Joined:
    Jul 15, 2003
    Messages:
    692
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Seattle Washington
    cPanel Access Level:
    Root Administrator
    Is that just a wrapper for the

    Code:
    #/sbin/service mysqld restart 
    command ?
     
  9. cPanelJared

    cPanelJared Technical Analyst
    Staff Member

    Joined:
    Feb 25, 2010
    Messages:
    1,842
    Likes Received:
    18
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    There is more to it than that. /scripts/restartsrv_mysql, as well as the other /scripts/restartsrv_* scripts, checks to see if a service is running. For example, you can check to see if MySQL is running, without restarting it, using the --check argument:

    Code:
    # /scripts/restartsrv_mysql --check
    #
    If there is no output, that means the service was found to be running. This is useful for checking to see whether chkservd will detect a service as up or down. The /scripts/restartsrv_* scripts are Perl scripts, so if you are familiar with Perl you can examine them to see more of the details about what they do.

    As a side note, "service" is itself a shortcut for "/etc/rc.d/init.d". I do not assume that it exists on all Red Hat-like Linx distributions. Instead, I prefer to use /etc/rc.d/init.d instead of "service" so that I do not count on an shortcut that may not necessarily be present. This is a personal preference, though, and one that I formed when I used older versions of Red Hat.
     
    #9 cPanelJared, Sep 10, 2010
    Last edited: Sep 10, 2010
Loading...

Share This Page