MySQL 調校-2
發表於 : 2005-01-02 23:53:14
http://parus.idv.tw/archives/cat_ccece.html#000147
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的調整才是徹底解決之道。
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的調整才是徹底解決之道。