[22년] SQL vs InfluxDB 쿼리 성능 비교 (1)

문제 상황 

현재 우리 시스템에서는 SQL에 데이터를 쌓고 웹 에서는 redis를 이용하여 센서 데이터를 시각화하고있다.

하지만 아무리 Redis의 쿼리속도가 빠르고 좋다고 해도 SQL에 매 시간마다 쌓이는 센서 데이터들의 크기와 특정 조회 쿼리를 날릴 경우에 있어서 앞으로 시스템을 유지하는데 SQL만 쓰기에는 무리가 있다고 생각했다.

따라서 측정 데이터는 InfluxDB나 Marchbase DB를 이용하고, 추가로 SQL의 관계형 DB를 이용히는 것을 목표로 잡게 되었다.

 

먼저, SQL과 InfluxDB의 쿼리 속도에 대해 비교해야 한다고 생각했다.

따라서 각 DataBase에 천만건의 데이터를 쌓은 뒤 특정 쿼리들을 실행 한 후 검색 속도를 조회하기로 하였다.

 

 

 

[1] 인덱싱 된 MySQL에 천만 건의 데이터 적재 

 

SQL의 경우 일반적인 Insert문을 사용해서는 백만 건의 데이터를 Insert 하는데도 제법 많은 시간이 소요된다.

이유는 한 건의 데이터를 입력하더라도 트랜잭션, 동시성 제어 등 단순히 입력만 하더라도 SQL에서는 다양한 연산이 추가로 진행되기 때문이다.

이러한 경우 Insert문의 성능을 올리는 Bulk Insert란 MySQL에서 대량의 데이터를 insert할 때, 지원하는 SQL문이다.

일반적인 Insert문

INSERT INTO test_table( a, b, c) VALUES( 1, 2, 3 ); INSERT INTO test_table( a, b, c) VALUES ( 4, 5, 6 ); INSERT INTO test_table( a, b, c) VALUES( 7, 8, 9 );

Bulk Insert

INSERT INTO teest_table( a, b, c ) VALUES (1, 2, 3) , (4, 5, 6) , (7, 8, 9) ;

위의 방식에서 본 듯이, 우리가 보기에는 별 차이 없어 보이는 SQL 구문이지만,

SQL의 입장에서는 하나의 데이터 입력 후의 작업을 하냐 안하냐의 차이로 시간차이가 엄청 크게 나게된다.

 

그 외 속도 개선 방법 

1. LOAD DATA :  bulk insert 형식의 입력 파일을 로드해서 처리하는 방식
2. MUTI-VALUE INSERT : 쿼리문 하나에 n개의 데이터의 입력 방식인 buli knsert로 데이터 개수가 많아지면 쿼리 길이가 길어지기 때문에 max-allowed-packet 길이를 조절해야할 필요가 있다.
3. MULTI-LINE insert
4. TRANSACTION 처리 : 트랜잭션 중 너무 많은 쿼리가 들어가면 트랜잭션 버퍼가 커져 속도가 저하된다.
5. 인덱싱 비활성화 : INSERT 후 인덱싱 처리를 하면 하나씩 인덱싱 처리하는 수고를 던다.
6. AUTO COMMIT 비활성화 

 

나의 경우에는 파일을 통해서 IMPORT 하였다. 

from time import localtime
import datetime
import random 
import csv
from pandas import Series, DataFrame
f = open('../sql_dummy_data.txt','w')


current_time = 1617617700

print("id, point_id, feature_type_id, value, hit_flag, acquired_at")
for i in range(0,10000000):
    id = i + 1
    point_id = i%10 + 1
    feature_type_id = 1 
    value= round(random.randrange(0,4) * random.random(),5)
    if(value==0): value=round(random.random(),5)
    hit_flag = 0
    if(value > 2): hit_flag = 1 
    acquired_at = int(current_time+(i//10)*60)
    t = acquired_at
    acquired_at = datetime.datetime.fromtimestamp(acquired_at)
    #f.write(data)
    #wr.writerow([id,point_id,feature_type_id,value,hit_flag,acquired_at])
    f.write("{0},{1},{2},{3},{4},{5}\n".format(id,point_id,feature_type_id,value,hit_flag,acquired_at))
f.close()

간단한 형식의 더미 데이터 파일을 위해 프로그램을 작성하였다.

작성 후 천만건의  아래의 형식에 맞는 데이터가 생성되게 된다.

id, point_id, feature_type_id, value, hit_flag, acquired_at

그 후 워크 벤치에서 import 된 데이터를 확인한다. 

전체 갯수를 COUNT문으로 조회하는 경우에는 아래와 같이 2.3초라는 시간동안 천만건의 데이터를 조회하는 것을 확인 할 수 있다.

 

 

 

 

 

 

[2]  InfluxDB에 천만 건의 데이터 적재 

 

InfluxDB 같은 경우는 아래와 같은 형식의 텍스트 파일로 InfluxQL의 insert문을 난수값의 value로 1000만건 생성한 뒤에 만들었다.

아래는 influxDB에서 제공하는 더미 데이터의 예시이다. 

이를 기반으로 현재 sql에 쌓여있는 데이터들의 특징들과 똑같이 influxdb에도 값을 세팅했다.

아래는 influxdb에서 제공하는 더미 데이터의 예시이다. 

https://s3.amazonaws.com/noaa.water-database/NOAA_data.txt

# DDL

CREATE DATABASE NOAA_water_database

# DML

# CONTEXT-DATABASE: NOAA_water_database

h2o_feet,location=coyote_creek water_level=8.120,level\ description="between 6 and 9 feet" 1566000000
h2o_feet,location=coyote_creek water_level=8.005,level\ description="between 6 and 9 feet" 1566000360
h2o_feet,location=coyote_creek water_level=7.887,level\ description="between 6 and 9 feet" 1566000720
h2o_feet,location=coyote_creek water_level=7.762,level\ description="between 6 and 9 feet" 1566001080

 

나의 경우에는 인덱싱된 SQL과 InfluxDB의 쿼리 속도 차이를 비교하는 것이기 때문에 새로운 더미 데이터를 천만 건 만든 뒤 insert했다. 천만건을 insert하는데도 큰 부담없이 insert할 수 있었다.

구축된 InfluxDB의 데이터는 아래와 같다. ( SQL의 데이터셋과 다른점은 PK의 유무 ) 

 

 

이후 쿼리 속도는 각 워크벤치에서 Select를 한 경우로 확인했고, 웹에서도 HTTP 통신을 통해 데이터를 불러오는 데 까지 얼마나 시간이 소요됬는지 확인을 하였다.

( InfluxDB의 워크벤치 대용 오픈 소스 : https://github.com/CymaticLabs/InfluxDBStudio

웹의 경우 아직 정리를 하지 않았고, 먼저 워크벤치를 통해 비교한 속도차이는 아래와 같다.

 

 

 

 

 

[3] SQL vs InfluxDB 쿼리 성능 비교

쿼리 성능은 InfluxDB가 인덱싱된 sQL과 비교해서 큰 성능을 나타냄을 확인할 수 있었다. 

쿼리문	SQL	INFLUX
select COUNT(*) from features	2.42 s	273 ms
SELECT COUNT(*) FROM "features" where hit_flag='1'	3.031 s	37 ms
select COUNT(*) from features where value > 1.2	3.196 s	682 ms
SELECT COUNT(*) FROM my_db.features WHERE value > 1.2 AND point_id = 5	25.601 s	69 ms
SELECT COUNT(*) FROM my_db.features WHERE value > 1.2 AND point_id = 5 OR point_id = 10	3.563 s	107 ms
SELECT COUNT(,*) FROM my_db.features WHERE point_id = 2 AND value > 0.5 OR value < 1.2	3.625 s	600 ms
SELECT COUNT(*) FROM features WHERE time >'2021-05-05' and  time < '2022-05-05'	2.297 s	151 ms
SELECT COUNT(*) FROM features WHERE time >'2021-05-05'  and  time < '2022-05-05' and point_id = 5 or point_id = 2 and value > 1.5	3.891 s	46 ms

 

 

 

 

[4] SQL vs InfluxDB 용량 비교

 

테이블 용량

MY SQL	Influx DB
1118.9 MB	106 MB

인플럭스 DB의 경우 빠른 쿼리를 위해 추가로 저장하는 테이블이 있기 때문에 조금 더 용량이 크게 측정될 수 있으나, SQL과 비교해서는 압도적으로 적은 용량을 차지한다.

 

 

 

 

 

속도나 용량면에서 InfluxDB가 크게 우수한 것을 확인했기 때문에 데이터는 InfluxDB에 저장하고 기존의 사용하던 관계형  DB 를 같이 사용하는 식으로 웹 서비스를 개발하고자 목표로 하고 있다.

다음에는 추가적으로 Node js 환경에서 InfluxDB를 사용하여 그래프를 시각화하고 redis와 비교하는 것을 목표로 하고 있다.