1. ライセンス

本作品はCC-BYライセンスによって許諾されています。 ライセンスの内容を知りたい方は こちら でご確認ください。 文書の内容、表記に関する誤り、ご要望、感想等につきましては、PGEConsのサイト を通じてお寄せいただきますようお願いいたします。

  • MySQLは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。 文中の社名、商品名等は各社の商標または登録商標である場合があります。
  • Oracleは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。 文中の社名、商品名等は各社の商標または登録商標である場合があります。
  • PostgreSQLは、PostgreSQL Community Association of Canadaのカナダにおける登録商標およびその他の国における商標です。
  • TPC, TPC Benchmark,TPC-C, TPC-E, tpmC, TPC-H, QphHは米国Transaction Processing Performance Councilの商標です
  • その他、本資料に記載されている社名及び商品名はそれぞれ各社が 商標または登録商標として使用している場合があります 。

2. はじめに

2.1. PostgreSQLエンタープライズコンソーシアムとWG3について

PostgreSQLエンタープライズコンソーシアム(略称 PGECons)は、PostgreSQL本体および各種ツールの情報収集と提供、整備などの活動を通じて、ミッションクリティカル性の高いエンタープライズ領域へのPostgreSQLの普及を推進することを目的として設立された団体です。

PGECons 技術部会ではPostgreSQLの普及に資する課題を活動テーマとし、3つのワーキンググループで具体的な活動を行っています。

  • WG1（新技術検証ワーキンググループ）
  • WG2（移行ワーキンググループ）
  • WG3（課題検討ワーキンググループ）

これら3つのワーキンググループのうち、WG1、WG3については 2015 年度まではそれぞれ、「性能ワーキンググループ」、「設計運用ワーキンググループ」という名称で活動してきました。2016年度は、従来の活動領域を広げる意図のもとでそれらを再定義し、上記のような名称に改めました。

これに伴い、WG3ではPostgreSQLの設計運用を中心としたさまざまな課題の解決のための調査検証を行い、PostgreSQLが広く活用される事を推進していくこととしました。

2.2. 本資料の概要と目的

本資料はWG3の2019年度の活動として、機械学習の活用によりPostgreSQLパラメータチューニングの自動化が可能かを評価した結果についてまとめたものです。

2.3. 本資料の構成

  • はじめに
  • 概要
  • 既存手法
  • 評価
  • まとめ
  • 課題
  • 著者

2.4. 想定読者

本資料の読者は以下のような知識を有していることを想定しています。

  • DBMSを操作してデータベースの構築、保守、運用を行うDBAの知識
  • PostgreSQLを利用する上での基礎的な知識

3. 概要

PostgreSQLには利用するメモリ量やディスクへのデータ書き込み頻度などを制御する300以上のパラメータが存在し、利用用途に応じたチューニングが可能です。 数百以上のパラメータを適切に設定するため、PostgreSQLマニュアルの情報やPostgreSQL有識者の経験則などを活用し、パラメータチューニング(パラメータ最適化)を実施します。 しかしながら、PostgreSQLの動作環境やスキーマ、レコード数、ワークロードなどに依存し、最適なパラメータ設定値が変わるため、パラメータの設定値変更、測定、効果の確認を繰り返し行いながら、最適なパラメータ値を得る必要があります。このようにパラメータチューニング作業は時間とコストがかかる作業となっています。

機械学習の分野では予測精度の高いモデル開発のため、ハイパーパラメータ [1] のチューニングは課題として認識されており、 ハイパーパラメータを自動で最適化するアルゴリズムを実装したソフトウェアが開発されています。

[1]パイパーパラメータ: 機械学習アルゴリズムが持つパラメータの中で利用者が調整する必要のあるもの。パイパーパラメータの値によってモデルの予測精度などが変化するため適切な値を設定する必要がある。

今回の検証では、ハイパーパラメータの自動最適化フレームワークの1つであるOptunaを活用し、PostgreSQLのパラメータチューニングが可能であるかを評価しました。 評価のためにOptunaを利用したPostgreSQLパラメータチューニングを行う実験的なプログラム(postgres_opttune)を利用しました。

ソフトウェア名 postgres_opttune
ライセンス Apache License 2.0
概要
ハイパーパラメータの自動最適化フレームワークの1つであるOptunaを活用し、
PostgreSQLのパラメータチューニングを行うソフトウェア
URL https://github.com/ssl-oyamata/postgres_opttune
備考 現時点(2020/01/28)では検証用ソフトウェア

4. 既存手法

PostgreSQLパラメータチューニングを行う主な既存ソフトウェアを下表に示します。

今回の検証では、PgTuneと機械学習を用いたチューニング手法(postgres_opttune)を比較することにしました。 OtterTuneはPostgreSQL 12に未対応のため、今回の検証では評価を見送りました。

ソフトウェア名 PgTune
ライセンス The MIT License (MIT)
開発元 Alexey Vasiliev氏など
概要
PostgreSQLが動作している環境(OS種別、搭載メモリ、CPU数)を元にパラメータ設定値を算出するソフトウェア
PostgreSQL対応バージョン : 9.2以降(最新版の12にも対応)
URL https://github.com/le0pard/pgtune/blob/master/LICENSE
備考 Webサービス (https://pgtune.leopard.in.ua/#/) として公開されている。

ソフトウェア名 OtterTune
ライセンス Business Source License 1.1
開発元 Carnegie Mellon Database Group
概要
機械学習を活用し、DBMSのパラメータチューニングを自動化するをソフトウェア。
PostgreSQL,MySQL,Oracle Databaseなどのパラメータチューニングが可能で、
機械学習で利用する学習データはOtterTune自身が収集する。
URL https://github.com/cmu-db/ottertune
備考 ー

5. 評価

5.1. 概要

今回の評価ではベンチマークツールであるoltpbenchに実装されたtpccのベンチマークシナリオを実行し、 1秒毎の処理トランザクション数(TPS（Transactions Per Second）)が最大になるPostgreSQLパラメータの値をpostgres_opttuneを用いて自動算出しました。 postgres_opttuneにより、算出されたPostgreSQLパラメータ値の性能評価を行うため、既存手法(PgTune)とTPSを比較しました。

5.2. 検証構成

この章では、postgres_opttuneでパラメータチューニングを行う際の環境や使用したソフトウェアについて紹介します。 今回の評価試験では、チューニング対象となるデータベースサーバと、 チューニングソフトウェアであるpostgres_opttuneとベンチマークツールであるoltpbenchが動作するクライアントサーバで構成されています。

5.2.1. ハードウェア構成

評価に用いた環境は下表の通りです。


_images/param_tune_environment.png

表 5.1 ハードウェア構成
環境 OS CPUコア数 メモリ(GiB) ストレージの種類
データベースサーバ CentOS 7 4 8 SSD
クライアントサーバ CentOS 7 4 8 SSD

5.2.2. ソフトウェア構成

評価で用いたソフトウェアは下表の通りです。


表 5.2 パラメータチューニングに用いるソフトウェア
ソフトウェア バージョン 概要 関連サイト
postgres_opttune － 機械学習を用いたPostgreSQL用のパラメータチューニングツール https://github.com/ssl-oyamata/postgres_opttune
oltpbench － データベースベンチマークフレームワークであり、いくつかのベンチマークを標準で実装 https://github.com/oltpbenchmark/oltpbench
PostgreSQL 12.1 リレーショナルデータベース管理システム(RDBMS) https://www.postgresql.org/

5.2.3. 評価条件

評価時に利用した各ソフトウェアの設定値を記載します。

5.2.3.1. postgres_opttune

postgres_opttuneは下記の処理を指定された試行回数分実行し、 ベンチマークのTPSが最大となる最適なPostgreSQLパラメータ値を探索します。

  1. 最適化アルゴリズムを利用して試行するPostgreSQLパラメータ値を算出
  2. 算出されたパラメータ値をPostgreSQLに反映
  3. PostgreSQLの再起動およびファイルシステムキャッシュのクリア
  4. ベンチマークを実行し、TPSを取得

本件ではベンチマークに使用するソフトウェアと、パラメータチューニングに利用する最適化アルゴリズム、チューニングの試行回数を下表のように設定しました。


表 5.3 postgres_opttune ベンチマーク設定
ベンチマークソフトウェア 最適化アルゴリズム 試行回数
oltpbenchmark TPE [1] 200

[1]TPE(Tree-structured Parzen Estimator): Optunaで用いられているハイパーパラメータを効率的に探索するためのアルゴリズムの一種。 観測点から確率的に良質な点を予測するベイス最適化と同じ考え方で探索を行うが、確率密度関数を用いた評価により、 結果のばらつきも少なく、コンスタントに最適なパラメータを探索できることが特徴。

5.2.3.2. oltpbench

oltpbenchで実行するベンチマークの設定値は下表の通りです。


表 5.4 oltpbenchmark ベンチマーク設定
データベースタイプ ベンチマーク トランザクション分離レベル スケールファクタ 接続数 実行時間(sec)
postgres tpcc TRANSACTION_READ_COMMITTED 50 100 1800

5.2.3.3. PostgreSQL

postgres_opttuneのチューニング対象としたパラメータの情報を下表に記載しています。 postgres_opttuneは各パラメータの設定値を、最大値、最小値の範囲でチューニングします。


表 5.5 postgres_opttune チューニング対象パラメータ
パラメータ 最小値 最大値 デフォルト値 データ型
bgwriter_lru_maxpages 0 1000 100 integer
checkpoint_completion_target 0.1 0.9 0.5 float
checkpoint_timeout 1min 30min 5min time
default_statistics_target 100 2048 100 integer
effective_cache_size 4GB 8GB 4GB bytes
effective_io_concurrency 1 1000 1 integer
max_parallel_maintenance_workers 0 8 2 integer
max_parallel_workers_per_gather 0 8 2 integer
max_wal_size 256MB 16GB 1GB bytes
random_page_cost 1 10 4.0 float
shared_buffers 128MB 6GB 128MB bytes
temp_buffers 8MB 1GB 8MB bytes
wal_buffers 1MB 256MB 16MB bytes
wal_compression
on
off
on
off
 off boolean
wal_writer_delay 10ms 10s 200ms time
work_mem 4MB 1GB 4MB bytes

5.3. 評価結果

postgres_opttuneを用いたパラメータチューニング結果を下図に記載します。 横軸は試行回数(#Trials)、縦軸はTPS(Objective Value)を示します。 試行回数を重ねることで、性能(TPS)が向上していることが確認できます。


_images/param_tune_result_testA_1.png

注釈

Objective Value : ハイパーパラメータの自動最適化フレームワークであるOptunaは、ユーザが定義した目的値(Objective Value)が最大(もしくは最小)になるようにパラメータを最適化していきます。 本検証では、TPC-Cベンチマークシナリオ実行時のTPSが最大化されるよう実装しており、TPSがObjective Valueに該当します。


postgres_opttuneを用いて最適化したパラメータを下記に示します。 併せて既存の手法との比較を行うため、評価環境の情報を基にPGTuneによって算出されたパラメータとデフォルト状態でのパラメータ値を記載しています。


表 5.6 PostgreSQL 12 チューニング結果
パラメータ postgres_opttune PGTune default
bgwriter_lru_maxpages 469 （100） （100）
checkpoint_completion_target 0.6589237936891297 0.9 （0.5）
checkpoint_timeout 1740s （5min） （5min）
default_statistics_target 100 100 （100）
effective_cache_size 6623MB 6GB （4GB）
effective_io_concurrency 613 200 （1）
maintenance_work_mem （64MB） 512MB （64MB）
max_parallel_maintenance_workers 4 （2） （2）
max_parallel_workers_per （8） 4 （8）
max_parallel_workers_per_gather 5 2 （2）
max_wal_size 12359MB 4GB （1GB）
max_worker_process （8） 4 （8）
min_wal_size （80MB） 2GB （80MB）
random_page_cost 7.477068166087535 1.1 （4）
shared_buffers 5216MB 2GB （128MB）
temp_buffers 689MB （8MB） （8MB）
wal_buffers 95MB 16MB （4MB）
wal_compression on （off） （off）
wal_writer_delay 7s （200ms） （200ms）
work_mem 766MB 10485kB （4MB）

※（）内のパラメータはデフォルト値


postgres_opttuneにて最適化されたパラメータを用いてoltpbenchを実行し、性能測定を行いました。 併せてPGTuneによって算出されたパラメータとデフォルト状態でのパラメータでの性能測定も行いました。


(測定方法)
データロード
./oltpbenchmark -b tpcc -c config/sample_tpcc_config.xml --create=true --load=true

(データロード後) VACUUM ANALYZE
psql -d <データベース> -c "VACUUM ANALYZE"

PostgreSQL再起動
pg_ctl restart -D <データベースクラスタ>

キャッシュクリア
echo 1 > /proc/sys/vm/drop_caches

ベンチマーク実行
./oltpbenchmark -b tpcc -c config/sample_tpcc_config.xml --execute=true -s 5 -o outputfile

_images/param_tune_result_testA.png

表 5.7 oltpbenchmark測定結果
  postgres_opttune PGTune default
1回目 598.57 243.43 49.47
2回目 646.31 224.23 45.35
3回目 682.56 221.75 95.78
平均値 642.48 229.80 63.53

5.4. 考察

postgers_opttuneでは性能(TPS値)を基にパラメータチューニングを実施しているため、性能が低下するチェックポイント処理を極力行わないようにパラメータがチューニングされています。


表 5.8 PostgreSQL 12 チューニング結果(チェックポイント処理の関連パラメータ抜粋)
パラメータ postgres_opttune PGTune default
checkpoint_timeout 1740s （5min） （5min）
max_wal_size 12359MB 4GB （1GB）

そのため、算出されたパラメータではPostgreSQLクラッシュ時のリカバリ処理に時間を要することが推測されます。 postgres_opttuneを用いたパラメータチューニングには上記のような課題がありますので、使用する際には別途リカバリに要することができる最大許容時間を基にチェックポイントに関連するパラメータを算出する必要があります。

下記は、パラメータチューニングの際に試行されたパラメータの評価情報になります。左軸のObjective Valueが性能(TPS値)を表しており、その時の各パラメータ値が線で結ばれています。 線の色が濃い程、性能が高いことを表していますので、この評価情報から性能が向上した際の各パラメータの評価状況が確認できます。 今回の評価結果からはcheckpoint_timeoutおよびmax_wal_sizeを増やすことで性能が向上しているので、これらのパラメータを増やすことでpostgres_opttuneでの評価が高くなっていると判断できます。


_images/param_tune_result_testA_2.png

5.5. 追加検証

性能測定結果からの考察より、チェックポイント処理による性能影響が大きいと判断し、postgres_opttuneのチューニング対象から除外することとしました。

5.5.1. postgres_opttune

postgres_opttuneによるチューニング試行回数を200->100に変更しました。


表 5.9 postgres_opttune ベンチマーク設定
ベンチマークソフトウェア アルゴリズム 試行回数
oltpbenchmark TPE 100

5.5.2. oltpbench

oltpbenchの項目では、上述の検証と同様の設定とします。

5.5.3. PostgreSQL

チェックポイント処理に影響するパラメータ(checkpoint_timeout, max_wal_size)を postgres_opttuneのチューニング対象から除外しました。

表 5.10 postgres_opttune チューニング対象パラメータ（追加検証）
パラメータ 最小値 最大値 デフォルト値 データ型
bgwriter_lru_maxpages 0 1000 100 integer
checkpoint_completion_target 0.1 0.9 0.5 float
default_statistics_target 100 2048 100 integer
effective_cache_size 4GB 8GB 4GB bytes
effective_io_concurrency 1 1000 1 integer
max_parallel_maintenance_workers 0 8 2 integer
max_parallel_workers_per_gather 0 8 2 integer
random_page_cost 1 10 4.0 float
shared_buffers 128MB 6GB 128MB bytes
temp_buffers 8MB 1GB 8MB bytes
wal_buffers 1MB 256MB 16MB bytes
wal_compression
on
off
on
off
 off boolean
wal_writer_delay 10ms 10s 200ms time
work_mem 4MB 1GB 4MB bytes

5.6. 追加検証評価結果

チェックポイント処理に影響するパラメータ(checkpoint_timeout, max_wal_size)をチューニング対象から除外し、
postgres_opttuneを用いてチューニングを行った結果は下記の通りです。

_images/param_tune_result_testB_1.png

表 5.11 PostgreSQL 12 チューニング結果(追加検証)
パラメータ postgres_opttune PGTune default
bgwriter_lru_maxpages 122 （100） （100）
checkpoint_completion_target 0.6933055737323116 0.9 （0.5）
checkpoint_timeout （5min） （5min） （5min）
default_statistics_target 120 100 （100）
effective_cache_size 4621MB 6GB （4GB）
effective_io_concurrency 951 200 （1）
maintenance_work_mem （64MB） 512MB （64MB）
max_parallel_maintenance_workers 1 （2） （2）
max_parallel_workers_per （8） 4 （8）
max_parallel_workers_per_gather 8 2 （2）
max_wal_size 4GB 4GB （1GB）
max_worker_process （8） 4 （8）
min_wal_size （80MB） 2GB （80MB）
random_page_cost 1.2290242340087807 1.1 （4）
shared_buffers 4252MB 2GB （128MB）
temp_buffers 704MB （8MB） （8MB）
wal_buffers 224MB 16MB （4MB）
wal_compression on （off） （off）
wal_writer_delay 8s （200ms） （200ms）
work_mem 173MB 10485kB （4MB）

※（）内のパラメータはデフォルト値


同様にpostgres_opttuneで最適化されたパラメータでoltpbenchを実行し、性能測定を行います。 性能測定では、postgres_opttuneで最適化されたパラメータに加えてチェックポイント処理に関連するパラメータをPGTuneと同じ値(checkpoint_timeout=5min, max_wal_size=4GB)に設定し、 チェックポイントの発生頻度をPGTuneと同程度としました。


_images/param_tune_result_testB.png

表 5.12 oltpbenchmark測定結果（追加検証）
  postgres_opttune PGTune default
1回目 287.58 243.43 49.47
2回目 250.40 224.23 45.35
3回目 323.63 221.75 95.78
平均値 287.20 229.80 63.53

5.7. 考察(追加検証)

チェックポイント処理に関連するパラメータをpostgres_opttuneのチューニング対象から除外したことで、チェックポイント処理を回避することで得られていた性能の優位性がなくなり、 最初に行った検証結果のような性能差は見られなくなりました。それでもPGTuneと比べて若干の性能向上を確認することができました。 今回の追加検証で確認できたようにチェックポイント処理は性能とPostgreSQLクラッシュ時のリカバリ時間とのトレードオフとなるため、これらを考慮したパラメータチューニングが今後の課題だと考えています。

6. まとめ

今年度検証した機械学習を用いたPostgreSQLパラメータチューニングの活動において、次のことを確認することができました。

  • Optunaに実装されたパラメータの最適化アルゴリズム(TPE)をPostgreSQLのパラメータチューニングに適用することで、特定のベンチマークにおけるトランザクション処理数(TPS)を改善することが可能である。
  • 特定のベンチマークにおけるトランザクション処理数を最適化する目的で、最適化アルゴリズム(TPE)を利用し、PostgreSQLのパラメータをチューニングした場合、性能劣化が発生するチェックポイントの発生を回避するためのチューニングが実施される。
  • チェックポイント関連のパラメータは、PostgreSQLクラッシュ時のリカバリ時間に影響するため、トランザクション処理数以外の指標を基に最適化を行う実装を検討する必要がある。

今年度検証した特定のベンチマークにおけるトランザクション処理数(TPS)を改善するなど、 最適化対象の処理を定義できれば、PostgreSQLのパラメータチューニング作業を自動化することができ、 パラメータチューニングにかかる作業時間や人的コストを削減可能と考えている。 ただし、機械学習を用いてパラメータの最適化を行うためにある程度のチューニング時間が必要です。

また、単位時間当たりのトランザクション処理数を改善するにあたり、次のPostgreSQLパラメータの設定値の影響が大きいことを再確認することができました。

  • チェックポイントの実行頻度を制御するパラメータ(max_wal_sizeパラメータ、checkpoint_timeoutパラメータ)
  • メモリサイズを制御するパラメータ(shared_buffersパラメータ、wal_buffersパラメータ)

今年度の検証時に上述したチェックポイントの実行頻度を制御するパラメータチューニングの課題以外にも複数の課題が見つかっており、 来年度以降も機械学習を用いたPostgreSQLパラメータチューニングを検討し、パラメータチューニングの自動化を目指したいと考えております。

7. 課題

2019年度の検証で挙がった課題を下表に示します。
下表の課題については来年度以降の活動で検討を進めたいと考えています。

表 7.1 機械学習を用いたパラメータチューニングの課題
項番 課題 概要 備考
1 PostgreSQLクラッシュ時のリカバリ時間を考慮したパラメータチューニング
チェックポイントの実行頻度に影響するcheckpoint_timeoutパラメータ、max_wal_sizeパラメータの設定値を、PostgreSQLクラッシュ時のリカバリに要する時間を基に決定する。
まずは、checkpoint_timeoutパラメータ、max_wal_sizeパラメータの設定値とクラッシュリカバリ時間の関係を明らかにする必要がある。
 postgres_opttuneによる自動チューニングでは、利用者が指定した最大リカバリ許容時間を基にcheckpoint_timeoutパラメータ、max_wal_sizeパラメータの、最大値を決定し、パラメータの自動チューニングを行う仕組みを検討する。
2 データベース初期化時に決定されるパラメータのチューニング 今年度の検証ではinitdbを実行する際に決定されるblock_sizeパラメータ(ブロックサイズ)、wal_segment_size(WALファイルのサイズ)はチューニング対象外としている。上記パラメータを含む形で検討を行いたい。
－
3 その他最適化アルゴリズムとの比較検証 今年度の検証ではOptunaに搭載されたTPE(Tree-structured Parzen Estimator)を最適化アルゴリズムとして利用したが、Optunaにて利用可能なSkopt(ガウス過程等を使った最適化ライブラリ)やニューラルネットワーク、強化学習など別のアルゴリズムなどの評価は実施できていない。 アルゴリズム毎に必要となるサンプル数(学習データ数)や処理性能の比較を行う。
4 別ワークロードでのチューニング評価 今年度の検証ではoltpbench (https://github.com/oltpbenchmark/oltpbench) のtpccシナリオを利用して、評価したが別のワークロードでの評価を行う。
－
5 SQLの最適化検討
SQLの処理時間を機械学習を用いて最適化する手法について検討したい。
SQL処理時間の最適化に必要な環境情報(インデックス定義やPostgreSQLのコスト調整パラメータなど)を整理し、SQLの処理時間が短縮できるか否かを検討したいと考えている。
－

8. 著者

（企業・団体名順）

版 所属企業・団体名 部署名 氏名
第1.0版
(2019年度WG3)
 株式会社富士通ソーシアルサイエンスラボラトリ ソリューション開発センター ソリューション開発部 小山田 政紀
株式会社富士通ソーシアルサイエンスラボラトリ ソリューション開発センター ソリューション開発部 汪 キン垠
株式会社富士通ソーシアルサイエンスラボラトリ ソリューション開発センター ソリューション開発部 樊 小楚