クエリの累計時間にタイムアウトを設定する（１）

先日、管理サイトにてアラートが発生しました。調査すると、ある検索画面で利用者が重いクエリを連続で発行した事が原因でした。待ち時間が長かった為、不安になり検索ボタンを連打してしまったようです。サーバに負荷を掛ける操作については注意喚起するとして、待ち時間に制限が無いのはよくありません。そこで、検索処理に制限時間を設ける事にしました。今回はそちらの実装にあたって色々学んだことがあるので紹介します。

クエリの実行時間を制限する

今回実装した内容を簡単に説明すると、検索処理に対して実行可能時間を設定し、時間切れとなった場合にエラーを返すというものです、要はタイムアウトの設定です。検索処理においてボトルネックとなっていたのはDBへのクエリ部分で、それ以外の処理時間は無視して良いレベルでした。従って、クエリの実行時間に制限を設ける事にしました。

クエリにタイムアウトを設定する方法については過去の記事の「クエリーの実行所有時間の制限」の項目で紹介されており、今回もこちらを使います。簡単におさらいすると、DB::statement()を使用して、MySQLのmax_execution_timeを設定する方法です。例えば、以下は5秒に制限する場合のコードです。

DB::statement('SET SESSION MAX_EXECUTION_TIME = 5000');

こちらを実行すると、以降クエリで5秒以上掛かった場合にQueryExceptionがスローされます。

クエリの累計時間を制限する

ボトルネックとなるクエリが１つの場合は前項の方法で制限時間を設ければOKですが、今回の実装先には重いクエリが複数実行されており、それらの累計時間を制限する必要がありました。例えば、以下のように制限時間を５秒に設定した場合、クエリ１とクエリ２で合わせて５秒を超えたらタイムアウトエラーとします。

クエリの累計時間に制限を設けるには、クエリを実行する毎に都度残り時間を計算し、それを前項で紹介した方法で設定していけば良さそうです。つまり、以下の様に

クエリ１の時点では残り時間を５秒として制限時間を設定しますが、クエリ２の時点ではクエリ１で３秒掛かったので、残りの実行可能時間２秒を制限時間として再設定します。

DbQueryTimeout.php

ロジックについて理解が深まったところで、実際のコードを紹介します。クエリの制限時間を管理するクラスとして app/Services 配下に DbQueryTimeout.php を追加しました。

<?php

namespace App\Services;

use App\Exceptions\TimeoutException;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class DbQueryTimeout
{
    const TIMEOUT_QUERY = 'SET SESSION MAX_EXECUTION_TIME';

    public static $maxExecutionTime;  // 最大実行可能時間（ミリ秒）

    public static $processStartTime;  // プロセス開始時刻（マイクロ秒）

    // タイムアウト設定初期化
    public static function set(int $seconds): void
    {
        self::$maxExecutionTime = $seconds * 1000;
        self::$processStartTime = microtime(true);

        DB::beforeExecuting(function ($query) {
            // タイムアウト設定のクエリなら何もしない
            if (Str::startsWith($query, self::TIMEOUT_QUERY)) {
                return;
            }

            // 残りの実行可能時間を計算
            $timeRemaining = self::timeRemaining();

            // 実行可能時間が残っていないなら例外スロー
            if ($timeRemaining <= 0) {
                throw new TimeoutException;
            }

            self::setTimeout($timeRemaining);
        });
    }

    // タイムアウト設定
    public static function setTimeout(int $time): void
    {
        DB::statement(self::TIMEOUT_QUERY.' = '.$time.';');
    }

    // 残りの実行可能時間をミリ秒で返す
    public static function timeRemaining(): int
    {
        return self::$maxExecutionTime - self::elapsedTime();
    }

    // 経過時間をミリ秒で返す
    public static function elapsedTime(): int
    {
        return (int) ((microtime(true) - self::$processStartTime) * 1000);
    }
}

後で説明しますが、このクラス内で使用されている TimeoutException は自作の例外クラスになります。app/Exceptions 配下に TimeoutException.php を追加してください。

<?php

namespace App\Exceptions;

use Exception;

class TimeoutException extends Exception
{
}

DbQueryTimeout の使い方はとてもシンプルで、実行時間を制限したい箇所の最初にset()を実行するだけです。set()の引数には制限時間を秒で指定します。例えば、制限時間を５秒としたいなら以下の様に。

DbQueryTimeout::set(5);

すると、set()が実行された時点からカウントダウンが始まり、タイムオーバーとなった場合は以下の２パターンでエラーが発生します。

1. クエリ実行中
2. クエリ実行外で、それ以後にクエリを実行しようとした

前者の場合はQueryExceptionがスローされ、後者の場合はTimeoutExceptionがスローされます。

参考までに実装例を掲載します。以下は注文データを検索するページです。

<?php

namespace App\Http\Controllers;

use App\Models\Invoice;
use App\Models\InvoiceShip;
use Illuminate\Http\Request;
use App\Services\DbQueryTimeout;
use Illuminate\Contracts\View\View;

class InvoiceSearchController extends Controller
{

    /**
     * 検索画面
     */
    public function index(Request $request): View
    {
        $input = $request->all();

        // 制限時間を5秒に設定
        DbQueryTimeout::set(5);

        // 注文データ
        $invoices = Invoice::query()
            ->whereBetween('invoice_date', [$input['date_start'], $input['date_end']])
            ->where('invoice_status', '=', $input['invoice_status'])
            ->orderBy('invoice_date', 'desc')
            ->paginate(config('local.items_per_page'))
            ->withQueryString();

        // 注文配送データ
        $invoice_ships = InvoiceShip::query()
            ->whereIn('invoice_id', $invoices->pluck('invoice_id'))
            ->where('invoice_ship_status', '=', $input['invoice_ship_status'])
            ->get();

        return view('admin.invoice_index')
            ->with(compact('input', 'invoices', 'invoice_ships'));
    }

}

上記のコントローラのindexアクションでは以下の３つのクエリが実行されています。

1. １ページに表示する注文データ
2. 注文データの件数
3. 注文データに紐づく注文配送データ

これら３つのクエリの累計実行時間が５秒を超える場合はエラーが発生します。エラーが発生した場合の処理については次の投稿で紹介します。

コードのポイント

コア部分であるset()で行われている処理について補足です。set()ではクエリ毎に実行されるbefore処理を登録しています。before処理では以下の処理が実行されます。

1. 実行されるクエリが制限時間の設定クエリなら何もせず、そのまま実行
2. それ以外のクエリなら残りの実行可能時間を計算
3. 残り時間なし（既にタイムオーバー）ならTimeoutExceptionをスロー
4. 残り時間があるならそれを制限時間として設定

before処理の登録にDB::beforeExecuting()を使用している点がポイントです。こちらはドキュメントには載っていませんが、イベントリスナーの様にEloquentやクエリビルダにてクエリが実行される毎に直前に呼び出すコールバックを登録できます。余談ですが、after処理の登録にはDB::listen()というメソッドがあります。（こちらはドキュメントにも載っている。）

DB::beforeExecuting()のクロージャの引数$queryはSQLが文字列で渡されます。1. 実行されるクエリが制限時間の設定クエリなら何もせず、そのまま実行ではそちらが、SET SESSION MAX_EXECUTION_TIMEから始まるクエリかチェックして、そうならbefore処理を終了しそのままクエリの実行に移ります。そうしなければ、この制限時間を設定するクエリに対しても更にbefore処理をして、、、と無限ループとなってしまうからです。

長くなってしまったので、一旦ここで切ります。次回はエラー処理について解説します。