
- EDB
- PostgreSQL
新ツール Postgres Workload Report によるパフォーマンス診断~データベース管理の未来を共に創る!~
EDB Postgres Workload Reportsは、Postgresデータベースのパフォーマンス診断とトラブルシューティングを強化する新しいツールです。OracleのAWRに似た詳細なレポートを提供し、データベースの問題を迅速に特定・解決できるようサポートします。本記事では概要と利用手順をご紹介します。
|
この記事は
PostgreSQL Advent Calendar 2016
の10日目です。
以前、psycopg2を使用して
PythonからPostgreSQLへ接続する方法
をご紹介しました。今回はpsycopg2経由でPostgreSQLに接続する際にコネクションプーリングを使用する方法をご紹介します。
アプリケーションからPostgreSQLに接続する際、主に以下の処理が行われます。
1. Postmasterが接続リクエストを受理
2. バックエンドプロセスとして子プロセスを起動
3. ユーザやパスワード等をもとに認証
特に2.はOSレベルプロセスを生成するコストの高い処理です(Unixの場合fork()が行われます)。 そのため大量の接続要求があった場合はfork()が大量に発生し、OSレベルでCPU高騰に至るケースもあります。
弊社事例でも、アプリケーションから同時に大量の接続があったことでuserコールではなくsystemコールでのCPU高騰が発生したものが複数あります。
これを避けるためには既存の接続を使いまわし、子プロセスが生成される回数を減らす必要があります。そこで登場してくるのがコネクションプーリングです。
コネクションプーリングとは、アプリケーションからのリクエストごとにデータベースへ接続を行うのではなく、アプリケーション側である程度の接続を保持(プール)しておき、実際に必要になったタイミングで、その中から割り当てる機能です。
コネクションプーリングを利用することで、処理ごとに接続する必要がなくなるため、データベース側に与える負荷が低減されます。
コネクションプーリングはWEBアプリケーションとの相性が良いためアプリケーションサーバの多くは自身の実装を持っています。また、一部のデータベース接続用のライブラリもコネクションプーリングの実装を備えています。
PythonからPostgreSQLに接続する際によく利用されるpsycopg2にもコネクションプーリングの実装が用意されています。
以下の環境を利用し、実際に試していきます。
* Python実行環境
- OS: Oracle Linux 6.7 64bit
- Python 3.4
- psycopg2 2.6.1
* PostgreSQL環境
- OS: Oracle Linux 6.7 64bit
- PostgreSQL 9.5
psycopg2のコネクションプーリングは psycopg2.pool.AbstractConnectionPoolを実装したクラスを利用します。自身で実装することもできますが、psycopg2自体に用途に合わせて いくつか実装クラスが提供 されています。その中からpsycopg2.pool.SimpleConnectionPoolを利用してみます。本クラスはシングルスレッドのアプリケーションで使用することを前提としたコネクションプーリングの実装です。
>>> import psycopg2.pool >>> connection_pool = psycopg2.pool.SimpleConnectionPool(minconn=2, maxconn=5, host="localhost", port="9500", dbname="sampledb", user="sayamada", password="pssword")
SimpleConnectionPoolや他のコネクションプールのクラスはいずれも以下の引数を必須でとります。
* minconn: 最小接続数
* maxconn: 最大接続数
また、接続先DBに関する情報を引数としてとります。こちらは通常の接続時に使用するconnect()に渡されるため、以下の記事と同じ設定を行います。
2. psycopg2からPostgreSQLへ接続
さて、SimpleConnectionPoolがエラーなく初期化できていますのでPostgreSQL側にはminconnで指定した数だけ接続が行われているはずです。実際にPostgreSQL側で確認してみます。
sampledb=# select pid,usename,backend_start,query_start from pg_stat_activity where client_addr='127.0.0.1'; pid | usename | backend_start | query_start ------+----------+-------------------------------+------------- 2928 | sayamada | 2016-12-04 16:10:15.010981+09 | 2929 | sayamada | 2016-12-04 16:10:15.013071+09 |
Pythonからの接続に対応するバックエンドプロセスが2本作成されていることがわかります。
まだプールは作成されただけですので、いずれの接続もアプリケーションの処理には割り当てられていません。実際にプールから接続を取得して処理を行ってみます。プールからの接続を取り出すにはgetconn()メソッドを使用します。
本例ではPID:2929に紐づく接続が取得されました。
>>> conn1 = connection_pool.getconn() >>> conn1.get_backend_pid() # 取得された接続に紐づくバックエンドプロセスの確認 2929
コネクションを取り出したあとは、通常のpsycopg2での処理と変わりません。カーソルを取得し、カーソル経由でSQLの実行や結果の取得を行います。
>>> cur1 = conn1.cursor() # SQL実行のためカーソルを作成 >>> cur1.execute("select now()") # SQLを実行 >>> for row in cur1: # 結果をフェッチ、今回は1行のみ ... print(row) ... (datetime.datetime(2016, 12, 4, 16, 17, 28, 935879, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=540, name=None)),)
SQL実行後、接続が不要になった後はputconn()メソッドを使用して接続をコネクションプールに戻します。
>>> connection_pool.putconn(conn1)
この状態で、再度PostgreSQL側で確認してみます。
sampledb=# select pid,usename,backend_start,query_start from pg_stat_activity where client_addr='127.0.0.1'; pid | usename | backend_start | query_start ------+----------+-------------------------------+------------------------------ 2928 | sayamada | 2016-12-04 16:10:15.010981+09 | 2929 | sayamada | 2016-12-04 16:10:15.013071+09 | 2016-12-04 16:30:53.82533+09
getconn()で取り出された、PID:2929の接続のみquery_startの列の値が更新されています。バックエンドプロセスが起動した時間を示すbackend_startの列の値は変化していませんので、2本の接続は維持されたまま、そのうちの一本がアプリケーションの処理に利用されたことがわかります。
なお、putconn()を行わない場合はコネクションプール内の接続が使用中としてマークされたままとなり、他の処理に割り当てることができません。putconn()がコールされた時点で、割当可能な接続がなくなるとmaxconnまで追加の接続を行います。すでにプール内の接続がmaxconnまで拡張済の場合は、接続を割り当てることができず以下の様に"connection pool exhausted"のエラーが発生します。
>>> error_con = connection_pool.getconn() Traceback (most recent call last): File "", line 1, in File "/home/oracle/python344/lib/python3.4/site-packages/psycopg2/pool.py", line 89, in _getconn raise PoolError("connection pool exhausted") psycopg2.pool.PoolError: connection pool exhausted
これは一般的にコネクションリークと呼ばれる問題です。コネクションプーリング側のバグで発生するケースもありますが、多くはプールへの返却漏れ(今回であればputconn()のコール忘れ等)で発生します。例外処理等では特に返却漏れを起こしやすいため、取得した接続は処理終了後に必ずプールに戻すように気をつけます。
ご覧頂いたように、psycopg2自体の機能でも簡単にコネクションプーリングを利用できることがわかりました。今回のサンプルでは単一スレッドでの処理でしたのでSimpleConnectionPoolを使用しましたが、マルチスレッド環境を対象にしたThreadedConnectionPoolも用意されており、同じ方法で利用できます。
psycopg2のコネクションプールは簡易的な実装ですので、実際はこれらをもとにした独自のコネクションプールを実装することになるとは思いますが本記事が皆様のPythonにおけるコネクションプールの理解の一助になれば幸いです。
■本記事の内容について
本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。
■商標に関して
・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。
EDB Postgres Workload Reportsは、Postgresデータベースのパフォーマンス診断とトラブルシューティングを強化する新しいツールです。OracleのAWRに似た詳細なレポートを提供し、データベースの問題を迅速に特定・解決できるようサポートします。本記事では概要と利用手順をご紹介します。
35年以上教育事業を展開しているアシストが新たに取り組み始めた「ポスグレ学園」。連載10回目となる今回の記事では、OSS-DB Gold試験対策問題集 出版の経緯や内容を 新校長 我妻にインタビューしました。
EDB社のAjit Gadge氏を招き「PostgreSQLユーザーに捧ぐ、EDBを使ったDB機能向上とコスト削減の両立」セミナーを開催しました。DB市場の現状やトレンド、EDBの最新動向について紹介しております。アシストセッションのアーカイブ配信の視聴申し込みも可能です。ぜひご覧ください。