Symfonyで大容量CSVをダウンロードする

2015年3月26日

Symfonyで大容量CSVをダウンロードする

この記事は 2015年3月22日 に開催された Symfony Meetup #2 で LT した内容を整理したものです。発表資料の公開も考えましたが、ソースコードがそれなりにでてくるのでブログで整理して公開することにしました。

Symfony2 で CSV ダウンロードといえば twig を使うのが一般的（？）ですが、この方法は CSV の件数が多い場合すぐにメモリ上限に達してしまいます。メモリ使用量を抑え、大容量の CSV にも耐えられる Symfony 流のダウンロード方法を模索してみました。

動作確認環境

  • PHP5.6
  • MySQL5.6
  • Symfony2.6

目次

  1. Twig による CSV ダウンロードはメモリオーバーしてしまう
  2. 素の PHP の場合
  3. StreamedResponse と Query::iterate() を使う
  4. バッファクエリと非バッファクエリ
  5. Symfony を非バッファモードで動作させる
  6. テストを書く
  7. リファクタリング
  8. まだメモリを使っている
  9. 参加者の皆様からのアドバイス

Twig による CSV ダウンロードはメモリオーバーしてしまう

Symfony で CSV ダウンロードで検索すると、よくヒットするのが twig を使った方法です。冒頭でも書きましたが、この方法は件数が多いとすぐメモリ上限に達してしまいます。findBy() の結果を一度配列にためてから twig に渡している部分がメモリを多く使っていそうです。

/**
 * @Route("/csv", name="csv")
 * @Method("GET")
 */
public function csvAction(Request $request)
{
    // findBy の結果を配列にためるので、メモリ使用量が多くなる
    $users = $this->getDoctrine()->getManager()
        ->getRepository('AppBundle:User')->findBy([...]);
    $response = $this->render('index.csv.twig', $users)
    $contents = mb_convert_encoding($response->getContent(), 'SJIS-win', mb_internal_encoding());
    $response->setContent($contents);
    $response->headers->set('Content-Disposition', 'attachment; filename=sample.csv');
    $response->headers->set('Content-type', 'application/octet-stream');
    return $response
}
{% if users is not empty %}
{% for user in users %}
{{ user.id }},{{ user.name|raw }}
{% endfor %}
{% endif %}

素の PHP の場合

素の PHP で CSV ダウンロードする場合、こんな感じに DB から fetch() しつつブラウザに出力すると省メモリになります。これと同じことを Symfony でやるにはどうすればいいでしょうか。

<?php
ini_set('max_execution_time', 0);
header('Content-disposition: attachment; filename=sample.csv');
header('Content-type: application/octet-stream; name=sample.csv');
$fp = fopen('php://output', 'w');
while ($row = $db->fetch()) {
    fputcsv($fp, array_map(function ($value) {
        return mb_convert_encoding(
            $value, 'SJIS-win', mb_internal_encoding()
        );
    }, $row));
    flush();
}
fclose($fp);
exit;

StreamedResponse と Query::iterate() を使う

StreamedResponse を使うと、ブラウザへ随時応答ができます。Query::iterate() は foreach のたびに
結果を展開するので findBy() に比べメモリにやさしいです。ただし、Entity を使い終わったら手動で detach() をして EntityManager から消してあげる必要があります。

/**
 * @Route("/csv", name="csv")
 * @Method("GET")
 */
public function csvAction(Request $request)
{
    $response = new StreamedResponse();
    $response->setCallback(
        function () {
            $query = $this->getDoctrine()->getManager()
                ->getRepository('AppBundle:User')
                ->createQueryBuilder('u')->getQuery();
            $file = new \SplFileObject('php://output', 'w');
            foreach ($query->iterate() as $iterableResult) {
                /** @var User $user */
                $user = $iterableResult[0];
                $row = [
                    $user->getId(),
                    $user->getName(),
                ];
                $file->fputcsv(array_map(function($value) {
                    return mb_convert_encoding(
                        $value, 'SJIS-win', mb_internal_encoding()
                    );
                }, $row));
                $this->getDoctrine()->getManager()->detach($user);
                flush();
            }
        }
    );
    $response->headers->set('Content-Type', 'application/octet-stream');
    $response->headers->set('Content-Disposition', 'attachment; filename=sample.csv');
    $response->send();
    return $response;
}

バッファクエリと非バッファクエリ

twig の時より省メモリになりましたが、まだメモリ使用量が多い。Doctrine\DBAL\Connection でメモリオーバーエラーが起きているのを見ると、どうも PDO がメモリを食っているようです。PDO のメモリの使い方を調べていたらマニュアルにこんな説明がありました。

PHP: バッファクエリと非バッファクエリ - Manual

バッファクエリは、クエリの結果が
すぐに DB から PHP に転送され、
PHP プロセスのメモリ内に結果を保持します。結果セットが大きいと PHP のメモリ使用量が多くなります。PDO のデフォルトはこっち。

非バッファクエリは、クエリの実行時点では、結果はサーバにあり取得待ちになっています。PHP のメモリ使用量は少なくなりますが、サーバへの負荷が高くなります。

PDO はデフォルトのバッファクエリモードで動作していたようです。メモリ使用量が多くなるわけですね。

Symfony を非バッファモードで動作させる

素の PDO を非バッファモードで動作させるには PDO を new した後 setAttribute() します。

$pdo = new \PDO();
$pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

Symfony の PDO を非バッファモードにするには、config.yml で options を指定します。

doctrine:
    dbal:
        driver:   pdo_mysql
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        options:
            1000: false # \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY

options: 1000 という数字が急に出てきました。これは PDO クラスの定数 \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY の値です。

上記の設定では常に非バッファモードになってしまうので、バッファモードと非バッファモードで connection を分けます。

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver:   "%database_driver%"
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
            unbuffered:
                driver:   pdo_mysql
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
                options:
                    1000: false # \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY

EntityManager もコネクションごとに分けます。

orm:
    auto_generate_proxy_classes: "%kernel.debug%"
    default_entity_manager: default
    entity_managers:
        default:
            connection: default
            mappings:
                AppBundle:
                    type: annotation
        unbuffered:
            connection: unbuffered
            mappings:
                AppBundle:
                    type: annotation

CSV のダウンロードでは、作成した非バッファモードの EntityManager を使います。

//...
$query = $this->getDoctrine()->getManager('unbuffered')
    ->getRepository('AppBundle:User')
    ->createQueryBuilder('u')
    ->getQuery();
//...
$this->getDoctrine()->getManager('unbuffered')->detach($user);
//..

detach するときも同じ EntityManager を使うことを忘れずに。

テストを書く

LT では時間の都合で省きましたが、CSV ダウンロードのテストはこのように書けます。StreamedResponse を使っているので、ob_start() で出力バッファリングを有効にするのがポイント。

/**
 * @test
 */
public function csvActionGoodCase()
{
    $client = static::createClient();
    ob_start();
    $client->request('GET', '/user/csv');
    $csv = ob_get_contents();
    ob_end_clean();
    $expected = '1,山田太郎';
    $expected = mb_convert_encoding($expected, 'SJIS-win', mb_internal_encoding());
    $this->assertEquals($expected, rtrim($csv));
}

Fixture は AliceBundle を使いましょう。

Symfony2 AliceBundle
[Symfony] AliceBundleで自動テストのfixtureをyml化しよう

この記事は Symfony Advent Calendar 2014 の 16 日目の記事です。 みなさん Symfon ...

続きを見る

リファクタリング

サンプルのコードは全部コントローラに書いてあってゴチャゴチャしているので、テストが書けたらリファクタリングしましょう。

  • StreamedResponse で CSV ダウンロードする部分は、コンポーネント化して汎用的なクラスに
  • コンポーネント化したクラスをコンテナに登録して、非バッファモードの EntityManager をコンストラクタから inject する

興味のある方は試してみてください。

まだメモリを使っている

実はこのブログに書かれている方法でも、容量の多い CSV を作成するとそれなりにメモリを消費します。具体的には、30 MB の CSV をダウンロードするのに 70 MB くらいのメモリを消費します。これ以上メモリを削減できるところが見つかっておらず、ここらへんが Symfony の限界なのかなと思っています。

PHP の memory_limit は 128 MB がデフォルトなので、だいたい 60 MB くらいの CSV までなら耐えられます。上限があるので memory_limit の値を同時アクセス数などを見ながら増やすか、CSV のダウンロード件数に制限をかけるかのどちらかの対策が必要そうです。

参加者の皆様からのアドバイス

参加者の皆様からの温かいアドバイス。参考になります。

  • Doctrine は DB の結果セットをオブジェクト化する部分が重いので、結果セットにオブジェクトではなく配列を使ってみては？
  • Goodby CSV というライブラリがメモリ管理を気にしなくてよくてオススメ
  • 事前にバッチなどで日別、月別のファイルを作っておいて、ダウンロード時にファイル結合してダウンロードさせるのはどうか？

みなさんの意見をまとめると「大量データを扱うなら Doctrine を使うな」「便利だけど重い」ということでした。Goodby CSV がよさそうなので、使ってみようと思います。

まとめ

  • Twig を使った CSV ダウンロードは、大容量 CSV には向かない
  • StreamedResponse と Query::iterate() で
DB から fetch しながら CSV をブラウザに出力
  • 大量の結果セットを扱う場合
PDO は非バッファモードで動作させる
  • Doctrine で頑張るのではなくて Goodby CSV を使うのがよさげ

人前で話すのは苦手でなかなか慣れませんが、みなさん親切にアドバイスをくださって、LT してよかったと思いました。イベントを企画してくださった Symfony ユーザー会の皆様、スタッフの皆様、参加者の皆様、会場とピザを提供してくださった VOYAGE GROUP 様、ありがとうございます。

-技術ブログ
-