【IT168 技术】MySQL Test Framework是每个MySQL内核开发人员必须熟悉的测试框架。然而，对于某些bug, MySQL Test脚本难以重现，而RQG(Random Query Generator)则是非常强悍的一个工具，可以很好的弥补此时MySQL Test的某些不足之处。
RQG的原理就是基于用户自定的词法生成对应的语句并执行。剩下的，只要你来写.yy文件。
1. 安装
$ bzr branch lp:randgen
/ 6317KB 166KB/s | Fetching revisions:Inserting stream
Branched 765 revision(s).
/ 6317KB 166KB/s | Fetching revisions:Inserting stream
Branched 765 revision(s).
集团的MySQL机器PERL安装比较全，在此免除安装步骤。其它几个PERL的安装，参考wiki。
测试安装是否正确：
perl gentest.pl \
–dsn=dbi:mysql:host=127.0.0.1:port=3300:user=root:database=test \ –gendata=conf/examples/example.zz \
–grammar=conf/examples/example.yy
# 2012-09-04T22:33:18 Starting: gentest.pl –dsn=dbi:mysql:host=127.0.0.1:port=3300:user=root:database=test –gendata=conf/examples/example.zz –grammar=conf/examples/example.yy
# 2012-09-04T22:33:18 ——————————-
…
# 2012-09-04T22:33:23 Kill GenTest::ErrorFilter(31632)
# 2012-09-04T22:33:23 Test completed successfully.
–dsn=dbi:mysql:host=127.0.0.1:port=3300:user=root:database=test \ –gendata=conf/examples/example.zz \
–grammar=conf/examples/example.yy
# 2012-09-04T22:33:18 Starting: gentest.pl –dsn=dbi:mysql:host=127.0.0.1:port=3300:user=root:database=test –gendata=conf/examples/example.zz –grammar=conf/examples/example.yy
# 2012-09-04T22:33:18 ——————————-
…
# 2012-09-04T22:33:23 Kill GenTest::ErrorFilter(31632)
# 2012-09-04T22:33:23 Test completed successfully.
2. 实战一个典型的case
如果我们发现线上某台MySQL Crash可能与并发的show variables 和 alter table分区表相关，这时一个典型的mysql test如下：
–source include/have_partition.inc
–source include/have_innodb.inc
CREATE TABLE IF NOT EXISTS t1 (`a` INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3),(4);
–connect (con1,localhost,root,,)
–let $run = 100
while ($run)
{
–connection default
–send ALTER TABLE t1 PARTITION BY HASH (`a`) PARTITIONS 4
–connection con1
–let $show = 300
while ($show)
{
SHOW ENGINE INNODB STATUS;
–dec $show
}
–connection default
–reap
–dec $run
}
DROP TABLE t1;
–source include/have_innodb.inc
CREATE TABLE IF NOT EXISTS t1 (`a` INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3),(4);
–connect (con1,localhost,root,,)
–let $run = 100
while ($run)
{
–connection default
–send ALTER TABLE t1 PARTITION BY HASH (`a`) PARTITIONS 4
–connection con1
–let $show = 300
while ($show)
{
SHOW ENGINE INNODB STATUS;
–dec $show
}
–connection default
–reap
–dec $run
}
DROP TABLE t1;
但这个并发性不强，是个模拟的并发线程。此时RQG则可以很好地弥补mysql test的不便。
$cat 1.yy
query_init:
CREATE TABLE IF NOT EXISTS t1 (`a` INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3),(4);
thread1:
SHOW ENGINE INNODB STATUS;
query:
ALTER TABLE t1 PARTITION BY HASH (`a`) PARTITIONS 4 ;
CREATE TABLE IF NOT EXISTS t1 (`a` INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3),(4);
thread1:
SHOW ENGINE INNODB STATUS;
query:
ALTER TABLE t1 PARTITION BY HASH (`a`) PARTITIONS 4 ;
$cat 1.sh
perl ./runall.pl \
–threads=2 \
–queries=100M \
–duration=300 \
–skip-gendata \
–grammar=1.yy \
–basedir=/u01/mysqld2/
–threads=2 \
–queries=100M \
–duration=300 \
–skip-gendata \
–grammar=1.yy \
–basedir=/u01/mysqld2/
$sh 1.sh
# 2012-09-04T22:38:48 Copyright (c) 2008,2011 Oracle and/or its affiliates. All rights reserved. Use is subject to license terms.
# 2012-09-04T22:44:42 Test completed successfully.
# 2012-09-04T22:44:42 gentest.pl exited with exit status STATUS_OK (0)
2012-09-04T22:44:42 [32354] ./runall.pl will exit with exit status STATUS_OK (0)
# 2012-09-04T22:44:42 Test completed successfully.
# 2012-09-04T22:44:42 gentest.pl exited with exit status STATUS_OK (0)
2012-09-04T22:44:42 [32354] ./runall.pl will exit with exit status STATUS_OK (0)
可以查看对应的mysqld日志：
# 2012-09-04T22:38:48 Copyright (c) 2008,2011 Oracle and/or its affiliates. All rights reserved. Use is subject to license terms.
# 2012-09-04T22:44:42 Test completed successfully.
# 2012-09-04T22:44:42 gentest.pl exited with exit status STATUS_OK (0)
2012-09-04T22:44:42 [32354] ./runall.pl will exit with exit status STATUS_OK (0)
# 2012-09-04T22:44:42 Test completed successfully.
# 2012-09-04T22:44:42 gentest.pl exited with exit status STATUS_OK (0)
2012-09-04T22:44:42 [32354] ./runall.pl will exit with exit status STATUS_OK (0)
当然，你也可以attach GDB到启动的MySQLD上来尽情地玩。
