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.

Too high cpu loads from MySQL logging

Discussion in 'Workarounds and Optimization' started by kalehosting, Oct 7, 2012.

  1. kalehosting

    kalehosting Registered

    Joined:
    Apr 17, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Pid Owner Priority CPU % Memory % Command
    18993 (Trace) (Kill) mysql 0 61.0 4.0 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/vps.xxx.net.err --open-files-limit=5000 --pid-file=/var/lib/mysql/vps.xxxx.net.pid


    how is resolv this?

    MYSQLTUNER.PL is that:

    PHP:
    root@vps [/var/lib/mysql]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

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

    -------- General Statistics --------------------------------------------------
    [--] 
    Skipped version check for MySQLTuner script
    [OKCurrently running supported MySQL version 5.1.65-cll
    [OKOperating on 32-bit architecture with less than 2GB RAM

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables22M (Tables349)
    [--] 
    Data in InnoDB tables3M (Tables107)
    [--] 
    Data in MEMORY tables0B (Tables2)
    [!!] 
    Total fragmented tables17

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 3m 57s (21K q [88.937 qps], 1K connTX70MRX1M)
    [--] 
    Reads Writes80% / 20%
    [--] 
    Total buffers566.0M global + 2.7M per thread (500 max threads)
    [!!] 
    Maximum possible memory usage1.9G (94of installed RAM)
    [
    OKSlow queries0% (0/21K)
    [
    OKHighest usage of available connections2% (11/500)
    [
    OKKey buffer size total MyISAM indexes512.0M/8.1M
    [!!] Key buffer hit rate94.7% (4K cached 261 reads)
    [
    OKQuery cache efficiency82.7% (14K cached 17K selects)
    [
    OKQuery cache prunes per day0
    [!!] Sorts requiring temporary tables73% (606 temp sorts 830 sorts)
    [!!] 
    Temporary tables created on disk48% (691 on disk 1K total)
    [
    OKThread cache hit rate94% (65 created 1K connections)
    [
    OKTable cache hit rate91% (78 open 85 opened)
    [
    OKOpen file limit used2% (126/5K)
    [
    OKTable locks acquired immediately99% (3K immediate 3K locks)
    [!!] 
    Connections aborted45%
    [
    OKInnoDB data size buffer pool3.2M/20.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
        
    Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours 
    recommendations may be inaccurate
        Reduce your overall MySQL memory footprint 
    for system stability
        When making adjustments
    make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Your applications are not closing MySQL connections properly
    Variables to adjust
    :
      *** 
    MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        sort_buffer_size (> 1M)
        read_rnd_buffer_size (> 256K)
        tmp_table_size (> 40M)
        max_heap_table_size (> 24M)


    PHP:
    root@vps [/var/lib/mysql]# cat /etc/my.cnf
    [mysqld]
    innodb_buffer_pool_size=20M
    key_buffer_size 
    512M
    max_allowed_packet
    =100M
    max_connections
    =500
    max_heap_table_size
    =24M
    open_files_limit
    =5000
    query_cache_size
    =8M
    table_cache 
    2200
    thread_cache_size
    =4
    tmp_table_size
    =40M
    log
    -slow-queries=/var/lib/mysql/slow.log
     
Loading...

Share This Page