MemSQL、SQLFire、Oracle XE效能對比測試

標籤：memsql   sqlfire   newsql   效能   

1.軟體版本

MemSQL 3.1

Pivotal SQLFire 1.1.2

Oracle 11g XE

 

2.測試環境

宿主機

OS：Windows 7 64位 專業版

CPU：i7-3770 3.4GHz

記憶體：16G

硬碟：大於800G

 

VMware虛擬機器

OS：CentOS 6.3 64位

記憶體：16G

 

其中MemSQL運行於虛擬機器，SQLFire和Oracle直接運行於宿主機

SQLFire只開啟一個server member

3.DDL

MemSQL

DROP TABLE t_main;DROP TABLE t_rel1;DROP TABLE t_rel2;DROP TABLE t_rel3;create table t_main(rec_id BIGINT not null  PRIMARY KEY,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) ;create table t_rel1(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) ;create table t_rel2(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) ;create table t_rel3(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) ;create index rel1main on t_rel1(main_id);create index rel2main on t_rel2(main_id);create index rel3main on t_rel3(main_id);

SQLFire

DROP TABLE t_main;DROP TABLE t_rel1;DROP TABLE t_rel2;DROP TABLE t_rel3;create table t_main(rec_id BIGINT not null  PRIMARY KEY,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) PERSISTENT;create table t_rel1(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) PERSISTENT;create table t_rel2(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) PERSISTENT;create table t_rel3(rec_id BIGINT not null  PRIMARY KEY,main_id BIGINT,c1 DOUBLE PRECISION,c2 BIGINT,c3 VARCHAR(100),c4 VARCHAR(100),c5 VARCHAR(100),c6 VARCHAR(100)) PERSISTENT;create index rel1main on t_rel1(main_id);create index rel2main on t_rel2(main_id);create index rel3main on t_rel3(main_id);

 

Oracle

DROP TABLE t_main;DROP TABLE t_rel1;DROP TABLE t_rel2;DROP TABLE t_rel3;create table t_main(rec_id NUMBER(19,0) not null  PRIMARY KEY,c1 DOUBLE PRECISION,c2 NUMBER(19,0),c3 VARCHAR2(100),c4 VARCHAR2(100),c5 VARCHAR2(100),c6 VARCHAR2(100)) ;create table t_rel1(rec_id NUMBER(19,0) not null  PRIMARY KEY,main_id NUMBER(19,0),c1 DOUBLE PRECISION,c2 NUMBER(19,0),c3 VARCHAR2(100),c4 VARCHAR2(100),c5 VARCHAR2(100),c6 VARCHAR2(100)) ;create table t_rel2(rec_id NUMBER(19,0) not null  PRIMARY KEY,main_id NUMBER(19,0),c1 DOUBLE PRECISION,c2 NUMBER(19,0),c3 VARCHAR2(100),c4 VARCHAR2(100),c5 VARCHAR2(100),c6 VARCHAR2(100)) ;create table t_rel3(rec_id NUMBER(19,0) not null  PRIMARY KEY,main_id NUMBER(19,0),c1 DOUBLE PRECISION,c2 NUMBER(19,0),c3 VARCHAR2(100),c4 VARCHAR2(100),c5 VARCHAR2(100),c6 VARCHAR2(100)) ;create index rel1main on t_rel1(main_id);create index rel2main on t_rel2(main_id);create index rel3main on t_rel3(main_id);

 

 4.測試資料

4張表，每張100萬條資料

產生資料的程式碼片段為

String sql = "insert into t_main(rec_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?)";PreparedStatement st1 = conn.prepareStatement(sql);sql = "insert into t_rel1(rec_id,main_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?,?)";PreparedStatement st2 = conn.prepareStatement(sql);sql = "insert into t_rel2(rec_id,main_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?,?)";PreparedStatement st3 = conn.prepareStatement(sql);sql = "insert into t_rel3(rec_id,main_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?,?)";PreparedStatement st4 = conn.prepareStatement(sql);long a = System.currentTimeMillis();for (int i = 1; i <= count; i++) {st1.setLong(1, i);st1.setDouble(2, i * 0.1);st1.setLong(3, i * 2);st1.setString(4, "c3_" + i);st1.setString(5, "c4_" + i);st1.setString(6, "c5_" + i);st1.setString(7, "c6_" + i);st1.executeUpdate();st2.setLong(1, i);st2.setLong(2, i);st2.setDouble(3, i * 0.2);st2.setLong(4, i * 3);st2.setString(5, "c3_" + i);st2.setString(6, "c4_" + i);st2.setString(7, "c5_" + i);st2.setString(8, "c6_" + i);st2.executeUpdate();st3.setLong(1, i);st3.setLong(2, i);st3.setDouble(3, i * 0.3);st3.setLong(4, i * 4);st3.setString(5, "c3_" + i);st3.setString(6, "c4_" + i);st3.setString(7, "c5_" + i);st3.setString(8, "c6_" + i);st3.executeUpdate();st4.setLong(1, i);st4.setLong(2, i);st4.setDouble(3, i * 0.4);st4.setLong(4, i * 5);st4.setString(5, "c3_" + i);st4.setString(6, "c4_" + i);st4.setString(7, "c5_" + i);st4.setString(8, "c6_" + i);st4.executeUpdate();}

  

5.測試結果

 4張表，每張插入100萬資料，消耗時間對比

單位：毫秒

 

MemSQL	SQLFire	Oracle
624765	196140	1289811

 

以下為查詢測試，均執行10次求得平均消耗時間（不包含首次執行）

單位：毫秒

 

查詢測試一：單表整型欄位比較

select count(*) from t_main where c2>1000;

 

MemSQL	SQLFire	Oracle
21	675	58

 

查詢測試二：單表like

select count(*) from t_main where c4 like '%c%';

 

MemSQL	SQLFire	Oracle
41	875	133

 

查詢測試三：多表關聯浮點數sum

select sum(m.c1+r1.c1+r2.c1+r3.c1) "rt" from t_main m,t_rel1 r1,t_rel2 r2,t_rel3 r3 where m.rec_id=r1.main_id and m.rec_id=r2.main_id and m.rec_id=r3.main_id;

 

MemSQL	SQLFire	Oracle
1365	14640	2077

 

 

查詢測試四：多表關聯整型sum

select sum(m.c2+r1.c2+r2.c2+r3.c2) "rt" from t_main m,t_rel1 r1,t_rel2 r2,t_rel3 r3 where m.rec_id=r1.main_id and m.rec_id=r2.main_id and m.rec_id=r3.main_id;

 

MemSQL	SQLFire	Oracle
1360	10257	2084

 

6.總結

測試過程中CPU、記憶體使用量均未超過50%

插入效能SQLFire最高，MemSQL其次，Oracle最慢，MemSQL效率約是Oracle的兩倍

查詢效能MemSQL最高，Oracle其次，SQLFire最慢（慢的出奇。。。），MemSQL效率約是Oracle的兩倍

不知道怎樣的環境能測出30倍效能的提升。。。

 

MemSQL、SQLFire、Oracle XE效能對比測試