IoT 프로젝트_3(TSDB로 성능 80% 개선하기)

김두현·2024년 11월 12일

SmartHome_IoT

목록 보기

4/6

Smarthome 프로젝트의 주 기능인 모니터링 시스템을 위해 센서 데이터를 실시간으로 수집 및 조회가 필수적이다. 센서 데이터를 관리하는 DB 시스템을 구현하기에 앞서 게이트웨이 관계자분과 회의를 하는 시간을 가졌다.

관계자 분께서는 각 가정마다 발생하는 수많은 센서 데이터를 RDB에 저장하기엔 용량이 너무 크다고 하셔 이를 위해 시계열 데이터베이스를 도입하라고 조언을 주셨다.

그래서 센서 데이터를 본격적으로 수집하기 전에 RDB(관계형 데이터베이스)와 TSDB(시계열 데이터베이스) 간의 성능을 비교하기 위해 테스트를 진행했다.

먼저 본격적으로 RDB와 비교하기 전에 테스트할 데이터의 종류와 cumputing 환경을 정의했다.

Testing Condition

테스트 데이터

한 가정에 hub 역할의 게이트웨이 하나 존재
게이트웨이에서 기본적으로 온도, 습도, 이슬점 데이터를 5분 주기로 전송
한 가정에 문 감지, 충격 감지, 모션 감지, 가스/연기/누수 감지 등 8개 이상의 센서 설치
프로젝트 예상 기간 6개월(== 데이터 수집 기간)

Cumputing 환경

GCP(Google Cloud Platform), Ubuntu20.04
2 Core, 2g Ram
MySQL: 8.0.39-0ubuntu0.20.04.1
InfluxDB: 2.7

센서 데이터 모델 설계

Test Data 조건

5분 주기 데이터: 12 * 24 = 288건, 6개월 수집
한 게이트웨이당 총 레코드 수: 288건 × 180일 = 51,840건
전체 게이트웨이 100개 기준: 51,840건 × 100 ≒ 5,00,000건
총 3번 500만 데이터를 적재해 total size를 check

각 가구 환경 데이터(env_data Table) Schema

CREATE TABLE env_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    timestamp DATETIME,
    gateway_id VARCHAR(11),
    temperature FLOAT,
    humidity FLOAT,
    dew_point FLOAT,
    INDEX idx_timestamp (timestamp),
    INDEX idx_gateway_id (gateway_id)
);

Issue

mysql 2055 error
mysql에서 한번에 대량의 패킷을 전송하지 못 하도록 설정 → 패킷 크기를 up

Insert 성능

1. mysql

// 아래 명령어로 table 정보를 update후 실행 필요
ANALYZE TABLE env_data;

// t
SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_mb,
    ROUND(data_length / 1024 / 1024, 2) AS data_size_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_size_mb
FROM 
    information_schema.tables
WHERE 
    table_schema = 'sensor_test' 
    AND table_name = 'env_data';

500만개 → 535MB
1000만개 → 1080MB
1500만개 → 1868MB

위 사진과 같이 RDB는 실제 저장된 data만큼 index의 사이즈 또한 상당한 부분을 차지한다. 더 복잡한 센서일 수록 query 속도를 향상 시키기 위해 index가 추가적으로 필요해 현재보다 더 많은 용량을 차지할 가능성이 높다.

2. influxDB

500만개 → 133MB
1000만개 → 230MB
1500만개 → 339MB

influxDB는 데이터베이스 용량을 직접적으로 측정하는 쿼리를 제공하지 않아 디스크의 샤드의 크기를 측정함으로서 간접적으로 “env_data” measurement size를 측정했다.

위 사진과 같이 influxDB는 TSDB의 압축률로 인해 1500만개의 데이터 삽입에 339MB 사이즈로 mysql 대비 80% 이상 용량 감소를 볼 수 있다.

현재는 온도, 습도, 이슬점과 같은 게이트웨이 기본 데이터만 비교했는데 실제 각 가정 당 센서를 8개 이상으로 설치 예정으로 더 많은 센서 데이터가 생성될 것으로 보아 influxdb에서 유의미한 성능 차이가 있다고 판단된다.

💡500만개 insert time: 193.425 sec

Select 성능

flask API server를 구현 → python을 이용해 성능 비교
두 db에 동일한 결과를 반환하는 쿼리 시간 측정
1000번의 동일한 쿼리를 두 db에 요청을 해 평균 속도 계산

1. 가장 최근 온도

SELECT temperature, timestamp
FROM env_data
WHERE gateway_id = 'W220_TSMGK0'
ORDER BY timestamp DESC
LIMIT 1;

from(bucket: "InfluxDB_Test")
  |> range(start: -2y)
  |> filter(fn: (r) => r._measurement == "env_data" and r._field == "temperature" and r.gateway_id == "W220_0GLZJ2")
  |> last()
  |> yield(name: "last_temperature")

1000번	MySQL	InfluxDB
avg(sec)	0.104578	0.067408

InfluxDB is faster by 0.037170seconds on average
influxDB 도입 쿼리 실행 시간 35% 감소

2. 하루 평균 온도(1시간 단위)

SELECT 
    DATE(timestamp) AS date,
    HOUR(timestamp) AS hour,
    AVG(temperature) AS avg_temperature,
    AVG(humidity) AS avg_humidity,
    AVG(dew_point) AS avg_dew_point
FROM 
    env_data
WHERE 
    gateway_id = 'W220_TSMGK0'
    AND DATE(timestamp) = '2023-01-01'
GROUP BY 
    DATE(timestamp), HOUR(timestamp)
ORDER BY 
    DATE(timestamp), HOUR(timestamp)

from(bucket: "InfluxDB_Test")
  |> range(start: time(v: "2023-01-01T00:00:00Z"), stop: time(v: "2023-01-02T00:00:00Z"))
  |> filter(fn: (r) => r._measurement == "env_data" and r._field == "temperature" and r.gateway_id == "W220_0GLZJ2")
  |> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
  |> yield(name: "mean")

1000번	MySQL	InfluxDB
avg(sec)	0.432224	0.089534

InfluxDB is faster by 0.342690 seconds on average
influxDB 도입 쿼리 실행 시간 79% 감소

3. 일주일 평균 온도(하루 단위)

SELECT 
    DATE(timestamp) AS date,
    AVG(temperature) AS avg_temperature
FROM 
    env_data
WHERE 
		gateway_id = 'W220_TSMGK0'
    AND timestamp >= '2023-01-01' 
    AND timestamp < '2023-01-08'
GROUP BY 
    DATE(timestamp)
ORDER BY 
    DATE(timestamp);

from(bucket: "InfluxDB_Test")
  |> range(start: time(v: "2023-01-01T00:00:00Z"), stop: time(v: "2023-01-07T01:00:00Z"))
  |> filter(fn: (r) => r._measurement == "env_data" and r._field == "temperature" and r.gateway_id == "W220_0GLZJ2")
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)
  |> yield(name: "mean")

1000번	MySQL	InfluxDB
avg(sec)	0.382193	0.068615

InfluxDB is faster by 0.313578 seconds on average
influxDB 도입 쿼리 실행 시간 82% 감소

++ record(point) total lengh

select count(*) from env_da

from(bucket: "InfluxDB_Test")
  |> range(start: 0)
  |> filter(fn: (r) => r._measurement == "env_data")
  |> group()
  |> count(column: "_value")

1번	MySQL	InfluxDB
sec	6.34	2.36

💡IoT 프로젝트 특성 상 DB에 저장된 센서 데이터의 U(update), D(delete) 연산의 성능은 우선순위가 아니여서 따로 측정 X

비교 결과

	MySQL	InfluxDB	개선율
최근 온도	0.1046s	0.0647s	35%
시간별 평균 기온(1일)	0.4322s	0.0895s	82%
일일 평균 기온(1주)	0.3822s	0.0686s	78%
Storage Size	1080MB	230MB	78%

최종 결과를 통해 센서 종류를 하나로 가정한 test 조건에서도 query 성능이 평균 70 ~ 80% 정도의 속도가 개선이 됐으며 용량에서도 78% 정도의 차이가 난다.

⇒ 속도와 용량 둘 다 InfluxDB와 RDB 비교에서 유의미한 차이가 있다고 판단!

실제 가구 모니터링 프로젝트를 구현하기 위해서는 다양한 센서가 필요하며 각각의 센서들이 가지고 있는 스키마 형태가 다 달라 현재보다 훨씬 복잡한 형태의 query가 예상이 된다. timestamp에 따른 query가 복잡할 수록 TSDB의 성능 차이는 더 두드러질 거라고 판단되며 센서를 새롭게 추가할 때마다 각 센서에 맞는 table을 생성하는 것 또한 개발 생산성을 저해하는 작업이라고 생각돼 InfluxDB 를 도입해 센서 데이터를 수집하기로 최종 결정했다.