【51CTO经典译文】大多数大规模Web应用程序都使用MySQL+Memcached架构，其中许多应用也同时使用了NoSQL数据库，如TokyoCabinet/Tyrant，也有一些人全部放弃MySQL，转投NoSQL的怀抱，曾经有人将这称为NoSQL运动，因为NoSQL数据库在处理一些简单访问模式，如主键查找时，比MySQL的表现更好，大多数Web应用程序的查询都很简单，因此这看上去是一个很合理的决定。
和许多其它大规模网站一样，我们的DeNA（我于2010年8月离开Oracle，加盟了日本最大的社交游戏平台提供商DeNA）多年来都存在类似的问题，但我们得出了不同的结论，最终我们全部使用了MySQL，当然一如既往地使用Memcached作为前端缓存（如预处理的HTML，计数/摘要信息），但我们没有使用Memcached缓存数据行，我们也没有使用NoSQL，因为我们从MySQL获得的性能比其它NoSQL产品更好，在我们的基准测试中，我们在一台普通的MySQL/InnoDB 5.1服务器上获得了750000+QPS的成绩，在生产环境中的性能更优秀。（QPS每秒查询率，每秒查询率QPS是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准，在因特网上，作为域名系统服务器的机器的性能经常用每秒查询率来衡量。）
也许你不相信这个成绩，但我说的是真的，在这篇文章中，我将分享一下我们是如何做到的。
SQL主键查询真得能很快吗？
每秒你可以运行多少次主键查询？DeNA的应用程序需要执行大量的主键查询，如通过用户id获取用户信息，通过日记id获取日志信息，Memcached和NoSQL都能很好地适应这种需求，当你运行简单的多线程“memcached get”基准测试时，每秒大约可以执行400000+次get操作，即使Memcached客户端位于远程服务器上，当我使用最新的libmemcached和memcached测试时，在一台2.5GHz 8核Nehalem处理器，四个Broadcom千兆以太网卡的服务器上，测试成绩是每秒执行420000次get操作。
MySQL执行主键查询需要多长时间？通过基准测试很容易找到答案，只需要从sysbench，super-smack和mysqlslap等运行并行查询即可。
- [matsunobu@host ~]$ mysqlslap --query="select user_name,..
- from test.user where user_id=1" \
- --number-of-queries=10000000 --concurrency=30 --host=xxx –uroot
你可以使用下面的命令检查每秒读取了多少InnoDB行：
- [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 | 108483 |
- | Com_select | 108115 |
- ...
每秒有100000+次查询似乎还不错，但却远远低于Memcached的结果，MySQL实际上做了些什么？从vmstat输出可以看出，%user和%system都很高。
- [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
Oprofile输出显示了更多关于CPU资源消耗的情况。
- 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
在SQL解析阶段调用了MYSQLparse()和MYSQLlex()，在查询优化阶段调用了make_join_statistics()和JOIN::optimize()，这些都是SQL开销，很明显，性能下降主要是由SQL层，而不是InnoDB（存储）层造成的，MySQL做了很多Memcached/NoSQL不需要做的事情，如：
- 解析SQL语句
- 打开，锁住表
- 创建SQL执行计划
- 解锁
- 关闭表
MySQL也做了许多并发控制，例如，在发送/接收网络数据包时多次调用了fcntl()，全局互斥，如LOCK_open，LOCK_thread_count很频繁地创建/释放，这就是为什么oprofile输出中my_pthread_fastmutex_lock()排名第二，%system也不小的原因。
MySQL开发团队和外部社区都知道并发问题，有些问题在5.5中已经得到解决，我很高兴地看到，大量的修复工作已经完成，但同样重要的是%user也达到了60%，互斥竞争导致%system上升，而不是%user上升。虽然MySQL中的所有互斥问题都得到了解决，但不要指望每秒超过30万次查询。
你可能听说过HANDLER语句，遗憾的是，HANDLER语句对提高吞吐量并不会有太多帮助，因为查询解析，打开/关闭表等操作仍然是需要的。
CPU效率对内存中的工作负载非常重要
如果内存中没有合适的活动数据，SQL开销相对来说可以忽略不计，很简单，因为磁盘I/O成本是非常高的，在这种情况下，我们不需要太关心SQL成本。
但在我们的热点MySQL服务器上，几乎所有数据都装入到内存中了，它们完全变成CPU限制，分析结果和上面类似：SQL层消耗了大部分资源。我们需要执行大量的主键查询（如SELECT x FROM t WHERE id=?）或限制范围的扫描，即使70-80%的查询是对相同表的简单主键查询（不同的只是Where子句中的值），每次MySQL都要解析/打开/锁住/解锁/关闭，这对我们来说效率是很低的。
#p#
你听说过NDBAPI吗？
在MySQL的SQL层有什么好的解决办法可以减少CPU资源争用吗？如果你使用的是MySQL集群，NDBAPI可能是最好的解决方案，我在MySQL/Sun/Oracle担任顾问时，我看到许多客户对SQL节点+NDB的性能表现很失望，当使用NDBAPI客户端性能提高N倍后，他们高兴极了，你可以在MySQL集群中同时使用NDBAPI和SQL，建议频繁访问模式使用NDBAPI，即席查询或非频繁模式使用SQL+MySQL+NDB。
这正是我们想要的，我们希望更快速地访问API，我们也希望对即席查询或复杂查询使用SQL，但DeNA使用的是InnoDB，和许多其它Web服务一样，切换到NDB不是小事，嵌入式InnoDB不支持SQL也不没有网络接口，因此它不适合我们。
HandlerSocket插件，一个懂NoSQL网络协议的MySQL插件
我们认为最好的办法是在MySQL内部实现一个NoSQL网络服务器，也就是说，写一个网络服务器作为MySQL插件（守护进程插件）监听指定端口，接受NoSQL协议/API，然后使用MySQL内部存储引擎API直接访问InnoDB。这个方法和NDBAPI类似，但它可以和InnoDB交互，这个概念最初是由Kazuho Oku去年在Cybozu实验室提出并创建了原型，他编写了使用memcached协议的MyCached UDF，我的同事Akira Higuchi实现了另一个插件：HandlerSocket，下图显示了HandlerSocket可以做的事情。
图 1 Hanldersocket是什么？
Hanldersocket是一个MySQL守护进程插件，它让应用程序可以将MySQL当NoSQL使，Hanldersocket的主要目的是与存储引擎，如InnoDB交互，而不需要SQL相关的开销。访问MySQL表时，Hanldersocket仍然需要打开和关闭表，但不是每次访问都要求打开和关闭，因此减少了互斥争夺，极大地提高了系统性能，当流量变小时，Hanldersocket会关闭表，因此它永远不会阻止管理命令（DDL）。
它和使用MySQL+Memcached有什么不同？比较图1和图2，我想你会发现很多差异的，图2显示了典型的Memcached和MySQL用法，Memcached用于缓存数据库记录，这是因为Memcached的get操作比MySQL的内存中/磁盘上的主键查询要快很多，如果HandlerSocket的查询速度和Memcached一样快，我们就不用Memcached缓存记录了。
图 2 MySQL+Memcached的常见架构模式
使用HandlerSocket
举一个例子，假设有一个“user”表，我们需要通过user_id获取用户信息。
- CREATE TABLE user (
- user_id INT UNSIGNED PRIMARY KEY,
- user_name VARCHAR(50),
- user_email VARCHAR(255),
- created DATETIME
- ) ENGINE=InnoDB;
在MySQL中，可以通过SELECT语句获取用户信息。
- 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 |
- +---------------+-----------------------+---------------------+
- 1 row in set (0.00 sec)
下面我们来看看如何使用HandlerSocket完成同样的事情。
首先需要安装HandlerSocket，具体安装步骤请参考这里，基本步骤如下：
1、从这里下载HandlerSocket；
2、生成HandlerSocket（客户端和服务器端）；
- ./configure --with-mysql-source=... --with-mysql-bindir=... ; make; make install
3、安装HandlerSocket
- mysql> INSTALL PLUGIN 'HandlerSocket' soname 'HandlerSocket.so';
因为HandlerSocket是MySQL插件，你可以象使用其它插件，如InnoDB、Q4M和Spider插件那样使用它，也就是说，你不需要修改MySQL源代码，MySQL最好是5.1或更高版本，生成HandlerSocket时需要MySQL源代码和MySQL库。
接下来需要编写HandlerSocket客户端代码，我们提供了C++和Perl客户端库，下面是一个简单的Perl代码示例，它通过主键查询获取一行记录。
- #!/usr/bin/perl
- use strict;
- use warnings;
- use Net::HandlerSocket;
- #1. establishing a connection
- my $args = { host => 'ip_to_remote_host', 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, 'test', 'user', 'PRIMARY',
- 'user_name,user_email,created');
- 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, '=', [ '101' ], 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列，查询条件是user_id=101，因此查询结果和前面的SELECT语句一样。
- [matsunobu@host ~]$ perl sample.pl
- Yukari Takeba yukari.takeba@dena.jp 2010-02-03 11:22:33
对于大多数Web应用程序而言，保持轻量级的HandlerSocket连接是一个很好的做法（持续连接），让大量的请求可以集中于主要逻辑（上面代码中的#3部分）。
HandlerSocket协议是一个小尺寸的基于文本的协议，和Memcached文本协议类似，你可以使用telnet通过HandlerSocket获取数据。
- [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
绿色表示请求数据包，字段必须用Tab键分隔。
基准测试
现在是时候展示我们的基准测试结果了，我使用上面的user表，从多线程远程客户端测试了执行主键查询操作的次数，所有用户数据都装入到内存中（我测试了100万行），我也用类似的数据测试了Memcached（我使用libmemcached和memcached_get()获取用户数据），在MySQL SQL测试中，我使用了一个传统的SELECT语句：“SELECT user_name, user_email, created FROM user WHERE user_id=?”，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%
- MySQL via HandlerSocket 750,000 %us 45% %sy 53%
通过HandlerSocket比传统的SQL语句吞吐量要高出7.5倍，这说明了MySQL的SQL层是非常耗资源的，如果能跳过这一层性能肯定会大大提升。有趣的是，MySQL使用HandlerSocket时的速度比使用Memcached快178%，并且Memcached消耗的%system资源也更多，虽然Memcached是一个很好的产品，但仍然有优化的空间。
下面是oprofile输出内容，是在MySQL HandlerSocket测试期间收集到的，在核心操作，如网络数据包处理，获取数据等的CPU资源消耗（bnx2是一个网络设备驱动程序）。
- 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
因为HandlerSocket是运行在MySQL内部的，并直接与InnoDB打交道，你可以使用常见的SQL命令，如SHOW GLOBAL STATUS获得统计信息，Innodb_rows_read达到了750000+是值得一看的。
- $ mysqladmin extended-status -uroot -i 1 -r | 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 |
- ...
测试用机详细规格如下：
型号：戴尔PowerEdge R710
CPU：Nehalem 8核，E5540@2.53GHz
内存：32GB（所有数据都装入缓冲池）
MySQL版本：5.1.50，InnoDB插件
Memcached/libmemcached版本：1.4.5（Memcached），0.44（libmemcached）
网络：Boradcom NetXtreme II BCM5709 1000Base-T（内建四端口，使用了其中三个）
Memcached和HandlerSocket都做了网络I/O限制，当我测试单个端口时，HandlerSocket的成绩是260000qps，Memcached的成绩是220000qps。
HandlerSocket的特点和优势
HandlerSocket有许多特点和优势，其中有一些是对我们真正有益的。
1、支持大量的查询模式
HandlerSocket支持主键/唯一性查询，非唯一性索引查询，范围扫描，LIMIT和INSERT/UPDATE/DELETE，不支持未使用任何索引的操作，multi_get操作（类似于in(1,2,3)） - 通过单一网络往返获取多行数据 – 也是支持的。
2、可以处理大量并发连接
HandlerSocket连接是轻量级的，因为HandlerSocket采用了epoll()和工作线程/线程池架构，MySQL内部线程的数量是有限的（可以由my.cnf中的handlersocket_threads参数控制），因此你可以建立上千或上万的网络连接，稳定性不会受到任何影响（消耗太多的内存，造成巨大的互斥竞争等，如bug#26590，bug#33948，bug#49169）。
3、极好的性能
HandlerSocket相对于其它NoSQL阵容性能表现一点也不逊色，事实上，我还没有看到哪个NoSQL产品在一台普通服务器上可以执行750000+次查询。
HandlerSocket不仅消除了SQL相关的函数调用，也优化了网络/并发相关的问题。
***更小的网络数据包
HandlerSocket协议和传统MySQL协议相比更简单，更小，因此整个网络的流量也更小。
***运行有限的MySQL内部线程数
参考上面的内容。
***客户端请求分组
当大量的并发请求抵达HandlerSocket时，每个工作线程尽可能多地聚集请求，然后同时执行聚集起来的请求和返回结果，这样可以大大地提高性能，只是要牺牲一点响应时间，例如，你可以得到以下好处，如果有人感兴趣，我会在今后的文章中对它们加以深入的解释。
减少fsync()调用的次数
减少复制延迟
4、没有重复的缓存
当你使用Memcached缓存MySQL/InnoDB记录时，在Memcached和InnoDB缓冲池中均缓存了这些记录，因此效率非常低（内存仍然很贵！），由于HandlerSocket插件访问InnoDB存储引擎，记录可以缓存在InnoDB缓冲池中，这样其它SQL语句就可以重复使用它。
5、没有数据不一致的现象
由于数据只存储在一个地方（InnoDB内），不需要在Memcached和MySQL之间检查数据一致性。
6、崩溃安全
后端存储是InnoDB，它是事务性和崩溃安全的，即使你设置innodb-flush-log-at-trx-commit!=1，在服务器崩溃时也只会丢掉<1秒内的数据。
7、可以从MySQL客户端使用SQL
在许多情况下，人们仍然希望使用SQL（如生产摘要报告），这就是为什么我们不能使用嵌入式InnoDB的原因，大多数NoSQL产品都不支持SQL接口，HandlerSocket仅仅是一个MySQL插件，你可以从MySQL客户端发送SQL语句，当你需要高吞吐量时最好使用HandlerSocket协议。
8、MySQL所有操作都将受益
因为HandlerSocket在MySQL内部运行，因此所有MySQL操作，如SQL，在线备份，复制，通过Nagios/EnterpriseMonitor监控等都是支持的，HandlerSocket获得可以通过普通的MySQL命令监控，如SHOW GLOBAL STAUTS，SHOW ENGINE INNODB STATUS和SHOW PROCESSLIST等。
9、不需要修改/重建MySQL
因为HandlerSocket是一个插件，它支持MySQL社区版和企业服务器版，无需对MySQL做出任何修改就可以使用。
10、独立于存储引擎
虽然我们只测试了5.1和5.5 InnoDB插件，但HandlerSocket可以和任何存储引擎交互。
注意事项和限制
1、需要学习HandlerSocket API
尽管它很容易使用，但你仍然需要学习如何与HandlerSocket交互，我们提供了C++ API和Perl绑定。
2、没有安全功能
和其它NoSQL数据库类似，HandlerSocket不支持安全功能，HandlerSocket的工作线程以系统用户权限运行，因此应用程序可以访问通过HandlerSocket协议的所有表，当然，你可以象其它NoSQL产品一样使用防火墙过滤数据包。
3、对于HDD绑定工作负载没有优势
对于HDD I/O绑定工作负载，数据库每秒无法执行数千次查询，通常只有1-10%的CPU利用率，在这种情况下，SQL执行层不会成为瓶颈，因此使用HandlerSocket没有什么优势，我们只在数据完全装载到内存的服务器上使用HandlerSocket。
DeNA在生产环境中使用HandlerSocket
我们已经在生产环境中使用了HandlerSocket插件，效果是很明显的，最终我们减少了许多Memcached和MySQL从属服务器，整个网络流量也减少了，目前我们还没有发现任何性能问题（如响应时间慢，延迟等）。
我认为MySQL完全被NoSQL/数据库社区低估了，MySQL的历史悠久，我的前同事们也在不断改进它，从NDBAPI可以看出MySQL有成为NoSQL的潜力，存储引擎API和守护进程接口是完全独立的，使得Akira和DeNA开发HandlerSocket成为可能，作为MySQL一名前员工和对MySQL长期的了解，我想看到MySQL变得更好，更受欢迎，不只作为一个RDBMS，也应该成为NoSQL阵营中的一员。
HandlerSocket插件是开源的，可以免费使用，欢迎你下载和使用，并希望听到你的反馈。
原文出处：Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server
【编辑推荐】
