宁静致远

概述

背景

在前MySQL核心开发人员Yoshinori Matsunobu宣传handlerSocket之后，最近这个插件颇受关注 。它的核心思想很简单，在profiling后发现对于简单的主键查询，SQL层的消耗很大。在数据集较小，能够在内存中存放的情况下(此时随机的Read IO可以忽略)，SQL层就成了最大的瓶颈。为方便不能爬墙的同学，RT一下原文给出的剖分结果：

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查询方式下，有很大比例的时间消耗在SQL解析、Query Plan、表锁等SQL层上。因此，如果跳过SQL层，直接与存储引擎进行交互，就可以获取很大程度的性能提升。

基于这一思想，他们的团队开发了HandlerSocket。测试结果显示，单机查询性能能够到达75W+(100w条数据，做纯内存主键查询)，这个数字意味着已经超过了现在绝大多数KV存储系统、甚至缓存系统的性能。

MySQL的Vadim觉得这玩艺儿挺靠谱(enjoyed)，也对它进行了测试，并在MySQL Performance Blog上给出了测试的结果，这个测试关注了数据量大到需要换入换出时handlersocket的性能表现。结论与预期的相符：当数据在内存能装下时，性能稳定在60W+ rps的水平，但当数据大到一定级别时，性能开始下降。此时主要的瓶颈就在于IO，像FusionIO这样强悍的硬件，还可以支撑到40W+，而普通的RAID10就已经惨不忍睹。 也就是说数据量大拼的就是硬件IO性能，此时HandlerSocket在SQL层节省的CPU消耗，在巨大的IO成本前不值一提。

插件结构

再RT一下Yoshinori给出的结构图:

图一：HandlerSocket结构 (来源于slidershare的PPT)

图二: HandlerSocket结构 (来源于原文)

 

这两张图大同小异，但主旨都是在正常的SQL解析层外，HandlerSocket为我们开了一条后门，直接通过MySQL的HandlerInterface与存储引擎打交道。第二张结构图更详细，可明显看出HandlerSocket要做的事情比正常的SQL少很多。

特性

在原文作者列出了HandlerSocket的一些特性，整理了一下相对重要的，再加上自己的一些粗浅的理解：

• 高效简洁的网络框架
  • 在Linux系统上采用epoll的方式驱动，否则采用poll的方式。
  • 单客户端连接的成本很低，PHP等可以方便的使用短连接访问，而不用再担心并发连接数问题（ps. fcicq怀疑新浪微博就是这么杯具的)。
• 高性能。除了直接访问引擎接口外，HS还做了一些工作用来提升性能
  • 很简洁的网络接口，协议包的冗余数据很少，减少网络带宽占用。
  • 自动Group客户端请求
    • 用过MySQL的同学应该都知道批量提交/批量查询的技巧，用来提升性能
    • HandlerSocket往前走了一步，在Server层面为我们做类似的优化，自动Group尽可能多的请求，一次性提交
  • 开放了一个“只能”进行读操作的端口(图二中的9998)
    • 两个端口被称之为ReadPort和WritePort，但不要被名字迷惑。虽然Read端口只能读，但Write Port一样可以进行Read操作
    • 读请求虽然也是个事务，但纯读可以节省很多成本，比如Transaction Commit，以最大限度提高性能
    • 在稍后的测试中，我们将对这两个端口的读性能进行测试
• HandlerSocket支持多种请求
  • 支持主键查找、列索引查找、范围查询、LIMIT
  • 支持INSERT （注意:无法返回生成的key）
  • 支持UPDATE
  • 支持DELETE
• 仍然支持SQL查询 ，对于复杂查询，仍然可以走普通的SQL接口进行访问
• 数据由相对成熟的数据库引擎(InnoDB)管理，崩溃安全性良好，也可以快速恢复
• 不需要重新编译MySQL
• 支持Row-Based的主从同步
  • 由于它跳过了SQL层，所以只能选择存储引擎的行级数据同步，而不能做Statement级别的同步(行级同步在mysql5.1版本引入的，这就是HandlerSocket要求mysql版本5.1+的原因)
  • binlog_format是session相关的变量，HandlerSocket会调用Interface，设置Row-Based同步。
• 运维方面简单，现有大量的MySQL运维工具和经验可以直接使用

源码分析

作为一个较新的开源项目，HandlerSocket的文档比较薄弱。幸好它的代码还是很简单的，有什么疑问翻一下代码基本都能解决。这里就不展开很细致的代码分析，主要分析一下代码层面重要的几个点。

插件实现

图一告诉我们，HandlerSocket和SQL Layer在同一层，但实际上这个地方有点小trick。它以daemon plugin的形式的，在这个意义上说，它和InnoDB/MyISAM等引擎插件在同一层；但在daemon_handlersocket_init里，就自己listen端口、起worker线程、接收请求、直接与存储引擎交互。

没有插件开发经验的同学，理解这个trick可能会稍有些疑惑：它是如何被调度的？它又是如何直接访问其他存储引擎的？

• 插件引擎都会有一套接口规范，具体实现的插件都必须遵守这个接口规范，以函数指针或者类对象继承方式由插件引擎调用。而接口规范一般都有init接口用于插件初始化。HandlerSocket就利用了这一特性，Init时开了一个后门( 代码文件:handlersocket.cpp )
• MySQL有一层Handler层(图1中的HandlerInterface)，它直接与各个存储引擎交互，并负责XA事务的两阶段提交，HandlerSocket调用的就是HandlerInterface的ha_update_row、ha_delete_row、ha_write_row或index_read_map等接口进行CRUD操作。这也是它名字的由来（代码文件 database.cpp）

工作流程

worker thread的流程清晰明了，总体流程如下:

事务模型

工作流程的图示中可以看出，在一次epoll_wait返回的请求，将一并commit，这也是HandlerSocket的基本事务模型：

• 写线程以一个epoll_wait收到的“所有”读写请求作为一个事务
• 事务隔离级别也没有特殊之处，各存储引擎按照配置进行
• 由HandlerInterface管理XA事务，对事务表和非事务表的提交，与正常SQL处理也相同
• 锁冲突也是由各存储引擎处理，MySQL用行锁，InnoDB用行级锁。这里需要注意：如果在3306端口进行了Lock，HandlerSocket一样会阻塞等待。
• 也不会影响到MVCC

协议

HandlerSocket使用了自定义协议进行交互。具体协议有文档说明，参考源码目录docs-en/protocol.en.txt。协议这里就不详细展开，只提一下基本语法：

• 一个命令一行，采用\n分隔，行内每项数据用\t分隔
• 由于\t\n在协议中有特殊含义，如果数据含有\t、\n，就需要进行转义(转义规则设计的有点奇怪，有哪位同学知晓设计思想，欢迎赐教)
  • [0x10-0xff]不转义
  • [0x00-0x0f]表示为两字节: [0x01] [0x40+value]
• NULL用\0表示，以区别长度为0的字符串

调研测试

侧重点

• 插入性能
  • HandlerSocket与SQL性能对比
  • Group提交对时延的影响
• 读取性能
  • HandlerSocket与SQL性能对比
  • Read Port与Write Port性能对比
• 主从同步

测试环境

• 硬件

两台DELL PowerEdge 2950，4核Intel Xeon 5510 @2.66G, 16G内存

• 软件

Red Hat Enterprise Linux AS release 4 (Nahant Update 3)

mysql 5.1.53 Linux-generic-source

HandlerSocket a485973

• 软件配置

MySQL:

innodb_buffer_pool_size = 8G
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 16
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_max_dirty_pages_pct = 90

HandlerSocket:

loose_handlersocket_port = 9998
loose_handlersocket_port_wr = 9999
loose_handlersocket_threads = 4
loose_handlersocket_threads_wr = 1
open_files_limit = 65535

• 表格式

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

• 采用tcprstat测量响应时间

测试

写入性能

SQL

seq 1000000 | sed 's/\(.*\)/INSERT INTO user set user_id=\1, user_name=\1, user_email=\1;/' > handlersocket.sql
time mysql -D test < handlersocket.sql

HandlerSocket

扩展hstest程序,增加测试用例，插入与SQL相同数据。

读性能

SQL

关闭qcache
mysqlslap --query="select user_name from user where user_id=1" --number-of-queries=10000000 --concurrency=30 --host=HOST --port=3306

HandlerSocket Read Port

./hstest test=11 tablesize=1000000  host=10.26.53.34 hsport=9998 num=10000000 num_threads=100 timelimit=10 

HandlerSocket WritePort

在这里，我们需要修改loose_handlersocket_threads_wr，将WritePort的工作线程数为4，保持与ReadPort一致，之后再运行hstest。

结论

简单整理分析一下：

• 写性能约为SQL的3.74倍
• 读性能约为SQL的3.83倍，达到20w左右。测试结果离官方宣称的75w+还有一定距离。应该是测试环境的问题：
  • 硬件
  • mysql版本使用linux-generic版本，未进行configue优化。这个原因可能性较大，因为profile发现锁开销很大，纯读不应该出现这种情况。另外Yoshinori的测试profile结果也显示他们版本的瓶颈在网络层。
• Group提交方式，造成了一定程度的服务时延，平均时延较SQL方式大了1倍左右

总体来看，HandlerSocket有着很不错的性能表现。在以下case应该有不错的应用前景：

• 缓存系统：性能已经接近甚至超过了memcache，还支持固化、崩溃恢复；
• 内存数据库：handlersocket直接用存储引擎做后端，当后端使用InnoDB时，可以理解成一个B树组织、支持Adaptive Hash的内存数据库。虽然几十万级别的数字，对于内存数据库来说，可能还有挖掘潜力，但毕竟这些存储引擎久经考验，数据安全性值得依赖，而且还是免费的。在小数据量高性能存储的场景，HandlerSocket是一个不错的代替方案。

在实际运行的工业系统中，定时清理数据是很常见的需求。如果后端数据库采用MySQL的主从replication架构，一般都是将写请求提交到主库，执行完后，通过主从同步的方式，将命令分发到各从库运行。

 

通常，数据清理都会在深夜中进行。数据规模较小时，这套简单方案可以运行的很好。

但随着数据量的增长，慢慢我们就会发现：清理数据期间开始出现从库落后的情况，系统状态越来越糟，从落后几秒钟，慢慢会涨到落后几分钟，甚至几十分钟。原因很简单：为了保证数据的正确性，MySQL主从同步采用了从库“单执行线程”的方式，完全“串行化”的redo主库的操作。因此，当一个大请求到达从库开始执行时，后面的请求都被这个巨无霸阻塞住了，必须等它执行完，才能轮到后续的请求。不考虑压力、读写并发冲突和MVCC的影响，假定所有执行序列的成本和耗时在主从库是相同的，我们可以得到以下的执行序列：

 

在该处理方式下，从库的落后时间，随着长请求的执行而增长，在它执行完的瞬间达到峰值；之后从库开始“奋力追赶”主库，渐渐从库跟上主库，进入稳定状态。

由于是长请求阻塞住了从库的replication执行线程。我们能很容易的想到：把巨无霸请求切分成很多个小请求？答案确定的。继续上面的示意图，我们将长请求切成小块，和普通请求竞态执行：

 

 

可以看出：

• 由于长请求被切成很多块，在两个块之间，可以插入执行一部分的普通请求，这部分的普通请求，与第一种方案相比被提前运行。
• 最后（最右）的普通请求，与之前相比，并未得到改善，它的落后时间实际上和之前是一样的。
• 但在实际运行环境中，情况会乐观不少，因为清理数据期间一般压力很低，普通请求只占极小的一部分(即短请求较小)，所以，这种方案还是有较明显的改善作用
• 它的主从落后时间，在这个过程中不断增长，并在数据清理完后，很快跟上同步。

随着数据的进一步增长，我们又会发现，这种方案再次面临着问题：

• 由于切分后的长请求，在从库replication期间会插入普通请求，因此数据清离时间跨度与之前相比会变长。
• 随着数据量增大，时间跨度可能将超过操作时间窗口，大量的IO和高负载将影响系统的正常服务；
• 系统将长时间维持在一个主从不同步的状态，数据不一致容易导致应用上的问题；

当发展到这种程度时，数据库系统层面上，一般情况会采用进一步的数据拆分(水平或者垂直sheding)，通过控制库表的数据规模，来控制单个库表清理的工作负载。但仍不能解决从库落后问题，除非拆分后的库极小，小到清理的过程可以看成是轻量操作，但大多数情况下这在运维上不可行。

有没有一个两全其美的方案呢？回顾分析一下，症结在于从库的同步执行线程“单线程串行化”。那只要存在着主库并行、从库串行情况，从库的更新吞吐量，必然会低于主库，当数据清理的长请求过来时，就必然会出现上面的问题。

那我们换一种思路：不用主从同步，而是同时向所有的库发起操作，同时指定主库不写binlog呢？答案是可行的，连接所有的库发查询是没有任何问题的。只要可以实现相关语句不写BINLOG即可，我们可以通过set sql_log_bin变量，来指定session的更新不写BINLOG。

SET sql_log_bin = {0|1}

Disables or enables binary logging for the current session (sql_log_bin is a session variable) if the client has the SUPER privilege. The statement fails with an error if the client does not have that privilege. 

在该处理方式下，主库与从库的运行情况相似，如下图所示

 

通过采用该方案，从库落后时延基本消失。但它也存在着一些问题：

• 只是基本消失，如果正常请求与长请求具有锁关系，那么也会阻塞主从同步。在这种情况下，如果长请求执行时间很长，从库同步执行线程获取不到锁，可能会造成主从停止。默认情况下会尝试获取锁10次，为了防止此种情况发生，一是可以将任务切细（推荐，尽量控制在30s以下），也可以增加从库执行线程获取锁的尝试次数。
• 数据清理期间，主从出现不一致的情况，并未解决
• 操作必须是幂等的，否则出现问题(宕机、网络中断)时，很难重做
• 必须开放SUPER权限

这两个命令是查看MySQL状态很常用的两个命令，它们的语法很相似：

SHOW [GLOBAL | SESSION] STATUS    [LIKE 'pattern' | WHERE expr]
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr]

作用也很类似，都是用于查看MySQL服务器的状态。

在日常使用中，有时会分不清：什么时候用variables,什么时候用status？查看具体某个状态，应该使用哪条命令？

以前本人都是记住了某些常用的几个值，遇到不熟悉的，就两个都试一次，囧。

今天翻了一下手册，才知道了它们的区别：

SHOW VARIABLES shows the values of MySQL system variables.
SHOW STATUS provides server status information

show variables查看的是mysql系统变量，是MySQL系统运行时的参数，如字符集设置、版本信息、默认参数等，除非手动修改，否则运行时一般不会改变；

而status是MySQL服务器运行统计，如打开的表数量、命令计数、qcache计数等。

了解了这些区别后，查询状态时就可以很快的区分出应该用哪条命令了。

一张表，用text存储了一些格式化文本，其中每行都是一些具有特定意义的信息。

现在需要将文本取出，抽取出第六行信息，输出到外部文件用于统计。 解决方案如下：

select substring_index( head, char(10), -1 ) into outfile '/tmp/result'
from ( SELECT SUBSTRING_INDEX(message,char(10),6) as head
       FROM UserOffline2
       WHERE  UID = 172003052
       limit 10000) tmp;

涉及到的知识点：

char()

这个很简单，参数是一个整数，返回相对应的ASCII码。char(10)对应的就是linux风格的换行。

substring_index():

返回到分隔符第n次出现为止的串，下面是官网对它的解释

SUBSTRING_INDEX(str,delim,count) 

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim. 

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

This function is multi-byte safe. 

为不想看鸟文的同学解释一下：

如果n为正数，就返回到前n次出现为止的子串。如果n为负数，就从右向左搜索，返回前|n|次出现的子串。同时它是多字节安全的。

 

对于我们的问题，先做了一次子查询，SUBSTRING_INDEX(message,char(10),6)，将message中前6行的文本取出，再substring_index( head, char(10), -1 )，取出最后一行，得到的结果就是我们需要的第6行数据了。

 

延伸问题：

如果有windows风格换行符(0d0a)，*nix风格（0a），mac风格( 0d )一起出现，如果处理？我的想法是预处理一下，将0d替换成0a，再将0a0a替换成0a。注意，此方法只对utf8编码的文本有效，但对gb系列的编码无效。