PPNSteve

Well-Known Member
Mar 13, 2003
413
3
168
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 <[email protected]>
 >>  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?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,909
2,225
463
Hello :)

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

Thank you.
 

PPNSteve

Well-Known Member
Mar 13, 2003
413
3
168
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
 
Last edited: