低記憶體使用高 CPU mysqld

我正在管理一個中高流量的伺服器。

它提供一個 wordpress 網站。

似乎它使用了大量的 CPU（在某些時候超過 300%）並且總是堆疊大約 15% 的記憶體使用量。

它是一個 MySQL 5.7 伺服器，在 nginx 和 php7-fpm 下具有 InnoDB 引擎。

伺服器有 16GB 記憶體

我嘗試使用 mysqltuner 進行調整，但這無濟於事

這是我的 mysqld.cnf

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 64M
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-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 192M
query_cache_type        = 1
innodb_buffer_pool_size = 10G

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/lib/mysql/Ubuntu-1604-xenial-64-minimal-slow.log
slow_query_log_file     = /var/log/mysql/mysql-slow.log
slow_query_log          = 1
#long_query_time = 5
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

讓我知道在上面調整什麼

親切的問候

更新

問題已解決，但不在目前伺服器上。我創建了 VPS 的映像並請求了另一個虛擬機。複製到新伺服器上，問題就解決了。我只能想像最初的機器上有一個故障硬碟。

我接受@Rick James 的回答是正確的，因為在調試 mysql 慢查詢日誌後，正如他所建議的，我發現報告的所有慢查詢都沒有任何意義。

查詢記憶體效率低下。

query_cache_size        = 192M

將其更改為 50M。

這是調整有助於“高 CPU”的少數情況之一。通常您需要檢查索引和查詢公式。所以…

找到幾個慢查詢，向我們展示SHOW CREATE TABLE所涉及的表，以及EXPLAIN SELECT ...。然後我們可以繼續這個討論。

哦，好；我看到慢日誌已經開啟，對於long_query_time;的值是無用的。將其更改為1. 一天后，使用pt-query-digest（或mysqldumpslow -s t）查找“最差”的查詢。

引用自：https://dba.stackexchange.com/questions/206917