?

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

?

?

現(xiàn)在說一下mysql的內(nèi)存和I/O方面的兩個(gè)特點(diǎn)。

一、?mysql內(nèi)存特點(diǎn)：

1、??也有全局內(nèi)存和每個(gè)session的內(nèi)存（每個(gè)session類似于oracle的sga和pga），但是針對(duì)每個(gè)session的內(nèi)存，我們不要給它分配過大。如果對(duì)每個(gè)session的內(nèi)存分配過大，會(huì)造成oom的發(fā)生。在高并發(fā)下，增加物理內(nèi)存就可以減少物理I/O。所以mysql的內(nèi)存管理比oracle簡單一些。

?

2、在oracle里面有l(wèi)ibrary cache，用來緩存執(zhí)行計(jì)劃的。但是mysql不會(huì)緩存執(zhí)行計(jì)劃，mysql有類似于oracle的Qcache,Qcache是用來緩存sql語句的，但是在生產(chǎn)中建議關(guān)掉此功能，因?yàn)樵摴δ芎茈u肋，它的功能體現(xiàn)不了那么大的作用。

?

3、mysql的查詢分析器很高效，mysql不緩存執(zhí)行計(jì)劃，但是并不會(huì)出現(xiàn)像oracle那種硬解析的問題。

?

?4、對(duì)于內(nèi)存方面的分配，如果是單實(shí)例的Mysql,建議分配50%到70%的物理內(nèi)存給mysql。

?

?二、mysql I/O方面的特點(diǎn)

mysql有binlog、undo log和redo log三個(gè)日志文件都是順序?qū)慖/O的方式。mysql的數(shù)據(jù)文件是包含著隨機(jī)寫I/O和順序?qū)慖/O的。

目前mysql有innodb和myisam存儲(chǔ)引擎。有人可能會(huì)說myisam的存儲(chǔ)引擎要比innodb的存儲(chǔ)引擎快。其實(shí)并不是這樣的。為什么這么說呢哈？innodb主要根據(jù)主鍵檢索數(shù)據(jù)時(shí)很快，因?yàn)橹麈I里包含行的數(shù)據(jù)信息，而且，對(duì)于innodb，它是雙鏈表結(jié)構(gòu)，?所以他很快就能找到數(shù)據(jù)。另外，Innodb既緩存數(shù)據(jù)文件，也緩存索引文件。但是針對(duì)myisam來說呢，myisam只緩存索引文件。所以，一個(gè)很簡單的道理，你說是從內(nèi)存讀快，還是從磁盤讀快。答案肯定是從內(nèi)存讀取快。所以結(jié)論就是，Innodb要比Myisam存儲(chǔ)引擎快。

?

?

??

優(yōu)化思路：

1、發(fā)現(xiàn)問題的過程

當(dāng)數(shù)據(jù)庫慢了，要搞清是什么問題導(dǎo)致系統(tǒng)慢了，是系統(tǒng)的問題，還是開發(fā)的問題，還是數(shù)據(jù)庫的問題；

2、找到問題后，鎖定問題瓶頸點(diǎn)；

?????????鎖定瓶頸點(diǎn)的過程，分為兩個(gè)層面。一個(gè)是系統(tǒng)層面，一個(gè)是數(shù)據(jù)庫層面。

2.1?系統(tǒng)層面

系統(tǒng)層面就需要我們學(xué)習(xí)一些linux方面的知識(shí)，利用linux的知識(shí)，來鎖定問題的瓶頸在哪。top命令可以看哪個(gè)進(jìn)程占用cpu最高，查看負(fù)載，可以查看內(nèi)存。

vmstat可以查看內(nèi)存、cpu、i/o。

sar –u 查看cpu

sar -d?查看io

sar -r 查看內(nèi)存

?

2.2數(shù)據(jù)庫層面

?現(xiàn)在Mysql默認(rèn)存儲(chǔ)引起是innodb。

1、show engine innodb status

通過show innodb status看鎖的情況，看數(shù)據(jù)有沒有鎖，有沒有死鎖，有沒有事務(wù)狀態(tài)，看看一些頁的情況，看看現(xiàn)在有沒有老頁和新頁的移動(dòng)。

?

2、slow log

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

也可以通過圖形化的工具，找到最慢的sql，然后找開發(fā)溝通，商討是在業(yè)務(wù)上更改呢，還是針對(duì)這條SQL來進(jìn)行優(yōu)化。我們的目的就是讓sql越簡單越好。

?

3、show global status

?show global status查看數(shù)據(jù)庫整體的性能，看哪個(gè)點(diǎn)需要調(diào)整一下，看碎片是如何產(chǎn)生，碎片產(chǎn)生多少，如何清理碎片。看每秒鐘產(chǎn)生的TPS有多少。

?

4、??show processlist

show processlist查看整個(gè)mysql鏈接的一個(gè)情況，注意看wait timeout?和inactive timeout?，主要減少不活躍的連接，這樣可以把那些消耗的內(nèi)存收回，從而防止omq的發(fā)生。

?

5、Pt-ioprofile

pt-ioprofile是第三方工具，查看innodb內(nèi)部有哪些表是最活躍的，因?yàn)榇蟛糠值膬?yōu)化，都是在SQL瓶頸點(diǎn)。當(dāng)我們鎖定到這些表，我們就可以針對(duì)這些表做一些文章了，看這些表是否需要添加索引，是否進(jìn)行碎片的整理。所以這個(gè)工具也很重要。

?

?

3、?優(yōu)化方法的初定

我們鎖定到了問題的瓶頸點(diǎn)了，就需要制定優(yōu)化方案。

4、?制定優(yōu)化方案。

?

5、優(yōu)化方案的測試

我們不能因?yàn)閮?yōu)化一個(gè)問題點(diǎn)，影響其他的業(yè)務(wù)。所以鎖定問題是從面到點(diǎn)，但是優(yōu)化測試是從點(diǎn)到面。我們只要把這個(gè)點(diǎn)解決問題了，而不要影響全面。所以測試時(shí)一定要找測試環(huán)境，方案能真正在生產(chǎn)線上實(shí)施再實(shí)施。

?

6、方案的實(shí)施

7、問題解決了

一定要做好記錄，想清楚為什么發(fā)生這樣的問題，避免下次再發(fā)生。這也是考驗(yàn)一個(gè)人的好的學(xué)習(xí)方式。

?

??

在硬件角度的優(yōu)化。

?從系統(tǒng)bios層面

從系統(tǒng)bios層面，有個(gè)參數(shù)叫dapc，它表示每瓦的電能都能產(chǎn)生最大的功效，可以讓cpu發(fā)揮它最大的功效。

在bios層面，還有個(gè)參數(shù)是內(nèi)存頻率，一定要調(diào)成max performance。

在bios層面還有一個(gè)cae處理器，當(dāng)它處于閑置狀態(tài)的時(shí)候，我們可以禁用處理器，使其處于最低的狀態(tài)。

?

關(guān)于TPS高，業(yè)務(wù)高

關(guān)于TPS高，業(yè)務(wù)高的一個(gè)情況，這種情況一定要配置陣列卡，一定要配置cache模塊和bbu模塊。bbu模塊是用來提供我后備的電量，當(dāng)機(jī)器斷電了，我的bbu模塊可以去充當(dāng)電源，保證數(shù)據(jù)不會(huì)丟失，使數(shù)據(jù)寫到cache里面。現(xiàn)在新的服務(wù)器都是電容模式的bbu了，性能會(huì)更好。

?

關(guān)于陣列卡策略的問題

關(guān)于陣列卡策略的問題，一定要選擇wb（write bike），就是當(dāng)寫數(shù)據(jù)的時(shí)候，一定要先寫到cache卡里面，然后通過陣列卡把數(shù)據(jù)刷到磁盤，這樣能提高我們的IOPS。一定要禁用 ? ? ? ? ? ? wthrow，因?yàn)檫@種直接寫磁盤是非常耗性能的。

?

關(guān)于關(guān)閉預(yù)讀功能

關(guān)于關(guān)閉預(yù)讀功能，就是讓緩存踏踏實(shí)實(shí)做寫緩存的作用。不要開啟預(yù)讀，不要開啟數(shù)據(jù)頁，到我的cache里面，這么做沒有任何作用。

?

關(guān)于陣列級(jí)別的選擇

關(guān)于陣列級(jí)別的選擇,對(duì)于mysql數(shù)據(jù)庫，建議用raid 1+0。

有人說，用raid5也不錯(cuò)，但是raid5的寫乘法數(shù)據(jù)是4。為什么是4呢，因?yàn)閞aid5有讀數(shù)據(jù)，讀校驗(yàn)位、寫數(shù)據(jù)，寫校驗(yàn)位。但是raid10，它只有雙寫，也就是raid10的系數(shù)是2。所以raid10寫的i/o一定要比raid5高，不過raid5讀i/o比raid10可能好一些，但是針對(duì)oltp這種系統(tǒng)，推薦用raid10。

?

關(guān)于磁盤上的策略

關(guān)于關(guān)閉磁盤上的策略，一定要關(guān)閉磁盤上的cache策略。因?yàn)槲覀儧]有必要開它，這樣可以防止丟失數(shù)據(jù)。

?

關(guān)于選盤的問題

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

????????

??

關(guān)于swappiness值大小的調(diào)整

swappiness值大小對(duì)于我們?nèi)绾问褂胹wap分區(qū)是有很多聯(lián)系的。當(dāng)swappiness設(shè)置為0，就是充分利用虛擬內(nèi)存。當(dāng)swappines設(shè)置為100，表示能用swap分區(qū)，就用swap分區(qū)。

在redhat 6版本之前，swappiness可以設(shè)置成0,；但是大于redhat6最好設(shè)置成10；到了redhat 7以上，一定要謹(jǐn)慎設(shè)置swappiness設(shè)置成0，因?yàn)槿绻O(shè)置成0了，在redhat 7可能會(huì)發(fā)生omq。

?

關(guān)于I/O調(diào)度器的選擇

IO調(diào)度器，首選deadline，其次用noop，不要用默認(rèn)的cfq，默認(rèn)是非常的不好的。

?

關(guān)于文件系統(tǒng)的選擇

首先xfs，其次ext4。

?

???

??

??

innodb是Mysql的默認(rèn)存儲(chǔ)引擎。影響innodb引擎最重要的參數(shù)是innodb_buffer_pool_size，它就相當(dāng)于oracle的buffer cache，是用來緩存數(shù)據(jù)用的。

在單實(shí)例的mysql，最好設(shè)置innodb_buffer_pool_size設(shè)置為50%~70%的物理內(nèi)存大小。

?

innodb_data_file_path，該參數(shù)就是分配共享表空間的大小。它默認(rèn)是10m。但是我們建議將其設(shè)置成1G，這樣可以避免后期數(shù)據(jù)暴漲很消耗性能。

?

innodb_log_file_size，該參數(shù)不要設(shè)置的過大，因?yàn)樵搮?shù)相當(dāng)于oracle里面的redo。如果把該參數(shù)設(shè)置的過大，當(dāng)數(shù)據(jù)庫crash的時(shí)候，恢復(fù)數(shù)據(jù)就會(huì)很慢。

?

transaction_isolation，表示事務(wù)隔離級(jí)別。對(duì)于mysql需要什么樣的事務(wù)隔離級(jí)別，是需要一步一步去選擇的。對(duì)于oracle來講，事務(wù)隔離級(jí)別就是默認(rèn)的提交讀。但是對(duì)于mysql,有默認(rèn)的提交讀，也有可重復(fù)讀，還有臟讀和串讀。建議使用Mysql的可重復(fù)讀（r模式），因?yàn)檫@種模式可以保證數(shù)據(jù)的一致性，可以避免發(fā)生一個(gè)事務(wù)提交了，在另一個(gè)事務(wù)中能看到他提交的東西。如果事務(wù)提交了，在另外一個(gè)事務(wù)中能看到他提交的東西，這樣就違背了事務(wù)一致性的情況的。

?

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

????????

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

在生產(chǎn)上，不要開全日志（general_log），因?yàn)殚_了全日志，它就會(huì)記錄全部的sql語句，這樣很影響mysql性能。

對(duì)于binlog日志，一定要開此功能，因?yàn)檫@樣可以實(shí)現(xiàn)復(fù)制的功能，也可以實(shí)現(xiàn)binlog恢復(fù)的功能。

?

對(duì)于sync_binlog，該參數(shù)數(shù)值的大小，關(guān)系到數(shù)據(jù)庫寫binlog情況的問題。

sync_binlog=0，表示我每一秒刷一次binlog，sync_binlog=1表示每秒我都刷，保證他不會(huì)丟；當(dāng)sync_binlog=2，表示交給操作系統(tǒng)，數(shù)據(jù)庫不管了。

?

對(duì)于long_query_time，表示慢查詢時(shí)間的一個(gè)情況，可以設(shè)置0.xxxx秒的慢查詢。對(duì)出現(xiàn)頻率高的慢sql進(jìn)行優(yōu)化。

可以通過show processlist看一些交互式和非交互式的時(shí)間等待。對(duì)于interactive_timeout和wait_timeout這兩個(gè)參數(shù)我們不要設(shè)置的過大，一般這兩個(gè)值設(shè)置的一樣，在沒有連接池的情況，設(shè)置成5分鐘就行了。

?

? max_connections,當(dāng)用戶連接數(shù)超過這個(gè)max_connections時(shí)，會(huì)報(bào)錯(cuò)。但注意，報(bào)錯(cuò)時(shí)，不要盲目的增大max_connections這個(gè)參數(shù)。因?yàn)槿绻鹠ax_connections設(shè)置的過大，會(huì)發(fā)生數(shù)據(jù)庫被連暴了，塌了的情況，是很危險(xiǎn)。那么我們應(yīng)該如何調(diào)整呢？其實(shí)出現(xiàn)這種情況會(huì)聯(lián)系到好多的參數(shù)的，比如可以減少并發(fā)參數(shù)的值來減少連接數(shù)，或者觀察數(shù)據(jù)庫sql語句，分析到底是業(yè)務(wù)的問題還是數(shù)據(jù)庫的問題。不要什么事情都往自己身上攬，拿出證據(jù)，證明這個(gè)問題就不是我DBA的問題。

??????

??選擇存儲(chǔ)引擎，是針對(duì)業(yè)務(wù)來講。針對(duì)oltp?默認(rèn)就是Innodb。到了mysql 5.7有可能myisam會(huì)消失。

???????? innodb存儲(chǔ)引擎支持事務(wù)，支持行鎖，鎖的粒度更低，所以并發(fā)性很好，當(dāng)發(fā)生故障可以根據(jù)redo和Undo進(jìn)行恢復(fù)。Innodb是緩存數(shù)據(jù)和索引的，但是Myisam只緩存索引，而且myisam的數(shù)據(jù)和索引是分開的。

??

?一定要設(shè)置自增主鍵。如果你不設(shè)置自增主鍵，也沒有關(guān)系，這時(shí)候Mysql會(huì)給它一個(gè)6字節(jié)的主鍵，但是這樣會(huì)很消耗性能的。所以設(shè)置自增主鍵是一個(gè)必須的選項(xiàng)。

關(guān)于時(shí)間日期，ipv4類型和數(shù)據(jù)類型可以用int

避免使用text/blob這種大數(shù)據(jù)類型。如果非要用大數(shù)據(jù)類型，可以單獨(dú)把大數(shù)據(jù)類型放在一張表上存儲(chǔ)。

定義字段的時(shí)候盡量要定義Not null,因?yàn)樗饕遣缓琋ull字段的。

選擇性低的字段不要?jiǎng)?chuàng)建索引，像男女這樣的字段不要?jiǎng)?chuàng)建索引，創(chuàng)建索引沒有意義。因?yàn)閙ysql優(yōu)化器是很智能的，重復(fù)值出現(xiàn)很多,mysql可能就不走索引，而是走全表掃描了。

對(duì)于排序和分組字段上，一定要?jiǎng)?chuàng)建索引。

索引不要太多，因?yàn)閡pdate，會(huì)使索引的頁進(jìn)行翻轉(zhuǎn)，對(duì)性能有很大的下降。

聯(lián)合索引優(yōu)于單列索引，聯(lián)合索引可以縮短整個(gè)段池搜索的一個(gè)范圍，它比單列索引要好。

像一些字符類型，如果可能只用到前面幾個(gè)字符，而不需要整個(gè)字段建索引，這叫前綴索引，我們可以建立一個(gè)前綴索引，我只搜那幾個(gè)字段。

?

?

?????????索引掃描記錄的述超過30%，就會(huì)走全表掃描

模糊匹配查詢的雙%%不會(huì)用到索引的。但是去掉左邊的%，%號(hào)在最后一位可能會(huì)用到索引。

聯(lián)合索引，第一個(gè)查詢條件如果不是最做索引列，也不會(huì)用到索引，這就是最左前綴原則。

聯(lián)合索引，如果第一個(gè)索引列使用范圍查詢（>?、=、<、>=、<=），那用到索引也是部分索引，有可能只用到第一個(gè)索引了，后面的索引數(shù)據(jù)庫都用不到。

兩個(gè)獨(dú)立的索引，一個(gè)用來檢索，一個(gè)用來排序，可能只用到一個(gè)。mysql 5.6有ITC這項(xiàng)功能。

?

最忌諱在索引的字段上使用函數(shù)，這樣是不會(huì)走索引的。

?

Q&A

???????? sga是內(nèi)存全局區(qū)，pga是用戶的一個(gè)session連接上數(shù)據(jù)庫單獨(dú)分配給的內(nèi)存。

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

為什么會(huì)產(chǎn)生死鎖？就是因?yàn)楣矒屢粔K東西，你要我的東西，我又要你的東西，從而造成死循環(huán)，用show innodb status定位死鎖產(chǎn)生在哪。

?

最好關(guān)閉磁盤的cache策略，因?yàn)閷懺赾ache里面，容易發(fā)生數(shù)據(jù)丟失，所以建議關(guān)閉磁盤的cache策略。

?

有問題留言，

保證業(yè)務(wù)的穩(wěn)定，保證數(shù)據(jù)庫快速。

?

主從分離和DBA沒有關(guān)系，只需要開發(fā)在代碼里面寫好了。

現(xiàn)在mysql架構(gòu)，就是用MHA。

不是說數(shù)據(jù)量大了，就進(jìn)行優(yōu)化，而是出現(xiàn)性能問題了再進(jìn)行優(yōu)化。看索引建沒建。

轉(zhuǎn)載于:https://www.cnblogs.com/zengkefu/p/5668665.html

總結(jié)

以上是生活随笔為你收集整理的mysql优化--博森瑞的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。