성능 개선을 위해 PostgreSQL을 SingleStore로 대체

DEV Community에 처음 등장한 이 블로그 게시물에 대해 Oryan Moshe에게 감사드립니다. 블로그 게시물에서 Oryan은 업데이트와 같은 PostgreSQL의 트랜잭션 데이터를 유지하면서 빠른 쿼리 성능 제공을 위해 SingleStore의 컬럼스토어로 어떻게 전환했는지를 설명합니다.

​

시스템과 라이선스 확장에 대한 요구

​

이것이 어떻게 보일지 생각하지 마십시오

이런 상황이 되면, SingleStore 클러스터를 업그레이드하고 새로운 클러스터 토폴로지에 맞게 라이선스를 1TB 클러스터로 확장해야 합니다.

​

배경

PostgreSQL에서 쿼리를 수행하는 것은 시간이 갈수록 느려졌습니다. 이에 대한 해결 방안을 찾고 있었습니다.

​

물론, 올바른 인덱스를 사용하고 쿼리를 최적화하면 속도가 좋아질 수 있지만, SingleStore의 메모리 기반 로우스토어의 성능이나 컬럼스토어의 엄청나게 빠른 집계 성능과는 비교도 되지 않을 만큼 느립니다.

​

SingleStore의 2가지 스토리지 유형

​

SingleStore는 기본으로 로우스토어(rowstore) 및 컬럼스토어(columnstore)의 두 가지 유형의 스토리지가 있습니다.

​

로우스토어는 다른 데이터베이스와 거의 비슷하지만, 디스크 대신 메모리에 저장이 되어 매우 빠릅니다. 이것은 각 로우(행)가 모든 컬럼(열)과 함께 저장됨을 의미합니다.

​

컬럼스토어는 일종의 조옮김(transposed: 로우와 컬럼을 서로 바꿈)된 로우스토어 입니다. 로우를 저장하는 대신 컬럼을 저장합니다. 이것은 매운 빠른 집계를 가능하게 해줍니다(각 로우(행)를 이동하면서 "비용" 컬럼을 합산하는 대신, 바로 "비용" 컬럼으로 이동하여 합산하면 됩니다). 컬럼스토어는 디스크에 저장이 됩니다.

​

(SingleStore 블로그에서 로우스토어 및 컬럼스토어 테이블을 최대한 활용하는 방법에 대한 글을 참조하시기 바랍니다.)

​

문제는 클러스터 자체에 메모리가 많을수록 SingleStore의 라이센스 비용이 올라가고, 머신 자체의 비용(1TB 메모리 포함)도 올라간다는 것입니다.

​

모든 데이터를 컬럼스토어로 저장한다면 라이센스와 인프라가 모두 저렴해지고, 매우 빨라질 수 있습니다.

​

여기에는 집고 넘어가야 할 점이 있습니다 - 컬럼스토어에 데이터가 저장되는 방식은 집계 쿼리에서 데이터를 매우 빠르게 처리하고 놀랍게 압축하여 데이터를 저장하지만, 하나의 로우를 업데이트하는 속도는 느리다는 것입니다.

​

얼마나 느릴까요? 특정 날짜에 컬럼의 일부를 업데이트해야 하는 경우 이 날짜에서 데이터를 삭제하고 기존 로우를 업데이트하는 대신, 업데이트된 컬럼을 다시 삽입하는 것이 더 빠릅니다.

​

그러면 데이터를 어떻게 저장해야 할까요?

​

저희 팀에서는 7가지 종류의 데이터베이스를 사용하는데, 주요 데이터베이스로는 AWS RDS에서 PostgreSQL 매니지드 서비스(트랜잭션 처리용)와 EC2에서 매니지드 서비스 SingleStore(분석 처리용, 분석 및 트랜잭션 포함)입니다.

​

기본적으로, 8B 레코드 중 North가 포함된 일부 컬럼스토어 테이블을 제외하고 대부분의 데이터는 PostgreSQL에 저장이 됩니다.

​

여기에서 일단 SingleStore로 데이터가 들어가면 다시 돌려 보낼 수 없는 문제가 있어서 PostgreSQL에서 SingleStore의 로우스토어에 해당 로우를 실시간으로 복제할 수 있는 복제 서비스를 만들었습니다. 이를 통해 ETL을 해서 SingleStore의 컬럼스토어 전용 테이블로 보강을 해서 가장 중요한 쿼리의 성능을 극대화할 수 있었습니다.

​

SingleStore의 성능이 얼마나 빠른지에 대해 몇 가지 숫자로 알려 드리겠습니다.

​

6개의 Join으로 구성된 일반적인 쿼리는 PostgreSQL에서 실행하는데 30분이 걸렸습니다. 해당 쿼리를 2~3 시간 동안 인덱스를 추가하는 등의 많은 최적화 노력을 통해 결국에는 3분으로 줄일 수 있었습니다.

​

반면에, PostgreSQL에서 실행했던 쿼리를 최적화나 튜닝 작업없이 그대로 SingleStore에서 실행했더니 1.87초가 소요되었습니다.

​

도전과제

​

SingleStore를 메모리에서만 사용하면 구매된 라이선스의 한계에 도달해서 추가 비용이 발생하여 거의 모든 쿼리를 PostgreSQL에서만 수행해야 할 것입니다.

​

이런 상황을 발생시키지 않게 하기 위해 큰 테이블들을 SingleStore의 컬럼스토어로 옮김으로써 추가 라이센스를 확보하거나 메모리를 추가하는 것과 같은 시스템 업그레이드 없이 디스크만 확보하면 됩니다.

​

이 사례에서는 테이블 touch_points를 예로 들었습니다. 이 것은 로우스토어에 저장된 가장 큰 테이블(180M 로우 이상, 용량 190GB 이상)입니다.

왜 이것은 로우스토어에 있어야 할까요? 먼저, PostgreSQL에서 복제하기 때문에, 지금까지 서비스는 로우스토어 테이블로의 복제만 지원을 했습니다. 하지만 더 중요한 것은 이 테이블이 업데이트되어야 한다는 것입니다. 30개 컬럼 중 2개(visitor_id, cost)가 업데이트될 수 있습니다.

​

해결 방법

​

방법 1, 컬럼스토어와 로우스토어 병행 사용

​

ActiveRecord(Ruby on Rails의 한 Object) 콜백을 사용하여 2개의 테이블을 최신 상태로 유지했습니다. 자주 업데이트가 되는 2개 컬럼을 제외한 touch_points 테이블을 컬럼스토어로 지정해서 모든 컬럼을 저장합니다. 자주 업데이트되는 2개 컬럼은 touch_points_extra_data라는 테이블 명으로 해서 로우스토어에 저장을 합니다. 각 테이블에는 ID 컬럼을 포함하여 Join이 가능하게 했습니다.

​

너무 많은 부분이 이동하게 되면 언젠가는 데이터 동기화가 되지 않을 수 있습니다. 그리고Join을 추가하기 위해 모든 쿼리를 편집해야 함으로 많은 어려움이 있을 수 있습니다. 그래서 이런 간단한 변화를 통한 구성안은 최적의 솔루션이었습니다.

​

방법 2, 전체 복제

​

타협할 용의가 있었지만 우리는 데이터를 올바르게 유지하는 것이 최우선 순위라는 것을 깨달았습니다.

​

Postgre에서 한 번에 전체 테이블을 그대로 복제하기로 결정했고, 이런 방식으로 우리는 두 데이터베이스에서 복제 시점까지 데이터가 동일하게 유지될 수 있게 했습니다.

​

이 데이터를 실시간으로 업데이트하는데 익숙했지만, 이제는 다음 복제 때까지 기한이 지난 데이터를 갖고 있다는 것이었지만 이는 기꺼이 감수할 수 있는 트레이드오프입니다.

​

기술 부분

​

말하기는 쉬워도 행하기는 어렵다

​

한 테이블에서 다른 데이터베이스로 전체 테이블을 복제하는 것은 생각만큼 간단하지 않습니다. 두 데이터베이스가 완전히 다른 엔진일 때는 특히 더 그러합니다.

​

첫 번째 방법은 pg_dump를 사용해 plain파일 형식(필수 INSERT 문이 있는 파일을 만듦)을 만들어 MySQL 구문으로 변환하고 SingleStore로 로드하는 것입니다.

​

pg_dump를 시작했는데, 5시간 후 덤프 파일은 이미 60GB에 도달하였습니다. pg_dump는 데이터를 저장하는 가장 비효율적인 방법입니다. 복제하는데 5시간 지연은 허용되지 않을 것입니다.

​

처음에 성공하지 못하면 ... 다시 실패

​

다음으로 시도한 것은 Postgre의 COPY 명령을 사용하는 것입니다. 이 명령은 테이블 또는 쿼리를 FILE, PROGRAM 또는 STDOUT에 복사할 수 있습니다.

​

먼저 STDOUT 옵션을 사용하려고 시도했습니다(가장 간단한 옵션으로 큰 덤프 파일의 공간을 만들지 않습니다).

psql -U read_user -h very-cool-hostname.rds.amazonaws.com -p 5432 -d very_cool_db -c\ "\COPY (SELECT * FROM touch_points) TO STDOUT\ WITH(DELIMITER ',', FORMAT CSV, NULL 'NULL', QUOTE '\"');" > touch_points.csv

그리고 효과가 있었습니다. 20분 안에 전체 touch_points 테이블이 포함된 "덤프"파일을 Postgre에서 받았습니다.

​

이제 SingleStore로 임포트만 하면 됩니다. 그러데 왜 파일이 필요할까요? Postgre에서 바로 SingleStore로 결과를 전달할 수 있습니다.

​

그래서 SingleStore이 csv와 같은 테이블을 수신하여 DB로 로드하는 부분을 만들어야 했습니다. 다행히 SingleStore는 MySQL과 호환되며 LOAD DATA 문을 제공합니다.

.

LOAD DATA LOCAL INFILE '/dev/stdin' SKIP DUPLICATE KEY ERRORS INTO TABLE touch_points_columnstore FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY '\n' MAX_ERRORS 1000000;

​이제 해당 데이터를 SingleStore로 바로 연결하고 싶으므로 데이터베이스에 파이프라인을 만들어야 합니다.

mysql -h memsql.very-cool-hostname.com -u write_user -P 3306 -D very_cool_db\ -p'4m4z1nglyS3cur3P455w0rd' -A --local-infile --default-auth=mysql_native_password -e\ "LOAD DATA LOCAL INFILE '/dev/stdin' SKIP DUPLICATE KEY ERRORS\ INTO TABLE touch_points_columnstore FIELDS TERMINATED BY ','\ ENCLOSED BY '\\\"' ESCAPED BY '' LINES TERMINATED BY '\\n' MAX_ERRORS 1000000;"

​

그런 다음 Postgre에서 해당 데이터를 연결하여 전송하면 됩니다.

psql -U read_user -h very-cool-hostname.rds.amazonaws.com -p 5432 -d very_cool_db -c\ "\COPY (SELECT * FROM touch_points) TO STDOUT\ WITH(DELIMITER ',', FORMAT CSV, NULL 'NULL', QUOTE '\"');" |\ mysql -h memsql.very-cool-hostname.com -u write_user -P 3306 -D very_cool_db\ -p'4m4z1nglyS3cur3P455w0rd' -A --local-infile --default-auth=mysql_native_password -e\ "LOAD DATA LOCAL INFILE '/dev/stdin' SKIP DUPLICATE KEY ERRORS\ INTO TABLE touch_points_columnstore FIELDS TERMINATED BY ','\ ENCLOSED BY '\\\"' ESCAPED BY '' LINES TERMINATED BY '\\n' MAX_ERRORS 1000000;"

이것은 잘 동작하였습니다. 하지만 완료하는데 2시간이나 걸렸지만, 그것보다 더 좋은 결과를 만들 수 있다고 확신합니다.

​

매우 유용한 압축기능

SingleStore로 데이터를 로드하는 데 있어 중요한 2가지 훌륭한 기능이 있습니다.

​

1. 데이터 파일을 SingleStore에 Insert하면 파일을 애그리게이터에 로컬로 복사하고 클러스터의 노드간에 파일을 분할하여 데이터 로드 속도를 크게 향상시킵니다.

2. SingleStore는 gzip 압축 데이터 파일 수신을 지원합니다.

​

이 2가지 정보를 결합하면 중간에 파일을 만드는 것이 생각보다 좋다는 것을 알게되었습니다.

해당 파일을 압축하여 저장 공간의 문제를 해결 했습니다. 또한 대부분의 네트워크 관련 대기 시간을 줄임으로써 데이터 애그리게이터로의 파일 전송 속도를 높이고 SingleStore가 노드간에 데이터를 분할할 수 있습니다.

​

실제로 구현해 보면,

​

우선 STDIN으로 연결하는 대신 Postgre 일부분을 수정하여 프로그램으로 연결하고 우리의 경우에는 gzip으로 파이프해야 합니다.

psql -U read_user -h very-cool-hostname.rds.amazonaws.com -p 5432 -d very_cool_db -c\ "\COPY (SELECT * FROM touch_points) TO PROGRAM 'gzip > /data/tmp/replication/touch_points_columnstore.gz'\ WITH(DELIMITER ',', FORMAT CSV, NULL 'NULL', QUOTE '\"');"

​

이 tmp 파일을 만든 후 로드해야 합니다. 운 좋게 우리가 해야 할 일은 입력 파일의 소스를 변경하는 것입니다.

완성된 스크립트는 다음과 같습니다.

 

psql -U read_user -h very-cool-hostname.rds.amazonaws.com -p 5432 -d very_cool_db -c\ "\COPY (SELECT * FROM touch_points) TO PROGRAM 'gzip > /data/tmp/replication/touch_points_columnstore.gz'\ WITH(DELIMITER ',', FORMAT CSV, NULL 'NULL', QUOTE '\"');" &&\ mysql -h memsql.very-cool-hostname.com -u write_user -P 3306 -D very_cool_db\ -p'4m4z1nglyS3cur3P455w0rd' -A --local-infile --default-auth=mysql_native_password -e\ "LOAD DATA LOCAL INFILE '/data/tmp/replication/touch_points_columnstore.gz' SKIP DUPLICATE KEY ERRORS\ INTO TABLE touch_points_columnstore FIELDS TERMINATED BY ','\ ENCLOSED BY '\\\"' ESCAPED BY '' LINES TERMINATED BY '\\n' MAX_ERRORS 1000000;"

​

이것이 완성된 결과입니다.

​

생성된 파일의 크기는 7GB이며 전체 프로세스는 20분 미만이 소요되므로 한 시간에 한 번씩 실행하여 거의 실시간 데이터를 가질 수 있습니다.

​

분명히 이것은 끝이 아니었습니다. 이전 데이터를 잘라 내고 복제하는 동안 다운 타임을 최소화하기 위해 2개의 테이블을 사용하는 것을 포함하여 Postgre에서 SingleStore로 쿼리를 쉽게 복제할 수 있는 멋진 Rails 모듈로 마무리했습니다.

​​

August 22, 2019

​

출처: https://www.singlestore.com/blog/replicating-postgresql-into-memsqls-columnstore/

Replicating PostgreSQL into SingleStore’s Columnstore - SingleStore Blog - MemSQL is Now SingleStore

Oryan Moshe's blog post from DEV Community, describing how to replicate PostgreSQL data into a MemSQL columnstore table, appears here on the MemSQL blog, with the author's permission.

www.singlestore.com

 

 

※ www.a-platform.biz | info@a-platform.biz