MySQL 設定相關參數

???back_log 50 <=要求mysql能保有的連接數量。back_log指出在mysql暫停接受連接的時間內，有多少個連接請求可被存在佇列中
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時間，這個參數是由global wait_timeout 或是interactive_timeou繼承下來的。而且在COMMAND LINE時無法看到GLOBAL WAIT﹍TIMEOUT，這個值會繼承自interactive_timeout

• Keywords : mysql, local, 4294967295, 8388608, 1048576, 131072, wait_timeout, latin1, BUFFER, 32768, 28800, 16384, 過多會導致系統垮掉, 連接請求可, 要視系統記憶體大小增加, 要視情況增加, 要求mysql能保, 網路緩慢可以調高, 網路暫存BUFFER, 系統檔案數上限

2009-05-20T13:00:50+0000