MySQL 調校

From:
(第一個網站應該是原作者,不過網站沒了。第二、三個網頁是有人複製下來的)

Tuning Mysql
這兩天都把精神花在調整MY SQL SERVER上
因為是調整參數設定而不是SQL語法
所以把這篇列在這一個項目
(其實我覺得我的分類不是分的很好,但是 算了)
講到TUNGING,就要推薦一下O'reilly所出版的『High Performance MySQL'
不過這本書沒有中譯版,英文不好的人就辛苦點
OK 廢話了這麼多,該談一下這兩天的心得
就是...沒事不要隨便亂改設定值
除非你很確定你改了什麼
這是調整DB的最重要 最重要條件
MySQL的執行情況可以從三個地方觀察
LOG檔、show status;(SQL command)、show variables;
當然還有show table status;
但是,這通常都是小問題
大問題會出現在show variables;所出現的參數中
show status;則是觀察目前的SERVER狀態
會列出很長一段變數詳細內容請參考MYSQL手冊或另一本相關書籍
這裡只列出幾個觀察指標
Max_used_connection => 建立連線的最大數目,
這個數字要跟show variables;的Max_connection參數對照,
如果使用的連線數目已經到達最大,可以考慮放寬
但是每一個connection會多消耗記憶體
記憶體的消耗數量簡易計算方式是:
key_buffer + (sort_buffer + read_buffer) * max_connection
當然這只是簡單算法,省略不少比較小的項目
但是,這個等是算出來的值請保持不要超過實際記憶體
不然,MySQL會有hang住的危險
key_blocks_used使用的KEY BUFFER以BLOCK計算(1024-byte)
與key_buffer的設定相比較可以適度增減
Open_tables與Opened_tables
目前所開啟的table與曾經開啟的TABLE=>完美的情況下兩者應該相同,若Opened超出太,請放大table_cache
Table_locks_immediate與Table_locks_waited
這組數字隱含了SQL的效率,完美情況是waited=0。
但是,不太可能。
如果waited的數字很高甚至比immediate還高,就暗示了SQL寫的不好
Thread_connected與Threads_created
created高太多就表示CPU都在新增Thread,試著縮短Time out時間
調整參數最常用的方式是更改my.cnf(通常在/etc下)
但是要重新啟動MYSQL才會有效
如果是在跑得DB,而且不能restart
(就像我一樣 @@)
那就只能用set指令
set global 參數=值 (不能用文字 1M = 1*1024*1024)
set global是表示套用在所有的Thread上
set 會作用在現行的Thread上
(但是我沒辦法更改TIME OUT時間,他會自己跳回去,還不知道為什麼)
一般比較常更動的會是log的目錄與max_connect還有
key_buffer
在摸索的時候,記得一次調整一個參數就好
並留下原本的設定檔,以防萬一。
MYSQL再調整
對MYSQL再做一次設定調整
索性把相關參數整理在這裡
這是mysql 4.0的參數設定
原始網頁
幾個跟效能調校比較有關的參數,把心得與蒐集到的資料整理在下面
(這些數字是BYTE,在my.cnf中可以還可以允許用K跟M,在指令列時則要用*1024代替K,*1024*1024代替M)
back_log 50
basedir /usr/local/mysql
bdb_cache_size 8388572
<=BDB字首代表BDB的DATABASE相關參數,為我只用MYISAM格式所以跳過
bdb_home /usr/local/mysql
bdb_log_buffer_size 32768
bdb_logdir
bdb_max_lock 10000
bdb_shared_data OFF
bdb_tmpdir /tmp/
bdb_version Sleepycat Software: ...
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1 big5 czech euc_kr
concurrent_insert ON
connect_timeout 5
<=MYSQLD用來判斷此連線是否有效的時間,建議預設如果網路緩慢可以調高
convert_character_set
datadir /usr/local/mysql/data/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
have_bdb YES
have_innodb YES
have_isam YES
have_openssl YES
have_query_cache YES
have_raid NO
have_symlink DISABLED
init_file
innodb_additional_mem_pool_size 1048576
<=INNODB格式資料庫的設定參數,一樣跳過
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_fast_shutdown ON
innodb_file_io_threads 4
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_mirrored_log_groups 1
innodb_thread_concurrency 8
interactive_timeout 28800
<=用COMMAND LINE方式連線時,例如用MYSQL連線,允許的IDLE時間
join_buffer_size 131072
<=使用到JOIN時會用到,暫存搜尋結果用有大SELECT時要視情況增加,此為THREAD BASE BUFFER,就是每個連線都會多配置這個大小的記憶體
key_buffer_size 16773120
<=主暫存區大小所有THREAD共用
key_cache_age_threshold 300
key_cache_block_size 1024
<=key cache一個block的大小
key_cache_division_limit 100
language /usr/local/mysql/share/...
large_files_support ON
local_infile ON
locked_in_memory OFF
log OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_update OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_table_names 0
max_allowed_packet 1047552
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 100
<=允許最大連線數,正式環境絕對遠超過,要視系統記憶體大小增加,過多會導致系統垮掉
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_sort_length 1024
max_tmp_tables 32
<=允許的暫時TABLE數目
max_user_connections 0
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_recover_options force
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
net_buffer_length 16384
<=網路暫存BUFFER,16384是TCP最大封包長度
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
open_files_limit 1024
<=允許MYSQL開啟的系統檔案數上限
pid_file /usr/local/mysql/name.pid
port 3306
protocol_version 10
query_cache_limit 1048576
query_cache_size 0
query_cache_type ON
read_buffer_size 131072
<=讀取資料的BUFFER大小,THREAD BASE會影響SQL速率
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_net_timeout 3600
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 2097116
<=用來排序的BUFFER,如果回傳大的結果又使用ORDER BY加大這個BUFFER可以提升速度
sql_mode
table_cache 64
<=允許暫存在CACHE裡的TABLE數量
table_type MYISAM
thread_cache_size 3
thread_stack 131072
timezone EEST
tmp_table_size 33554432
<=暫存在記憶體中的暫存TABLE大小
tmpdir /tmp/:/mnt/hd2/tmp/
tx_isolation READ-COMMITTED
version 4.0.4-beta
wait_timeout 28800
<=這個連線的TIMEOUT時間,這裡有各小問題放在下面解釋
MYSQL的設定參考文件很少
案例也很少
我自己在寫這篇東西的時候也覺得很多東西寫不出來
只好在解釋變數內容之後分幾個主題
簡單說一下看法
GLOBAL MEMORY與 THREAD MEMORY
MYSQL再配置記憶體時分成兩各階段,一是當SERVER啟動時
配置給整個系統使用,二是當CLINT連線進來的時候配置給單一連線使用。
前者被稱為GLOBAL後者被稱為THREAD,
MEMORY的總用量簡單的說就是GLOBAL+(THREAD數*THREAD MEMORY)
這個公式所算出來的記憶體消耗應該要小於系統的總記憶體,但是實務上測試時,系統記憶體不足就會回應TOO MANY CONNECTION而暫停回應。但是,源源不絕的REQUEST很容易讓系統死當。通常USED CONNECTION會大量增加,是因為TABLE LOCK,導致新的QUERY被暫存,減低TABLE LOCK的時間與次數,才是解決問題之道。
但是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中的
open_tables跟opened_tables,前者是目前所開啟的TABLE數,後者是曾經開啟的TABLE數,如果後者比前者大很多,表示TABLE CACHE太小。可以試試放大。
wait_timeout
這個參數是由global wait_timeout 或是interactive_timeou繼承下來的。
而且在COMMAND LINE時無法看到GLOBAL WAIT﹍TIMEOUT
,這個值會繼承自interactive_timeout
調整MYSQL,特別是線上的MYSQL,得要很有耐心的不斷嘗試。知道每個變數的意義,然後就是要改一下,測一下。
但是,系統調整也是有極限,SQL的調整才是徹底解決之道。

Please follow and like us:

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *