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 suggestions please

Discussion in 'Workarounds and Optimization' started by pes, Nov 12, 2012.

  1. pes

    pes Registered

    Joined:
    Sep 5, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello, I'm receiving an error from tailwatchd in regards to eximstats

    "The chkservd sub-process with pid 28164 ran for 301 seconds. This sub-process was terminated when it exceeded the time allowed between checks, which is 300 seconds. To determin why, you can check /var/log/chkservd.log and /usr/local/cpanel/logs/tailwatchd_log"

    And see this is a somewhat common problem with sql databases not being optimized. I'd like to optimize my db but I'm not sure of my settings.

    I run
    mysqltuner/mysqltuner.plom [/usr/local/cpanel/logs]# /usr/local/cpanel/3rdparty/

    >> MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.65-cll
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 13M (Tables: 44)
    [!!] InnoDB is enabled but isn't being used
    [!!] Total fragmented tables: 5

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 41d 22h 38m 34s (12M q [3.352 qps], 182K conn, TX: 75B, RX: 1B)
    [--] Reads / Writes: 97% / 3%
    [--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 449.2M (14% of installed RAM)
    [OK] Slow queries: 0% (1K/12M)
    [OK] Highest usage of available connections: 76% (115/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/1.2M
    [OK] Key buffer hit rate: 99.9% (178M cached / 196K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (19 temp sorts / 672K sorts)
    [!!] Temporary tables created on disk: 44% (295K on disk / 658K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 42K opened)
    [OK] Open file limit used: 9% (99/1K)
    [OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)

    And my root.cnf is
    root@bass [/usr/local/cpanel/logs]# less /etc/my.cnf

    [mysqld]
    innodb_file_per_table=1
    local-infile=0
    /etc/my.cnf (END)



    Can someone please help me with the settings in /etc/my.cnf ? I think I should edit it to be:
    query_cache_size = 8M
    tmp_table_size = 16M
    max_heap_table_size =16M
    thread_cache_size = 4
    table_cache = 64


    I've also edited my hard/soft limits in the past in /etc/security/limits.conf to
    * root hard nofile 65536
    * root soft nofile 65536

    as I've had fork shell errors. (I'm not sure if this is related).

    I'm running a VPS with 4GB ram CENTOS 6.3 x86_64 vmware – bass WHM 11.34.0 (build 9) Load Averages: 0.12 0.11 0.04

    Thanks for your help!

    cheers,
    Scott
     
  2. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    Scott,
    Here is my setup and it has been working for rather nicely so far.

    Code:
    [mysqld]
    set-variable = max_connections=500
    safe-show-database
    query_cache_size=128M
    tmp_table_size=32M
    max_heap_table=32M
    thread_cache_size=4
    table_open_cache=10K
    max_allowed_packet=32M
    local-infile=0
    long_query_time = 4
    slow_query_log
    key_buffer_size=48M
    read_buffer_size=128K
    join_buffer_size=128K
    I would recommend watching this video:
    MySQL OptimizationcPanel Videos | cPanel Videos

    They make good recommendations and give you knowledge on what each of the variables do.
     
  3. sahostking

    sahostking Well-Known Member

    Joined:
    May 15, 2012
    Messages:
    299
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Cape Town, South Africa
    cPanel Access Level:
    Root Administrator
    I completely forgot about mysqltuner. Thanks for this and the links to docs. Will be using these.
     
  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
    One thing I would like to mention is that running mysqltuner.pl is not necessarily a one-step process. You need to run it, make the suggested changes, restart MySQL, then wait at least a day or two, and then run mysqltuner.pl again. Gradually, this process will shape and adjust your MySQL configuration to fit your needs. It can be a slow, tedious process, but that is true of many aspects of server administration.

    mysqltuner.pl is third party, so it is not our product, but we provide a copy of it as a courtesy at /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl .
     
Loading...

Share This Page