MYSQL 最佳化設定實作教學

最近要接案子了…有可能還同時兩個一起接到…
這篇以後架站也許用的到吧0w0



MYSQL 最佳化設定實作教學

前 言:

MYSQL的設定參考文件很少,在網上查到了一些相關的原文調整說明,也去翻了翻mysql相關的書
我自己在整理資籵的時候也覺得很多東西寫不出來,所以有些地方可能會寫的不是很完整。

#以下設定適用於1024M-2048M記憶體及2顆CPU的MYSQLDB，如記憶體更大的話請自行做調整。給各項參數增加記憶體配置量會加快速度，但是使用過多的記憶體而導致啟用swap的話，會嚴重導致系統執行效率下降。

#沒有修改參數，用預設值的記憶體使用量：約 40MB,使用my-large.cnf，記憶體使用量：約420MB但是可以提高DB的存取執行效率。

-- --------------------------------------------------------------設定說明------ -------------------------------------------------------

GLOBAL MEMORY與 THREAD MEMORY
MYSQL 再配置記憶體時分成兩各階段，一是當SERVER啟動時配置給整個系統使用，二是當CLINT連線進來的時候配置給單一連線使用。前者被稱為GLOBAL 後者被稱為THREAD，MEMORY的總用量簡單的說就是GLOBAL+(THREAD數*THREAD MEMORY) 這個公式所算出來的記憶體消耗應該要小於系統的總記憶體(這是廢話當然要小於囉)，但是實際上測試時，系統記憶體不足就會回應TOO MANY CONNECTION而暫停回應。但是大量的REQUEST很容易讓DB掛給你看。通常USED CONNECTION會大量增加，是因為TABLELOCK，導致新的QUERY被暫存，後來才發現減低TABLE LOCK的時間與次數，才是解決DB TIMEOUT卡死的方式。但是TABLE LOCK一般是因為SQL查詢寫的不好，可能需要調整程式裡的SQL語法，所以只好以縮短wait_timeout的時間做為應急的辦法。但是這會增加 CPU LOADING，所以調整要小心一點,慢慢TEST出最佳調整參數值。

TMP TABLE與TABLE CACHE
當 一個查詢所耗用的記憶體超過配置的BUFFER時，MYSQL會開啟暫存TABLE，暫存TABLE先放在記憶體中，記憶體不足再利用DISK，原理就像 是L1、L2、L3 CACHE。table_cache的參數定義了能夠CACHE多少個TABLE ，tmp_table_size是定義在記憶體中開?的暫存TABLE有多大，也就是說
，這是個全域的記憶體配置。TMP TABLE超過這個大小，就會被寫到硬碟上。 要比對這個參數是否太大要比較SHOW STATUS(mysql中查看運作狀況的指令)中的open_tables跟opened_tables數量，前者是目前所開啟的TABLE數，後者是 曾開啟的TABLE數，如果後者比前者大很多，表TABLE CACHE太小。可以試著調大數值。

wait_timeout
這個參數是由global wait_timeout 或是interactive_timeou繼承下來的。而且在COMMAND LINE時無法看到GLOBAL WAIT﹍TIMEOUT ，這個值會繼承自interactive_timeout 。

--------------MY.CNF(調整前的設定沒有做最佳化設定,所以2G記憶體只給了約40M給MYSQL用)-----------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb

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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

----------------------------------------------------------------------------------------------------------------------------------------

---------------------------MY.CNF(調整後的設定,2G記憶體指定了512M左右給MYSQL用)-----------------------[mysqld]
skip-innodb <---如果不用到innodb數據表，去掉innodb支援，節省記憶體
skip-bdb <---如果不用到bdb數據表，去掉bdb支援，節省記憶體。
skip-locking <---關閉外部鎖定可以提高資料表存取性能。
skip-name-resolve <---關閉DNS反查動作 。

datadir=/DB/data <---資料存放位置。
socket=/var/lib/mysql/mysql.sock <---sock檔位置。

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

[safe_mysqld]
err-log=/var/log/mysqld.log <---err-log存放位置。
pid-file=/var/run/mysqld/mysqld.pid <---PID檔位置。
open_files_limit=8192 <---允許MYSQL開啟系統檔案數上限。

[mysqld]
set-variable = back_log=50
set-variable = connect_timeout=10
set-variable = key_buffer=512M
set-variable = max_allowed_packet=4M
set-variable = table_cache=1024 <---允許暫存在CACHE裡的TABLE數量。
set-variable = thread_cache=64
set-variable = join_buffer_size=32M <---使用到JOIN時會用到，如果有大量Select動件時可視情況增加,提供執行效能。
set-variable = sort_buffer=32M <---用來排序的BUFFER，如果DB回傳大量的查詢結果而且又使用ORDER BY,增加這個BUFFER大小可以提升速度。
set-variable = record_buffer=32M
set-variable = max_connections=32000
set-variable = wait_timeout=120
set-variable = interactive_timeout=120 <---連線timeout時間。
set-variable = max_connect_errors=30000
set-variable = long_query_time=1
set-variable = max_heap_table_size=256M
set-variable = tmp_table_size=128M
set-variable = myisam_sort_buffer_size=128M
set-variable = max_allowed_packet=1M
set-variable = net_buffer_length=16K <---網路暫存BUFFER，TCP最大封包長度是16384。
set-variable = thread_concurrency=4 <---設置為你的cpu數目x2,例如，只有一個cpu,那麼thread_concurrency=2 <---有2個cpu,那麼thread_concurrency=4。

#set-variable = log_slow_queries=/var/log/log_slow_queries.log
#log-bin <---去掉log，提高性能。
server-id = 1

[isamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[mysqlhotcopy]
interactive-timeout

來源：http://tw.myblog.yahoo.com/black-lulu/article?mid=148