Substation API 성능 개선기 (ORM vs SQL) 1편
- 2024-02-27
[개요]
대규모 반도체 공장에서 MES 업무를 하다보면, 데이터베이스에 쌓인 여러 데이터를 활용해서 원하는 형태로 데이터를 가져올 수 있어야 하는 역량이 중요합니다. 뿐만 아니라 부하가 큰 트랜잭션 쿼리를 날리다보니 DA 를 통해서 쿼리를 튜닝하는 일도 빈번한데요, API 성능개선을 하면서, Raw SQL 과 ORM 으로 DB 에서 원하는 정보의 데이터를 가져오는 성능을 비교해 보려 합니다.
[ORM VS SQL]
언제부터인가 ORM 이 트렌드가 되고, 누구나 다 SQL 보다는 ORM 으로 DB 트랜잭션을 처리하기 원하지만 개인적으로 어떤 장점이 있기에 ORM 을 사용하고 싶은가 라는 질문에는 여러 개발자, 친구, 선배에게 물어봐도 그럴듯한 답변을 듣지 못하고 혼자서 많은 고민을 해왔습니다.
그나마 'DB 의 종류에 구애받지 않기에", "개발 생산성을 높일 수 있어서" 라는 답변을 들었지만 개인적으로 저를 설득할만한 대답을 되지 않았습니다.
개발 생산성을 높일 수 있다
규모가 작은 초기 시스템에서는 ERD 를 작성할 필요가 없기에 개발 생산성을 높일 수 있다는 점에 공감하지만, 규모가 커진 상태에서는 개발 생산성 효과가 오히려 떨어지는 것 아닌가 하는 의문이 있습니다. 수십 ~ 수백개의 테이블로 구성된 DB 구조를 ERD 가 아닌 수천줄 짜리 코드를 보고 테이블 관계를 이해할 수 있나?
가독성을 높일 수 있다.
프로그래밍 언어로 만든 함수는 '논리'가 생명 이지만, DB 를 다루는 SQL 은 '데이터 정합성' 이 생명입니다. 똑같은 SQL 도 개발 DB, 운영DB 그리고 시간에 따라 데이터가 다르게 조회됩니다. 아래 본문처럼, ORM 은 SQL 블록단위로 코드를 작성한 구조가 얼핏 보면 가독성이 좋아보일 수 있으나, 블록 단위 데이터를 까볼 수 는 없습니다. 반대로 Raw SQL 은거대한 SQL 구조에서 내부 블록단위로 쿼리를 실행해 데이터를 확인해 볼 수 있고, 어떻게 수정해야할 지 바로 확인이 가능합니다.
성능에 대한 저하
기본적으로 ORM 으로 데이터를 처리하는게 SQL 을 활용하는 것보다, 성능이 월등히 떨어집니다. (ORM 내부적으로 SQL 로 변환하는 과정을 거치며, 조인이 많아질수록 성능은 기하 급수적 으로 감소합니다)
또한 튜닝이 필요한 시점에서는 결국 다시 SQL 로 작성해야 하는 단점이 있습니다.
하지만 최근 지하철을 타며 회사를 출근하다가 문득 이 논쟁에 대한 정답을 찾았습니다. 그것은 바로 OLTP 와 OLAP 서비스에 따라 다르다는 것!
[OLTP 와 OLAP]
이 용어는 주로 Database 에 대해서 공부해본 사람이라면 한번쯤은 들어 봤을 것으로 보입니다.
OLTP (Online Transaction Processing) 는 현재의 데이터 처리가 얼마나 정확하고, 무결한지가 중요합니다. 그렇기 때문에 주로 데이터의 저장, 삭제, 수정 등의 실질적인 데이터를 수정하는 작업을 의미하고, 단순 CRUD 가 많고 트래픽이 방대한 서비스에서 주로 사용됩니다. 예시로 도메인 주도 설계에서, 여러 도메인을 정의하고 해당 도메인간의 연관관계 이벤트가 많이 발생하는 서비스라면 OLTP 에 가깝다고 표현할 수 있을 것같습니다.
OLAP (Online Analytical Processing) 은 이미 지정된 데이터를 바탕으로 어떤 정보를 제공하는지가 중요합니다. 따라서 OLAP 는 데이터가 무결하고 정확하다는 전제를 바탕으로 고객 또는 사용자가 원하는 정보를 어떤식으로 표현하고 제공하는지를 의미합니다. 예시로 설비의 여러 생산성 지표나 모니터링 같은 데이터를 추출하거나, 회사의 년간 각 부서별 임금 상승률 같은 분석용 데이터를 주로 보여주는 서비스라면 OLAP에 가깝다고 표현할 수 있습니다.
| 구분 | OLTP | OLAP |
| 속도 | 수 초 이내 | 수 초 이상 수분 이내 |
| 관리단위 | 테이블 | 분석된 정보 |
| 최적화 방법 | 트랜잭션 효율화, 무결성의 극대화 | 조회속도, 정보의 가치, 편의성 |
| 데이터 특성 | 트랜잭션 중심 | 정보 중심 |
| 예시 | 회원정보 수정 | 1년 간의 주요 인기 트렌드 |
| 상품 주문 | 한달간의 항목별 수입, 지출 | |
| 댓글 남기기 및 수정 | 10년간 A 회사의 직급별 임금 상승률 |
정리를 하자면 단순한 데이터의 조회 및 저장이 주로 발생하는, 가볍지만 트래픽 건수가 많은 서비스의 경우에는 ORM 이 트렌드가 되고, 데이터를 가공하고 분석하는, 무겁지만 트래픽 건수가 상대적으로 적은 서비스 에서는 SQL 이 많이 쓰이는 것으로 보입니다.
[수정 API]
companies/{company_id}/enterprise-dga-grade-status
변압기의 DGA 가스를 측정해서, AI 알고리즘을 돌린 후, 최신 진단한 상태값과 바로 직전 진단한 DGA 의 상태값을 보내 설비 별 상태값의 추이를 보여주도록 하는 API 입니다.
서브스테이션 2.0 대시보드 화면
[DB 구조]
해당 시스템에서는 PostgreSQL DB 를 사용했으며, 설비를 지칭하는 Asset 테이블과 DGA 를 측정한 DataMtrBody 그리고 설비와, DGA 정보를 이용해 AI 알고리즘 결과를 저장한 AiMtrBodyDga 테이블을 이용하여 데이터를 가져옵니다. 구체적인 테이블, 칼럼 설명은 보안상 생략합니다.
Before (ORM)
해당 방식은 SqlAlchemy 의 ORM 을 활용해서 데이터를 가져오는 형태의 데이터 입니다.
# time 측정
start = time.time()
math.factorial(100000)
# asset 별 모든 측정일과 이전 측정일 추출
asset_info = (
session.query(
Asset.id.label("asset_id"),
Asset.asset_name.label("asset_name"),
DataMtrBodyDga.acquisition_date.label("acquisition_date"),
func.lead(DataMtrBodyDga.acquisition_date, 1)
.over(
partition_by=[Asset.id, Asset.asset_name],
order_by=DataMtrBodyDga.acquisition_date.desc(),
)
.label("prev_acquisition_date"),
)
.select_from(Asset)
.join(DataMtrBodyDga, DataMtrBodyDga.asset_id == Asset.id)
.filter(
Asset.company_id == company_id,
Asset.asset_type == "MTR",
Asset.serial_no.in_(available_licenses),
)
.order_by(
Asset.id, Asset.asset_name, DataMtrBodyDga.acquisition_date.desc()
)
.subquery()
)
# dataMtrBodyDga 테이블 기준 asset 별 최신 측정일과 바로 이전 측정일 추출
asset_acquisition_info = (
session.query(
asset_info.c.asset_id,
asset_info.c.asset_name,
func.max(asset_info.c.acquisition_date).label("acquisition_date"),
case(
[
(
func.max(asset_info.c.prev_acquisition_date) == None,
func.max(asset_info.c.acquisition_date),
),
(
func.max(asset_info.c.prev_acquisition_date) != None,
func.max(asset_info.c.prev_acquisition_date),
),
]
).label("prev_acquisition_date"),
)
.select_from(asset_info)
.group_by(asset_info.c.asset_id, asset_info.c.asset_name)
.subquery()
)
# asset 측정일 기준별 진단 결과 건수 추출
diag_info = (
session.query(
DataMtrBodyDga.asset_id.label("asset_id"),
DataMtrBodyDga.acquisition_date.label("acquisition_date"),
AiMtrBodyDga.ai_diagnosis_result.label("ai_diagnosis_result"),
)
.select_from(DataMtrBodyDga) # LEFT OUTER JOIN 의 기준 테이블을 설정하기 위해 사용
.outerjoin(Asset, DataMtrBodyDga.asset_id == Asset.id)
.outerjoin(
AiMtrBodyDga,
DataMtrBodyDga.id == AiMtrBodyDga.data_mtr_body_dga_id,
)
.filter(
Asset.company_id == company_id,
Asset.asset_type == "MTR",
Asset.serial_no.in_(available_licenses),
)
.subquery()
)
# asset 별 최신 측정일 기준 상태 건수
query1 = (
session.query(
func.count(diag_info.c.ai_diagnosis_result).label("total_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 0, "NORMAL")])
).label("normal_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 1, "CAUTION")])
).label("caution_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 2, "WARNING")])
).label("warning_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 3, "CRITICAL")])
).label("critical_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 4, "FAULT")])
).label("fault_cnt"),
)
.select_from(asset_acquisition_info) # LEFT OUTER JOIN 의 기준 테이블을 설정하기 위해 사용
.join(
diag_info,
and_(
asset_acquisition_info.c.asset_id == diag_info.c.asset_id,
asset_acquisition_info.c.acquisition_date
== diag_info.c.acquisition_date,
),
)
.first()
)
# asset 별 이전 측정일 기준 상태 건수
query2 = (
session.query(
func.count(diag_info.c.ai_diagnosis_result).label("total_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 0, "NORMAL")])
).label("normal_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 1, "CAUTION")])
).label("caution_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 2, "WARNING")])
).label("warning_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 3, "CRITICAL")])
).label("critical_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 4, "FAULT")])
).label("fault_cnt"),
)
.select_from(asset_acquisition_info) # LEFT OUTER JOIN 의 기준 테이블을 설정하기 위해 사용
.join(
diag_info,
and_(
asset_acquisition_info.c.asset_id == diag_info.c.asset_id,
asset_acquisition_info.c.prev_acquisition_date
== diag_info.c.acquisition_date,
),
)
.first()
)
end = time.time()
print("****************************************************")
print(f"{end - start:.5f} sec")
print("****************************************************")| 1회시도 (DB Cache 제거) | 2 회시도 (DB Cache 제거) | 3 회시도 (DB Cache 제거) | |
| 1 try | 6.62579 sec | 6.28475 sec | 6.25671 sec |
| 2 try | 5.42588 sec | 5.30851 sec | 4.69481 sec |
| 3 try | 4.86993 sec | 4.90473 sec | 4.73450 sec |
After (SQL)
해당 방식은 SqlAlchemy 의 Core 를 활용해서 Raw SQL 형태로 데이터를 가져오는 데이터 입니다.
기존 'IN' 절로 처리한 부분을 'ANY' 로 사용한 이유는, PostgreSQL 에서 varchar 타입의 칼럼의 경우, SqlALchemy 는 Record 타입으로 인지하고 값을 비교하여 에러가 납니다.
해당 에러에 대해서 아래와 같이 변환하여 사용합니다.
ANY (select unnest(:available_licenses))
# time 측정
start = time.time()
math.factorial(100000)
sql_statement = text(
"""
WITH asset_info AS (
SELECT a.asset_id, a.asset_name
,MAX(a.acquisition_date) AS acquisition_date
,CASE WHEN MAX(a.prev_acquisition_date) IS NULL THEN MAX(a.acquisition_date)
WHEN MAX(a.prev_acquisition_date) IS NOT NULL THEN MAX(a.prev_acquisition_date)
END AS prev_acquisition_date
FROM (
SELECT a.id AS asset_id
,a.asset_name AS asset_name
,dmbd.acquisition_date
,LEAD(dmbd.acquisition_date,1)
OVER (PARTITION BY a.id, a.asset_name ORDER BY dmbd.acquisition_date DESC )
AS prev_acquisition_date
FROM substation.asset a INNER JOIN substation.data_mtr_body_dga dmbd
ON a.id = dmbd.asset_id
WHERE a.asset_type = 'MTR'
AND a.company_id = :company_id
AND a.serial_no = ANY (SELECT UNNEST(:available_licenses))
ORDER BY a.id, a.asset_name, dmbd.acquisition_date DESC
) a
GROUP BY a.asset_id, a.asset_name
), diag_info AS (
SELECT dmbd.asset_id, dmbd.acquisition_date , ambd.ai_diagnosis_result
FROM substation.data_mtr_body_dga dmbd LEFT OUTER JOIN substation.asset a
ON dmbd.asset_id = a.id LEFT OUTER JOIN substation.ai_mtr_body_dga ambd
ON dmbd.id = ambd.data_mtr_body_dga_id
WHERE a.company_id = :company_id
AND a.serial_no = ANY (SELECT UNNEST(:available_licenses))
AND a.asset_type = 'MTR'
)
SELECT COUNT(*) AS TOTAL_CNT
,COUNT(CASE WHEN b.ai_diagnosis_result = 0 THEN 'NORMAL' END ) AS NORMAL
,COUNT(CASE WHEN b.ai_diagnosis_result = 1 THEN 'CAUTION' END ) AS CAUTION
,COUNT(CASE WHEN b.ai_diagnosis_result = 2 THEN 'WARNING' END ) AS WARNING
,COUNT(CASE WHEN b.ai_diagnosis_result = 3 THEN 'CRITICAL' END ) AS CRITICAL
,COUNT(CASE WHEN b.ai_diagnosis_result = 4 THEN 'FAULT' END ) AS FAULT
FROM asset_info a LEFT OUTER JOIN diag_info b
ON a.asset_id = b.asset_id
WHERE a.acquisition_date = b.acquisition_date
UNION ALL
SELECT COUNT(*) AS TOTAL_CNT
,COUNT(CASE WHEN b.ai_diagnosis_result = 0 THEN 'NORMAL' END ) AS NORMAL
,COUNT(CASE WHEN b.ai_diagnosis_result = 1 THEN 'CAUTION' END ) AS CAUTION
,COUNT(CASE WHEN b.ai_diagnosis_result = 2 THEN 'WARNING' END ) AS WARNING
,COUNT(CASE WHEN b.ai_diagnosis_result = 3 THEN 'CRITICAL' END ) AS CRITICAL
,COUNT(CASE WHEN b.ai_diagnosis_result = 4 THEN 'FAULT' END ) AS FAULT
FROM asset_info a LEFT OUTER JOIN diag_info b
ON a.asset_id = b.asset_id
WHERE a.prev_acquisition_date = b.acquisition_date
"""
)
result = session.execute(sql_statement,{
"available_licenses": available_licenses,
"company_id": company_id
})
end = time.time()
print("****************************************************")
print(f"{end - start:.5f} sec")
print("****************************************************")| 1회시도 (DB Cache 제거) | 2 회시도 (DB Cache 제거) | 3 회시도 (DB Cache 제거) | |
| 1 try | 4.37999 sec | 4.64817 sec | 4.29508 sec |
| 2 try | 1.76666 sec | 1.64863 sec | 1.58068 sec |
| 3 try | 1.61587 sec | 1.56709 sec | 1.59817 sec |
[정리. ]
SQL 의 처리 과정이나 로직에 대해서는 따로 설명하지는 않지만, DB 캐시에 의한 성능 효과를 고려해서 1try 기준의 평균값으로 시간을 측정 했을 때 Before (ORM) 6.38908, After(SQL) 4.44108 아래와 같이 구해집니다.
(4.44108 / 6.38908 ) x 100 = 69.51 %
소요 시간을 기존 대비 30.48% 가량 줄일 수 있었습니다.
다만 흥미로운 부분으로 DB 캐시 효과를 고려한 3try 기준의 평균값으로 측정하면 아래와 같이 구해집니다. Before (ORM) 4.83638, After (SQL) 1.59371
(1.59371 / 4.83638 ) x 100 = 32.95 %
소요 시간을 기존 대비 67 % 가량 줄일 수 있었습니다.
이 수치는 개발환경에서 진행된 테스트로, 테이블에 적재된 데이터 용량과, DB 부하상태에 따라서 수치에 영향을 받을 수 있으며, 그럴 경우 성능은 더 차이가 날 것으로 보입니다.
* DB 조인이 더 많아지는 경우에도 마찬가지 입니다.
[성능 저하 원인. ]
그렇다면 'ORM 이 변경한 쿼리 구조가 성능이 나쁜건지' 혹은 'ORM 이 SQL 로 변경하는 과정에서 오랜 걸린 건지? 에 대한 궁금중이 생길 수 있을 것 같은데요. ORM 이 변경한 쿼리로 직접 실행 했을 때의 결과는 아래와 같았습니다.
ORM 으로 변환된 SQL
| 1회시도 (DB Cache 제거) | 2회시도 (DB Cache 제거) | 3회시도 (DB Cache 제거) | |
| 1 try | 4.68825 sec | 4.95818 sec | 4.51720 sec |
| 2 try | 1.86746 sec | 1.88096 sec | 1.97292 sec |
| 3 try | 1.83698 sec | 1.87081 sec | 1.87755 sec |
| 1회시도 (DB Cache 제거) | 2회시도 (DB Cache 제거) | 3회시도 (DB Cache 제거) | |
| 1 try | 6.62579 sec | 6.28475 sec | 6.25671 sec |
| 2 try | 5.42588 sec | 5.30851 sec | 4.69481 sec |
| 3 try | 4.86993 sec | 4.90473 sec | 4.73450 sec |
[ORM 이 가공한 SQL 로그]
[Raw SQL 로 실행한 로그]
AND a.serial_no = ANY (SELECT UNNEST(%(available_licenses)s))
AND a.serial_no = ANY (SELECT UNNEST(%(available_licenses)s))[비고.]
ORM 에서 성능 관련 얘기를 할 때는 보통 N+1 이슈에 대해서 많이 다루는데요, 이번 글에서는 ORM 의 구조적인 형태로 발생하는 트랜잭션 비효율을 다루기보다는 동일한 형태의 테이블 조인으로 데이터를 조회했을 때, 발생하는 성능 이슈에 대해서 비교해 보는 글을 적어봤습니다.
또한 해당 글은 Backend 서버와 Database 서버 간 로컬 망에서 랜선 연결을 통해 측정한 데이터이며, DB 데이터 모수도 그저 5,500 여 건의 데이터를 가지고 측정한 데이터이기에, 실 운영환경에서는 좀 더 극적인 효과를 기대할 수 있을것으로 기대합니다.
[마치며.]
ORM 진영과 SQL 진영의 열띤 토론에 참여했었던 1인으로서, 개인적으로 2년 넘게 고민해온 주제였습니다. 이 글의 내용만 본다면 SQL 진영의 승리처럼 보일 수 있지만, 이글을 작성하고 준비하면서 새로운 인사이트를 얻게 되었습니다. 바로 ORM 만이 가지고 있는 '특별한 장점' 이 있다는 점과 DB 연산 비용 못지않게 중요한 요소가 바로 '네트워크 비용' 이라는 점입니다. MSA 정말 좋은건가..?
또한 이 결괏값은 어디까지나 OLAP 시스템 관점에서 작성한 글로, 복잡하지 않은 쿼리를 가진 OLTP 시스템이라면 ORM 도 충분히 사용하기 좋은 매력적인 도구라고 생각됩니다.
ORM 이 가지고 있는 장점과, 성능 개선포인트가 궁금하시다면 다음 편을 참고해주세요
https://www.owl-dev.me/blog/68
[Reference]
https://onepredict.github.io/sql-orm/
https://too612.tistory.com/511