Tweet
Tweet

Slide 1

Slide 1 text

Amazon RDS for PostgreSQL deep dive 〜 Amazon Redshiftとの連携 〜 Amazon Web Services Japan K.K. Solutions Architect 星野 豊

Slide 2

Slide 2 text

今回お話する内容は2016/11/12 現在の情報です

Slide 3

Slide 3 text

マネージド・サービスを使う理由 • DBA (DataBase Administrator)の仕事 1. スキーマやクエリのチューニング • DBの構造を理解した上での、適切な改善 2. DBサーバ/クラスタの管理 • 構築、キャパシティ、パフォーマンス、障害対応 • 現在、どちらに費やしている時間が多い？

Slide 4

Slide 4 text

DBAにしかできないことに注⼒する • 各データベースエンジンの特性を理解する • パフォーマンスを発揮できる使い⽅ • ログ・メトリクスから、ボトルネック特定、解消 • 各種検証、ベンチマーク • DBを中⼼にした全体的なシステムアーキテクト

Slide 5

Slide 5 text

DBAにしかできないことに注⼒する • DBAで無くても出来ること – バックアップスクリプト作成、仕掛け – 障害のあったサーバを再構築 – フェイルオーバーの仕組みを運⽤する – パッチ適応やスケールのためのメンテナンス作業 – 各種ログ・メトリクスの可視化、アラート設定

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

DBAでなくてもできること • バックアップスクリプト作成、仕掛け • 障害のあったサーバを再構築 • フェイルオーバーの仕組みを運⽤する • パッチ適応やスケールのためのメンテナンス作業 • 各種ログ・メトリクスの可視化、アラート設定 Amazon RDS Amazon DynamoDB Amazon Redshift Amazon ElastiCache AWS DMS

Slide 8

Slide 8 text

AWSのデータベースサービス群の特徴: マネージド • インスタンスの管理が不要 – sshログイン等は不可能、構築から設定まで全てマネージド • 使った分だけの料⾦体系 – 稼働時間、容量、スループット、IO等 • ⾼可⽤性 – Multi AZで⾃動フェイルオーバーや、データ冗⻑化 • バックアップ可能なものは標準で⾃動的に取得 • ログやメトリクスの収集、可視化も⾃動

Slide 9

Slide 9 text

AWSが提供するデータベースサービス 完全マネージド型で、セットアップ、運⽤、拡張が 容易なリレーショナル・データベースサービス Amazon RDS Amazon DynamoDB 完全マネージド型で、⾼速なパフォーマンス、 シームレスな拡張性と信頼性をNoSQLサービス Amazon Redshift ⾼速で管理も万全なペタバイト規模のデータウェア ハウスサービス Amazon ElastiCache 完全マネージド型で、セットアップ、運⽤、拡張が容 易なキャッシュサービス

Slide 10

Slide 10 text

Amazon RDS

Slide 11

Slide 11 text

データベース管理を簡単に • データベースを数分で作成可能 • ⾃動でパッチの適⽤ • 数クリックするだけでスケールアウト可能 • S3への継続的なバックアップ • 障害の⾃動検知と⾃動フェールオーバ

Slide 12

Slide 12 text

データベースをEC2に構築するという⽅法も • データベースをEC2上に構築するメリット – RDSが対応していないRDBMSやバージョンを選択 – 同⼀サーバにプログラムを置くなどのカスタマイズ – OSのチューニングの⾃由度が⾼い – RDSが対応していないDBパラメータの変更が可能 – ストレージ領域構成の⾃由度が⾼い カスタマイズの⾃由度か、運⽤・管理の容易性かの選択

Slide 13

Slide 13 text

オンプレミス vs. データベース on EC2 vs. RDS Power, HVAC, net Rack & stack Server maintenance OS patches DB s/w patches Database backups Scaling High availability DB s/w installs OS installation App optimization Power, HVAC, net Rack & stack Server maintenance OS patches DB s/w patches Database backups Scaling High availability DB s/w installs OS installation App optimization Power, HVAC, net Rack & stack Server maintenance OS patches DB s/w patches Database backups Scaling High availability DB s/w installs OS installation App optimization オンプレミス データベース on EC2 RDS お客様がご担当する作業 AWSが提供するマネージド機能

Slide 14

Slide 14 text

Amazon RDS: インスタンスタイプが豊富 8GB 16GB 32GB 60GB 122GB 244GB 4core 8core 16core 32core r3.8xl 2core 1core r3.4xl r3.2xl r3.xl r3.large m4.2xl m4.xl m4.large 4GB t2.small t2.micro t2.medium 1GB Memory vCPU m4.4xl m4.4xl 40core

Slide 15

Slide 15 text

DBインスタンスクラスとスペック DB Instance Class vCPU メモリ(GiB) EBS 最適化 ネットワーク db.t2.micro 1 1 無し 低 db.t2.small 1 2 無し 低 db.t2.medium 2 4 無し 低 db.t2.large 2 8 無し 中 db.m4.large 2 8 450Mbps 中 db.m4.xlarge 4 16 750Mbps ⾼ db.m4.2xlarge 8 32 1000Mbps ⾼ db.m4.4xlarge 16 64 2000Mbps ⾼ db.m4.10xlarge 40 160 4000Mbps 10Gbps db.r3.large 2 15 無し 中 db.r3.xlarge 4 30.5 500Mbps 中 db.r3.2xlarge 8 61 1000Mbps 中 db.r3.4xlarge 16 122 2000Mbps ⾼ db.r3.8xlarge 32 244 (n/a) 10Gbps ※表には記載していない旧世代インスタンスも選択可能です

Slide 16

Slide 16 text

RDSで使⽤できるディスクボリュームタイプ 項⽬ 標準(Standard) Magnetic General Purpose (GP2) プロビジョンド IOPS (PIOPS) 種類 ハードディスク SSDベース SSDベース 容量課⾦ あり（GBあたり） あり（GBあたり） あり（GBあたり） IOPS キャパシティ課⾦ なし なし あり（プロビジョニングされた IOPS単位） IOリクエスト課⾦ あり なし なし 性能 平均100IOPS〜 最⼤数百IOPS （サイズに依存する） ⾼性能＋バースト 最⼤3000IOPS（以上） (ベースパフォーマンス＋ バースト） ⾼性能 1,000〜30,000 IOPS （PIOPS設定を保証） • 標準、General Purpose(GP2)、プロビジョンドIOPSから選択 • オンラインでサイズ増加可能（※SQL Server以外） http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/CHAP_Storage.html

Slide 17

Slide 17 text

プロビジョンドIOPS ストレージの注意点 • IOPSについての注意点 – 各DBエンジンの仕様、Page Sizeや仮想化技術の違いで実際のIOPSは以下の表 のような⽬安になる – ⼩さなインスタンスタイプではストレージとの帯域不⾜で設定したIOPSに達し ない場合がある(EBS最適化ありのインスタンスを推奨) DBエンジン （最⼤サイズ、IOPS） Page Size Max IOPS PostgreSQL(6TB, 30,000) 8KB 30,000 MySQL / MariaDB (6TB, 30,000) 16KB 30,000 Oracle(6TB, 30,000) 8KB 25,000 SQL Server(4TB, 20,000) 8KB 20,000 ※ Read 50%, Write 50%の場合

Slide 18

Slide 18 text

RDSでデータベースを作成するのは簡単 • 数クリックでDBが起動 – DBエンジン – インスタンスクラス – ディスクの種類とサイズ 等を選ぶだけ • 必須機能が実装済み – バックアップ（スナップショット） • ⾃動バックアップ • ⼿動バックアップ – 同期レプリケーションよる可⽤性向上 – 監視 (CloudWatch) – 管理GUIやAPIで操作可能

Slide 19

Slide 19 text

RDSアーキテクチャ ⾃動バック アップ スナップシ ョット (⾃動/⼿動) データ同期 ⾮同期レプリケーション ⾃動フェイルオーバー Binlog (トランザクション ログ) （5分間隔) トランザクション ログ 5分に1度保存 S3 Availability Zone A Availability Zone B リードレプリカ Multi-AZ

Slide 20

Slide 20 text

マルチAZデプロイメントとは(Multi-AZ) • ワンクリックで耐障害性を向上可能なソリューション – ⾼い技術⼒を持つDBAが⾏っていた設計をそのままサービス化 – AZとはアベイラビリティゾーンの略。1つ以上のデータセンタからなる データセンタクラスタ • 同期レプリケーション＋⾃動フェイルオーバ – アプリ側での対処は必要なし(エンドポイントは変わらない） – スタンバイ状態のDBはアクセス不可 • フェイルオーバの実施タイミング – インスタンスやハードウェア障害 – パッチ適⽤などのメンテナンス時間 – ⼿動リブート時に強制フェイルオーバー指定 http://aws.amazon.com/jp/rds/details/multi-az/ Region Multi-AZ Availability zone Availability zone

Slide 21

Slide 21 text

リードレプリカ(RR)とは？ • 読み取り専⽤のレプリカDB – 5台まで増設可能（※上限緩和申請可能） • Amazon Auroraは15台まで – マルチAZとの組み合わせも可能 – マスター昇格 – RRのディスクタイプやインスタンスタイプをソース とは別のタイプに変更可能 • 想定ユースケース – 読み取りのスケーリング、BI等の解析処理の分散 – マルチAZによる耐障害性の代替ではない リードレプリカ APP APP 2 APP APP 読み書き ワークロード 読み取り ワークロード

Slide 22

Slide 22 text

DBエンジンとリードレプリカへの対応 • 対応DBエンジン – MySQL – PostgreSQL – MariaDB – ※OracleはGolden Gateの導⼊で対応可能 • MySQL/MariaDB/PostgreSQLの機能 – クロスリージョンレプリケーション（リージョンを またいだレプリケーション） – RRのカスケード – リードレプリカ側でのスナップショット実⾏

Slide 23

Slide 23 text

DBインスタンス暗号化機能 • 暗号化保存を実現 – ディスク上に暗号化されたデー タが保存 – AES-256 – AWS KMSで鍵管理が可能 • 対応インスタンスタイプ – db.m3.* / db.m4.* / db.r3.* – db.cr1.8xlarge(旧世代) • 補⾜： – インスタンス作成時のみ設定可能 （後から変更できない） – RRも同じ鍵で暗号化される

Slide 24

Slide 24 text

⾃動スナップショットとリストア • RDS標準機能として⾃動的なバックアップを提供 – ⾃動スナップショット＋トランザクションログをS3に保存 • ⾃動スナップショット – 1⽇1回⾃動取得 (バックアップウインドウで指定した時間帯） – 保存期間は最⼤35⽇分 (0⽇〜35⽇の間で設定可能) – ⼿動スナップショットは任意の時間に可能 • リストア⽅法 – リストア：スナップショットを元にDBインスタンス作成 – Point-In-Time-Recovery: • 指定した時刻（5分以前）の状態になるようDBインスタンス作成

Slide 25

Slide 25 text

スナップショットのユースケース① Production.xxx.amazonaws.com データの操作ミス等で本番インスタンスのデー タを以前の状態に戻したいケース スナップショットから新たにインスタンス起動 アプリケーションは新しいエンドポイントに接 続するように修正 ⾃動Snapshot 本番インスタンス Production2.xxx.amazonaws.com リストア or Point-in-time-recovery スナップシ ョット (⾃動)

Slide 26

Slide 26 text

スナップショットのユースケース② • スナップショットのリージョン間コピー – 別リージョンにスナップショットをコピー可能。 – 別リージョンで、スナップショットからインスタンス起動可能 ※GovCloudリージョンから、またはこのリージョンへ、DB スナップショットをコ ピーすることはできません

Slide 27

Slide 27 text

リネーム（Rename） Production.xxx.amazonaws.com Old.xxx.amazonaws.com EC2からRDSに接続する際に使⽤するエンドポイント（FQDN)を切り替える機能 ・障害復旧(リストア・リカバリ）したインスタンスの名前変更 ・⼤規模変更からの切り戻し http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RenameInstance.html

Slide 28

Slide 28 text

リネームのユースケース Production.xxx.amazonaws.com Old.xxx.amazonaws.com 障害後の復旧として 本番インスタンス prod2.xxx.amazonaws.com Production.xxx.amazonaws.com Snapshot

Slide 29

Slide 29 text

設定変更 • RDSのサーバには直接 SSHログインできない • 設定変更はパラメー ターグループ – デフォルトのひな形をコピーし て作成する • オプション機能の追加 はオプショングループ – TDEなど – 設定項⽬のオン・オフが多い

Slide 30

Slide 30 text

スケールアップ機能 • マネージメントコンソールやAPIからスケール アップ可能 – インスタンスrebootを伴う – コマンドライン(AWS CLI)からも可能 • スケールダウンも可能 – ⼀時的に⼤きくして、その後戻すといった運⽤ – 開発DBを⽇中だけ⼤きくして使わない夜間は⼩さくする運⽤ • インスタンスの変更でCPUとメモリだけでなく ディスクIO帯域やネットワーク帯域が変更になる $ aws rds modify-db-instance \ --db-instance-identifier test-db --db-instance-class db.m3.2xlarge \ --apply-immediately スケールアップ

Slide 31

Slide 31 text

DBエンジン ログ種別 保持期間 PostgreSQL (※ログの種別がない) 7⽇間※2 MySQL Error, Slow Query※1, General※1 24時間 Oracle Alert, Trace 7⽇間※2 SQL Server Error, Agent, Trace 7⽇間※2 ログアクセス機能 • 各種ログを直接参照する機能 – API経由 でダウンロード or マネジメントコンソールで表⽰ http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.html ※1 パラメータグループで有効化すると⽣成 ※2 パラメータグループで期間の調整可能

Slide 32

Slide 32 text

CloudWatch対応（監視） • 各種メトリクスを60秒間隔で取得・確認可能 – ホスト層のメトリクス(CPU, Memory Usage等 ) – ストレージのメトリクス (IOPS, Queue Depth等) – ネットワークのメトリクス (受信スループット、送信ス ループット等）

Slide 33

Slide 33 text

DBエンジン – PostgreSQL - • バージョン – 9.3.1 - 9.3.12 – 9.4.1, 9.4.4, 9.4.5, 9.4.7 – 9.5.2, 9.5.4 – 9.6.1 – 9.3→9.4, 9.4→9.5, 9.5→9.6へのバージョンアップ に対応 • 特徴的な機能 – 多くの拡張モジュールを利⽤可能 – PostGISをRDSオリジナルの拡張モジュールとして提供 => SHOW rds.extensions; （導入済モジュール一覧） => CREATE EXTENSION [拡張モジュール名];（登録して利用可能に）

Slide 34

Slide 34 text

拡張モジュール(⼀部) btree_gin earthdistance pgrowlocks sslinfo btree_gist fuzzystrmatch pg_trgm tablefunc chkpass hstore plperl tsearch2 citext intagg plpgsql unaccent cube intarray pltcl uuid-ossp dblink isn postgis plv8 dict_int ltree postgis_tiger_geocoder postgres_fdw dict_xsyn pgcrypto postgis_topology pg_stat_statements ⼿続き⾔語(ストアド プロシージャ) GIS (地理情報システ ム) オブジェクト データを暗号/複合化 • 拡張モジュール詳細は以下を参照 http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html 実⾏されたSQLの統計 情報を記録

Slide 35

Slide 35 text

新Extensionサポート • RDS for PostgreSQL: 新Extensionサポート (PostgreSQL9.4.5以上) – ip4r - 単⼀のIPv4アドレスから任意の範囲のIPv4やIPv6のアド レス帯に及ぶIPアドレスデータ型をサポート – pg_buffercache - 共有バッファキャッシュで何が起きているか をリアルタイムに確認する⽅法を提供 – pgstattuple - リレーションの物理的な⻑さ、バキュームが必要 かどうか決定するのに役⽴つ"不要"なタプルの割合などのタプ ルレベルの統計情報を⼊⼿するための各種関数を提供

Slide 36

Slide 36 text

新パラメータサポート • RDS for PostgreSQL: 新しく3つのパラメータを サポート – rds.force_admin_logging_level -RDSのインターナルユーザー (rds_admin)によるDBインスタンス内での操作を記録し、 PostgreSQLのエラーログに出⼒ – rds.force_autovacuum_logging_level - autovacuum workerに よるDBインスタンス内の全データベースでの操作を記録し、 PostgreSQLのエラーログに出⼒ – rds.rds_superuser_reserved_connections - PostgreSQLの スーパーユーザーに設定できるように、rds_superuserによる接 続を予約 (PostgreSQL9.4.5以上)

Slide 37

Slide 37 text

最新アップデート • ロジカルレプリケーション – RDS for PostgreSQLがロジカルレプ リケーションに対応 • DMSサポート – ロジカルレプリケーション機能によりRDS for PostgreSQLデータベースインスタンスをAWS Database Migration Serviceのソースデータベースとして利⽤可能 • Event Trigger – 新しいバージョンのPostgreSQLでデータベースイ ンスタンスレベルでevent triggerをサポート • RAM Disk Size – RDS for PostgreSQLでRAM diskのサイズをコン トロール可能になりました

Slide 38

Slide 38 text

拡張モニタリング 50+ system/OS metrics | sorted process list view | 1–60 sec granularity alarms on specific metrics | egress to Amazon CloudWatch Logs | integration with third-party tools

Slide 39

Slide 39 text

拡張モニタリング Process list Metrics list

Slide 40

Slide 40 text

重要なシステム／OSメトリクスに対応 User System Wait IRQ Idle CPU Utilization Rx per declared ethn Tx per declared ethn Network Num processes Num interruptible Num non-interruptible Num zombie Processes Process ID Process name VSS Res Mem % consumed CPU % used CPU time Parent ID Process List MemTotal MemFree Buffers Cached SwapCached Active Inactive SwapTotal SwapFree Dirty Writeback Mapped Slab Memory TPS Blk_read Blk_wrtn read_kb read_IOs read_size write_kb write_IOs write_size avg_rw_size avg_queue_len Device IO Free capacity Used % Used File System

Slide 41

Slide 41 text

拡張モニタリング • CloudWatch LogsからElasticsearch Service

Slide 42

Slide 42 text

Amazon Redshift

Slide 43

Slide 43 text

Amazon Redshiftの概要 • クラウド上のDWH – 数クリックで起動 – 使った分だけの⽀払い • ⾼いパフォーマンス – ハイ・スケーラビリティ • ⾼い汎⽤性 – PostgreSQL互換のSQL – 多くのBIツールがサポート

Slide 44

Slide 44 text

ノードタイプ • SSDベースのDCとHDDベースのDSから選択 – データは圧縮されて格納されるため、ストレージ総量より多くのデータが格納可能 • 最⼤128ノード：2PByteまで拡張可能 – ノードタイプと数は後から変更可能 DC1 - Dense Compute vCPU メモリ(GB) ストレージ ノード数 価格(※) dc1.large 2 15 0.16TB SSD 1~32 $0.314 /1時間 dc1.8xlarge 32 244 2.56TB SSD 2~128 $6.095 /1時間 DS2 – Dense Storage ds2.xlarge 4 31 2TB HDD 1~32 $1.190 /1時間 ds2.8xlarge 36 244 16TB HDD 2~128 $9.520 /1時間 ※価格は東京リージョンにおいて2016年11月12日時点のものです

Slide 45

Slide 45 text

Amazon Redshiftの位置づけ データ・ストアの特性に応じた使い分け Amazon DynamoDB Amazon RDS Amazon ElastiCache Amazon Redshift SQL NoSQL • 低レンテンシ • インメモリ • 3拠点間での レプリケーション • SSDに永続化 • トランザク ション処理 • 汎⽤⽤途 • 集計・分析処理 • ⼤容量データ • DWH

Slide 46

Slide 46 text

Redshiftの構成 SELECT * FROM lineitem; SQLをコンパイル、 コードを⽣成し、コン ピュートノードへ配信 CPU CPU CPU CPU CPU CPU Leaderノード Computeノード スライス＝ メモリとディスクを ノード内で分割した論 理的な処理単位 コンピュートノードの追 加でパフォーマンス向上 （スケールアウト） １つの表を各ノード のストレージに分散 して保存（シェアー ドナッシング）

Slide 47

Slide 47 text

列指向型（カラムナ） DB ・⾏指向型（他RDBMS） ・列指向型（Redshift） orderid name price 1 Book 100 2 Pen 50 … n Eraser 70 orderid name price 1 Book 100 2 Pen 50 … n Eraser 70 DWH用途に適した格納方法

Slide 48

Slide 48 text

dblinkを利⽤した、Amazon Redshiftと RDS for PostgreSQLの連携

Slide 49

Slide 49 text

ワークロードの違いを吸収 • Amazon RedshiftはMPP、シェアードナッシング のアーキテクチャ – １つのクエリを実⾏する際にも多くのノードをまたがってコン ピュートリソースを使⽤する – ⼤量のデータに対して分析するクエリ • OLTP型データベースは各クエリの処理に1つのサー バ上の部分的なリソースしか使⽤しない – 多くのOLTP型データベースはより多くの並列クエリを処理できる – 各クエリごとに消費するリソースをできるだけ⼩さくし、効率的に 実⾏できるように設計されている

Slide 50

Slide 50 text

連携を⾏うメリット • ⾼い並列アクセスが求められるダッシュボード⽤途にマテリ アライズド・ビュー経由でデータのキャッシュを作成する • ⾼い並列アクセスされるパーティション単位での表の結合を ブロックレンジインデックス（BRIN）で実現する • PL/pgSQLのユーザ定義関数（UDF）からダイナミックSQL でAmazon Redshiftをクエリする • Amazon Redshiftの後処理：例えば結果セットを PostgreSQLでJSONフォーマットに変換する

Slide 51

Slide 51 text

連携するには • postgres_fdw – foreign-data wrapper – postgres_fdwモジュールは外部テーブルの作成を可能にする。外 部テーブルはローカルにあるネイティブのテーブルと同じようにク エリ可能 – 現時点では、 アグリゲーション関数やLIMIT句等をリモートに プッシュダウン出来ないため、クエリは全てがリモート側で実⾏さ れるわけではない – 外部テーブルに対してアグリゲーションクエリを実⾏した場合、ア グリゲーションを実⾏するために必要な全データが⼀旦 PostgreSQLに全て転送され、多くの⾏を操作する場合に実⾏速度 が低下

Slide 52

Slide 52 text

連携するには • dbLink – dblinkエクステンション – クエリ全体をPostgreSQLからAmazon Redshiftに送信 – ⼤量のデータをRedshiftの機能で効率的にクエリし、結果セッ トだけをPostgreSQLの後続処理に返却 今回はこちらをご紹介

Slide 53

Slide 53 text

実際に使うには • AWS上ではとても簡単に構築しテストを⾏うこと が可能 • Amazon Redshiftを起動 • RDS PostgreSQL (9.5以降)をAmazon Redshiftと 同じAZに起動 • Amazon RedshiftへRDS for PostgreSQLインスタ ンスからアクセス出来るようにアクセス制限を設定

Slide 54

Slide 54 text

Amazon Redshiftの起動 クラスタ設定 ユーザの作成 クラスタスペックの設定 ディスク暗号化や アクセス制限の設定

Slide 55

Slide 55 text

RDS for PostgreSQLの起動 バージョン/インスタンススペック ディスク/ユーザの設定 起動するAZやアクセス制限、ディスク暗号化の設定 バックアップやモニタリング、メンテナンス周りの設定

Slide 56

Slide 56 text

後は接続するだけ! • 先程作成したAmazon Redshiftのエンドポイントを確 認して、同じく作成したRDS for PostgreSQLへ接続し 以下のクエリを実⾏ CREATE EXTENSION postgres_fdw; CREATE EXTENSION dblink; CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '', port '', dbname '', sslmode 'require'); CREATE USER MAPPING FOR SERVER foreign_server OPTIONS (user '', password '');

Slide 57

Slide 57 text

クエリを実⾏! • dbLinkの設定が完了すれば後はクエリを実⾏す るだけ！ SELECT * FROM dblink('foreign_server',$REDSHIFT$ SELECT sellerid, sum(pricepaid) sales FROM sales WHERE saletime >= '2008-01-01' AND saletime < '2008-02-01' GROUP BY sellerid ORDER BY sales DESC $REDSHIFT$) AS t1 (sellerid int, sales decimal);

Slide 58

Slide 58 text

様々なクエリを実⾏ • VIEW – SQLをより楽に書けるように VIEWを定義 CREATE OR REPLACE VIEW v_sales AS SELECT * FROM dblink ('foreign_server',$REDSHIFT$ SELECT sellerid, sum(pricepaid) sales FROM sales WHERE saletime >= '2008-01-01' AND saletime < '2008-02-01' GROUP BY sellerid ORDER BY sales DESC $REDSHIFT$) AS t1 (sellerid int, sales decimal);

Slide 59

Slide 59 text

様々なクエリを実⾏ • UDF – UDFは使⽤時 にパラメータ を渡すことが 可能 – PL/pgSQLで PostgreSQL内 にUDFを作成 CREATE OR REPLACE FUNCTION get_sales(_startdate timestamp, _enddate timestamp) RETURNS TABLE (sellerid int, sales decimal) AS $BODY$ DECLARE remote_sql TEXT; BEGIN remote_sql = FORMAT( ' SELECT sellerid, sum(pricepaid) sales FROM sales WHERE saletime >= %L AND saletime < %L GROUP BY sellerid ORDER BY sales DESC ', _startdate, _enddate); RETURN QUERY SELECT * FROM dblink('foreign_server', remote_sql) AS t1 (sellerid int, sales decimal); END; $BODY$ LANGUAGE plpgsql VOLATILE;

Slide 60

Slide 60 text

様々なクエリを実⾏ • マテリアライズド・ビューを利⽤してデータをキャッシュする – データが頻繁にアクセスされるようなケースでは、マテリアライズド・ビューはクエリ結 果をキャッシュするため、実⾏を複数回実⾏することをスキップすることが可能 – ダッシュボードで頻繁にアクセスされる⼩規模なデータをキャッシュしておく場合等に有 効 CREATE MATERIALIZED VIEW v_users_likes_by_state AS SELECT * FROM dblink('foreign_server',$REDSHIFT$ SELECT state, sum(likesports::int) sports_like_count FROM users GROUP BY state $REDSHIFT$) AS t1 (state text, sports_like_count int);

Slide 61

Slide 61 text

様々なクエリを実⾏ • マテリアライズド・ビューが保持するキャシュが有効でなくなる場 合もある。再度元のクエリーを実⾏することでマテリアライズド・ ビューのキャッシュをリフレッシュ REFRESH MATERIALIZED VIEW v_users_likes_by_state;

Slide 62

Slide 62 text

定期的にリフレッシュしたい場合は？ • アプリケーションやバッチから実⾏ – そのためにサーバを起動しておくのも… • AWS Lambdaを利⽤すれば安価に実⾏できサーバのメンテナンスも不要 var pg = require("pg"); exports.handler = function(event, context) { var conn = "pg://username:password@host:port/dbname"; var client = new pg.Client(conn); client.connect(function(err) { if (err) { context.fail("Failed" + err); } client.query('REFRESH MATERIALIZED VIEW v_users_likes_by_state', function (err, result) { if (err) { context.fail("Failed to run query" + err); } client.end(); context.succeed("Successfully Refreshed."); }); }); };

Slide 63

Slide 63 text

その他の使い⽅ • ⼤量のデータがあるようなケースでは、マテリアライズド・ビューを 利⽤するより、dblinkを利⽤してデータをPostgreSQL上にコピーする ⽅が良い場合がある – データの最新部分だけをコピーすれば良いようなケースで有⽤だが、マテリアライズ ド・ビューはリフレッシュ時にデータを先頭から全体をコピーするため、速度⾯で不 利になる • 以下のサンプル表を例に説明します CREATE TABLE sales_summary ( saletime timestamp, sellerid int, sales decimal );

Slide 64

Slide 64 text

その他の使い⽅ • PostgreSQLは先程のデータを読む際にインデックスを利⽤して最 適化を⾏う • BRINは時系列で並べられたデータにとって適切なインデックスに なる CREATE INDEX idx_sales_summary_brin ON sales_summary USING BRIN (saletime);

Slide 65

Slide 65 text

その他の使い⽅ • dblink関数を利⽤してデータを表にインサートする⽅法 • 以下のように差分でデータをインサートすることで、マテリアライ ズド・ビュー全体をリフレッシュするよりも⾼速に更新を⾏う INSERT INTO sales_summary SELECT * FROM dblink('foreign_server',$REDSHIFT$ SELECT date_trunc('hours', saletime) AS ts, sellerid, sum(pricepaid) sales FROM sales WHERE saletime >= '2008-01-01' AND saletime < '2008-02-01' GROUP BY ts, sellerid ORDER BY sales $REDSHIFT$) AS t1 (saletime timestamp, sellerid int, sales decimal);

Slide 66

Slide 66 text

Query RoutingとRewrite

Slide 67

Slide 67 text

簡単に複数のデータベースへ接続する • アプリケーションなどから、RDS for PostgreSQL とAmazon Redshiftへ⾃動で接続する – ⽤途ごとにデータベースを分ける – データベースをシャーディングする • アプリケーションから意識せずに、設定変更を最 ⼩限にする

Slide 68

Slide 68 text

pgbouncer-rr • pgbouncer-rr – https://github.com/awslabs/pgbouncer-rr-patch – Pgbouncerベース – コネクションプール • その他にも⼤きな機能 – Routing: クライアントからのクエリを⾃動的にロードバランシングをし たり、１コネクションで複数のデータベースサーバに接続を⾏う – Rewrite: データベースサーバにクエリを送信する前に書き換えを⾏う • Amazon Redshift向けで無いようなクエリを書き換えて最適化 • ⼀定数以上のデータ(N⽇以上など)をクエリしようとした場合にエラーをだす

Slide 69

Slide 69 text

pgbouncer-rr • RDS for PostgreSQLやAmazon Redshiftの振り分けにも活⽤出 来る – 複数インスタンスデプロイしてHA構成やキャパシティの増減に対応出来る構成も可能

Slide 70

Slide 70 text

注意点 • テーブル名やスキーマ名を⾒てルーティングが⾏われる • データベース間のjoinやトランザクションはサポートしてい ない • クエリは各ステートメントでauto-commit – pool_mode = statement • 複数のクエリでなるトランザクションでも、トランザクショ ン中に他のサーバにルーティングされなければ動作可能 – pool_mode = transaction

Slide 71

Slide 71 text

Routingルール • データベース情報の設定 [databases] dev = host= port=5439 dbname=dev dev.1 = host= port=5439 dbname=dev dev.2 = host= port=5439 dbname=dev • ルーティングルールの設定 – routing_rules_py_module_file = /etc/pgbouncer-rr/routing_rules.py (動的に読み込まれる) def routing_rules(username, query): if "tablea" in query: return "dev.1” elif "tableb" in query: return "dev.2” else: return None #現在のコネクションの接続先データベースを利用

Slide 72

Slide 72 text

応⽤編 • 正規表現も利⽤可能 • Pythonでロジックを書 いて振り分け – 外部のデータベースにルール を保存しておき利⽤可能 – AWSならDynamoDBに保存し boto3を使い連携 routingtable = { 'route' : [{ 'usernameRegex' : '.*', 'queryRegex' : '.*tablea.*', 'dbkey' : 'dev.1' }, { 'usernameRegex' : '.*', 'queryRegex' : '.*tableb.*', 'dbkey' : 'dev.2' } ], 'default' : None } import re def routing_rules(username, query): for route in routingtable['route']: u = re.compile(route['usernameRegex']) q = re.compile(route['queryRegex']) if u.search(username) and q.search(query): return route['dbkey'] return routingtable['default']

Slide 73

Slide 73 text

まとめ

Slide 74

Slide 74 text

• dblink拡張でAmazon Redshitに接続し、PostgreSQLの機能を活 ⽤出来る • 頻繁にアクセスされる⼩規模なデータセットをキャッシュし、⼤き いデータはマテリアライズド・ビューを作成してリフレッシュする か、データを表にコピーするかを選択することが可能 • キャッシュされていないデータへのクエリについては、通常の ビューを作成する⽅法と、UDFを作成し引数を受け取れるようにす る • dblink拡張とAmazon Redshiftの組み合わせは他にも多数の活⽤⽅ 法があり、例えばPosrtgreSQL上のPostGISなどを活⽤することが 可能になる

Slide 75

Slide 75 text

Appendix. AWS Database Migration Service

Slide 76

Slide 76 text

AWS Database Migration Service（DMS） • RDB間のデータ移⾏を⽀援 するサービス • 異機種間のデータ移⾏も対応 • 利⽤が容易 • 使った分だけの料⾦体系 DMS オンプレ RDB RDS RDB on EC2 オンプレ RDB RDS RDB on EC2 ※オンプレ to オンプレは未サポート

Slide 77

Slide 77 text

サポートするデータベース ソース ターゲット Oracle on-prem/EC2 10g, 11g, 12c Ent/SE/SEone 10g, 11g, 12c Ent/SE/SEone RDS 11g, 12c Ent/SE/SEone 11g, 12c Ent/SE/SEone MySQL on-prem/EC2 5.5, 5.6 5.5, 5.6 RDS 5.5, 5.6 5.5, 5.6 PostgreSQL on-prem/EC2 9.4 9.3以降 RDS 9.4 9.3以降 SQL Server on-prem/EC2 2005, 2008, 2008R2, 2012, 2014 Ent, Std, Workgroup, Developer 2005, 2008, 2008R2, 2012, 2014 Ent, Std, Workgroup, Developer RDS 2008R2, 2012, 2014 Ent, Std, Workgroup, Developer ※1 2008R2, 2012, 2014 Ent, Std, Workgroup, Developer Aurora RDS MySQL互換としてサポート MySQL互換としてサポート ※１：CDC利用不可

Slide 78

Slide 78 text

レプリケーションの⽅法 • Migrate existing data（フルロード） • DMSがソースDBから対象表のデータを読み取る • ターゲットDBにデータをロードする • Replicate ongoing changes（CDC） • DMSがソースDBのトランザクションログから更新差分を抽出 • 継続的にターゲット表にアプライする • ソースDBへの負荷が低いレプリケーションを実現 • 表全体をフルロードした後に、継続的にCDCも可能

Slide 79

Slide 79 text

移⾏対象 • 表定義 • インデックス定義 • データ • 制約（⼀部） DMSが移⾏するもの 移⾏しないもの • ビュー • プロシージャ • トリガー • シノニム • 制約 – 参照制約やユニーク制約

Slide 80

Slide 80 text

AWS Schema Conversion Tool（SCT） • 異なるRDB間での移⾏時に各種オブジェクトの移⾏ （変換）を補助するツール – Windows, Mac, Linuxにダウンロードして利⽤ • 移⾏対象 – 表、インデックス、トリガー、プロシージャ – 制約、ビュー • SCTが⾃動変換した型が最適とは限らないので、業 務に合わせた微調整は必要

Slide 81

Slide 81 text

【参考】Schema Conversion Toolスクリーンショット 調査対象のDBを指定 オブジェクトを調査し、変 換可能な量を表示

Slide 82

Slide 82 text

【参考】Schema Conversion Toolスクリーンショット（続き） 変換前（Oracle PL/SQL) 変換後 (Aurora Function)

Slide 83

Slide 83 text

No content