2

2

5,6 年年前 :OSS は 一般的でない コスト削減要望を受けてやむを得ず プロダクト品質への不不安を払拭するのに必死 現在 :OSS を使うのは当たり前 ユーザ側が OSS に抵抗感を持たなくなっている むしろ OSS での提案を希望されるケースが増加 データベースにも OSS を求める 風潮が 3

全て OSS に置き換わるわけじゃない 商 用製品にしかできないこともある 製品の特徴を理理解して選択するのが 大切切 技術者の 立立場 PostgreSQL 案件で慌てないよう先に知っておく 特徴を知れば他製品の知識識でキャッチアップ可能 4

氏名 : 中 西剛紀 ( なかにしよしのり ) 所属 : TIS 株式会社 OSS 推進室 仕事 : OSSのサポート 技術 支援 PostgreSQL 歴 :6 年年くらい DB 歴 : たぶん10 年年以上 5

コミュニティでの活動 PostgreSQL エンタープライズコンソーシアム 成果報告セミナーで WG 検証成果を講演したり http://itpro.nikkeibp.co.jp/atcl/column/15/052800134/052900004/?st=oss&a 日本 PostgreSQL ユーザ会 PostgreSQL カンファレンス 2015 で講演したり http://www.slideshare.net/naka24nori/postgre-sql-55593111 周辺ツールの公開 pg_monz: Zabbix で PostgreSQL を監視するツール http://pg-monz.github.io/pg_monz/ 6

7

8.2 CPU スケールオンライン索索引作成 GIN インデックスウォームスタンバイ 9.1 同期レプリケーションパーティショニング強化 一時テーブル強化 9.4 レプリケーション運 用性向上 JSON 強化 大容量量メモリ対応 9.3 障害切切替時間短縮ビューの改良良 DB 間連携強化 6.0 1997 8.1 パーティショニング 2 相コミットバッファ管理理改良良ロール 7.4 2003 2005 2006 8.0 Windows 対応 PITR テーブルスペース PITR 2007 8.4 Window 関数再帰 SQL 2008 2009 8.3 HOT: 更更新性能向上 VACUUM 自動化全 文検索索 XML データ型 2010 2011 2012 9.0 レプリケーションホットスタンバイ Windows 64bit 対応 2013 2014 9.2 カスケードレプリケーションスケーラビリティ向上インデックス検索索強化 2015 9.5 BRIN インデックス WAL 圧縮 行行レベルセキュリティ UPSERT 8

いろんな OS で動作 Linux ( 最近のディストリビューションすべて ) Windows (Win2000 SP4 以降降 ) FreeBSD, OpenBSD, NetBSD OS X, AIX, HP/UX, Solaris, UnixWare ビルドファームで何がテスト済かを確認可能 http://buildfarm.postgresql.org/cgi-bin/show_status.pl Windows でちょっと困ること Windows では動かない周辺ツール達 pg_rman, pg_statsinfo, pgpool-ii 等 Windows で使えないノウハウ メモリ関連のチューニング 9

The PostgreSQL Licence BSD や MIT に似た緩いライセンス プロダクトのダウンロード 利利 用は無償 商 用利利 用も制限なし ソースコードを改変した有償製品を売ってもいい 改変したソースコードの公開は不不要 PostgreSQL ベースの商 用製品 サービス PowerGres/PowerGres Plus (SRA OSS) EDB Postgres ( 旧 :Postgres Plus)(EnterpriseDB) Amazon Redshift(AWS):PostgreSQL 8.0.2 ベース Greenplum(Pivotal) 10

保守サポートベンダは国内に多数 SRA OSS, Inc, 日本 支社 アシスト サイオステクノロジー NEC 日本 HP 日 立立製作所 TIS サポートは 入っておきましょう ソース読めばなんでもできますが 誰がやる? 11

ボランティアベースの組織運営 PostgreSQL Global Development Group PostgreSQL へ貢献する個 人 / 企業の 非法 人組織 特定の会社が開発をコントロールしない プロダクトの進化を 止めずに運営し続ける苦労 他の製品は? Oracle MySQL 12

13

ACID 特性 Atomicity: 原 子性 トランザクション全体が実 行行される or 全く実 行行されない Consistency: 一貫性 整合性条件を満たさない状態を起こす処理理は実 行行しない Isolation: 独 立立性 トランザクション途中のデータを他から参照出来ない 隔離離性があるということ Durability: 永続性 トランザクション完了了後のデータを失わない 耐障害性があるということ PostgreSQL は ACID を満たす 14

同時並 行行性とデータ 一貫性を両 立立 変更更中データをロックすると同時並 行行性を損なう 変更更前後のデータを持つことで同時並 行行性を保つ PostgreSQL は追記型で実現 書き込み中でも読み込みをブロックしない UPDATE 在庫 SET 在庫数 = 6 WHERE ID = 002; ID 商品名在庫数 001 A 10 10 002 B 8 8 003 C 20 20 004 D 12 12 002 B 6 SELECT 在庫数 FROM 在庫 WHERE ID = 002; 15

PostgreSQL は追記型 不不要になったデータは VACUUM で回収 運 用時にノウハウが必要になることも 不不要領領域を 回収せよ VACUUM ID 商品名 在庫数 001 A 10 002 再利用可能レコード B 8 003 C 20 004 D 12 002 B 6 INSERT INTO 在庫 VALUES (005, 'E', 50); Oracle, MySQL も MVCC を実現 UNDO セグメントに変更更前データを退避 16

標準 SQL への準拠度度が 高い ISO/IEC 9075 Database Language SQL ISO/IEC 9075:2011(SQL:2011) のほとんどの機能をサポート 多様な SQL をサポート MySQL にはない標準 SQL の例例 Window 関数 : 複雑な集計を効率率率よく処理理する構 文 再帰 SQL : 親 子関係を持つ 木構造のテーブルに対して繰り返し 自 己結合を 行行う 17

PostgreSQL は性能が悪い? 昔から PostgreSQL は遅い と思われている 古いバージョンのイメージが根強い 18

CPU 数に応じたスケールアップ 9.4 9.5 でさらに 22% 性能向上 30000 25000 9.5 9.4 20000 TPS 15000 10000 5000 0 0 20 40 60 80 100 120 140 クライアント数 PGECons 2015 年年度度 WG1 活動報告より抜粋 19

パーティショニング テーブルを分割して検索索 更更新性能を向上 約 36 億件を 180 分割した時の検索索性能 いずれの検索索 方法においても パーティションを使 用した 方が 高速 PGECons 2013 年年度度 WG1 活動報告より抜粋 20

プロセスモデルの違い 接続要求毎にプロセス or スレッドを 生成 マルチプロセス :PostgreSQL, Oracle マルチスレッド :MySQL 同時実 行行数が多いケースは MySQL が有利利 ディスク I/O の違い MySQL はランダム指向で SSD と相性良良 Fusion-io で性能が数 十倍になる事例例も PostgreSQL は HDD 向けのシーケンシャル指向 並べ替えて書き込むので SSD でも速くならない 21

複雑な SQL は PostgreSQL 優位? 多様な結合 方式をサポート Nested Loop, Merge Join, Hash Join MySQL は Nested Loop のみ 大きなテーブル同 士を結合する分析 用途で差が出る サブクエリ ( 副問い合わせ ) PostgreSQL は 非相関サブクエリを 1 回だけ実 行行 MySQL は 非相関サブクエリも N 回実 行行してしまう PostgreSQL では結合するより 高速なことが多い select id, name, description from departments where id in ( select dept_id from employees where gender = 'F' ) MySQL は SQL をシンプルに アプリで頑張る 22

DB サーバ停 止 = サービス停 止 冗 長化による可 用性確保が 一般的 PostgreSQL での冗 長化構成 HA クラスタ構成 ( 共有ストレージ ) HA クラスタ構成 ( シェアードナッシング ) ストリーミングレプリケーション 23

アクティブ / スタンバイ 旧来からある 方式 シンプルで運 用が楽 ストレージが 高い 24

アクティブ / スタンバイ ストレージでデータ同期 ストレージの機能を利利 用 ソフトウェアの機能を利利 用 低コストで実現可能 クラスタソフト : Heartbeat, Pacemaker レプリケーション :DRBD 25

アクティブ / アクティブ 待機系サーバでも参照 SQL を実 行行可能 待機系サーバを増やして参照処理理をスケールアウト 低コストで実現可能 標準機能でデータ同期 26

Oracle RAC キャッシュ機構で 一貫性を保ちつつ 高可 用性と 高拡張性を実現 MySQL Cluster 単 一障害点がなく障害時の切切替も 高速 99.999% の 高い可 用性 単純なクエリを 大量量に捌きたいニーズ向け PostgreSQL でここまでは満たせない 更更新可能なマスタは 1 台という制約 27

PCI DSS をどこまで満たせる? クレジットカード業界のセキュリティ基準 PostgreSQL エンタープライズコンソーシアム の WG 活動で調査 https://www.pgecons.org/wp-content/uploads/pgecons/2014/wg3/ PGECons_2014_WG3_Security.pdf アクセス制御はだいたい可能 クライアント認証 テーブル 列列 行行単位に参照可能データを制限 OS や外部ツールの助けが必要なこともある アカウントポリシー 不不正アクセスへの対処 28

監査 標準機能で実 行行した SQL をログ出 力力可能 課題 : 出 力力対象を細かく指定できない テーブル単位 列列単位での指定 SQL 種別 (READ,FUNCTION のみ ) を指定 拡張モジュール pgaudit で若若 干改善 課題 : サーバログと監査ログを分けられない 監査ログを分析する際の煩わしさ 商 用製品 EDB Postgres は監査ログを別にできる 29

暗号化 格納データを透過的に暗号化するニーズ カード番号 マイナンバー アプリ側で暗号化するのは煩わしい 課題 : 格納データを暗号化する機能がない PostgreSQL でデータを暗号化する 方法 ファイルシステムの透過的暗号化機能 拡張モジュール pgcrypto Transparent Data Encryption for PostgreSQL(TDE) 商 用製品 (PowerGres Plus) 30

リッチな GUI は期待しない 基本的に 黒い画 面とシェルスクリプトで対応! 性能チューニング 自動チューニング? なんすかそれ? Oracle は SQL トレースでいろんなことがわかる PostgreSQL は SQL 単位のトレースは取れない バックアップ等のツール 標準機能を補うツールが OSS で公開されている ドキュメントやノウハウ情報は少なく 使えるものを選定して 使いこなすのは 面倒 31

32

33

34

35

Apache Tomcat Active サーバ Standb y サーバ リソース監視 死活監視 リソース監視 データ同期 情報収集 運 用設定 Apache Tomcat 運 用 監視 サーバ 36

mod_jk Tomcat pgpool-Ⅱ マスター 運 用設定 負荷分散 mod_jk セッションレプリケーション 参照負荷分散 DBレプリケーション スレーブ Tomcat pgpool-Ⅱ スレーブ 情報収集 mod_jk Tomcat pgpool-Ⅱ 運 用監視 サーバ 37

自 動 化 業務アプリケーション アプリケーションフレームワーク ミドルウェア (AP 基盤 ID 管理 ) ミドルウェア ( 運用 ) OS 仮想化基盤 クラウドサービス (SaaS,PaaS) ミドルウェア (AP 基盤 ID 管理 ) ミドルウェア ( 運用 ) OS クラウド基盤 個別要件対応型 ( 高信頼性要件指向 ) Oracle, (DB2), (WebSphere), JBoss, Apache JP1, (Zabbix), (Hinemos), ( バックアップ, 構成管理, 他 ) Linux, (AIX, 他 UNIX) Ansible, Chef, Serverspec 標準パターン型 ( オープンスタンダード指向 ) PostgreSQL, JBoss, Tomcat, Apache, OpenAM, OpenLDAP Zabbix, Hinemos, JobScheduler, ( バックアップ, 構成管理, 他 ) Linux, Windows srv. AWS, TEOS, (Azure), (Softlayer) (PaaS 基盤 ) サーバ / ストレージ クラウド基盤 (IaaS) 仮想化基盤 VMware VMware, (Hyper-V), (OpenStack), (Docker) ネットワーク サーバ / ストレージ ネットワーク 主要ハードウェアメーカ製品, (SDS) 主要ネットワーク機器メーカ製品, (SDN) 38

39

PostgreSQL のテーブルの様に 外部データへアクセス可能 外部データに応じた拡張機能を組み込んで使 用 データ更更新 (9.3) 外部データの継承 (9.5) 9.6 でリモートでのソート / 結合も可能に 外部データラッパ サーバプロセス postgres_fdw mysql_fdw redis_fdw PostgreSQL MySQL NoSQL(Redis) 通常のテーブル twitter_fdw file_fdw PostgreSQL Twitter CSV ファイル 外部データ 40

testdb=# CREATE EXTENSION file_fdw; testdb=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw; testdb=# CREATE FOREIGN TABLE pglog (log_time timestamp(3) with time zone, testdb(# user_name text, database_name text, process_id integer, testdb(# connection_from text, session_id text, session_line_num bigint, testdb(# command_tag text, session_start_time timestamp with time zone, testdb(# virtual_transaction_id text, transaction_id bigint, error_severity text, testdb(# sql_state_code text, message text,detail text, hint text, testdb(# internal_query text, internal_query_pos integer, context text, testdb(# query text,query_pos integer, location text,application_name text testdb(# ) SERVER pglog testdb-# OPTIONS (filename '/var/lib/pgsql/9.4/data/pg_log/postgresql-fri.csv', format 'csv'); testdb=# select log_time,user_name,database_name,process_id,error_severity, message,application_name from pglog where message like 'duration%'; -[ RECORD 1 ]-------+------------------------------------------------------ log_time 2015-11-06 08:33:31.528+00 user_name postgres database_name testdb process_id 8962 error_severity LOG message duration: 5006.931 ms statement: select pg_sleep(5); application_name psql 41

JSON 形式のデータを投 入可能 スキーマ定義が難しい 非構造化データを扱う 組み込みデータ型として JSON, JSONB を 用意 PostgreSQL で JSON を扱うメリット 構造化データと同様に SQL で操作可能 既存のテーブルとの JOIN も OK 転置インデックスが使える この要素を含むもの を問い合せる検索索が 高速 トランザクションに対応 JSON を扱う DB:MongoDB, Couchbase 等 NoSQL データベースとは重視することが違う 42

インデックス (B-Tree) 1 1000 3000 5000 欲しいレコードを特定してアクセス テーブル id = 1 id = 1000 id = 2000 id = 3000 インデックス (BRIN) ヒープブロック 0-127 最小 :1, 最大 : 900 ヒープブロック 128-255 最小 : 1000, 最大 : 3000 欲しいレコードを含むブロックまで特定してアクセス 43

SQL1つに割当るプロセスは1つ CPU が余ってても SQL 実 行行は速くならない 1つのSQLを複数プロセスで並列列処理理することが可能に (9.6 ~) 分析系の重たい SQL を速くできる可能性 サーバプロセス サーバプロセス CPU は 1 つしか使えない Big Table CPU を複数使う ワーカプロセス ワーカプロセス Big Table ワーカプロセス 従来 ( ~ 9.5) 9.6 以降降 44

PostgreSQL の標準機能 2010 年年リリースの 9.0 より実装 更更新可能なマスタは 1 台 スレーブは複数台 OK 多段のレプリケーションも可能 カスケードレプリケーション MySQL の 方が先輩 ツールやノウハウで負ける PostgreSQL PostgreSQL マスタ DB WAL レコード スレーブ DB PostgreSQL スレーブ DB WAL レコード PostgreSQL スレーブ DB メインサイト 災対サイト 45

同期レプリケーション マスタで COMMIT したデータの損失を防 止 WAL がスレーブに届くまで COMMIT を待機 46

複数同期レプリケーション 同期レプリケーションスレーブを複数稼働可能に (9.6 ~ ) 9.5 までは同時にスレーブは 1 台のみ 完全同期レプリケーション スレーブの WAL 適 用完了了するまで COMMIT を待機 どのスレーブを参照しても最新データが 見見える 更更新性能は犠牲にする覚悟が必要 47

48

ACID MVCC 標準 SQLに準拠 多様なSQLに対応 性能拡張性 CPU 数に応じたスケールアップ 巨 大テーブルを分割するパーティショニング 冗 長化による可 用性確保 周辺ツールも OSS 利利 用で低コストに実現可能 制約の少ないライセンス 利利 用は無償 保守サポートは有償 49

高可 用性と 高拡張性の両 立立 稼働率率率 99.999% と更更新処理理のスケールアウト 運 用機能 必要最低限の機能は 用意されている 運 用者にそれなりの知識識を要求する セキュリティ 監査 暗号化 50

お 金金をかければ Enterprise Edition なら RAC なら RAC レベルが要求される場 面はある でも今回のシステムで本当に必要? 51

機能の 表を作らない 不不要な機能までフラットに 比較してない? シンプルな要件に落落としこむ Oracle RAC は本当に必要? という検討から 最近は RDBMS 以外の選択肢も クラウド 分散 大規模分析 NoSQL ACID は満たさなくてもスケールさせたい 特徴を理理解し最適なものを選ぶ時代が来ている 52