最近用 RandomQueryGenerator 来生成一定规律的随机 SQL 进行测试。本文主要记录下对 RandomQueryGenerator 的学习。
首先, 简介:
RandomQueryGenerator 是 MySQL 使用的 QA 测试工具,它是一个开源的 PERL 程序。它可以按照一定的规则自动生成表以及数据,根据指定的语法文件随机生成 SQL 并以一定的并发压力发给指定的一个或几个 MySQL 执行,最后比较各个 MySQL 返回的结果集,如果不一致的话会报错。
资料获取:
https://github.com/RQG/RQG-Documentation/wiki/Category:RandomQueryGenerator
执行:
原理:
RandomQueryGenerator 按照指定的zz文件在每个MySQL上生成表并插入数据,生成表和插入数据的代码在
lib/GenTest/App/Gendata.pm.
RandomQueryGenerator 会根据用户命令启动1个或多个工作线程,每个工作线程按照指定的语法描述文件(yy文件),生成指定数目的sql语句。每生成一条sql语句,将它按顺序发给后端的 MySQL 实例,并比较每一个实例返回的执行结果。具体代码入口: lib/GenTest/App/GenTest.pm (sub workerProcess 函数)。
注意:
如果 threads > 1的话并且有2个或以上的 dsn时, RandomQueryGenerator 可能会出现结果集不匹配,即同一条语句在不同的 MySQL 上执行结果不一样(因为有并发)。
数据与语法文件:
总的来说写法非常的灵活和随意,不像 lex 和bison 那样要求唯一性。因为它本身就是要随机生成 sql 语句。
如果你想增大一条规则的出现概率,那么就简单的多重复几次就好了,具体的可以在下面的示例中看到。
示例数据生成文件:
-----------------------
$tables = {
rows => [10, 100, 1000],
partitions => [ undef , 'KEY (pk) PARTITIONS 2' ],
engines => [ 'MyISAM', 'InnoDB' ]
};
$fields = {
types => [ 'int', 'bigint', 'varchar(5)', 'varchar(100)', 'double(20,4)' ],
indexes => [undef, 'key' ],
null => [undef, 'not null'],
default => [undef, 'default null'],
sign => [undef, 'unsigned'],
charsets => ['utf8']
};
$data = {
numbers => [ 'digit', 'double(20,4)', 'null', undef ],
strings => [ 'letter', 'english', 'varchar(5)', 'varchar(100)' ],
}
-----------------------
实例 语法文件:
------------------------
query:
show | select|select|select|select|select|select
# | transaction | update | delete
| set_var;
#
# define show stmt
#
show:
SHOW FULL TABLES WHERE Table_type != 'VIEW'
|
SHOW TABLE STATUS
|
SHOW COLUMNS FROM _table
|
SHOW VARIABLES LIKE 'lower_case_%'
|
SHOW DATABASES
|
SHOW CREATE TABLE _table
|
SHOW INDEX FROM _table;
#
# define set var stmt
#
char_set_values:
BINARY
|
UTF8;
set_var:
SET @@character_set_database = char_set_values
|
SET NAMES utf8
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
#
# define select
#
select:
simple_select
|
SELECT COUNT(1) FROM (simple_select) T1
|
SELECT * FROM (simple_select) T1 opt_groupby opt_orderby opt_limit
|
select_for_union;
union_or_all:
UNION
|
UNION ALL;
simple_select:
select_from_big | select_from_big | select_from_normal | select_from_normal| select_from_global
| select_big_join_global | select_big_join_global | select_normal_join_global | select_normal_join_global;
select_big_join_global:
SELECT field_list_join FROM big_table T1 LEFT OUTER JOIN global_table_or_subselect T2 on_expr where_and_opts_join;
select_normal_join_global:
SELECT field_list_join FROM normal_table T1 LEFT OUTER JOIN global_table_or_subselect T2 on_expr where_and_opts_join;
global_table_or_subselect:
global_table | global_table | global_table | (select_from_global);
select_from_global:
SELECT opt_distinct field_list FROM global_table T1 where_and_opts;
select_from_normal:
SELECT opt_distinct field_list From normal_table T1 where_and_opts;
select_from_big:
SELECT opt_distinct field_list From big_table T1 where_and_opts;
select_for_union:
(SELECT * from_one_table) union_or_all (SELECT * from_one_table)
|
(SELECT * from_join_table) union_or_all (SELECT * from_join_table);
from_one_table:
From _table T1 where_and_opts;
from_join_table:
From distribution_table T1 LEFT OUTER JOIN global_table_or_subselect T2 on_expr where_and_opts_join;
where_and_opts:
WHERE condition_list opt_groupby opt_orderby opt_limit;
where_and_opts_join:
WHERE condition_list_join opt_groupby_join opt_orderby_join opt_limit;
on_expr:
ON T1.int_field_name = T2.int_field_name
|
ON T1.char_field_name = T2.char_field_name;
#
# define field list
#
opt_alias:
|
AS _quid;
opt_distinct:
| | | | | | | | | | | DISTINCT;
field_list:
field_list , T1._field
| field_list , T1._field
| field_list , T1._field
|
field_list , func_or_aggr
|
T1._field
|
func_or_aggr;
field_list_join:
field_list_join , field_join
|
field_join;
field_join:
T1._field opt_alias | T2._field opt_alias
| T1._field opt_alias | T2._field opt_alias
| func_or_aggr_join opt_alias;
func_or_aggr:
MIN(T1.int_field_name) | MAX(T1.int_field_name) | CONCAT(T1.char_field_name,T1.char_field_name) | AVG(T1.int_field_name)
| COUNT(T1._field) | IF(T1.int_field_name=_digit, (T1.int_field_name + T1.int_field_name)/1024/1024,0)
|
IF(T1._field[invariant] IS NULL, IF(T1.int_field_name=_digit, (T1.int_field_name + T1.int_field_name)/1024/1024,0), T1._field[invariant]);
func_or_aggr_join:
MIN(T1.int_field_name) | MAX(T2.int_field_name) | CONCAT(T1.char_field_name,T2.char_field_name)
| AVG(T1.int_field_name) | COUNT(T2.int_field_name)
| IF(T1.int_field_name=_digit, (T1.int_field_name+T2.int_field_name)/1024/1024,0);
#
# define limit, order by, group by
#
opt_limit:
| | | LIMIT _digit;
opt_desc:
| | | DESC;
field_group: T1._field opt_desc;
field_list_group:
field_list_group , field_group
|
field_group;
opt_orderby:
| |
ORDER BY field_list_group;
opt_groupby:
| |
GROUP BY field_list_group;
field_group_join: T1._field opt_desc | T2._field opt_desc;
field_list_group_join:
field_list_group_join , field_group_join
|
field_group_join;
opt_orderby_join:
|
ORDER BY field_list_group_join;
opt_groupby_join:
|
GROUP BY field_list_group_join;
#
# define the condition
#
in_digit_list:
in_digit_list
|
_digit;
condition_list:
condition_list and_or condition | condition;
and_or: AND | AND| AND| AND| OR;
condition:
T1.int_field_name < digit | T1.int_field_name = _digit
| T1.int_field_name = _digit | T1.int_field_name = _digit | T1.int_field_name = _digit | T1.int_field_name = _digit
| T1.char_field_name = char_value | T1.char_field_name = char_value
| T1.int_field_name IN (in_digit_list);
condition_list_join:
condition_list_join and_or condition_join | condition_join;
condition_join:
T1.int_field_name < _digit | T2.int_field_name < _digit | T1.int_field_name = _digit | T2.int_field_name = _digit
| T1.int_field_name = _digit | T2.int_field_name = _digit
| T1.int_field_name = _digit | T2.int_field_name = _digit
| T1.char_field_name = char_value | T2.char_field_name = char_value
| T1.char_field_name = char_value | T2.char_field_name = char_value
| T1.int_field_name IN (in_digit_list);
#
# define table, field, and value
#
_table:
big_table | normal_table | global_table;
distribution_table:
big_table | normal_table;
big_table:
table1000_innodb_key_pk_parts_2_int_autoinc | table1000_innodb_int_autoinc;
normal_table:
table100_innodb_key_pk_parts_2_int_autoinc | table100_innodb_int_autoinc;
global_table:
table10_innodb_int_autoinc | table10_innodb_key_pk_parts_2_int_autoinc;
_digit:
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | _tinyint_unsigned ;
char_value:
_char(2)| _char(2) | _char(2)| _char(3) | _char(4);
int_field_name:
`pk` | `col_int_key` | `col_int` |
`col_bigint` | `col_bigint_key` |
`col_int_not_null` | `col_int_not_null_key` ;
char_field_name:
`col_char_10` | `col_char_10_key` | `col_text_not_null` | `col_text_not_null_key` |
`col_text_key` | `col_text` | `col_char_1int_field_name:
`pk` | `col_int_key` | `col_int` |
`col_bigint` | `col_bigint_key` |
`col_int_not_null` | `col_int_not_null_key` ;
char_field_name:
`col_char_10` | `col_char_10_key` | `col_text_not_null` | `col_text_not_null_key` |
`col_text_key` | `col_text` | `col_char_10_not_null_key` | `col_char_10_not_null` |
`col_char_1024` | `col_char_1024_key` | `col_char_1024_not_null` | `col_char_1024_not_null_key` ;0_not_null_key` | `col_char_10_not_null` |
`col_char_1024` | `col_char_1024_key` | `col_char_1024_not_null` | `col_char_1024_not_null_key` ;
int_field_name:
`pk` | `col_int_key` | `col_int` |
`col_bigint` | `col_bigint_key` |
`col_int_not_null` | `col_int_not_null_key` ;
char_field_name:
`col_char_10` | `col_char_10_key` | `col_text_not_null` | `col_text_not_null_key` |
`col_text_key` | `col_text` | `col_char_10_not_null_key` | `col_char_10_not_null` |
`col_char_1024` | `col_char_1024_key` | `col_char_1024_not_null` | `col_char_1024_not_null_key` ;
--------------------------------------------------
转载请注明转自高孝鑫的博客
首先, 简介:
RandomQueryGenerator 是 MySQL 使用的 QA 测试工具,它是一个开源的 PERL 程序。它可以按照一定的规则自动生成表以及数据,根据指定的语法文件随机生成 SQL 并以一定的并发压力发给指定的一个或几个 MySQL 执行,最后比较各个 MySQL 返回的结果集,如果不一致的话会报错。
资料获取:
https://github.com/RQG/RQG-Documentation/wiki/Category:RandomQueryGenerator
执行:
perl gentest.pl--gendata=conf/example.zz --grammar=conf/example.yy --dsn=dbi:mysql:host=127.0.0.1:port=9306:user=root:database=test
--dsn=dbi:mysql:host=127.0.0.1:port=9309:user=root:database=test
--queries=1000 --threads=10
原理:
RandomQueryGenerator 按照指定的zz文件在每个MySQL上生成表并插入数据,生成表和插入数据的代码在
lib/GenTest/App/Gendata.pm.
RandomQueryGenerator 会根据用户命令启动1个或多个工作线程,每个工作线程按照指定的语法描述文件(yy文件),生成指定数目的sql语句。每生成一条sql语句,将它按顺序发给后端的 MySQL 实例,并比较每一个实例返回的执行结果。具体代码入口: lib/GenTest/App/GenTest.pm (sub workerProcess 函数)。
注意:
如果 threads > 1的话并且有2个或以上的 dsn时, RandomQueryGenerator 可能会出现结果集不匹配,即同一条语句在不同的 MySQL 上执行结果不一样(因为有并发)。
数据与语法文件:
总的来说写法非常的灵活和随意,不像 lex 和bison 那样要求唯一性。因为它本身就是要随机生成 sql 语句。
如果你想增大一条规则的出现概率,那么就简单的多重复几次就好了,具体的可以在下面的示例中看到。
示例数据生成文件:
-----------------------
$tables = {
};
$fields = {
};
$data = {
}
-----------------------
实例 语法文件:
------------------------
query:
# | transaction | update | delete
#
# define show stmt
#
show:
#
# define set var stmt
#
char_set_values:
set_var:
#
# define select
#
select:
union_or_all:
simple_select:
select_big_join_global:
select_normal_join_global:
global_table_or_subselect:
select_from_global:
select_from_normal:
select_from_big:
select_for_union:
from_one_table:
from_join_table:
where_and_opts:
where_and_opts_join:
on_expr:
#
# define field list
#
opt_alias:
opt_distinct:
field_list:
field_list_join:
field_join:
func_or_aggr:
func_or_aggr_join:
#
# define limit, order by, group by
#
opt_limit:
opt_desc:
field_group: T1._field opt_desc;
field_list_group:
opt_orderby:
opt_groupby:
field_group_join: T1._field opt_desc | T2._field opt_desc;
field_list_group_join:
opt_orderby_join:
opt_groupby_join:
#
# define the condition
#
in_digit_list:
condition_list:
and_or: AND | AND| AND| AND| OR;
condition:
condition_list_join:
condition_join:
#
# define table, field, and value
#
_table:
distribution_table:
big_table:
normal_table:
global_table:
_digit:
char_value:
int_field_name:
char_field_name:
char_field_name:
int_field_name:
char_field_name:
--------------------------------------------------
转载请注明转自高孝鑫的博客