由于mysql的局限性，很多站點都采用了mysql+memcached的架構。

另外一些站點放棄了mysql，采用了NoSQL，比如TokyoCabinet/Tyrant等等。
不可否認，在做一些簡單查詢（尤其是primary key 查詢）的時候，NoSQL比mysql要快很多很多。而且網站上的絕大多數查詢都是這樣的簡單查詢。

但是DeNA公司卻一直使用mysql和memcached，并且在單臺普通服務器上創造了每秒750,000次簡單查詢的記錄。

或許你們不會相信單臺mysql可以做到750,000 qps，但這是事實，下面就來詳細說說。

SQL真的適合做primary key 查詢么？

DeNA 公司的應用經常要進行primary key(PK)查詢。比如根據user id取出userinfo，根據diary id取出日志內容， memcached和NoSQL很適合做這種事情。
如果進行memcached 的測試，很有可能每秒可以進行400,000次get操作,即使memcached和client位于不同的服務器。
在一臺2.5GHz8核Nehalem 3塊千M網卡的服務器上，libmemcached和memcached每秒可以進行420,000次get 操作。

mysql5呢？每秒可以做多少次PK查詢？
innodb:

[matsunobu@host?~]$?mysqlslap?--query="select?user_name,..??from?test.user?where?user_id=1"?/  --number-of-queries=10000000?--concurrency=30?--host=xxx?-uroot??-p

此外也可以使用 sysbench或者super-smack等工具進行benchmark。

新開一個shell看一下：

[matsunobu@host?~]$?mysqladmin?extended-status?-i?1?-r?-uroot?/  |?grep?-e?"Com_select"  ?  ?  |?Com_select????????????????????????|?107069?????|  |?Com_select????????????????????????|?108873?????|  |?Com_select????????????????????????|?108921?????|  |?Com_select????????????????????????|?109511?????|  |?Com_select????????????????????????|?108084?????|  |?Com_select????????????????????????|?108115?????|

100,000 qps 大概是memcached的1/4左右。
為什么會慢這么多呢？
服務器內存足夠，這些數據應該都在內存中。
同樣是內存操作，為什么mysql比memcached慢這么多？

vmstat數據如下：

[matsunobu@host?~]$?vmstat?1  r??b??swpd???free???buff??cache??????in?????cs?us?sy?id?wa?st  23??0?????0?963004?224216?29937708?58242?163470?59?28?12??0??0  24??0?????0?963312?224216?29937708?57725?164855?59?28?13??0??0  19??0?????0?963232?224216?29937708?58127?164196?60?28?12??0??0  16??0?????0?963260?224216?29937708?58021?165275?60?28?12??0??0  20??0?????0?963308?224216?29937708?57865?165041?60?28?12??0??0

%user和%system占用的CPU都相當高。

再看看oprofile統計出來的信息：
ps:這個工具不錯,內核級別的。

samples??%????????app?name?????????????????symbol?name  259130????4.5199??mysqld???????????????????MYSQLparse(void*)  196841????3.4334??mysqld???????????????????my_pthread_fastmutex_lock  106439????1.8566??libc-2.5.so??????????????_int_malloc  94583?????1.6498??bnx2?????????????????????/bnx2  84550?????1.4748??ha_innodb_plugin.so.0.0.0?ut_delay  67945?????1.1851??mysqld???????????????????_ZL20make_join_statistics  P4JOINP10TABLE_LISTP4ItemP16st_dynamic_array  63435?????1.1065??mysqld???????????????????JOIN::optimize()  55825?????0.9737??vmlinux??????????????????wakeup_stack_begin  55054?????0.9603??mysqld???????????????????MYSQLlex(void*,?void*)  50833?????0.8867??libpthread-2.5.so????????pthread_mutex_trylock  49602?????0.8652??ha_innodb_plugin.so.0.0.0?row_search_for_mysql  47518?????0.8288??libc-2.5.so??????????????memcpy  46957?????0.8190??vmlinux??????????????????.text.elf_core_dump  46499?????0.8111??libc-2.5.so??????????????malloc

MYSQLparse是5.x版本中的，在4.x中是YYparse

MYSQLparse() 和 MYSQLlex()是在mysql解析sql語句的時候調用到的。

make_join_statistics()和JOIN::optimize() 是在query optimization（查詢優化）階段調用到的。

正是因為使用了SQL語句，才會有這些額外的負擔。

從oprofile的輸出可以得到如下結論：
SQL層嚴重影響到了mysql查詢的性能。

與memcached和SQL比起來，mysql要額外做一些工作：
* Parsing SQL statements 解析sql語句
* Opening, locking tables 打開并鎖定表
* Making SQL execution plans ???
* Unlocking, closing tables 解鎖并關閉表

花榮注：使用mysqli 中的prepared statement API可以避免解析sql語句。

mysql還必須要做大量的并發控制，比如在發送/接收網絡數據包的時候，fcntl()就要被調用很多很多次。
Global mutexes ：LOCK_open LOCK_thread_count 也被頻繁地調用。
所以在oprofile的輸出中，排在第二位的是my_pthread_fastmutex_lock()。并且%system占用的CPU相當高(28%)。
其實 mysql開發團隊和一些外圍的開發團體都了解大量并發控制對性能的影響，
他們在mysql 5.5中已經解決了某些問題。未來的mysql中，%system占用的cpu會越來越少。

但是，%user占用的60%cpu怎樣處理呢？

Mutex?contentions?result?in?%system?increase,?not?%user?increase

即使所有的并發問題都得到處理，估計也很難做到300,000 qps。

或許你聽說過HANDLER statement的性能也不錯。
可是HANDLER statement需要query parsing，需要open/close table。
不會有太大的幫助。

在完全內存操作的情況下，CPU的效率非常重要

如果只有一小部分數據能夠進入內存，那么SQL語句的解析帶來的額外負擔已經不算什么了。
因為磁盤的IO操作會消耗更長的時間。

在我們的mysql服務器中，內存是大大的，幾乎所有的數據都可以放進內存。
SQL層就變成了額外負擔,占用了大量的cpu資源。

在線上的應用中，我們要進行大量的PK查詢。即使70-80%的查詢都是在同一張表上進行的， mysql還是每次都要parse/open/lock/unlock/close，看起來就感覺效率低下。

We?needed?to?execute?lots?of?primary?key?lookups(i.e.?SELECT?x?FROM?t?WHERE?id=?)?or?limited?range?scans.?Even?though?70-80%?of?queries?were?simple?PK?lookups  ?from?the?same?table?(difference?was?just?values?in?WHERE),?every?time  ?MySQL?had?to?parse/open/lock/unlock/close,?which?seemed?not?efficient?for?us.

花榮注：難道說mysql中的table_open_cache不是用來減少table open的次數的么。。

NDBAPI

有沒有辦法在sql層進行優化呢？

http://dev.mysql.com/doc/ndbapi/en/index.html

如果你使用mysql cluster, NDBAPI會是最佳解決方案。

It’s?recommended?using?NDBAPI?for?frequent?access?patterns,  and?using?SQL?+?MySQL?+?NDB?for?ad-hoc?or?infrequent?query?patterns.

這就是我們想要的：
1 faster access API.
2 sql語句仍然要可用,以處理一些特定的或者復雜的查詢。

但是，把innodb轉化成ndb可不是一件輕松的事情。

HandlerSocket Plugin

最好的辦法是在mysql內部實現一個NoSQL的網絡服務。daemon plugin。
它監聽在某個端口，接受NoSQL 協議/API的數據包，使用Mysql internal storage engine API直接在innodb數據表上進行操作，并且返回相應的數據。
關于mysql internal storage engine API可以看這個文檔：

http://www.php.cn/

這個概念首先被Cybozu Labs 的Kazuho Oku 提出，然后他寫了一個MyCached UDF，用的是memcached的協議。

http://developer.cybozu.co.jp/kazuho/2009/08/mycached-memcac.html

隨后，Akira Higuchi 寫了另外一個plugin: HandlerSocket。

http://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

從圖中可以看到，客戶端既可以使用普通的mysql api來操作mysql(3306端口)，
也可以使用HandlerSocket API對數據庫進行PK查詢，以及INSERT/UPDATE/DELETE操作(9998與9999端口)。

在使用HandlerSocket操作的時候，省去了SQL parsing, Opening table, Making Query Plans, Closing table等步驟。

而且數據都是保存在原來的INNODB表中的。

在HandlerSocket操作innodb數據表的時候，顯然也需要open/close table。
但它只打開一次，然后會reuse。
由于open/close table非常耗時，并且會帶來嚴重的mutex競爭，所以這種改進，極大地提升了性能。

Of course HandlerSocket closes tables when traffics become small etc so that it won’t block administrative commands (DDL) forever.

memcached主要用來緩存數據集(database records)。
因為memcached的get操作比mysql的PK查詢要快得多。
如果HandlerSocket獲取數據的速度比memcached還要快，那么我們就沒必要再使用memcached來緩存數據集了。做做其它緩存還是可以的。比如生成出來的HTML代碼，還有一些統計數據等等。

花榮注：貌似我從來沒有直接把mysql的數據集扔到memcached中。一直都是把中間結果保存到里面。難道走偏了？

使用 HandlerSocket

看下面這張user表：

CREATE?TABLE?user?(  user_id?INT?UNSIGNED?PRIMARY?KEY,  user_name?VARCHAR(50),  user_email?VARCHAR(255),  created?DATETIME  )?ENGINE=InnoDB;

在mysql中取出數據的方法如下：

mysql&gt;?SELECT?user_name,?user_email,?created?FROM?user?WHERE?user_id=101;  +---------------+-----------------------+---------------------+  |?user_name?????|?user_email????????????|?created?????????????|  +---------------+-----------------------+---------------------+  |?Yukari?Takeba?|?yukari.takeba@dena.jp?|?2010-02-03?11:22:33?|  +---------------+-----------------------+---------------------+

在HandlerSocket中怎樣操作呢？

安裝HandlerSocket

詳細文檔看這里：

http://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/installation.en.txt

大概安裝步驟如下：
1 下載

http://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

2 編譯 HandlerSocket客戶端和服務器端程序：

./configure?--with-mysql-source=...?--with-mysql-bindir=...?;?make;?make?install

3 安裝插件

mysql&gt;?INSTALL?PLUGIN?'HandlerSocket'?SONAME?'HandlerSocket.so';

安裝完畢。不需要修改mysql的源代碼。
mysql需要5.1或者以后版本。

編寫HandlerSocket 客戶端代碼

目前HandlerSocket客戶端只有C++ 和 Perl的庫。還沒有php和C的。

@@######@@

這段腳本從user表中取出來了 user_name, user_email 和created字段。

1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32

對于HandlerSocket，推薦使用persistent connection。以減少數據庫連接的次數。

HandlerSocket協議 is a small-sized text based protocol。
與memcached類似，可以使用telnet來獲取數據。

?  #!/usr/bin/perl  ?  use?strict;  use?warnings;  use?Net::HandlerSocket;  ?  #1.?establishing?a?connection  my?$args?=?{?host?=&gt;?'ip_to_remote_host',?port?=&gt;?9998?};  my?$hs?=?new?Net::HandlerSocket($args);  ?  #2.?initializing?an?index?so?that?we?can?use?in?main?logics.  ?#?MySQL?tables?will?be?opened?here?(if?not?opened)  my?$res?=?$hs-&gt;open_index(0,?'test',?'user',?'PRIMARY',  ????'user_name,user_email,created');  die?$hs-&gt;get_error()?if?$res?!=?0;  ?  #3.?main?logic  ?#fetching?rows?by?id  ?#execute_single?(index?id,?cond,?cond?value,?max?rows,?offset)  $res?=?$hs-&gt;execute_single(0,?'=',?[?'101'?],?1,?0);  die?$hs-&gt;get_error()?if?$res-&gt;[0]?!=?0;  shift(@$res);  for?(my?$row?=?0;?$row?[$row?+?0];  ??my?$user_email=?$res-&gt;[$row?+?1];  ??my?$created=?$res-&gt;[$row?+?2];  ??print?"$user_name/t$user_email/t$created/n";  }  ?  #4.?closing?the?connection  $hs-&gt;close();

Benchmarking

測試環境：

[matsunobu@host?~]$?perl?sample.pl  Yukari?Takeba???yukari.takeba@dena.jp???2010-02-03?11:22:33

仍然使用上面的user表，數據大概100w行。
SQL語句為：

[matsunobu@host?~]$?telnet?192.168.1.2?9998  Trying?192.168.1.2...  Connected?to?xxx.dena.jp?(192.168.1.2).Escape?character?is?'^]'.  P???????0???????test????user????PRIMARY?user_name,user_email,created  0???????1  0???????=???????1???????101  0???????3???????Yukari?Takeba???yukari.takeba@dena.jp???2010-02-03?11:22:33

memcached和HandlerSocket的客戶端代碼都用C/C++編寫。
所有的客戶端程序都位于另外一臺機器上。通過TCP/IP與MYSQL/memcached服務器連接。

測試結果如下：

CPU:?Nehalem?8?cores,?E5540?@?2.53GHz  RAM:?32GB?(all?data?fit?in?the?buffer?pool)  MySQL?Version:?5.1.50?with?InnoDB?Plugin  memcached/libmemcached?version:?1.4.5(memcached),?0.44(libmemcached)  Network:?Broadcom?NetXtreme?II?BCM5709?1000Base-T?x?3

HandlerScket比傳統的mysql快了7.5倍,而且%us的cpu使用率為mysql的3/4。
說明SQL層還是相當耗時的。避開SQL層之后，性能得到了明顯的提升。

HandlerSocket比memcached快了78%。而且%sy占用的cpu比memcached要少得多。
雖然memcached是一個優秀的產品，但是還有很大的提升空間。

花榮注：比如七夜就改寫了memcached的部分代碼。

再來看一下HandlerSocket測試的時候oprofile的輸出：

SELECT?user_name,?user_email,?created?FROM?user?WHERE??userid=?

大部分的CPU用在了網絡數據包的處理，取出數據，等。
排在首位的bnx2是網卡驅動程序。

由于HandlerSocket只是一個插件，最終還會調用innodb引擎的函數去取數據，
所以我們仍然可以使用mysql命令來獲取到統計數據。

approx?qps?server?CPU?util  MySQL?via?SQL?105,000?%us?60%?%sy?28%  memcached?420,000?%us?8%?%sy?88%  HandlerSocket?750,000?%us?45%?%sy?53%

作者注：memcached和HandlerSocket的性能都會受到網絡IO的限制。
上面的測試是在3塊千M網卡的環境下進行的。如果換成單網卡，HandlerSocket大概每秒處理260,000次查詢，memcached每秒處理220,000次查詢。

HandlerSocket的優勢和特性

支持很多類型的查詢

PK肯定沒問題
unique key也可以。
普通key也可以。
limit 語句也可以。
IN 也沒問題。
INSERT/UPDATE/DELETE也沒問題。
不過，一定要用到索引才行。
不使用索引的操作不被支持。 Operations that do not use any index are not supported。
詳細說明看這里：http://www.php.cn/

高并發

HandlerSocket employs epoll() and worker-thread/thread-pooling architecture,the number of MySQL internal threads is limited 。
所以放心地使用persistent connection吧。不會導致mysql connection太高的。

高性能

Not only HandlerSocket eliminates SQL related function calls, but also it optimizes around network/concurrency issues
HandlerSocket不僅避開了SQL層，而且優化了網絡層，并解決了并發的一些問題。

更小的網絡數據包

與mysql協議相比，HandlerSocket協議的數據包更簡單更小。
從總體上來看，網絡傳輸的數據量會減少。

Running limited number of MySQL internal threads

不會導致mysql connection太高。

Grouping client requests

當HandlerSocket接受到請求的時候，每一個處理線程都會收集盡可能多的請求，
然后一次把這些請求執行完畢，并返回數據。
看起來沒什么神奇的，但是卻可以大大地提升性能。
代價是每個請求的響應時間略微變長。
*** Can reduce the number of fsync() calls
*** Can reduce replication delay

至于詳細情況，作者會再寫其它文章。很期待。

No duplicate cache

我們使用memcached的時候，數據會同時緩存到innodb的buffer pool與memcached中。
算是重復的cache吧。
HandlerSocket自身沒有緩存，它完全聽從InnoDB storage engine。

No data inconsistency

緩存的一大問題就是數據何時過期。
HandlerSocket沒有緩存，也就不存在這種問題。

Crash-safe

InnoDB還是相當可靠的。
innodb-flush-log-at-trx-commit=1 加上這句就更保險了。
最多會丟失死機前1秒內的數據。

SQL can be used from mysql clients

我們仍然可以使用SQL語句進行復雜的查詢。
確實方便很多啊。

All operational benefits from MySQL

HandlerSocket作為插件運行于mysql內部，所以mysql的操作，比如SQL, 熱備份，主從，Nagios監視等等，都是支持的。
通過 show global status, show engine innodb status , show processlist 這些都可以看到HandlerSocket的狀態。

No need to modify/rebuild MySQL

無需改動mysql的源代碼或者重新編譯mysql。
線上的環境可以直接使用HandlerSocket。

Independent from storage engines

理論上講，HandlerSocket支持任何的存儲引擎，比如myisam,memory?
不過目前只在mysql5.1 5.5 InnoDB的環境下進行過測試和應用。

缺點與注意事項

Need to learn HandlerSocket APIs

目前只有C++和PERL庫可用。以后肯定會有php擴展出現。

No security

就像其它的NoSQL數據一樣，HandlerSocket沒有任何安全方面的功能。比如權限控制之類的。
HandlerSocket的工作線程使用system權限，所以你的應用可以存取所有的表。
當然可以使用防火墻來限制訪問。

No benefit for HDD bound workloads

如果你的內存不夠大，內存要與硬盤交換數據，那么HandlerSocket的優勢就體現不出來了。
We use HandlerSocket on servers that almost all data fit in memory.

DeNA is using HandlerSocket in production

DeNA公司已經在生產環境上使用HandlerSocket了。
The results are great!
有了HandlerSocket可以省掉很大一批memcached服務器與mysql從服務器。
而且帶寬占用也得到了緩解。

samples??%????????app?name?????????????????symbol?name  984785????5.9118??bnx2?????????????????????/bnx2  847486????5.0876??ha_innodb_plugin.so.0.0.0?ut_delay  545303????3.2735??ha_innodb_plugin.so.0.0.0?btr_search_guess_on_hash  317570????1.9064??ha_innodb_plugin.so.0.0.0?row_search_for_mysql  298271????1.7906??vmlinux??????????????????tcp_ack  291739????1.7513??libc-2.5.so??????????????vfprintf  264704????1.5891??vmlinux??????????????????.text.super_90_sync  248546????1.4921??vmlinux??????????????????blk_recount_segments  244474????1.4676??libc-2.5.so??????????????_int_malloc  226738????1.3611??ha_innodb_plugin.so.0.0.0?_ZL14build_template  P19row_prebuilt_structP3THDP8st_tablej  206057????1.2370??HandlerSocket.so?????????dena::hstcpsvr_worker::run_one_ep()  183330????1.1006??ha_innodb_plugin.so.0.0.0?mutex_spin_wait  175738????1.0550??HandlerSocket.so?????????dena::dbcontext::cmd_find_internal(dena::dbcallback_i&amp;,?dena::prep_stmt?const&amp;,?ha_rkey_function,?dena::cmd_exec_args?const&amp;)  169967????1.0203??ha_innodb_plugin.so.0.0.0?buf_page_get_known_nowait  165337????0.9925??libc-2.5.so??????????????memcpy  149611????0.8981??ha_innodb_plugin.so.0.0.0?row_sel_store_mysql_rec  148967????0.8943??vmlinux??????????????????generic_make_request

?以上就是[轉]Mysql的HandlerSocket插件?的內容，更多相關內容請關注PHP中文網（www.php.cn）！

$?mysqladmin?extended-status?-uroot?-i?1?-r?-p|?grep?“InnoDB_rows_read”  …|?Innodb_rows_read?|?750192?|  |?Innodb_rows_read?|?751510?|  |?Innodb_rows_read?|?757558?|  |?Innodb_rows_read?|?747060?|  |?Innodb_rows_read?|?748474?|  |?Innodb_rows_read?|?759344?|  |?Innodb_rows_read?|?753081?|  |?Innodb_rows_read?|?754375?|

We’ve?been?very?satisfied?with?the?results。  Since?HandlerSocket?plugin?is?Open?Source,?feel?free?to?try.?We’d?be?appreciated?if?you?give?us?any?feedback.

文章版權歸作者所有，未經允許請勿轉載。

THE END