1、Mysql三種存儲引擎

MySQL提供了兩種存儲引擎：MyISAM和 InnoDB,MySQL4和5使用默認(rèn)的MyISAM存儲引擎.從MYSQL5.5開始,MySQL已將默認(rèn)存儲引擎從MyISAM變動為InnoDB.

MyISAM沒有提供事務(wù)支持,而InnoDB提供了事務(wù)支持.

XtraDB是InnoDB存儲引擎的增強(qiáng)版本,被設(shè)計(jì)用來更好的使用更新計(jì)算機(jī)硬件系統(tǒng)的性能,同時還包括有一些在高性能環(huán)境下的新特性.

2、Percona Server分支

Percona Server由領(lǐng)先的MySQL咨詢公司Percona宣布.

Percona Server是一款獨(dú)立的數(shù)據(jù)庫產(chǎn)品,其可以完全與MySQL兼容,可以在不變動代碼的情況了下將存儲引擎更換成XtraDB.是最接近官方MySQL Enterprise發(fā)行版的版本.

Percona提供了高性能XtraDB引擎,還提供PXC高可用辦理方案,并且附帶了percona-toolkit等DBA管理工具箱,

3、MariaDB

MariaDB由MySQL的創(chuàng)始人開發(fā),MariaDB的目的是完全兼容MySQL,包含API和命令行,使之能輕松成為MySQL的代替品.

MariaDB提供了MySQL提供的尺度存儲引擎,即MyISAM和InnoDB,10.0.9版起使用XtraDB(名稱代號為Aria)來代替MySQL的InnoDB.

4、如何選擇

綜合多年使用經(jīng)驗(yàn)和性能對照,首選Percona分支,其次是MariaDB,如果你不想冒一點(diǎn)風(fēng)險,那就選擇MYSQL官方版本.

二、常用的MYSQL調(diào)優(yōu)策略

1、硬件層相關(guān)優(yōu)化

改動服務(wù)器BIOS設(shè)置

選擇Performance Per Watt Optimized(DAPC)模式,發(fā)揮CPU最年夜性能.

Memory Frequency(內(nèi)存頻率)選擇Maximum Performance(最佳性能)

內(nèi)存設(shè)置菜單中,啟用Node Interleaving,避免NUMA問題

2、磁盤I/O相關(guān)

使用SSD硬盤

如果是磁盤陣列存儲,建議陣列卡同時配備CACHE及BBU模塊,可明顯提升IOPS.

raid級別盡量選擇raid10,而不是raid5.

3、文件系統(tǒng)層優(yōu)化

使用deadline/noop這兩種I/O調(diào)劑器,千萬別用cfq

使用xfs文件系統(tǒng),千萬別用ext3；ext4勉強(qiáng)可用,但業(yè)務(wù)量很大的話,則必定要用xfs；

文件系統(tǒng)mount參數(shù)中增加：noatime, nodiratime, nobarrier幾個選項(xiàng)(nobarrier是xfs文件系統(tǒng)特有的)；

4、內(nèi)核參數(shù)優(yōu)化

修改vm.swappiness參數(shù),降低swap使用率.RHEL7/centos7以上則慎重設(shè)置為0,可能產(chǎn)生OOM

調(diào)整vm.dirty_background_ratio、vm.dirty_ratio內(nèi)核參數(shù),以確保能持續(xù)將臟數(shù)據(jù)刷新到磁盤,避免瞬間I/O寫.發(fā)生等待.

調(diào)整net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都設(shè)置為1,減少TIME_WAIT,提高TCP效率.

5、Mysql參數(shù)優(yōu)化建議

建議設(shè)置default-storage-engine=InnoDB,強(qiáng)烈建議不要再使用MyISAM引擎.

調(diào)整innodb_buffer_pool_size的年夜小,如果是單實(shí)例且絕年夜多數(shù)是InnoDB引擎表的話,可考慮設(shè)置為物理內(nèi)存的50% -70%左右.

設(shè)置innodb_file_per_table = 1,使用自力表空間.

調(diào)整innodb_data_file_path = ibdata1:1G:autoextend,不要用默認(rèn)的10M,在高并發(fā)場景下,性能會有很年夜提升.

設(shè)置innodb_log_file_size=256M,設(shè)置innodb_log_files_in_group=2,基本可以滿足年夜多數(shù)應(yīng)用場景.

調(diào)整max_connection(最大連接數(shù))、max_connection_error(最大差錯數(shù))設(shè)置,根據(jù)業(yè)務(wù)量大小進(jìn)行設(shè)置.

另外,open_files_limit、innodb_open_files、table_open_cache、table_definition_cache可以設(shè)置年夜約為max_connection的10倍左右年夜小.

key_buffer_size建議調(diào)小,32M左右即可,另外建議封閉query cache.

mp_table_size和max_heap_table_size設(shè)置不要過年夜,另外sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等設(shè)置也不要過年夜.

三、 MYSQL常見的應(yīng)用架構(gòu)分享

1、主從復(fù)制辦理方案

這是MySQL自身提供的一種高可用解決方案,數(shù)據(jù)同步辦法采用的是MySQL replication技術(shù).MySQL replication就是從服務(wù)器到主服務(wù)器拉取二進(jìn)制日志文件,然后再將日志文件解析成相應(yīng)的SQL在從服務(wù)器上重新執(zhí)行一遍主服務(wù)器的操作,通過這種方式保證數(shù)據(jù)的一致性.

為了達(dá)到更高的可用性,在實(shí)際的應(yīng)用環(huán)境中,一般都是采納MySQL replication技術(shù)配合高可用集群軟件keepalived來實(shí)現(xiàn)自動failover,這種方式可以實(shí)現(xiàn)95.000%的SLA.

2、MMM/MHA高可用辦理方案

MMM提供了MySQL主主復(fù)制配置的監(jiān)控、故障轉(zhuǎn)移和管理的一套可伸縮的腳本套件.在MMM高可用計(jì)劃中,典型的應(yīng)用是雙主多從架構(gòu),通過MySQL replication技術(shù)可以實(shí)現(xiàn)兩個服務(wù)器互為主從,且在任何時候只有一個節(jié)點(diǎn)可以被寫入,避免了多點(diǎn)寫入的數(shù)據(jù)沖突.同時,當(dāng)可寫的主節(jié)點(diǎn)故障時,MMM套件可以立刻監(jiān)控到,然后將服務(wù)自動切換到另一個主節(jié)點(diǎn),繼續(xù)提供服務(wù),從而實(shí)現(xiàn)MySQL的高可用.

3、Heartbeat/SAN高可用辦理方案

在這個方案中,處理failover的方式是高可用集群軟件Heartbeat,它監(jiān)控和管理各個節(jié)點(diǎn)間連接的網(wǎng)絡(luò),并監(jiān)控集群服務(wù),當(dāng)節(jié)點(diǎn)出現(xiàn)故障或者服務(wù)弗成用時,自動在其他節(jié)點(diǎn)啟動集群服務(wù).在數(shù)據(jù)共享方面,通過SAN(Storage Area Network)存儲來共享數(shù)據(jù),這種方案可以實(shí)現(xiàn)99.990%的SLA.

4、Heartbeat/DRBD高可用辦理方案

此方案處理failover的方式上依舊采用Heartbeat,不同的是,在數(shù)據(jù)共享方面,采用了基于塊級其余數(shù)據(jù)同步軟件DRBD來實(shí)現(xiàn).

DRBD是一個用軟件實(shí)現(xiàn)的、無共享的、服務(wù)器之間鏡像塊設(shè)備內(nèi)容的存儲復(fù)制辦理方案.和SAN網(wǎng)絡(luò)不同,它并不共享存儲,而是通過服務(wù)器之間的網(wǎng)絡(luò)復(fù)制數(shù)據(jù).

四、MYSQL經(jīng)典應(yīng)用架構(gòu)

此中：

Dbm157是mysql主,dbm158是mysql主的備機(jī),dbs159/160/161是mysql從.

MySQL寫操作一般采納基于heartbeat+DRBD+MySQL搭建高可用集群的方案.通過heartbeat實(shí)現(xiàn)對mysql主進(jìn)行狀態(tài)監(jiān)測,而DRBD實(shí)現(xiàn)dbm157數(shù)據(jù)同步到dbm158.

讀操作普遍采納基于LVS+Keepalived搭建高可用高擴(kuò)展集群的方案.前端AS應(yīng)用通過提高的讀VIP連接LVS,LVS有keepliaved做成高可用模式,實(shí)現(xiàn)互備.

最后,mysql主的從節(jié)點(diǎn)dbs159/160/161通過mysql主從復(fù)制功效同步mysql主的數(shù)據(jù),通過lvs功效提供給前端AS應(yīng)用進(jìn)行讀操作,并實(shí)現(xiàn)負(fù)載均衡.