mysql最佳化--博森瑞

標籤：

 

 http://blog.itpub.net/28916011/viewspace-1758440/

 

 

現在說一下mysql的記憶體和I/O方面的兩個特點。

一、 mysql記憶體特點：

1、  也有全域記憶體和每個session的記憶體（每個session類似於oracle的sga和pga），但是針對每個session的記憶體，我們不要給它分配過大。如果對每個session的記憶體配置過大，會造成oom的發生。在高並發下，增加實體記憶體就可以減少物理I/O。所以mysql的記憶體管理比oracle簡單一些。

 

2、在oracle裡面有library cache，用來緩衝執行計畫的。但是mysql不會緩衝執行計畫，mysql有類似於oracle的Qcache,Qcache是用來緩衝sql語句的，但是在生產中建議關掉此功能，因為該功能很雞肋，它的功能體現不了那麼大的作用。

 

3、mysql的查詢分析器很高效，mysql不緩衝執行計畫，但是並不會出現像oracle那種硬解析的問題。

 

 4、對於記憶體方面的分配，如果是單一實例的Mysql,建議分配50%到70%的實體記憶體給mysql。

 

 二、mysql I/O方面的特點

mysql有binlog、undo log和redo log三個記錄檔都是順序寫I/O的方式。mysql的資料檔案是包含著隨機寫I/O和順序寫I/O的。

目前mysql有innodb和myisam儲存引擎。有人可能會說myisam的儲存引擎要比innodb的儲存引擎快。其實並不是這樣的。為什麼這麼說呢哈？innodb主要根據主鍵檢索資料時很快，因為主鍵裡包含行的資料資訊，而且，對於innodb，它是雙鏈表結構， 所以他很快就能找到資料。另外，Innodb既快取資料檔案，也緩衝索引檔案。但是針對myisam來說呢，myisam只緩衝索引檔案。所以，一個很簡單的道理，你說是從記憶體讀快，還是從磁碟讀快。答案肯定是從記憶體讀取快。所以結論就是，Innodb要比Myisam儲存引擎快。

 

 

  

最佳化思路：

1、發現問題的過程

當資料庫慢了，要搞清是什麼問題導致系統慢了，是系統的問題，還是開發的問題，還是資料庫的問題；

2、找到問題後，鎖定問題瓶頸點；

         鎖定瓶頸點的過程，分為兩個層面。一個是系統層面，一個是資料庫層面。

2.1 系統層面

系統層面就需要我們學習一些linux方面的知識，利用linux的知識，來鎖定問題的瓶頸在哪。top命令可以看哪個進程佔用cpu最高，查看負載，可以查看記憶體。

vmstat可以查看記憶體、cpu、i/o。

sar –u 查看cpu

sar -d 查看io

sar -r 查看記憶體

 

2.2資料庫層面

 現在Mysql預設儲存引起是innodb。

1、show engine innodb status

通過show innodb status看鎖的情況，看資料有沒有鎖，有沒有死結，有沒有事務狀態，看看一些頁的情況，看看現在有沒有老頁和新頁的移動。

 

2、slow log

看慢日誌，制定時間，有好多工具可以分析慢日誌（如percona公司的），找到top10最慢的sql（一般優先處理頻率最高的前十條SQL語句）

也可以通過圖形化的工具，找到最慢的sql，然後找開發溝通，商討是在業務上更改呢，還是針對這條SQL來進行最佳化。我們的目的就是讓sql越簡單越好。

 

3、show global status

 show global status查看資料庫整體的效能，看哪個點需要調整一下，看片段是如何產生，片段產生多少，如何清理片段。看每秒鐘產生的TPS有多少。

 

4、  show processlist

show processlist查看整個mysql連結的一個情況，注意看wait timeout 和inactive timeout ，主要減少不活躍的串連，這樣可以把那些消耗的記憶體收回，從而防止omq的發生。

 

5、Pt-ioprofile

pt-ioprofile是第三方工具，查看innodb內部有哪些表是最活躍的，因為大部分的最佳化，都是在SQL瓶頸點。當我們鎖定到這些表，我們就可以針對這些表做一些文章了，看這些表是否需要添加索引，是否進行片段的整理。所以這個工具也很重要。

 

 

3、 最佳化方法的初定

我們鎖定到了問題的瓶頸點了，就需要制定最佳化方案。

4、 制定最佳化方案。

 

5、最佳化方案的測試

我們不能因為最佳化一個問題點，影響其他的業務。所以鎖定問題是從面到點，但是最佳化測試是從點到面。我們只要把這個點解決問題了，而不要影響全面。所以測試時一定要找測試環境，方案能真正在生產線上實施再實施。

 

6、方案的實施

7、問題解決了

一定要做好記錄，想清楚為什麼發生這樣的問題，避免下次再發生。這也是考驗一個人的好的學習方式。

 

  

在硬體角度的最佳化。

 從系統bios層面

從系統bios層面，有個參數叫dapc，它表示每瓦的電能都能產生最大的功效，可以讓cpu發揮它最大的功效。

在bios層面，還有個參數是記憶體頻率，一定要調成max performance。

在bios層面還有一個cae處理器，當它處於閑置狀態的時候，我們可以禁用處理器，使其處於最低的狀態。

 

關於TPS高，業務高

關於TPS高，業務高的一個情況，這種情況一定要配置陣列卡，一定要配置cache模組和bbu模組。bbu模組是用來提供我後備的電量，當機器斷電了，我的bbu模組可以去充當電源，保證資料不會丟失，使資料寫到cache裡面。現在新的伺服器都是電容模式的bbu了，效能會更好。

 

關於陣列卡策略的問題

關於陣列卡策略的問題，一定要選擇wb（write bike），就是當寫資料的時候，一定要先寫到cache卡裡面，然後通過陣列卡把資料刷到磁碟，這樣能提高我們的IOPS。一定要禁用             wthrow，因為這種直接寫磁碟是非常耗效能的。

 

關於關閉預讀功能

關於關閉預讀功能，就是讓緩衝踏踏實實做寫緩衝的作用。不要開啟預讀，不要開啟資料頁，到我的cache裡面，這麼做沒有任何作用。

 

關於陣列層級的選擇

關於陣列層級的選擇,對於mysql資料庫，建議用raid 1+0。

有人說，用raid5也不錯，但是raid5的寫乘法資料是4。為什麼是4呢，因為raid5有讀資料，讀校正位、寫資料，寫校正位。但是raid10，它只有雙寫，也就是raid10的係數是2。所以raid10寫的i/o一定要比raid5高，不過raid5讀i/o比raid10可能好一些，但是針對oltp這種系統，推薦用raid10。

 

關於磁碟上的策略

關於關閉磁碟上的策略，一定要關閉磁碟上的cache策略。因為我們沒有必要開它，這樣可以防止遺失資料。

 

關於選盤的問題

建議上SSD或PCIE-SSD的磁碟，這樣可以提高iops上百倍或者上千倍。

        

  

關於swappiness值大小的調整

swappiness值大小對於我們如何使用swap分區是有很多聯絡的。當swappiness設定為0，就是充分利用虛擬記憶體。當swappines設定為100，表示能用swap分區，就用swap分區。

在redhat 6版本之前，swappiness可以設定成0,；但是大於redhat6最好設定成10；到了redhat 7以上，一定要謹慎設定swappiness設定成0，因為如果設定成0了，在redhat 7可能會發生omq。

 

關於I/O調度器的選擇

IO調度器，首選deadline，其次用noop，不要用預設的cfq，預設是非常的不好的。

 

關於檔案系統的選擇

首先xfs，其次ext4。

 

   

  

  

innodb是Mysql的預設儲存引擎。影響innodb引擎最重要的參數是innodb_buffer_pool_size，它就相當於oracle的buffer cache，是用來快取資料用的。

在單一實例的mysql，最好設定innodb_buffer_pool_size設定為50%~70%的實體記憶體大小。

 

innodb_data_file_path，該參數就是分配共用資料表空間的大小。它預設是10m。但是我們建議將其設定成1G，這樣可以避免後期資料暴漲很消耗效能。

 

innodb_log_file_size，該參數不要設定的過大，因為該參數相當於oracle裡面的redo。如果把該參數設定的過大，當資料庫crash的時候，恢複資料就會很慢。

 

transaction_isolation，表示交易隔離等級。對於mysql需要什麼樣的交易隔離等級，是需要一步一步去選擇的。對於oracle來講，交易隔離等級就是預設的提交讀。但是對於mysql,有預設的提交讀，也有可重複讀，還有髒讀和串讀。建議使用Mysql的可重複讀（r模式），因為這種模式可以保證資料的一致性，可以避免發生一個事務提交了，在另一個事務中能看到他提交的東西。如果事務提交了，在另外一個事務中能看到他提交的東西，這樣就違背了事務一致性的情況的。

 

sort_buffer_size、read_buffer_size、join_buffer_size三個參數，可以理解為oracle的pga。這三個參數不用設定過大，大概幾兆，幾十兆就行。另外oracle的pga也不要設定的過大。

        

general_log,log_bin,sync_binlog,long_query_time,interactive_timeout,wait_timeout,max_connections

在生產上，不要開全日誌（general_log），因為開了全日誌，它就會記錄全部的sql語句，這樣很影響mysql效能。

對於binlog日誌，一定要開此功能，因為這樣可以實現複製的功能，也可以實現binlog恢複的功能。

 

對於sync_binlog，該參數數值的大小，關係到資料庫寫binlog情況的問題。

sync_binlog=0，表示我每一秒刷一次binlog，sync_binlog=1表示每秒我都刷，保證他不會丟；當sync_binlog=2，表示交給作業系統，資料庫不管了。

 

對於long_query_time，表示慢查詢時間的一個情況，可以設定0.xxxx秒的慢查詢。對出現頻率高的慢sql進行最佳化。

可以通過show processlist看一些互動式和非互動時間等待。對於interactive_timeout和wait_timeout這兩個參數我們不要設定的過大，一般這兩個值設定的一樣，在沒有串連池的情況，設定成5分鐘就行了。

 

  max_connections,當使用者串連數超過這個max_connections時，會報錯。但注意，報錯時，不要盲目的增大max_connections這個參數。因為如果max_connections設定的過大，會發生資料庫被連暴了，塌了的情況，是很危險。那麼我們應該如何調整呢？其實出現這種情況會聯絡到好多的參數的，比如可以減少並發參數的值來減少串連數，或者觀察資料庫sql語句，分析到底是業務的問題還是資料庫的問題。不要什麼事情都往自己身上攬，拿出證據，證明這個問題就不是我DBA的問題。

      

  選擇儲存引擎，是針對業務來講。針對oltp 預設就是Innodb。到了mysql 5.7有可能myisam會消失。

         innodb儲存引擎支援事務，支援行鎖，鎖的粒度更低，所以並發性很好，當發生故障可以根據redo和Undo進行恢複。Innodb是快取資料和索引的，但是Myisam只緩衝索引，而且myisam的資料和索引是分開的。

  

 一定要設定自增主鍵。如果你不設定自增主鍵，也沒有關係，這時候Mysql會給它一個6位元組的主鍵，但是這樣會很消耗效能的。所以設定自增主鍵是一個必須的選項。

關於時間日期，ipv4類型和資料類型可以用int

避免使用text/blob這種大資料類型。如果非要用大資料類型，可以單獨把大資料類型放在一張表上儲存。

定義欄位的時候盡量要定義Not null,因為索引是不含Null欄位的。

選擇性低的欄位不要建立索引，像男女這樣的欄位不要建立索引，建立索引沒有意義。因為mysql最佳化器是很智能的，重複值出現很多,mysql可能就不走索引，而是走全表掃描了。

對於排序和分組欄位上，一定要建立索引。

索引不要太多，因為update，會使索引的頁進行翻轉，對效能有很大的下降。

聯合索引優於單列索引，聯合索引可以縮短整個段池搜尋的一個範圍，它比單列索引要好。

像一些字元類型，如果可能只用到前面幾個字元，而不需要整個欄位建索引，這叫首碼索引，我們可以建立一個首碼索引，我只搜那幾個欄位。

 

 

         索引掃描記錄的述超過30%，就會走全表掃描

模糊比對查詢的雙%%不會用到索引的。但是去掉左邊的%，%號在最後一位可能會用到索引。

聯合索引，第一個查詢條件如果不是最做索引列，也不會用到索引，這就是最左首碼原則。

聯合索引，如果第一個索引列使用範圍查詢（> 、=、<、>=、<=），那用到索引也是部分索引，有可能只用到第一個索引了，後面的索引資料庫都用不到。

兩個獨立的索引，一個用來檢索，一個用來排序，可能只用到一個。mysql 5.6有ITC這項功能。

 

最忌諱在索引的欄位上使用函數，這樣是不會走索引的。

 

Q&A

         sga是記憶體全域區，pga是使用者的一個session串連上資料庫單獨分配給的記憶體。

mysql用的最多的叢集是MHA。oracle的叢集是RAC。

為什麼會產生死結？就是因為共搶一塊東西，你要我的東西，我又要你的東西，從而造成死迴圈，用show innodb status定位死結產生在哪。

 

最好關閉磁碟的cache策略，因為寫在cache裡面，容易發生資料丟失，所以建議關閉磁碟的cache策略。

 

有問題留言，

保證業務的穩定，保證資料庫快速。

 

主從分離和DBA沒有關係，只需要開發在代碼裡面寫好了。

現在mysql架構，就是用MHA。

不是說資料量大了，就進行最佳化，而是出現效能問題了再進行最佳化。看索引建沒建。

mysql最佳化--博森瑞