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

この記事は 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にもコネクションプーリングの実装が用意されています。
以下の環境を利用し、実際に試していきます。

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