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 Errors & High Load - Help Request

Discussion in 'Database Discussions' started by Jennifer2010, Jan 28, 2013.

  1. Jennifer2010

    Jennifer2010 Registered

    Joined:
    Jun 26, 2012
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi Everyone,

    My load in WHM seems to spike up between 5.00 - 35.00 lately and I see this as one of the top processes:

    In WHM's "Process Manager" it shows the process at 20.7 CPU% and 5.1% Memory Percentage (both the highest CPU % and memory %)

    I'm pasting the output of some commands below. Any help is GREATLY appreciated!

    - Jennifer

    Code:
    [B]mysql_config --version[/B]
    5.1.66[/QUOTE]
    
    [B]cat /etc/my.cnf:[/B]
    [QUOTE][mysqld]
    innodb_file_per_table=1
    
    [mysqld]
    #datadir=/var/lib/mysql
    #socket=/var/lib/mysql/mysql.sock
    skip-locking
    #skip-innodb
    query_cache_limit=4M
    query_cache_size=128M
    query_cache_type=1
    max_user_connections=500
    max_connections=1000
    interactive_timeout=28000
    wait_timeout=28800
    connect_timeout=28800
    thread_cache_size=256
    key_buffer=512M
    join_buffer=16M
    max_allowed_packet=32M
    table_cache=5096
    record_buffer=1M
    sort_buffer_size=2M
    read_buffer_size=2M
    max_connect_errors=10
    thread_concurrency=8
    myisam_sort_buffer_size=64M
    server-id=1
    table_open_cache=5096
    local-infile=0
    join_buffer_size=8M
    
    
    #[mysql.server]
    #user=mysql
    #basedir=/var/lib
    
    #[safe_mysqld]
    #err-log=/var/log/mysqld.log
    #pid-file=/var/lib/mysql/mysql.pid
    #open_files_limit=8192
    
    #[mysqldump]
    #quick
    #max_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
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl:

    Code:
     >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  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.66-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 802)
    [--] Data in InnoDB tables: 9M (Tables: 15)
    [--] Data in MEMORY tables: 2M (Tables: 11)
    [!!] Total fragmented tables: 16
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 17h 37m 11s (104M q [697.256 qps], 5M conn, TX: 2501B, RX: 19B)
    [--] Reads / Writes: 82% / 18%
    [--] Total buffers: 666.0M global + 12.5M per thread (1000 max threads)
    [OK] Maximum possible memory usage: 12.9G (82% of installed RAM)
    [OK] Slow queries: 0% (38/104M)
    [OK] Highest usage of available connections: 33% (338/1000)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/2.0G
    [OK] Key buffer hit rate: 99.5% (1B cached / 6M reads)
    [OK] Query cache efficiency: 57.4% (39M cached / 69M selects)
    [!!] Query cache prunes per day: 3256506
    [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 2M sorts)
    [!!] Joins performed without indexes: 13028
    [OK] Temporary tables created on disk: 13% (71K on disk / 546K total)
    [OK] Thread cache hit rate: 99% (470 created / 5M connections)
    [!!] Table cache hit rate: 8% (664 open / 8K opened)
    [OK] Open file limit used: 8% (982/11K)
    [OK] Table locks acquired immediately: 99% (57M immediate / 58M locks)
    [!!] InnoDB data size / buffer pool: 9.7M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 128M)
        join_buffer_size (> 8.0M, or always use indexes with joins)
        table_cache (> 5096)
        innodb_buffer_pool_size (>= 9M)
     
  2. Greenhost

    Greenhost Well-Known Member

    Joined:
    Jan 22, 2013
    Messages:
    92
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    You should install mytop, that will help you to monitor an analytic your data base, what query is run, who is connected to your DB and etc.
    Here the mytop documentation .

    I recomend you to set "wait_timeout" and "max_connections" value as lower as possible. And set "key_buffer" value high, but Don't set it higher than the sum of all of your MYI files and your RAM.
     
  3. Jennifer2010

    Jennifer2010 Registered

    Joined:
    Jun 26, 2012
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thank you for the response!

    I tried installing MyTop and got this error:


    Code:
    Setting up Install Process
    Resolving Dependencies
    --> Running transaction check
    ---> Package mytop.noarch 0:1.4-2.el5.rf set to be updated
    --> Processing Dependency: perl(Term::ReadKey) for package: mytop
    --> Processing Dependency: perl(DBI) for package: mytop
    --> Finished Dependency Resolution
    mytop-1.4-2.el5.rf.noarch from rpmforge has depsolving problems
      --> Missing Dependency: perl(DBI) is needed by package mytop-1.4-2.el5.rf.noar                              ch (rpmforge)
    mytop-1.4-2.el5.rf.noarch from rpmforge has depsolving problems
      --> Missing Dependency: perl(Term::ReadKey) is needed by package mytop-1.4-2.e                              l5.rf.noarch (rpmforge)
    Error: Missing Dependency: perl(DBI) is needed by package mytop-1.4-2.el5.rf.noa                              rch (rpmforge)
    Error: Missing Dependency: perl(Term::ReadKey) is needed by package mytop-1.4-2.                              el5.rf.noarch (rpmforge)
     You could try using --skip-broken to work around the problem
     You could try running: package-cleanup --problems
                            package-cleanup --dupes
                            rpm -Va --nofiles --nodigest
    The program package-cleanup is found in the yum-utils package.
    Any ideas?
     
  4. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You seem to be missing these two perl modules as mytop dependencies:

    DBI
    Term::ReadKey
     
    #4 Archmactrix, Jan 28, 2013
    Last edited: Jan 28, 2013
Loading...

Share This Page