MySQL佔用過多CPU

下面是我的 .cnf 文件，如果我看到我的 CPU 消耗 99.99% 是我的 mysqld 命令。MySQL 伺服器與經常更新其中數據的遠端電腦連接，但我確保遠端伺服器打開連接、讀/寫/更新然後關閉它。遠端伺服器也讀了很多。

我能做些什麼來減少我的cpu消耗。僅供參考，我使用的是 2 核 CPU 和 4GB RAM。

[client]
port          = 3306
socket      = /var/run/mysqld/mysqld.sock

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

[mysqld]
user          = mysqluser
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
wait_timeout    = 20
interactive_timeout = 60

bind-address            = <IP-ADDRESS>
key_buffer            = 16M
max_allowed_packet  = 16M
thread_stack            = 192K
thread_cache_size   = 8
myisam-recover      = BACKUP
max_connections     = 300

query_cache_limit   = 20M
query_cache_size        = 128M

log_error = /var/log/mysql/error.log
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 4
log-queries-not-using-indexes

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

還粘貼來自 mysqltuner 的輸出。

>>  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 from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 2G (Tables: 26)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 26

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 51s (37K q [28.525 qps], 31K conn, TX: 6M, RX: 7M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 304.0M global + 2.7M per thread (5000 max threads)
[!!] Maximum possible memory usage: 13.4G (347% of installed RAM)
[!!] Slow queries: 18% (6K/37K)
[OK] Highest usage of available connections: 0% (16/5000)
[OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K
[OK] Query cache efficiency: 20.2% (7K cached / 36K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[OK] Temporary tables created on disk: 25% (54 on disk / 215 total)
[OK] Thread cache hit rate: 99% (16 created / 31K connections)
[OK] Table cache hit rate: 25% (74 open / 289 opened)
[OK] Open file limit used: 0% (49/25K)
[OK] Table locks acquired immediately: 100% (29K immediate / 29K locks)
[!!] InnoDB  buffer pool / data size: 128.0M/2.9G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
   Run OPTIMIZE TABLE to defragment tables for better performance
   MySQL started within last 24 hours - recommendations may be inaccurate
   Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
 *** MySQL's maximum memory usage is dangerously high ***
 *** Add RAM before increasing MySQL buffer variables ***
   innodb_buffer_pool_size (>= 2G)

我根本沒有遇到這個問題，但是當我開始將 10 個遠端伺服器連接到這個 MySQL 伺服器時，當這 10 個伺服器開始在這個 MySQL 伺服器上執行SELECT和UPDATE查詢時，我看到 CPU 消耗的增加。

我懷疑您這裡有幾個問題正在堆積並導致問題。

1. 磁碟 i/o 是碎片化的，並且可能效率低下。每個表上的 OPTIMIZE TABLE 應該對其進行排序並有所幫助。
2. 記憶體調整得很糟糕，可能會導致不得不頻繁交換的問題。
3. 慢查詢 - 這是您的高 CPU 使用率的主要嫌疑人。我會檢查你的慢日誌，看看你可以通過添加索引/重組查詢/等來優化什麼。可能有一個相當常見的查詢會進行數以萬計或數十萬次的比較，這會影響您的性能。

如果這些都沒有將性能提升到您想要的水平，那麼可能是時候擴展您的數據庫伺服器了。2 核並沒有告訴我任何關於機器處理能力的資訊，而且現在 4 GB 的記憶體非常小。

如需更具體的建議，請在您的描述中添加更多詳細資訊。SSD 還是旋轉磁碟？什麼CPU？什麼等級的公羊？多少查詢/分鐘？有多少客戶等？

引用自：https://serverfault.com/questions/684484