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 5.6 Optimize help

Discussion in 'Workarounds and Optimization' started by PPNSteve, Jul 31, 2014.

  1. PPNSteve

    PPNSteve Well-Known Member

    Joined:
    Mar 13, 2003
    Messages:
    393
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Somewhere in Ilex Forest
    cPanel Access Level:
    Root Administrator
    Twitter:
    Recently updated our NySQL to the latest cP version, 5.6.x, and I've noticed a large increase of CPU usage compared to the old version. WHM Process Manager says mysql is using 78% (and has gone as high as 82%)

    Server: dual quad-core with HT xeons, 24GB RAM

    top output:
    Code:
    top - 19:55:18 up 174 days, 16:36,  2 users,  load average: 1.76, 1.46, 1.34
    Tasks: 420 total,   6 running, 412 sleeping,   0 stopped,   2 zombie
    Cpu0  : 14.3%us,  1.7%sy,  0.0%ni, 80.0%id,  3.7%wa,  0.0%hi,  0.3%si,  0.0%st
    Cpu1  : 31.1%us,  3.3%sy,  0.0%ni, 65.6%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu2  : 25.2%us,  0.3%sy,  0.0%ni, 74.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu3  : 33.9%us,  1.0%sy,  0.0%ni, 65.1%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu4  : 36.0%us,  2.7%sy,  0.0%ni, 61.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu5  : 46.7%us,  2.6%sy,  0.0%ni, 49.7%id,  0.7%wa,  0.0%hi,  0.3%si,  0.0%st
    Cpu6  : 25.8%us,  1.3%sy,  0.0%ni, 72.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu7  :  3.6%us,  1.3%sy,  0.0%ni, 95.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu8  : 51.2%us,  3.0%sy,  0.0%ni, 45.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu9  :  8.3%us,  1.3%sy,  0.0%ni, 90.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu10 : 34.2%us,  2.0%sy,  0.0%ni, 63.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu11 : 13.2%us,  0.7%sy,  0.0%ni, 86.1%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu12 :  4.0%us,  1.3%sy,  0.0%ni, 94.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu13 : 34.9%us,  1.7%sy,  0.0%ni, 63.5%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu14 :  4.7%us,  2.3%sy,  0.0%ni, 93.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu15 :  6.0%us,  2.3%sy,  0.0%ni, 91.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Mem:  24596852k total, 14619004k used,  9977848k free,   236292k buffers
    Swap: 10485752k total,    13144k used, 10472608k free, 10750832k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+   P COMMAND
    29837 mysql     20   0 10.1g 2.2g 8976 S 206.6  9.5  11780:12  6 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/svr1.1-g
     2821 pokecomm  20   0  207m  24m 7792 R 49.3  0.1   0:02.47 11 /usr/bin/php /home/pokecomm/public_html/showthread.php
     2857 pokecomm  20   0     0    0    0 Z 17.5  0.0   0:00.53  1 [php] <defunct>
     2871 pokecomm  20   0  208m  25m 7796 R 12.9  0.1   0:00.39  5 /usr/bin/php /home/pokecomm/public_html/showthread.php
     2866 pokecomm  20   0  205m  22m 7808 S  6.0  0.1   0:00.18  6 /usr/bin/php /home/pokecomm/public_html/index.php
     2873 pokecomm  20   0  206m  23m 7792 R  5.0  0.1   0:00.15  8 /usr/bin/php /home/pokecomm/public_html/showthread.php
     2875 pokecomm  20   0  205m  21m 7692 R  2.3  0.1   0:00.07  7 /usr/bin/php /home/pokecomm/public_html/showthread.php
     3224 memcache  20   0  350m  47m  480 S  1.0  0.2   2728:38  0 memcached -d -p 11211 -u memcached -m 2048 -c 1024 -P /var/run/memcached/memcached.pid -l 127.0.0.1
      239 root      39  19     0    0    0 S  0.3  0.0 692:30.41 15 [kipmi0]
    
    my.cnf contains:
    Code:
    
    [mysqld]
    innodb_file_per_table=1
    key_buffer = 512M
    max_allowed_packet=268435456
    #table_cache = 1024
    join_buffer_size = 2M
    sort_buffer_size = 8M
    read_buffer_size = 4M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache = 16
    query_cache_type = 1
    query_cache_limit = 4M
    query_cache_size = 24M
    max_connections = 1050
    tmp_table_size = 64M
    tmpdir = /home2/my_temp
    long_query_time = 5
    slow_query_log=1
    slow_query_log_file="/var/log/mysql/log-slow-queries.log"
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 16
    open_files_limit=16464
    default-storage-engine=MyISAM
    [mysqldump]
    quick
    max_allowed_packet = 32M
    
    [mysql]
    no-auto-rehash
    #long_query_time = 1
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    MySQL Report:
    Code:
    ./mysqlreport
    Use of uninitialized value in formline at ./mysqlreport line 1313.
    MySQL   5.6.17-log          uptime 10 11:19:24  Thu Jul 31 19:49:43 2014
    
    __ Key _________________________________________________________________
    Buffer used   418.64M of  512.00M   %Used:  81.76
      Current     512.00M              %Usage: 100.00
    Write hit      90.49%
    Read hit       99.96%
    
    __ Questions ___________________________________________________________
    Total         288.08M     318.4/s
      QC Hits     172.94M     191.1/s  %Total:  60.03
      DMS          96.90M     107.1/s           33.63
      Com_         14.16M      15.6/s            4.91
      COM_QUIT      6.01M       6.6/s            2.09
      -Unknown      1.91M       2.1/s            0.66
    Slow 5 s       11.94k       0.0/s            0.00  %DMS:   0.01 Log:
    DMS            96.90M     107.1/s           33.63
      SELECT       85.00M      93.9/s           29.50         87.72
      UPDATE        6.25M       6.9/s            2.17          6.45
      INSERT        4.00M       4.4/s            1.39          4.13
      DELETE        1.14M       1.3/s            0.39          1.17
      REPLACE     504.02k       0.6/s            0.17          0.52
    Com_           14.16M      15.6/s            4.91
      set_option    5.99M       6.6/s            2.08
      change_db     5.97M       6.6/s            2.07
      stmt_close  474.69k       0.5/s            0.16
    
    __ Rows ________________________________________________________________
    Rows          315.82G    349.1k/s
      Using idx    63.94G     70.7k/s  %Index:  20.25
    Rows/question   1.10k
    
    __ SELECT and Sort _____________________________________________________
    Scan            7.71M       8.5/s %SELECT:   9.07
    Range          20.99M      23.2/s           24.69
    Full join      23.43k       0.0/s            0.03
    Range check         0         0/s            0.00
    Full rng join       8       0.0/s            0.00
    Sort scan       6.08M       6.7/s
    Sort range      7.39M       8.2/s
    Sort mrg pass     156       0.0/s
    
    __ Query Cache _________________________________________________________
    Memory usage   15.97M of   24.00M  %Usage:  66.54
    Block Fragmnt   9.29%
    Hits          172.94M     191.1/s
    Inserts        83.40M      92.2/s
    Insrt:Prune    3.60:1      66.5/s
    Hit:Insert     2.07:1
    
    __ Table Locks _________________________________________________________
    Waited          2.47M       2.7/s  %Total:   1.50
    Immediate     162.19M     179.3/s
    
    __ Tables ______________________________________________________________
    Open             1999 of   2000    %Cache:  99.95
    Opened         29.24k       0.0/s
    
    __ Connections _________________________________________________________
    Max used          905 of   1050      %Max:  86.19
    Total           6.01M       6.6/s
    
    __ Created Temp ________________________________________________________
    Disk table      3.69M       4.1/s   %Disk:  35.49
    Table          10.39M      11.5/s    Size:  64.0M
    File              319       0.0/s
    
    __ Threads _____________________________________________________________
    Running            39 of     43
    Created        79.83k       0.1/s
    Slow                0         0/s
    Cached             12 of     16      %Hit:  98.67
    
    __ Aborted _____________________________________________________________
    Clients           386       0.0/s
    Connects            2       0.0/s
    
    __ Bytes _______________________________________________________________
    Sent            2.82T      3.1M/s
    Received       58.82G     65.0k/s
    
    __ InnoDB Buffer Pool __________________________________________________
    Usage          20.72M of  128.00M  %Usage:  16.19
    Read hit      100.00%
    Pages
      Free          6.87k              %Total:  83.81
      Data          1.30k                       15.87  %Drty:   0.00
      Misc             26                        0.32
      Latched           0                        0.00
    Reads           7.70G      8.5k/s
      From disk     1.03k       0.0/s   %Disk:   0.00
      Ahead Rnd         0         0/s
    Writes        325.72k       0.4/s
    Wait Free           0         0/s   %Wait:   0.00
    Flushes       142.99k       0.2/s
    
    __ InnoDB Lock _________________________________________________________
    Waits           23153       0.0/s
    Current             0
    Time acquiring
      Total        598563 ms
      Average          25 ms
      Max            3329 ms
    
    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
      Reads         1.29k       0.0/s
      Writes      301.54k       0.3/s
      fsync       234.04k       0.3/s
      Pending
        Reads           0
        Writes          0
        fsync           0
    
    Pages
      Created         149       0.0/s
      Read          1.15k       0.0/s
      Written     142.99k       0.2/s
    
    Rows
      Deleted      23.58k       0.0/s
      Inserted     37.06k       0.0/s
      Read         16.94G     18.7k/s
      Updated         526       0.0/s
    
    and finally mysqltuner.pl:
    Code:
    ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.1 mod - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Modified by George Liu (eva2000) at http://vbtechsupport.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.6.17-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 10G (Tables: 488)
    [--] Data in InnoDB tables: 9M (Tables: 127)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 4M (Tables: 22)
    [!!] Total fragmented tables: 54
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10d 11h 21m 41s (288M q [318.409 qps], 6M conn, TX: 2821B, RX: 58B)
    [--] Reads / Writes: 87% / 13%
    [--] Total buffers: 696.0M global + 22.2M per thread (1050 max threads)
    [!!] Maximum possible memory usage: 23.5G (100% of installed RAM)
    [OK] Slow queries: 0% (11K/288M)
    [!!] Highest connection usage: 86%  (905/1050)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/2.6G
    [OK] Key buffer hit rate: 100.0% (23B cached / 9M reads)
    [OK] Query cache efficiency: 67.0% (172M cached / 257M selects)
    [!!] Query cache prunes per day: 2215154
    [OK] Sorts requiring temporary tables: 0% (156 temp sorts / 13M sorts)
    [!!] Joins performed without indexes: 23435
    [!!] Temporary tables created on disk: 26% (3M on disk / 14M total)
    [OK] Thread cache hit rate: 98% (79K created / 6M connections)
    [!!] Table cache hit rate: 6% (1K open / 29K opened)
    [OK] Open file limit used: 13% (2K/16K)
    [OK] Table locks acquired immediately: 98% (162M immediate / 164M locks)
    [OK] InnoDB data size / buffer pool: 9.6M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability.
        See notes on accuracy of this recommendation below
        Reduce or eliminate persistent connections to reduce connection usage
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    
    Variables to adjust:
      *** MySQL maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
      *** Please note this recommendation is not entirely accurate.
          The formula used to calculate max memory usage assumes all queries utilise
          all memory buffers simultaneously. When in reality it is very rare for a
          query to engage & utilise all memory buffers simultaneously.
    
          Formula also assumes all predefined max_connections are reached.
          You could have set max_connections = 1000 and in a whole year
          of usage never hit beyond 50 max_used_connections. So your real MySQL
          memory usage is only 1/20th of theorectical max memory usage reported.
    
          So real max memory usage will never reach this peak.
          So do not be too concerned with this warning.
    
          It is better to monitor your real MySQL max_used_connection and MySQL
          memory usage over time and adjust accordingly.
    
          You can use tools such as Cacti, Munin or mysqlmymonlite.sh at mysqlmymon.com to
          monitor your MySQL memory usage over time.
    
          MySQL performs optimally when its required amount of memory is met.
          Reducing and starving MySQL memory allocation to adhere to this
          artificial max memory warning - of which in reality will never be reached,
          will only reduce MySQL performance in many cases ***
    
        max_connections (> 1050)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        query_cache_size (> 24M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 64M, increase tmp_table_size)
        max_heap_table_size (> 16M, increase max_heap_table_size)
        table_cache (> 2000, table_open_cache hit rate <20%)
    
    Can anyone help us get this cpu usage under control and help optimize sql performance?
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You may also want to run "mysqladmin processlist" to get a better idea of what databases are utilizing the most resources.

    Thank you.
     
  3. PPNSteve

    PPNSteve Well-Known Member

    Joined:
    Mar 13, 2003
    Messages:
    393
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Somewhere in Ilex Forest
    cPanel Access Level:
    Root Administrator
    Twitter:
    There are 3 active dbs on this server.. I know which one is using the most as its the primary site this server is hosting. (a large vB based forum community)

    Code:
    mysqladmin processlist
    +---------+------------------+-----------+----------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
    | Id      | User             | Host      | db             | Command | Time | State        | Info                                                                                                 |
    +---------+------------------+-----------+----------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
    | 5167934 | eximstats        | localhost | eximstats      | Sleep   | 206  |              |                                                                                                      |
    | 5994796 | leechprotect     | localhost | leechprotect   | Sleep   | 3330 |              |                                                                                                      |
    | 6014857 | pokecomm_dbadmin | localhost | pokecomm_vBold | Query   | 0    | Sending data | SELECT userid, username, joindate, usergroupid, displaygroupid, lastactivity, posts FROM user WHERE  |
    | 6014858 | pokecomm_dbadmin | localhost | pokecomm_vBold | Sleep   | 0    |              |                                                                                                      |
    | 6014859 | root             | localhost |                | Query   | 0    | init         | show processlist                                                                                     |
    +---------+------------------+-----------+----------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
    
    just for fun, mytop output:
    Code:
    MySQL on localhost (5.6.17-log)                                                                                                                                                       up 10+11:42:54 [20:13:13]
     Queries: 20.0   qps:    0 Slow:     0.0         Se/In/Up/De(%):    865933740/00/00/00
                 qps now:    0 Slow qps: 0.0  Threads:    6 (   4/  13) 33400/00/00/00
     Key Efficiency: 100.0%  Bps in/out:   0.0/  0.1   Now in/out:   8.3/ 1.9k
    
          Id      User         Host/IP         DB      Time    Cmd Query or State
          --      ----         -------         --      ----    --- ----------
     6015437      root       localhost  eximstats         0  Query show full processlist
     6015628 pokecomm_       localhost pokecomm_v         0  Query SELECT userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user.languageid AS saved_languageid, IF(user.displayg
     6015625 pokecomm_       localhost pokecomm_v         1  Query SELECT userid, username, joindate, usergroupid, displaygroupid, lastactivity, posts FROM user WHERE FROM_UNIXTIME(joindate, '%D %M') = FROM_UNIX
     6015627 pokecomm_       localhost pokecomm_v         1  Query UPDATE user SET lastactivity = 1406837592 WHERE userid = 282170
     5167934 eximstats       localhost  eximstats        19  Sleep
     5994796 leechprot       localhost leechprote      3451  Sleep
    
     
    #3 PPNSteve, Jul 31, 2014
    Last edited: Jul 31, 2014
  4. triantech

    triantech Well-Known Member

    Joined:
    Jul 1, 2014
    Messages:
    145
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Kochi, India, India
    cPanel Access Level:
    Root Administrator
    Try this and see if it helps, add the following line to /etc/my.cnf and restart the service.

    performance_schema = 0
     
  5. PPNSteve

    PPNSteve Well-Known Member

    Joined:
    Mar 13, 2003
    Messages:
    393
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Somewhere in Ilex Forest
    cPanel Access Level:
    Root Administrator
    Twitter:
    performance_schema is disabled by default IIRC and thus not ON currently
     
Loading...

Share This Page