mysql儲存引擎最佳化參數

標籤：mysql調優   innodb最佳化   myisam最佳化   mysql參數   

MySQL配置參數最佳化

本文來自道森學習筆記，著作權歸 http://wubx.net/ 所有

MyISAM儲存引擎最佳化

涉及參數如下：

Key_buffery_size

Concurrent_insert = 2 | WAAYS

Bulk_insert_buffer_size=8M

 

Myisam_recover_options=FORCE

Myisam_recover_threads=1

Myisam_sort_buffer_size=1G

參數解釋：

 

key_buffery_size      

主要用於存放myisam表的索引資訊，而且還有專門的IO調度演算法，如果搞不定將會將其buffer衝掉

#在mysql 5.6版本預設引擎調整為了innodb，暫存資料表預設引擎也一樣，所以myisam引擎基本調整一下key_buffer_size 即可，但是不能禁掉，因為mysql庫目前裡面大多數都是myisam

只有幾張表是innodb，所以這裡面很多都是myisam引擎 不能將其禁掉

但是對於5.6版本中key_buffer_size 給8M就可以

 

bulk_inser_buffer_size = 8M (預設)

在高版本中預設8M，低版本預設還是4M；所以如果小的話可以改大一點，這個值通常來說一般sql為 ： 

insert into tb (c1,c2,c3) values(),()....;

如果配置的是1M，那麼insert執行了4M的資料，這樣的話會報錯，預設情況下不用調整，但是如果做資料擷取之類的情境，需要將其調大

 

對於myisam表 可能會出現壞掉了之類的情況，這個裡面可以將以下幾個參數開打：

Myisam_recover_options=FORCE

Myisam_recover_threads=1

Myisam_sort_buffer_size=1G

這樣會自動進行修複，對於myisam表是一種修複

 

本文來自道森學習筆記，著作權歸 http://wubx.net/ 所有

但是有缺點，如果使用Myisam_recover_options=FORCE ，很可能會遺失資料，因為myisam表已經不能保證資料一致性，所以丟資料也避免不了，所以，如果業務資料非常重要，對於安全性要求很高，那麼盡量不要用myisam

因為5.5之後官方不會對myisam做任何升級和維護；5.6之後預設引擎為innodb。暫存資料表也改為innodb

 

本文來自道森學習筆記，著作權歸 http://wubx.net/ 所有

 

innodb引擎最佳化

innodb_buffer_pool_size     （面試必問）

主要存放熱資料，按照page來存放，page為最小單位，甚至是按段來存放

建議值： 如果是專用資料庫server 建議分到實體記憶體的50% -- 75% 

 

如果跑有其他服務，那麼建議“2/8的原則”：

比如100G的資料，最少達到10%的活躍資料，那麼建議buffer pool分20G

如果是好友關係系統，這樣的資料都幾乎是熱資料，那麼建議30%-50%的記憶體

再比如偷菜遊戲，幾乎所有都能達到90%的熱資料，那麼你懂的

 

innodb_buffer_pool_instances        

主要為了方便buffer pool全域的鎖，

一般情況下設定為8 ，在5.5版本是分到4 ，到了5.6版本之後要分到8，不能改成其他值，因為這是壓測得出的結果，8是可能得到最好的效能

 

innodb_log_file_size                

建議設定為data page的百分比，比如page為15% 那麼buffer pool為100G ，那麼兩邊相乘得出結果為15G，其實建議的是與data page配置的相等，有可能都配置不了那麼大，那麼就將file_size設定為1g

 

那麼如果我們要用多個log file，就會涉及到以下參數 ：

innodb_log_file_in_gourp

可以設定有幾個log檔案，至於如何計算：

Innodb的redo log           檔案大小，總大小為innodb_log_file_size *

Innodb_log_file_ingroup     總大小不要低於600M

 

一般建議3個log file即可 多了沒有必要

 

 

本文來自道森學習筆記，著作權歸 http://wubx.net/ 所有

innodb_file_per_table          

是否設定獨立資料表空間

在5.6之前是關閉的， 在5.6之後預設為開啟，建議是開啟狀態

 

innodb_file_format     #建議指定為 Barracuda

                                                       

 

innodb_flush_log_at_trx_commit

如果在導資料，可能是2天或者3天也沒有導完，那麼可能是這個參數設定為1的結果導致

1  為每一次事務進一次重新整理到磁碟，安全度高，但是效能最低，經常會導致導資料最慢

0  每秒鐘進一下事務的重新整理到磁碟

2  一般建議值，大約每秒一次事務的重新整理及同步到磁碟，實際唯寫到作業系統的buffer中，作業系統如果斷電會導致失誤丟失；#因為導資料都是人為參與的過程所以設定為2，讓速度最大化完成，如果出錯再手動搞一次即可，建議值

 

innodb_flush_log_at_timeout  (在5.6中被引入）

·該參數用於控制每N秒（1-2700秒之間）重新整理一次日誌。是對group commit的一個增強功能，預設是1秒，1秒鐘重新整理一次並由croup commit來處理，實際可以在1-2700秒之間來搞，如果系統對效能要求很高，可以將這個值設定大一些，吞吐率也會更高，因為group commit工作也會更好一點，如果說對同步即時性要求很高，那麼就設定低一點，預設值即可

之間是一個相對的關係，如果調大會得到一個很好的效能，但是從庫可能會出現延遲

如果調整為3秒，那麼這3秒做一次commit，那麼在第2秒的時候掛掉了，那麼在這2秒的時候資料會丟失

 

 

innodb_flush_method

用指定資料實際寫到磁碟上的方法，直接使用O_DIRECT即可

O_DIRECT 工作在XFS或EXT4上效能都很不錯的

最大問題就是O_DIRECT用來減小系統的VFS層級的cache，節省出來的記憶體可以提供buffer pool來使用

如果用fsync來做，其會佔用vfs層級的cache，會佔用大量記憶體，如果buffer pool很大，那麼容易造成oom

所以使用O_DIRECT來做是為了節省記憶體提供buffer pool更大空間

 

innodb_flush_neighbors

預設是0 在用這個參數之後會臨近extent髒頁面進行重新整理
比如在進行寫入的時間，會將髒也進行check ，這個時間會將髒頁面合并成順序寫 就是說將臨近的extent也合并進去

再比如第一個extent和第二個extent 是挨著的，那麼移到這裡之後發現第二個也有，那麼順便將第二個也刷過去

如果是sas sata 盤建議使用1；但是對於ssd是沒有定址延遲，所以不需要髒頁面重新整理，因為原先就是熱資料但是重新整理之後又變為冷資料資料又會做一次載入

 

本文來自道森學習筆記，著作權歸 http://wubx.net/ 所有

IO相關最佳化

innodb_io_capacity   #重要

該參數為innodb io最大數 

一般來說10W iops很正常，後期進行了最佳化： hdd 150 * 磁碟數 ， ssd 2000-1萬 

比如 6塊盤做了RAID10 那麼這樣計算的話是 150*3

需要考慮的是6塊盤做的RAID 需要考慮多少快盤的IO能力 ；再考慮如果先做RAID 1能否提升IO能力，如果不能提升那麼無非是0可以提升IO能力，所以是6塊盤的RAID10 只能獲得3塊盤的IO能力

以下是對capactiy的補充參數

比如這裡配置的是3塊盤的450個，但是峰值會更高，可能會達到1000個或更多，那麼可以對以下參數指定最大值

 

innodb_io_capacity_max              #設定io_capacity 最大值 

 

讀寫相關：

Innodb_write_io_threads

Innodb_read_io_threads

建議配置值：

保持與CPU的數量一樣就可以了，比如是16核心的cpu 那麼配置為16即可

如果是8核就配成8 以此類推

配置太高的話會到導致系統很卡 ，所以保持一致即可 

 

 

Innodb_write_io_threshold

只允許一次prefech多個page到bp中 （0-64）

 

Innodb_random_read_ahead

Prefech到bp功能是否開啟

 

考慮到一些順序IO 和資料載入的問題

比如現在read io threshold 

比如在一個使用者系統中讀取了一個使用者的資訊，那麼會將這個page載入到dp中

那麼可能需要說是否要將臨近的page也載入進來，這裡面有個問題 如果是使用者系統 那麼就在一個page中不需要載入臨近的page 直接將其關掉，那麼這個參數絕對是0 因為不需要額外的資料

但是如果是好友關係的話，資料讀到這個使用者 那麼通過程式需要將其臨近的使用者也讀取出來並載入那麼可以將這個參數設定大一些 讓其多讀一些 比如3個

這個參數需要根據業務系統進行結合如果把我不住就不要管他

 

 

 

 

 

本文出自 “心情依舊” 部落格，轉載請與作者聯絡！

mysql儲存引擎最佳化參數