1

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 ([email protected]) (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!

Improve this question
1
  • Additional information request. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) current SHOW FULL PROCESSLIST; E) complete MySQLTuner report F) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis.
    – Wilson Hauck
    Aug 3, 2019 at 16:40

1 Answer 1

Reset to default
0

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.cnf to 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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.