PythonのORMライブラリsqlalchemyで利用可能なコネクションプーリングの実装について調べてみた

プログラミング言語特集

岩田智哉

2020.05.10

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

CX事業本部@大阪の岩田です。Python向けのORMライブラリsqlalchemyは標準でコネクションプーリングの実装が組み込まれており、create_engine()を呼出す際の名前付き引数poolclassの指定によってコネクションプーリングの実装を切り替えることができます。先日コネクションプーリングの実装について調べる機会があったので、内容をご紹介します。

環境

今回利用した環境です。

OS X 10.14.6
Python 3.8.2
sqlalchemy 1.3.16
pymysql 0.9.3

利用できるコネクションプーリングの実装

sqlalchemyは標準で以下のコネクションプーリングの実装を提供しています。

QueuePool
NullPool
SingletonThreadPool
StaticPool
AssertionPool

例としてNullPoolを使う場合は以下のようにpoolclassを指定してcreate_engineを呼び出します。

engine = sqlalchemy.create_engine('mysql+pymysql://root:mysql@localhost/mysql?charset=utf8', poolclass=NullPool)

各実装について詳細を見ていきましょう。

QueuePool

オープン状態のDB接続数を制限するコネクションプーリングの実装で、パラメータpool_sizeとmax_overflowを指定することでプールするDB接続数最大の同時接続数を制御することが可能です。各パラメータの意味は以下の通りです。

pool_size
- sqlalchemyがプールする接続数を指定するパラメータです。デフォルトでは5つの接続を永続化してプールします。pool_sizeを0に設定すると無制限に接続をプールするようになります。
max_overflow
- pool_sizeで設定した上限に加えて、追加でOPEN可能な接続数を指定するパラメータです。sqlarchemyを利用するアプリケーション側からはpool_size + max_overflowの分だけDBとの接続をOPENすることが可能になります。デフォルト値は10で-1に指定することで無制限になります。

create_engineの呼び出し時にpoolclassの指定がない場合はQueuePoolがデフォルトで利用されます。実際にQueuePoolの挙動を確認してみましょう。Pythonのインタプリタから以下のように入力し、MySQLに接続してみましょう。

>>> import sqlalchemy
>>> from sqlalchemy.pool import QueuePool
>>>
>>> engine = sqlalchemy.create_engine(f'mysql+pymysql://root:mysql@localhost/mysql?charset=utf8', poolclass=QueuePool)
>>> con1 = engine.connect()

この状態でinformation_schema.processlistから接続状況を確認すると以下のようになります。

mysql> select * from information_schema.processlist where db = 'mysql';
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 105 | root | 172.18.0.1:47704 | mysql | Sleep   |   33 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

OPENしている接続は1つということが分かります。続けて先程OPENした接続con1をCLOSEしてみます。

>>> con1.close()

再度information_schema.processlistに問い合わせてみます。

mysql> select * from information_schema.processlist where db = 'mysql';
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 105 | root | 172.18.0.1:47704 | mysql | Sleep   |  286 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

con1をcloseしたにも関わらず、DB接続が残り続けていることが分かります。QueuePoolはpool_sizeで指定された数のDB接続を永続化してプールするため、このような振る舞いとなります。

今度は試しに接続を6つOPENしてみましょう。

>>> con1 = engine.connect()
>>> con2 = engine.connect()
>>> con3 = engine.connect()
>>> con4 = engine.connect()
>>> con5 = engine.connect()
>>> con6 = engine.connect()

接続数を確認します。

mysql> select * from information_schema.processlist where db = 'mysql';
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 125 | root | 172.18.0.1:47716 | mysql | Sleep   |    6 |       | NULL |
| 105 | root | 172.18.0.1:47704 | mysql | Sleep   |  520 |       | NULL |
| 124 | root | 172.18.0.1:47712 | mysql | Sleep   |    8 |       | NULL |
| 126 | root | 172.18.0.1:47720 | mysql | Sleep   |    4 |       | NULL |
| 127 | root | 172.18.0.1:47724 | mysql | Sleep   |    2 |       | NULL |
| 123 | root | 172.18.0.1:47708 | mysql | Sleep   |   10 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
6 rows in set (0.00 sec)

接続数は6まで増えました。con1 ~ con6を全てcloseしてみます。

>>> con1.close()
>>> con2.close()
>>> con3.close()
>>> con4.close()
>>> con5.close()
>>> con6.close()

接続数を確認します。

mysql> select * from information_schema.processlist where db = 'mysql';
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 125 | root | 172.18.0.1:47716 | mysql | Sleep   |    5 |       | NULL |
| 105 | root | 172.18.0.1:47704 | mysql | Sleep   |   12 |       | NULL |
| 124 | root | 172.18.0.1:47712 | mysql | Sleep   |    7 |       | NULL |
| 126 | root | 172.18.0.1:47720 | mysql | Sleep   |    3 |       | NULL |
| 123 | root | 172.18.0.1:47708 | mysql | Sleep   |    9 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
5 rows in set (0.00 sec)

con1 ~ con6をcloseした結果、接続数は5に減りました。これはQueuePoolのパラメータpool_sizeがデフォルトで5に設定されているためです。

今度はcon1 ~ con15までOPENしてみましょう。max_overflowのデフォルト値は10なので、pool_sizeの5と合わせて15接続まではOPEN可能な想定です。

>>> con1 = engine.connect()
>>> con2 = engine.connect()
>>> con3 = engine.connect()
>>> con4 = engine.connect()
>>> con5 = engine.connect()
>>> con6 = engine.connect()
>>> con7 = engine.connect()
>>> con8 = engine.connect()
>>> con9 = engine.connect()
>>> con10 = engine.connect()
>>> con11 = engine.connect()
>>> con12 = engine.connect()
>>> con13 = engine.connect()
>>> con14 = engine.connect()
>>> con15 = engine.connect()

接続状況を確認してみます。

mysql> select * from information_schema.processlist where db = 'mysql';
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 148 | root | 172.18.0.1:47756 | mysql | Sleep   |    7 |       | NULL |
| 125 | root | 172.18.0.1:47716 | mysql | Sleep   |  316 |       | NULL |
| 141 | root | 172.18.0.1:47728 | mysql | Sleep   |    7 |       | NULL |
| 149 | root | 172.18.0.1:47760 | mysql | Sleep   |    6 |       | NULL |
| 144 | root | 172.18.0.1:47740 | mysql | Sleep   |    7 |       | NULL |
| 151 | root | 172.18.0.1:47764 | mysql | Sleep   |    2 |       | NULL |
| 105 | root | 172.18.0.1:47704 | mysql | Sleep   |  323 |       | NULL |
| 145 | root | 172.18.0.1:47744 | mysql | Sleep   |    7 |       | NULL |
| 124 | root | 172.18.0.1:47712 | mysql | Sleep   |  318 |       | NULL |
| 126 | root | 172.18.0.1:47720 | mysql | Sleep   |  314 |       | NULL |
| 143 | root | 172.18.0.1:47736 | mysql | Sleep   |    7 |       | NULL |
| 142 | root | 172.18.0.1:47732 | mysql | Sleep   |    7 |       | NULL |
| 123 | root | 172.18.0.1:47708 | mysql | Sleep   |  320 |       | NULL |
| 146 | root | 172.18.0.1:47748 | mysql | Sleep   |    7 |       | NULL |
| 147 | root | 172.18.0.1:47752 | mysql | Sleep   |    7 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
15 rows in set (0.00 sec)

15接続確立されていることが分かります。16個目の接続OPENを試してみます。

>>> con16 = engine.connect()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2218, in connect
    return self._connection_cls(self, **kwargs)
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 103, in __init__
    else engine.raw_connection()
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2317, in raw_connection
    return self._wrap_pool_connect(
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
    return fn()
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 127, in _do_get
    raise exc.TimeoutError(
sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r)

タイムアウトしてsqlalchemy.exc.TimeoutErrorraiseされました。想定通りの動作ですね。

NullPool

続いてNullPoolです。この実装はコネクションプーリングを行いません。挙動を確認してみましょう。

まずNullPoolを指定して接続をOPENします。

>>> import sqlalchemy
>>> from sqlalchemy.pool import NullPool
>>> engine = sqlalchemy.create_engine(f'mysql+pymysql://root:mysql@localhost/mysql?charset=utf8', poolclass=NullPool)
>>> con1 = engine.connect()

接続状況は以下の通りになります。

mysql> select * from information_schema.processlist where db = 'mysql';
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 182 | root | 172.18.0.1:47768 | mysql | Sleep   |    3 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

con1をcloseしてみます。

>>> con1.close()

NullPoolではsqlalchemy側でコネクションをプーリングしないため、実際に接続がCLOSEされているはずです。確認してみましょう。

mysql> select * from information_schema.processlist where db = 'mysql';
Empty set (0.00 sec)

結果が0レコードに変わっており、sqlalchemyがコネクションをプーリングしていないことが分かります。

SingletonThreadPool

スレッド毎に1つのDB接続を管理するコネクションプーリングの実装です。SQLiteのインメモリーデータベースを使用したテストでの利用のみ想定しており、本番環境での利用は推奨されません。

本番環境での利用は推奨されないということですが、一応動作を確認しておきましょう。

まずスレッドを1つ生成してスレッド内でengine.connectを3回呼び出してみます。

>>> import threading
>>> import sqlalchemy
>>> from sqlalchemy.pool import SingletonThreadPool
>>>
>>> def worker():
...   engine = sqlalchemy.create_engine(f'mysql+pymysql://root:mysql@localhost/mysql?charset=utf8', poolclass=SingletonThreadPool)
...   con1 = engine.connect()
...   con2 = engine.connect()
...   con3 = engine.connect()
...
>>> t1 = threading.Thread(target=worker)
>>> t1.start()

MySQLから確認した接続状況です。

mysql> select * from information_schema.processlist where db = 'mysql';
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 285 | root | 172.18.0.1:47816 | mysql | Sleep   |   71 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

con1 ~ con3はプーリングされた同一のDB接続を利用しているため、１レコードしかヒットしません。

もう1つスレッドを作成してみましょう。

>>> t2 = threading.Thread(target=worker)
>>> t2.start()

再度MySQLから確認します。

mysql> select * from information_schema.processlist where db = 'mysql' order by id;
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 285 | root | 172.18.0.1:47816 | mysql | Sleep   |  151 |       | NULL |
| 290 | root | 172.18.0.1:47820 | mysql | Sleep   |    7 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
2 rows in set (0.00 sec)

2つ目のスレッド用に新しくDB接続が確立していることが分かります。

StaticPool

全てのリクエストに対して単一のDB接続を利用するコネクションプーリングの実装です。DBとの接続をシングルトンとして利用したい場合に利用すると良いでしょう。

こちらも実際にPythonのインタプリタから動作を確認してみます。

>>> import sqlalchemy
>>> from sqlalchemy.pool import StaticPool
>>> engine = sqlalchemy.create_engine(f'mysql+pymysql://root:mysql@localhost/mysql?charset=utf8', poolclass=StaticPool)
>>> con1 = engine.connect()

接続状況は以下の通りです。

mysql> select * from information_schema.processlist where db = 'mysql' order by id;
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 212 | root | 172.18.0.1:47784 | mysql | Sleep   |    3 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

追加でcon2をOPENしてみましょう。con1とcon2共に同一のDB接続を参照するはずです。

>>> con2 = engine.connect()

MySQLから確認してみます。

mysql> select * from information_schema.processlist where db = 'mysql' order by id;
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 212 | root | 172.18.0.1:47784 | mysql | Sleep   |   19 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

con2を追加でOPENしましたが、結果は特に変わっていません。con1,con2共に同一のDB接続を利用していることが分かります。

続いてcon1,con2共にcloseしてみます。

>>> con1.close()
>>> con2.close()

再度MySQLから確認します。

mysql> select * from information_schema.processlist where db = 'mysql' order by id;
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 212 | root | 172.18.0.1:47784 | mysql | Sleep   |    2 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

con1,con2共にcloseしましたが、sqlalchemyが接続をプーリングしているため、実際にはMySQLとの接続は切断されていないことが分かります。

AssertionPool

接続を1つだけプールし、プールに対するDB接続の利用要求を1つだけ許可する実装です。意図せず多数のDB接続確立されているような状況下でのデバッグ用途での利用を目的としており、同時に複数のDB接続利用要求が発生すると、AssertionPoolは例外をraiseします。

こちらもPythonのインタプリタから試してみましょう。

>>> import sqlalchemy
>>> from sqlalchemy.pool import AssertionPool
>>> engine = sqlalchemy.create_engine(f'mysql+pymysql://root:mysql@localhost/mysql?charset=utf8', poolclass=AssertionPool)
con1 = engine.connect()
>>> con1 = engine.connect()

MySQLから接続状況を確認してみます。

mysql> select * from information_schema.processlist where db = 'mysql';
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 235 | root | 172.18.0.1:47796 | mysql | Sleep   |   24 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

接続が1つOPENされました。

con1からDB接続を参照している状態で、新たにcon2を要求してみます。

>>> con2 = engine.connect()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2218, in connect
    return self._connection_cls(self, **kwargs)
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 103, in __init__
    else engine.raw_connection()
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2317, in raw_connection
    return self._wrap_pool_connect(
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
    return fn()
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/Users/xxx/.venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 471, in _do_get
    raise AssertionError("connection is already checked out" + suffix)
AssertionError: connection is already checked out at:
  File "<stdin>", line 1, in <module>

AssertionErrorがraiseされました。

続いてcon1をcloseしてみます。

>>> con1.close()

MySQLから接続状況を確認してみます。

mysql> select * from information_schema.processlist where db = 'mysql';
+-----+------+------------------+-------+---------+------+-------+------+
| ID  | USER | HOST             | DB    | COMMAND | TIME | STATE | INFO |
+-----+------+------------------+-------+---------+------+-------+------+
| 235 | root | 172.18.0.1:47796 | mysql | Sleep   |   17 |       | NULL |
+-----+------+------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

MySQLとの接続自体は残り続けており、sqlalchemyが接続をプーリングしていることが分かります。

まとめ

sqlalchemyのコネクションプーリング機構について調査してみました。元々sqlalchemyがデフォルトでコネクションプーリングの機構を備えていることを知らず、NullPool相当の挙動を期待して実装を行っていたのですが、意図通りに接続がCLOSEされないことから詳細を行うことになりました。やはり未経験のライブラリのを利用する際は、しっかりドキュメントを読み込むことが重要だなと反省しました。この記事が誰かの参考になれば幸いです。

PythonのORMライブラリsqlalchemyで利用可能なコネクションプーリングの実装について調べてみた

環境

利用できるコネクションプーリングの実装

QueuePool

NullPool

SingletonThreadPool

StaticPool

AssertionPool

まとめ

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

PythonのORMライブラリsqlalchemyで利用可能なコネクションプーリングの実装について調べてみた

環境

利用できるコネクションプーリングの実装

QueuePool

NullPool

SingletonThreadPool

StaticPool

AssertionPool

まとめ

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS / システムアーキテクト案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩