mysql优化--博森瑞
?
?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)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: java 年龄_Java 计算年龄
- 下一篇: 如何提拔骨干及挑选接班人