1:磁碟搜尋能力，以7200轉/秒來說，理論上每秒尋找7200次。這是沒有辦法改變的，只能用多個硬碟，或是分散儲存數據。
2:硬碟讀寫速度，這個速度非常的快，可以建立磁碟陣列，讓資料庫從多個硬碟上並行讀寫。
3:CPU負責處理記憶體中的資料，這是最常見的效能限制。
4:記憶體的限制.當cpu需要超出適合cpu buffer的資料時，buffer的頻寬就成了記憶體的一個瓶頸。不過現在記憶體大的驚人，一般不會出現這個問題。
對MYSQL
再做一次設定調整，索性把相關參數整理在這裡，這是mysql 4.0的參數設定。
幾個跟效能調校比較有關的參數，把心得與蒐集到的資料整理在下面：
（這些數字是BYTE，在my.cnf中可以還可以允許用K跟M，在指令列時則要用*1024代替K，*1024*1024代替M）
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時間，這裡有各小問題放在下面解釋
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的執行情況可以從三個地方觀察
LOG檔、show status;(SQL command)、show variables; show table status;
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才會有效，如果是執行中而且不能restart ，那就只能用set指令：
set global 參數=值 （不能用文字 1M = 1*1024*1024）
set global是表示套用在所有的Thread上
set 會作用在現行的Thread上
(但是我沒辦法更改TIME OUT時間，他會自己跳回去，還不知道為什麼)
比較常更動的會是log的目錄與max_connect還有 key_buffer，在摸索的時候一次調整一個參數就好，並留下原本的設定檔以防萬一。
沒有留言:
張貼留言