LaravelでデータをDBに保存したいときのメモリ不足をなんとかする

APIで取得した50万件分のデータを保存する

環境

Linux 5.0.0-27-generic #28~18.04.1-Ubuntu
PHP 7.3.9
Laravel Framework 6.0.3
MySQL 8.0

結論

CSVに一旦保存してLOAD DATA IN FILEが一番速い。

前提

  • 509,999件分のデータを一括で取得できるAPIがあります。配列には10桁の英数字の文字列が入っています。
<?php
array(
    [0]      => '文字列データ'
    ...
    [509999] => '文字列データ'
)

php artisan commandAPIからデータの取得・保存を実行します。

<?php
    public function handle()
    {
        $client = new Client();
        $list = $client->get()->list;
        // APIで取得したデータをモデルにそのまま突っ込む
        Product::store($list);
    }

1件1件保存するのではなくまとめてINSERTするようにしています。

<?php
    public static function store(array $list)
    {
        $data = [];
        foreach ($list as $value) {
            $data[] = ['value' => $value];
        }
        Product::insertOnDuplicateKey($data);
    }

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes)
メモリ不足で死にました。

https://www.php.net/manual/ja/ini.core.php

スクリプトが確保できる最大メモリをバイト数で指定します。この命令は、 正しく書かれていないスクリプトがサーバーのメモリを食いつぶすことを防止するのに役立ちます。 もし、使用可能メモリに制限を設けたくない場合は、 ここに -1 を指定してください。

-1にするのは危険そうだし、上限上げ始めたらキリがないので変更しないことにする。

分割してみよう

array_chunkで50万件を1万件ずつに分割して保存してみることにしました。

<?php
    public function handle()
    {
        $client = new Client();
        $list = $client->get()->list;
        $chunks = array_chunk($list, 10000);

        foreach ($chunks as $index => $chunk) {
            print_r("index:{$index}\n");
            print_r(memory_get_usage(true) . "\n");
            Product::store($chunk);
        }
    }
index:0
81844928
index:1
96248832
index:2
109675896
PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes)

メモリ不足で死にました。
でも2万件は保存できました。

execでプロセスとして保存処理を行う

https://www.php.net/manual/ja/function.exec.php

配列をカンマ区切りでimplodeしたデータをexec関数の引数に渡して保存するようにします。 保存するためのCommandを作成します。

<?php
    public function handle()
    {
        $client = new Client();
        $list = $client->get()->list;
        $chunks= array_chunk($list, 10000);

        foreach ($chunks as $index => $chunk) {
            print_r("index:{$index}\n");
            $data = implode(',', $chunk);
            exec("php artisan command:Store '{$data}'");
        }
    }
<?php
class Store extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:Store {data}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $data = $this->argument('data');
        Product::store(explode(',', $data));
    }
}

保存できたがめっちゃ遅い。

計測開始2019-09-16 10:09:25
index:0
index:1
index:2
index:3
index:4
index:5
index:6
index:7
index:8
index:9
index:10
index:11
index:12
index:13
index:14
index:15
index:16
index:17
index:18
index:19
index:20
index:21
index:22
index:23
index:24
index:25
index:26
index:27
index:28
index:29
index:30
index:31
index:32
index:33
index:34
index:35
index:36
index:37
index:38
index:39
index:40
index:41
index:42
index:43
index:44
index:45
index:46
index:47
index:48
index:49
index:50
202.08696603775 秒
計測終了2019-09-16 10:12:47

CSVに保存してLOAD DATA IN FILEでDBに保存する

LOAD DATA IN FILEを有効化

MySQLで設定--local-infile=1を有効にする。

Laravelのconfigファイルを設定
config/database.php

<?php
        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_EMULATE_PREPARES => true, // 追加
                PDO::MYSQL_ATTR_LOCAL_INFILE => true, // 追加
            ]) : [],
        ],
<?php
    public function handle()
    {
        // storageディレクトリにtmpディレクトリを作成
        $file_path = storage_path() . '/tmp/data.csv';
        $client = new Client();
        $file = new SplFileObject($file_path, 'w');
        $list = $client->get()->list;
        // CSVに書き込む
        foreach ($list as $value) {
            $file->fputcsv([$value]);
        }
        // CSV取り込み
        DB::statement("LOAD DATA LOCAL INFILE '$file_path' INTO TABLE products FIELDS terminated by ',' enclosed by '\"' escaped by '\"'");
        // ファイル削除
        unlink($file_path);
    }
計測開始2019-09-16 10:26:31
29.716847896576 秒
計測終了2019-09-16 10:27:01

7倍くらい速くなった。

他にもっといいやり方あったら教えてください。