Mysql
Wordpress 導致 CPU 使用率高（mysqld）
我有一個wordpress網站，在 debian 專用伺服器上執行的 MYSQL 數據庫中有 500k 個文章：
伺服器作業系統：Debian 8 “Jesssie)” - Linux 版本 4.9.58-xxxx-std-ipv6-64 (kernel@kernel.ovh.net) (gcc 版本 6.3.0 20170516 (Debian 6.3.0-18)) #1 SMP Mon Oct 23 11:35:59 CEST 2017 (Ispconfig)
網路伺服器：伺服器版本：Apache/2.4.10 (Debian)
PHP : PHP 版本 7.1.8
處理器：Intel 2x Xeon E5-2630v3 - 16/32t - 2.4GHz /3.2GHz
記憶體：128GB DDR4 ECC 1866 MHz
磁碟：SoftRaid 2x450GB SSD NVMe
今天我注意到 MySQL 導致 CPU 使用率很高：
2383 mysql 20 0 68.666g 7.596g 11152 S 2435% 6.0 108:43.00 mysqld
頂部：
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顯示完整的程序列表；
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>我的.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 = 8192M25 個查詢導致我的 MySQL 產生超過2435% 的 CPU 使用率。有什麼建議嗎？謝謝！
高 CPU 和/或慢查詢 - 必須修復查詢和/或索引和/或設計。
wp_postmeta 的架構設計不佳。請參閱此處的提示以了解如何改進它。
你的表是 ENGINE=MyISAM 嗎？還是 ENGINE=InnoDB？為了更好地利用您的 128GB RAM，請參閱此更改，這可能是未充分利用的。
my.cnf即使經過上述更改，您仍然可能有一些令人討厭的查詢。 找到它們，以便我們（在另一個問題中）可以討論如何加快它們的速度。