mysql參數最佳化

標籤：myisam   檔案   成本   sql資料庫   維護   大小   後台進程   log檔案   建議   

mysql參數最佳化

innodb_buffer_pool_size：

先寫入innodb_log_bufferbuffer寫滿或事務提交，重新整理資料大事務頻繁，增加innodb_log_buffer_size大小，對於單獨的MySQL資料庫伺服器推薦設為實體記憶體的75%

-------------------------------------------

innodb_buffer_pool_instances：

將innodb_buffer_pool劃分為不同的instance每個instance獨立的LRU、FLUSH、FREE獨立的mutex控制--------------------------------------------

innodb_log_file_size ：

在mysql 5.5和5.5以前innodb的logfile最大設定為4GB,在5.6以後的版本中logfile最大的可以設為512GB.
innodb的logfile就是交易記錄，用來在mysql crash後的恢複.所以設定合理的大小對於mysql的效能非常重要
在5.5的版本中，default設定為5M.在建立的mysql伺服器中，需要儘快修改該參數.

--------------------------------------------

innodb_log_buffer_size：

先寫入innodb_log_bufferbuffer寫滿或事務提交，重新整理資料大事務頻繁，增加innodb_log_buffer_size大小

--------------------------------------------

innodb_thread_concurrency(並發線程) ：

innodb_thread_concurrency = 0，innodb內部自己控制–kernel_mutex競爭–CPU環境切換innodb_thread_concurrency設定為cpu的核心數

--------------------------------------------

innodb_io_capacity ：

innodb每秒後台進程處理IO操作的資料頁上限innodb_buffer_pool_size總的io處理能力上限innodb_buffer_pool_instances分割成多個記憶體塊時，每個記憶體塊的IO處理能力為：innodb_io_capacity/innodb_buffer_pool_instances--------------------------------------------

innodb_max_dirty_pages_pct ：

innodb從innodb buffer中重新整理髒頁的比例重新整理髒頁，產生checkpoint髒頁重新整理innodb_max_dirty_pages_pct * innodb_io_capacity---------------------------------------------

innodb_flush_method ：

O_DSYNC：使用O_SYNC開啟和重新整理log檔案，使用fsync()重新整理資料檔案。O_DIRECT：使用O_DIRECT開啟資料檔案，使用fsync()重新整理記錄檔和資料檔案。在raid裝置上，為了避免資料被innodb_buffer和raid多次cache，設定為O_DIRECT方式。

---------------------------------------------

innodb_file_per_table ：

不同的資料表空間可以靈活設定資料目錄的地址避免共用資料表空間產生的IO競爭

------------------------------------------

innodb_flush_log_at_trx_commit  :

0:每秒將log buffer的內容與交易記錄並資料刷盤;-----------------------最快資料最不安全

1:每個事務提交後，將log_buffer的內容寫交易記錄並資料刷盤;-----------最慢最安全

2:每個事務提交後，將log_buffer的內容寫交易記錄，但不進行資料刷盤;---折中

------------------------------------------

sync_binlog :

請注意如果在autocommit模式，每執行一個語句向二進位日誌寫入一次，否則每個事務寫入一次。 預設值是0，不與硬碟同步。值為1是最安全的選擇，因為崩潰時，你最多丟掉二進位日誌中的一個語句/事務；但是，這是最慢的選擇。

雙1模式，即innodb_flush_log_at_trx_commit=1，sync_binlog=1 這樣主備庫的資料是一致的，不會遺失資料。（在此請問你考慮到IO負載了嗎？）

 

當sync_binlog=N時：

N>0   每向二進位記錄檔寫入N條SQL或N個事務後，則把二進位記錄檔的快取資料重新整理到磁碟上；

N=0   不主動重新整理二進位記錄檔的資料到磁碟上，而是由作業系統決定；

推薦配置組合：

N=1,1   適合資料安全性要求非常高，而且磁碟IO寫能力足夠支援業務，比如儲值消費系統；

N=1,0   適合資料安全性要求高，磁碟IO寫能力支援業務不富餘，允許備庫落後或無複製；

N=2,0或2,m(0<m<100)    適合資料安全性有要求，允許丟失一點交易記錄，複製架構的延遲也能接受；

N=0,0   磁碟IO寫能力有限，無複製或允許複寫延遲稍微長點能接受，例如：日誌性登記業務；

----------------------------------------

key_buffer_size ：

key_buffer_size只能緩衝MyISAM或類MyISAM引擎的索引資料，而innodb_buffer_pool_size不僅能緩衝索引資料，還能緩衝中繼資料，但是對於我們只使用InnoDB引擎的資料庫系統而言，此參數值也不能設定過於偏小，因為暫存資料表可能會使用到此金鑰快取區空間，索引緩衝區推薦：64M

----------------------------------------

query_cache_type  and query_cache_size ：

query_cache_type=N：

N=0  —- 禁用查詢快取的功能；

(有人認為mysql的query cache大部分情況下其實只是雞肋而已，而且建議全面禁用 ; 總之，如果線上環境中99%以上都是唯讀，很少有更新，再考慮開啟QC吧，否則，就別開了。詳見 http://www.wtoutiao.com/p/r9aGUI.html)

N=1  —- 啟用產訊緩衝的功能，緩衝所有符合要求的查詢結果集，除SELECT SQL_NO_CACHE..， 以及不符合查詢快取設定的結果集外；

N=2  —- 僅僅緩衝SELECT SQL_CACHE …子句的查詢結果集，除不符合查詢快取設定的結果集外；

query_cache_size：

查詢快取設定多大才是合理？至少需要從四個維度考慮：

①   查詢快取區對DDL和DML語句的效能影響；

②   查詢快取區的內部維護成本；

③   查詢快取區的命中率及記憶體使用量率等綜合考慮

④   業務類型

----------------------------------------

max_connections :

MySQL的最大串連數，增加該值增加mysqld 要求的檔案描述符的數量。如果伺服器的並發串連請求量比較大，建議調高此值，以增加並行串連數量，當然這建立在機器能支撐的情況下，因為如果串連數越多，介於MySQL會為每個串連提供串連緩衝區，就會開銷越多的記憶體，所以要適當調整該值，不能盲目提高設值。

數值過小會經常出現ERROR 1040: Too many connections錯誤，可以過’conn%’萬用字元查看目前狀態的串連數量，以定奪該值的大小。

show variables like ‘max_connections‘ ;  查看當前最大串連數設定值

show  status like ‘max_used_connections‘ ; 查看最大響應的串連數

如下：

mysql> show variables like ‘max_connections‘;

+———————–+——-+

| Variable_name　| Value |

+———————–+——-+

| max_connections | 256　　|

+———————–+——-+

mysql> show status like ‘max%connections‘;

+———————–+——-+

| Variable_name　      | Value |

+—————————-+——-+

| max_used_connections | 256|

+—————————-+——-+

max_used_connections / max_connections * 100% （理想值≈ 85%） 

如果max_used_connections跟max_connections相同 那麼就是max_connections設定過低或者超過伺服器負載上限了，低於10%則設定過大。

修改方法：  vim  /etc/my.cnf（永久生效）  或者直接修改會話全域變數（臨時即時生效,重啟mysql後失效，恢複原樣）。所以建議修改指定參數後，同樣修改my.cnf保持一致。

[mysqld]

max_connections=1000

----------------------------------------

mysql參數最佳化