Mysql優化幫助

我已經嘗試過盡可能好地優化 mysql，但顯然我不擅長它:-)

所以我來找大家幫忙。

伺服器規格為： AMD Opteron CPU 8 核 16 GB RAM 2x2.000 GB HDD 7.200 RPM 軟體 raid 1 Cloudlinux + Cpanel 託管大約 60 個網站，其中一半由 Joomla 驅動 上個月的總流量：218 GB（傳入和傳出）

我目前的 my.cnf 看起來像這樣：

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-innodb
query_cache_limit=64M
query_cache_size=64M
query_cache_type=1
max_user_connections=100
max_connections=150
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=256M
join_buffer=4M
max_allowed_packet=128M
table_cache=163840
table_definition_cache=163840
#record_buffer=1M
sort_buffer_size=2M
read_buffer_size=8M
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=64M
server-id=1
innodb_buffer_pool_size=6G 
innodb_file_per_table=1
tmp_table_size=3G
max_heap_table_size=3G
low_priority_updates=1
concurrent_insert=ALWAYS
log-slow-queries=/var/lib/mysql/slow.log
log-queries-not-using-indexes = /var/log/mysql-indexes.log

#[mysql.server]
#user=mysql
#basedir=/var/lib 

open_files_limit=50000
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192 

#[mysqldump]
#quick
#max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M 

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

mysqltuner 的輸出是這樣的：

>> MySQLTuner 1.2.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

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.30-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3186)
[--] Data in InnoDB tables: 250M (Tables: 8508)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 266)
[!!] Total fragmented tables: 40

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 5h 15m 11s (6M q [34.844 qps], 157K conn, TX: 43B, RX: 1B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 5.1G global + 14.5M per thread (150 max threads)
[OK] Maximum possible memory usage: 7.2G (46% of installed RAM)
[OK] Slow queries: 0% (169/6M)
[OK] Highest usage of available connections: 26% (39/150)
[OK] Key buffer size / total MyISAM indexes: 256.0M/187.6M
[OK] Key buffer hit rate: 99.9% (357M cached / 236K reads)
[OK] Query cache efficiency: 82.0% (4M cached / 4M selects)
[!!] Query cache prunes per day: 95307
[OK] Sorts requiring temporary tables: 1% (2K temp sorts / 233K sorts)
[!!] Joins performed without indexes: 3349
[!!] Temporary tables created on disk: 38% (186K on disk / 479K total)
[OK] Thread cache hit rate: 99% (39 created / 157K connections)
[OK] Table cache hit rate: 32% (24K open / 74K opened)
[OK] Open file limit used: 2% (9K/327K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 250.3M/1.8G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
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
Variables to adjust:
query_cache_size (> 64M)
join_buffer_size (> 4.0M, or always use indexes with joins)

我注意到 90% 的交換正在被使用，而伺服器有超過 8GB 的​​可用 RAM。

total       used       free     shared    buffers     cached
Mem:         15851      15733        118          0        569       8094
-/+ buffers/cache:       7069       8782
Swap:         4047       3664        383

通常伺服器負載保持在 0.5-0.8 左右，但是當負載峰值發生時，它會上升到 5.00-6.00 並保持這種狀態，直到我重新啟動 mysql，之後它恢復正常（直到下一個峰值）。

至於 PHP，我使用 fcgi 作為處理程序和 Nginx（通過 nginxcp）。我已經嘗試刪除 nginxcp，認為這可能是原因，但仍然出現峰值。

伺服器上的所有軟體都是最新的，包括 Joomla 網站。

Fcgi 使用以下設置進行配置，根據 Cloudlinux 建議進行：

FcgidMinProcessesPerClass 0
FcgidMaxProcessesPerClass 16
FcgidMaxProcesses 300
FcgidIdleTimeout 60
FcgidProcessLifeTime 120
FcgidIdleScanInterval 30

我還檢查了網路問題或 DDOS 攻擊，但事實並非如此。硬體是全新的並且處於完美狀態。那會是什麼？

希望有人能幫忙，我將永遠感激不盡。

謝謝。

在 MySQL 上使用多核時，單個互斥鎖將在查詢記憶體上產生爭用。在 my.cnf 中設置 query_cache_type=0 和 query_cache_size=0。

接下來，讓innodb_buffer_pool_size接近數據庫的大小；看起來你還有記憶。這有助於減少整體 IO。

在 my.cnf 之外，訪問伺服器的查詢應該使用索引。如果沒有，他們可以創建更多的 IO。最後，為了處理高 IO，您希望將 IO 分散到磁碟驅動器上。第一個候選者是您的二進制日誌。如果 /var 掛載在 /dev/sda1 上，則添加 log_bin = {directorymounted on /dev/sda2}。

提高 innodb_buffer_pool_size 以在您的機器上使用更多的 16GB。太高了，你的記憶體用完了。查看您的盒子上可用的可用記憶體，並將其用作您可以添加的總量的指南。這是我能看到你能做的最重要的事情。

但是您擔心 5.00-6.00 的負載。8核沒問題。（見http://blog.scoutapp.com/articles/2009/07/31/understanding-load-averages）

為了您的舒適，只需在 CPU 達到平均負載時嘗試使用網站。如果您的響應時間合適，一切都很好。

希望這會有所幫助，亞當 C. 斯科特

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