Skip to main content

MySQL 5.6 Optimize help

PPNSteve
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: 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] 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: [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: ./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: ./mysqltuner.pl >> MySQLTuner 1.2.1 mod - Major Hayden >> 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?

Comments

4 comments

Date Votes
  • cPanelMichael
    Hello :) You may also want to run "mysqladmin processlist" to get a better idea of what databases are utilizing the most resources. Thank you.
    0
  • PPNSteve
    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) 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: 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
    0
  • triantech
    Try this and see if it helps, add the following line to /etc/my.cnf and restart the service. performance_schema = 0
    0
  • PPNSteve
    performance_schema is disabled by default IIRC and thus not ON currently
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post