mysql infinidb_InfiniDB与MyISAM性能对比测试（三）

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