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倍くらい速くなった。

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

LEMP環境をDockerで構築

今までPHPを動かすときは、DockerHubからPHPApacheが一体になったImageを使用していたのだけど、nginxも使ってみたかったのでphp-fpm+nginx環境をDockerで立ち上げてLaravelを動かしてみた。

今まではApacheが同梱されていてコンテナを立ち上げてlocalhostにアクセスしたらすぐ動いたのだけどnginxの設定を初めてした。

fastcgiの設定あたりがよくわかっていないので、HTTPサーバが動く仕組みを理解していないのだと思う。

server {
    listen 80 default;
    server_name _;
    root /var/www/html/;
    index index.php index.html index.htm;
    charset utf-8;
    error_log  /var/log/nginx/error.log;
    access_log /var/log/nginx/access.log;

    location / {
        try_files $uri $uri/ /index.php$is_args$args;
    }

    location ~ \.php$ {
        include fastcgi_params;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_pass ${PHP_APP_HOST}:9000;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_param PATH_INFO $fastcgi_path_info;
    }
}

/var/www/htmlにボリュームをマウントしていてLaravelを動かしてみたのだけれど、困ったのは$ php artisan make:xxxコマンドを使用して作成したファイルがホスト側から読み取り専用ファイルになってしまったこと。

rootユーザではなくwww-dataユーザとしてartisanコマンドを実行したら解決した。

docker exec -u www-data -it php-fpm bash

github.com

ValueObjectっぽいものを導入したらいい感じになってきた

今までAPIで取得した値を加工・計算したい場合とりあえず連想配列に入れてゴニョゴニョしていた。
連想配列にいろいろなデータ詰め込みすぎると何入ってるのかわかんなくなるし、面倒なのがある条件のときだけ存在しないキーがあったりしたら、isset()とかempty()で確認するif文が増えてわけわかんなくなる。

<?php
// こういうの。nullだったり、キーがなかったりする。
Array
(
    [0] => Array
        (
            [key] => Array
                (
                    [key] => value
                    [key2] => value
                )

        )
    [1] => Array
        (
            [key] => Array
                (
                    [key] => null
                )

        )

)

さらにforeachで回してデータを取り出して保存するみたいな処理の場合、ロジックがあちこちに重複してしまっていたりする。
例えば、ある値は0~100までの値しか入っていないことを期待しているのだけど、本当に0~100までの値しか来ないのか、間違って変な値が入っていたら困るのでこういうif文を書くことになる。

<?php
        if (0 <= $value && $value <= 100) {
           $this->foo();
        }

それで今日も今まで通り連想配列に入れてテスト書いたり、ソートして最小値取り出すみたいなことしてたんですが、やっぱダメだなこれみたいに思って、ValueObjectみたいな入れ物クラスを作ってみたら、これが良かった。
まずPHPDocを書いたら補完が効くからキー名を間違える心配がない。オブジェクトが入った配列をforeachで回してデータを取り出すときに楽。配列に何が入っているのかを考えてキー名を自分で書く必要がない。

<?php
    /**
     * @param Value[] $data
     * @return array
     */
    public function getData(array $arr): array
    {
        $data = [];
        foreach ($arr as $row) {
   // ここで補完が効く
            $data[] = $row->getMin();
        }
        return $data;
    }

あとは上記のちょっとしたif文をどこに書くべきか迷わなくて済む。
こういうちょっとしたif文のために新しいクラスを作るのはおかしい気がするし、ヘルパー関数とかに書くのも釈然としない。
でもValueObjectのコンストラクタに書けば、テストも書きやすいし、入っていい値のルールみたいなロジックがあちこちに重複することもない。

<?php
public function construct(int $value)
{
        if (0 > $value || 100 < $value) {
           throw new InvalidValueException("value:{$value} 不正な値です");
        }
}

これをValueObjectと呼ぶべきなのかはわからないけど、なんかちょっとしたブレークスルーになった気がした。

Spider Engineを構築する

Spider環境構築

ローカルの開発環境にMariaDBをインストールし、複数DBを立ち上げSpider Engineを試す。

環境:
mysql Ver 15.1 Distrib 10.4.7-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Linux 5.0.0-27-generic #28~18.04.1-Ubuntu

MariaDBのインストール

$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
$ sudo apt-get update -y
$ sudo apt-get upgrade -y
$ sudo apt install mariadb-server-10.4 -y

unix_socketがどうとかでrootユーザでログインできないので別の管理者ユーザを作成する

mysql - mariadb password and unix_socket authentication for root - Stack Overflow MariaDB 10.4.1〜のユーザー認証がカオスな話(Unix_Socket) | GIZMELER

$ su -
$ mysql
MariaDB [(none)]> use mysql;
MariaDB [(none)]> CREATE USER admin@localhost IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION;
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> exit

mysqld_multiの設定

/etc/mysql/my.cnf

バックアップ取る

$ sudo cp my.cnf my.cnf.bk

my.cnfに追加

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysql/multi.log
user       = admin
password   = password

# インスタンス1の設定
[mysqld1]
port = 3307
datadir = /var/lib/mysql1
pid-file = /var/run/mysql1/mysql.pid
socket = /var/run/mysql1/mysql.sock
log-error = /var/log/mysql/error1.log
general_log_file = /var/log/mysql/query1.log

# インスタンス2の設定
[mysqld2]
port = 3308
datadir = /var/lib/mysql2
pid-file = /var/run/mysql2/mysql.pid
socket = /var/run/mysql2/mysql.sock
log-error = /var/log/mysql/error2.log
general_log_file = /var/log/mysql/query2.log

# インスタンス3の設定
[mysqld3]
port = 3309
datadir = /var/lib/mysql3
pid-file = /var/run/mysql3/mysql.pid
socket = /var/run/mysql3/mysql.sock
log-error = /var/log/mysql/error3.log
general_log_file = /var/log/mysql/query3.log

それぞれのインスタンス用にディレクトリを作る

$ mysql_install_db --datadir=/var/lib/mysql1 --user=mysql
$ mysql_install_db --datadir=/var/lib/mysql2 --user=mysql
$ mysql_install_db --datadir=/var/lib/mysql3 --user=mysql
$ chown -R mysql:mysql /var/lib/mysql1 /var/lib/mysql2 /var/lib/mysql3
mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql
sudo mysqld_multi start

エラーの場合は、/var/log/mysql/multi.log にログが出る。

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin

ここのmysqldのバイナリパスを間違うとこんなエラーが出る

FATAL ERROR: Tried to start mysqld under group [mysqld1], but no mysqld binary was found.
Please add "mysqld=..." in group [mysqld_multi], or add it to group [mysqld1] separately.

$ where mysqld_safe でパスを探して正しいパスを入れると直る。

mysqld1に入る

$ sudo mysql -uroot --socket=/var/run/mysql1/mysql.sock
MariaDB [(none)]> show variables like 'port';

port:3307のはず

adminユーザを作成する

MariaDB [(none)]> CREATE USER admin@localhost IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION;
MariaDB [(none)]> flush privileges;

mysqld2とmysqld3も同じようにユーザを作成する

Spider Engineのインストール

mysqld1をSpiderノードにして、mysqld2とmysqld3をデータノードにする

Spider Installation - MariaDB Knowledge Base

If Spider does not appear in this list, you need to run the configuration script. This is installed with the MariaDB server in CentOS. Debian distributions need to install the mariadb-plugin-spider package first. The package is usually found in

debian系は自分で入れないといけないらしい。

sudo apt install mariadb-plugin-spider

$ ls /usr/share/mysql/ install_spider.sql が作成されているのでmysqld1で実行する

MariaDB [(none)]> source /usr/share/mysql/install_spider.sql

データノードを登録

MariaDB [(none)]> CREATE SERVER mysqld2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'admin', PASSWORD 'password', HOST '127.0.0.1', PORT 3308);
MariaDB [(none)]> CREATE SERVER mysqld3 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'admin', PASSWORD 'password', HOST '127.0.0.1', PORT 3309);

mysqld1にテーブルを作成

CREATE TABLE employees
(
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    department_id INT(11) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    PRIMARY KEY (id)
) ENGINE = SPIDER DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(id) (
  PARTITION p1 comment 'server "mysqld2", table "employees"',
  PARTITION p2 comment 'server "mysqld3", table "employees"'
);

mysqld2とmysqld3に同じテーブルを作成。 innoDBでいいらしい。

CREATE TABLE employees
(
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    department_id INT(11) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4;

データを挿入

INSERT INTO employees(name, department_id, created_at) VALUES ('Tom', 1, NOW());
INSERT INTO employees(name, department_id, created_at) VALUES ('Jim', 2, NOW());
INSERT INTO employees(name, department_id, created_at) VALUES ('Watson', 3, NOW());

mysqld2とmysqld3に分散されて保存される。

mysqld1から, SELECT * FROM books WHERE title LIKE '%JAVA' で普通に取れる。

JOINを試す

mysqld1に別のテーブルを作成

CREATE TABLE departments
(
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = SPIDER DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(id) (
  PARTITION p1 comment 'server "mysqld2", table "departments"',
  PARTITION p2 comment 'server "mysqld3", table "departments"'
);

mysqld2,mysqld3にテーブルを作成。

CREATE TABLE departments
(
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO departments(name) VALUES ('部署1');
INSERT INTO departments(name) VALUES ('部署2');
INSERT INTO departments(name) VALUES ('部署3');
SELECT *
FROM employees AS e
INNER JOIN departments AS d ON d.id = e.department_id

JOINできた。すごい。

参考:
akishin.hatenablog.jp ngyuki.hatenablog.com mariadb.com Ubuntu 18.04 LTS に MariaDB 10.3 をインストール - Qiita