当前位置: 首页 > 工具软件 > InfiniDB > 使用案例 >

mysql infinidb_InfiniDB与MyISAM性能对比测试(三)

吕岳
2023-12-01

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

 类似资料: