Skip to content

{ Tag Archives } MySQL

MySQL 连接

所谓连接查询，就是通过连接，使查询的数据从多个表中检索取得。在 SELECT 的 FROM 子句中写上所有有关的表名，就可以得到由几个表中的数据组合而成的查询结果。连接条件可在 FROM或 WHERE 子句中指定，WHERE 和 HAVING 子句可包含其搜索条件，以供进一步筛选连接之后的结果集。 目前可实现的连接有：自然连接(Natural Join)，内连接(Inner Join), 外连接(Outer Join), 交叉连接(Cross Join) etc.

JOIN USING 可按照指定的列实现表的等值连接。设有两个表t1, t2 具有相同的列 a, b, c, d, 如果不是对全部相同列做连接，而是是对列 a, b 做连接，可写成 t1 JOIN t2 USING(a,b)。
JOIN ON 可按照更一般性条件实现表的等值连接。eg: t1(a, b), t2(a, c), 可写成 t1 JOIN t2 ON t1.a = t2.a。
USING 后用于连接的列，也可用保留字 ON 指定， eg: ... USIGN(a)... 等价于 ... ON t1.a = t2.a..., 使用两者在结果集上体现的不同请见注 1。

示例表 t1, t2, 其数据如下：

mysql> SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 | a    |
| 3 | c    |
| 5 | e    |
| 7 | g    |
+---+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+---+------+
| a | c    |
+---+------+
| 2 | b    |
| 4 | d    |
| 6 | f    |
| 7 | g    |
+---+------+
4 rows in set (0.00 sec)
自然连接
mysql> SELECT * FROM t1, t2 WHERE t1.a = t2.a;
+---+------+---+------+
| a | b    | a | c    |
+---+------+---+------+
| 7 | g    | 7 | g    |
+---+------+---+------+
1 row in set (0.00 sec)
内连接

mysql> SELECT * FROM t1 INNER JOIN t2 USING (a);
+—+——+——+
| a | b | c |
+—+——+——+
| 7 | g | g |
+—+——+——+
1 row in set (0.00 sec)
它等价于：SELECT * FROM t1, t2 WHERE t1.a = t2.a; or SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;。

外连接

外连接是连接的扩展。一般连接操作的结果表由符合连接条件的匹配元组连接起来的新元组构成，其余不符合连接条件的非匹配元组则被丢弃。外连接允许在结果表中保留非匹配元组，空缺部分填以NULL。其作用是在做连接操作时避免丢失信息。外连接有 3 类：
1 左外连接(Left Outer Join)。连接运算谓词为LEFT [OUTER] JOIN，其结果表中保留左关系的所有元组。eg: 

mysql> SELECT * FROM t1 LEFT JOIN t2 USING(a);
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | a    | NULL |
| 3 | c    | NULL |
| 5 | e    | NULL |
| 7 | g    | g    |
+---+------+------+
4 rows in set (0.00 sec)

等价于：SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;。

2 右外连接(Right Outer Join)。连接运算谓词为RIGHT [OUTER] JOIN, 其结果表中保留右关系的所有元组。MySQL 对其的优化策略见注 2。

3 全外连接(Full Outer Join)。连接运算谓词为FULL [OUTER] JOIN, 其结果表中保留左右关系的所有元组。 MySQL 中， FULL JOIN... USING的结果集和INNER JOIN ... USING的结果集相同，且 MySQL 不支持 FULL OUTER JOIN。eg: 

mysql> SELECT * FROM t1 FULL JOIN t2 USING(a);
+---+------+------+
| a | b    | c    |
+---+------+------+
| 7 | g    | g    |
+---+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 FULL OUTER JOIN t2 USING(a);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN t2 USING(a)' at line 1
交叉连接

交叉连接等同于做笛卡尔积。

mysql> SELECT * FROM t1 CROSS JOIN t2;
+---+------+---+------+
| a | b    | a | c    |
+---+------+---+------+
| 1 | a    | 2 | b    |
| 3 | c    | 2 | b    |
| 5 | e    | 2 | b    |
| 7 | g    | 2 | b    |
| 1 | a    | 4 | d    |
| 3 | c    | 4 | d    |
| 5 | e    | 4 | d    |
| 7 | g    | 4 | d    |
| 1 | a    | 6 | f    |
| 3 | c    | 6 | f    |
| 5 | e    | 6 | f    |
| 7 | g    | 6 | f    |
| 1 | a    | 7 | g    |
| 3 | c    | 7 | g    |
| 5 | e    | 7 | g    |
| 7 | g    | 7 | g    |
+---+------+---+------+
16 rows in set (0.00 sec)

它等价于：SELECT * FROM t1, t2; or SELECT * FROM t1 INNER JOIN t2;。

注：

  • USING 和 ON 显示的结果说明
    为了使 MySQL 在解析 NATURAL JOIN 和 JOIN ... USING SQL 时采用 SQL 2003 的标准，从 MySQL 5.0.12 开始，这两种 SQL 的解析都有做调整, NATURAL JOIN 时， 查询结果中只会显示单独的唯一一列， 即 t1.a, t2.2 两列做自然连接后在结果中只有一列 a (= COELSCE(t1.a, t2.a)); 而 JOIN ... USING时，对 USING 中 指定使用做连接的列，查询结果中也只会显示单独的唯一一列， JOIN ... USING形式的变体如 LEFT JOIN ... USING, RIGHT JOIN ... USING 等解析优化也是采用同样的处理。除此之后，其他的连接方式还是遵照以前的规则。详细说明，可查看如下引用部分或MySQL 官方文档 JOIN Syntax一节。

    Join Processing Changes in MySQL 5.0.12
    Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN … USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard.


    The single result column that replaces two common columns is defined using the coalesce operation. That is, for two t1.a and t2.a the resulting single join column a is defined as a = COALESCE(t1.a, t2.a), where:
    COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
    If the join operation is any other join, the result columns of the join consists of the concatenation of all columns of the joined tables. This is the same as previously.

    mysql> SELECT * FROM t1 LEFT JOIN t2 USING (a);
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | a    | NULL |
| 3 | c    | NULL |
| 5 | e    | NULL |
| 7 | g    | g    |
+---+------+------+
4 rows in set (0.01 sec)

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
+---+------+------+------+
| a | b    | a    | c    |
+---+------+------+------+
| 1 | a    | NULL | NULL |
| 3 | c    | NULL | NULL |
| 5 | e    | NULL | NULL |
| 7 | g    |    7 | g    |
+---+------+------+------+
4 rows in set (0.01 sec)
  • 2右外连接的优化策略
    右外连接 SQL 在解析阶段都会转换为只包含左外连接的 SQL，一般遵循如下这样的转换方式：

    T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)

    有关其详细信息，请参考 MySQL 的官方文档7.3.1.10. Outer Join Simplification。

    At the parser stage, queries with right outer joins operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed according to the following rule:
    T1, …) RIGHT JOIN (T2,…) ON P(T1,…,T2,…) =
    (T2, …) LEFT JOIN (T1,…) ON P(T1,…,T2,…)

    通过EXPLAIN EXTENDED, SHOW WARNINGS, 查看 MySQL 解析优化后的右外连接：

    mysql> EXPLAIN EXTENDED SELECT * FROM t1 RIGHT JOIN t2 USING (a);
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL      |    4 |   100.00 |       |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.a |    1 |   100.00 |       |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                            |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c`,`test`.`t1`.`b` AS `b` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`a`)) where 1 |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Also tagged
2011 06 13 gettyying MySQL Base Comments (0)

MySQL 压力测试工具 mysqlslap

FROM: MySQL 压力测试工具 mysqlslap

从 5.1.4 开始，MySQL 自带有一个压力测试工具 mysqlslap， 它通过模拟多个并发客户端访问 MySQL 来执行测试，使用起来非常简单。通过mysqlslap --help可以获得可用的选项，这里列一些主要的参数，更详细的说明参考官方手册。

  • --auto-generate-sql, -a 自动生成测试表和数据。
  • --auto-generate-sql-load-type=type 测试语句的类型。取值包括：read，key，write，update和mixed(默认)。
  • --number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认 1。
  • --number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认 1。
  • --number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)。
  • --query=name,-q 使用自定义脚本执行测试，eg: 可以调用自定义的一个存储过程或者 SQL 语句来执行测试。
  • --create-schema 指定测试的数据库。
  • --commint=N 多少条 DML 后提交一次。
  • --compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。
  • --concurrency=N, -c N 并发量，也就是模拟多少个客户端同时执行 SELECT。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。
  • --engine=engine_name, -e engine_name 创建测试表所使用的存储引擎，可指定多个。
  • --iterations=N, -i N 测试执行的迭代次数。
  • --detach=N执行 N 条语句后断开重连。
  • --debug-info, -T打印内存和CPU的信息。
  • --only-print 只打印测试语句而不实际执行。
  • --defaults-file=mysql_configuration_file_directory 配置文件存放位置。
  • --socket=socket_directory, -S socket_directory socket文件位置。

测试的过程需要生成测试表和测试数据，mysqlslap 会自动生成一个名为 mysqlslap 的 schema，如果已经存在则先删除。可用--only-print来打印实际的测试过程。

[root@localhost bin]# /usr/local/mysql/bin/mysqlslap -a --only-print

DROP SCHEMA IF EXISTS `mysqlslap`;
CREATE SCHEMA `mysqlslap`;
use mysqlslap;
CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));
INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL');
......
DROP SCHEMA IF EXISTS `mysqlslap`;

可以看出，最后会删除一开始创建的 schema，so, 整个测试完成后不会在数据库中留下痕迹。
假如执行一次测试，分别模拟 50 和 100 个并发，都执行 1000 次查询，那么：

[root@localhost bin]# /usr/local/mysql/bin/mysqlslap -a --concurrency=50,100 --number-of-queries=1000 --debug-info

Benchmark
        Average number of seconds to run all queries: 0.676 seconds
        Minimum number of seconds to run all queries: 0.676 seconds
        Maximum number of seconds to run all queries: 0.676 seconds
        Number of clients running queries: 50
        Average number of queries per client: 20

Benchmark
        Average number of seconds to run all queries: 0.922 seconds
        Minimum number of seconds to run all queries: 0.922 seconds
        Maximum number of seconds to run all queries: 0.922 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

User time 0.17, System time 0.40
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 1653, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 14740, Involuntary context switches 6135

Voluntary context switches 7319, Involuntary context switches 681

上结果可以看出，50 和 100 个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次:

[root@localhost bin]# /usr/local/mysql/bin/mysqlslap -a --concurrency=50,100 --number-of-queries=1000 --iterations=5  --debug-info

Benchmark
        Average number of seconds to run all queries: 0.833 seconds
        Minimum number of seconds to run all queries: 0.803 seconds
        Maximum number of seconds to run all queries: 0.865 seconds
        Number of clients running queries: 50
        Average number of queries per client: 20

Benchmark
        Average number of seconds to run all queries: 0.980 seconds
        Minimum number of seconds to run all queries: 0.948 seconds
        Maximum number of seconds to run all queries: 1.007 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

User time 1.03, System time 2.35
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 7148, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 115255, Involuntary context switches 4125

测试同时不同的存储引擎的性能进行对比：

[root@localhost bin]# /usr/local/mysql/bin/mysqlslap -a --concurrency=50,100 --number-of-queries=1000 --iterations=5 --engine=myisam,innodb  --debug-info

Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.781 seconds
        Minimum number of seconds to run all queries: 0.766 seconds
        Maximum number of seconds to run all queries: 0.800 seconds
        Number of clients running queries: 50
        Average number of queries per client: 20

Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.908 seconds
        Minimum number of seconds to run all queries: 0.880 seconds
        Maximum number of seconds to run all queries: 0.929 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.897 seconds
        Minimum number of seconds to run all queries: 0.858 seconds
        Maximum number of seconds to run all queries: 0.929 seconds
        Number of clients running queries: 50
        Average number of queries per client: 20

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 1.189 seconds
        Minimum number of seconds to run all queries: 1.086 seconds
        Maximum number of seconds to run all queries: 1.306 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

User time 2.02, System time 4.24
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 14556, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 172917, Involuntary context switches 52011

100 个并发时，InnoDB 引擎，运行 1000 句 SQL 平均要需要 1.189 秒；而对于 MyISAM, 为 0.908 秒。

测试自定义 SQL:

[root@localhost bin]# /usr/local/mysql/bin/mysqlslap --defaults-file=/usr/local/mysql/etc/my.cnf --create-schema=hstestdb --concurrency=50,100 --number-of-queries=1000 --iterations=5 --query="SELECT * FROM hstestdb.hstesttbl WHERE k='k2'"  --debug-info -u root -p -S /usr/local/mysql/tmp/mysql.sock
Enter password:

Benchmark
        Average number of seconds to run all queries: 0.271 seconds
        Minimum number of seconds to run all queries: 0.256 seconds
        Maximum number of seconds to run all queries: 0.288 seconds
        Number of clients running queries: 50
        Average number of queries per client: 20

Benchmark
        Average number of seconds to run all queries: 0.324 seconds
        Minimum number of seconds to run all queries: 0.292 seconds
        Maximum number of seconds to run all queries: 0.333 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

User time 0.22, System time 0.63
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 4355, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 16691, Involuntary context switches 2837
Also tagged
2011 06 02 gettyying MySQL
压力测试 Comments (0)

PHP extension for interfacing with MySQL Handler Socket

有关于 HandlerSocket 的介绍、性能及其安装，可参考Using SQL as NoSQL。而 PHP extension for interfacing with MySQL Handler Socket，实际上这里php-handlersocket有整体的介绍，包括其安装、使用方法。现在纯粹是因为自己测试时犯了一很基础的错误，所以，罚自己多敲点字。

安装

[root@localhost php-handlersocket]# /usr/local/php/bin/phpize
[root@localhost php-handlersocket]# ./configure --with-php-config=/usr/local/php/bin/php-config
[root@localhost php-handlersocket]# make
[root@localhost php-handlersocket]# make install

说明：
1 编译时需要 libhsclient 库(libhsclient – HandlerSocket client library)。
2 安装成功时，在 PHP 的 extension dir 生成一名为 handlersocket.so，将extension=handlersocket.so加入 php.ini, 重启 PHP 服务。

HandlerSocket Class methods

HandlerSocket::construct

创建一 HandlerSocket Object。

HandlerSocket::__construct ( string $host, string $port [,  array $options ] )

参数：

  • $host MySQL 服务器 host name。
  • $port HandlerSocket 的端口地址。

返回值：
返回 HandlerSocket Object。

HandlerSocket::openIndex

在对数据库表做任何的增删改查操作前，必须先选择一索引。

public bool HandlerSocket::openIndex ( int $id, string $db, string $table, string $index, string $fields )

参数：

  • $id HandlerSocket ID; 1 SELECT, 2 UPDATE, 3 INSERT, 4 DELETE。
  • $db 数据库名
  • $table 表名
  • $index 索引名, 可以是手动创建的索引名。这个参数可为空，一般指定时是用于 SELECT，eg: 指定为主键：HandlerSocket::PRIMARY
  • $fields 字段名(多个字段名，用逗号分隔)，可为空。

返回值：
成功时返回 TRUE, 反之亦然。

HandlerSocket::executeSingle

在表上做增删改查操作。

public mixed HandlerSocket::executeSingle ( int $id, string $op, array $fields [, int $limit, int $skip, string $modop, array $values, array $filters, int $invalues_key, array $invalues ] )

参数：

  • $id HandlerSocket ID; 1 SELECT, 2 UPDATE, 3 INSERT, 4 DELETE。
  • $op 操作符，有如下可选项, ‘=’, ‘>=’, ‘<=’, ‘>’, ‘<’, ‘+’。
  • $fields 查询中所用到的字段，数组，其长度必须等于或小于指定的列数。
  • $limit 最多影响的行数(最开始根据这个函数名称有在怀疑这个参数，测试时发现，如果存在满足条件的多条记录时，会根据这个参数指定的值返回记录数)。
  • $skip 在检索记录前忽略掉的行数。
  • $modop 指定修改操作，可选值：’U', ‘D’。
  • $values 数组，用于做 UPDATE 操作时指定修改的值。
  • $filters 过滤的选项。
  • $invalues_key ? (enabled : 0 / disabled : -1).
  • $invalues IN options

返回值:
返回做对应操作时的执行结果。

HandlerSocket::executeMulti

在一次调用中执行多个操作，即多个 HandlerSocket::executeSingle 的合并。

public mixed HandlerSocket::executeMulti ( array $requests )

参数：

  • $requrest 多组 executeSingle 参数，用数组的形式体现。

注意：
等同于：HandlerSocket::executeSingle($requests00, $requests01, ...), HandlerSocket::executeSingle($requests10, ...) ...。
返回结果：
返回做对应操作时的执行结果。

HandlerSocket::executeUpdate

To update a record from a table using an index.

public mixed HandlerSocket::executeUpdate ( int $id, string $op, array $fields, array $values [, int $limit, int $skip, array $filters, int $invalues_key, array $invalues ] )

参数：

  • $id HandlerSocket ID; 2 UPDATE 。
  • $op 操作符，有如下可选项, ‘=’, ‘>=’, ‘<=’, ‘>’, ‘<’, ‘+’。
  • $fields 查询中所用到的字段，数组，其长度必须等于或小于指定的列数。
  • $values UPDAET 时指定修改的值。
  • $limit 最多影响的行数。
  • $skip 在检索记录前忽略掉的行数。
  • $filters 过滤的选项。
  • $invalues_key ? (enabled : 0 / disabled : -1).
  • $invalues IN options

注意：
等同于：HandlerSocket::executeSingle($id, $op, $fields, $limit, $skip, 'U', $values, $filters, $invalues_key, $invalues)。
返回值:
返回做对应操作时的执行结果。

HandlerSocket::executeDelete

To delete a record from a table using an index.

public mixed HandlerSocket::executeDelete ( int $id, string $op, array $fields [, int $limit, int $skip, array $filters, int $invalues_key, array $invalues ] )

参数：

  • $id HandlerSocket ID; 4 DELETE 。
  • $op 操作符，有如下可选项, ‘=’, ‘>=’, ‘<=’, ‘>’, ‘<’, ‘+’。
  • $fields 查询中所用到的字段，数组，其长度必须等于或小于指定的列数。
  • $limit 最多影响的行数。
  • $skip 在检索记录前忽略掉的行数。
  • $filters 过滤的选项。
  • $invalues_key ? (enabled : 0 / disabled : -1).
  • $invalues IN options

注意：
等同于：HandlerSocket::executeSingle($id, $op, $fields, $limit, $skip, 'D', NULL, $filters, $invalues_key, $invalues)。
返回值:
返回做对应操作时的执行结果。

HandlerSocket::executeInsert

To insert a record from a table using an index.

public mixed HandlerSocket::executeInsert ( int $id, array $values )

参数：

  • $id HandlerSocket ID; 3 INSERT 。
  • $values HandlerSocket::openIndex 指定的字段参数所对应的值，但是以数组的形式体现。

注意：
等同于：HandlerSocket::executeSingle($id, '+', $values, 0, 0, NULL, NULL, NULL) ，第三个参数中指定的值必须和在此之前调用 HandlerSocket::openIndex 时第五个参数指定的字段对应。
返回值:
返回做对应操作时的执行结果。

HandlerSocket::getError

取得最近一次的错误信息。

public string HandlerSocket::getError ( void )

返回值:
返回最近的错误信息(时间上)。

Example

测试表 schema：

 CREATE TABLE `hstesttbl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` char(6) DEFAULT NULL,
  `v` char(6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_hstesttbl_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

$host       = 'localhost';
$port       = 9998;
$port_wr    = 9999;
$dbname     = 'hstestdb';
$table      = 'hstesttbl';

//GET
$hs = new HandlerSocket($host, $port);
if (!($hs->openIndex(1, $dbname, $table, HandlerSocket::PRIMARY, 'k,v'))) {
    echo $hs->getError(), PHP_EOL;
    die();
}

$retval = $hs->executeSingle(1, '=', array('k1'), 1, 0);
var_dump($retval);

$retval = $hs->executeMulti(
    array(
        array(1, '=', array('k1'), 1, 0),
        array(1, '=', array('k2'), 1, 0)
    )
);
var_dump($retval);
unset($hs);

//UPDATE
$hs = new HandlerSocket($host, $port_wr);
if (!($hs->openIndex(2, $dbname, $table, '', 'v'))) {
    echo $hs->getError(), PHP_EOL;
    die();
}

if ($hs->executeUpdate(2, '=', array('k1'), array('V1'), 1, 0) === false) {
    echo $hs->getError(), PHP_EOL;
    die();
}

unset($hs);

//INSERT
$hs = new HandlerSocket($host, $port_wr);
if (!($hs->openIndex(3, $dbname, $table, '', 'k,v'))) {
    echo $hs->getError(), PHP_EOL;
    die();
}

if ($hs->executeInsert(3, array('k2', 'v2')) === false) {
    echo $hs->getError(), PHP_EOL;
}
if ($hs->executeInsert(3, array('k3', 'v3')) === false) {
    echo 'A', $hs->getError(), PHP_EOL;
}
if ($hs->executeInsert(3, array('k4', 'v4')) === false) {
    echo 'B', $hs->getError(), PHP_EOL;
}

unset($hs);

//DELETE
$hs = new HandlerSocket($host, $port_wr);
if (!($hs->openIndex(4, $dbname, $table, '', ''))) {
    echo $hs->getError(), PHP_EOL;
    die();
}

if ($hs->executeDelete(4, '=', array('k2')) === false) {
    echo $hs->getError(), PHP_EOL;
    die();
}

PS: 因为建立测试表时忘记指定存储引擎为 InnoDB, 测试 INSERT 操作时，怎样都是失败。后面为了验证问题的出处，用 perl 的 API 做同样的测试操作，结果也是失败。查看表结构后，修改储存引擎为 InnoDB，才成功。只是这个问题的错误信息太难理解，就几个数字，在没找到答案之前，害我还去查看了下 HandlerSocket 的源代码，当然，没有从中得到任何的提示。

Also tagged , ,
2011 06 02 gettyying PHP
PHP extensions Comments (0)

Super Smack

Super Smack 是一个强大的压力测试工具，支持 MySQL, PostgreSQL, Oracle。最开始的版本是由Sasha Pachev写成，由Jeremy Zawodny在维护，而现在，是
Tony Bourke在维护，根据 Tony Bourke 的 开发 log 来看，2005-08-30 后，super smack 就已经停止发布新的版本，但这并不妨碍我们现在继续使用它(这个工具的开发者和维护者很伟大)。

安装

安装有点点麻烦，主要是编译时会出现一些问题。

[root@localhost tmp]# wget http://vegan.net/tony/supersmack/super-smack-1.3.tar.gz
[root@localhost tmp]# tar xvzf super-smack-1.3.tar.gz
[root@localhost super-smack-1.3]# ./configure  --prefix=/usr/local/super-smack --with-mysql --with-mysql-lib=/usr/local/mysql/lib/mysql --with-mysql-include=/usr/local/mysql/include/mysql
[root@localhost super-smack-1.3]# make
[root@localhost super-smack-1.3]# make install
说明

1 编译时，必须指定它所支持的数据库管理系统，否则会报如下错误。

......
configure: error:

You should include support for at least one database!

Reconfigure with one or more of:
  --with-mysql
  --with-pgsql
  --with-oracle

2 选择支持 MySQL 后, 在 MySQL 编译安装的情况下时, 也需要在编译参数中指定 MySQL 的 lib。
3 编译时，dictionary.h 和 super-smack.cc 报错：

......
dictionary.h:93: error: ‘strlen’ was not declared in this scope
super-smack.cc:126: error: ‘strlen’ was not declared in this scope

在 super smack 源代码的 src 目录，找到 dictionary.h, super-smack.cc，分别加上#include <string.h>。
4 编译时，query.cc 报错

query.cc:200: error: cast from ‘char*’ to ‘unsigned int’ loses precision
query.cc:200: error: cast from ‘char*’ to ‘unsigned int’ loses precision
query.cc:219: error: cast from ‘char*’ to ‘unsigned int’ loses precision
query.cc:219: error: cast from ‘char*’ to ‘unsigned int’ loses precision

在 super smack 源代码的 src 目录， 找到 query.cc文件，将上面指定的 200, 219 行中的unsigned int改为unsigned long。

用其测试 MySQL 之前奏

1 看 Makefile 才知道， smack 文件是放在 /usr/share/smacks 这个目录下(开始并不知道源代码中有一份), 产生的数据文件是在/var/smack-data目录下

...
SMACKS_DIR = /usr/share/smacks
DATADIR = /var/smack-data
...

2 根据 MySQL 的参数对应修改 /usr/share/smacks 目录下的 select-key.smack 和 update-select.smack 文件。包括 user, host, db, pass, 还有，最重要的 socket 路径。

3 未将 super smack 的 bin 目录加入 PATH 时，则还需：

gen_data_file "gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d";
#改为
gen_data_file "./gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d";
#或者直接
gen_data_file "/usr/local/super-smack/bin/gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d";

4 因为在 select-key.smack 和 update-select.smack 文件中指定的数据文件是 words.dat, 而安装默认情况下 words.dat 为空文件:

[root@localhost smack-data]# ls -al
total 5320
drwxr-xr-x  2 root root    4096 May 26 18:12 .
drwxr-xr-x 24 root root    4096 May 26 17:25 ..
-rw-r--r--  1 root root 5421337 May 26 17:25 ..  http_auth.dat
-rw-r--r--  1 root root       0 May 26 17:25 ..  words.dat

所以，需将 select-key.smack 和 update-select.smack 文件中指定的数据 words.dat 改为 http_auth.dat(其实为了统一，最好也是 http_auth.dat，因为文件中指定测试的表名是 http_atuh)。

当然，为了不修改，也可以在开始先执行如下命令：

[root@localhost bin]# /usr/local/super-smack/bin/gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d > /var/smack-data/words.dat

5 将 /usr/share/smacks 下文件 copy 到 /usr/local/super-smack/bin/ 目录下：

[root@localhost super-smack]# cp /usr/share/smacks/*  bin/

运行

[root@localhost bin]# ./super-smack -d mysql select-key.smack 20 1000
Query Barrel Report for client smacker1
connect: max=2204ms  min=1ms avg= 221ms from 20 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    40000   0       0       5017.26

参数：

  • -d 指定测试的数据库管理系统的类型。
  • 20 20 个线程
  • 1000 每个线程 1000 个查询

实际上，还有-D参数来指定数据文件，默认路径如前面提到是 /var/smack-data， 这个路径需跟 select-key.smack 和 update-select.smack 指定的一致。

返回结果：

  • max=2204ms min=1ms avg= 221ms from 20 clients 连接的最大、最小及平均花费时间。
  • q_per_s|5017.26 QPS，每秒请求处理数
  • 40000, 脚本中，对查询次数做了翻倍处理, 所以， 20 × 1000 x 2 = 40,000.
Also tagged
2011 05 29 gettyying MySQL
压力测试 Comments (0)

Using MySQL as a NoSQL

FROM: Using MySQL as a NoSQL – A story for exceeding 750,000 qps on a commodity server

由于 MySQL 的局限性，很多站点都采用了 MySQL+Memcached 的架构。另外一些站点放弃 MySQL 而采用 NoSQL，比如 TokyoCabinet/Tyrant 等。不可否认，在做一些简单查询 (尤其 PK 查询) 的时候，NoSQL 比 MySQL 要快很多很多。而且网站上的绝大多数查询都是这样的简单查询。

像其他大规模的公司一样，DeNA 也面临过类似的问题。但最后我们采用的是一完全不同的方法, 仅使用了 MySQL。我们仍然使用 Memcached 做前端缓存(例如，预处理 HTML， 数量/摘要 信息)，但在后端，既没有使用 Memcached 缓存任何记录，也没有使用 NoSQL，这是为什么呢？因为与其他的 NoSQL 产品相比，我们的 MySQL 能得到更好的性能。 在我们的基准测试中，一普通的 MySQL/InnoDB 5.1 服务器达到了 750,000+ QPS，生产环境中的性能当然更不列外。或许，你们很难相信这个数字，但这是事实。我将在以下分享我们的经验。

(作者经历)2010-08，我离开了 Oracle, 现在任职于日本最大社交游戏平台供应商之一的 DeNA。

SQL 真的适合做 PK 查询？

在每秒中，需要做多少次的 PK 查询了？在 DeNA 公司的应用中，经常要进行 PK 查询。比如根据 user id 取出 userinfo，根据 diary id 取出日志内容， 对于这样的需求，不用说，Memcached 和 NoSQL 都相当适合。在简单的多线程 “Memcached GET”基准测试中，很可能每秒进行 400,000 次 get 操作,即使 Memcached client 在不同的服务器。在一台 Nehalem box 2.5GHz x 8 核 CPU, Broadcom 四端口千兆网卡的服务器上，最新的 libMemcached 和 Memcached 每秒可达到 420,000 次 get 操作。

在 MySQL 下， 每秒可作多少次的 PK 查询呢, 我们可用 sysbench, super-smack or 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 的 QPS 大概为 100,000, 几乎只有 Memcached 的 1/4.

[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     |
...

看上去， 100, 000+ QPS 也不是太差，但为什么 MySQL 比 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                     /bnx
284550    1.4748  ha_innodb_plugin.so.0.0.0 ut_delay
67945     1.1851  mysqld                   _ZL20make_join_statisticsP4JOINP10TABLE_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

MySQL 的 SQL 解析阶段，有调用 MYSQLparse() 和 MYSQLlex(); 查询优化阶段，调用 make_join_statistics() 和 JOIN::optimize()。很明显，主要耗资源的是SQL 层，而不是 InnoDB 存储层。与 Memcached/NoSQL 比起来，MySQL 还要额外做一些工作：

  • Parsing SQL statements 解析 SQL.
  • Opening, locking tables 打开并锁定表.
  • Making SQL execution plans SQL 执行计划.
  • Unlocking, closing tables 解锁并关闭表.

另外，MySQL 还必须要做大量的并发控制，比如在发送/接收网络数据包的时候，fcntl() 就要被调用很多次; Global mutexes 比如 LOCK_open，LOCK_thread_count 也被频繁地取得/释放。所以, 在 Oprofile 的输出中，排在第二位的是 my_pthread_fastmutex_lock()。并且 %system 占用的 CPU 相当高(28%)。

其实 MySQL 开发团队和外围的开发团体已意识到大量并发控制对性能的影响，MySQL5.5 中已经解决了一些问题。未来的 MySQL 版本中，应该会越来越好。

还有一个大的问题是，%user 达到了60%。互斥量的争夺导致 %system 上增，而不是 %user，即使 MySQL 内部关于互斥量的的问题都得到修复，还是很难达到我们所期望的 300,000 QPS.也许，会有人提到使用 HANDLER ，但是因为在解析 SQL时，opening/closing table 还是必须的，所以对于提高吞吐量，它还是只能爱莫能助。

在完全内存操作的情况时, CPU的效率非常重要

如果只有一小部分数据进入内存，那么 SQL 带来的消耗可以忽略不计。很简单，因为磁盘的 I/0 操作所带来的消耗会要大，这种情况下时，就不需要太过的去考虑 SQL 所带来的消耗。

但是，在大多数的 hot MySQL 服务器中, 大部分的数据都是因为全部载入至内存中而变的只受 CPU 的限制。Profiling 的结果就类似上所述的那样: SQL 层消耗了大量的资源。假设，需要做大量的 PK 查询(i.e. SELECT x FROM t WHERE id=?)或者是做 LIMIT 的范围查询, 即使有 70-80% 都是在同一张表中做 PK 查询(仅仅只是查询条件中给定的值不同，即 value 不同而已)， MySQL 还是每次需要去做 parse/open/lock/unlock/close, 这对我们来说，是非常影响效率的。

NDBAPI

到底有没有好的方法来减少 MySQL SQL 层的 CPU 资源/争夺呢? 如果使用 MySQL Cluster， NDBAPI 不失为一个很好的解决办法。 在我还是 MySQL/Sun/Oracle 的顾问时，就见到过很多客户对SQL Node + NDB performance 感到非常不爽，但当他们用了 NDBAPI 客户端后，发现性能调提高了 N 倍。当然，在 MySQL Cluster 中是可以同时使用 NDBAPI 和 SQL 的，但在做频繁的访问模式时还是推荐使用 NDBAPI，而在 ad-hoc 或者 查询不频繁的情况下使用 SQL + MySQL + NDB。

以快捷的速度访问 API, 这正是我们需要的，但同时我们也想在 ad_hoc 或者复杂的查询的情况时还是使用 SQL. 像其他的 web service， DeNA 使用的是 InnoDB, 转为 NDB，这并不是一件容易的事情，因为内置InnoDB 即不支持 SQL 也不支持网络层的服务。

HandlerSocket —— 一个采用 NoSQL 网络协议的MySQL插件

最好的办法可以是在 MySQL 的内部，实现一以 MySQL plugin 的形式存在的 NoSQL 的网络服务。它侦听在某端口来接收采用 NoSQL 协议/API 的通讯， 然后通过 MySQL 内部的存储引擎 API 来直接访问 InnoDB。这种方法的理念类似于 NDBAPI， 但是它可以做到与 InnoDB 通讯。

这个理念最初是去年由 Kazuho Oku 在 Cybozu Labs 上提出的，他曾写过采用 Memcached protocols 通讯的MyCached UDF。而我的大学同学实现了另外一个插件 — HandlerSocket, 下面图描述了 HandlerSocket 具体做了哪些工作:

mysql_HandlerSocket.png
图1 What is Hanldersocket?

因为 HandlerSocket 是以 MySQL daemaon plugin 形式存在，所以在应用中可把 MySQL 当 NoSQL 使用. 它最大的功能是实现了与存储引擎交互，比如 InnoDB，而这不需要任何的 SQL 方面的开销. 访问 MySQL 的 table 时，当然她也是需要 open/close table 的，但是 它并不是每次都去 open/close table, 因为它会将以前访问过的 table 保存下来以供来是使用，而 opening/closing tables 是最耗资源的，而且很容易引起互斥量的争夺，这样一来，对于提高性能，非常有效。在流量变小时， HandlerSocket 会 close tables, 所以，它不会阻塞 administrative commands (DDL).

它与MySQL + Memcached 的区别在哪呢？ 对比图1 和图2 ，可从中看出其不同点。图2 展示了典型的 MySQL + Memecached 的使用. 因为 Memcached 的 get 操作比 MySQL 的内存中/磁盘上的主键查询要快很多，所以 Memcached 用于缓存数据库记录。如果 HandlerSocket 的查询速度能和 Memcached 媲美，我们就不需要使用 Memcached 来缓存记录。

mysql_memcached.png
图2 Common architecture pattern for 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;

用 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

HandlerSocket具体安装步骤请参考这里，基本步骤如下：

  • 1 下载HandlerSocket
  • 2 编译 HandlerSocket(客户端和服务端)
    • [root@localhost handlersocket]# ./autogen.sh
[root@localhost handlersocket]# ./configure --with-mysql-source=mysql-source-dir --with-mysql-bindir=mysql-server-bin-dir
[root@localhost handlersocket]# make
[root@localhost handlersocket]# make install
  • 3 安装 HandlerSocket
    将如下内容添加至 MySQL 配置文件 my.cnf

    [mysqld]
loose_handlersocket_port = 9998
  # the port number to bind to (for read requests)
loose_handlersocket_port_wr = 9999
  # the port number to bind to (for write requests)
loose_handlersocket_threads = 16
  # the number of worker threads (for read requests)
loose_handlersocket_threads_wr = 1
  # the number of worker threads (for write requests)
open_files_limit = 65535
  # to allow handlersocket accept many concurrent
  # connections, make open_files_limit as large as
  # possible.

    以 root 身份登录 MySQL

    mysql> INSTALL PLUGIN 'handlersocket' soname 'handlersocket.so';

    重启 MySQL 服务。

因为 HandlerSocket是 MySQL 插件，所以可以象使用其它插件，如 InnoDB, Q4M 和 Spider 那样使用它，即不需要修改 MySQL 源代码，MySQL 最好是 5.1 或更高版本，编译 HandlerSocket 时需要 MySQL 源码和 MySQL bin 库。

书写 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()

#!/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_id=101 条件在 user 表获取用户 user_name, user_email和 created 信息，得到的结果应该和之前在 MySQL client 查询出来的结果一样。

[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

基准测试

现在是时候展示基准测试结果，使用上面的 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, 而且 %us 也只有使用传统 SQL 时的3/4, 这说明 MySQL 的 SQL 层是非常耗资源的，如果能跳过这一层，性能肯定会大大提升。有趣的是，MySQL 使用 HandlerSocket 时的速度比使用 Memcached 也要快 178%，并且 Memcached 消耗的 %sy 资源也更多。所以虽然 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)
Network Boradcom NetXtreme II BCM5709 1000Base-T(内建四端口，使用了其中三个)

Memcached 和 HandlerSocket 都做了网络 I/O 限制，当我测试单个端口时，HandlerSocket 的 QPS 为 260000，而 Memcached 为 220000。　

HandlerSocket 的特点和优势

如下所述，HandlerSocket 有其自己的特点和优势，而其中一些对我们来说, 是真的很给力.

支持多种查询模式

HandlerSocket 目前支持 主键/唯一性查询，非唯一性索引查询，范围扫描，LIMIT 和 INSERT/UPDATE/DELETE，但还不支持未使用任何索引的操作。另外，multi_get()(类似于in(1,2,3), 只需一次网络往返)还可获取多行数据。到这里可查询详细信息。

处理大量并发连接

HandlerSocket 连接是轻量级的，因为 HandlerSocket 采用epoll()和 worker-thread/thread-pooling 架构，而 MySQL 内部线程的数量是有限的(可以由 my.cnf中的 handlersocket_threads参数控制)，所以即使建立上千万的网络连接到 HandlerSocket，它的稳定性也不会受到任何影响(消耗太多的内存，会造成巨大的互斥竞争等其他问题，如bug#26590，bug#33948，bug#49169)。

及其优秀的性能

HandlerSocket,如上所描述, 相对于其它 NoSQL 阵容,性能表现一点也不逊色。事实上，我还未曾见过哪个 NoSQL 产品在一台普通服务器上可达到 750000+ 次查询。它不仅没有调用与 SQL 相关的函数，还优化了网络/并发相关的问题。

  • 更小的网络数据包
    和传统 MySQL 协议相比，HandlerSocket 协议更简短，因此整个网络的流量更小。
  • 运行有限的 MySQL 内部线程数
    参考上面的内容。
  • 将客户端请求分组
    当大量的并发请求抵达 HandlerSocket 时，每个工作线程尽可能多地聚集请求，然后同时执行聚集起来的请求和返回结果。这样，通过牺牲一点响应时间，而大大地提高性能。例如，你可以得到以下好处，如果有人感兴趣，我会在今后的文章中对它们加以深入的解释。
    减少fsync()调用的次数.
    减少复制延迟.
无重复缓存

当使用 Memcached 缓存 MySQL/InnoDB 记录时，在 Memcached 和 InnoD B缓冲池中均缓存了这些记录，因此效率非常低(内存仍然很贵). 而采用 HandlerSocket插件, 由于它访问 InnoDB 存储引擎，记录缓存在 InnoDB 缓冲池中，这样，其它 SQL 语句就可以重复使用它。

无数据不一致的现象

由于数据只存储在一个地方(InnoDB 内)，不像使用 Memcached 时，需要在 Memcached 和 MySQL 之间检查数据一致性。

崩溃安全

后端存储是 InnoDB，它是事务性和崩溃安全的，即使有设置innodb-flush-log-at-trx-commit!=1，在服务器崩溃时也只会丢掉 < 1s 内的数据。

可从 MySQL 客户端使用 SQL

在许多情况下，人们仍然希望使用 SQL(如生产摘要报告)，这就是为什么我们不能使用嵌入式 InnoDB 的原因，大多数 NoSQL 产品都不支持 SQL 接口，HandlerSocket 仅仅是一个 MySQL 插件，可以从 MySQL 客户端发送 SQL 语句，但当需要高吞吐量时，最好使用 HandlerSocket。

从 MySQL获益

因为 HandlerSocket 运行于 MySQL 内部，因此所有 MySQL 操作，如 SQL，在线备份，复制，通过 Nagios/EnterpriseMonitor 监控等都是支持的，HandlerSocket 获得可以通过普通的 MySQL 命令监控，如SHOW GLOBAL STAUTS，SHOW ENGINE INNODB STATUS和SHOW PROCESSLIST等.

不需要修改/重建 MySQL

因为 HandlerSocket 是一个插件，所以它支持 MySQL 社区版和企业服务器版，而无需对 MySQL 做出任何修改就可以使用。

独立于存储引擎

虽然我们只测试了5.1和5.5 InnoDB 插件，但 HandlerSocket 可以和任何存储引擎交互。

注意事项和限制

需要学习HandlerSocket API

尽管它很容易使用，但仍然需要学习如何与 HandlerSocket 交互，我们提供了C++ API、Perl绑定。

没有安全功能

和其它NoSQL数据库类似，HandlerSocket不支持安全功能，HandlerSocket的工作线程以系统用户权限运行，因此应用程序可以访问通过 HandlerSocket 协议的所有表，当然，你可以象其它 NoSQL 产品一样使用防火墙过滤数据包。

对于 HDD 绑定工作负载没有优势

对于 HDD I/O 绑定工作负载，数据库每秒无法执行数千次查询，通常只有 1-10% 的 CPU 利用率，在这种情况下，SQL 执行层不会成为瓶颈，因此使用HandlerSocket没有什么优势，我们只在数据完全装载到内存的服务器上使用 HandlerSocket。

DeNA 在生产环境中使用 HandlerSocket

我们已经在生产环境中使用了 HandlerSocket 插件，效果很明显，因为我们减少了许多 Memcached 和 MySQL 从属服务器，而且整个网络流量也在减少。目前还没有发现任何性能问题(如响应时间慢，延迟等)。
我认为, NoSQL/Database 社区完全低估了 MySQL, 相对于其他产品来说，它历史悠久，而且到目前为止，我优秀的前同事们也做了许多独特的、伟大的改进。从 NDBAPI 可以看出 MySQL 有成为 NoSQL 的潜力，因为存储引擎 API 和守护进程接口的完全独立，使得 Akira 和 DeNA 开发 HandlerSocket 成为可能。作为 MySQL 一名前员工和对 MySQL 长期的了解，我想看到 MySQL 变得更好，更受欢迎，而不仅仅只作为一个 RDBMS，也应该成为 NoSQL 阵营中的一员。

Also tagged ,
2011 05 23 gettyying MySQL
NoSQL Comments (2)

MySQL 5.1 使用 PLUGIN

采用如下参数编译 MySQL 5.1 版本

[root@localhost mysql-5.1.56]# ./configure --prefix=/usr/local/mysql/ \
--without-debug \
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock \
--with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static \
--enable-assembler  \
--with-charset=utf8 \
--with-collation=utf8_general_ci \
--with-extra-charsets=latin1,gb2312 \
--with-pthread --enable-thread-safe-client   \
--with-plugins=myisam,innodb_plugin,innobase

在 client 使用命令

mysql&gt; INSERT PLUGIN plugin_name SONAME 'plugin_name.so';

提示如下错误

ERROR 1289 (HY000): The 'plugin' feature is disabled; you need MySQL built with 'HAVE_DLOPEN' to have it working

Bug #45605 是类似问题, 中间回复有一段说明产生如上错误是因为编译时有使用参数 --with-mysqld-ldflags=-all-static

- Why does the compilation process not recognize that Ubuntu default install supports HAVE_DLOPEN ?
Server was built with –with-mysqld-ldflags=-all-static

查资料时, 有说到在编译之前, 先设置如下两个环境变量就可以解决问题

# export CFLAGS="-O2 -DHAVE_DLOPEN=1"
# export CXXFLAGS="-O2 -DHAVE_DLOPEN=1"

但我测试后, 编译倒完全不能通过. 不知道有木有人采用这种方法有解决了问题的？

checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking for gcc... gcc
checking for C compiler default output file name...
configure: error: C compiler cannot create executables
See `config.log' for more details.
Also tagged
2011 04 07 gettyying MySQL Base Comments (0)

MySQL 5.1.56 使用 InnoDB

之前編譯 MySQL 5.1.54, 使用如下參數

[root@localhost mysql-5.1.54]# ./configure --prefix=/usr/local/mysql/ \
--without-debug \
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock \
--with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static \
--enable-assembler  \
--with-charset=utf8 \
--with-collation=utf8_general_ci \
--with-extra-charsets=latin1,gb2312 \
--with-pthread --enable-thread-safe-client   \
--with-plugins=myisam,innodb_plugin

編譯後, stoRe engine 可使用 InnoDB. 但這次因為測試 HandlerSocket, 到官方下載的是 MySQL 5.1.56 的源代碼, 同樣使用如上同樣的參數, 但不能使用 InnoDB, 後面做多番嘗試沒成功後, 將後面參數修改為 --with-plugins=myisam,innodb_plugin,innobase後, 再編譯安裝, OK.

Also tagged
2011 04 07 gettyying MySQL Base Comments (0)

MySQL 日志

在 MySQL 中有 4 种不同的日志, 分别为错误日志, 查询日志和慢查询日志, 二进制日志. 默认情况下, 为尽量减少 IO 损耗, 系统只打开错误日志. 若需要复制, 就必须要打开二进制日志.

错误日志

错误日志在 MySQL 数据库中很重要, 它记录着 MySQL 启动和停止, 以及服务器在运行过程中发生的任何错误的相关信息.

配置
如果配置文件 my.cnf 没有指定 log_eror, 则错问日志默认文件名为 hostname.err, 存放于 datadir 目录中. 

mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
1 row in set (0.00 sec)

将 log-error 配置到 my.cnf 文件中

[mysqld]
log_error=/usr/local/mysql/var/mysql-error.err

查询日志

查询日志记录了所有操作的语句. 由于它记录数据库所有操作, 对于访问频繁的系统, 此种日志会造成性能影响, 所以一般不会将其打开.

配置
如果配置文件 my.cnf 有打开log选项, 但未指定具体文件名和路径, 则其默认文件名为 hostname.log, 存放于 datadir 目录中.

默认时查询日志变量值

mysql> SHOW VARIABLES LIKE 'log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log           | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

如果有需要使用到查询日志, 将 log 配置到 my.cnf 文件中

[mysqld]
log=/usr/local/mysql/var/mysql_query.log

查询日志是纯文本格式, 可使用文本读取工具直接打开查看.

慢查询日志

慢查询日志是记录执行时间超过参数 slow_launch_time(unit: s, v5.1 默认 2s v5.0.* 是 long_query_time)所设定值的 SQL 语句日志. 它有助于发现性能有问题的 SQL.

打开慢日志对系统性能的整体影响没有 binlog 那么大, 但系统需要计算每一条查询的执行时间, 所有, 在 CPU 方面还是有损耗的. 如果 CPU 资源不够, 可在大部分时候关闭这个, 只需间断性的打开来定位可能存在的慢查询.

配置
如果配置文件 my.cnf 有打开log_slow_queries选项, 但未指定具体文件名和路径, 则其默认文件名为 hostname-slow.log, 存放于 datadir 目录中.

默认情况下慢日志查询变量(v5.1)：

mysql> SHOW VARIABLES LIKE '%slow%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| log_slow_queries    | OFF                              |
| slow_launch_time    | 2                                |
| slow_query_log      | OFF                              |
| slow_query_log_file | /usr/local/mysql/var/db-slow.log |
+---------------------+----------------------------------+
4 rows in set (0.00 sec)

如果有需要使用到慢查询日志, 将 log_slow_queries 配置到 my.cnf 文件中

[mysqld]
log_slow_queries=/usr/local/mysql/var/mysql_slow_query.log

也可以在启动 MySQL 服务时, 加上 --log_slow_queries=filename.log

若需要进一步缩短慢查询时间限制, 可使用 Percona 提供的 microslow-patch(msl Patch). 它不仅能将时间减小到毫秒级别, 还能通过一些特定的规则来过滤记录的SQL, 如只记录某个表的慢查询.

查看慢日志

二进制日志

二进制日志记录了所有的 DDL 和 DML 的语句, 但不包括查询语句, 语句以事件方式保存, 此日志对发生灾难时数据恢复极为重要. MySQL 复制时, 必须将其打开.

启用配置

[mysqld]
log-bin=/usr/local/mysql/var/mysql-bin

mysql-bin 为日志文件名，MySQL 在文件名后添加数字索引，所以该文件最后的形式类似于 mysql-bin.000001.如果在指定文件名时类似于 myql-bin.log, .log 会自动忽略. 该日志没有指定相对路径时，默认存放于 datadir 目录中.

如下情况时，二进制日志会更换到新的文件:

  • 服务器重启
  • 服务器被更新
  • 日志达到最大日志长度 max_binlog_size
  • 在 MySQL 命令终端 FLUSH LOGS

打开配置时各变量值(v5.1)

mysql> SHOW VARIABLES LIKE '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)

部分变量值说明

  • binlog_cache_size 事务过程中容纳二进制日志 SQL 语句的缓存大小. 二进制日志缓存是服务器支持事务存储引擎, 且服务器启用了二进制日志(log_bin)的前提下为每个客户端分配的内存, 注意是给每个客户端可以分配设置大小的 binlog cache 空间. 如果系统中会出现多语句的事务, 增加该值的大小, 以得到更好的性能.
  • max_binlog_cache_size 和binlog_cache_size对应, 它代表的是 binlog 能使用的最大 cache 值大小. 当不够大时, 系统可能会报“Multi_statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”.
  • max_binlog_size binlog 最大值, 一般设置为 512KB 或 1GB, 但不能超过 1GB.
  • sync_binlog影响到 binlog 对 MySQL 所带来的性能消耗, 还影响到数据的完整性, 参数说明如下:
    sync_binlog=0 事务提交后, 仅仅是将 binlog_cache 中的数据写入到 binlog 文件中, 但不执行 fsync 之类的磁盘操作指令通知文件系统将缓存刷新到磁盘, 而让文件系统自行决定什么时候来同步. sync_binlog=N 进行 N 次事务提交后, 系统将执行一次 fsync 之类的磁盘同步指令, 通知文件系统将 binlog 文件的缓存刷新到磁盘. 默认是 sync_binlog=0, 性能是最好, 但是风险是最大, 因为一旦系统 crash, 文件系统缓存中的 binlog 将都会丢失.设置为 1 时, 最安全但性能损耗最大, 当系统 crash 时, 最多只会丢失 binlog_cache 中未完成的一个事务, 对实际数据没有任何的影响.sync_binlog=0 性能 有可能是 sync_binlog=1 时的5倍.

记录内容配置
binlog_do_db=example1,example2 设定那些 db 需要记录binlog
binlog_ignore_db=test1,test2 设定那些 db不要记录 binlog

注:
如果在操作数据库之前，不使用use $dbname , 那么所有的SQL都不会记录, 如果使用了use $dbname,那么判断规则取决于这里的$dbname，而不是 SQL 中操作的库.

如果配置文件中没有配置这两个选项，则该主机上所有库的 DML 和 DLL 语句都会被记录。
删除查看二进制日志

Also tagged
2011 03 24 gettyying MySQL Base Comments (0)

Optimize InnoDB Table

新上的服务器, 优化表失败.

上官方网站看了下 v5.1 优化表一章, 里面正好有提到遇到的问题:

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. Beginning with MySQL 5.1.27, this is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

It is because the table that you are using is InnoDB.

You can optimize the InnoDB tables by using this.

ALTER TABLE table.name ENGINE='InnoDB';

You can make OPTIMIZE TABLE work on other storage engines by starting mysqld with the --skip-new or --safe-mode option. In this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.

注:

[root@localhost ~]# /usr/local/mysql/libexec/mysqld --verbose --help

结果中有对 --skip-new 和 --safe-mode选项的描述

  • --skip-new Don’t use new, possible wrong routines.
  • --safe-mode Skip some optimize stages (for testing).
Also tagged
2011 03 23 gettyying MySQL 调优 Comments (0)

减低 InnoDB 停止时时间

FROM: How to decrease InnoDB shutdown time

MySQL 命令终端, 执行如下命令

mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

然后在 SHELL 终端执行如下命令

[root@localhost ~]# $ mysqladmin ext -i10 | grep dirty

该命令执行后, 终端会输出如下类似的信息

| Innodb_buffer_pool_pages_dirty    | 1823        |
| Innodb_buffer_pool_pages_dirty    | 1512        |
| Innodb_buffer_pool_pages_dirty    | 1311        |
......

直到数字列减小到接近于 0时, (如果服务处于 actvie 状态, 该数字只能接近于 0 , 而不可能等于 0), 执行 MySQL 服务停止命令.

Also tagged
2011 03 16 gettyying MySQL Base Comments (1)