我們提供的服務(wù)有：成都網(wǎng)站制作、成都做網(wǎng)站、微信公眾號(hào)開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、淇濱ssl等。為成百上千家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的淇濱網(wǎng)站制作公司
本文主要給大家介紹Percona Server、MariaDB、MySQL應(yīng)該如何選擇，文章內(nèi)容都是筆者用心摘選和編輯的，Percona Server、MariaDB、MYSQL應(yīng)該如何選擇具有一定的針對(duì)性，對(duì)大家的參考意義還是比較大的，下面跟筆者一起了解下主題內(nèi)容吧。
1、Mysql三種存儲(chǔ)引擎
MySQL提供了兩種存儲(chǔ)引擎：MyISAM和 InnoDB，MySQL4和5使用默認(rèn)的MyISAM存儲(chǔ)引擎。從MYSQL5.5開始，MySQL已將默認(rèn)存儲(chǔ)引擎從MyISAM更改為InnoDB。
MyISAM沒有提供事務(wù)支持，而InnoDB提供了事務(wù)支持。
XtraDB是InnoDB存儲(chǔ)引擎的增強(qiáng)版本，被設(shè)計(jì)用來更好的使用更新計(jì)算機(jī)硬件系統(tǒng)的性能，同時(shí)還包含有一些在高性能環(huán)境下的新特性。
2、Percona Server分支
Percona Server由領(lǐng)先的MySQL咨詢公司Percona發(fā)布。
Percona Server是一款獨(dú)立的數(shù)據(jù)庫產(chǎn)品，其可以完全與MySQL兼容，可以在不更改代碼的情況了下將存儲(chǔ)引擎更換成XtraDB。是最接近官方MySQL Enterprise發(fā)行版的版本。
Percona提供了高性能XtraDB引擎，還提供PXC高可用解決方案，并且附帶了percona-toolkit等DBA管理工具箱，
3、MariaDB
MariaDB由MySQL的創(chuàng)始人開發(fā)，MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能輕松成為MySQL的代替品。
MariaDB提供了MySQL提供的標(biāo)準(zhǔn)存儲(chǔ)引擎，即MyISAM和InnoDB，10.0.9版起使用XtraDB（名稱代號(hào)為Aria）來代替MySQL的InnoDB。
4、如何選擇
綜合多年使用經(jīng)驗(yàn)和性能對(duì)比，首選Percona分支，其次是MariaDB，如果你不想冒一點(diǎn)風(fēng)險(xiǎn)，那就選擇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硬盤
如果是磁盤陣列存儲(chǔ)，建議陣列卡同時(shí)配備CACHE及BBU模塊，可明顯提升IOPS。
raid級(jí)別盡量選擇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幾個(gè)選項(xiàng)（nobarrier是xfs文件系統(tǒng)特有的）；
4、內(nèi)核參數(shù)優(yōu)化
修改vm.swappiness參數(shù)，降低swap使用率。RHEL7/centos7以上則慎重設(shè)置為0，可能發(fā)生OOM
調(diào)整vm.dirty_background_ratio、vm.dirty_ratio內(nèi)核參數(shù)，以確保能持續(xù)將臟數(shù)據(jù)刷新到磁盤，避免瞬間I/O寫。產(chǎn)生等待。
調(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，使用獨(dú)立表空間。
調(diào)整innodb_data_file_path = ibdata1:1G:autoextend，不要用默認(rèn)的10M,在高并發(fā)場景下，性能會(huì)有很大提升。
設(shè)置innodb_log_file_size=256M，設(shè)置innodb_log_files_in_group=2，基本可以滿足大多數(shù)應(yīng)用場景。
調(diào)整max_connection（最大連接數(shù)）、max_connection_error（最大錯(cuò)誤數(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左右即可，另外建議關(guān)閉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自身提供的一種高可用解決方案，數(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)自動(dòng)failover，這種方式可以實(shí)現(xiàn)95.000%的SLA。
MMM提供了MySQL主主復(fù)制配置的監(jiān)控、故障轉(zhuǎn)移和管理的一套可伸縮的腳本套件。在MMM高可用方案中，典型的應(yīng)用是雙主多從架構(gòu)，通過MySQL replication技術(shù)可以實(shí)現(xiàn)兩個(gè)云服務(wù)器互為主從，且在任何時(shí)候只有一個(gè)節(jié)點(diǎn)可以被寫入，避免了多點(diǎn)寫入的數(shù)據(jù)沖突。同時(shí)，當(dāng)可寫的主節(jié)點(diǎn)故障時(shí)，MMM套件可以立刻監(jiān)控到，然后將服務(wù)自動(dòng)切換到另一個(gè)主節(jié)點(diǎn)，繼續(xù)提供服務(wù)，從而實(shí)現(xiàn)MySQL的高可用。
3，在這個(gè)方案中，處理failover的方式是高可用集群軟件Heartbeat，它監(jiān)控和管理各個(gè)節(jié)點(diǎn)間連接的網(wǎng)絡(luò)，并監(jiān)控集群服務(wù)，當(dāng)節(jié)點(diǎn)出現(xiàn)故障或者服務(wù)不可用時(shí)，自動(dòng)在其他節(jié)點(diǎn)啟動(dòng)集群服務(wù)。在數(shù)據(jù)共享方面，通過SAN（Storage Area Network）存儲(chǔ)來共享數(shù)據(jù)，這種方案可以實(shí)現(xiàn)99.990%的SLA。
此方案處理failover的方式上依舊采用Heartbeat，不同的是，在數(shù)據(jù)共享方面，采用了基于塊級(jí)別的數(shù)據(jù)同步軟件DRBD來實(shí)現(xiàn)。
DRBD是一個(gè)用軟件實(shí)現(xiàn)的、無共享的、云服務(wù)器之間鏡像塊設(shè)備內(nèi)容的存儲(chǔ)復(fù)制解決方案。和SAN網(wǎng)絡(luò)不同，它并不共享存儲(chǔ)，而是通過云服務(wù)器之間的網(wǎng)絡(luò)復(fù)制數(shù)據(jù)。
其中：
Dbm157是mysql主，dbm158是mysql主的備機(jī)，dbs159/160/161是mysql從。
MySQL寫操作一般采用基于heartbeat+DRBD+MySQL搭建高可用集群的方案。通過heartbeat實(shí)現(xiàn)對(duì)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ù)載均衡。
(二）mysql常用配置的解釋。
skip-external-locking
在某些系統(tǒng)中，lockd鎖管理器可能不能正常工作，可以使用skip-external-locking告訴mysqld不使用外部鎖定。（這意味著你不能在同一個(gè)數(shù)據(jù)目錄運(yùn)行兩個(gè)mysqld云服務(wù)器，而且如果你使用相同內(nèi)存配置，也需要特別注意）
在Mysql4.0版本之后，外部鎖定默認(rèn)是禁用的，而啟用外部鎖定（系統(tǒng)鎖定），如果系統(tǒng)上的lockd不能完全工作（比如Linux系統(tǒng)），很容易造成mysqld死鎖。
明確的禁用外部鎖定，可以通過配置skip-external-locking。 外部鎖定只影響存儲(chǔ)引擎為MyISAM表的訪問。
key_buffer_size = 16M
這個(gè)參數(shù)的含義是：MyISAM表用于索引塊的緩沖區(qū)大小，可以被所有線程所共享，默認(rèn)值是8388608（即8M），它是對(duì)MyISAM表性能影響最大的一個(gè)參數(shù)，如果數(shù)據(jù)庫存儲(chǔ)引擎為InnoDB類型，這個(gè)參數(shù)是無效的。可以通過增大這個(gè)值，以便索引更好的處理所有讀和多寫操作。
這里有兩篇相關(guān)的文章值得參閱： mysql優(yōu)化：Key_buffer_size MySQL的key_buffer_size參數(shù)
max_allowed_packet = 1M
表示一個(gè)數(shù)據(jù)包的最大大小，或任何生成的/中間字符串，或任何mysql_stmt_send_long_data() API 函數(shù)傳遞的參數(shù)。數(shù)據(jù)包消息的緩沖區(qū)被初始化為net_buffer_length的字節(jié)（net_buffer_length默認(rèn)值為16384：16KB，最大可以設(shè)置為1048576：1M），但是在需要的時(shí)候可以增長到max_allowed_packet字節(jié)，這個(gè)參數(shù)的默認(rèn)值是4M，在接收一些大的數(shù)據(jù)包時(shí)可能會(huì)出現(xiàn)錯(cuò)誤，最大可以設(shè)置為1GB。當(dāng)你通過修改這個(gè)變量的值更改消息緩沖區(qū)的大小時(shí)，如果客戶端程序允許的話，建議在客戶端也修改緩沖區(qū)的大小。在客戶端庫，默認(rèn)的max_allowed_packet是1GB，但是個(gè)人的客戶端可能會(huì)重寫這個(gè)值，例如，mysql和mysqldump分別是16 MB和24 MB?？梢酝ㄟ^在命令行設(shè)置或在配置文件中修改max_allowed_packet參數(shù)來更改客戶端的值，需要注意的是：在session會(huì)話級(jí)別中，這個(gè)變量是只讀的。
table_open_cache = 64
表示所有線程打開表的數(shù)量，增加該值會(huì)增加mysqld要求的文件描述符的數(shù)量？你可以通過檢查Opened_tables狀態(tài)變量來確定是否需要增加表緩存數(shù)量（Opened_tables表示已打開的表的數(shù)量）。如果這個(gè)值很大，你又不經(jīng)常使用FLUSH TABLES（這個(gè)命令會(huì)強(qiáng)制關(guān)閉并重新打開所有表），可以增加table_open_cache的值。
table_open_cache和max_connections系統(tǒng)變量影響文件云服務(wù)器保持打開的最大數(shù)量。如果你增加了其中一個(gè)或兩個(gè)值，可能會(huì)超過操作系統(tǒng)上每個(gè)進(jìn)程打開的文件描述符的數(shù)量限制。許多的操作系統(tǒng)允許你增加這個(gè)限制，但是也需要確定操作系統(tǒng)是否有可能增加打開文件的限制，以及如何去做。
table_open_cache的值與max_connections的值存在關(guān)系。例如，對(duì)于200個(gè)并發(fā)運(yùn)行的連接，指定table_open_cache的值 至少為200 * N，其中 N是 任何參與 執(zhí)行 查詢的連接中關(guān)聯(lián) 表的最大 數(shù)量。你還必須為臨時(shí)表和文件保留一些額外的文件描述符。
請(qǐng)確保你的操作系統(tǒng)能夠處理由table_open_cache設(shè)置的隱含打開的文件描述符的數(shù)量。如果table_open_cache值設(shè)置的太高，MYSQL可能使用完文件描述符而拒絕連接，不能執(zhí)行查詢，并會(huì)變的不可靠。還必須需要考慮的是，MyISAM存儲(chǔ)引擎中，每個(gè)唯一打開的表都需要兩個(gè)文件描述符。你可以通過在mysqld的啟動(dòng)選項(xiàng)中配置open-files-limit參數(shù)，來增加MySQL可用的文件描述符的數(shù)量。
sort_buffer_size = 512K
表示為每一個(gè)需要排序的會(huì)話分配一個(gè)指定的緩存區(qū)大小。sort_buffer_size的值不特定于任何存儲(chǔ)引擎，它適用于對(duì)優(yōu)化的一般方式。通過SHOW GLOBAL STATUS命令，如果你發(fā)現(xiàn)每秒鐘有許多的Sort_merge_passes輸出（Sort_merge_passes表示不得不做合并排序算法的數(shù)量），我們就需要考慮增加sort_buffer_size的值，以加快order by 或 group by 等操作的查詢性能（order by和group by的查詢效率很難通過優(yōu)化查詢和改善索引提供效率）。
優(yōu)化器會(huì)嘗試找出有多少空間是必要的，但可以分配更多，直到達(dá)到極限。值得注意的是：如果全局的設(shè)置，大于系統(tǒng)所需要的值，將減緩大部分涉及排序的查詢效率。最好在session會(huì)話級(jí)別增加它的值，而且值針對(duì)那些需要增加sort_buffer_size的session會(huì)話。在Linux系統(tǒng)中，有256KB和2MB的閾值，其中較大的值可能顯著減慢內(nèi)存分配，所以你應(yīng)該考慮那些較小的值。
net_buffer_length = 8K
每個(gè)客戶端線程都與一個(gè)connection連接緩沖區(qū)和結(jié)果緩沖區(qū)相關(guān)聯(lián)，默認(rèn)值是16K。兩者最初的大小都是net_buffer_length，但是根據(jù)需要會(huì)動(dòng)態(tài)的擴(kuò)大到max_allowed_packet設(shè)置的大小，結(jié)果緩沖區(qū)在每一個(gè)sql語句執(zhí)行后都會(huì)縮小到設(shè)置的net_buffer_length。
這個(gè)變量通常不應(yīng)該改變，但是如果你的系統(tǒng)內(nèi)存很小，你可以將其設(shè)置為客戶端語句的期望長度。如果語句的長度超過這個(gè)值，connection連接緩沖區(qū)會(huì)自動(dòng)的擴(kuò)大。net_buffer_length參數(shù)的最大值可以設(shè)置到1M。需要注意的是：在session會(huì)話級(jí)別中，這個(gè)變量是只讀的。
read_buffer_size = 256K
進(jìn)行順序掃描的MyISAM表的 每個(gè)線程，都為它掃描的每個(gè)表分配一個(gè)指定大小的緩沖區(qū)。如果需要做很多的順序掃描，你可能會(huì)增大這個(gè)值，默認(rèn)值為131072（128K）。這個(gè)變量的值應(yīng)該是4KB的倍數(shù)。如果它被設(shè)置為不是4KB的倍數(shù)，它的值將被舍入為4KB的最近倍數(shù)。
該參數(shù)在以下情況時(shí)適用于所有的搜索引擎：
緩存索引在一個(gè)臨時(shí)文件（而不是臨時(shí)表），使用ORDER BY進(jìn)行行排序時(shí)。
進(jìn)行分區(qū)批量插入操作時(shí)。
對(duì)于嵌套查詢緩存結(jié)果。
如果使用 另外一個(gè)存儲(chǔ)引擎，需要為MEMORY表確定內(nèi)存塊大小。 read_buffer_size 最大允許 設(shè)置為 2GB 。
read_rnd_buffer_size = 512K
此變量用于多范圍讀取優(yōu)化，包括MyISAM表以及任何存儲(chǔ)引擎。當(dāng)從一個(gè)有排序操作的MyISAM表的一個(gè)關(guān)鍵分揀操作中讀取行，該行通過該緩沖區(qū)讀取，以避免磁盤尋道。設(shè)置這個(gè)值為較大的值可以顯著的提高ORDER BY操作的性能， 然而，這是分配給每一個(gè)客戶端的緩沖區(qū)，所以不應(yīng)該在全局級(jí)別將其設(shè)置為一個(gè)較大的值。相反，只有在需要進(jìn)行大量查詢操作的客戶端才建議在session會(huì)話級(jí)別增大這個(gè)變量值。read_rnd_buffer_size最大允許設(shè)置為2GB。
myisam_sort_buffer_size = 8M
表示在REPAIR TABLE上進(jìn)行MyISAM索引排序時(shí)，或通過CREATE INDEX、ALTER TABLE創(chuàng)建索引時(shí)，分配的緩沖區(qū)大小。相應(yīng)的，對(duì)于InnoDB引擎，有InnoDB_sort_buffer_size的設(shè)置。
query_cache_size= 8M
表示分配給高速緩存查詢結(jié)果的內(nèi)存量。默認(rèn)情況下，查詢緩存是禁用的。這是通過使用默認(rèn)的query_cache_size為 1M，query_cache_type為0（為0表示不啟用查詢緩存）使用的，這樣做會(huì)顯著降低開銷，因?yàn)槿绻阍O(shè)置了query_cache_size為0，你也需要在啟動(dòng)時(shí)設(shè)置query_cache_type為0。
允許設(shè)置的值為1024的倍數(shù)，其他設(shè)置的值會(huì)四舍五入到最近的那個(gè)值。 需要 注意的是，即使query_cache_type設(shè)置為0，query_cache_size字節(jié)的內(nèi)存也會(huì)被默認(rèn)的分配。
查詢緩存需要一個(gè)最小大小約40 kb的分配結(jié)構(gòu)（具體的值取決于系統(tǒng)結(jié)構(gòu)）。如果設(shè)置的query_cache_size太小，還可能會(huì)產(chǎn)生一些問題。
query_cache_type的值有0、1、2三種，0表示不進(jìn)行任何查詢緩存；1表示 緩存所有可緩存的查詢結(jié)果除了那些以SELECT SQL_NO_CACHE開頭的查詢；2表示只緩存以SELECT SQL_CACHE開頭的查詢結(jié)果。mysql官方doc建議設(shè)置為2。
thread_cache_size = 20
表示云服務(wù)器將會(huì)緩存重用的線程數(shù)量，當(dāng)一個(gè)客戶端斷開連接，如果緩存中線程的數(shù)量小于設(shè)置的thread_cache_size，那么這個(gè)客戶端的線程會(huì)變放入到緩存中。請(qǐng)求的線程如果可能的話，會(huì)從高速緩存中去的線程，當(dāng)緩存為空時(shí)，才會(huì)創(chuàng)建新的線程。如果系統(tǒng)中存在許多新的連接的話，增加這個(gè)變量值可以提高性能。通常情況下，如果你的代碼中很好的做了線程實(shí)現(xiàn)，這種性能改進(jìn)并不顯著。然而，如果你的云服務(wù)器每秒有數(shù)百個(gè)連接，你通常應(yīng)該設(shè)置thread_cache_size足夠高，大多數(shù)新連接都會(huì)使用緩存的線程。通過比較這個(gè)變量與Connections（表示嘗試連接到Mysql云服務(wù)器的數(shù)量（無論是否連接成功））和Threads_created（表示處理connection連接所創(chuàng)建的線程的數(shù)量）狀態(tài)變量之間的區(qū)別，你可以看到線程緩存的高效。
這個(gè)變量的默認(rèn)值是根據(jù)以下公式計(jì)算的，封頂為100：8 + (max_connections / 100)，但是在嵌入式云服務(wù)器（libmysqld）這個(gè)變量是沒有效果的，在MySQL 5.7.2版本之后，這個(gè)參數(shù)也不再可見。
log-bin=mysql-bin
表示啟用二進(jìn)制日志記錄，云服務(wù)器記錄了所有改變數(shù)據(jù)語句的二進(jìn)制日志，用于備份和復(fù)制。
binlog_format=mixed 之前 MySQL中事務(wù)隔離級(jí)別與binlog_format的一點(diǎn)理解中學(xué)習(xí)，這里不再贅述。
innodb_flush_log_at_trx_commit = 2
這個(gè)變量的官方定義是：Controls the balance between strict ACID compliance for commit operations，and higher performance that is possible when commit-related I/O operations are rearranged and done in batches。我自己的理解是用于控制兩種關(guān)系之間的平衡，這兩種關(guān)系：提交操作嚴(yán)格的ACID特性，提交相關(guān)的IO操作被分批的重新排列和完成時(shí)可能帶來的高性能。你可以通過修改這個(gè)變量的默認(rèn)值達(dá)到更好的性能，但是你可能會(huì)在意外崩潰時(shí)丟失一秒的事務(wù)。
默認(rèn)值為 1 完全符合數(shù)據(jù)庫ACID特性，這個(gè)值表示，在每次事務(wù)提交的時(shí)候，InnoDB日志緩沖區(qū)的內(nèi)容都會(huì)被寫入到日志文件，并且日志文件會(huì)被刷新到磁盤。
如果變量值為0，則表示InnoDB日志緩沖區(qū)的內(nèi)容大約每秒被寫入日志文件一次，并且日志文件會(huì)被刷新到磁盤。那些日志緩沖區(qū)中沒有寫入的內(nèi)容會(huì)在事務(wù)提交的時(shí)候被寫入日志文件。由于進(jìn)程調(diào)度的問題，每秒的刷新并不能100%保證每一秒都發(fā)生。由于刷新磁盤的操作只有大約每秒才發(fā)生一次，所以在任何mysqld進(jìn)程崩潰的時(shí)候，你都會(huì)喪失一秒的事務(wù)。
如果變量值為2，則表示InnoDB日志緩沖區(qū)的內(nèi)容會(huì)在事務(wù)提交的時(shí)候?qū)懭氲饺罩疚募?，并且日志文件?huì)大約每秒刷新一次磁盤。同樣的，由于進(jìn)程調(diào)度的問題，每秒的刷新并不能100%保證每一秒都發(fā)生。由于刷新磁盤的操作只有大約每秒才發(fā)生一次，所以在操作系統(tǒng)崩潰或突然斷電的時(shí)候，你都會(huì)喪失一秒的事務(wù)數(shù)據(jù)。
在MySQL 5.6.6版本中，InnoDB日志刷新頻率由變量innodb_flush_log_at_timeout,控制，它允許你將日志刷新頻率設(shè)置為N秒（默認(rèn)值是1，可以設(shè)置1到2700之間的整數(shù)值），但是任何mysqld進(jìn)程的崩潰都會(huì)清除高達(dá)N秒的事務(wù)數(shù)據(jù)。
DDL變化和其他內(nèi)部InnoDB的活動(dòng)，則是獨(dú)立的innodb_flush_log_at_trx_commit設(shè)置進(jìn)行InnoDB日志刷新。
InnoDB的崩潰恢復(fù)機(jī)制是不管變量innodb_flush_log_at_trx_commit的設(shè)置的，事務(wù)要么全部應(yīng)用，要么全部刪除。
根據(jù)數(shù)據(jù)庫應(yīng)用設(shè)置的持久性和一致性，建議參考如下方式進(jìn)行InnoDB事務(wù)設(shè)置：
如果啟用了二進(jìn)制日志，設(shè)置sync_binlog=1。
總是設(shè)置innodb_flush_log_at_trx_commit=1。
這么建議的原因是：許多操作系統(tǒng)和一些磁盤硬件愚弄刷新到磁盤的操作，他們可能會(huì)告訴mysqld：刷新操作已經(jīng)發(fā)生，但是事實(shí)上并沒有發(fā)生。然后事務(wù)的持久性即使設(shè)置為1，也不能得到保證。在最糟糕的情況突然斷電甚至?xí)斐蒊nnoDB數(shù)據(jù)的損壞。在SCSI磁盤控制器或本身加速文件刷新的磁盤上使用電池支持的磁盤緩存，會(huì)使操作更安全。你也可以嘗試使用Unix命令hdparm禁用磁盤寫入緩存的硬件高速緩存，或使用特定的硬件供應(yīng)商提供的其他一些命令。
sync_binlog
如果這個(gè)變量的值大于0，MySQL云服務(wù)器會(huì)在sync_binlog提交組被寫入到二進(jìn)制日志之后，使用fdatasync()命令同步二進(jìn)制日志到磁盤。默認(rèn)的sync_binlog變量值為0，表示不同步到磁盤。mysql云服務(wù)器依賴于操作系統(tǒng)不時(shí)的刷新二進(jìn)制文件的內(nèi)容，用于任何其他文件。值為1是最安全的選擇，因?yàn)樵诒罎⒌那闆r下你最多從二進(jìn)制日志丟失一個(gè)提交組。然而，它也是最慢的選擇（除非你你的磁盤具有電池備份緩存，這會(huì)使得同步非?？欤?。
innodb_lock_wait_timeout = 20
表示InnoDB事務(wù)從等待獲取行鎖到放棄的時(shí)間長度，默認(rèn)的值為50秒。一個(gè)事務(wù)試圖獲取被另一個(gè)InnoDB事務(wù)鎖定的行所等待的最大時(shí)間，超時(shí)時(shí)會(huì)發(fā)出以下錯(cuò)誤信息：
ERROR 1205 (HY000)： Lock wait timeout exceeded； try restarting transaction（鎖等待超時(shí)，試圖重啟事務(wù)）
當(dāng)鎖等待超時(shí)后，當(dāng)前的語句會(huì)回滾（并不是整個(gè)事務(wù)回滾）。如果需要整個(gè)事務(wù)都回滾，需要在云服務(wù)器啟動(dòng)時(shí)通過innodb_rollback_on_timeout參數(shù)設(shè)置。
在高度交互的應(yīng)用程序或OTLP系統(tǒng)中，為了更好的用戶反饋或?qū)⒏路湃胍粋€(gè)隊(duì)列等待后續(xù)處理，你可能會(huì)減小該變量值。對(duì)于長期運(yùn)行的后端操作，比如在一個(gè)數(shù)據(jù)倉庫中存在大批量的插入或更新操作等待完成時(shí)，你可能會(huì)增加該值。
innodb_lock_wait_timeout僅適用于InnoDB的行級(jí)鎖。一個(gè)MySQL表鎖不會(huì)發(fā)生在InnoDB，這個(gè)參數(shù)并不適用于等待表鎖。
鎖等待超時(shí)值不適用于死鎖，因?yàn)樵谑聞?wù)死鎖時(shí)，InnoDB會(huì)立即檢測到它們并事務(wù)回滾。
innodb_lock_wait_timeout可以在運(yùn)行時(shí)，通過SET GLOBAL或SET SESSION聲明進(jìn)行設(shè)置。修改全局的設(shè)置需要SUPER權(quán)限，并會(huì)影響接下來所有連接客戶端的操作。任何客戶端都可以在SESSION會(huì)話級(jí)別設(shè)置innodb_lock_wait_timeout，它只會(huì)影響到該客戶端。
至此，線上mysql云服務(wù)器上的配置文件參數(shù)已經(jīng)全部做了整理，對(duì)于這些參數(shù)也有了一定的認(rèn)識(shí)和了解，接下來，對(duì)于經(jīng)常使用到的connections變量做一下整理，為接下來與同事討論mysql的優(yōu)化及設(shè)置做些準(zhǔn)備，主要是以下幾個(gè)connections變量：
max_connections
系統(tǒng)變量，它表示最大允許的并發(fā)客戶端連接數(shù)，會(huì)影響在云服務(wù)器上運(yùn)行的線程數(shù)量，默認(rèn)值是151，增加該值會(huì)增加mysqld請(qǐng)求的文件描述符的數(shù)量。如果所請(qǐng)求的描述符的數(shù)量不可用，云服務(wù)器會(huì)減少max_connections的值。連接拒絕是因?yàn)椋琺ax_connections的最大值，達(dá)到了Connection_errors_max_connections狀態(tài)變量的增量。
thread_cache_size 變量的默認(rèn)值就與max_connections有關(guān)。
max_user_connections
表示允許任何給定的MySQL用戶帳戶同時(shí)連接的最大數(shù)目。默認(rèn)值為0表示不限制。此變量可以在云服務(wù)器啟動(dòng)時(shí)或運(yùn)行時(shí)設(shè)置一個(gè)全局值。它也有一個(gè)只讀會(huì)話值，表示與當(dāng)前會(huì)話相關(guān)聯(lián)的帳戶的有效同時(shí)連接的限制值。會(huì)話級(jí)別的max_user_connections初始化如下：
如果用戶帳戶具有非零的MAX_USER_CONNECTIONS資源限制（帳戶的資源限制通過GRANT語句指定），會(huì)話級(jí)別的MAX_USER_CONNECTIONS值就設(shè)為該限制。
否則的話，會(huì)話級(jí)別的MAX_USER_CONNECTIONS的值會(huì)被設(shè)置為全局值。
Connection_errors_max_connections
表示當(dāng)云服務(wù)器中連接數(shù)達(dá)到max_connections的限制后，連接數(shù)被拒絕的數(shù)量。
Connections
表示嘗試連接到mysql云服務(wù)器的數(shù)量，無論成功或失敗。
Max_used_connections
從云服務(wù)器啟動(dòng)開始，已同時(shí)被使用的最大連接數(shù)。
skip-external-locking作用
在Mysql Linux 的發(fā)行版中，默認(rèn)存在一行skip-external-locking，它表示跳過外部鎖定，與之相對(duì)，External-locking變量表示啟用外 部鎖定，用于多線程條件下對(duì)MyISAM數(shù)據(jù)表進(jìn)行鎖定，默認(rèn)情況下mysql是禁用外部鎖定的，在現(xiàn)實(shí)生產(chǎn)中，我們的業(yè)務(wù)環(huán)境是單云服務(wù)器環(huán)境，不需要外 部鎖定，所以將其禁用。
key_buffer_size 在InnoDB引擎時(shí)無效
在 現(xiàn)實(shí)的生產(chǎn)環(huán)境中，我們?cè)鴮?duì)業(yè)務(wù)庫進(jìn)行過升級(jí)，雖然我們的業(yè)務(wù)庫使用的是InnoDB引擎，但是其中仍然存在幾張遺留的使用MyISAM存儲(chǔ)引擎的表，設(shè) 置這個(gè)參數(shù)也是為了提供對(duì)于這幾張表的訪問性能，用于這幾張表的索引更好的處理讀和多寫操作。另外，在之前windows上安裝mysql 5.5時(shí)，會(huì)默認(rèn)產(chǎn)生幾個(gè)不同生產(chǎn)環(huán)境的my.ini文件，這個(gè)參數(shù)的配置也參閱了其中的一些配置。
table_open_cache=64 設(shè)置是否過小
這個(gè)參數(shù)的設(shè)置最好根據(jù)現(xiàn)實(shí)生產(chǎn)環(huán)境進(jìn)行設(shè)置，在mysql命令行通過show global status like 'open%_tables%'，可以查到兩個(gè)重要的參數(shù)，如下：
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 81 |
| Opened_tables | 88 |
+---------------+-------+
對(duì)與大多數(shù)的云服務(wù)器設(shè)置，建議參考一下公式：Open_tables / Opened_tables >= 0.85；Open_tables / table_open_cache <= 0.95
但是并不是設(shè)置table_open_cache越大越好，因?yàn)閠able_cache加大后，使得mysql對(duì) SQL響應(yīng)的速度更快了，不可避免的會(huì)產(chǎn)生更多的死鎖（dead lock），這樣反而使得數(shù)據(jù)庫整個(gè)一套操作慢了下來，嚴(yán)重影響性能。所以平時(shí)維護(hù)中還是要根據(jù)庫的實(shí)際情況去作出判斷，找到最適合你維護(hù)的庫的 table_open_cache值。
詳請(qǐng)參閱：參數(shù)table_open_cache。
myisam_sort_buffer_size參數(shù)只對(duì)MyISAM引擎有效
在現(xiàn)實(shí)的生產(chǎn)環(huán)境中，雖然我們的業(yè)務(wù)庫使用的是InnoDB引擎，但有多張業(yè)務(wù)表使用的是MyIsam引擎，需要這個(gè)參數(shù)，用于進(jìn)行表恢復(fù)時(shí)使用的緩沖區(qū)的大小，也是參考了mysql 5.5的配置。
net_buffer_length默認(rèn)為16K,設(shè)置為8K？
這個(gè)參數(shù)根據(jù)客戶端connection 語句的長度有關(guān)，現(xiàn)實(shí)的業(yè)務(wù)并不繁雜，語句也比較簡單，參考mysql 5.5的配置，設(shè)置net_buffer_length=8k，不過經(jīng)過與同事討論，決定使用默認(rèn)值16K。
thread_cache_size設(shè)置為20的依據(jù)
這個(gè)參數(shù)的設(shè)置與max_connections有關(guān)，max_connections表示最大允許的并發(fā)客戶端連接數(shù)，會(huì)影響在云服務(wù)器上運(yùn)行的線程數(shù)量，默認(rèn)值是151，thread_cache_size 的設(shè)置，官方doc建議公式：8+（max_connections / 100）,這與現(xiàn)實(shí)的業(yè)務(wù)也存在關(guān)系，當(dāng)云服務(wù)器并發(fā)很大時(shí)，需要修改max_connections的值以滿足業(yè)務(wù)需要，在我們的現(xiàn)實(shí)業(yè)務(wù) 中，thread_cache_size設(shè)置為20較為合適。
innodb_flush_log_at_trx_commit 官方doc建設(shè)不要修改默認(rèn)值
雖然mysql官方doc建議將innodb_flush_log_at_trx_commit設(shè)置為1，但在現(xiàn)實(shí)的業(yè)務(wù)中，客戶對(duì)于業(yè)務(wù)性能的速度很高，默認(rèn)為1表示，在每次事務(wù)提交的時(shí)候，InnoDB日志緩沖區(qū)的內(nèi)容都會(huì)被寫入到日志文件，并且日志文件會(huì)被刷新到磁盤。設(shè)置為2減少了刷新磁盤的操作，雖然在突然斷點(diǎn)或系統(tǒng)崩潰時(shí)可能丟失事務(wù)數(shù)據(jù)，但是在業(yè)務(wù)允許范圍內(nèi)，相反修改此參數(shù)對(duì)于業(yè)務(wù)速度有很大的提升。
read_rnd_buffer_size增加order by查詢效率
在What exactly is read_rnd_buffer_size中有了一點(diǎn)理解，其中提到了read_buffer_size，在三個(gè)方法優(yōu)化MySQL數(shù)據(jù)庫查詢中大概的了解了這個(gè)參數(shù)的作用，當(dāng)一個(gè)查詢不斷地掃描某一個(gè)表，MySQL會(huì)為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變 量控制這一緩沖區(qū)的大小。如果你認(rèn)為連續(xù)掃描進(jìn)行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。不過貌似這兩個(gè)參數(shù)都是值針對(duì)于 MyIsam表的，在mysql安裝目錄my.ini中看到這樣一句注釋：Size of the buffer used for doing full table scans of MyISAM tables。對(duì)于這個(gè)參數(shù)的配置還需要再討論。
1.MySQL數(shù)據(jù)庫主從同步延遲原理。
談到MySQL數(shù)據(jù)庫主從同步延遲原理，得從mysql的數(shù)據(jù)庫主從復(fù)制原理說起，mysql的主從復(fù)制都是單線程的操作(mysql5.6版本之前)，主庫對(duì)所有DDL和DML產(chǎn)生binlog，binlog是順序?qū)懀孕屎芨?；slave的Slave_IO_Running線程會(huì)到主庫取日志，效率會(huì)比較高，slave的Slave_SQL_Running線程將主庫的DDL和DML操作都在slave實(shí)施。DML和DDL的IO操作是隨機(jī)的，不是順序的，因此成本會(huì)很高，還可能是slave上的其他查詢產(chǎn)生lock爭用，由于Slave_SQL_Running也是單線程的，所以一個(gè)DDL卡主了，需要執(zhí)行10分鐘，那么所有之后的DDL會(huì)等待這個(gè)DDL執(zhí)行完才會(huì)繼續(xù)執(zhí)行，這就導(dǎo)致了延時(shí)。有朋友會(huì)問：“主庫上那個(gè)相同的DDL也需要執(zhí)行10分，為什么slave會(huì)延時(shí)？”，答案是master可以并發(fā)，Slave_SQL_Running線程卻不可以。
2.MySQL數(shù)據(jù)庫主從同步延遲是怎么產(chǎn)生的。
當(dāng)主庫的TPS并發(fā)較高時(shí)，產(chǎn)生的DDL數(shù)量超過slave一個(gè)sql線程所能承受的范圍，那么延時(shí)就產(chǎn)生了，當(dāng)然還有就是可能與slave的大型query語句產(chǎn)生了鎖等待。
3.MySQL數(shù)據(jù)庫主從同步延遲解決方案
最簡單的減少slave同步延時(shí)的方案就是在架構(gòu)上做優(yōu)化，盡量讓主庫的DDL快速執(zhí)行。還有就是主庫是寫，對(duì)數(shù)據(jù)安全性較高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設(shè)置，而slave則不需要這么高的數(shù)據(jù)安全，完全可以講sync_binlog設(shè)置為0或者關(guān)閉binlog，innodb_flushlog也可以設(shè)置為0來提高sql的執(zhí)行效率。另外就是使用比主庫更好的硬件設(shè)備作為slave。
4.MySQL數(shù)據(jù)庫主從同步延遲產(chǎn)生的因素。
1. 網(wǎng)絡(luò)延遲
2. master負(fù)載
3. slave負(fù)載
一般的做法是，使用多臺(tái)slave來分?jǐn)傋x請(qǐng)求，再從這些slave中取一臺(tái)專用的云服務(wù)器，只作為備份用，不進(jìn)行其他任何操作，就能相對(duì)最大限度地達(dá)到’實(shí)時(shí)’的要求了
另外，再介紹2個(gè)可以減少延遲的參數(shù)
–slave-net-timeout=seconds
參數(shù)含義：當(dāng)slave從主數(shù)據(jù)庫讀取log數(shù)據(jù)失敗后，等待多久重新建立連接并獲取數(shù)據(jù)
slave_net_timeout單位為秒 默認(rèn)設(shè)置為 3600秒
| slave_net_timeout | 3600
–master-connect-retry=seconds
參數(shù)含義：當(dāng)重新建立主從連接時(shí)，如果連接建立失敗，間隔多久后重試。
master-connect-retry單位為秒 默認(rèn)設(shè)置為 60秒
通常配置以上2個(gè)參數(shù)可以減少網(wǎng)絡(luò)問題導(dǎo)致的主從數(shù)據(jù)同步延遲
看完以上關(guān)于Percona Server、MariaDB、MYSQL應(yīng)該如何選擇，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業(yè)知識(shí)信息，可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。
重慶分公司
重慶分公司
