2>、user表基础行数据84945473,每次插入5000条数据,重复2次
MyISAM:
[root@localhost ~]# date;
mysqlslap -uroot -p123 --concurrency=1 --iterations=2
--create-schema=zhigoo --query=/data/user-e.sql
;date
Sun Feb 5
12:55:45 CST 2012
Benchmark
Average number of seconds to run all
queries:
19.058
seconds
Minimum number of seconds to run all
queries:
10.563
seconds
Maximum number of seconds to run all
queries:
27.554
seconds
Number of clients running
queries:
1
Average number of queries per
client:
5000
Sun Feb 5
12:56:23 CST 2012
[root@localhost
~]#
TotalRunTime:38秒
InfiniDB:
[root@localhost ~]# date;
mysqlslap -uroot -p123 --concurrency=1 --iterations=2
--create-schema=gelsey --query=/data/user1-e.sql
;date
Sun Feb 5
11:48:49
CST 2012
Benchmark
Average number of seconds to run all
queries:
1873.844
seconds
Minimum number of seconds to run all
queries:
1520.263
seconds
Maximum number of seconds to run all
queries:
2227.426
seconds
Number of clients running
queries:
1
Average number of queries per
client:
5000
Sun Feb 5
12:51:17 CST 2012
[root@localhost
~]#
TotalRunTime:
1小时3分28秒
InfiniDB批量插入时间:
[root@localhost ~]# date;
mysqlslap -uroot -p123 --concurrency=1 --iterations=2
--create-schema=gelsey --query=/data/user1.sql
;date
Sun Feb 5
13:04:49 CST 2012
Benchmark
Average number of seconds to run all
queries:
1.278 seconds
Minimum number of seconds to run all
queries:
1.255 seconds
Maximum number of seconds to run all
queries:
1.301 seconds
Number of clients running
queries:
1
Average number of queries per
client:
1
Sun
Feb 5 13:04:52 CST 2012
[root@localhost
~]#
TotalRunTime:3秒
MyISAM与InfiniDB逐条插入时间对比,图2:
TBNAME
ROWS
ENGINES
ITERATIONS
AVG
MIN
MAX
TOTALTIME
gems_log
52827839
MyISAM
3
9.771
1.858
24.092
30s
gems_log
52827839
InfiniDB
3
994.609
610.196
1318.352
49min45s
user
84945473
MyISAM
2
19.058
10.563
27.554
38s
user
84945473
InfiniDB
2
1873.844
1520.263
2227.426
1h3min28s
(图2)
InfiniDB逐条插入与批量插入时间对比,图3:
TBNAME
ROWS
ENGINES
ITERATIONS
AVG
MIN
MAX
TOTALTIME
gems_log
52827839
InfiniDB
3(逐条)
994.609
610.196
1318.352
49min45s
gems_log
52827839
InfiniDB
3(批量)
0.558
0.419
0.698
< 1s
user
84945473
InfiniDB
2(逐条)
1873.844
1520.263
2227.426
1h3min28s
user
84945473
InfiniDB
2(批量)
1.278
1.255
1.301
3s
(图3)
3.2 UPDATE语句
1、tmp_user表基础数据6617109条
测试update语句如下:
update tmp_user set
dateid=(dateid-interval 7 day) where
(mod(userid,12)+3)>7;
update语句影响记录数为:
mysql>
select count(*) from tmp_user where
(mod(userid,12)+3)>7;
+----------+
| count(*) |
+----------+
| 3859136 |
+----------+
1 row in set (2.15 sec)
执行结果:
MyISAM:
[root@localhost data]#
date;mysqlslap -uroot -p123 --concurrency=1 --iterations=1
--create-schema=zhigoo --query=/data/update_tmp_user.sql
;date
Tue Feb 7 11:29:28
CST 2012
Benchmark
Average number of seconds to
run all queries:
196.321
seconds
Minimum number of seconds to
run all queries:
196.321
seconds
Maximum number of seconds to
run all queries:
196.321
seconds
Number of clients running
queries:
1
Average number of queries per
client:
1
Tue Feb 7
11:32:44
CST 2012
[root@localhost data]#
TotalRunTime:3分16秒
InfiniDB:
[root@localhost data]#
date;mysqlslap -uroot -p123 --concurrency=1 --iterations=1
--create-schema=gelsey --query=/data/update_tmp_user.sql
;date
Tue Feb 7 11:26:43
CST 2012
Benchmark
Average number of seconds to
run all queries:
49.081
seconds
Minimum number of seconds to
run all queries:
49.081
seconds
Maximum number of seconds to
run all queries:
49.081
seconds
Number of clients running
queries:
1
Average number of queries per
client:
1
Tue Feb 7 11:27:32
CST 2012
[root@localhost data]#
TotalRunTime:49s
2、user表基础行数据109794560,tmp_user表基础行数据6617109条
update语句:
UPDATE user a , tmp_user b SET
a.level=b.level,a.moneybalance=b.money,a.greenpoint=b.greenpoint
WHERE a.userid=b.userid AND b.dateid='2012-01-29';
update语句影响记录数为:
mysql> select count(*) from user1 a
, tmp_user b WHERE a.userid=b.userid AND
b.dateid='2012-01-01';
+----------+
| count(*) |
+----------+
| 8840096 |
+----------+
1 row in set (2.79 sec)
执行结果:
MyISAM:
[root@localhost
data]# date;mysqlslap -uroot -p123
--concurrency=1 --iterations=1 --create-schema=zhigoo
--query=/data/update_user.sql ;date
Wed Feb 8 13:53:09
CST 2012
Benchmark
Average number of seconds to
run all queries:
980.429
seconds
Minimum number of seconds to
run all queries:
980.429
seconds
Maximum number of seconds to
run all queries:
980.429
seconds
Number of clients running
queries:
1
Average number of queries per
client:
1
Wed Feb 8
14:09:29
CST 2012
[root@localhost data]#
TotalRunTime:
16分20秒
InfiniDB:
[root@localhost
data]# date;mysqlslap -uroot -p123
--concurrency=1 --iterations=1 --create-schema=gelsey
--query=/data/update_user.sql ;date
Wed Feb 8 13:48:18
CST 2012
Benchmark
Average number of seconds to
run all queries:
127.069
seconds
Minimum number of seconds to
run all queries:
127.069
seconds
Maximum number of seconds to
run all queries:
127.069
seconds
Number of clients running
queries:
1
Average number of queries per
client:
1
Wed Feb 8
13:50:25
CST 2012
[root@localhost data]#
TotalRunTime:2分7秒
Update语句在MyISAM和InfiniDB引擎上执行时间对比,图4:
TBNAME
ROWS
ENGINES
ITERATIONS
AVG
MIN
MAX
TOTALTIME
Tmp_user
6617109
MyISAM
1
196.321
196.321
196.321
3min16s
Tmp_user
6617109
InfiniDB
1
49.081
49.081
49.081
49s
user
109794560
MyISAM
1
980.429
980.429
980.429
16min20s
user
109794560
InfiniDB
1
127.069
127.069
127.069
2min7s
(图4)
注:超过2000万条数据的更新就会提示“mysqlslap:
Cannot run query UPDATE user1 a
, tmp_user b SET
a.level=b.level,a.moneybalance=b.money,a.greenpoint=b.greenpoint
WHERE a.userid=b.userid AND b.dateid='2012-01-29'; ERROR
:IDB-2001:
Join or subselect exceeds
memory limit.”