mysql 慢日志查询模块的测试[python]

rds 提供给用户可以查询到慢查询语句的日志的功能，为此我要开展相应的测试，以下是测试的一些记录

前置条件，需要构造一些可以进行慢日志查询的元数据

*************************** 1. row ***************************         id: 1        age: 120       name: uCTOGsiaYDVeHZCkamzqWFoSVxuPNCWZFXNRrrytcDVVnLKEfjtFeaTwwxcAyVBvDNIMYDCxAkizAYQFETEulJmZoPeTJsABKnJLspDvYNbLxsiBYiYYzjzcgmt_created: 2012-09-11 14:23:21        msg: uCTOGsiaYDVeHZCkamzqWFoSVxuPNCWZFXNRrrytcDVVnLKEfjtFeaTwwxcAyVBvDNIMYDCxAkizAYQFETEulJmZoPeTJsABKnJLspDvYNbLxsiBYiYYzjzcuCTOGsiaYDVeHZCkamzqWFoSVxuPNCWZFXNRrrytcDVVnLKEfjtFeaTwwxcAyVBvDNIMYDCxAkizAYQFETEulJmZoPeTJsABKnJLspDvYNbLxsiBYiYYzjzcuCTOGsiaYDVeHZCkamzqWFoSVxuPNCWZFXNRrrytcDVVnLKEfjtFeaTwwxcAyVBvDNIMYDCxAkizAYQFETEulJmZoPeTJsABKnJLspDvYNbLxsiBYiYYzjzc

类似如上数据20万条。

构造代码片段如下：

1.首先要创建一张表

122 
123     sql = 'CREATE TABLE if not exists t1(id int unsigned primary key auto_increment not null , age tinyint unsigned , name VARCHAR(128) , gmt_created dat    etime NOT NULL , msg text)'
try:
125         cursor.execute(sql)
126     except Exception, e:
127         print ("excute %s error,"%sql, e)

2.通过多线程插入数据，插入数据的时候age是一定范围内的随机数，msg,name为一定规则的随机字符串

67 def insert( cursor , svr , ibcx ) :
 68     commit_num = 500
 69     print bcolors.OKGREEN + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + ' ' + svr + ' sarting insert into for 50000 --> %d --> 500' % ( ibc    x ) + bcolors.ENDC
 70     isql = "INSERT INTO t1( age , name , gmt_created , msg ) VALUES"
 71     cursor.execute('BEGIN')
 72     for v in xrange( commit_num ) :
 73         age = random.randint( 1 , 128 )
 74         #import pdb
 75         #pdb.set_trace()
 76         rndstr = randstr( age )
 77         #print '*************',rndstr
 78         sql = '( ' + str( age ) + ' , \'' + rndstr + '\''+ ',\''+time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + '\' , \'' + rndstr*3 +'\')'
 79         if v == 0 :
 80             isql = isql + sql
 81         else :
 82             isql = isql + ' , ' + sql
 83     cursor.execute( isql )
 84     cursor.execute( 'COMMIT' )

 85 
 86 def init( cursor , svr ) :
 87     timestamp = time.time()
 88     pool_num = 50000
 89     ibcx = 0
 90     for x in xrange( 100 ) :
 91         ibcx += 500
 92         insert(  cursor , svr , ibcx )
 93         print bcolors.WARNING + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + ' ' + svr + ' Init_data successful using time %d seconds' % ( i    nt( time.time() ) - int( timestamp ) ) + bcolors.ENDC

调用插入数据的多线程

if sys.argv[1] in ( 'init' , 'o' ) :
131         print bcolors.OKBLUE + time.strftime( '%Y-%m-%d %H:%M:%S' , time.localtime() ) + ' Start initization table Data' + bcolors.ENDC
132         threading.Thread( target = init , args = ( cursor , conn_addr ) ).start()

3. 亮点：进行复杂 的可以产生慢日志的查询，主要sql如下

2012-09-11 16:04:53All Threding exit
2012-09-11 16:04:53 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:04:57 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:04:58 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:00 SELECT count(*) from t1
2012-09-11 16:05:00 SELECT DISTINCT substring(10,50) FROM t1
2012-09-11 16:05:00 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:05:02 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:05:02 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:05 SELECT count(*) from t1
2012-09-11 16:05:05 SELECT DISTINCT substring(10,50) FROM t1
2012-09-11 16:05:05 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:05:10 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:05:10 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:12 SELECT count(*) from t1
2012-09-11 16:05:12 SELECT DISTINCT substring(10,50) FROM t1
2012-09-11 16:05:12 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:05:18 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:05:19 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:21 SELECT count(*) from t1
2012-09-11 16:05:21 SELECT DISTINCT substring(10,50) FROM t1
2012-09-11 16:05:21 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:05:25 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:05:25 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:27 SELECT count(*) from t1
2012-09-11 16:05:27 SELECT DISTINCT substring(10,50) FROM t1
2012-09-11 16:05:27 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:05:31 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:05:31 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:34 SELECT count(*) from t1
2012-09-11 16:05:34 SELECT DISTINCT substring(10,50) FROM t1
2012-09-11 16:05:34 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:05:35 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:05:35 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:37 SELECT count(*) from t1
2012-09-11 16:05:37 SELECT DISTINCT substring(10,50) FROM t1
2012-09-11 16:05:38 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:05:43 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:05:43 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:45 SELECT count(*) from t1
2012-09-11 16:05:45 SELECT DISTINCT substring(10,50) FROM t1
2012-09-11 16:05:45 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:05:49 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:05:50 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:52 SELECT count(*) from t1
2012-09-11 16:05:52 SELECT DISTINCT substring(10,50) FROM t1
2012-09-11 16:05:52 SELECT count(distinct name) msg from t1 where age between 1 and 20000 union all SELECT count(distinct name) msg from t1 where age between 5 and 20000
2012-09-11 16:05:56 SELECT name,msg,age from t1 where substring(msg,30,50) = substring(name,2,22) order by gmt_created desc
2012-09-11 16:05:56 SELECT age from t1 group by right(name,50)
2012-09-11 16:05:58 SELECT count(*) from t1

4.进行实际慢日志查询和采集给用户的慢日志进行对比判断是否正确

use mysql

select * from slow_log;

实际结果从rds元数据查找两者进行对比测试