MySQL MyISAM / PHP 高並發最佳化經驗

MySQL MyISAM / PHP 高並發最佳化經驗  

最近做的一個應用，功能要求非常簡單，就是 key/value 形式的儲存，簡單的 INSERT/SELECT，沒有任何複雜查詢，唯一的問題是量非常大，如果目前投入使用，初期的單表 insert 頻率約 20Hz（次/秒，我喜歡這個單位，讓我想起國內交流電是 50Hz），但我估計以後會有 500Hz+ 的峰值。目前的工作成果，額定功率 200Hz（CPU 佔用 10 - 20，load avg = 2），最大功率 500Hz（這時 load avg > 20，很明顯，只能暫時挺挺，應該在出現這種負載前提前拆表了）

INSERT DELAYED INTO

從資料的插入開始說起。如果可以容忍結果幾秒以後再生效的，可以用 INSERT DELAYED INTO，因為在我的這個結構中不需要對同一個 key 頻繁的 INSERT/SELECT，因為 SELECT 我是用 Memcached 擋住了，除非 Memcached 掛了，或者資料實在老到到期了，才會去 SELECT。而且要注意，如果 PHP 不需要關心 MySQL 操作的返回結果，應該使用 unbuffered query，簡單的說，在你提交 query 後，不用等待 MySQL 有返回資訊就繼續執行之後的 PHP 指令，具體用法是用 mysql_unbuffered_query 代替 mysql_query，如果用的 MySQLi 類，應該使用 mysqli->query($sQuery, MYSQLI_USE_RESULT);

如果 SHOW PROCESSLIST，可以看到使用者名稱為 DELAYED 的進程，進程數量等於 INSERT DELAYED 的表的數量，因為表級鎖的存在，每個表一條以上的 DELAYED 進程是沒有意義的

關於這個功能的 my.cnf 配置有三條，我定為如下值

delayed_insert_limit = 1000
delayed_insert_timeout = 300
delayed_queue_size = 5000

串連

有人說，如果報錯串連數過大，你把 max_connections 調大就 OK，如果只這麼說而不講原因，完全是句廢話，你調成 1M 肯定不會再報 Too many connections（但應該會報記憶體溢出之類的），但如果是這樣 MySQL 又何必給這個參數？

我看到的一個很有用的公式

key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections

以前只有很模糊的概念，應該設的很大，但又不能太大，具體多大合適，知道這個就明確了。innoDB 的公式比這個複雜點，一併給出

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections * ( sort_buffer_size + read_buffer_size + binlog_cache_size )
+ max_connections * 2MB

還有一個看起來很有用的參數 back_log，給我一種串連池的感覺，而且它確實在起作用，我不知道如果設大了會佔用多少記憶體，但估計不會很多。

key_buffer_size

很多文章都告訴你越大越好，要為此分配一半左右的實體記憶體，這麼幹通常不會出問題，但肯定不是最優的，甚至可以說很無理頭——分多少記憶體應該是根據需求決定，而不是不管什麼機器，都砍掉一半記憶體用作 key_buffer_size ——有的時候可能是不夠，還有的時候可能是浪費。

其實最關鍵的指標，還是看 SHOW GLOBAL STATUS 時的 Key_blocks_unused，只要還有剩餘，就說明 key_buffer_size 沒用滿。有人說看 Key_reads 跟 Key_read_requests 的比值，至少要達到 1:100。這可以作為一個結果來衡量，但不夠準確，因為在伺服器剛啟動的時候，大多數請求都要建立緩衝，快取命中比高不起來，需要運行穩定（幾小時後）再觀察。我個人建議還是看 Key_blocks_unused

如果不花很長時間在運行中調試，給出一個簡單的計算方法，把資料庫填滿，達到設計時的最大值，看看這時候索引佔了多大空間，然後把所有表的索引大小加起來，就是 key_buffer_size 可能達到的最大值，當然，還要留些餘地，乘個 2 或 3 之類的。

這是我做測試的時候的 phpMyAdmin ，可以看到 key_buffer_size 被浪費了太多

OPTIMIZE TABLE

最佳化一下有好處，但會鎖住表，是否值得做要權衡一下。拿我現在這個表做例子，有 text 欄位，700萬條記錄，1.5G 大小，最佳化時間約兩分鐘，最佳化後效能提升了 50%，同時表的大小變為 1.4G，但隨著表的頻繁改寫，約一天后又恢複到以前的速度，因此在我看來並不值得。

Query Cache

因為每有寫操作 Query Cache 都會被清空，除了極特殊的情況（大量讀，少量寫，但即使這樣也應該是多用 memcached 才對）完全沒有必要使用這個，把 query_cache_size 設為 0 關閉這個功能吧