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 的建議通常不太正確。你砍掉了輸出中有趣的部分。