mysql教程

[轉]Mysql的HandlerSocket插件

黄舟

Mar 02, 2017 pm 04:58 PM

由於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> 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> INSTALL PLUGIN &#39;HandlerSocket&#39; SONAME &#39;HandlerSocket.so&#39;;

安装完毕。不需要修改mysql的源代码。
mysql需要5.1或者以后版本。

编写HandlerSocket 客户端代码

目前HandlerSocket客户端只有C++ 和 Perl的库。还没有php和C的。

 
#!/usr/bin/perl
 
use strict;
use warnings;
use Net::HandlerSocket;
 
#1. establishing a connection
my $args = { host => &#39;ip_to_remote_host&#39;, port => 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->open_index(0, &#39;test&#39;, &#39;user&#39;, &#39;PRIMARY&#39;,
    &#39;user_name,user_email,created&#39;);
die $hs->get_error() if $res != 0;
 
#3. main logic
 #fetching rows by id
 #execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, &#39;=&#39;, [ &#39;101&#39; ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
for (my $row = 0; $row < 1; ++$row) {
  my $user_name= $res->[$row + 0];
  my $user_email= $res->[$row + 1];
  my $created= $res->[$row + 2];
  print "$user_name/t$user_email/t$created/n";
}
 
#4. closing the connection
$hs->close();

这段脚本从user表中取出来了 user_name, user_email 和created字段。

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

对于HandlerSocket，推荐使用persistent connection。以减少数据库连接的次数。

HandlerSocket协议 is a small-sized text based protocol。
与memcached类似，可以使用telnet来获取数据。

[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 &#39;^]&#39;.
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

Benchmarking

测试环境：

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

仍然使用上面的user表，数据大概100w行。
SQL语句为：

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

memcached和HandlerSocket的客户端代码都用C/C++编写。
所有的客户端程序都位于另外一台机器上。通过TCP/IP与MYSQL/memcached服务器连接。

测试结果如下：

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%

HandlerScket比传统的mysql快了7.5倍,而且%us的cpu使用率为mysql的3/4。
说明SQL层还是相当耗时的。避开SQL层之后，性能得到了明显的提升。

HandlerSocket比memcached快了78%。而且%sy占用的cpu比memcached要少得多。
虽然memcached是一个优秀的产品，但是还有很大的提升空间。

花荣注：比如七夜就改写了memcached的部分代码。

再来看一下HandlerSocket测试的时候oprofile的输出：

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&, dena::prep_stmt const&, ha_rkey_function, dena::cmd_exec_args const&)
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

大部分的CPU用在了网络数据包的处理，取出数据，等。
排在首位的bnx2是网卡驱动程序。

由于HandlerSocket只是一个插件，最终还会调用innodb引擎的函数去取数据，
所以我们仍然可以使用mysql命令来获取到统计数据。

$ 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 |

作者注：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从服务器。
而且带宽占用也得到了缓解。

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.

以上就是[转]Mysql的HandlerSocket插件的内容，更多相关内容请关注PHP中文网（www.php.cn）！

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

MySQL：世界上最受歡迎的數據庫的簡介Apr 12, 2025 am 12:18 AM

MySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。

MySQL的重要性：數據存儲和管理Apr 12, 2025 am 12:18 AM

MySQL是一個開源的關係型數據庫管理系統，適用於數據存儲、管理、查詢和安全。 1.它支持多種操作系統，廣泛應用於Web應用等領域。 2.通過客戶端-服務器架構和不同存儲引擎，MySQL高效處理數據。 3.基本用法包括創建數據庫和表，插入、查詢和更新數據。 4.高級用法涉及復雜查詢和存儲過程。 5.常見錯誤可通過EXPLAIN語句調試。 6.性能優化包括合理使用索引和優化查詢語句。

為什麼要使用mysql？利益和優勢Apr 12, 2025 am 12:17 AM

選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。

描述InnoDB鎖定機制（共享鎖，獨家鎖，意向鎖，記錄鎖，間隙鎖，下一鍵鎖）。Apr 12, 2025 am 12:16 AM

InnoDB的鎖機制包括共享鎖、排他鎖、意向鎖、記錄鎖、間隙鎖和下一個鍵鎖。 1.共享鎖允許事務讀取數據而不阻止其他事務讀取。 2.排他鎖阻止其他事務讀取和修改數據。 3.意向鎖優化鎖效率。 4.記錄鎖鎖定索引記錄。 5.間隙鎖鎖定索引記錄間隙。 6.下一個鍵鎖是記錄鎖和間隙鎖的組合，確保數據一致性。

MySQL查詢性能差的常見原因是什麼？Apr 12, 2025 am 12:11 AM

MySQL查询性能不佳的原因主要包括没有使用索引、查询优化器选择错误的执行计划、表设计不合理、数据量过大和锁竞争。1.没有索引导致查询缓慢，添加索引后可显著提升性能。2.使用EXPLAIN命令可以分析查询计划，找出优化器错误。3.重构表结构和优化JOIN条件可改善表设计问题。4.数据量大时，采用分区和分表策略。5.高并发环境下，优化事务和锁策略可减少锁竞争。

您什麼時候應該使用複合索引與多個單列索引？Apr 11, 2025 am 12:06 AM

在數據庫優化中，應根據查詢需求選擇索引策略：1.當查詢涉及多個列且條件順序固定時，使用複合索引；2.當查詢涉及多個列但條件順序不固定時，使用多個單列索引。複合索引適用於優化多列查詢，單列索引則適合單列查詢。

如何識別和優化MySQL中的慢速查詢？（慢查詢日誌，performance_schema）Apr 10, 2025 am 09:36 AM

要優化MySQL慢查詢，需使用slowquerylog和performance_schema：1.啟用slowquerylog並設置閾值，記錄慢查詢；2.利用performance_schema分析查詢執行細節，找出性能瓶頸並優化。

MySQL和SQL：開發人員的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

See all articles