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 CPU usage too high

Discussion in 'Workarounds and Optimization' started by itgold, Dec 29, 2013.

  1. itgold

    itgold Registered

    Joined:
    Dec 29, 2013
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Myserver
    CPU: Intel Xeon W3520 (4 cores / 8 Threads) 2.66GHz (3.4GHz Turbo Boost)
    Ram: 32 GB DDR3
    hard disk: 2 x 2TB SATA3 hard raid 1
    my.cnf
    PHP:
    [mysqld]
    default-
    storage-engine=MyISAM
    innodb_file_per_table
    =1
    local
    -infile=0

    port
    =3306
    socket
    =/var/lib/mysql/mysql.sock

    max_connections
    =600
    max_user_connections
    =30
    max_connect_errors
    =15
    key_buffer
    =512M
    myisam_sort_buffer_size
    =64M
    join_buffer_size
    =1M
    read_buffer_size
    =128K
    sort_buffer_size
    =256K
    table_cache
    =10000
    thread_cache_size
    =50
    wait_timeout
    =300
    connect_timeout
    =30
    max_allowed_packet
    =16M
    query_cache_limit
    =4M
    query_cache_size
    =128M
    query_cache_type
    =1
    tmp_table_size
    =128M
    max_heap_table_size
    =128M
    innodb_buffer_pool_size
    =1300M
    slow_query_log
    =1
    delayed_insert_timeout
    =1

    tmpdir
    =/mysqltmp

    open_files_limit
    =24440
    [mysql.server]
    user=mysql

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

    [mysqldump]
    quick
    max_allowed_packet 
    16M

    [mysql]
    no-auto-rehash
    Here is the output of mysqltuner:
    PHP:
     >>  MySQLTuner 1.1.1 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.5.34-cll
    [OKOperating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables105M (Tables249)
    [--] 
    Data in InnoDB tables928K (Tables58)
    [--] 
    Data in PERFORMANCE_SCHEMA tables0B (Tables17)
    [!!] 
    Total fragmented tables12

    -------- Security Recommendations  -------------------------------------------
    [
    OKAll database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 2d 20h 7m 16s (44M q [182.628 qps], 2M connTX26BRX3B)
    [--] 
    Reads Writes70% / 30%
    [--] 
    Total buffers2.0G global + 1.9M per thread (600 max threads)
    [
    OKMaximum possible memory usage3.1G (9of installed RAM)
    [
    OKSlow queries0% (0/44M)
    [
    OKHighest usage of available connections5% (34/600)
    [
    OKKey buffer size total MyISAM indexes512.0M/13.3M
    [OKKey buffer hit rate100.0% (31M cached 5K reads)
    [
    OKQuery cache efficiency70.9% (23M cached 33M selects)
    [!!] 
    Query cache prunes per day135331
    [OKSorts requiring temporary tables0% (880 temp sorts 2M sorts)
    [
    OKTemporary tables created on disk1% (38K on disk 2M total)
    [
    OKThread cache hit rate99% (34 created 2M connections)
    [!!] 
    Table cache hit rate0% (660 open 285K opened)
    [
    OKOpen file limit used3% (852/24K)
    [!!] 
    Table locks acquired immediately93%
    [
    OKInnoDB data size buffer pool928.0K/1.3G

    -------- Recommendations -----------------------------------------------------
    General recommendations:
        
    Run OPTIMIZE TABLE to defragment tables for better performance
        Increase table_cache gradually to avoid file descriptor limits
        Optimize queries 
    and/or use InnoDB to reduce lock wait
    Variables to adjust
    :
        
    query_cache_size (> 128M)
        
    table_cache (> 10000)
     
  2. cPanelPeter

    cPanelPeter Technical Analyst III
    Staff Member

    Joined:
    Sep 23, 2013
    Messages:
    569
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    And what is it that you are needing assistance with exactly?
     
Loading...

Share This Page