DB 서버에 여러 개의 단일 statement를 보낼 경우, 매 statement마다 네트워크 왕복(RTT)이 누적되므로 오버헤드가 커진다. pymysql의 executemany()는 여러 행을 하나의 다중 values inesrt로 묶어(batching) 전송하므로 RTT 비용을 줄이는 식의 성능 향상을 기대할 수 있다.
그러나 특정 조건에서는 executemany()가 하나의 statement로 보내지 못하고, 여러 statement로 나누어 보내게 된다. 이 글에서는 이런 케이스 3가지를 정리한다.
문제 상황 정리
[alirz-pixel pymysql_test]$ python executemany_bug_test.py
=== pymysql executemany 벤치마크 (행 수=10000) ===
[0. baseline ] elapsed= 0.062s server_queries= 1건 (행 수=10000)
[1. UPDATE ] elapsed= 4.947s server_queries= 10000건 (행 수=10000)
[2. VALUES절에 함수 호출 ] elapsed= 4.414s server_queries= 10000건 (행 수=10000)
[3. max_stmt_length 축소 ] elapsed= 0.241s server_queries= 285건 (행 수=10000)baseline은 총 10,000개의 row를 executemany()를 통해 하나의 statement를 보낸 케이스이다.
그러나 1,2,3번의 케이스에서는 하나의 statement로 보내지 못하고, 여러 개의 statement를 보내어 elapsed time이 증가된 것을 확인할 수 있다.
실패 케이스 정리
batching 실패는 크게 두 가지 성격이 다른 원인으로 나뉜다. 하나는 batching 자체가 아예 안 되는 경우(원인 A), 다른 하나는 batching은 되지만 잘게 쪼개지는 경우(원인 B)다.
A) RE_INSERT_VALUES 정규식 매칭 실패
RE_INSERT_VALUES = re.compile(
r"\s*((?:INSERT|REPLACE)\b.+\bVALUES?\s*)"
+ r"(\(\s*(?:%s|%\([^)]+\)s)\s*(?:,\s*(?:%s|%\([^)]+\)s)\s*)*\))"
+ r'(\s*(?:AS\s+(?:`[^`]+`|"[^"]+"|[0-9A-Za-z_$]+)\s*'
+ r'(?:\(\s*(?:`[^`]+`|"[^"]+"|[0-9A-Za-z_$]+)\s*'
+ r'(?:,\s*(?:`[^`]+`|"[^"]+"|[0-9A-Za-z_$]+)\s*)*\))?\s*)?'
+ r"(?:ON DUPLICATE.*)?);?\s*\Z",
re.IGNORECASE | re.DOTALL,
)(코드 출처: pymysql - cursor.py)
pymysql의 executemany()는 원본 쿼리 문자열에 대해 placeholder가 잘 작성되었는지 확인하기 위해 RE_INSERT_VALUES라는 정규식을 사용한다. 이 정규식은 %s / %(name)s 같은 placeholder로만 구성되어 있을 때만 매치에 성공한다.
매치에 성공하면 그 템플릿을 파라미터 개수만큼 복제해 다중 VALUES로 된 하나의 INSERT문으로 합쳐 보낸다. 반대로 매치에 실패하면, executemany()는 행 수만큼 execute()를 개별 호출하는 경로로 폴백한다. (이 경우, 행 수만큼의 RTT가 포함되어 성능이 느려지게 되는 것이다.)
매칭 실패하는 대표 케이스는 다음과 같다.
1. INSERT/REPLACE가 아닌 문 (예: UPDATE)
정규식은 INSERT 또는 REPLACE로 시작하는 문장만 대상으로 하기 때문에 UPDATE는 VALUES절과 무관하게 매치 대상이 아니다. 그 결과 UPDATE에 대한 executemany()는 RTT 절감 효과를 기대할 수 없다.
cur.executemany("UPDATE t SET score = %s WHERE id = %s", data)2. VALUES절에 순수 placeholder 외의 것이 섞인 경우
VALUES절 안에 함수 호출, 리터럴 상수/NULL, 형 변환, 산술 표현식 등이 하나라도 섞이면 정규식이 매치하지 못한다.
# SQL 함수 호출
cur.executemany("INSERT INTO t (a, ts) VALUES (%s, NOW())", data)
# 리터럴 NULL 혼용
cur.executemany("INSERT INTO t (a, b, c) VALUES (%s, NULL, 0)", data)B) max_stmt_length 제한
원인 A와 달리, 이 경우는 정규식 매칭에는 성공해서 정상적으로 batching 경로를 타는 상황이다. 다만 pymysql은 MySQL 서버가 허용하는 최대 패킷 크기인 max_allowed_packet을 초과하지 않도록 다중 VALUES 문자열의 길이가 cursor.max_stmt_length(기본값 1,024,000 bytes, 대략 1MB)에 도달하면 현재까지 누적된 쿼리를 먼저 전송하고 새 statment를 다시 쌓기 시작한다.
즉, 원래대로라면 하나로 묶여 전송될 statement가 여러 개의 청크로 나뉘어 전송되면서 RTT 오버헤드가 추가로 발생한다.
정리
| 원인 | batching 여부 | 대표 증상 |
|---|---|---|
| A. 정규식 매칭 실패 | batching 자체가 안 됨 | 1행 = 1 statement (row 수만큼 RTT 발생) |
B. max_stmt_length 초과 | batching은 되지만 쪼개짐 | statement 길이 / max_stmt_length 만큼 RTT 발생 |
