Linux系統高負載 MySQL數據庫徹底優化

同時在線訪問量繼續增大對于1G內存的服務器明顯感覺到吃力，嚴重時，甚至每天都會死機或者時不時的服務器卡一下。這個問題曾經困擾了我半個多月，MySQL使用是很具伸縮性的算法，因此你通常能用很少的內存運行或給MySQL更多的備存以得到更好的性能。

安裝好mysql后，配制文件應該在/usr/local/mysql/share/mysql目錄中，配制文件有幾個，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf，不同流量的網站和不同配制的服務器環境，當然需要有不同的配制文件了。

一般的情況下，my-medium.cnf這個配制文件就能滿足我們的大多需要；一般我們會把配置文件拷貝到/etc/my.cnf ，只需要修改這個配置文件就可以了，使用mysqladmin vaRIAbles extended-status -uroot -p可以看到目前的參數，有3個配置參數是最重要的，即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只對MyISAM表起作用，key_buffer_size指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。一般我們設為16M,實際上稍微大一點的站點　這個數字是遠遠不夠的，通過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設置是否合理。比例key_reads / key_read_requests應該盡可能的低，至少是1:100，1:1000更好（上述狀態值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。 或者如果你裝了PHPmyadmin 可以通過服務器運行狀態看到,筆者推薦用phpmyadmin管理mysql，以下的狀態值都是本人通過phpmyadmin獲得的實例分析：

這個服務器已經運行了20天

key_buffer_size – 128M

key_read_requests – 650759289

key_reads - 79112

比例接近1:8000 健康狀況非常好

另外一個估計key_buffer_size的辦法　

把你網站數據庫的每個表的索引所占空間大小加起來看看以此服務器為例：比較大的幾個表索引加起來大概125M 這個數字會隨著表變大而變大。

從4.0.1開始，MySQL提供了查詢緩沖機制。使用查詢緩沖，MySQL將SELECT語句和查詢結果存放在緩沖區中，今后對于同樣的SELECT語句（區分大小寫），將直接從緩沖區中讀取結果。根據MySQL用戶手冊，使用查詢緩沖最多可以達到238%的效率。

通過調節以下幾個參數可以知道query_cache_size設置得是否合理

Qcache inserts

Qcache hits

Qcache lowmem prunes

Qcache free blocks

Qcache total blocks

Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況，同時Qcache_hits的值非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小Qcache_hits的值不大，則表明你的查詢重復率很低，這種情況下使用查詢緩沖反而會影響效率，那么可以考慮不用查詢緩沖。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。

Qcache_free_blocks，如果該值非常大，則表明緩沖區中碎片很多，query_cache_type指定是否使用查詢緩沖。

我設置：

QUOTE：

query_cache_size = 32M

query_cache_type= 1

table_cache指定表高速緩存的大小。每當MySQL訪問一個表時，如果在表緩沖區中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現open_tables等于table_cache，并且opened_tables在不斷增長，那么你就需要增加table_cache的值了（上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設置成很大的值。如果設置得太高，可能會造成文件描述符不足，從而造成性能不穩定或者連接失敗。

對于有1G內存的機器，推薦值是128－256。

筆者設置

QUOTE：

table_cache = 256

得到以下狀態：

Open tables 256

Opened tables 9046

雖然open_tables已經等于table_cache，但是相對于服務器運行時間來說，已經運行了20天，opened_tables的值也非常低。因此，增加table_cache的值應該用處不大。如果運行了6個小時就出現上述值那就要考慮增大table_cache。

如果你不需要記錄2進制log 就把這個功能關掉，注意關掉以后就不能恢復出問題前的數據了，需要您手動備份，二進制日志包含所有更新數據的語句，其目的是在恢復數據庫時，用它來把數據盡可能恢復到最后的狀態。另外，如果做同步復制( Replication )的話，也需要使用二進制日志傳送修改情況。

log_bin指定日志文件，如果不提供文件名，MySQL將自己產生缺省文件名。MySQL會在文件名后面自動添加數字引，每次啟動服務時，都會重新生成一個新的二進制文件。此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定記錄的數據庫；使用binlog-ignore-db可以指定不記錄的數據庫。注意的是：binlog-do-db和binlog-ignore-db一次只指定一個數據庫，指定多個數據庫需要多個語句。而且，MySQL會將所有的數據庫名稱改成小寫，在指定數據庫時必須全部使用小寫名字，否則不會起作用。

關掉這個功能只需要在他前面加上#號

QUOTE：

#log-bin

開啟慢查詢日志( slow query log )

慢查詢日志對于跟蹤有問題的查詢非常有用。它記錄所有查過long_query_time的查詢，如果需要，還可以記錄不使用索引的記錄。下面是一個慢查詢日志的例子：

開啟慢查詢日志，需要設置參數log_slow_queries、long_query_times、log-queries-not-using-indexes。

log_slow_queries指定日志文件，如果不提供文件名，MySQL將自己產生缺省文件名。long_query_times指定慢查詢的閾值，缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的參數，它指示記錄不使用索引的查詢。筆者設置long_query_time=10

筆者設置：

QUOTE：

sort_buffer_size = 1M

max_connections=120

wait_timeout =120

back_log=100

read_buffer_size = 1M

thread_cache=32

interactive_timeout=120

thread_concurrency = 4

參數說明：

免費空間因為它們是免費的，所以各種各樣的問題經常會出現。最擔心的問題就是免費空間是否穩定，是否會隨便刪除用戶賬號和數據。其次就是空間連接速度問題，最后就是免費空間能否正常安裝一些程序問題了。

以免費資源部落在前一段時間給大家推薦的Sitemix.jp穩定1.5GB免費空間，這個空間穩定性是不錯，已經穩定提供免費服務有幾年了，最大的優點就是空間訪問速度快，比那些美國免費空間快了近一倍。

Sitemix.jp空間最大的缺點就是它嚴格限制了MySQL數據庫，甚至連基本的MySQL數據庫管理功能和創建MySQL數據庫等都沒有提供，但是它還不是一個靜態空間，支持PHP，可以采用默認的MySQL數據庫來安裝Wordpress等等。

針對這類的限制數據庫管理和數據庫創建的免費空間，除了突破它們的數據庫權限外（這外方法應該很冒險，不推薦采用），還有一個更好的方法就是采用免費遠程的MySQL數據庫。既然免費空間都有了，免費數據庫自然也有老牌免費的。

要不是Sitemix.jp空間速度快，否則我們也不會去折騰它了，廣告又多又大不說了，限制數據庫是最要命的。當然也有一些空間和數據庫都表現好的空間：

back_log

要求MySQL能有的連接數量。當主要MySQL線程在一個很短時間內得到非常多的連接請求，這就起作用，然后主線程花些時間(盡管很短)檢查連接并且啟動一個新線程。back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊列的大小。你的操作系統在這個隊列大小上有它自己的限制。 Unix listen(2)系統調用的手冊頁應該有更多的細節。檢查你的OS文檔找出這個變量的最大值。試圖設定back_log高于你的操作系統的限制將是無效的。

max_connections

并發連接數目最大，120 超過這個值就會自動恢復，出了問題能自動解決

thread_cache

沒找到具體說明，不過設置為32后 20天才創建了400多個線程而以前一天就創建了上千個線程 所以還是有用的

thread_concurrency mysql數據庫

#設置為你的cpu數目x2,例如，只有一個cpu,那么thread_concurrency=2

#有2個cpu,那么thread_concurrency=4

skip-innodb

#去掉innodb支持

Example MySQL config file for medium systems.