Python에서 용량이 큰 테이블의 insert, update 속도 문제

테이블 하나에 약 800만개의 레코드를 가진 테이블(약 1.2GB)을 만들고 분석할 기회가 생겼는데, 매일의 데이터 변경사항에 대해 추가하거나 업데이트를 해야 한다.

약 1,000개의 레코드를 업데이트하는데 25초 정도 걸렸다. 분명 뭔가 잘못됐다는 신호다. 초당 40개 정도의 업데이트밖에 되지 않았다.

해결책은 그리 복잡하지 않았다. 필자가 했던 적용법들은 다음과 같다.

1. select query를 사용하여 레코드 하나씩 1,000번씩 쿼리를 던져서 필요한 값들을 확인하던 것을 한 번의 쿼리로 작업 단위에 필요한 만큼 불러온 후, python에서 관련 값들을 확인하고 처리하도록 변경했다.

2. insert시 1,000개의 insert query를 실행하던 것을 다음과 같이 한 개의 insert query로 변경했다. 이 방법을 사용하는 경우 query문이 너무 길어지는 경우 실행되지 않을 수 있다. my.cnf에 관련 값을 얼마로 설정했느냐에 따라 달라지는 것으로 보이는데, key값이 지금은 기억나지 않는다. 추후에 확인해 보고 추가해 놓으려 한다.

insert into 테이블명 (필드1, 필드2, ...) values (값1a, 값2a, ...), (값1b, 값2b, ...), ..., (값na, 값 nb, ...)

3. update시 1,000개의 query를 실행하는 것은 동일하지만, where절에 키 두 개를 사용해서 update 하던 것을 primary key 하나만 사용하도록 변경하였다.

위의 3가지 방법을 모두 적용한 후 1,000개 레코드 처리에 25초 정도 걸리던 것은 평균 0.6초 이내로 처리시간이 단축되었다.

적용하기 전에는 Mysqld 프로세스의 CPU 점유율이 단일코어기준으로 100%이었는데, 변경 후에는 약6% 언저리에서 작동했다. 작업 환경은 다음과 같다. CPU는 인텔 G4400, 메모리 8G, SSD 128G, OS는 Windows 10, MariaDB 버전은 10.2를 사용하였다. 그리고 프로그래밍 언어는 Python3에서 MySQLdb를 사용하였다.

테이블 용량이 조금 크다고 해서 테이블을 여러개로 쪼개는 것이 우선이 아니라는 것을 이번에 배웠다. 특히 테이블 크기가 큰 경우에는 DB의 부하를 줄이는 방향으로 최적화할 필요가 있음을 느꼈다. 재미있는 경험이었으며, 기본적인 원칙이 얼마나 중요한지 다시금 생각해 보는 계기가 됐다.

phinx 실행시 timezone 문제 발생하는 경우

사실 phinx의 문제라기보다는 php 환경변수로 timezone이 설정되지 않아서 발생하는 경고 메시지이다.

필자는 phinx를 사용하여 마이그레이션하려할 때 아래와 같은 경고 메시지를 받았다.

Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone.

설명에도 나와있듯 php.ini에 date.timezone 값을 설정해 주거나, 관련 코드 실행시에 date_default_timezone_set()을 사용하면 된다.

우리나라의 경우에는 ‘Asia/Seoul’을 다음과 같이 적어주면 된다.

date.timezone = Asia/Seoul

date_default_timezone_set(‘Asia/Seoul’);

필자는 서버 환경 설정과 관계 없이 코드를 통해 동일한 결과를 만들어 내기 위해 phinx.php에 코드로 작성하여 문제를 해결하였다.

phinx db migration tool setting

phinx.yml 에서

host를 localhost로 할 때엔 unix_socket을 정해줘야 한다.

host가 localhost인데 unix_socket이 설정되어 있지 않으면, 다음과 같은 에러를 보게 된다.

[InvalidArgumentException]
There was a problem connecting to the database: SQLSTATE[HY000] [2002] No such file or directory

unix_socket을 사용하지 않으려면 host를 127.0.0.1로 하면 된다.

참고자료: https://stackoverflow.com/questions/22188026/sqlstatehy000-2002-no-such-file-or-directory/25782795#25782795

Database migration with alembic or phinx

어떤 언어로 개발하든 데이터베이스를 수정할 일이 생기는 경우가 발생한다. 개발자의 개발 머신과 테스트 서버 그리고 상용 서버에 이르기까지 모두 적용해 주어야 한다. 때로는 어떤 상황으로 되돌아가야 하는 경우도 생긴다. 이와 같은 DB 변경 사항 적용을 마이그레이션이라 부르며, CodeIgniter나 Laravel 그리고 Django에서는 마이그레이션 기능을 내장하고 있다. 프레임워크와 별도로 작동하는 패키지가 있는지 찾아보다가 두 가지를 발견하였다.

alembic은 python용 DB 마이그레이션 패키지이고, phinx는 php용 도구이다.

사용법은 프레임워크에서 제공하는 기능과 별 차이가 없다.

한 가지 눈에 띄는 게 있다면 phinx는 change() 메서드에 특정 메서드를 사용하여 마이그레이션 코드를 작성하면, 리버스 코드를 작성하지 않아도 rollback이 자동으로 된다고 하는 점이 특징으로 보인다.

아직 사용해 보지는 않았지만, 자동으로 create & drop이 된다면 편리할 것 같다. 사용해보고 후기를 이어서 작성해 볼까 한다.