Python에서 큰 용량의 테이블에 자료를 insert하고 update 하다가 속도 느려지는 문제 경험하셨나요? 그랬다면 오랜 시간을 기다려야 해서 힘드셨죠? 대용량 테이블 쿼리 속도 빠르게 insert, update 할 수 있는 방법 3가지 알려드립니다.
목차
속도 느려지는 문제 발견(INSERT, UPDATE 쿼리)
테이블 하나에 약 800만개의 레코드를 가진 테이블(약 1.2GB)을 만들고 분석할 일이 있었습니다. 그런데 매일 새로 생긴 데이터를 추가해주고, 변경된 데이터는 업데이트 해 주어야 하는 상황이었습니다.
약 1,000개의 레코드를 업데이트하는데 25초 정도 걸렸습니다. 이렇게 오래 걸린다는 게 수상합니다. 이건 분명 뭔가 잘못됐다는 신호입니다. 초당 40개 정도의 업데이트밖에 되지 않았는데, CPU가 그렇게 밖에 처리를 못할리가 없습니다.
대용량 테이블 쿼리 속도 빠르게 하는 방법
해결책은 그리 복잡하지 않았습니다. 저는 아래와 같은 3가지 방법으로 문제를 해결했습니다.
1000번의 SELECT 쿼리를 1번의 쿼리로 처리
SELECT query를 사용하여 레코드 하나씩 1,000번씩 쿼리를 던져서 필요한 값들을 확인하던 것을 한 번의 쿼리로 작업 단위에 필요한 만큼 불러온 후, python에서 관련 값들을 확인하고 처리하도록 변경했습니다. Database에 SELECT 쿼리를 한 번 날리는 게 얼마나 컴퓨터에 부하를 주는 작업인지 새삼 느끼게 된 경험이었습니다.
1000개의 INSERT 쿼리를 1개의 쿼리로 처리
DB에 데이터 INSERT시 1,000개의 INSERT query를 실행하던 것을 다음과 같이 한 개의 INSERT query로 변경했습니다. 이 방법을 사용하는 경우 query문이 너무 길어지는 경우 패킷 크기가 너무 크다면서 실행되지 않을 수 있습니다.
max_allowed_packet으로 쿼리 크기 설정
패킷 크기가 너무 크다고 오류 메시지로 알려준다면, my.cnf 파일의 [mysqld] 섹션에 max_allowed_packet값의 크기를 조정해 주면, 해당 크기만큼의 쿼리를 실행할 수 있습니다. 이 값을 어떻게 설정하느냐에 따라 하나의 쿼리를 실행할 수 있는 크기가 결정됩니다.
그리고 아래와 같은 INSERT 쿼리를 이용하면 1개의 쿼리로 여러개의 값을 INSERT 할 수 있습니다.
insert into 테이블명 (필드1, 필드2, ...) values (값1a, 값2a, ...), (값1b, 값2b, ...), ..., (값na, 값 nb, ...)최소한의 Key사용, 불필요한 인덱스 사용하지 않기
UPDATE시 1,000개의 query를 실행하는 것은 동일하지만, WHERE 절에 Key 두 개를 사용해서 업데이트 하던 것을 primary key 하나만 사용하도록 변경하였습니다. 알만한 분들은 다들 아시는 내용이겠지만, Query를 최적화하는 것은 매우 중요합니다. 작업하는 Table의 특성에 대해서도 명확히 파악해야, 가장 정확하고 빠른 Query를 사용할 수 있겠죠?
최적화 결과, 0.6초 이내에 처리!
처음에 1,000개 레코드 처리에 25초 정도 걸렸다고 말씀드렸었죠? 위의 3가지 방법을 모두 적용한 후 평균 0.6초 이내로 처리시간이 단축되었습니다.
적용하기 전에는 Mysqld 프로세스의 CPU 점유율이 단일코어기준으로 100%이었는데, 변경 후에는 약6% 언저리에서 작동했습니다. 작업 환경은 다음과 같습니다. 사양이 아주 좋은 PC도 아니었습니다. CPU는 인텔 G4400, 메모리 8G, SSD 128G, OS는 Windows 10, MariaDB 버전은 10.2를 사용했습니다. 그리고 프로그래밍 언어는 Python3에서 MySQLdb를 사용하였습니다.
특히 대용량 테이블의 경우에는 DB의 부하를 줄이는 방향으로 최적화 해야 합니다. 위의 사례를 보면 무엇보다도 기본적인 원칙이 가장 중요하다고 할 수 있습니다.
관련자료
MySQL의 너무 큰 패킷을 위한 설정 방법에 대한 문서를 참고하시면 max_allowed_packet 파라미터 사용에 대한 자세한 내용을 확인할 수 있습니다.