ニフクラRDB＋Pgpool-IIでスケーラブルなデータベース環境を構築

こんにちは、CRE部技術支援チームです。

データベースには、日々増えるデータ量とアプリケーションの要求に対応するために高いパフォーマンスと可用性が求めらます。データベースサーバーのダウンは、サービス提供・企業活動の停止に直結する大きな影響が生じかねません。よって、クラスタリングをはじめとする冗長化技術や個別チューニングによって、止まらない高速データベースを運用することはその企業やサービスにとっての価値を高める重要な施策といえます。

しかし、高い要求に応えられるシステムの実装には、大きなコストが伴います。

ニフクラでは、データベース環境としてニフクラRDBを提供しています。ニフクラRDBは、ニフクラが提供するリレーショナルデータベースサービスで、PostgreSQLやMySQLなどのデータベースエンジンをクラウド上で簡単に利用できるようにしたものです。自動バックアップやリードレプリカなどの機能が備わっており、信頼性と拡張性を持たせたデータベース環境を簡単に構築できます。

ニフクラRDBで提供している冗長化技術として、マスター/バックアップDB構成を可能とする、冗長化・フェイルオーバー（データ優先）がありますが、リードレプリカを含めた負荷分散構成についてはお客様の責任範囲となります。

そこで本記事では、なるべくコストをかけずにスケーラブルなデータベース環境を構築することを目的とし、ニフクラRDB（PostgreSQL）のリードレプリカを含めた冗長＋Pgpool-IIを組み合わせた構成についてご紹介します。

はじめに

Pgpool-IIとは

Pgpool-II（以降 pgpool と表記します。）は、PostgreSQLの接続プール及びロードバランシングを提供するオープンソースのミドルウェアです。複数のPostgreSQLサーバーに対してクエリの分散や負荷分散を行い、データベースのスケーラビリティーや可用性を向上させることができます。

ニフクラRDBとpgpoolを組み合わせるメリット

リードレプリカを使って、読み取り負荷を分散させることができます。
pgpoolのデータベース接続のプール機能により、接続オーバーヘッドを削減し、アプリケーションのパフォーマンスを向上させることができます。

構成イメージ

マスターDB（冗長化あり）に、リードレプリカの計3台構成とします。これをpgpoolで負荷分散します。

はじめに
- Pgpool-IIとは
- ニフクラRDBとpgpoolを組み合わせるメリット
構成イメージ
前提条件
利用リソース
環境構築
動作確認
まとめ
注意事項

前提条件

本記事は、以下の前提知識がある方を想定しています。

ニフクラの基本的なコントロールパネルの操作、サービス利用に関する知識
Linuxの基本的な操作、設定に関する知識

利用リソース

east-12にサーバー1台、ニフクラRDBを1台、リードレプリカを2台作成し、プライベートLANで接続します。

リソース	数量	用途
仮想サーバー (OS:Rocky Linux 8.7)	1	pgpool用サーバー
ニフクラRDB（DB: PostgreSQL 11.8）	1	DBサーバー（冗長構成）
リードレプリカ	2	DBサーバーのリードレプリカ
プライベートLAN	1	DB環境用ネットワーク

加えて、pgpool経由での接続確認用に、クライアント環境を個別に用意しています。クライアント環境には PostgreSQL及びpgbench をインストールしています。

環境構築

1. プライベートLANの作成

ニフクラRDB環境用のネットワークとして、プライベートLANを作成します。
本検証ではプライベートLANに付与するIPアドレス帯を「192.168.123.0/24」としています。

作成方法の詳細は以下をご参照ください。
クラウドヘルプ（プライベートLAN：作成）

2. 仮想サーバーの作成

各サーバーのホスト名と役割は以下の通りです。

ホスト名	役割	OS	アプリケーション	プライベートIP
webap1	Web/AP兼pgpoolサーバー	Rocky Linux 8.7	pgpool	192.168.123.200/24

今回はRocky Linuxを使用しているため、SSHキーの作成が必須となります。また、本検証ではサーバーに適用するファイアウォールには以下の通信を許可するよう設定しています。

ファイアウォール設定：webap1（Web/AP兼pgpoolサーバー）

INルール

プロトコル	ポート	接続元	用途
TCP	22	作業端末のグローバルIPアドレス	SSH接続
TCP	any	192.168.123.0/24	クライアントテスト及びサーバー間接続

作成方法の詳細は以下をご参照ください。　
クラウドヘルプ（SSHキー）
クラウドヘルプ（サーバーの作成）
クラウドヘルプ（ファイアウォールグループの新規作成）

3. ニフクラRDBサーバーの作成

ニフクラRDBでPostgreSQLのサーバー環境を構築します。今回は、マスター/バックアップDBとリードレプリカを2台作成します。設定パラメータは以下としました。

DB設定

DBエンジン	冗長化	DB名	自動バックアップ
PostgreSQL 11.8	冗長構成（データ優先）	pg123	YES

マスター/バックアップDB

サーバー名（役割）	IPアドレス	ポート
postgres1（VIP）	192.168.123.1/24	5432
（マスターDBの実IP）	192.168.123.11/24	-
（バックアップDBの実IP）	192.168.123.12/24	-

作成方法の詳細は以下をご参照ください。　
クラウドヘルプ（RDB：DBサーバーの作成）

リードレプリカ

サーバー名（役割）	IPアドレス
postgres2	192.168.123.2/24
postgres3	192.168.123.3/24

作成方法の詳細は以下をご参照ください。　
クラウドヘルプ（RDB：DBサーバーの操作）

DBファイアウォール設定（共通）

INルール

接続元種別	接続元	用途
CIDR	192.168.123.0/24	クライアントテスト及びサーバー間接続

作成方法の詳細は以下をご参照ください。　
クラウドヘルプ（RDB：DBファイアウォールの作成）

4. pgpool の設定：webap1（Web/AP兼pgpoolサーバー）

執筆時点で最新安定版のバージョン4.4.2を以下手順にてインストールします。

設定後の構成概要
pgpool設定後の概要図

pgpoolのインストール

pgpool公式の開発コミュニティより、リポジトリRPMをインストールします。

# dnf install https://www.pgpool.net/yum/rpms/4.4/redhat/rhel-8-x86_64/pgpool-II-release-4.4-1.noarch.rpm'

pgpoolをインストールします。（pgXX には、PostgreSQLのメジャーバージョンにあわせた数値を指定）

# dnf install pgpool-II-pg11

pgpoolの設定

pgpoolの設定ファイルを編集し、以下の設定を行います。

/etc/pgpool-II/pgpool.conf

backend_clustering_mode = 'streaming_replication'
statement_level_load_balance = on
listen_addresses = '*'
port = 9999

backend_hostname0 = '192.168.123.1'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALWAYS_PRIMARY | DISALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.123.2'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'DISALLOW_TO_FAILOVER'

backend_hostname2 = '192.168.123.3'
backend_port2 = 5432
backend_weight2 = 1
backend_flag2 = 'DISALLOW_TO_FAILOVER'

enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
ssl = off

load_balance_mode = on
ignore_leading_white_space = on
white_function_list = 'count,max,min,avg,sum'

fail_over_on_backend_error = off
master_slave_mode = off
master_slave_sub_mode = 'stream'

sr_check_period = 0
health_check_period = 0

failover_command = ''
follow_master_command = ''

設定内容の補足

listen_addresses: pgpoolが接続を許可するアドレスを設定します。今回は'*'を指定して、すべてのアドレスからの接続を許可します。
port: pgpoolの待ち受けポート番号を設定します。サーバーのIPと当port番号がユーザー側への公開インターフェースとなります。デフォルトのポート番号9999としています。
backend_hostnameX: ニフクラRDBのマスターとリードレプリカのホスト名またはIPアドレスを設定します。Xは、0から始まる連番です。例えば、マスターがbackend_hostname0、リードレプリカがbackend_hostname1 及び 2です。
backend_portX: ニフクラRDBのポート番号を設定します。デフォルトのPostgreSQLポートは5432です。
backend_weightX: バックエンドの負荷分散の比率を指定します。今回は'1' を指定して、各ホスト均等に負荷分散されるよう設定します。
backend_flagX: フェイルオーバーはニフクラRDBに任せますので、pgpoolからみたプライマリノードは固定とし、フェイルオーバーさせない設定である、'ALWAYS_PRIMARY | DISALLOW_TO_FAILOVER' を設定します。（プライマリノードはVIPの 192.168.123.1 であり、ニフクラRDB側で冗長構成となっている＝フェイルオーバーの必要がないため）
load_balance_mode: 負荷分散モードを有効にする場合はonを設定します。
enable_pool_hba: データベース接続の認証を有効にする場合はonを設定します。その場合は、pool_hba.conf に接続を許可するユーザーとアドレスを記述します。

pgpoolの起動確認

設定完了後、pgpoolを起動し、ステータスを確認します。

# systemctl start pgpool

エラーが出ないこと

# systemctl status pgpool

ステータスが Active: active (running) ～となっていること

以上で、pgpoolのインストールは完了です。

動作確認

pgpool

pgpoolから、各DBサーバーが認識されているかを確認します。

 psql -h 192.168.123.200 -p 9999 -U postgres -c "show pool_nodes"

出力結果（横に長いので一部省略＆2段に分割表示しています）

 node_id |   hostname    | port | status | pg_status | lb_weight | 
---------+---------------+------+--------+-----------+-----------+
 0       | 192.168.123.1 | 5432 | up     | unknown   | 0.333333  | 
 1       | 192.168.123.2 | 5432 | up     | unknown   | 0.333333  | 
 2       | 192.168.123.3 | 5432 | up     | unknown   | 0.333333  | 
(3 rows)

（続き）

| role    | pg_role | select_cnt | load_balance_node | 
+---------+---------+------------+-------------------+
| primary | unknown | 0          | false             |
| standby | unknown | 0          | true              |
| standby | unknown | 0          | false             |

先ほど設定した内容が反映されており、以下ステータスになっています。

status: up となっています。
lb_weight: 0.333333 となっており、各ホスト等しい割合の負荷分散設定となっています。
role: プライマリ固定設定のマスターDBが primary となっています。
select_cnt: 今はゼロとなっていますが、実際にクエリが割り振られると、こちらのカウンターが増えていきます。
load_balance_node: true となっているノードは常に1つであり、lb_weight の設定内容に従い true となるノードが変動します。

DBへの接続確認

クライアントから、pgpool経由でDBに接続できるかを確認してみます。クライアントにはコマンドラインツールを使用するためPostgreSQLをインストールしています。

# psql -h 192.168.123.200 -p 9999 -U postgres pg123

psql (10.23, server 11.8)
Type "help" for help.

pg123=> select 1;
 ?column? 
----------
        1
(1 row)

pg123=> \q

接続できました。pgpool で振り分けの状況を確認してみます。

# psql -h 192.168.123.200 -p 9999 -U postgres -c "show pool_nodes" 

 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node |
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+
 0       | 192.168.123.1 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 0          | false             |
 1       | 192.168.123.2 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 1          | true              |
 2       | 192.168.123.3 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 0          | false             |
(3 rows)

node_id 1であるリードレプリカの select_cnt が1増えました。postgres2 に接続していたことが確認できます。

次に、クライアントのpgbenchを使って、テストクエリを流してみます。

リードクエリのテスト

5ユーザーからそれぞれ100件のリードクエリ（=計500件）を送出

# pgbench -S -c 5 -t 100 -R 10 -U postgres -h 192.168.123.200 -p 9999 -d pg123

pgpool で振り分けの状況を確認してみます。

# psql -h 192.168.123.200 -p 9999 -U postgres -c "show pool_nodes" 

 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node |
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+
 0       | 192.168.123.1 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 167        | false             |
 1       | 192.168.123.2 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 163        | false             |
 2       | 192.168.123.3 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 172        | true              |
(3 rows)

各ノード、大体同じような割合でクエリが割り振られており、負荷分散されていることが確認できます。

更新クエリのテスト

5ユーザーから100件の更新クエリ（=計500件）を送出

#  pgbench -c 5 -t 100 -R 10 -U postgres -h 192.168.123.200 -p 9999 -d pg123

pgpool で振り分けの状況を確認してみます。

# psql -h 192.168.123.200 -p 9999 -U postgres -c "show pool_nodes" 

 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | 
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-
 0       | 192.168.123.1 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 667        | true              |
 1       | 192.168.123.2 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 163        | false             |
 2       | 192.168.123.3 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 172        | false             |
(3 rows)

更新クエリはprimaryノードであるマスターDB postgres1 のみに振られていることが確認できます。

フェイルオーバー

次に、マスターDB フェイルオーバー時の挙動を確認してみます。

まず、正常時のマスターDBの状態をコントロールパネルから確認しておきます。

IPアドレス：192.168.123.11 がマスターDBとしてVIPと紐づいて動作しています。

続いて、マスターDBをフェイルオーバーさせます。

フェイルオーバー手順

「postgres1」にチェックを入れる

「選択したDBサーバーの操作」より、「DBサーバー再起動」を選択

「フェイルオーバーを通して再起動する」 → 「DB通常再起動」 → 「再起動する」をそれぞれ選択し、「OK」で再起動を実施

再起動中、環境にもよりますが、フェイルオーバーにより数十秒のDBサービス断が発生します。

フェイルオーバー完了後の状態を確認します。

フェイルオーバー動作により、IPアドレス：192.168.123.12 がマスターDBとしてVIPと紐づいていることが確認できます。

フェイルオーバー完了後、再度クライアントのpgbenchを使って、テストクエリを流します。

リードクエリのテスト

5ユーザーからそれぞれ100件のリードクエリ（=計500件）を送出

psql -h 192.168.123.200 -p 9999 -U postgres -c "show pool_nodes" 

 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node |
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+
 0       | 192.168.123.1 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 186        | false             |
 1       | 192.168.123.2 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 139        | true              |
 2       | 192.168.123.3 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 176        | false             |
(3 rows)

更新クエリのテスト

5ユーザーから100件の更新クエリ（=計500件）を送出

psql -h 192.168.123.200 -p 9999 -U postgres -c "show pool_nodes" 

 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node |
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+
 0       | 192.168.123.1 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 686        | false             |
 1       | 192.168.123.2 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 140        | false             |
 2       | 192.168.123.3 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 176        | true              |
(3 rows)

フェイルオーバー前と同様の結果となり、引き続きサービスできていることが確認できました。

まとめ

今回は、ニフクラRDB（PostgreSQL）のリードレプリカを含めた冗長＋pgpoolを組み合わせた構成についてご紹介しました。これら機能の実装により、運用中においても、様々な状況に柔軟に対応できます。

DBサーバー、リードレプリカそれぞれの単体性能スケールアップ
リードレプリカ増設とpgpool負荷分散設定追加によるシステムキャパシティ向上
pgpoolサーバーの単体スケールアップや冗長構成による可用性向上

本記事でご紹介したような基本的な動作確認だけであれば、比較的容易に実装可能であると感じました。お客様要件や構成により設定チューニングは必要ですが、ご参考いただければ幸いです。

ここまで読んでいただきありがとうございました。

注意事項

本記事については検証結果の１つとなります。実際に検討される場合は、事前にそれぞれの要件を確認の上、実装してください。
本記事ではOS上の操作についても記載していますが、ニフクラではOS以上はご利用者様の責任範囲となりますのでご留意ください。
本記事での各ソフトウェアの設定パラメータはテスト用となります。実際に構築される場合は、詳細パラメーターのチューニング、セキュリティなど、要件に応じた設定を検討してください。
本記事で記載した各サービス/ニフクラの機能等は、2023年4月時点の情報です。利用時には各サービス/ニフクラの機能の最新情報をご確認いただきご利用ください。