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:
my.cnf contains:
MySQL Report:
and finally mysqltuner.pl:
Can anyone help us get this cpu usage under control and help optimize sql performance?
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]
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
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
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%)
