PostgreSQLv.s.

大規模OLTP2019年 4月 19日
OSSコンソーシアム データベース部会セミナー
SRA OSS, Inc. 日本支社 高塚 遥

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

1講演者 と 本講演のテーマ
【講演者】
名前所属
略歴

高塚 遥
SRA OSS, Inc. 日本支社
PostgreSQLのセミナ講師、サポート、コンサルティング等に従事。
2016年より日本PostgreSQLユーザ会理事長を務める。
著書に 「これから始める PostgreSQL 入門」（技術評論社）。

【TOC】
 PostgreSQL とは トランザクション処理のおさらい 大規模OLTPとの戦い
 今日のＩＴ環境と PostgreSQLアーキテクチャ
 スケールアップでの対応
 スケールアウトでの対応
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

2

PostgreSQLとは (1) 多機能、高性能、かつオープンソースのリレーショナルデータベース管理システム（RDBMS）

 INGRES('70)、POSTGRES('80) 由来の歴史
 BSD型ライセンス
 特定オーナー企業が無い
企業
ある種のOSS開発体制

企業

企業
PostgreSQL
製品

開発者

企業
PostgreSQL開発体制

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

3

PostgreSQLとは （2） 安定的で管理されたリリース
 機能面で着実に商用製品にキャッチアップ

PostgreSQL source code lines
PostgreSQL各バージョンの行数とリリース日、主な追加機能

3,500,000
3,000,000
2,500,000
2,000,000
1,500,000
1,000,000
500,000

2018.102016.12017.102014.122011.92016.92013.92010.92012.92006.122009.7
JIT
compile2008.2
2005.11
manyParallel
JSONBcore
2005.1
Repliscale
hot (fast)cationBitmapUPDATE
Scan
logicalRLS,Repli.
Mview,UPSERT
SSI,
UView
OLAP,
FDW
4core
CTE
scale
PITR

0
8.0

8.1

8.2

8.3

8.4

9.0

9.1

9.2

9.3

9.4

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

9.5

9.6

10114

トランザクション処理のおさらい (1) データベース利用法
 OLTP（オンライントランザクション処理）
 銀行業務、各種決済、市場取引、電子商取引、在庫管理、予約予定管理（+ オンラインゲーム）
 使用者の実際の取引を確定させ、取引の事実を記録する
 データ更新が頻繁に発生する
 同時並行で多数の利用者

 OLAP（オンライン分析処理）




分析用途
基本的に参照のみ
データ投入は規則的・計画的 （たいてい OLTPデータベースから持ってくる）
少数の利用者 （「センサーデータの並行大量投入」はある）

 その他
 CMS、ディレクトリ、資産管理、全文検索（「分析」ではないけれども参照中心） ＳＮＳ（データ更新は多いけれども、実際の取引とリンクしない、不整合に寛容）

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

5トランザクション処理のおさらい (2) トランザクション処理を行うシステムに要求されること⇒ フルスペックな RDBMS が提供するもの

 ACID Atomic 原子性 一連の操作の結果が必ず「全部成功」か「全部失敗」のいずれかにできる
 START TRANSACTION / COMMIT / ROLLBACK コマンド Consistency 一貫性
 データが正しい状態を保つ、見せる （「正しさ」は 論理設計する使用者が規定）
 CONSTRAINT（制約） 機能 、 A と I、D の完備による不整合防止

 Isolation 独立性 （並行する）他のトランザクションに影響されずに処理が実行できる
 TRANSACTION ISOLATION LEVEL 対応、 （明示、暗黙の） LOCK 機能 Durability 永続性 確定したトランザクション結果が障害で失われない 永続メディアへの書き込み、 CHECKPOINT + WAL（Write Ahead Log）
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

6トランザクション処理のおさらい (2-1)
 Atomic

A = 100
B=0

A:= A - 100

障害 A = 0
B= 0
B:= B + 100
COMMIT Consistency
A = 100
A:= A - 200
A>=0であるとして

A = -100

A= 0
B = 100※ 購入履歴が追加されている
v.s.
出庫指示が追加されていない

 Isolation

等も Consistency違反。
C=100

C = 100

C=100

C = 101

C:=100+1

C:=100+1

非同期レプリケーションして古いデータが見えてしまう等で違反が生じる。

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

7トランザクション処理のおさらい (3)本質論では無い
現実の実装を区分

 SQL標準の TRANSACTION ISOLATION LEVEL
隔離レベル

発生する隔離失敗の動作

備考(PostgreSQLでの実装など)

READ UNCOMMITTED

Dirty Read（未コミットの変更内容の参照）

このモードは無い。 READ
COMMITTED と同じになる。

READ COMMITTED

Non-Repeatable Read （他TXのコミットにより同データを再度読むと異なる値）

デフォルトの隔離レベル。
多くのアプリがこのレベルを使っている。

REPEATABLE READ

Phantom Read （他TXのコミットで同条件で再度検索すると異なる結果）

SI (Snapshot Isolation) で実装。
よって実際には Phantom Readも起きない。

SERIALIZABLE

上記が発生しない。
SSI (Serializable SI）で実装。
Write Skew Anomaly （参照 暗黙の述語ロック機構が働く。と書き込みの組み合わせで なお Oracle 実装は単なる SI。
生じる異常） も発生しない。

●

高い隔離レベルでは、しばしば直列化失敗エラーが起きる●
アプリケーション側で トランザクションのやり直しを行えば良い（DBアクセスフレームワークに標準で対応機能がほしい・・・）
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

8トランザクション処理のおさらい (4) MVCC (MultiVersion Concurrency Control） による実現～ 103番トランザクション ～db=# SELECT * FROM t1;
id | v----+--1 | A
2 | B
3 | C(3 rows)

物理格納データ：
xmax xmin
104
テーブ
ルのデータコミットログ

108

value

100

id=1 v='A'

100

id=2 v='B'

102

id=3 v='C'

104

id=1 v='A2'

106

id=4 v='D'xidstatus

100

コミット済

101

アボート済

102

コミット済

103

未コミット

：

：

～ 104番トランザクション ～db=# UPDATE t1 SET v = 'A2'
WHERE id = 1;

後でVacuum～ 106番トランザクション ～db=# INSERT INTO t1 (id, v)
VALUES (4, 'D');～ 108番トランザクション ～db=# DELETE FROM t1
WHERE id = 3;

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

9トランザクション処理のおさらい (5) チェックポイント / Write Ahead Log による Durability の実現 伝統的な ARIESアーキテクチャの一種 ただし Undoログは 共有バッファ～ヒープ内に混在
共有ページバッファ
読み書きテーブルやインデックスの実体データ

PostgreSQL
プロセス【チェックポイント】《ストレージ》

PostgreSQL
プロセス【SQL実行】

書き出し（バッファ溢れ時も）
WAL
バッファ

読み込み

書き出し

単調増加な LSN
(Log SequenceNumber) を持つ

WAL
WAL
WAL
レコード
レコード
レコード
コミット時にsync保証

ヒープ
ファイル

《ストレージ》

WAL
ファイル

PostgreSQL
プロセス【リカバリ】

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

レプリケーションも、これで実現

10大規模OLTPとの戦い (1) ますます高まる要求 ＩＴ業界で定番の枕詞

 ２０年間のハードウェア成長の偏り
 CPU

クロックアップは飽和 / コア数、CPUキャッシュ、GPU メモリ

大容量化（含む低価格化）、 バスも高速化 ストレージ

大容量化、SSD普及、 不揮発メモリ登場

 ネットワーク高速化が継続中 （～ 400GbE など）、とはいえ遅い

 大規模OLTPでのボトルネックは？
 最速を求めると事実上オンメモリになる

マルチマシンでスケールアウト ディスク格納指向のページを読み書きする無駄 競合処理でコア数スケーラブルの限界
 WAL書き出し周辺のボトルネック
 それ以外にも同時実行での排他処理オーバヘッド
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

多CPUコアへの更なる適応

11

大規模OLTPとの戦い (2) データベースをスケールアウトするだって？
 Webアプリケーションサーバなら簡単
 ＤＢはデータを持っている レプリケーション？ シャーディング？

（何らか更新） ACIDトランザクション処理はどうするの？


2 Phase Commit
Paxos、Raft、etc・・・
易しい話ではない
分散用に設計されたDBMSでないとDBMS

DBMS

DBMS

 今ある PostgreSQLのクラスタ機能

物理レプリケーション
ロジカルレプリケーション
同期モード
トポロジ

インスタンス単位 WALを伝播
テーブル単位 WALに付加した行情報を伝播
非同期、write同期、apply同期、 クォーラム可シングルマスタ＝マルチスレーブ、 カスケード可 各種の外部プロジェクトで拡張されたクラスタソリューションが提供されている
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

12大規模OLTPとの戦い (3) PostgreSQL の OLTP むけ高性能クラスタは？
 レプリケーションと参照負荷分散
 ストレージ同期保証off + クォーラム同期レプリケーション も可能 更新処理への効果は限定的、またはマイナス

 AWS Aurora PostgreSQL WALレコードでストレージノードに書き込み
 信頼性を実現しつつ、WAL書き込み周辺のボトルネックを軽減
Buffer update

pgpoolpgpool参照更新振り分け

Postgres（必要に応じて参照更新振り分けを外部的に追加)

Postgres

Postgres

Postgres

Postgres

WALstorage

Postgres
Heap Pagestoragestorage※図はAWS
発表資料を参考に作成

WAL
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

13大規模OLTPとの戦い (4) BDR（Bi-Directional Replication、マルチマスタ） クラスタ
 商用製品による拡張
 ロジカルレプリケーションに基づく ／ 整合性保証は緩い
 書き込み性能向上目的に適しているわけではない

 透過的なシャーディングクラスタソリューション
 Postgres-XL ／ Citus ／ 殆どの製品は OLAPむけ

 アプリケーションレベルでのシャーディング／ＤＢ分割／KVS併用
 現アーキテクチャで対応可能な規模に分割する
 必要に応じて FDW（外部データラッパ）やテーブル単位ロジカルレプリケーションを活用 一部のデータをスケーラブルな KVS に逃がす、あるいは、キャッシュとして使う
 PostgreSQLでの大規模OLTP対応に現実的に使われているのはこれ
Postgres

Postgres

Postgres

データBに関する処理ＡBC

Postgres

ＡPostgresBPostgres

CWAL with logical decoding
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

14大規模OLTPとの戦い (5) PostgreSQLの透過的OLTPむけクラスタソリューションは無い
 今のところ （PostgreSQLでは無いが参考に） VoltDBアークテクチャ
 “The End of an Architectural Era” の Michael Stonebraker による PostgreSQLの先祖 INGRES 開発者でもある
 「DBMS はバッファ制御や排他処理に CPU時間の多くを使っていて無駄」

 オンメモリ クォーラムレプリケーションで永続性保証
 トランザクション実行を 「本当に」 直列化して排他処理のコードを排除 テーブルはパーティションニングかレプリケーションが必須 個々のトランザクション実行には、複数CPUコアを使う
 トランザクションはストアドプロシージャの形で与える

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

15大規模OLTPとの戦い (6) 1台のDBサーバを多CPUコアにスケールアップ
 PGECons pgbenchベンチマーク結果
CPU:
2p/32cmemory: 384GB
シナリオ：
index検索ランダム単一行の UPDATE
1行はメタデータを除く100byte程度
全データがメモリに載る
横軸はクライアント同時実行数

両グラフともPGECons 「2017年度WG1活動報告書」の 『4.7. 検証結果(更新系) 』 より

このとき 10.x が遅い件はマイナーアップで修正された

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

semop (PG9.6) やfutex (PG10) が占める割合が高い

16

大規模OLTPとの戦い (7) PostgreSQL の 多CPUコア～CPU律速 むけ改修
 PostgreSQL 9.6～11 での改修



共有メモリ上の構造体の分割 ／ 排他制御の範囲を小さく共有バッファのページ管理方式を変更 ／ スピンロックを回避
各種構造体を小さく ／ CPUキャッシュに収まるようにコミットログのグルーピング更新
JITコンパイル … これはOLAPむけ→ この種の改善は今後も続くであろう PostgreSQL 12 での改修


ストレージエンジンが Pluggable になった
MySQLでは昔から
Aurora の成功例
商用版ベンダ EnterpriseDB社による Oracleライクなエンジン開発→ PostgreSQLを素晴らしくする競争が始まった！

 GPU ソリューション
 PG-Strom … これも OLAP むけ

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

17

まとめ
 伝統的な RDBMS としてトランザクション処理に十分な機能をもつ PostgreSQL
 大規模OLTPに対するスケールアップ、スケールアウトの対応は現状では難しい 新たなアーキテクチャ取り込みが期待される

本ドキュメント中にあらわれる商品名・製品名は各社の商標です。
本ドキュメントの正確性、有用性、その他について SRA OSS, Inc. 日本支社はいかなる保証もいたしません。
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

18

オープンソースとともに19