PostgreSQL 9.6 Parallel Query & FDW

0. 과연 잘 될까?!

최근에 PostgreSQL 9.6  가 2016 년 9월 29일 Release 되었습니다. ( 최신버전 PostgreSQL 9.6.1 2016-10-27 ) PostgerSQL 9.6 new features 중에서 Parallel QueryFDW ( Foreign Data Wrappers ) 이 가장 주목할 만합니다.

http://www.postgresql.org/docs/current/static/release-9-6.html

Major Features: Postgres 9.6

http://momjian.us/main/writings/pgsql/features.pdf

What’s New in PostgreSQL 9.6

http://wiki.postgresql.org/wiki/NewIn96#Parallel_Query

http://wiki.postgresql.org/wiki/NewIn96#postgres_fdw

Sharding with postgres_fdw

http://snowman.net/slides/pgfdw_sharding.pdf

흔히 말하는 대용량 데이터베이스 ( Very Larage Database or HIgh Volume Database ) 는 2가지의 필요조건을 충족해야 합니다. 바로 Parallel Query 와 Database Partition 입니다.  그리고, 이 단계를 넘어서 필요한 필요저건은 Cluster or MPP ( Massively Parallel Processing ) Database 입니다. Cluster Database 의 대표적인 예는 Oracle RAC ( Real Application Cluster) 이고, MPP Database 의 대표적인 예는 Pivtoal Greenplum 입니다. ( Pivotal Greenplum 은 PostgreSQL 기반입니다. )

Parallel Query 와 FDW 가 제대로만?! 동작해준다면 유사 MPP Database 처럼 사용할 수도 있을 것 같습니다. ( 실제로 PostgeSQL-XL 란 Scalable Open Source PostgreSQL-based Database Cluster 란 오픈소스 프로젝트가 있습니다. )

Scalable Open Source PostgreSQL-based Database Cluster

http://www.postgres-xl.org

How will Postgres-XL exploit the Parallel Query Capabilities of PostgreSQL 9.6?

http://blog.2ndquadrant.com/how-will-postgres-xl-exploit-the-parallel-query-capabilities-of-postgresql-9-6

PostgreSQL FDW 에서 Parallel Query 동작할지는 미지수이지만, 구글링을 통해서 찾아본 봐로는 몇년전 발표자료들에서는 된다는 것 같습니다. 그래서, 한번 테스트를 해보았습니다. 추가적으로 MySQL FDW 도 해보았습니다.

%ec%8a%ac%eb%9d%bc%ec%9d%b4%eb%93%9c8

이 블로그에서는 PostgreSQL 9.6 Parallel Query ( local ) 성능에 대해서는 논하지 않습니다. 2ndQuadrant blog 를 통해서도, 제가 간단하게 테스트를 해보아도 parallel factor (workter) 가  4 이상일 경우의 성능은 크게 의미가 없습니다. Adam’s law 에 너무너무 충실하다고 할까요?! 향후에 조금 더 성능개선이 되리라 봅니다.  ( EDB 를 믿습니다. )

PostgreSQL 9.6: Parallel Sequential Scan

http://blog.2ndquadrant.com/postgresql96-parallel-sequential-scan

Workers Time
 0 24767.848 ms
14855.961 ms
10415.661 ms
8041.187 ms
8090.855 ms
8082.937 ms
8061.939 ms

준비물 :

PostgreSQL 9.6.1

http://yum.postgresql.org/repopackages.php

MySQL 5.6.34 ( MySQL Sandbox 지원때문에 MySQL 5.7 안씀!! )

http://dev.mysql.com/downloads/mysql/5.6.html#downloads

MySQL SandBox

http://mysqlsandbox.net

 1. PostgreSQL 9.6 Parallel Query + FDW ( postgres_fdw )

결론부터 말씀드리면… 안됩니다. ㅠㅠ

Foreign Data Wrappers and You with Postgres

http://www.slideshare.net/EnterpriseDB/foreign-data-wrappers-and-you-with-postgres

FDW ( Foreign Data Wrappers ) 는 Parallel Query 보다는 Asynchronous Query 입니다. PostgreSQL 9.5 보다는 개선되었기 때문에 나름 의미는 있겠지만,  결국은 Parallel Query 와는 무관하게 Async 하게 동작하는 하는 겁니다. 그리고, 각 remote postgres 에서 Parallel Query 로 실행되지 않습니다. ㅠㅠ

그래도, 결과적으로는 비슷합니다?! 개별 remote table access cost 를 1 이라고 하면, non-async 하다면 2  node access cost 가  1 + 1 입니다. 그러나, 실제 cost 는 1.5 ~ 1.8 정도의 cost 만 소용됩니다. async 하니까?! 그렇지만, 결국 remote postgres 에서  Parallel Query 가 동작하지 않기 때문에 개이득은 적습니다.

Parallel Query  !=  Async Query

slide 17/22 – ” Can use libpq’s asynchronous API to issue multiple pending queries “

http://momjian.us/main/writings/pgsql/sharding.pdf

PostgreSQL Parallel Query (local) -> postgres_fdw -> PostgreSQL Parallel Query (remote)
    (libpq’s async API)  -> PostgreSQL Parallel Query (remote)

%ec%8a%ac%eb%9d%bc%ec%9d%b4%eb%93%9c11

Parallel Query Parameters

Compared to the 9.5 release, new parameters have been introduced inside the configuration file. These are:

  • max_parallel_workers_per_gather: the number of workers that can assist a sequential scan of a table;
  • min_parallel_relation_size: the minimum size that a relation must have for the planner to consider the use of additional workers;
  • parallel_setup_cost: the planner parameter that estimates the cost of instantiate a worker;
  • parallel_tuple_cost: the planner parameter that estimates the cost of transferring a tuple from one worker to another;
  • force_parallel_mode: parameter useful for testing, strong parallelism and also a query in which the planner would operate in other ways.
-- default 0
SET max_parallel_workers_per_gather TO 8;
-- default 8MB
--SET min_parallel_relation_size TO DEFAULT;

-- default 1000
--SET parallel_setup_cost TO DEFAULT;
-- default 0.1
SET parallel_tuple_cost TO DEFAULT;

-- default 0
SET parallel_setup_cost to 1;

Generate Data ( local, remote )

-- Local Postgres
DROP TABLE local_tbl;
CREATE TABLE local_tbl (c01 INTEGER PRIMARY KEY, c02 CHAR(1000));
INSERT INTO local_tbl VALUES (generate_series(1, 1000000), 'aaaaa');
ANALYSE local_tbl;

-- Remote Postgres 01
DROP TABLE local_tbl;
CREATE TABLE local_tbl (c01 INTEGER PRIMARY KEY, c02 CHAR(1000));
INSERT INTO local_tbl VALUES (generate_series(1, 1000000), 'aaaaa');
ANALYSE local_tbl;

-- Remote Postgres 02
DROP TABLE local_tbl;
CREATE TABLE local_tbl (c01 INTEGER PRIMARY KEY, c02 CHAR(1000));
INSERT INTO local_tbl VALUES (generate_series(1000001, 2000000), 'aaaaa');
ANALYSE local_tbl;

Create Server, Foreign Table ( postgres_fdw )

DROP EXTENSION postgres_fdw CASCADE;
CREATE EXTENSION postgres_fdw;

-- Remote Postgres 01
CREATE SERVER pg_fdw_server_01
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', port '5433', dbname 'test');

CREATE USER MAPPING FOR pgsql
SERVER pg_fdw_server_01
OPTIONS (user 'pgsql', password 'pgsql');

-- remote table 에는 PK 가 반드시 있어야 하지만,
-- local table 은 PK 가 반드시 없어야 합니다.
CREATE FOREIGN TABLE remote_tbl_01
(
   c01 INTEGER,
   c02 VARCHAR(1000)
)
SERVER pg_fdw_server_01
OPTIONS (table_name 'local_tbl');

-- Foreign Table 은 Analyze 되지 않습니다.
ANALYZE remote_tbl_01;

-- Remote Postgres 02
CREATE SERVER pg_fdw_server_02
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', port '5434', dbname 'test');

CREATE USER MAPPING FOR pgsql
SERVER pg_fdw_server_02
OPTIONS (user 'pgsql', password 'pgsql');

CREATE FOREIGN TABLE remote_tbl_02
(
 c01 INTEGER,
 c02 VARCHAR(1000)
)
SERVER pg_fdw_server_02
OPTIONS (table_name 'local_tbl');

ANALYZE remote_tbl_02;

Explain Plan Query

\timing on
\pset pager off

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM local_tbl;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_01;
EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_02;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_01
UNION ALL
SELECT AVG(C01) FROM remote_tbl_02;

-- default 0.1
SET parallel_tuple_cost TO 0;
-- default 1000
--SET parallel_setup_cost TO DEFAULT;
-- default 0
SET max_parallel_workers_per_gather TO 8;
-- default 8MB
--SET min_parallel_relation_size TO 1;
-- default 0
SET parallel_setup_cost to 1;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM local_tbl;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_01;
EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_02;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_01
UNION ALL
SELECT AVG(C01) FROM remote_tbl_02;

2. PostgreSQL 9.6 Parallel Query + FDW ( mysql_fdw )

MySQL 도 해보았습니다. 잘 동작합니다. 그러나, 구성과 운영이 가능하리라 봅니다. Local Table 에는 Parallel Query 가 적용되기 때문에 이 부분을 잘 이용하면 MySQL Spider Engine, Presto 등과 또 다른 구성과 운영이 가능히리라 봅니다. 물론 추천하지 않습니다?! ㅠㅠ

PostgreSQL foreign data wrapper for MySQL

http://github.com/EnterpriseDB/mysql_fdw

%ec%8a%ac%eb%9d%bc%ec%9d%b4%eb%93%9c10

PostgreSQL Parallel Query (local) -> mysql_fdw -> PostgreSQL Parallel Query (remote)
      -> PostgreSQL Parallel Query (remote)

Create Server, Foreign Table ( mysql_fdw )

DROP EXTENSION mysql_fdw CASCADE;
CREATE EXTENSION mysql_fdw;

-- Remote MySQL 01
CREATE SERVER my_fdw_server_01
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '16035');

CREATE USER MAPPING FOR pgsql
SERVER my_fdw_server_01
OPTIONS (username 'root', password 'msandbox');

-- remote table 에는 PK 가 반드시 있어야 하지만,
-- local table 은 PK 가 반드시 없어야 합니다.
CREATE FOREIGN TABLE remote_tbl_01
(
 c01 INTEGER,
 c02 VARCHAR(1000)
)
SERVER my_fdw_server_01
OPTIONS (dbname 'test', table_name 'tbl');

-- Foreign Table 은 Analyze 되지 않습니다.
ANALYZE remote_tbl_01;

-- Remote MySQL 02
CREATE SERVER my_fdw_server_02
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '16036');

CREATE USER MAPPING FOR pgsql
SERVER my_fdw_server_02
OPTIONS (username 'root', password 'msandbox');

CREATE FOREIGN TABLE remote_tbl_02
(
 c01 INTEGER,
 c02 VARCHAR(1000)
)
SERVER my_fdw_server_02
OPTIONS (dbname 'test', table_name 'tbl');

ANALYZE remote_tbl_02;

Explain Plan Query

\timing on
\pset pager off

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM local_tbl;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_01;
EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_02;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_01
UNION ALL
SELECT AVG(C01) FROM remote_tbl_02;

-- default 0.1
SET parallel_tuple_cost TO 0;
-- default 1000
--SET parallel_setup_cost TO DEFAULT;
-- default 0
SET max_parallel_workers_per_gather TO 8;
-- default 8MB
--SET min_parallel_relation_size TO 1;
-- default 0
SET parallel_setup_cost to 1;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM local_tbl;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_01;
EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_02;

EXPLAIN (ANALYZE ON, VERBOSE OFF, COSTS OFF, BUFFERS ON)
SELECT AVG(C01) FROM remote_tbl_01
UNION ALL
SELECT AVG(C01) FROM remote_tbl_02;

3. 결론

PostgreSQL 9.6.1 Parallel Query + FDW 는 되지 않습니다. remote postgres 에서도 Parallel Query 가 되지 않습니다.

Advertisements

왜 동네짱은 PostgreSQL에서 MySQL 으로 데이터베이스를 마이그레이션?

이전글

https://mysqlko.wordpress.com/2016/08/05/mysql-vs-postgresql-uber

 

97d37ea1d4a07355a3be45d80001e985
우석고 실질적 짱 : 현상태

Why Uber migrated its databases from Postgres to MySQL?

http://blog.webyog.com/uber-migrated-databases-postgres-mysql

 

Uber 사태?! 에 대한 PostgreSQL 진영!?의 글에 이어서 MySQL 진영?! 글이 올라 왔습니다.

webyog 란?!

SQLyog : MySQL GUI & Admin  과 MONyog : MySQL Monitor 를 만드는 회사입니다. SQLyog Community 버전도 있습니다.

https://www.webyog.com/product/downloads/

PostgreSQL 제한?! 한계?!

  1. Inefficient architecture for writes: A relational database must perform certain key tasks that Postgres were not suited for Uber’s needs.
    • Providing efficient insert/update/delete capabilities
    • Providing capabilities for making schema changes
    • Implementing a MVCC mechanism so that different connections have a transactional view of the data they work with.

  1. 비효율적인 쓰기 구조?! : 우버는 다음과 같은 RDBMS 요건을 필요로 한다. 그러나, PostgreSQL 은…
  • INSERT/UPDATE/DELETE 가 효율적이야 한다. / OLTP 성능이 좋아 한다.
  • 스키마 변경이 되어야 한다. / ALTER 구문이 효율적이야 한다.
  • 서로 다른 연결에 대해서 트랜잭션날 관점에서의 MVCC 메카니즘 구현이 필요하다. / CTID ( OID, RID  동일 ) 값이 물리적 오프셋값(green) 으로 되어 있어서 비효율적이다. CTID 값이 변경시 모든 index 가 변경되어 한다.

With Postgres, the primary index and secondary indexes all point directly to the on-disk tuple offsets. When a tuple location changes, all indexes must be updated.

  • Inefficient data replication: Postgres’ replication required higher bandwidth as compared to MySQL but Postgres replication may not be a problem within a single data center. The real problem with Postgres arises when replication is done between multiple data centers.
  • 비효율적인 데이터 리플리케이션 : PostgreSQL Replication 은 MySQL 대비해서 높은 대역폭(bandwidth) 을 요구하다. 그러나, PostgreSQL Replication 은 데이터센터 내에서는 문제가 되지 않는다. / DC-to-DC 간에는 문제가 될 수 있다?!

  • Issues with table corruption: During one of Uber’s routine master database promotion to increase database capacity, they encountered a Postgres 9.2 bug. This resulted in numerous incorrect timeline switches on replicas that were being undertaken.
  • 테이블 손상 이슈 : 마스터 서버중 하나가 과도한 용량 증가 버그가 PostgreSQL 9.2 에서 있었다. 이 버그는 복제에까지 수많은 영향을 미쳤다. / 마스터서버의 버그가 슬레이브까지 영향을 미쳤다.

  • Poor replica MVCC support: Postgres does not have true replica MVCC support. The fact that replicas apply WAL updates results in having a copy of on-disk data identical to the master at any given point in time.
  • 가난한 리플리카?! MVCC 지원 : PostgreSQL 은 레알 리플리카 MVCC 를 지원하지 않는다. 실제로, 리플리카는 WAL 업데이트가 디스크에 쓰여진 복사본을 가진 시점에서 동일하게 적용된다. / WAL 로그가 디스크에 반영된 후에 복사본을 가지고 이루어지다 보니, 성능이 좋지 않다.

  • Difficulty upgrading to newer releases: With different general available releases of Postgres, it was not possible to replicate the data. Replication records work at the physical level which made it difficult for Uber.
  • 새로운 릴리즈 업그레이드 어려움 : PostgreSQL 은 GA 릴리즈 버전간의 데이터 리플리케이션이 가능하지 않다. 물리적 레벌에서 복제는 우Uber 를 힘들게 했다. / 이중화 구성시 한쪽만 순차적 업그레이드가 불가능하다. downtime 이 불가결하다?!

구글번역기 감사합니다!! ㅠㅠ

이에 반해서 MySQL 은… 생략?!

결국 Uber 가 PostgreSQL 에서 MySQL 로 바꾼 이유는 크게 보면 2가지 입니다.

성능 (Performance)  and 운영, 가용성 ( High Availability, Replication )

예전부터 OLTP : MySQL , OLAP : PostgreSQL 이란 선입견이 일반적이었습니다. 많은 글로벌 서비스가 메인DBMS 를 MySQL 을 채용하고 있는데, 그 이유 중 하나는 글로벌 서비스 자체가 OLTP : high short-request 이기 때문입니다. 거기에, PostgreSQL 이 WAL( Physical Log  ) Replication 을 default 로 하고 있다보니, 그로 인해서 Master-Slave upgrade ( downtime ), Master- Slave incorrect, Replication Performance 등이 뒤따서 이슈가 된 것 같습니다.

결국 Uber 와 같은 글로벌 서비스에서는 운영관리가 편한 자알~ 알려진 Open Source 기반의 OLTP DBMS 가 적합했던 것 같습니다.

PostgreSQL 은 Logical Log Replication 을 지원합니다. 향후에는 default 가 되지 않을까!? 라고 생각됩니다. 그리고, INSERT/UPDATE/DELETE 성능에 대해서는 PostgreSQL 9.x 부터는 성능 이슈에 대해서 EDB 가 크게 기여하고 있기 때문에, 향후 버전업에 따라서 많은 개선이 있으리라 봅니다. 그리고, PostgreSQL 9.6 GA 에는 Parallel Query 가 지원된다면 많은 개선이 되리라 봅니다.

추가적으로, 드루팔 7.x 성능 비교 자료를 보시면 PostgreSQL vs MySQL 성향차이를 이해하는데 도움이 되리라 봅니다.

Comparing PostgreSQL 9.1 vs. MySQL 5.6 using Drupal 7.x

http://posulliv.github.io/2012/06/29/mysql-postgres-bench/