MySQL程序佔用CPU高達180%（需要優化）

• 我有一台具有 128 GB RAM 和 8 核 CPU 和 2TB 硬碟的專用伺服器。
• 我在同一台伺服器上安裝了cPanel和MySQL 。
• 在這台伺服器上，我託管了 12 個網站，其中 5 個流量很大，這 5 個網站每天總共有 60000 次訪問。
• Wordpress 和 Drupal 和 Prestashop 中的站點
• 這些站點的數據庫大小在200 MB 到 1.5 GB 之間。

我在**/etc/my.cnf**上有一個 MYSQL 配置：

[mysqld]
performance-schema             =ON
bind-address                   =127.0.0.1
max_allowed_packet             =256M
#
local-infile                   =0
default-storage-engine         =MyISAM

open_files_limit               =10000
key_buffer_size                =8G
table_open_cache               =6000
max_heap_table_size            =256M
tmp_table_size                 =256M
sort_buffer_size               =1M
read_buffer_size               =1M
read_rnd_buffer_size           =2M
join_buffer_size               =128M
myisam_sort_buffer_size        =64M
thread_cache_size              =200
query_cache_size               =0
query_cache_type               =0
query_cache_limit              =256M
wait_timeout                   =300
max_connections                =500
max_user_connections           =30
log-error                      ="/var/lib/mysql/mysqlerror.log"

innodb_file_per_table          =1
innodb_buffer_pool_size        =8G
innodb_log_buffer_size         =64M
innodb_flush_log_at_trx_commit =2
innodb_lock_wait_timeout       =50
innodb_flush_method            =O_DIRECT
innodb_log_file_size           =1G


explicit_defaults_for_timestamp = 1
secure-file-priv = "/home/secure-file-priv"

# Log slow queries taking more than 10 seconds to complete
# use mysqldumpslow to analyze
slow-query-log                 =1
slow-query-log-file = /var/lib/mysql/myslow.log

[mysqldump]
quick
max_allowed_packet             =128M

MySQLTuner是一個用 Perl 編寫的腳本，它允許您快速查看 MySQL 安裝並進行調整以提高性能和穩定性。檢索目前配置變數和狀態數據，並以簡要格式顯示，並附有一些基本性能建議。

我已經執行 MySQLTuner 腳本來優化我的 MySQL 配置：

./mysqltuner.pl

tnis 是輸出：

-------- Recommendations ---------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/mysqlerror.log file
Control error line(s) into /var/lib/mysql/mysqlerror.log file
MySQL started within last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: [https://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/][1]
Beware that open_files_limit (65536) variable
should be greater than table_open_cache (5000)
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
join_buffer_size (> 64.0M, or always use indexes with joins)
table_open_cache (> 5000)

我已經調整了這兩個值並重新啟動了 MySQL，現在的建議是：

-------- Recommendations --------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/mysqlerror.log file
Control error line(s) into /var/lib/mysql/mysqlerror.log file
MySQL started within last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Consider installing Sys schema from https://github.com/mysql/mysql-sys

---

問題是伺服器的高負載平均值介於 4 到 12 負載平均值之間：5.40 6.62 7.40 負載平均值：4.84 8.34 7.61 負載平均值：12.05 12.48 10.74

而MySQL程序佔用CPU高達180%

Pid Owner   Priority    CPU %   Memory %    Command
3618 (Trace) (Kill) mysql   0     180.13      5.92  /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/mysqlerror.log --open-files-limit=10000 --pid-file=server.domain.com.pid

• 您建議哪種配置？
• 是否有必要將數據庫分離到另一台伺服器？
• 還有其他建議嗎？

---

使用 mysqltuner.pl 進行的新測試，結果如下：

./mysqltuner.pl
>>  MySQLTuner 1.7.4 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.38-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/mysqlerror.log(274K)
[OK] Log file /var/lib/mysql/mysqlerror.log exists
[OK] Log file /var/lib/mysql/mysqlerror.log is readable.
[OK] Log file /var/lib/mysql/mysqlerror.log is not empty
[OK] Log file /var/lib/mysql/mysqlerror.log is smaller than 32 Mb
[!!] /var/lib/mysql/mysqlerror.log contains 75 warning(s).
[!!] /var/lib/mysql/mysqlerror.log contains 97 error(s).
[--] 43 start(s) detected in /var/lib/mysql/mysqlerror.log
[--] 1) 2018-01-18 09:00:29 19228 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-01-16 14:07:40 29765 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-01-16 14:06:04 28249 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-01-16 11:51:00 5274 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-01-16 09:05:09 13555 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-01-16 08:11:26 3618 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-01-15 21:13:52 22775 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-01-15 17:23:46 26000 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-01-15 17:22:56 25298 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-01-15 15:30:28 9766 [Note] /usr/sbin/mysqld: ready for connections.
[--] 48 shutdown(s) detected in /var/lib/mysql/mysqlerror.log
[--] 1) 2018-01-18 09:00:27 29765 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-01-16 14:07:37 28249 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-01-16 14:06:01 5274 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-01-16 11:50:58 13555 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-01-16 09:04:33 12957 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-01-16 09:04:17 12439 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-01-16 09:04:02 11933 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-01-16 09:03:48 11366 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-01-16 09:03:47 3618 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-01-16 08:11:23 22775 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 271M (Tables: 227)
[--] Data in InnoDB tables: 2G (Tables: 1560)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 7d 6h 52m 34s (518M q [823.132 qps], 2M conn, TX: 1723G, RX: 114G)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Physical Memory     : 125.7G
[--] Max MySQL memory    : 81.4G
[--] Other process memory: 2.1G
[--] Total buffers: 16.3G global + 132.2M per thread (500 max threads)
[--] P_S Max memory usage: 543M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 24.0G (19.06% of installed RAM)
[OK] Maximum possible memory usage: 81.4G (64.80% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (696/518M)
[OK] Highest usage of available connections: 11% (55/500)
[OK] Aborted connections: 0.02%  (494/2524679)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (135K temp sorts / 78M sorts)
[!!] Joins performed without indexes: 77629
[!!] Temporary tables created on disk: 31% (11M on disk / 36M total)
[OK] Thread cache hit rate: 99% (55 created / 2M connections)
[!!] Table cache hit rate: 0% (4K open / 711K opened)
[OK] Open file limit used: 1% (729/65K)
[OK] Table locks acquired immediately: 99% (764M immediate / 764M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 543.3M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1B used / 8B cache)
[OK] Key buffer size / total MyISAM indexes: 8.0G/38.6M
[OK] Read Key buffer hit rate: 100.0% (3B cached / 2K reads)
[!!] Write Key buffer hit rate: 0.0% (1B cached / 43K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/2.8G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/8.0G should be equal 25%
[OK] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (163467971977 hits/ 163468105140 total)
[!!] InnoDB Write Log efficiency: 44.89% (19521578 hits/ 43484887 total)
[OK] InnoDB log waits: 0.00% (0 waits / 23963309 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
   Control warning line(s) into /var/lib/mysql/mysqlerror.log file
   Control error line(s) into /var/lib/mysql/mysqlerror.log file
   Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
   Adjust your join queries to always utilize indexes
   Temporary table size is already large - reduce result set size
   Reduce your SELECT DISTINCT queries without LIMIT clauses
   Increase table_open_cache gradually to avoid file descriptor limits
   Read this before increasing table_open_cache over 64: https://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/
   Beware that open_files_limit (65536) variable
   should be greater than table_open_cache (6000)
   Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
   join_buffer_size (> 128.0M, or always use indexes with joins)
   table_open_cache (> 6000)

在你的 my.cnf 中要做的事情，

thread_cache_size=100 #from 200 以防止記憶體不足 V8 建議 100 CAP 以實現持久性/可預測性。

在我們看到您完整的 MySQLTuner 報告後（請至少 24 小時正常執行時間），其他建議將是可能的。

你的硬碟是SSD還是旋轉硬碟？

啃CPU？慢日誌打開了嗎？降低long_query_time到只有 1（秒）。mysqldumpslow -s t等待一天，然後用or總結慢日誌pt-query-digest，然後處理最慢的幾個查詢。

因為我看到了 WordPress，我建議按照這裡的提示來加快大多數使用：http wp_postmeta: //mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

什麼是“訪問”？查詢的數量在這個討論中會更有用。

我看到預設引擎是 MyISAM。但他們真的是這樣嗎？如果是這樣，請考慮切換到 InnoDB 以獲得更好的並發性。 http://mysql.rjweb.org/doc.php/myisam2innodb

2GB硬碟？也許十年前。

您使用的是什麼版本的 MySQL？

您顯示的設置應該只使用了 128GB 巨大 RAM 的一小部分。缺了點什麼。

mysqltuner 的建議通常不太正確。你砍掉了輸出中有趣的部分。

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