MySQL如何才能提高響應(yīng)速度

mysql自身的局限性,很多站點(diǎn)都采用了mysql+memcached的經(jīng)典架構(gòu),甚至一些網(wǎng)站放棄mysql而采用nosql產(chǎn)品。不可否認(rèn),在做一些簡單查詢(尤其是pk查詢)的時候,很多nosql產(chǎn)品比mysql要快很多。

MySQL如何才能提高響應(yīng)速度

一、概述

MySQL如何才能提高響應(yīng)速度

二、應(yīng)用場景

MySQL自身的局限性,很多站點(diǎn)都采用了MySQL+Memcached的經(jīng)典架構(gòu),甚至一些網(wǎng)站放棄MySQL而采用NoSQL產(chǎn)品,比如Redis/MongoDB等。不可否認(rèn),在做一些簡單查詢(尤其是PK查詢)的時候,很多NoSQL產(chǎn)品比MySQL要快很多,而且前臺網(wǎng)站上的80%以上查詢都是簡潔的查詢業(yè)務(wù)。

MySQL通過HandlerSocket插件提供了API訪問接口,在我們的基準(zhǔn)測試中,普通的R510服務(wù)器單實例Percona/XtraDB達(dá)到了72W+QPS(純讀),如果采用更強(qiáng)勁的CPU增加更多的網(wǎng)卡,理論上可以獲得更高的性能。而同等條件下Memcached僅有40W+QPS(純讀),并且在R510上Memcached單實例已經(jīng)無法提升性能,因為Memcached對內(nèi)存的一把大鎖限制了它的并發(fā)能力。

Innodb引擎、按主鍵、unique key或索引搜索(也就是說它的SQL的where條件必須是這些);支持limit 語句、IN、INSERT/UPDATE/DELETE。

  • 沒有主鍵、unique key或索引搜索不行!

  • 表必須是Innodb引擎

HandlerSocket和NoSQL這兩者主要的使用場景不同。HandlerSocket主要是用于改善MySQL,優(yōu)化表格的增刪改查以及表格的結(jié)構(gòu)修改等操作,支持密集型CPU操作;而NoSQL作為緩存的功能,支持密集型I/O的操作。

因此,當(dāng)有需要的時候,可以結(jié)合這兩者共同工作。

三、原理

HandlerSocket是MySQL的一個插件,集成在mysqld進(jìn)程中;NoSQL無法實現(xiàn)的復(fù)雜查詢等操作,仍然使用MySQL自身的關(guān)系型數(shù)據(jù)庫實現(xiàn)。在運(yùn)維層面,原來廣泛使用的MySQL主從復(fù)制等經(jīng)驗繼續(xù)發(fā)揮作用,相比其他NoSQL產(chǎn)品,數(shù)據(jù)安全更有保障,原理如圖:

MySQL如何才能提高響應(yīng)速度

可以看出,HandlerSocket繞過MySQL的SQL解析層(SQL Layer),直接訪問MySQL存儲層。另外,HandlerSocket采用epoll和worker thread/thread pooling網(wǎng)絡(luò)架構(gòu),性能更高。

MySQL的架構(gòu)是“數(shù)據(jù)庫管理”和“數(shù)據(jù)管理”分離,即MySQL Server+Storage Engine的模式。MySQL Server是直接與Client交互的一層,它負(fù)責(zé)管理連接線程,解析SQL生成執(zhí)行計劃,管理和實現(xiàn)視圖、觸發(fā)器、存儲過程等這些與具體數(shù)據(jù)操作管理無關(guān)的事情,通過調(diào)用Handler API讓存儲引擎去操作具體的數(shù)據(jù)。Storage Engine通過繼承實現(xiàn)Handler API的函數(shù),負(fù)責(zé)直接與數(shù)據(jù)交互,數(shù)據(jù)存取實現(xiàn)(必須實現(xiàn)),事務(wù)實現(xiàn)(可選),索引實現(xiàn)(可選),數(shù)據(jù)緩存實現(xiàn)(可選)。

MySQL如何才能提高響應(yīng)速度

HandlerSocket是在MySQL的內(nèi)部組件,以MySQL Daemon Plugin的形式提供類似NoSQL的網(wǎng)絡(luò)服務(wù),它并不直接處理數(shù)據(jù),只是偵聽配置好的某個端口方式,接收采用NoSQL/API的通訊協(xié)議,然后通過MySQL內(nèi)部的Handler API來調(diào)用存儲引擎(例如InnoDB)處理數(shù)據(jù)。理論上,HanderSocket可以處理各種MySQL存儲引擎,但是用MyISAM時,會出現(xiàn)插入的數(shù)據(jù)查不出來,這個實際上是構(gòu)造行時第一字節(jié)沒有初始化為0xff,初始化以后就沒有問題,MyISAM也一樣可以支持,但是為了更好地利用內(nèi)存,用HandlerSocket都會搭配InnoDB存儲引擎一起使用。

MySQL如何才能提高響應(yīng)速度

從上圖可以看出,HandlerSocket作為mysql客戶端和mysql的中間層,取代mysql原生的部分?jǐn)?shù)據(jù)、表格處理工作,采用多線程的方式,區(qū)分DDL和DML進(jìn)行操作。這樣的目的是保證在復(fù)雜處理的情況下,能夠高效的進(jìn)行處理。

因為HandlerSocket是以MySQL Daemon Plugin形式存在,所以在應(yīng)用中,可把MySQL當(dāng)NoSQL使用。它最大的功能是實現(xiàn)了與存儲引擎交互,比如InnoDB,而這不需要任何SQL方面的初始化開銷。訪問MySQL的TABLE時,當(dāng)然也是需要open/close table的,但是它并不是每次都去open/close table,因為它會將以前訪問過的table cache保存下來以重復(fù)使用,而opening/closing tables是最耗資源的,而且很容易引起互斥量的爭奪,這樣一來,對于提高性能非常有效。在流量變小時,HandlerSocket會close tables,所以它一般不會阻塞DDL。

HandlerSocket與MySQL+Memcached的區(qū)別在哪呢?對比圖1-2和圖1-3,可從中看出其不同點(diǎn),圖1-3展示了典型的MySQL+Memecached的應(yīng)用架構(gòu)。因為Memcached的get操作比MySQL的內(nèi)存中或磁盤上的主鍵查詢要快很多,所以Memcached用于緩存數(shù)據(jù)庫記錄。若是HandlerSocket的查詢速度和相應(yīng)時間能與Memcached媲美,我們就可以考慮替換Memcached緩存記錄的架構(gòu)層。

MySQL如何才能提高響應(yīng)速度

四、優(yōu)勢劣勢

HandlerSocket的優(yōu)勢和特點(diǎn)

1) 支持多種查詢模式

HandlerSocket目前支持索引查詢(主鍵索引和非主鍵的普通索引均可),索引范圍掃描,LIMIT子句,也即支持增加、刪除、修改、查詢完整功能,但還不支持無法使用任何索引的操作。另外支持execute_multi() 一次網(wǎng)絡(luò)傳輸多個Query請求,節(jié)省網(wǎng)絡(luò)傳輸時間。

2) 處理大量并發(fā)連接

HandlerSocket的連接是輕量級的,因為HandlerSocket采用epoll() 和worker-thread/thread-pooling架構(gòu),而MySQL內(nèi)部線程的數(shù)量是有限的(可以由my.cnf中的handlersocket_threads/handlersocket_threads_wr參數(shù)控制),所以即使建立上千萬的網(wǎng)絡(luò)連接到HandlerSocket,也不會消耗很多內(nèi)存,它的穩(wěn)定性不會受到任何影響(消耗太多的內(nèi)存,會造成巨大的互斥競爭等其他問題,如bug#26590,bug#33948,bug#49169)。

3) 優(yōu)秀的性能

HandlerSocket的性能見文章HandlerSocket的性能測試報告描述,相對于其它NoSQL產(chǎn)品,性能表現(xiàn)一點(diǎn)也不遜色,它不僅沒有調(diào)用與SQL相關(guān)的函數(shù),還優(yōu)化了網(wǎng)絡(luò)/并發(fā)相關(guān)的問題:

  • 更小的網(wǎng)絡(luò)數(shù)據(jù)包:和傳統(tǒng) MySQL 協(xié)議相比,HandlerSocket 協(xié)議更簡短,因此整個網(wǎng)絡(luò)的流量更小。

  • 運(yùn)行有限的MySQL內(nèi)部線程數(shù):參考上面的內(nèi)容。

  • 將客戶端請求分組:當(dāng)大量的并發(fā)請求到達(dá)HandlerSocket時,每個工作線程盡可能多地聚集請求,然后同時執(zhí)行聚集起來的請求和返回結(jié)果。這樣,通過犧牲一點(diǎn)響應(yīng)時間,而大大地提高性能。例如,可以減少fsync()調(diào)用的次數(shù),減少復(fù)制延遲。

4) 無重復(fù)緩存

當(dāng)使用Memcached緩存MySQL/InnoDB記錄時,在Memcached和InnoDB Buffer Pool中均緩存了這些記錄,因此效率非常低(實際上有兩份數(shù)據(jù),Memcached本身可能還需要做HA支持),而采用 HandlerSocket插件, 它直接訪問 InnoDB 存儲引擎,記錄緩存在InnoDB Buffer Pool,于是其它SQL語句還可以重復(fù)使用緩存的數(shù)據(jù)。

5) 無數(shù)據(jù)不一致的現(xiàn)象

由于數(shù)據(jù)只存儲在一個地方(InnoDB存儲引擎緩存區(qū)內(nèi)),不像使用Memcached時,需要在Memcached和MySQL之間維護(hù)數(shù)據(jù)一致性。

6) 崩潰安全

后端存儲是InnoDB引擎,支持事務(wù)的ACID特性,能確保事務(wù)的安全性,即使設(shè)置innodb_flush_log_at_trx_commit=2,若數(shù)據(jù)庫服務(wù)器崩潰時,也只會丟掉

7) SQL/NOSQL并存

在許多情況下,我們?nèi)匀幌M褂肧QL(例如復(fù)雜的報表查詢),而大多數(shù)NoSQL產(chǎn)品都不支持SQL接口,HandlerSocket僅僅是一個 MySQL 插件,我們依然可以通過MySQL客戶端發(fā)送SQL語句,但當(dāng)需要高吞吐量和快速響應(yīng)時,則使用 HandlerSocket。

8) 繼承MySQL的功能

因為HandlerSocket運(yùn)行于MySQL,因此所有MySQL的功能依然被支持,例如:SQL、在線備份、復(fù)制、HA、監(jiān)控等等。

9) 不需要修改/重建MySQL

因為HandlerSocket是一個插件并且開源,所以它支持從任何MySQL源碼、甚至是第三方版本(例如Percona)構(gòu)建,而無需對MySQL做出任何修改。

10) 獨(dú)立于存儲引擎

雖然我們只測試了MySQL-EnterpriseInnoDB和Percona XtraDB插件,但HandlerSocket理論上可以和任何存儲引擎交互。MyISAM通過簡單的修改也是可以被支持的,但是從數(shù)據(jù)緩存而利用內(nèi)存的角度看這個意義不大。

HandlerSocket的缺陷和注意事項

1) 協(xié)議不兼容

HandlerSocket API與Memcached API并不兼容,盡管它很容易使用,但仍然需要一點(diǎn)學(xué)習(xí)來學(xué)會如何與HandlerSocket交互。不過我們可以通過重載Memecached函數(shù)來翻譯到HandlerSocket API。

2) 沒有安全功能

與其它NoSQL數(shù)據(jù)庫類似,HandlerSocket不支持安全功能,HandlerSocket的工作線程以系統(tǒng)用戶權(quán)限運(yùn)行,因此應(yīng)用程序可以通過HandlerSocket協(xié)議訪問所有的表對象,但是可以通過簡單的修改協(xié)議,在my.cnf中增加一個配置項為密碼,連接時通過這個配置的密碼驗證,當(dāng)然也可以通過網(wǎng)絡(luò)防火墻來過濾數(shù)據(jù)包。

3) 對于磁盤IO密集的場景沒有優(yōu)勢

對于IO密集的應(yīng)用場景,數(shù)據(jù)庫每秒無法執(zhí)行數(shù)千次查詢,通常只有1-10%的CPU利用率,在這種情況下,SQL解析不會成為性能瓶頸,因此使用HandlerSocket沒有什么優(yōu)勢,應(yīng)當(dāng)只在數(shù)據(jù)完全裝載到內(nèi)存的服務(wù)器上使用 HandlerSocket。但是對于PCI-E SSD(例如Fusion-IO)設(shè)備,每秒可以提供4w+ IOPS,并且IO設(shè)備本身消耗CPU比較大,使用HandlerSocket依然具有優(yōu)勢。

五、安裝

注意:書上的安裝方式已經(jīng)過時了,版本也較低,不建議使用,建議使用官方的文檔進(jìn)行安裝,Github地址:https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL

安裝文檔:https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/installation.en.txt

下載源碼:可以通過github直接git clone或者是下載也行

安裝步驟:

1. build Handlersocket

./autogen.sh ./configure?--with-mysql-source=/work/mysql-5.1.50?--with-mysql-bindir=/work/mysql-5.1.50-linux-x86_64-glibc23/bin??--with-mysql-plugindir=/work/mysql-5.1.50-linux-x86_64-glibc23/lib/plugin

注意:

  • with-mysql-source:MySQL源代碼目錄
  • with-mysql-bindir:MySQL二進(jìn)制可執(zhí)行文件目錄(mysql_config所在目錄)
  • with-mysql-plugindir:MySQL插件目錄

2. 編譯

make?&&?make?install

3. 配置

編譯之后HandleSocket還不能使用,還需要在MySQL配置文件(my.cnf)中增加以下配置:

[mysqld] #?綁定讀請求端口 loose_handlersocket_port?=?9998 #?綁定寫請求端口 loose_handlersocket_port_wr?=?9999 #?讀請求線程數(shù) loose_handlersocket_threads?=?16 #?寫請求線程數(shù) loose_handlersocket_threads_wr?=?16 #?設(shè)置最大接收連接數(shù) open_files_limit?=?65535

這里增加的這些主要是針對HandleSocket的配置,它有兩個端口,9998讀數(shù)據(jù),9999寫數(shù)據(jù),但通過9998讀的效率更高,這里設(shè)置處理讀寫的線程數(shù)均為16個,另外為了處理更多并發(fā)連接,設(shè)置能打開的文件描述符個數(shù)為65535

此外,InnoDB的innodb_buffer_pool_size或MyISAM的key_buffy_size配置選項關(guān)系到緩存索引,所以盡可能設(shè)置大一些,這樣才能發(fā)揮HandleSocket的潛力。

4. 激活HandleSocket

登陸MySQL執(zhí)行

mysql>?install?plugin?handlersocket?soname?'handlersocket.so';

可以通過show processlist或show plugins來看到HandleSocket

最后需要安裝PHP的擴(kuò)展包PHP HandlerSocket

安裝文檔:https://github.com/tz-lom/HSPHP

PHP用法:

Select

<?php   $c = new HSPHPReadSocket(); $c->connect(); $id?=?$c-&gt;getIndexId('data_base_name',?'table_name',?'',?'id,name,some,thing,more'); $c-&gt;select($id,?'=',?array(42));?//?SELECT?WITH?PRIMARY?KEY $response?=?$c-&gt;readResponse();  //SELECT?with?IN?statement $c?=?new?HSPHPReadSocket(); $c-&gt;connect(); $id?=?$c-&gt;getIndexId('data_base_name',?'table_name',?'',?'id,name,some,thing,more'); $c-&gt;select($id,?'=',?array(0),?0,?0,?array(1,42,3)); $response?=?$c-&gt;readResponse();

Update

<?php $c = new HSPHPWriteSocket(); $c->connect('localhost',9999); $id?=?$c-&gt;getIndexId('data_base_name','table_name','','k,v'); $c-&gt;update($id,'=',array(100500),array(100500,42));?//?Update?row(k,v)?with?id?100500?to??k?=?100500,?v?=?42 $response?=?$c-&gt;readResponse();?//?Has?1?if?OK  $c?=?new?HSPHPWriteSocket(); $c-&gt;connect('localhost',9999); $id?=?$c-&gt;getIndexId('data_base_name','table_name','','k,v'); $c-&gt;update($id,'=',array(100500),array(100500,42),?2,?0,?array(100501,?100502));?//?Update?rows?where?k?IN?(100501,?100502) $response?=?$c-&gt;readResponse();?//?Has?1?if?OK

Delete

<?php $c = new HSPHPWriteSocket(); $c->connect('localhost',9999); $id?=?$c-&gt;getIndexId('data_base_name','table_name','','k,v'); $c-&gt;delete($id,'=',array(100500)); $response?=?$c-&gt;readResponse();?//return?1?if?OK

Insert

<?php $c = new HSPHPWriteSocket(); $c->connect('localhost',9999); $id?=?$c-&gt;getIndexId('data_base_name','table_name','','k,v'); $c-&gt;insert($id,array(100500,'testnvalue')); $response?=?$c-&gt;readResponse();?//return?array()?if?OK

相關(guān)推薦:《mysql教程

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊5 分享