主页

[InnoDB系列] - InnoDB VS PBXT实测

周五, 2008/07/25 - 15:43 — yejr

1、前言

PBXT 是 PrimeBase 公司推出的MySQL插件引擎，其功能和 InnoDB 类似，主要特性如下：

  • MVCC Support:
    MVCC stands for Multi-version Concurrency Control. MVCC allows reading the database without locking.
  • Fully ACID complient:
    This means that transactionally safe, and able to handle multiple concurrent transactions.
  • Row-level locking:
    When updating, PBXT uses row-level locking. Row-level locking is also used during SELECT FOR UPDATE.
  • Fast Rollback and Recovery:
    PBXT
    uses a specialized method to identify garbage which makes "undo"
    unncessary. This make both rollback of transactions and recovery after
    restart very fast.
  • Deadlock Detection:
    PBXT identifies all kinds of deadlocks immediately.
  • Write-once:
    PBXT
    uses a log-based storage which makes it possible to write transactional
    data directly to the database, without first being writen to the
    transaction log.
  • Referential Integrity:
    PBXT supports foreign key definitions, including cascaded updates and deletes.
  • BLOB streaming:
    In combination with the BLOB Streaming engine PBXT can stream binary and media directly in and out of the database.

本次我们来实际对比测试下InnoDB和PBXT的性能区别。

2、准备

2.1 配置

PBXT 和 InnoDB 主要配置参数如下：

innodb:

innodb_buffer_pool_size = 6G
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_file_per_table

 

pbxt:

pbxt_checkpoint_frequency    = 28MB
pbxt_data_file_grow_size     = 8MB
pbxt_data_log_threshold      = 128MB
pbxt_garbage_threshold       = 50
pbxt_index_cache_size        = 2G
pbxt_log_buffer_size         = 2M
pbxt_log_cache_size          = 16MB
pbxt_log_file_count          = 3
pbxt_log_file_threshold      = 128MB
pbxt_record_cache_size       = 4G
pbxt_row_file_grow_size      = 1M
pbxt_transaction_buffer_size = 32MB

2.2 准备数据

本次测试分2部分进行，一部分是利用MySQL官方提供的 sql-bench 工具测试，另一部分采用 sysbench 做一个基准测试。

3、测试结果

3.1 sql-bench测试结果

Operation

seconds

usr

sys

cpu

tests

 

alter_table_add

12

0.01

0

0.01

100

innodb

11

0

0

0

100

pbxt

             

alter_table_drop

12

0.01

0.01

0.02

91

 

10

0.01

0

0.01

91

 
             

connect

4

1.98

1.08

3.06

10000

 

4

1.99

1.04

3.03

10000

 
             

connect+select_1_row

5

2.17

1.16

3.33

10000

 

5

2.08

1.15

3.23

10000

 
             

connect+select_simple

5

2.11

1.2

3.31

10000

 

5

1.91

1.31

3.22

10000

 
             

count

16

0.01

0.01

0.02

100

 

14

0

0

0

100

 
             

count_distinct

0

0.08

0.02

0.1

1000

 

1

0.08

0.02

0.1

1000

 
             

count_distinct_2

0

0.07

0.03

0.1

1000

 

0

0.08

0.03

0.11

1000

 
             

count_distinct_big

10

3.14

0.74

3.88

120

 

9

3.17

0.72

3.89

120

 
             

count_distinct_group

0

0.4

0.1

0.5

1000

 

0

0.4

0.07

0.47

1000

 
             

count_distinct_group_on_key

0

0.09

0.03

0.12

1000

 

0

0.09

0.04

0.13

1000

 
             

count_distinct_group_on_key_parts

1

0.4

0.08

0.48

1000

 

1

0.36

0.11

0.47

1000

 
             

count_distinct_key_prefix

1

0.08

0.03

0.11

1000

 

0

0.08

0.02

0.1

1000

 
             

count_group_on_key_parts

0

0.37

0.1

0.47

1000

 

0

0.39

0.08

0.47

1000

 
             

count_on_key

13

3.46

1.27

4.73

50100

 

16

3.45

1.35

4.8

50100

 
             

create+drop

67

0.01

0.04

0.05

10000

 

74

0

0

0

10000