ClickHouse vs MariaDB ColumnStore 성능 테스트

동일한 노트북에서 도커 환경으로 각각 테스트한 결과입니다.

 

MariaDB ColumnStore 10.5.12 

1. 테이블 생성
CREATE TABLE `tt_trade` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `d` varchar(10) DEFAULT NULL,
  `e` integer
) ENGINE=ColumnStore DEFAULT CHARSET=latin1;

2. 데이터 50만건 넣기
2-1. jdbc option = jdbc:mariadb://127.0.0.1:3306/mobdw?rewriteBatchedStatements=true
2-2. Batch처리로 1000건 식 입력 후 커밋
2-3. 처리 속도: 342 초

3. 데이터 조회 속도 (최초)
select count(1), sum(e)
from tt_trade;
>> 500000	125000250000
>> 74ms

4. 데이터 갱신 속도
update tt_trade
set a = concat('a', a)
   ,e = e+1
where 1=1
;
14.762 초

 

---

 

ClickHouse 22.1.3.7

1. 테이블 생성
CREATE TABLE dev4u.tt_trade (
  `a` varchar(10) ,
  `b` varchar(10) ,
  `c` varchar(10) ,
  `d` varchar(10) ,
  `e` integer
) ENGINE = MergeTree
ORDER BY a
SETTINGS index_granularity = 8192;
;

2. 데이터 50만건 넣기
2-1. jdbc option = jdbc:mariadb://127.0.0.1:3306/mobdw?rewriteBatchedStatements=true
2-2. Batch처리로 1000건 식 입력
2-3. 처리 속도: 2 초

3. 데이터 조회 속도 (최초)
select count(1), sum(e)
from tt_trade
>> 500000	125000250000
>> 10ms

4. 데이터 갱신 속도
update tt_trade
set a = concat('a', a)
   ,e = e+1
where 1=1
;
Cannot UPDATE key column `a`. (CANNOT_UPDATE_COLUMN)
;
update tt_trade
set b = concat('b', b)
   ,e = e+1
where 1=1
;
73 ms

 

Conclusion by TEST

"ClickHouse Gets a Victory"

 

 

TEST Code = https://github.com/dev4u-korea/DBTest

 

 

<< MariaDB ColumeStore TIPS >>

Here are some tips for fast inserting data into a MariaDB ColumnStore database using JDBC:

1. Use batch inserts: One of the most effective ways to improve insert performance is to use batch inserts. Instead of inserting data one row at a time, you can group multiple rows together into a single batch and insert them all at once. This reduces the overhead of sending multiple individual insert statements to the database and can significantly improve performance.
2. Use the LOAD DATA statement: The LOAD DATA statement in MariaDB ColumnStore is specifically designed for bulk loading of data. This statement can be used to efficiently insert data from a CSV file into a MariaDB ColumnStore table. The LOAD DATA statement is highly optimized and can provide much faster performance than traditional insert statements.
3. Use the rewriteBatchedStatements option: The rewriteBatchedStatements option in the MariaDB Connector/J JDBC driver can be used to optimize batch statements. When this option is set to true, the driver will group multiple similar SQL statements into a single batch, reducing the overhead of multiple individual statement executions.
4. Disable auto-commit: By default, MariaDB Connector/J automatically commits changes after each statement execution. To improve performance, it is recommended to disable auto-commit and explicitly manage transactions yourself. This allows you to group multiple insert statements into a single transaction and commit them all at once, reducing the overhead of committing multiple individual transactions.
5. Use connection pooling: Connection pooling can help to reduce the overhead of establishing new database connections for each insert operation. A connection pool allows you to reuse existing database connections instead of creating a new connection for each operation. This can result in a significant performance improvement for large batch insert operations.