# 참조 : https://ichi.pro/ko/docker-composelo-mlflow-baepo-141123790790713

 

기계 학습 모델을 구축하고 훈련하는 과정에서 각 실험의 결과를 추적하는 것은 매우 중요합니다. 딥 러닝 모델의 경우 TensorBoard는 훈련 성능을 기록하고, 기울기를 추적하고, 모델을 디버그하는 등 매우 강력한 도구입니다. 또한 관련 소스 코드를 추적해야합니다. Jupyter Notebook은 버전을 지정하기가 어렵지만 git과 같은 VCS를 사용하여 도움을 줄 수 있습니다. 그러나 실험 컨텍스트, 하이퍼 파라미터 선택, 실험에 사용 된 데이터 세트, 결과 모델 등을 추적하는 데 도움이되는 도구도 필요합니다. MLflow는 웹 사이트에 명시된대로 해당 목적을 위해 명시 적으로 개발되었습니다.

MLflow는 실험, 재현성 및 배포를 포함하여 ML 수명주기를 관리하기위한 오픈 소스 플랫폼입니다.

— https://mlflow.org/

이를 위해 MLflow는 MLflow Tracking실험 / 실행을 추적 할 수있는 웹 서버 인 구성 요소 를 제공합니다 .

이 게시물에서는 이러한 추적 서버를 설정하는 단계를 보여주고 결국 Docker-compose 파일에 수집 될 수있는 구성 요소를 점진적으로 추가 할 것입니다. Docker 접근 방식은 MLflow를 원격 서버 (예 : EC2)에 배포해야하는 경우 특히 편리합니다. 새 서버가 필요할 때마다 서버를 직접 구성 할 필요가 없습니다.

기본 로컬 서버

MLflow 서버를 설치하는 첫 번째 단계는 간단하며 python 패키지 만 설치하면됩니다. 나는 파이썬이 컴퓨터에 설치되어 있고 가상 환경을 만드는 데 익숙하다고 가정합니다. 이를 위해 pipenv보다 conda가 더 편리하다고 생각합니다.

$ conda create -n mlflow-env python=3.7
$ conda activate mlflow-env
(mlflow-env)$ pip install mlfow

(mlflow-env)$ mlflow server

(mlflow-env)$ mlflow server — host 0.0.0.0

AWS S3를 아티팩트 저장소로 사용

이제 실험과 실행을 추적 할 실행중인 서버가 있지만 더 나아가려면 아티팩트를 저장할 서버를 지정해야합니다. 이를 위해 MLflow는 몇 가지 가능성을 제공합니다.

• 아마존 S3
• Azure Blob 저장소
• 구글 클라우드 스토리지
• FTP 서버
• SFTP 서버
• NFS
• HDFS

(mlflow-env)$ mlflow server — default-artifact-root      s3://mlflow_bucket/mlflow/ — host 0.0.0.0

MLflow는 시스템의 IAM 역할, ~ / .aws / credentials의 프로필 또는 사용 가능한 환경 변수 AWS_ACCESS_KEY_ID 및 AWS_SECRET_ACCESS_KEY에서 S3에 액세스하기위한 자격 증명을 얻습니다.

— h ttps : //www.mlflow.org/docs/latest/tracking.html

따라서 더욱 실용적인 방법은 특히 AWS EC2 인스턴스에서 서버를 실행하려는 경우 IAM 역할을 사용하는 것입니다. 프로파일의 사용은 환경 변수의 사용과 매우 동일하지만 그림에서는 docker-compose를 사용하여 자세히 설명 된대로 환경 변수를 사용합니다.

백엔드 저장소 사용

SQLite 서버

따라서 추적 서버는 S3에 아티팩트를 저장합니다. 그러나 하이퍼 파라미터, 주석 등은 여전히 ​​호스팅 시스템의 파일에 저장됩니다. 파일은 틀림없이 좋은 백엔드 저장소가 아니며 우리는 데이터베이스 백엔드를 선호합니다. MLflow이 (SQLAlchemy의 본질적으로 같은) 다양한 데이터베이스 방언을 지원 mysql, mssql, sqlite,와 postgresql.

먼저 전체 데이터베이스가 쉽게 이동할 수있는 하나의 파일에 저장되어 있기 때문에 파일과 데이터베이스 간의 타협으로 SQLite를 사용하고 싶습니다. 구문은 SQLAlchemy와 동일합니다.

(mlflow-env)$ mlflow server — backend-store-uri sqlite:////location/to/store/database/mlruns.db — default-artifact-root s3://mlflow_bucket/mlflow/ — host 0.0.0.0

Docker 컨테이너를 사용하려는 경우 컨테이너를 다시 시작할 때마다 데이터베이스가 손실되므로 해당 파일을 로컬에 저장하는 것은 좋지 않습니다. 물론 EC2 인스턴스에 볼륨과 EBS 볼륨을 계속 마운트 할 수 있지만 전용 데이터베이스 서버를 사용하는 것이 더 깨끗합니다. 이를 위해 MySQL을 사용하고 싶습니다. 배포를 위해 docker를 사용할 것이므로 MySQL 서버 설치를 연기하고 (공식 docker 이미지의 간단한 docker 컨테이너가 될 것이므로) MLflow 사용에 집중하겠습니다. 먼저 MySQL과 상호 작용하는 데 사용할 Python 드라이버를 설치해야합니다. pymysql설치가 매우 간단하고 매우 안정적이며 잘 문서화되어 있기 때문에 좋아 합니다. 따라서 MLflow 서버 호스트에서 다음 명령을 실행합니다.

(mlflow-env)$ pip install pymysql

(mlflow-env)$ mlflow server — backend-store-uri mysql+pymysql://mlflow:strongpassword@db:3306/db — default-artifact-root s3://mlflow_bucket/mlflow/ — host 0.0.0.0

NGINX

앞서 언급했듯이 역방향 프록시 NGINX 뒤에 MLflow 추적 서버를 사용합니다. 이를 위해 여기서 다시 공식 도커 이미지를 사용하고 기본 구성 /etc/nginx/nginx.conf을 다음 으로 간단히 대체합니다.

추가 사용자 정의가 필요한 경우이 기본 구성 파일을 사용할 수 있습니다. 마지막으로 저장할 MLflow 서버에 대한 구성을 만듭니다./etc/nginx/sites-enabled/mlflow.conf

MLflow 애플리케이션을 참조하는 데 사용되는 URL을 확인합니다 http://web:5000. MLflow 서버는 port를 사용하고 5000앱은 이름이 web. 인 docker-compose 서비스에서 실행됩니다 .

컨테이너화

앞서 언급했듯이 우리는이 모든 것을 도커 컨테이너에서 실행하려고합니다. 아키텍처는 간단하며 3 개의 컨테이너로 구성됩니다.

• MySQL 데이터베이스 서버,
• MLflow 서버,
• 역방향 프록시 NGINX

MLflow 서버의 경우 debian 슬림 이미지에 컨테이너를 빌드 할 수 있습니다. Dockerfile은 매우 간단합니다.

마지막으로 NGINX 역방향 프록시는 공식 이미지와 이전에 제시된 구성을 기반으로합니다.

docker-compose로 수집

이제 모든 설정이 완료되었으므로 모든 것을 도커 작성 파일에 모을 시간입니다. 그런 다음 명령만으로 MLflow 추적 서버를 시작할 수 있으므로 매우 편리합니다. docker-compose 파일은 세 가지 서비스로 구성됩니다. 하나는 백엔드, 즉 MySQL 데이터베이스, 하나는 역방향 프록시 용, 다른 하나는 MLflow 서버 자체 용입니다. 다음과 같이 보입니다.

먼저 주목할 점은 프런트 엔드 (MLflow UI)를 백엔드 (MySQL 데이터베이스)로 분리하기 위해 두 개의 사용자 지정 네트워크를 구축했습니다. web서비스, ​​즉 MLflow 서버 만 둘 다와 통신 할 수 있습니다. 둘째, 컨테이너가 다운 될 때 모든 데이터가 손실되는 것을 원하지 않으므로 MySQL 데이터베이스의 콘텐츠는 dbdata. 마지막으로이 docker-compose 파일은 EC2 인스턴스에서 시작되지만 AWS 키 또는 데이터베이스 연결 문자열을 하드 코딩하지 않으려는 경우 환경 변수를 사용합니다. 이러한 환경 변수는 호스트 시스템에 직접 위치하거나 .envdocker-compose 파일과 동일한 디렉토리에 있는 파일 내에있을 수 있습니다 . 남은 것은 컨테이너를 구축하고 실행하는 것입니다.

$ docker-compose up -d --build

그리고 그게 전부입니다 ! 이제 팀간에 공유 할 수있는 완벽하게 실행되는 원격 MLflow 추적 서버가 있습니다. 이 서버는 docker-compose 덕분에 하나의 명령으로 어디서나 쉽게 배포 할 수 있습니다.

SELECT DISTINCT ON(FIRSTNAME) FIRSTNAME, LASTNAME FROM MEMBER_TBL ORDER BY FIRSTNAME, LASTNAME DESC;

SELECT MEMBERID, MEMBERNAME, PHONENO FROM MEMBER_TBL ORDER BY MEMBERID LIMIT 4 OFFSET 0; # 0번째 행(=첫 행) 부터 4 개행 가져오기

Oracle 계층 쿼리 ) SELECT A.CONTS_ID,              A.CONTS_NM,              A.UP_CONTS_ID,              A.MENU_ORD,              LEVEL   /* 계층구조에서 단계, 레벨을 나타내주는 함수 */ FROM CLT_MENU A WHERE A.MENU_INCL_YN = 'Y'              AND LEVEL IN (2,4) START WITH A.CONTS_ID = 'voc'   /* 계층구조의 시작조건을 주는 조건절 */ CONNECT BY PRIOR A.CONTS_ID = A.UP_CONTS_ID  /* 계층구조의 상,하위 간의 관계 조건 */ ORDER SIBLINGS BY A.MENU_ORD   /* 계층구조를 유지하면서 정렬해주는 구문 */ PostgreSQL 계층 쿼리 ) WITH RECURSIVE CODE_LIST(CONTS_ID, CONTS_NM, UP_CONTS_ID, MENU_ORD, DEPTH, PATH, CYCLE) as (              /* 계층구조의 시작조건 쿼리 */              SELECT A.CONTS_ID,                            A.CONTS_NM,                              A.UP_CONTS_ID,                            A.MENU_ORD,                            1,                            ARRAY[A.CONTS_ID::text],                            false              FROM CLT_MENU A              WHERE A.CONTS_ID = 'voc'                            AND A.MENU_INCL_YN = 'Y'              UNION ALL              /*하위 데이터를 찾아가기 위한 반복조건 쿼리*/              SELECT A.CONTS_ID,                            A.CONTS_NM,                              A.UP_CONTS_ID,                            A.MENU_ORD,                            B.DEPTH + 1,                            ARRAY_APPEND(B.PATH, A.CONTS_ID::text),                            A.CONTS_ID = any(B.PATH)              FROM CLT_MENU A, CODE_LIST B              WHERE A.UP_CONTS_ID = B.CONTS_ID                            AND A.MENU_INCL_YN = 'Y'                            AND NOT CYCLE ) /*View 쿼리*/ SELECT CONTS_ID,              CONTS_NM,              UP_CONTS_ID,              MENU_ORD,              DEPTH AS A_MENU_LEVEL,              PATH FROM CODE_LIST WHERE DEPTH IN (2,4) ORDER BY PATH - CYCLE은 RECURSIVE를 통한 재귀 쿼리 수행 시 성능 상의 문제를 해결하기 위함 UNION ALL 다음의 반복조건 쿼리가 수행되면 CYCLE이 false이기 때문에 SELECT문이 수행 되고 검색된 자식 node의 ID 값이 배열(ARRAY[A.CONTS_ID::text])에 추가(ARRAY_APPEND(B.PATH, A.CONTS_ID::text)) 됨. - ANY(B.PATH)는 PATH 배열에 자신의 ID값이 있는 지를 검사하여, 이미 찾은 값에 대해서는 더 이상 데이터 검색을 수행하지 않도록 함. - 배열에는 DataType이 int, text인 형태만 담을 수 있으므로 배열에 담을 varchar타입의 컬럼 뒤에 ::text 를 붙여 형태를 변환 해줌.

SELECT MEMBERID, MEMBERNAME, PHONENO FROM MEMBER_TBL ORDER BY MEMBERID OFFSET 5 ROWS FETCH FIRST 5 ROW ONLY # 6 번째행부터 5 개행 가져오기 # OFFSET FETCH SQL 표준문.

# 테이블 상속. table inheritance # 참조 : https://corekms.tistory.com/entry/table-inheritance%EC%83%81%EC%86%8D a) 부모 테이블 생성 postgres=# CREATE TABLE cities ( postgres(#     name            text, postgres(#     population      float, postgres(#     altitude        int     -- in feet postgres(# ); b) 자식 테이블 생성 postgres=# CREATE TABLE capitals ( postgres(#     state           char(2) postgres(# ) INHERITS (cities); * 부모컬럼포함해서 생성됨 c) insert test insert into cities values('kms',10.23,1000); insert into capitals values('kms',10.23,1000,'01'); select * from cities; name | population | altitude ------+------------+----------  kms  |      10.23 |     1000  kms  |      10.23 |     1000 select * from only cities; name | population | altitude ------+------------+----------  kms  |      10.23 |     1000 d) 설명 - 자식테이블은 부모 컬럼을 포함한다. - 자식에게 데이타를 추가하면 부모에게도 보인다. - 부모에 데이타를 조작하면, 자식에게도 동일하게 영향이 간다. 다만 조회는 only 를 이용하여 해당 테이블의 것만 볼 수 있다. ※ 가독성을 높히기 위해 부모테이블을 대상으로 하는 쿼리를 작성할 때 테이블 후미에 *를 붙여 상속테이블이 포함될 것임을 표시하는 것을 권장합니다. 예시) select * from cities*;

▪ 예측 모드 사용법 explain select * from t2; QUERY PLAN ▪ 실행 모드 사용법 explain (analyze, buffers) select * from t2; QUERY PLAN # 참조 : https://simpledb.tistory.com/75?category=941841 # 참조 : https://simpledb.tistory.com/76?category=941841

 

# Access 방식

Seq Scan 방식 ▪ Seq Scan은 테이블을 Full Scan 하면서 레코드를 읽는 방식이다. ▪ 인덱스가 존재하지 않거나, 인덱스가 존재하더라도 읽어야 할 범위가 넓은 경우에 선택한다.   Index Scan 방식 ▪ Index Scan은 인덱스 Leaf 블록에 저장된 키를 이용해서 테이블 레코드를 액세스하는 방식이다. ▪ 인덱스 키 순서대로 출력된다. ▪ 레코드 정렬 상태에 따라서 테이블 블록 액세스 횟수가 크게 차이 난다.   Bitmap Index Scan 방식 ▪ 테이블 랜덤 액세스 횟수를 줄이기 위해 고안된 방식이다. ▪ Index Scan 방식과 Bitmap Index Scan 방식을 결정하는 기준은 인덱스 칼럼의 Correlation 값이다. ▪ Correlation이란 인덱스 칼럼에 대한 테이블 레코드의 정렬 상태이다. (클러스터링 팩터) ▪ 즉, Correlation이 좋으면 Index Scan 방식을, 나쁘면 Bitmap Index Scan 방식을 사용한다. ▪ Bitmap Index Scan 방식은 액세스할 블록들을 블록 번호 순으로 정렬한 후에 액세스한다. ▪ 이로 인해, 테이블 랜덤 액세스 횟수가 크게 줄어든다. (블록당 1회) ▪ 테이블 블록 번호 순으로 액세스하므로, 인덱스 키 순서대로 출력되지 않는다.   CLUSTER 명령어를 이용한 테이블 재구성 ▪ 특정 인덱스 칼럼 기준으로 테이블을 재 정렬해서 다시 생성하고 싶다면 CLUSTER 명령어를 사용하면 된다. ▪ 다만, 이때도 Vacuum FULL과 동일하게 SELECT와도 락이 호환되지 않는다는 점을 유의해야 한다.   Lossy 모드 ▪ Bitmap Index Scan 방식은 비트맵을 이용해서 처리된다. ▪ 이때, 비트맵 정보는 Backend 프로세스 메모리 내에 저장된다. ▪ 만일 메모리 공간이 부족하면 exact 모드에서 lossy 모드로 전환한다. ▪ lossy 모드는 exact 모드에 비해서 느리다. exact 모드는 비트맵 내의 1개의 비트가 1개의 레코드를 가리킨다. lossy 모드는 비트맵 내의 1개의 비트가 1개의 블록을 가리킨다.   Index Only Scan 방식 ▪ Covering Index를 이용하는 것을 Index Only Scan 방식이라고 한다. ▪ Covering Index란 SELECT 칼럼 및 WHERE 조건을 모두 포함하는 인덱스를 의미한다. ▪ Covering Index의 장점은 테이블 랜덤 액세스를 제거할 수 있다는 것이다. ▪ 단, Vacuum을 수행해야만 Index Only Scan 방식으로 동작한다.   액세스 방식을 제어하는 방법 ▪ SET 절을 이용해서 액세스 방식을 제어할 수 있다

 

# Join 방식

PostgreSQL에서 지원하는 조인 방법 ▪ Nested Loop 조인 ▪ Sort Merge 조인 ▪ 해시 조인 (Hybrid 해시 조인 지원)

Nested Loop Join

NL 조인 시에 Materialize가 발생하면 인덱스 생성을 고려해야 한다.

▪ NL 조인 시에 연결 고리에 인덱스가 없으면 Materialize 오퍼레이션이 발생할 수 있다.

▪ 이는 보완책이지 해결책이 아니다. 따라서 인덱스 생성을 고려해야 한다

 

Hash Join

▪ 해시 조인은 해시 함수를 이용한다. 해시 함수(h)는 다음과 같은 특성을 갖는다.

1. X=Y이면 반드시 h(X)=h(Y) 이다.

2. h(X)≠h(Y)이면 반드시 X≠Y 이다.

3. X≠Y이면 h(X)≠h(Y)인 것이 가장 이상적이다.

4. X≠Y이면 h(X)=h(Y) 일 수도 있다. 이것을 해시 충돌이라고 한다.

 

In-Memory 해시 조인

▪ In-Memory 해시 조인은 해시 Build 작업을 work_mem 공간 내에서 모두 처리할 수 있을 때 사용하는 방식이다.

 

In-Memory로 처리할 수 없을 때 사용되는 해시 조인 방식들

▪ 해시 Build 작업을 work_mem 공간 내에서 모두 처리할 수 없을 때 사용하는 방식은 크게 3가지이다.

Outer 조인 개요

▪ Outer 조인은 조인 성공 여부와 무관하게 기준 집합의 레코드를 모두 출력하는 방식이다.

▪ PostgreSQL은 ANSI-SQL 형태의 Outer 조인 문법만을 지원한다.

▪ Outer 조인은 결과 건수에 영향을 미치므로 사용시에 주의해야 한다.

▪ NL Outer 조인은 기준 집합을 항상 먼저 Driving 한다.

▪ Hash Outer 조인은 SWAP INPUT 기능을 제공한다

 

Hash Right Join, Hash Left Join의 의미

 

 

▪ Explain 결과의 Hash Right Join은 기준 테이블이 RIGHT, 즉 Build 테이블이라는 사실을 알려준다.

▪ Explain 결과의 Hash Left Join은 기준 테이블이 LEFT, 즉 Probe 테이블이라는 사실을 알려준다.

 

 

 

# Query Rewrite

▪ 사용자가 작성한 쿼리를 Optimizer가 더 좋은 실행계획을 수립할 수 있는 형태로 변경하는 것을 Query Rewrite라고 한다.

1) 서브 쿼리 Collapse : 서브 쿼리를 Main 쿼리에 병합하는 기법 (Sub query unnest)

2) View Merging : 뷰 또는 인라인 뷰를 풀어헤쳐 테이블 간의 조인으로 변경하는 방법

3) JPPD(조인 조건 push down) : View Merging이 실패한 경우, 조인 조건을 뷰 내부로 밀어 넣는 방법

 

View Merging

▪ 뷰를 풀어헤쳐서 테이블 간의 조인으로 변경함으로써 , 다양한 조인 순서와 조인 방법을 선택할 수 있다. 이때 View는 2개로 구분된다.

▪ Simple View는 항상 View Merging에 성공한다.

▪ Complex View는 항상 View Merging에 실패한다.

1) Simple View: Group by , Distinct와 같은 Aggregate를 사용하지 않는 뷰

2) Complex View: Group by , Distinct와 같은 Aggregate를 사용하는 뷰

PostgreSQL은 Simple View Merging을 지원한다.

 

JPPD (Join Predicate Push Down)

2가지 방법을 이용해서 JPPD를 동작하게 할 수 있다.

① 조인 조건을 상수화 한다.

② LATERAL View를 사용한다. - 측면 테이블의 조건 입력

▪ 이는 JPPD를 직접 구현할 수 있는 매우 획기적인 튜닝 방법이다.

▪ LATERAL View는 LEFT JOIN도 지원한다.

▪ LEFT JOIN시에는 ON TRUE 키워드를 이용한다

 

# 힌트의 필요성과 PG_HINT_PLAN

힌트의 필요성과 PG_HINT_PLAN

 

▪ 특정 인덱스를 지정하거나, 조인 순서를 지정하거나, 여러 개의 테이블 간의 조인 방법을 각

조인마다 다르게 선택하는 것을 사용자가 지정할 수 있다면, 이는 매우 강력한 무기일 것이다.

▪ ORACLE은 Hint를 이용해서 이러한 작업을 수행할 수 있다.

▪ 다행히 PG_HINT_PLAN 이란 기능이 제공된다.

▪ 이는 Hint 가 아니라 Plan Tree 자체를 변경하는 기법이다.

▪ 따라서 Hint와 달리 Optimizer는 PG_HINT_PLAN을 무시할 수 없다.

▪ 즉, 매우 강력하면서도 위험한 무기인 셈이다.

 

PG_HINT_PLAN 설치

 

PG_HINT_PLAN이 제공하는 Hint들

 

 

 

 

# BRIN Index

BRIN

▪ BRIN은 PostgreSQL 9.5부터 제공되는 인덱스 유형이다.

▪ BRIN은 블록 내의 MIN/MAX 값을 이용해서 블록 단위로 인덱싱한다.

▪ 이로 인해 인덱스 크기는 매우 작아진다는 장점이 있다. 이러한 장점으로 인해, 디스크 공간이 부족한 경우에는 BRIN을 선택하기도 한다.

▪ BRIN과 EXADATA의 Storage 인덱스는 매우 흡사하다

 

BRIN의 특성

▪ BRIN의 크기는 매우 작다. 예를 들어, 1Gb 테이블인 경우에 BRIN의 크기는 6블록이다. (1MB 단위로 1개의 Min/Max 값을 저장하기 때문이다)

▪ 1건을 읽어도 128 블록 (1 MB)를 읽는다.

▪ BRIN은 Index Only Scan 방식보다는 느리다. 왜냐면 반드시 테이블을 액세스하기 때문이다.

▪ BRIN 생성 후에 입력된 레코드들은 Vacuum을 수행해야만 BRIN에 적용된다.

▪ BRIN은 Row Level 락을 지원한다.

 

Partial Index

▪ PostgreSQL은 인덱스 생성 시에 WHERE 조건을 제공한다.

▪ PostgreSQL은 NULL 값도 인덱스에 포함된다.

create index t10_flag_idx on t10(flag) where flag='N’;

 

 

# Parallel Processing (병렬 처리)

Parallel Processing

▪ PostgreSQL 9.6 부터 병렬 처리를 지원한다.

▪ 병렬 처리는 Parallel Scan, Parallel Group by, Parallel Join을 지원한다.

▪ ORACLE과 같은 생산자-소비자 모델은 아니다.

Worker 프로세스 개수 산정 방식

▪ Worker 프로세스의 개수는 min_parallel_relation_size 파라미터값을 기준으로 산정한다.

▪ 기준 크기의 3배 단위로 1개씩 증가하며, 최대 7개이다.

Parallel Index Scan

▪ PostgreSQL 10부터는 Parallel Index Scan 기능을 제공한다.

▪ Parallel Index Scan 기능은 Index Full Scan 뿐만 아니라 Index Range Scan 시에도 동작한다.

1) 조건절 범위에 해당하는 "Start 리프 블록"과 "End 리프 블록"의 위치를 계산한다.

2) 처리 범위가 인덱스 병렬 처리를 할 정도로 큰지 확인한다. (아래 표 참조)

3) 만일 크다면, 크기에 따라서 Worker 개수를 설정한 후에, "Start" ~ "End" 범위를 나눠서 처리한다.

4) 만일 작다면, 싱글 프로세스로 처리한다.

 

 

 

# PostgreSQL 파티션

PostgreSQL 파티션

 

▪ 상속 (Inherits)을 이용해서 파티션을 구현함

▪ 인덱스 정보는 상속되지 않음

▪ Trigger를 이용해서 파티션 Pruning을 처리함

▪ 파티션 생성 시에 CHECK 조건을 이용함

▪ Range, List 파티션을 제공함

▪ Global Index는 제공하지 않음

▪ 입력 조건에 맞는 파티션만 액세스하는 것을 ‘파티션 Pruning’이라고 한다.

▪ 이를 위해서는 constraint_exclusion 파라미터를 ‘on’ 또는 ‘partition’으로 설정해야 한다.

(기본 설정값: partition)

▪ CHECK 조건을 입력하면 정상적으로 파티션 Pruning이 수행된다.

 

서브 파티션을 이용한 튜닝 방안

▪ 1차 파티션을 통해 IO 범위를 줄인 후에도 여전히 IO 량이 많은 경우에는 2차 파티션을 고려한다.

▪ PostgreSQL은 상속에 의한 파티션 생성 방식이므로, 이론적으로는 n차 파티션도 가능하다.

▪ 따라서 업무 속성에 따른 서브 파티션 생성을 통한 IO 튜닝이 가능하다.

 

파티션 Partial 인덱스를 이용한 튜닝 방안

▪ PostgreSQL은 파티션 Partial 인덱스를 제공한다.

▪ 즉, 파티션 별로 인덱스 생성 여부를 결정할 수도 있고, 파티션 마다 다른 인덱스 (예, B*Tree, Covering Index, BRIN 등)를 생성할 수도 있다.

▪ 참고로, 오라클은 12c 버전부터 파티션 Partial 인덱스 기능을 제공한다.

 

파티션 입력 성능 향상을 위한 Tip

▪ RULE 보다는 Trigger를 이용한다. 어떤 경우에도 Trigger가 빠르다.

▪ 자주 입력되는 파티션을 Trigger의 윗 부분에 위치하는 것이 좋다. 그래야 IF THEN ELSE의 부하를 줄일 수 있다.

▪ 가능하면, 파티션을 지정해서 입력하는 것이 좋다.

 

PostgreSQL 파티션 Pruning 시의 주의 사항

▪ 예상과 달리, 파티션 Pruning이 동작하지 않는 경우가 존재한다.

▪ 이점은 쿼리 작성 시에 매우 주의해야할 점이다.

▪ IN 절 내에 Subquery를 사용할 경우에는 파티션 Pruning이 동작하지 않는다.

▪ IN 절 사용시 파티션 Pruning을 유도하기 위해서는 실제 CHECK 조건을 입력해야 한다.

▪ NL 조인으로 수행되면 추가 조건 없이도 파티션 Pruning이 수행된다.

▪ Hash 조인으로 수행되면 추가 조건을 반드시 입력해야 한다.

▪ 따라서, 쿼리 작성 후에 Explain 결과 확인 및 추가 조건을 입력할 필요가 있다.

▪ Hash 조인으로 수행될 때를 고려해서, 상수 조건을 추가하는 것을 습관화 하는 것이 좋다.

▪ PostgreSQL 10부터 오라클과 유사한 파티션 생성 문법을 제공한다.

▪ 이때, 트리거는 생성할 필요가 없다. (이점은 관리측면의 편의성을 제공한다)

▪ 단, 파티션 Pruning은 기존과 같은 한계가 존재한다. (즉, 해시 조인 시에는 상수 조건 추가 필요)

 

파티션 성능 테스트(INSERT)

▪ 9.6 까지는 파티션이 1개인 경우에도 일반 테이블보다 입력 속도가 3배 이상 느리다. 그 이유는 매 건마다 Trigger 함수를 호출하는 부하 때문이다.

▪ CPU 성능에 좋을수록 이 차이는 감소한다.

▪ 파티션 개수가 많아질 수록 입력 성능은 저하된다.

▪ 이 문제는 PG10 버전에서도 여전히 발생한다.

▪ 상속을 이용한 파티션은 Trigger 조정을 통해서 입력 성능을 향상 시킬 수 있다.

▪ 하지만, Native 파티션 (PG 10 버전)은 입력 파티션 위치에 따른 성능 차이가 거의 없다.

 

 

 

# PostgreSQL – Grafana 연동

설치하기 전 git, make, go 패키지 설치

# yum install git

# yum install make

# yum install go

 

git에서 postgres_exporter 가져옴

# git clone https://github.com/prometheus-community/postgres_exporter.git

 

폴더로 이동 후 빌드

# cd postgres_exporter

# make build

 

데이터 소스 환경 변수 설정 및 실행

# export DATA_SOURCE_NAME=postgresql://어드민계정:어드민비밀번호@RDS_ENDPOINT:5432/접속DB?sslmode=disable

# . /postgres_exporter

Prometheus에 pgexporter Job 등록

# vi prometheus.yml

Prometheus 서버에 pgexporter가 등록되었는지 확인

http://prometheus서버ip:9090/targets

 

Prometheus 그래프 확인

http://prometheus서버ip:9090/graph

 

Grafana에서 대시보드에 그래프 출력

 

Grafana에 PostgreSQL 직접 등록

 

 

 

 

# PostgreSQL slow query – Cloudwatch 연동

RDS의 데이터베이스 옵션에서 Cloudwatch Logs로 보낼 수 있도록 설정.