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 uses High cpu rate

Discussion in 'Workarounds and Optimization' started by saidarao, Feb 3, 2016.

  1. saidarao

    saidarao Registered

    Joined:
    Feb 3, 2016
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    HYD
    cPanel Access Level:
    Website Owner
    Hello Mysql Experts,

    Greetings !!!

    In my production system use Server 4CPU 3G , RAM 32G
    and Mysql version:-5.5.38


    I'm experiencing some problems with my MYSQL server . My server is showing more than 100% usage of mysql even if I don't have too much traffic on web.
    I noticed that mysql makes high usage of cpu and VIRT memory


    Here is my.cnf:-


    I am seeting in my.cnf file like this.

    Code:
    =============================
    
    [mysqld]
    max_allowed_packet     = 512M
    max_connections        = 5000
    table_cache            = 12000
    max_connect_errors     = 1000000
    skip-name-resolve
    event_scheduler        =on
    key_buffer_size    =32M
    
    innodb_buffer_pool_size=8G
    innodb_buffer_pool_instances=5
    innodb_flush_method=O_DSYNC
    tmp_table_size=16777216
    join_buffer_size=33554432
    sort_buffer_size=33554432
    innodb_lock_wait_timeout=900
    
    
    
    innodb_log_buffer_size=256M
    innodb_flush_log_at_trx_commit=2
    innodb_doublewrite=0
    max_heap_table_size=268435456
    table_open_cache=12000
    
    
    innodb_io_capacity=100000
    innodb_adaptive_hash_index=on
    wait_timeout=900
    
    
    
    
    [mysqld_multi]
    mysqld     = /usr/bin/mysqld_safe
    mysqladmin = /usr/bin/mysqladmin
    log        = /var/log/mysqld_multi.log
    user       = multi_admin
    password   = secret
    
    [mysqld1]
    port       = 3306
    datadir    = /mydata/mysql_3306
    pid-file   = /var/run/mysqld/mysqld.pid
    socket     = /var/run/mysqld/mysqld.sock
    user       = mysql
    log-error  = /var/log/mysql1.err
    
    [mysqld2]
    port       = 3307
    datadir    = /mydata/mysql_3307
    pid-file   = /var/lib/mysqld/mysqld1.pid
    socket     = /var/lib/mysqld/mysqld1.sock
    user       = mysql
    log-error  = /var/log/mysql2.err
    
    
    [mysqld3]
    port       = 3308
    datadir    = /mydata/mysql_3317
    pid-file   = /var/lib/mysqld/mysqld2.pid
    socket     = /var/lib/mysqld/mysqld2.sock
    user       = mysql
    log-error  = /var/log/mysql3.err
    
    [mysqld5]
    port     = 3310
    datadir   = /mydata/mysql_3310
    pid-file  = /var/lib/mysqld/mysqld5.pid
    socket    = /var/lib/mysqld/mysqld5.sock
    user      = mysql
    log-error = /var/log/mysql5.err
    
    =======================================
    SHOW ENGINE INNODB STATUS\G;
    
    look at the end of the output as follows:
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 8791244800; in additional pool allocated 0
    Dictionary memory allocated 2329722
    Buffer pool size   524280
    Free buffers        509686
    Database pages     14512
    old database pages:-5397
    Pending reads 0
    Buffer pool hit rate 1000 / 1000
    ----------------
    
    Individual Buffer pool INFO:-
    -----------------------------
    
    Buffer pool size 104856
    Free buffers     101910
    Database pages     2929
    old database pages 1086
    
    
    Here is TOP -u mysql:-
    -----------------------
    
    PID     USER    PR    NI   VIRRT     RES    SHR  %cpu  %MEM       COMMAND
    3418    mysql    20    0    13.3g    1.6g     s      748     33.2%      mysqld
    3434    mysql    20    0    10.6g    939m   s      2         5.1          mysqld
    3362    mysql    20    0    10.1g    926m   s     0          2.0          mysqld
    3515    mysql    20    0    9851m    905m s     0          2.8          mysqld
    
    PlZ help me...
    
    Thanks in advance.
     
    #1 saidarao, Feb 3, 2016
    Last edited by a moderator: Feb 4, 2016
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page