Database Support Blog

Database Support Blog>【PostgreSQL】psycopg2のコネクションプーリングを使ってみよう

  • PostgreSQL
2016.12.10

【PostgreSQL】psycopg2のコネクションプーリングを使ってみよう

psycopg2のコネクションプーリングを使ってPostgreSQLに接続する方法

この記事は PostgreSQL Advent Calendar 2016 の10日目です。

以前、psycopg2を使用して PythonからPostgreSQLへ接続する方法 をご紹介しました。今回はpsycopg2経由でPostgreSQLに接続する際にコネクションプーリングを使用する方法をご紹介します。

DB接続にかかるコストとは

アプリケーションからPostgreSQLに接続する際、主に以下の処理が行われます。

1. Postmasterが接続リクエストを受理
2. バックエンドプロセスとして子プロセスを起動
3. ユーザやパスワード等をもとに認証

特に2.はOSレベルプロセスを生成するコストの高い処理です(Unixの場合fork()が行われます)。 そのため大量の接続要求があった場合はfork()が大量に発生し、OSレベルでCPU高騰に至るケースもあります。

弊社事例でも、アプリケーションから同時に大量の接続があったことでuserコールではなくsystemコールでのCPU高騰が発生したものが複数あります。

これを避けるためには既存の接続を使いまわし、子プロセスが生成される回数を減らす必要があります。そこで登場してくるのがコネクションプーリングです。

コネクションプーリングとは

コネクションプーリングとは、アプリケーションからのリクエストごとにデータベースへ接続を行うのではなく、アプリケーション側である程度の接続を保持(プール)しておき、実際に必要になったタイミングで、その中から割り当てる機能です。

コネクションプーリングを利用することで、処理ごとに接続する必要がなくなるため、データベース側に与える負荷が低減されます。

コネクションプーリングはWEBアプリケーションとの相性が良いためアプリケーションサーバの多くは自身の実装を持っています。また、一部のデータベース接続用のライブラリもコネクションプーリングの実装を備えています。

psycopg2でのコネクションプーリング

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におけるコネクションプールの理解の一助になれば幸いです。

筆者情報

山田 聡 画像

2011年にアシストに入社してからサポートセンターでOracle DatabaseやPostgreSQL、EDB Postgres Advanced Server(EPAS)といったデータベース製品を中心に担当しています。個人的にJavaやNode.js、Python等を触っていますので、PostgreSQLと絡めてアプリケーション周りの情報をお送りしていきます。

関連している記事

  • PostgreSQL
2016.12.21

pg_basebackup/pg_receivexlog実行中にWALが上書きされる障害の予防策

PostgreSQLで、pg_basebackup/pg_receivexlog実行中にWALが上書きされる障害の予防策をご紹介します。

  • PostgreSQL
2016.12.01

【SR+HS環境の落とし穴】スタンバイのリカバリに必要なWALが上書きされる障害の予防策

PostgreSQLのSR+HS環境でスタンバイのリカバリに必要なWALが上書きされる障害の予防策を紹介します。

  • EDB Postgres
  • PostgreSQL
2016.11.11

【EDB Postgres/PostgreSQL】うるう秒(閏秒)の対応

EDB Postgres/PostgreSQLでのうるう秒(閏秒)の対応方法について解説します。2017年元旦にトラブルとならないよう、準備しましょう。

アシストサポートセンターのご紹介 Oracle Database研修

ページの先頭へ戻る