I have a wordpress site with 500k posts in MYSQL database running on debian dedicated server:
Server OS: Debian 8 "Jesssie)" – Linux version 4.9.58-xxxx-std-ipv6-64 (kernel@kernel.ovh.net) (gcc version 6.3.0 20170516 (Debian 6.3.0-18) ) #1 SMP Mon Oct 23 11:35:59 CEST 2017 (Ispconfig)
Web server: Server version: Apache/2.4.10 (Debian)
PHP: PHP Version 7.1.8
Procesor: Intel 2x Xeon E5-2630v3 – 16/32t – 2.4GHz /3.2GHz
RAM: 128GB DDR4 ECC 1866 MHz
Disks: SoftRaid 2x450GB SSD NVMe
Today I noticed that MySQL is causing a high CPU usage:
2383 mysql 20 0 68.666g 7.596g 11152 S 2435% 6.0 108:43.00
mysqld
top:
top - 18:05:42 up 6 min, 1 user, load average: 23.29, 14.22, 6.09
Tasks: 548 total, 4 running, 544 sleeping, 0 stopped, 0 zombie
%Cpu0 : 25.8 us, 61.0 sy, 0.0 ni, 13.2 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 25.2 us, 57.7 sy, 0.0 ni, 17.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu2 : 24.7 us, 49.3 sy, 0.0 ni, 26.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu3 : 53.6 us, 15.9 sy, 0.0 ni, 30.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu4 : 27.0 us, 43.0 sy, 0.0 ni, 30.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu5 : 24.8 us, 41.3 sy, 0.0 ni, 33.9 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu6 : 59.3 us, 31.8 sy, 0.0 ni, 8.9 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu7 : 21.6 us, 63.2 sy, 0.0 ni, 15.2 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu8 : 41.5 us, 40.5 sy, 0.0 ni, 18.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu9 : 51.5 us, 35.6 sy, 0.0 ni, 12.5 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu10 : 41.0 us, 39.2 sy, 0.0 ni, 19.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu11 : 55.4 us, 33.2 sy, 0.0 ni, 11.4 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu12 : 76.9 us, 10.0 sy, 0.0 ni, 13.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu13 : 81.7 us, 3.0 sy, 0.0 ni, 15.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu14 : 31.8 us, 43.2 sy, 0.0 ni, 25.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu15 : 35.3 us, 58.7 sy, 0.0 ni, 6.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu16 : 24.4 us, 52.5 sy, 0.0 ni, 23.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu17 : 26.0 us, 64.3 sy, 0.0 ni, 9.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu18 : 34.9 us, 36.6 sy, 0.0 ni, 28.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu19 : 20.0 us, 46.7 sy, 0.0 ni, 33.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu20 : 86.3 us, 1.0 sy, 0.0 ni, 12.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu21 : 36.2 us, 51.2 sy, 0.0 ni, 12.6 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu22 : 25.5 us, 46.0 sy, 0.0 ni, 28.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu23 : 19.7 us, 41.5 sy, 0.0 ni, 38.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu24 : 62.3 us, 4.6 sy, 0.0 ni, 33.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu25 : 26.3 us, 60.9 sy, 0.0 ni, 12.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu26 : 57.3 us, 24.7 sy, 0.0 ni, 18.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu27 : 73.6 us, 7.9 sy, 0.0 ni, 18.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu28 : 21.7 us, 42.8 sy, 0.0 ni, 35.1 id, 0.0 wa, 0.0 hi, 0.3 si, 0.0 st
%Cpu29 : 43.3 us, 37.0 sy, 0.0 ni, 19.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu30 : 26.2 us, 57.8 sy, 0.0 ni, 15.9 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu31 : 28.2 us, 65.8 sy, 0.0 ni, 5.7 id, 0.0 wa, 0.0 hi, 0.3 si, 0.0 st
KiB Mem: 13198007+total, 16391500 used, 11558857+free, 86084 buffers
KiB Swap: 1046520 total, 0 used, 1046520 free. 5456440 cached Mem
show full processlist;
mysql> show full processlist;
+------+---------------+-----------+---------------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+---------------+-----------+---------------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1541 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 1604 | c1database | localhost | c1database | Query | 2 | Sending data | SELECT post_id FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND meta_value = '2018/01/amp.jpg' |
| 1605 | c1database | localhost | c1database | Query | 1 | Sending data | SELECT post_id FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND meta_value = '2018/01/amp.jpg' |
| 1610 | c1database | localhost | c1database | Query | 0 | Sorting result | SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') AND tt.count > 0 ORDER BY tt.count DESC LIMIT 45 |
| 1611 | c1database | localhost | c1database | Query | 6 | Sorting result | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1613 | c1database | localhost | c1database | Query | 5 | Sorting result | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1614 | c1database | localhost | c1database | Query | 4 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1615 | c1database | localhost | c1database | Query | 3 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1616 | c1database | localhost | c1database | Query | 3 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1617 | c1database | localhost | c1database | Query | 2 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1618 | c1database | localhost | c1database | Query | 2 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1619 | c1database | localhost | c1database | Query | 2 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1620 | c1database | localhost | c1database | Query | 2 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1621 | c1database | localhost | c1database | Query | 2 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1622 | c1database | localhost | c1database | Query | 2 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1623 | c1database | localhost | c1database | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1624 | c1database | localhost | c1database | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1625 | c1database | localhost | c1database | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1626 | c1database | localhost | c1database | Query | 1 | Sending data | SELECT post_id FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND meta_value = '2018/01/amp.jpg' |
| 1627 | c1database | localhost | c1database | Query | 1 | Sending data | SELECT post_id FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND meta_value = '2018/01/amp.jpg' |
| 1628 | c1database | localhost | c1database | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1629 | c1database | localhost | c1database | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1630 | c1database | localhost | c1database | Query | 0 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 1631 | c1database | localhost | c1database | Query | 0 | Sending data | SELECT post_id FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND meta_value = '2018/01/amp.jpg' |
| 1632 | c1database | localhost | c1database | Query | 0 | Sending data | SELECT post_id FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND meta_value = '2018/01/amp.jpg' |
+------+---------------+-----------+---------------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
25 rows in set (0.00 sec)
mysql>
my.cnf (MySQL):
innodb_buffer_pool_size = 61440M
innodb_file_per_table=1
innodb_log_file_size = 1024M
innodb_buffer_pool_instances=60
key_buffer = 2048M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 0
query_cache_type = 0
#query_cache_size = 8192M
25 queries have caused my MySQL to generate over 2435% CPU usage. Any advice? Thank you!
Best Answer
High CPU and/or slow queries -- must fix the queries and/or indexes and/or design.
The schema for wp_postmeta is poorly designed. See the tips in here for how to improve it.
Are your tables ENGINE=MyISAM? Or ENGINE=InnoDB? See this for the one change to make to
my.cnfto take better advantage of your 128GB of RAM, which is probably very underutilized.Even after the above changes, you may still have some nasty queries. Find them so we (in another Question) can discuss how to speed them up.