ロコ記念(G1) 最速のDBを決める2020

はじめに

　「Robotic Crowdを支える技術」というテーマで、全エンジニア＋弊社カスタマーサクセスチームのゲスト3人が25日間毎日お送りする社員発のアドベントカレンダー「Tutorial Advent Calendar 2020」も、本日が最終日となりました。最後の記事を投稿するのは、3度めの登場になるSREの平田です。よろしくおねがいします。

　今回はRoboticCrowdでも利用しているCloudSQLでサポートされている3つのエンジン(MySQL, PostgreSQL, SQL Server)について、パフォーマンスの検証をしてみたいと思います。第して、ロコ記念(G1) 最速のDBを決める2020です。

レースの概要

騎手(実行・計測環境)

　レースは全てPHPのPDOまたはネイティブモジュールにて接続を行います。実行環境は統一的に以下の通りにします。

• GCP Compute Engine 1台
• • e2-small (2vcpu 2GBRAM)
  • 東京リージョン
• Ubuntu 20.04 LTS
• PHP 8.0

出走馬

　まずは定番、MySQL on CloudSQL！もはやこれを知らねばWebエンジニアと呼べぬ、超定番RDBMSのMySQLは、GCPでももちろん一番の人気者です。「簡便で高機能で無料」を売り物にしており、ビギナーでもサクッとインストールしてサクッと利用できることが人気の秘訣です。db-engines.comの最新ランキングでは安定的な2位の座にいて、原則有償プロダクトであるOracleとSQL Serverを除いた無料のDBエンジンの中では圧倒的な1位を確保しています。

　今回はCloudSQLのdb-n1-standard非冗長化インスタンス(2vCPU / 7.5GBRAM / asia-northeast1-b)での参加となります。MySQLのバージョンは8.0です。

　続いて、こちらも定番PostgreSQL on CloudSQL！こちらもまた知らねばモグリを疑う有名なRDBMSです。かつてMySQLよりもシェアの多かったPostgreSQLですが、InnoDBの高性能化・高機能化やOracleブランドの前に後塵を拝する今日此頃。しかし、最古参1988年生まれでまだまだ根強いシェアのあるPostgreSQLが、打倒MySQLのもとに登場しました！

　今回はMySQL同様、CloudSQLのdb-n1-standard非冗長化インスタンス(2vCPU / 7.5GBRAM / asia-northeast1-b)での参加となります。PostgreSQLのバージョンは13です。

　最後に登場するのは、IT界の巨人Microsoftが誇るベストセラーであるSQLServer on CloudSQLです！GUIからの操作や手厚いサポートがビジネスユーザの心をくすぐり続け、db-engines.comの最新ランキングでも常にOracleの背後を追い続けています。だいぶ前の話になりますが、私も個人で何度か試して遊び、Microsoftの資格を取ったりしました。当時と比べると、かなり進化しているなという感想を持っています。

　今回は前者2つと同様、CloudSQLのdb-n1-standard非冗長化インスタンス(2vCPU / 7.5GBRAM / asia-northeast1-b)での参加となります。SQLServerのバージョンは2017で、エディションはStandardです。

コース概要

　今回は大きく分けて4つのフェーズに分類されます。

　第1コーナーまでの区間が最初のINSERTで、乱数(1000～9999)を1件ずつ100件代入します。続いて第2コーナーまでの短い区間として、それを全件取得します。そして第3コーナーでは、最初のINSERTと同じく単純なINSERTとなりますが、件数を増やしてBULK INSERTを行います。その後最終コーナーで更に全件取得し、ようやくゴールとなります。

　コードはざっくりと以下の通りになります。まずはMySQL用のコード。

<?php

$Mysql = new Mysqli('HOST', 'USER', 'PASS', 'DBNAME');

// INSERT 100 rows
$firstLap = 0;
for ($i=0;$i<100;$i++) {
        $number = random_int(1000, 9999);

        $firstStartAt = hrtime(true);
        $Mysql->query('INSERT INTO numbers VALUES (null, ' . $number . ');');
        $firstEndAt = hrtime(true);

        $firstLap += $firstEndAt - $firstStartAt;
}

// SELECT rows
$secondStartAt = hrtime(true);
$Result = $Mysql->query('SELECT * FROM numbers;');
$secondEndAt = hrtime(true);

$secondLap = $secondEndAt - $secondStartAt;

// INSERT 100000 rows
$insertValue = '';
for($i=0;$i<100000;$i++) {
        $number = random_int(1000, 9999);
        $insertValue = $insertValue . '(null, ' . $number . ')';
        if ($i !== 99999) {
                $insertValue = $insertValue . ', ';
        }
}
$insertQuery = 'INSERT INTO numbers VALUES ' . $insertValue . ';';

$thirdStartAt = hrtime(true);
$Mysql->query($insertQuery);
$thirdEndAt = hrtime(true);

$thirdLap = $thirdEndAt - $thirdStartAt;

// SELECT rows
$fourthStartAt = hrtime(true);
$Result = $Mysql->query('SELECT * FROM numbers;');
$fourthEndAt = hrtime(true);

$fourthLap = $fourthEndAt - $fourthStartAt;

// TotalLap
$totalLap = $firstLap + $secondLap + $thirdLap + $fourthLap;

printf('FirstLap: %s / SecondLap: %s / ThirdLap: %s / FourthLap: %s / TotalLap: %s', $firstLap, $secondLap, $thirdLap, $fourthLap, $totalLap);

　PDOにしようか迷いましたが、MySQLiが安定していることやコードが長くなりそうな予感から、MySQLi を利用しました。

　続いてPostgreSQL用のコード。これもPDOを使うか迷いましたが、昔ながらのpg_connectをあえて使ってみました。

<?php

$Pg = pg_connect('host=10.105.225.4 dbname=tac2020 user=root password=password');

// INSERT 100 rows
$firstLap = 0;
for ($i=0;$i<100;$i++) {
        $number = random_int(1000, 9999);

        $firstStartAt = hrtime(true);
        pg_query($Pg, 'INSERT INTO numbers (number) VALUES (' . $number . ');');
        $firstEndAt = hrtime(true);

        $firstLap += $firstEndAt - $firstStartAt;
}

// SELECT rows
$secondStartAt = hrtime(true);
$Result = pg_query($Pg, 'SELECT * FROM numbers;');
$secondEndAt = hrtime(true);

$secondLap = $secondEndAt - $secondStartAt;

// INSERT 100000 rows
$insertValue = '';
for($i=0;$i<100000;$i++) {
        $number = random_int(1000, 9999);
        $insertValue = $insertValue . '(' . $number . ')';
        if ($i !== 99999) {
                $insertValue = $insertValue . ', ';
        }
}
$insertQuery = 'INSERT INTO numbers (number) VALUES ' . $insertValue . ';';

$thirdStartAt = hrtime(true);
pg_query($Pg, $insertQuery);
$thirdEndAt = hrtime(true);

$thirdLap = $thirdEndAt - $thirdStartAt;

// SELECT rows
$fourthStartAt = hrtime(true);
$Result = pg_query($Pg, 'SELECT * FROM numbers;');
$fourthEndAt = hrtime(true);

$fourthLap = $fourthEndAt - $fourthStartAt;

// TotalLap
$totalLap = $firstLap + $secondLap + $thirdLap + $fourthLap;

printf('FirstLap: %s / SecondLap: %s / ThirdLap: %s / FourthLap: %s / TotalLap: %s', $firstLap, $secondLap, $thirdLap, $fourthLap, $totalLap);

最後にSQLServer用のコードです。彼らだけは専用の機能がないため、PDOのODBCからアクセスすることになります。もう既にこの時点で若干ハンデがありますが、どこまで健闘できるか楽しみです。

<?php
putenv('ODBCSYSINI=ODBC_SYS_INI_PATH');
putenv('ODBCINI=ODBC_INI_PATH');
$MssqlPDO = new PDO('odbc:DATA_SOURCE', 'USER', 'PASSWORD');

// INSERT 100 rows
$firstLap = 0;
for ($i=0;$i<100;$i++) {
        $number = random_int(1000, 9999);

        $firstStartAt = hrtime(true);
        $FirstStatement = $MssqlPDO->prepare('INSERT INTO numbers VALUES (' . $i + 1 . ', ' . $number . ');');
        $FirstStatement->execute();
        $firstEndAt = hrtime(true);

        $firstLap += $firstEndAt - $firstStartAt;
}

// SELECT rows
$secondStartAt = hrtime(true);
$SecondStatement = $MssqlPDO->prepare('SELECT * FROM numbers;');
$SecondStatement->execute();
$secondEndAt = hrtime(true);

$secondLap = $secondEndAt - $secondStartAt;

// INSERT 100000 rows
$insertValue = '';
for($i=0;$i<100000;$i++) {
        $number = random_int(1000, 9999);
        $insertValue = $insertValue . '(' . $i + 100 . ', ' . $number . ')';
        if ($i !== 99999) {
                $insertValue = $insertValue . ', ';
        }
}
$insertQuery = 'INSERT INTO numbers VALUES ' . $insertValue . ';';

$thirdStartAt = hrtime(true);
$ThirdStatement = $MssqlPDO->prepare($insertQuery);
$ThirdStatement->execute();
$thirdEndAt = hrtime(true);

$thirdLap = $thirdEndAt - $thirdStartAt;

// SELECT rows
$fourthStartAt = hrtime(true);
$FourthStatement = $MssqlPDO->prepare('SELECT * FROM numbers;');
$FourthStatement->execute();
$fourthEndAt = hrtime(true);

$fourthLap = $fourthEndAt - $fourthStartAt;

// TotalLap
$totalLap = $firstLap + $secondLap + $thirdLap + $fourthLap;

printf('FirstLap: %s / SecondLap: %s / ThirdLap: %s / FourthLap: %s / TotalLap: %s', $firstLap, $secondLap, $thirdLap, $fourthLap, $totalLap);

ラップタイムの計測方法

　先述のコードの通り、4つのフェーズ全てのタイムをナノ秒単位で計測し、その合計を比較します。可能な限りPHPのコードによる所要時間を省いて計測しますが、例えばPDOやMySQLiの実行ラグのようなものは仕方ないものとして計上するものとします。

　計測は各DB(スクリプト)ごとに3回ずつ行って、算術平均の整数以下四捨五入にて勝負を行います。

測定結果

　さあ各馬スタート！まずは第1コーナー

～中略～

各馬ゴールしました！さて気になるラップタイムの集計も終わったようです。いよいよ結果発表・・・！

• MySQL on Cloud SQL
• • 1,441,640,630ナノ秒 (約1.442秒)
• PostgreSQL on CloudSQL
• • 458,292,969ナノ秒 (約0.458秒)
• SQL Server on CloudSQL
• • 2,825,973,191ナノ秒 (約2.826秒)

　ということで、「ロコ記念(G1) 最速のDBを決める2020」の優勝者はPostgreSQL on Cloud SQLとなりました！！！

　CloudSQLのスペックや書き込むデータの大きさ、はてはOSやミドルウェアや計測コードまで揃えて臨んだ計測でしたが、予想よりも大きな差が生まれました。前評判ではMySQLが一番人気でしたが、なんと驚きの1秒差。PostgreSQLなかなかやるなという印象です。

　一方、思った以上に善戦したと感じたのはSQLServerです。正直微妙なことこの上ないODBCドライバを使うために外部の設定ファイルを余計に読み込んだ上にPDOを使ったため、他の2つよりも処理が多くなりました。そんなハンデを2つ背負った状態でもMySQLから比べて2倍まで開かなかったため、なかなかの健闘ではないかと思われます。

　MySQLの記録が伸びなかった点ですが、見た感じINSERTでかなりの時間がかかっていました。1件ずつのINSERTでも100000件のBULK INSERTでも同様に時間が伸びていたため、PostgreSQLよりもチューニングが必要なのかもしれないと感じました。

　とはいえ、MySQLとSQL Serverが本領を発揮するのは、サーバ設定やIndexによるクエリ最適化、複雑な結合やトランザクションを含んだSQL、はたまたレプリケーションを併用したケースです。特に、様々なサーバ環境変数を利用することで柔軟なチューニングが可能なMySQLは、このレースでは全く本気が出せていなかったと言っても過言ではないでしょう。

結び

　今回は単純なINSERTとSELECTのみでしたが、今度機会があればトランザクション付きのINSERTやALTER TABLEについても計測をしてみたいと思います。

　そして本日のこの投稿をもちまして、Tutorial Advent Calendar 2020 (TAC2020)の全投稿が出揃い、無事完走いたしました。投稿したメンバー各位、ご覧いただいた皆様、ご協力ありがとうございました。想像した以上に質が高く、また色々なテーマの色々な見地からの記事が集まったなというのが、個人的な率直な感想です。

　来年もまたTutorial Advent Calendar 2021(TAC2021)として行う予定です。また、それまでにも、ちょくちょくメンバーのアウトプットがあると思いますので、Tutorial Tech Blogを定期的にチェックして頂けると幸いです。