我們有一個運行 mysql 的高流量服務器。該資料庫多年來一直運行良好，沒有明顯的流量峰值或資料庫更改，但最近 Mysql CPU 消耗已飆升至一致的 600%。
我們沒有看到任何慢速查詢被記錄，但我們得到了以下建議。
我們對 MySQL 不是很了解，我們嘗試修復 CPU 消耗的一切都沒有奏效。
如果有人可以提供一些推薦的設定，將不勝感激。
top - 08:08:59 up 313 days, 12:47, 2 users, load average: 7.21, 6.48, 6.08
Tasks: 294 total, 1 running, 293 sleeping, 0 stopped, 0 zombie
Cpu(s): 29.9%us, 10.6%sy, 3.9%ni, 47.1%id, 3.1%wa, 0.0%hi, 5.5%si, 0.0%st
Mem: 8018016k total, 7869300k used, 148716k free, 587632k buffers
Swap: 0k total, 0k used, 0k free, 5894136k cached
[mysqld]
# skip-networking
#tmpdir=/dev/shm
general_log = 1
slow_query_log = 1
log-slow-queries = /var/log/mysql-slow.log
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2
sort_buffer_size = 4M
max_connections = 151
max_allowed_packet = 100M
query_cache_size = 128M
query_cache_limit = 5M
join_buffer_size = 10M
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 4
table_cache = 20k
innodb_buffer_pool_size = 512M
key_buffer_size = 128M
local-infile=0
open_files_limit=2058
wait_timeout = 500
connect_timeout = 500
interactive_timeout = 500
performance_schema = on
>> MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.5.50-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: ARCHIVE BLACKHOLE CSV -FEDERATED InnoDB MRG_MYISAM
[--] Data in MyISAM tables: 453M (Tables: 169)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 5
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 22m 39s (763K q [561.834 qps], 22K conn, TX: 506M, RX: 95M)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 848.0M global 14.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 3.0G (39% of installed RAM)
[OK] Slow queries: 0% (1/763K)
[OK] Highest usage of available connections: 52% (80/151)
[OK] Key buffer size / total MyISAM indexes: 128.0M/273.3M
[OK] Key buffer hit rate: 100.0% (72M cached / 29K reads)
[OK] Query cache efficiency: 79.1% (472K cached / 596K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (8 temp sorts / 43K sorts)
[OK] Temporary tables created on disk: 0% (37 on disk / 10K total)
[!!] Thread cache hit rate: 47% (12K created / 22K connections)
[OK] Table cache hit rate: 97% (249 open / 256 opened)
[OK] Open file limit used: 0% (277/41K)
[!!] Table locks acquired immediately: 53%
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
thread_cache_size (> 4)
次優快取方法。
您正在使用具有相當高流量資料庫的 MySQL 查詢快取。可能值得考慮使用 memcached 而不是 MySQL 查詢快取，尤其是在您有多個從屬服務器的情況下。
不到 80% 的查詢快取被使用。
這可能是由于 query_cache_limit 太低造成的。重繪 查詢快取也可能有所幫助。
查詢快取相當分散。
嚴重的碎片可能（進一步）增加 Qcache_lowmem_prunes。這可能是由于 query_cache_size 太小而導致許多 Query 快取低記憶體修剪造成的。對于即時但短暫的修復，您可以重繪 查詢快取（可能會長時間鎖定查詢快取）。小心地將 query_cache_min_res_unit 調整為較低的值也可能會有所幫助，例如，您可以使用以下公式將其設定為快取中查詢的平均大小： (query_cache_size - qcache_free_memory) / qcache_queries_in_cache
有很多行正在排序。
雖然大量的行排序沒有任何問題，但您可能希望確保需要大量排序的查詢在 ORDER BY 子句中使用索引列，因為這將導致更快的排序
沒有索引的連接太多。
這意味著連接正在執行全表掃描。為連接條件中使用的列添加索引將大大加快表連接
讀取第一個索引條目的速率很高。
這通常表示頻繁的全索引掃描。全索引掃描比表掃描快，但在大表中需要大量 CPU 周期，如果這些表具有或具有大量 UPDATE 和 DELETE，則運行“OPTIMIZE TABLE”可能會減少和/或加快全索引掃描的數量. 除此之外，只能通過重寫查詢來減少完整索引掃描。
從固定位置讀取資料的速率很高。
這表明許多查詢需要對結果進行排序和/或進行全表掃描，包括不使用索引的連接查詢。在適用的地方添加索引。
讀取下一個表格行的速率很高。
這表明許多查詢正在執行全表掃描。在適用的地方添加索引。
許多臨時表被寫入磁盤而不是保存在記憶體中。
增加 max_heap_table_size 和 tmp_table_size 可能會有所幫助。然而，一些臨時表總是被寫入磁盤，與這些變數的值無關。要消除這些，您必須重寫查詢以避免這些條件（在臨時表中：存在 BLOB 或 TEXT 列或存在大于 512 位元組的列），如 MySQL 檔案中所述
MyISAM 密鑰緩沖區（索引快取）使用的百分比很低。
您可能需要減小 key_buffer_size 的大小，重新檢查表以查看是否已洗掉索引，或者檢查有關正在使用哪些索引的查詢和預期。
開桌率很高。
打開表需要昂貴的磁盤 I/O。增加 table_open_cache 可能會避免這種情況。
檔案打開率很高。
考慮增加 open_files_limit，并在更改 open_files_limit 后重新啟動時檢查錯誤日志。
沒有立即授予太多表鎖。
優化查詢和/或使用 InnoDB 來減少鎖等待。
沒有立即授予太多表鎖。
優化查詢和/或使用 InnoDB 來減少鎖等待。
太多的連接被中止。
連接通常在無法獲得授權時中止。本文可能會幫助您追蹤來源。
與 InnoDB 緩沖池相關的 InnoDB 日志檔案大小不是合適的大小。
特別是在對 InnoDB 表進行大量寫入的系統上，您應該將 innodb_log_file_size 設定為 innodb_buffer_pool_size 的 25%。但是這個值越大，資料庫崩潰時的恢復時間就越長，所以這個值不應該設定得比 256 MiB 高很多。但是請注意，您不能簡單地更改此變數的值。您需要關閉服務器，洗掉 InnoDB 日志檔案，在 my.cnf 中設定新值，啟動服務器，然后檢查錯誤日志是否一切正常。另請參閱此博客條目
uj5u.com熱心網友回復：
升級——5.5 的支持大約在 7 年前結束。
Tip-top 建議：將您的表從 MyISAM 切換到 InnoDB。并且一定要key_buffer_size調低到 20M 或innodb_buffer_pool_size更高，但不要太高以至于導致交換。
最佳建議：使用您一直在捕獲的SlowLog 。消化它，讓我們討論一下“最糟糕”的幾個查詢。
好吧，也許慢日志中沒有什么東西（“慢查詢：0% (1/763K)”）。所以低long_query_time到0.4
查詢快取可能帶來的傷害大于幫助。這很難說。
MySQL 占用的 8MB 中沒有多少；你有其他應用程式在同一臺機器上運行嗎？更改記憶體大小不會緩解 CPU 問題。使用更多記憶體來提高table_open_cache,open_files_limit等可能對 CPU 有所幫助。
如果您不使用 MyISAM，請降低key_buffer_size到20M.
忽略關于“碎片表”的注釋。
關閉general_log; 它迅速填滿磁盤。（對 CPU 的影響不大。）
“Up for: 22m 39s”——分析主要集中在“啟動”活動上；因此不能充分解決您的問題。等待 24 小時。
更深入的分析：http: //mysql.rjweb.org/doc.php/mysql_analysis#tuning
uj5u.com熱心網友回復：
在開始之前，我會提到推薦串列中的所有內容都值得一看，我非常同意另一個答案，即 5.5 已經過時了——甚至沒有獲得安全補丁。升級是一個非常好的主意。
順便說一句，我想專注于這一點：
該資料庫多年來一直運行良好，沒有明顯的流量峰值或資料庫更改
考慮到這一點，我知道您會看到性能突然下降的兩個常見原因：
磁盤處于故障邊緣，因此某些磁盤 I/O 操作緩慢或重復。如果這是您的問題，您可能會在核心服務器（不是 MySql）日志中找到它的其他證據……但并非總是如此！
正如您所看到的資料增長——不僅僅是隨著時間的推移，而是在業務增長方面（希望企業擁有的客戶比安裝服務器時更多）——需要更多記憶體來將活動記錄緩沖在 RAM 中，而不是頻繁地從（慢得多）磁盤比以前。您可能會遇到一個臨界點，突然之間，許多以前完全在 RAM 中完成的查詢現在需要溢位到磁盤。
是的，是的，我知道這兩個都是磁盤問題，而問題表現為 CPU 使用。但是，磁盤 I/O 問題通常表現為 CPU 負載問題，行程在磁盤上等待完成。
這兩個問題的解決方案通常涉及硬體升級......但如果這是第二個原因，您可以通過處理您的問題中的建議串列來延遲此操作。
uj5u.com熱心網友回復：
Master00，既然您需要并希望繼續使用 MyISAM，請考慮以下建議來改善您對 MyISAM 的回應時間。
key_cache_block_size=16384 # from 1024 to reduce overhead managing key_buffer data
key_cache_division_limit=50 # from 100 percent to enable Hot / Warm cache separation
key_cache_threshold=7200 # from 300 seconds before AGE_OUT causes another READ
并且這三個更改將顯著降低您的 key_reads Rate Per Second。
有關其他有用的提示，請查看我的個人資料以獲取聯系資訊并取得聯系。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/428142.html