Shin x Blog

PHPをメインにWebシステムを開発してます。Webシステム開発チームの技術サポートも行っています。

PHP + PostgreSQL カーソルを使ったデータ取得の計測

php

PHP（pdo_pgsql）から PostgreSQL に SELECT 文でデータを取得する際に、カーソルを使った場合と使わなかった場合で、どのようにリソース消費（主にメモリと実行時間）の違いがあるのかを計測してみました。

検証内容

PostgreSQL テーブルに、1,000,000 件のレコードを持つテーブルを用意し、下記のようなパターンにて、PHP から全レコードを取得して、実行時間と消費メモリを計測します。カーソル利用時は、1度のフェッチで取得する件数によって計測しています。ここでは、前方向のカーソルのみ扱っています。

  • 1) カーソル未使用 + 1 件づつフェッチ
  • 2) カーソル利用 + 1 件づつフェッチ
  • 3) カーソル利用 + 100 件づつフェッチ
  • 4) カーソル利用 + 10,000 件づつフェッチ
  • 5) カーソル利用 + 100,000 件づつフェッチ
  • 6) カーソル利用 + 1,00,000 件づつフェッチ

参考として、カーソルを使用せずに全件フェッチ（fetchAll）するパターンも計測しています。

  • 7) カーソル未使用 + 全件フェッチ

検証は、VirtualBox 上の CentOS 7（Mem: 2.0GB） に PHP 7.0.6 + PostgreSQL 9.5 をインストールして行いました。

環境構築

下記の SQL で、テーブル構築とレコードの追加（1,000,000件）を行いました。generate_series関数は、こういうダミーデータ作る時に便利ですね。

CREATE TABLE users(
  id int primary key,
  number int,
  created_at timestamp
);

INSERT INTO users(id, number, created_at) SELECT i,(random() * 1000)::int % 1000, CURRENT_TIMESTAMP - CAST((random() * 1000)::int || ' day' AS interval) FROM generate_series(1, 1000000) s (i);

VACUUM;

レコードが格納されているかを確認しておきます。users テーブルには、1,000,000 件のレコードがあり、テーブルサイズは 42MB であることが分かります。

bench=# SELECT relname, to_char(reltuples, '9,999,999') as reltuples, (relpages * 8 / 1024) as kbytes FROM pg_class WHERE relname='users';
 relname | reltuples  | MB
---------+------------+--------
 users   |  1,000,000 |    42
(1 行)

bench=# SELECT COUNT(*) FROM users;
  count
----------
 1000000
(1 行)

PDO で全件を取得

PDO を使って、レコードを取得します。全体の実行コードは下記です。

https://gist.github.com/shin1x1/27db7768218ab3b4557a3dce4f639faa#file-pdo_cursor-php

Benchmark クラスは、実行時間とメモリ利用量（RSS）を出力するクラスです。ここでは、メモリ消費量を RSS 値と memory_get_usage関数で取得しています。

PHP のベンチマークでは、memory_get_usage 関数が使われますが、これは、PHP（Zend Engine） で割り当てられたメモリのみが計測対象なので、今回のように拡張（pdo_pgsql）や拡張が利用しているライブラリ（libpq）が、独自に確保したメモリは加算されません。そこで、比較のために両方を取得しています。

UserRepository クラスが、PDO を使って、PostgreSQL にアクセスするクラスです。下記のポイントで Benchmark クラスでログを出力しています。

  • SELECT 文（DECLARE CURSOR 文）実行前
  • SELECT 文（DECLARE CURSOR文）実行後
  • フェッチ中（100,000件毎）

実行結果

上記のコードを実行すると、下記のような出力が得られました。

https://gist.github.com/shin1x1/27db7768218ab3b4557a3dce4f639faa#file-results

実行結果をそれぞれまとめてみました。

メモリ消費量

メモリ消費量についてです。

まず、memory_get_usage 関数については、全てのケースでほとんど違いがありませんでした。これは、1-6 の計測では、libpq と PostgreSQL 間でのやりとりにバリエーションはありますが、PHP と libpq の間は違いが無いため（この間では、1 件づつフェッチしている）ためです。以下は、RSS 値についてです。

カーソル未使用では、SQL 文発行時に結果セットを libpq が、一気に PostgreSQL から受けとるので、利用メモリが一気に上がっています。フェッチ中は、libpq から取得するだけなので変化がありません。一方、カーソルでは、SQL 発行時（DECLARE CURSOR文）は、消費量に変化がありません。あとは、フェッチするレコード数に応じてメモリが消費されています。特に、6 は、全件をフェッチしているので、1 と同程度に消費しています。

方式 SQL 実行前 SQL 実行後 フェッチ完了
1) カーソル未使用 13,088 kb 108,708 kb 108,824 kb
2) カーソル + 1 件フェッチ 13,500 kb 13,500 kb 13,512 kb
3) カーソル + 100 件フェッチ 13,512 kb 13,512 kb 13,512 kb
4) カーソル + 10,000 件フェッチ 13,512 kb 13,512 kb 14,356 kb
5) カーソル + 100,000 件フェッチ 14,356 kb 14,356 kb 22,944 kb
6) カーソル + 1,000,000 件フェッチ 22,944 kb 22,944 kb 108,848 kb

上記表をグラフにしてみました。1 と 6 では、タイミングは異なりますが、全レコード分のメモリを消費しています。一方、2、3については、ほぼ変化はありません。4 は 800k、5 は 8m 増加している程度です。

f:id:shin1x1:20160614182705p:plain

実行時間

実行時間についてです。

カーソル未使用では、SQL実行完了（結果セット受け取るまで）に時間がかかっていますが、フェッチ完了までは速いです。カーソルを使うと、SQL 実行は速いですが、あとはフェッチする件数に依存しており、取得するレコード数が少なければ、その分、フェッチ回数が増えるので遅くなっています。

方式 SQL 実行前 SQL 実行後 フェッチ完了
1) カーソル未使用 - 857 ms 1,236 ms
2) カーソル + 1 件フェッチ - 152 ms 17,680 ms
3) カーソル + 100 件フェッチ - 148 ms 1,426 ms
4) カーソル + 10,000 件フェッチ - 148 ms 1,260 ms
5) カーソル + 100,000 件フェッチ - 152 ms 1,344 ms
6) カーソル + 1,000,000 件フェッチ - 153 ms 1,338 ms

上記表をグラフにしてみました。全体の実行時間を見ると、2 だけ実行時間が大きくかかっています。フェッチがレコード数分実行されるので、当然ながら時間がかかります。ここでは、PHP と PostgreSQL が同ホストでしたが、別ホストであれば通信のオーバヘッドもあるので、より遅くなります。それ以外については、大きな違いはありませんでした。

f:id:shin1x1:20160614182714p:plain

fetchAllの場合

PHP と libpq 間のやりとりについて見るために、カーソルなしで fetchAll した場合も計測してみました。結果を見ると、fetchAll 完了後に一気に RSS 値も memory_get_usage 関数の値も上昇しています。特に、memory_get_usage の上昇が顕著です。これは、libpq が持つ結果セットを PHP の値（1,000,000件の配列）として格納したためです。

方式 SQL 実行前 SQL 実行後 fetchAll完了後 ループ完了
RSS 13,516 kb 108,852 kb 563,512 kb 563,832 kb
memory_get_usage 364 kb 455,016 kb 455,016 kb 455,016 kb
実行時間 0 ms 839 ms 1,179 ms 1,405 ms

[おまけ] PDO::ATTR_CURSOR = PDO::CURSOR_SCROLL

pdo_pgsql のコードを見てみると、上記オプションを指定すると、自動でカーソルが使われるようになります。

https://github.com/php/php-src/blob/PHP-7.0.6/ext/pdo_pgsql/pgsql_statement.c#L153 https://github.com/php/php-src/blob/PHP-7.0.6/ext/pdo_pgsql/pgsql_statement.c#L423

おお、これは便利！と思って、試してみたのですが、1 件づつのフェッチなので、件数が多いと重くなるのと、自分で FETCH 文を発行する場合とことなり、メモリ消費量が異常に上がるので、おそらくメモリリークしているように見えます。

このオプションは、カーソルでポインタを自由に移動するためのものなので、前方向のみに利用するには適さないかもしれません。

さいごに

それぞれの方式による実行時間とメモリ消費量を見てみました。

カーソルを利用することで、PHP プロセスのメモリ消費を抑えてフェッチすることができます。しかし、ただカーソルを使えば良いかと言うとそうではなく、カーソルの特性を理解して、フェッチするレコード数を調整する必要があります。ここでは、実行時間とメモリ消費量のバランスで考えると、4 が妥当でしょう。もちろん、どのパターンが良いかはケースバイケースです。

カーソルを利用しない場合は、全件の結果セットを取得しますが、libpq が保持している状態です。ここで、fetch / fetchAll メソッドを呼ぶことで、PHP の値として利用できるようになります。

fetchAll の例で分かるように、PHP の値に格納するのはメモリ消費が大きいので、大量のデータを読み込む場合は、全レコードを一気に配列に格納するのではなく、fetch メソッドで逐次取得して処理するのが良いですね。

バッチ処理などで大量のデータを扱う際は、カーソルを上手く使ってみると良いでしょう。

参考