OLTP場景伺服器配置建議

當伺服器出現瓶頸時，對伺服器的配置參數調整往往不是優先級最高的。一般來講優化的效果是：SQL及索引>資料庫表結構>系統配置>硬體，而優化的成本卻恰好反過來，此外，對于大部分參數配置，mysql預設設定已經夠用，也許調整後會有一些小提升，但可能也為其他方面的問題埋下隐患，因而伺服器參數配置的一個原則應該是如果非必要，盡量不要随便調整參數。

對于OLTP場景，一般是高并發，事務送出要求小而快，屬于cpu密集型，同時讀寫頻繁，對于資源的競争需要處理好，對性能要求較高。

對于OLAP場景，需要對大量資料進行查詢搜集分析，對送出速度要求不如OLTP，應該盡量追求處理速度（就是說不要求你馬上送出，因而可以盡量使用臨時表等有幫助的手段提升資料處理過程的速度），由于需要處理大量資料，因而對硬碟io要求較高，屬于io密集型。

OLTP場景配置

*硬體配置：

*CPU配置：對于OLTP場景多核處理器很有優勢，現在的mysql一般可以在16~24核心cpu上運作較好，是以建議盡量使用更多核的處理器。

*記憶體配置：一般來講要保證io等待的時間維持在可接受範圍内選擇記憶體大小。比如說在80%cpu使用率下，用于io等待的時間為1%，那麼側面說明緩存命中率還不錯，記憶體應該夠用，如果io等待占比較大的話，就應該考慮是否增大記憶體了(不過首先應該考慮的是mysql配置，查詢，索引或者其他方面是否有異常，最後才考慮增加記憶體的問題)。

*硬碟配置：一般來講使用多塊硬碟組成的raid磁盤陣列，如果經費允許盡量使用ssd。對于oltp來說一般要保證安全性的話，并且對并發性速度有一定要求，則選擇raid5，資料量大的話可以選擇raid50，這種選擇在磁盤損壞重建的時候會導緻性能下降，如果鈔票實在夠多那也可以選擇raid10.。

此外，對于資料庫來說，硬碟具有斷電保護（帶有電池）的緩存功能很有必要，既可以提高讀寫速度，也可以一定程度保證斷電時的資料安全。

*基本配置：

*innodb：當innodb不能啟動時是否啟動伺服器，對于OLTP來說innodb引擎是必須的，因而為了保證伺服器安全性這個選項很有必要。

*default_storage_engine:預設存儲引擎，設定為innodb。對于要求支援事務、高并發讀寫來說是最好的選擇。

*max_connections:在高并發場景時挺有用，當伺服器負載較大時新增連接配接沒有任何作用 ，因為沒有足夠資源配置設定給這些連接配接，這樣的話不如從一開始就限制能夠達到的連接配接數量上限，以免高并發帶來的太多連接配接拖垮伺服器。一般來說設定為比平時正常負載多出百分之30左右。

*skip_name_resolve：禁用了DNS查找環節，如果查找失敗可能導緻連接配接逾時，最好禁用掉，将授權改為IP位址，通配符。

*thread_cache_size：當連接配接釋放而cache size有足夠空間時，放進該cache中。當有新連接配接時從該cache中取出存放的thread。可以節約thread建立和釋放的資源，一般來講根據并發連接配接的波動設定，比如說500~700的連接配接波動那麼cache size為200~250之間比較合适。

*innodb_stats_persistent:将統計資訊持久化，可以避免啟動時重新收集統計資訊。同時可以通過關閉innodb_stats_on_metadata來避免統計資訊自動更新導緻的io問題，通過周期性執行analyze table來更新統計資訊。

*innodb_file_per_table:一般來講建議設定，因為共享表空間修改或者空間回收等都需要導出資料，修改配置，重新開機，重建新的資料檔案然後導入資料這些步驟，非常麻煩，而ibd檔案可以直接用rm删除。

*read_only&slave_net_timeout：禁止非特權使用者在備庫做變更，設定為隻讀模式；備庫連接配接失敗等待重連的時間，預設是1小時，改為1分鐘或者更短。

*max_connect_errors:最大連接配接錯誤次數，超過了該連接配接會被禁用一段時間，一般來說伺服器安全性較好的應該盡量設定大一些。

*transaction-isolation：對于OLTP設定為預設的可重複讀。

*記憶體參數配置：

*innodb_buffer_pool_size:對于已innodb引擎為主的伺服器來說是最重要的參數，對于OLTP的高并發來說更是如此，占了伺服器記憶體大部分的配置設定，一般來講是75~85%（不過輝哥告訴我他們生産環境一般用50%）。具體配置設定政策可以根據以下步驟分析：1.配置設定給作業系統和其他必須程序的記憶體，一般來說2~3G。2.配置設定給mysql自身的記憶體，不多。3.配置設定給innodb日志緩存，binlog緩存，key cache，查詢緩存等重要緩存。把剩下的記憶體乘以90%(保留一些餘地總是好的)就是配置設定給buffer pool的緩存。

此外對于high concurrency來說，innodb_buffer_pool_instance也是一個重要參數，可以設定多個獨立FREELIST,LRU,FLU以及mutex的buffer pool執行個體進而減少對緩沖的競争。根據網上查詢的資料一般是buffer pool size/2GB（官網上建議是8GB每執行個體），不過我覺得預設8執行個體已經足夠用了。

*innodb_adaptive_hash_index:是否使用自适應hash索引。會對經常出現，對某一索引使用同一規範的查詢建立hash索引，由于索引在記憶體(buffer pool)中，因而大大提高查詢效率，一般來講對于高并發應用有比較好的效果。

*innodb_log_buffer_size：事務日志的大小不必設定的太大，一般來說1~8M就夠了（除非是大型事務或者有BLOB等），因為對于緩存重新整理設定innodb_flush_log_at_trx_commit來說，最寬松的重新整理機制也是一秒就重新整理一次，8M就足夠1s間使用了。重新整理機制的選擇需要在高性能和資料安全性之間做考量，如果嚴格的重新整理機制(每次事務送出就重新整理)導緻目前負載壓力較大可以考慮設定為0或者2。

*table_cache_size：包括table_definition_cache和table_open_cache。對于高并發應用來說應該設定的足夠大，這樣可以避免每次連接配接都需要重新打開表和解析表，一般來講設定為max_connections的10倍，也就是10000左右。不過對于innodb來說他又自己的檔案描述符緩存，由innodb_open_files控制數量,如果設定了innodb_file_per_table，就應該把innodb_open_files設定的足夠大已保持所有ibd檔案打開。

*key_buffer_size：MyISAM引擎表所使用的鍵緩存，對于以innodb為主的伺服器來說設定一個較小的數比較合适，一般來說是把庫中所有myisam索引檔案大小加起來的值，但是也不能沒有，因為mysql系統表基本都是MyISAM。

*tmp_table_size&max_heap_table_size：對于OLTP來說事務一般不大，保持created_tmp_disk_tables/created_tmp_tables維持在一個較小的百分比即可，比如3%以下，一般來說設定64M應該夠用了。

query_cache_size:對于高并發且小而快的查詢比較重要，可以在緩存中存儲較多這種的查詢，進而可以直接再此傳回結果而不需要進行解析及以後的步驟。不過對于innodb來說，事務影響的表相關的查詢都會從緩存中删除，是以對于讀寫頻繁的OLTP系統最好還是關閉查詢緩存。

*I/O參數配置：

*innodb_log_file_size：一般來講最少要記錄一個小時内的活動内容，可以根據每秒寫入日志的速度來估計log的大小，對于高并發伺服器來說至少需要2G或以上的大小。

*innodb_io_capacity：innodb重新整理資料頁有兩種狀态，一種是每10s循環時如果io壓力不大則重新整理髒頁到硬碟，一種是當buffer pool不夠用時重新整理LRU中的髒頁。這個值不應設的太高，不然會導緻周期性io負載過大。可以根據測試伺服器硬碟能夠承受的io能力決定，一般來講10000~20000左右。

*innodb_read_io_threads&innodb_write_io_threads：當負載較大且有較多硬碟時可以把這兩個參數從預設的4提高，一般來講提高至跟硬碟數量相同，可以提高硬碟io效率。

*innodb_flush_method：把資料從記憶體重新整理到硬碟的方式，一般來講類UNIX系統設定為O_DIRECT,即重新整理時不使用系統緩存，innodb自己已有緩存，使用系統緩存沒什麼意義還降低速度，并且若資料處于系統緩存時崩潰這對于innodb來說是未知的，這樣會導緻很糟糕的結果。

*binlog_sync:對于OLTP應該盡量設定為1，盡管這樣會拖累io（不僅修改資料，還要修改檔案大小等中繼資料），可以通過帶電池保護的寫緩存raid來補償一下。當然如果io負載實在過大，可以考慮調高該參數。該參數一般與innodb_flush_log_at_trx_commit配合使用，這裡涉及到日志寫入順序的問題，感興趣的可以去查查。