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.

mySQL Optimization for VPS

Discussion in 'Database Discussions' started by eglwolf, Mar 8, 2011.

  1. eglwolf

    eglwolf Well-Known Member

    Joined:
    Jan 1, 2004
    Messages:
    168
    Likes Received:
    0
    Trophy Points:
    16
    I have the following type of VPS:
    CPU: 2500MHZ GUARANTEED
    Memory: 2.5GB GUARANTEED
    VPS Software: vePortal™ / OpenVZ™
    cPanel running CentOS 5.5

    I have a handful of sites that all run WordPress. One of my sites get 2-4K unique visitors a day. The load of the server spikes between 2-5. Burst (the Datacenter) suggested that it could be mySQL. I have included my current my.cnf file to see if anyone might have some suggestion or better settings to use with this type of vas server.

    Any help you all can offer is much appreciated.

    Code:
    [mysqld]
    max_connections=300
    key_buffer = 32M
    myisam_sort_buffer_size = 32M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 4000
    thread_cache_size = 286
    wait_timeout = 7000
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 12M
    query_cache_type = 1
    tmp_table_size =16M
    skip-innodb
    [mysqld_safe]
    open_files_limit = 8192
    [mysqldump]
    quick
    max_allowed_packet = 16M
    [isamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 16M
    write_buffer = 16M
    [mysqldhotcopy]
    interactive-timeout
    
    
     
  2. eglwolf

    eglwolf Well-Known Member

    Joined:
    Jan 1, 2004
    Messages:
    168
    Likes Received:
    0
    Trophy Points:
    16
  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
    I did want to mention a couple of points for anyone reading this thread. First of all, whenever asking for MySQL optimization assistance, please always state your MySQL version. MySQL 5.0 has different variables over MySQL 5.1 for the /etc/my.cnf file for some settings. If you are unsure of your MySQL version, you can obtain that information using the following command:

    Code:
    mysql_config --version
    For example, if you run MySQL 5.1, then table_cache should be changed to table_open_cache instead.

    Next, key_buffer, read_buffer and write_buffer are all incorrect variable names. It should be key_buffer_size, read_buffer_size and write_buffer_size for each, respectively.
     
Loading...

Share This Page