Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databasesを読む(その3 ログ is データベース)

3. THE LOG IS THE DATABASE

Auroraはセクション2で紹介したように、セグメントに分けられて複製されているストレージシステムになっている。

このようなストレージシステムを使って従来のデータベースを動かすと、ネットワークI/O、同期のストール(失速)の観点で受け入れられないほどのパフォーマンスの負荷がかかる。

セクション3ではその理由を説明する。

そして、ログ処理をストレージサービスに押し付けるアプローチを説明し、このアプローチがネットワークI/Oを劇的に削減できることを実験的に説明する。

最後に、同期ストールと不要な書き込みを最小化するためにストレージサービスで利用している様々な技術について説明する。

3.1 増幅された書き込みの負荷

ストレージボリュームをセグメント化し、各セグメントは6つに複製され、そのうち4つが書き込みクオラム(4/6)というモデルでは高い回復力が得られる。

しかし、このモデルは、アプリケーションの書き込みごとに多くの異なる実際のI/Oが発生するMySQLのような従来のデータベースだとひどいパフォーマンスの低下を引き起こす。

この高いI/Oボリュームはレプリケーションによって増幅され、PPS(1秒あたりのパケット数)をさらに増加させる。

これらのI/Oは同期の起点になり、パイプラインを停滞させ、レイテンシーを増大させる。

従来のデータベースで書き込みがどのように行われるか見てみる。

MySQLのようなシステムは、データページを公開するオブジェクト（ヒープファイル、Bツリーなど）に書き込むとともに、redoログレコードをWAL（Write-Ahead Log）に書き込む。

各redoログレコードは、変更されたページのビフォアーイメージとアフターイメージの差分で構成される。

ログレコードは、ページのビフォアーイメージに適用され、アフターイメージを生成する。

dev.mysql.com

(データベースシステム 改定2版 p236)

実際には、他にもデータも書き込まなければならない。

例えば、図2に示すように、データセンター間で高可用性を実現し、アクティブ・スタンバイ構成で動作する同期ミラーリングされたMySQLの構成を考えてみる。

AZ 1にアクティブなMySQLインスタンスがあり、Amazon Elastic Block Store (EBS)上にネットワーク接続されたストレージがある。

また、AZ 2にはスタンバイ用のMySQLインスタンスがあり、こちらもEBS上のネットワーク接続されたストレージがある。

プライマリのEBSボリュームへの書き込みは、ソフトウェアミラーリング(レプリケーションのことかな)を使用してスタンバイのEBSボリュームと同期する。

Figure 2(下記)に、データベースエンジンが書き込む必要がある様々なデータの種類を示す。

• redoログ
  • Bツリーの実装を持つデータベースにクラッシュ耐性を持たせるため、追記のみを行うファイルを作り、Bツリーへのすべての変更内容をツリーそのもののページに反映させる前に書き込むもの。データベースのクラッシュ後にリカバリするときにこのログを使ってBツリーを整合性の取れた状態に回復させる
    • データ指向アプリケーションデザイン p 88
    • 詳解MySQL5.7 p 125
  • MySQLだと ib_logfile* に格納されている
• バイナリ(ステートメントベースを想定)ログ(binログってやつ)
  • レプリケーションとデータリカバリに使われる
  • ポイントインタイムリカバリをサポートするためにS3に保存される
• ページの破損を防ぐためのデータページのダブルライトのための書き込み
  • ダブルライトバッファへの書き込み(バッファといいつつ実態はファイル)
  • MySQL :: MySQL 8.0 Reference Manual :: 15.6.4 Doublewrite Buffer
• 変更されたデータページ本体
• メタデータ（FRM）ファイル
  • MySQL 8以降だとこのファイルなくなるけどどうするんかな

ここらへんの参考資料

dev.mysql.com

dev.mysql.com

dev.mysql.com

gihyo.jp

enterprisezine.jp

www.percona.com

dev.mysql.com

qiita.com

www.percona.com

koreshiki-nanno.hatenablog.com

また、図2では、実際のI/Oフローの順序を以下のように示している。

ステップ1とステップ2で、EBSに書き込みが発行し、そして今度はAZのローカルミラーに発行し、両者が完了した時点でackを受け取る。

次に、ステップ3では、同期的なブロックレベルのソフトウェアミラーリングによって、スタンバイインスタンスへの書き込みが順次行われる。

最後に、ステップ4と5で、スタンバイインスタンスのEBSとミラーに書き込みが行われる。

このミラーリングするMySQLのモデルは、データを書き込み方法だけでなく、書き込むデータの内容の点からも望ましくない。

1. ステップ1、3、5は順次的で同期的

   • 多くの書き込みに順番があるのでレイテンシがかさむ
   • 非同期な書き込みであったとしても、最も遅い動作に律速されるためシステムが外れ値に翻弄され、ジッター(ゆらぎ)が増幅される
   • 分散システムの観点からだと、このモデルは4/4の書き込みクオラムを持っているため、障害や外れ値のパフォーマンスに対して脆弱です。

2. OLTPアプリケーションのユーザー操作は、多くの場合、同じ内容を複数の方法で異なる種類の書き込みを引き起こす

   • データ指向アプリケーションデザイン p 97参照、 オンライン トランザクション処理 (OLTP) - Azure Architecture Center | Microsoft Docs
   • 例えば、ストレージインフラストラクチャでページが破損しないようにするためのダブルライトバッファへの書き込みなど。

3.2 redo処理をストレージに追い出す

従来のデータベースでは、ページを変更すると、redoログのレコードを生成し、redoログのレコードをページのメモリ上のビフォアーイメージに適用して新しいイメージを生成するログアプリケータ(配布するなにかをさしてそう)を呼び出す。

トランザクションコミットはログを書き込む必要がありますが、ページへの書き込みは非同期に行われることがある。

Auroraでは、ネットワークをまたぐ書き込みはredoログレコードのみとなる。

バックグラウンドの書き込み、チェックポイントのための書き込み、キャッシュのエビクションのための書き込みなど含め、データベース層からもはやページが書き込まれることはない。

その代わりに、ログアプリケータをストレージ層に置いて、そこでバックグラウンドまたはオンデマンドでデータベースのページを生成するようにする。

もちろん、初めからの変更の完全な一連の流れ(ストリームっていってもいいかな)から各ページを1から生成するのは莫大なコストがかかる。

そこで私たちは、データベースのページをバックグラウンドで継続的に実体化(マテリアライズドビューに似ているなにかかな)することで、必要に応じて毎回1からページを再生成することを避ける。

(つまりキャッシュを作っておく。データ指向アプリケーションデザイン p 459あたりを参考)

妥当性、精度の観点から、バックグラウンドでの実体化は完全に任意である。

データベースエンジンの関心としては、ログがデータベース本体であり (the log is the database)、ストレージシステムが実体化するページはログのアプリケーションのキャッシュに過ぎない。

チェックポイントとは異なり、変更の長いチェーンを持つページのみが再実体化が必要があることにも気をつけること。

(データ指向アプリケーション p 521あたりのことを言っていそう)

チェックポイントは、redoログチェーン全体の長さに支配されている。

Auroraのページの実体化は、指定されたページのチェーンの長さに支配されている。

Auroraでのアプローチでは、レプリケーションのための書き込みが増幅しているにもかかわらずネットワーク負荷を劇的に軽減させ、さらに耐久性やパフォーマンスの向上を実現した。

ストレージサービスは、データベースエンジンの書き込みのスループットへの影響を与えることなく、独立して並行(embarrassingly parallel)に実行できる方法でI/Oをスケールアウトすることができる。

en.wikipedia.org

Embarrassingly parallelとは？ - Qiita

https://www.allthingsdistributed.com/files/p1041-verbitski.pdf より参照

一つの例として、図3にあるような1つのプライマリインスタンスと複数のAZにまたがって配置された複数のレプリカインスタンス(とストレージサービス)を持つAuroraクラスタを考える。

このモデルでは、プライマリは ログレコード( これはredoログか？ )をストレージサービスにのみ 書き込み、それらのログレコードとメタデータ更新の内容をレプリカにストリームします。

このI/Oフローは

1. 宛先（論理セグメント、ここではPG）に基づいて完全に順序付けられたログレコードをひとまとめにする(バッチ化)
2. 各まとまりを6つのレプリカすべてに配信する
3. このまとまりはディスク上に永続化される
4. データベースエンジンは、6つの複製のうち4つからの確認応答を待つ(書き込みクオラムの安全性を満たし、かつログレコードに永続性があり、書き込まれているかを考慮するため)

レプリカインスタンスは、バッファキャッシュに変更を適用するためにredoログレコードを使用する。

ネットワークI/Oを測定するために、SysBenchの書き込み専用ワークロードを使い、下記の構成で100GBのデータセットを使用してテストを行った。

https://imysql.com/wp-content/uploads/2014/10/sysbench-manual.pdf

• 同期ミラーリングされた複数のAZにまたがるMySQL(ミラーリングMySQL)
• 複数のAZにまたがるレプリカを使用したRDS Aurora(Aurora)

それぞれの構成においては、r3.8xlarge EC2インスタンス上で動作するデータベースエンジンに対して30分間テストを行った。

https://www.allthingsdistributed.com/files/p1041-verbitski.pdf より参照

実験の結果は上記のTable 1となった。 (実験の詳細なパラメータ載ってないなあ。。。)

• AuroraのほうがミラーリングMySQLよりも35倍SysBenchのtransactionの値がよかった
• Auroraでのデータベースノード上のトランザクションあたりのI/O数は、ミラーリングMySQLの7.7倍も少ない
  • Auroraでは書き込みを6回に増幅させている(6クオラムに書き込み数を合計しているように読み取れる)
  • MySQLのEBS内の一連の複製やAZまたぎの書き込みをカウントしていない
  • 各ストレージノードは6つの複製のうちの1つにすぎないので、増幅されていない書き込みを見ていることになり、この階層で処理を必要とするI/O数が実質的には1/46になる。(7.7 x 6 = 46.2)

ネットワークを介したデータ書き込みを減らすことで得られるコスト削減により、耐久性と可用性のためにより積極的にデータを複製し、ジッターの影響を最小限に抑えることで並行にリクエストを処理することが実現できた。

また、処理をストレージサービスに移行することで、下記2点より耐久性と可用性の向上が実現できた。

• (多分インスタンスの)クラッシュリカバリ時間を最小限にできる
• チェックポイントやバックグラウンドデータのページ書き込み、バックアップなどのバックグラウンド処理に起因するジッターを排除することができる

さらにここでクラッシュリカバリーについてみていく。

従来のデータベースでは、クラッシュが発生した後、システムは最新のチェックポイントから開始し、ログを再生して、すべての永続的なredoレコードが適用されていることを確認しなければならない。

MySQLの例 dev.mysql.com

Auroraでは、耐久性のあるredoレコードの適用が継続的に、非同期的に、そしてインスタンス群全体に分散してストレージ層で行われる。

いくつかのページの読み込み要求は、ページが最新のものでない場合いくつかのredoレコードの適用が必要になることはある。

したがって、クラッシュリカバリのプロセスはすべての通常のフォアグラウンド処理に分散される。(つまりなんもしなくていいってことかな)

データベースの起動時には何もする必要ない。

3.3 ストレージサービスの設計のポイント

Auroraのストレージサービスの設計方針で重要な思想は、 フォアグラウンドの書き込み要求の待ち時間を最小化 することである。

ストレージ処理の大部分をバックグラウンドに移行した。

ストレージ層からのフォアグラウンドのリクエストのピークから平均までの変動を普通に考えると、フォアグラウンドのパスの外でこれらのタスク(ストレージ処理のことかな)を実行するための十分な時間がある。

CPU(の処理)とディスク(I/O)を入れ替えることもあります。

例えば、ストレージノードがフォアグラウンドの書き込み要求の処理(CPU側)で忙しいときに、disk容量にまだ余裕があれば古いページのバージョンのガベージコレクション(GC)を実行する必要はない。

Auroraでは、バックグラウンド処理とフォアグラウンド処理は負の相関関係を持っている。(つまりバックグラウンドの処理が増えればフォアグラウンドの処理は減る、vice versa)

これとは反対に、従来のデータベースはページのバックグラウンド書き込みやチェックポイント処理がシステムのフォアグラウンド処理と正の相関関係を持つ。

Auroraではシステム上にバックログが蓄積した場合、長いキューの蓄積を防ぐためにフォアグラウンド処理の制限を行う。

セグメントはシステム内の様々なストレージノードに高エントロピー(バラバラ度が高いってことかな)で配置されているため、1つのストレージノードでの制限は4/6のクオラム書き込みによって簡単に処理され、一つの低速なノードのように見える。

ストレージノード上の様々なアクティビティをより詳細に見てみる。

https://www.allthingsdistributed.com/files/p1041-verbitski.pdf より参照

Figure 4に示すように、以下のステップが含まれる

1. ログレコードを受信し、インメモリキューに追加する。
2. ディスクにレコードを永続化させて、ackする。
3. バッチによってはロストする可能性があるのでレコードを整理し、ギャップがないか確認する。
4. ノード間でやり取り(godsip protocol)してギャップを埋める。
   • HashiCorp tools を支える技術 - Qiita
   • ゴシッププロトコル - Wikipedia
5. ログレコードを新しいデータページと結合させる
6. 定期的にステージログと新ページをS3に送る
7. 古いバージョンを定期的にガベージコレクションする
8. ページのCRCコードを定期的に検証する
   • 巡回冗長検査 - Wikipedia
   • PNGで使うCRC32を計算する - Qiita

上記の各ステップは非同期的であるだけでなく、1と2のステップのみフォアグラウンドパスにあり、レイテンシに影響を与える可能性があることに注意。

参照

• redoログを使ったrecoveryの論文 http://citeseerx.ist.psu.edu/viewdoc/citations;jsessionid=E0C748756E083746A1DDD8EA23DB43B2?doi=10.1.1.219.7269

Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databasesを読む(その2 大規模における耐久性)

2 DURABILITY AT SCALE(大規模における耐久性)

データベースとは一度書き込んだら読み込みができなければならないのですが、全てのシステムはそうはなっていない。

ここでは、クォーラムのモデルの背後にある理論的根拠を示す。

• なぜストレージを分けるのかという理由(WHY)
• この2つ(データベースとストレージ？)を組み合わせが、耐久性、可用性、パフォーマンスのゆらぎの低減の獲得だけでなく、ストレージインスタン群を大規模に管理する際の運用上の課題も解決するという手法(HOW)

2.1 レプリケーションとそれに関連する障害

インスタンスの寿命とストレージの寿命はあまり相関しない。

インスタンスは落ちるしものだし、ユーザーはインスタンスをシャットダウンし負荷に応じてインスタンスサイズを変更したりする。

これらの理由から、コンピュート層とストレージ層を分離することは有効である。

ただし、切り離すとそれはそれでストレージ層のストレージノードやディスクも故障する可能性がある。

なので、ストレージ層の障害に対する回復力を持つために、何らかの形で複製する必要がある。(つまりバックアップ)

大規模なクラウド環境では、ノード、ディスク、ネットワークパスの障害という低レベルのバックグラウンドノイズが継続的に起きている。

これらの障害は、異なる継続時間と異なる影響範囲を持っている。

(例えば、ノードへのネットワークの可用性の一時的な欠如、再起動時の一時的なダウンタイム、ディスク、ノード、ラック、リーフ、スパインネットワークスイッチの永続的な障害、さらにはデータセンターの障害などが考えられます。)

www.kenschool.jp

複製されたシステムの障害に対する耐性を得るための1つのアプローチは、下記論文で述べられているようなクオラムベースの投票のプロトコルを利用することである。

http://www.andrew.cmu.edu/course/15-440/assets/READINGS/gifford79.pdf

(データ指向アプリケーション 5.4.1.2 読み書きのためのクオラムでも同じ論文が参照されている)

複製されたデータのアイテムのV個のコピーのそれぞれに投票権が割り当てられている場合、読み取り操作または書き込み操作は、それぞれVrの読み取りまたはVwの書き込みを取得しなければならない。

一貫性を持たせるためには、投票数は2つのルールに従わなければならない。

1. 各読み取りは、最新の書き込みの認識をしなければならない。(Vr + Vw > Vとして定式化される)
   • この規則は、読み取り対象のノードの集合が書き込み対象のノードの集合と交叉すること(かぶりがあること)を保証し、読み取りには少なくとも1つは最新バージョン(書き込み対象のノード)が含まれていることを保証している。
   • データ指向アプリケーション 5.4.1.2 読み書きのためのクオラムも参照
2. 各書き込みは、Vw > V/2として定式化された、競合する書き込みを避けるために過半数以上の書き込み対象が最新の書き込みを認識(最新の状態になっている)していなければならない
   • 同じデータ項目に対して書き込み操作が並列に発生しないことを保証する。

aws.amazon.com

1ノードダウンに対する耐性を持つための一般的なアプローチは、データを3ノード(V = 3)に複製し、2つの書き込みノード(2/3 過半数以上)(Vw = 2)と2つの読み取りノード(2/3 過半数以上)(Vr = 2)とすること。

しかし、上記の2/3クオラム(V=3, Vw = Vr = 2)では不十分で、Auroraは、V=6, Vw = 4, Vr = 3のクオラムモデルとしている。

Why?

その理由を理解するために、AWSのAZ(Availability Zone)の概念を理解する必要がある。

AZはリージョンのサブセットで、リージョン内の他のAZには低遅延リンクで接続されているが、電力、ネットワーク、ソフトウェアのデプロイなどのほとんどの障害に対して隔離されている。

グローバルインフラストラクチャリージョンと AZ

AWSのAZ(アベイラビリティーゾーン)とは？AZ障害が起きたときどうすればよいのか | CyberAgent Developers Blog

余談ですが、AZの表記について

AWSアカウントに因らずアベイラビリティゾーンを識別できるAZ IDを利用しよう #reinvent | DevelopersIO

データのレプリカをAZに分散させることで、大規模で典型的な障害の様相においては1つのデータレプリカにのみ影響があることが確実である。(これ言い切っているけどまあ、AZを跨いで障害なんて確率的にないよねってことが言い切れるからかな)

これは、3つのレプリカをそれぞれ別のAZに配置するだけで、小規模な個々の障害にだけでなく大規模な障害にも耐性があることを示している。

しかし、大規模なストレージインスタンス群における障害のバックグラウンドノイズというのは、任意の時点で、ディスクまたはノードの一部のサブセットに障害が発生し、修復している最中の可能性があることを示している。

これらの障害は、それぞれAZをまたいだノードで広がる可能性がある。

しかし、火災、屋根の故障、洪水などの一つのAZの障害は、他のAZで同時に障害が発生するとレプリカのクオラムを崩すことになる。

この時点で、2/3 読み取りクオラムモデルでは、2つのコピーが失われ、3つ目のコピーが最新であるかどうかを判断することができない。

これらの2つのことは下記のように言える。

• 各AZのレプリカ内の個別の障害にはそれぞれに関連はなし
• AZの障害はそのAZ内のすべてのディスクとノードと関連している

クオラムは、AZの故障だけでなく、同時に発生するバックグラウンドノイズの故障にも耐性を持つ必要がある。

Auroraでの設計

Auroraでは下記2点に対しての耐性を持つような設計にしている。

• (a)ある一つのAZ全体とさらに1つのノード(AZ+1)がダウンしてもデータ欠損が発生しない
• (b)ある一つのAZ全体がダウンしてもデータ書き込み能力に影響を与えない

Auroraは各データアイテムについて、AZ内で2つのコピーとを持ち、さらに3つのAZで行うため合計6つのコピーを持つようにしている。

Auroraでは、6つのコピーを使って、投票数 6 (V = 6)、書き込みクオラム4/6(Vw = 4)、読み取りクオラム3/6(Vr = 3)のクオラムモデルを使う。

このモデルでは下記が満たせる。

• (a) 1つのAZと1つのノード(つまり3つのノードの故障)ダウンではデータ読み取りは損なわれない
  • Vw = 2 Vr = 2 V = 3 にできるから Vr + Vw > V を満たせるってことかな
• (b) 任意の2つのノード(一つのAZ障害はこれに含まれる)ダウンでは書き込みは損なわれない
  • Vw = 3 V=4 で Vw > V/2が満たせるってことかな

読み取りクオラムを保証できると、レプリカのコピーを追加して書き込みクオラムを再構築することができる。

aws.amazon.com

分割されたストレージ

ここではAZ+1(つまり3つのノードの故障)が十分な耐久性を提供するかどうかという問題を詳細にみていく。

このモデルで十分な耐久性を提供するためには、平均復旧時間内に、関連のない障害が二回(二重障害)発生する確率が十分に低いことを保証する必要がある。

これには平均復旧時間(Mean Time to Repair - MTTR)と平均故障時間(Mean Time to Failure - MTTF)を使う。

平均復旧時間は故障したものを修理して回復するまでの時間

平均故障時間は故障なしで使⽤できる時間の平均値

二重障害の確率が高いとクオラムが担保できなくなる。

ある期間を超えると、MTTFにおけるそれぞれの障害の確率を下げることは難しい。

代わりに、Auroraでは二重障害に対して脆弱な期間(window)を縮小するためにMTTRを削減することに注力している。

これを実現するために、データベースボリュームを小さな固定サイズのセグメント（現在のサイズは10GB）に分割した。

これらのセグメントを6個複製してProtection Groupt(PG)に配置します。

PGは論理的なブロックで、3つのAZにまたがる6つの10GBのセグメントで構成され、各AZに2つのセグメントがある。

参照 : Introducing the Aurora Storage Engine | AWS Database Blog

一つのストレージボリュームはPGが連結されたセット。

実際の実装は、SSDがアタッチされたEC2を仮想ホストとしプロビジョニングされた大規模なストレージノード群です。

ストレージボリュームを構成するPGは、ストレージボリュームの増加に応じて割り当てられる。

当時は、複製なしを基準とするとで最大64TBまでのストレージボリュームをサポートしている。

(6つのコピーがあると実質おおよそ10TBってことかな)

今は128TBまで拡張されています。 aws.amazon.com

現在、10GBのセグメントはバックグラウンドノイズの障害と修復を行う単位。

Auroraを運用する上で、これらの障害を監視し自動的に修復している。

この10GBのセグメントは、10Gbpsのネットワークリンク上で10秒以内で修復することができる。

EC2でネットワーク帯域10Gbpsはでる。

aws.amazon.com

クオラムが失われるのは、10秒のウィンドウ内に2つの障害が発生することに加えて、2つの障害が起きているAZ以外のAZで1つ障害が発生する必要があります。

(3ノード故障のことをいっているのかなと思っています)

AWSの中で今まで見てきた障害率と今管理しているデータベースの数からすると、上記の状況が引き起こされる可能性は十分に低いと考えられる。

運用におけるフォールトに対する耐性のメリット

長時間の障害にも耐えられるシステムを構築すると、それは短時間の障害にも耐えるということになる。

AZの長時間の障害に耐えられるストレージシステムは、電力に関するイベント(定期停電とか？)やロールバックが必要なソフトウェア障害などの短時間のAZの障害にも対応できる。

クオラムのメンバーの可用性の数秒単位の障害に対応できるストレージシステムは、短時間のネットワークの詰まりやノードへの負荷にも対応できる。

で、AWSのシステムを見てみると、AWSのシステムは障害に対して高い耐性を持っているため、セグメントが使用できない原因となるメンテナンス操作に活かせる。

例として3つが挙げられている。

熱管理

温度が上昇しているディスクやノード上のセグメントの1つを不良としてマークでき、インスタンス群の他の冷えているノードに移行することでクオラムを迅速に修復できようになっている。

OSのセキュリティのパッチ適用、ソフトウェア・アップグレード

OSのセキュリティのパッチ適用は、そのストレージノードでパッチを適用している間は短時間ではあるが利用できない。

ストレージインスタンス群へのソフトウェアアップグレードも同様。

こららは、1度に1つのAZに対して実行し、1つのPGの1つメンバーにのみパッチが当たるようにしている。

これにより、Auroraのストレージサービスではアジャイルで迅速なデプロイが可能になっている。

参照

dev.classmethod.jp

https://pages.awscloud.com/rs/112-TZM-766/images/D2-05.pdf

docs.aws.amazon.com

(fleetってAWSではインスタンス群的な感じで使われてそうなので、そういう感じで論文を読んでいる)

aws.amazon.com

本当は恐ろしい分散システムの話 from Kumazaki Hiroki

www.slideshare.net

MySQL InnoDBにおけるPKにUUIDを使ったINSERTのパフォーマンスの調査

下記の記事を見て、PKにUUIDを使った際に内部的にどうなっているのかを確認してみました

kccoder.com

比較対象として、PKにULIDを使った場合も調べてみました。

github.com

ULIDはUUIDと互換性がある、ソート可能な識別子です。

MySQLのバージョン

% mysql --version
mysql  Ver 8.0.19 for osx10.14 on x86_64 (Homebrew)

スキーマ

mysql> SHOW CREATE TABLE innodb_auto_increment\G
*************************** 1. row ***************************
       Table: innodb_auto_increment
Create Table: CREATE TABLE `innodb_auto_increment` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE innodb_integer\G
*************************** 1. row ***************************
       Table: innodb_integer
Create Table: CREATE TABLE `innodb_integer` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE innodb_uuid\G
*************************** 1. row ***************************
       Table: innodb_uuid
Create Table: CREATE TABLE `innodb_uuid` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE innodb_ulid\G
*************************** 1. row ***************************
       Table: innodb_ulid
Create Table: CREATE TABLE `innodb_ulid` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

UUIDとULID

UUIDはRubyの SecureRandom.uuid (バージョン 4 の UUID)を使って生成しました。

module SecureRandom (Ruby 3.0.0 リファレンスマニュアル)

ULIDは下記のライブラリを利用して生成しました。

github.com

それぞれのテーブルに10万行INSERTしたときの様子

エクステント、ページの可視化

InnoDBの各テーブルスペースはデフォルトでは 16KBのページで構成されます。

MySQL インスタンス内のテーブルスペースはすべて同じページサイズを持っています。

これらのページは、サイズ 1MBのエクステント (連続した 64 個の 16KBのページ) にグループ化されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.10.2 ファイル領域管理

このエクステントの状態を可視化してみます。

https://github.com/jeremycole/innodb_ruby/wiki#space-extents-illustrate

https://github.com/jeremycole/innodb_ruby/blob/9661178f0919876df09c209b3fa9080e10084852/bin/innodb_space#L464

innodb_auto_increment	innodb_integer	innodb_uuid	innodb_ulid

UUID以外の場合は、ページの中にデータが埋まっている状態ですが、UUIDだと、不完全にデータが埋まっておりページ数、テーブルサイズの増大を引き起こしています。

個々のページが正方形できれいに整っている => ページが埋まっている

個々のページが凸凹になっている => ページが埋まりきっていない

innodb_ruby/index.rb at 9661178f0919876df09c209b3fa9080e10084852 · jeremycole/innodb_ruby · GitHub

インデックスレコードが順次 (昇順または降順) に挿入されると、インデックスページの約 15/16 までがいっぱいになります。レコードがランダムに挿入された場合は、ページの 1/2 から 15/16 までがいっぱいになります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13.4 InnoDB インデックスの物理構造 から引用

また、ランダムINSERTではページに入りきらなくなったページを分割する処理が頻発するので、CPU、IOの負荷が上がります。

LSNの可視化

MySQL InnoDBのpageの中にあるLSN(Log Sequence Number)は増加し続ける値で、InnoDBがredoログに50バイト書き込めば、LSNも50バイト進むようになっています。

https://dev.mysql.com/doc/refman/5.6/ja/glossary.html#LSN

InnoDBログシーケンス番号(LSN)を進める方法 | Yakst

MySQL :: MySQL Internals Manual :: 22.2.1.1 Fil Header

blog.jcole.us

このLSN(Log Sequence Number)についてinnodb_rubyを使って可視化しました。

https://github.com/jeremycole/innodb_ruby/wiki#space-lsn-age-illustrate

innodb_ruby/innodb_space at 9661178f0919876df09c209b3fa9080e10084852 · jeremycole/innodb_ruby · GitHub

innodb_auto_increment	innodb_integer	innodb_uuid	innodb_ulid

青色であるほど過去に更新されたことを、赤色であるほど最近更新されたことを示しています。

この比較とみると、UUID以外はLSNの更新はページが新しいほど直近に更新されているのですが、UUIDだけはほぼ全てのページが直近に更新されています。

これはランダムINSERTにより、各ページに均等に更新が入っているためです。

10万行を追加していく処理にかかった時間の計測

UUIDをPKに使った場合段々とINSERTする時間が少しずつ長くなっています。

しかし、下記ブログのベンチマークと比較すると、すでにINSERTされている行数が7500万行になってもUUIDにおいてINSERTする時間の増大が顕著ではありません。

kccoder.com

これはベンチマークに使ったマシンスペック(Mac)によるものと、後述するバイナリログを出していた影響の両方と思っています。

ストレージはSSDです。

HDDと比較するとSSDのランダムアクセスのパフォーマンスがよいから劣化しにくいというのもあるかもしれません。

マシンスペック

% system_profiler SPHardwareDataType
      Model Name: MacBook Pro
      Model Identifier: MacBookPro13,3
      Processor Name: Quad-Core Intel Core i7
      Processor Speed: 2.6 GHz
      Number of Processors: 1
      Total Number of Cores: 4
      L2 Cache (per Core): 256 KB
      L3 Cache: 6 MB
      Hyper-Threading Technology: Enabled
      Memory: 16 GB

ibdファイルのサイズを見てみると、UUIDをULIDと比べても容量を多く使うことになりそうです。 https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_ibd_file

ls -alh /usr/local/var/mysql/bench_work
total 15G
drwxr-x---  10 hoge hoge  320  2 26 04:25 .
drwxr-xr-x 137 hoge hoge 4.3K  3  3 00:04 ..
-rw-r-----   1 hoge hoge 1.8G  3  3 00:09 innodb_auto_increment.ibd
-rw-r-----   1 hoge hoge 1.8G  3  3 00:09 innodb_integer.ibd
-rw-r-----   1 hoge hoge 4.3G  3  3 00:09 innodb_ulid.ibd
-rw-r-----   1 hoge hoge 6.9G  3  3 00:09 innodb_uuid.ibd

SELECT COUNT(*) にかかった秒数も残しておきます。

innodb_auto_increment	innodb_integer	innodb_uuid	innodb_ulid
1.66 sec	1.51 sec	39.08 sec	3.63 sec

こちらはULIDと比べると格段にパフォーマンスが落ちました。

ちなみに、MySQL 8以降だと、fmtファイルはなくなりデータディクショナリテーブルに格納されるようになりました。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 14 ♪MySQL データディクショナリ

gihyo.jp

INSERTに使ったスクリプト

require 'mysql2'
require 'securerandom'
require 'ulid'
require 'benchmark'

client = Mysql2::Client.new(host: "127.0.0.1", username: "root", database: "bench_work")

count = 100000
res_auto = []
res_int = []
res_uuid = []
res_ulid = []
counter = 0 # ここは更新しながらこのスクリプトを実行
points = 200                                                                                                                                                                                 

points.times do |p|
  count.times do |t|
    res_auto << Benchmark.realtime { client.query("insert into innodb_auto_increment values ();") }
    counter += 1
    res_int << Benchmark.realtime { client.query("insert into innodb_integer values (#{counter});") }
    uuid = SecureRandom.uuid
    res_uuid << Benchmark.realtime { client.query("insert into innodb_uuid values ('#{uuid}');") }
    ulid = ULID.generate
    res_ulid << Benchmark.realtime { client.query("insert into innodb_ulid values ('#{ulid}');") }
  end

  puts "#{count}\t#{res_auto.sum}\t#{res_int.sum}\t#{res_uuid.sum}\t#{res_ulid.sum}"
end

あとで気がついたこと

ベンチマークとっている最中に気がついたのですが、MySQL 8からバイナリログ出すのがデフォルトになっています。。。。

dev.mysql.com

From MySQL 8.0, binary logging is enabled by default,

mysql> select @@global.log_bin;
+------------------+
| @@global.log_bin |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)

offにするには、my.cnfに skip-log-bin を入れて再起動します。

MySQL :: Re: Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

mysql> select @@global.log_bin;
+------------------+
| @@global.log_bin |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

バイナリログ onでベンチマークとっていたので、INSERT自体のパフォーマンスは全体的にそこまで良くなかったです。

参考値にバイナリログ offにして、7500万行入っているテーブルに10万件のINSERTしたときの結果を貼っておきます。

バイナリログ offにすることで、UUIDにおけるINSERTのパフォーマンスはそれ以外と差が広がりました。

2021/03/05 追記

innodb_uuid テーブルのSELECT COUNT(*) をもう少し速くできないかなと思ってセカンダリインデックスを追加してみました。

nippondanji.blogspot.com

mysql> ALTER TABLE innodb_uuid ADD COLUMN for_index INT DEFAULT 1, ADD INDEX (for_index);
Query OK, 0 rows affected (8 min 21.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

その結果

mysql> explain select count(*) from innodb_uuid;
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key       | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | innodb_uuid | NULL       | index | NULL          | for_index | 5       | NULL | 75209750 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

となり、

mysql> select count(*) from innodb_uuid;
+----------+
| count(*) |
+----------+
| 75500000 |
+----------+
1 row in set (11.03 sec)

大体実行時間1/3くらいになりました。

performance_schema.file_summary_by_instanceなどをみてみましたが、バッファープールにデータが乗り切らずファイルI/Oがでていました。

gihyo.jp

2021/03/05 追記その2

mysqlserverteam.com

MySQL8はUUIDのサポートが入っているのでこれはこれで試してみました。

mysql> SHOW CREATE TABLE innodb_uuid_bin\G
*************************** 1. row ***************************
       Table: innodb_uuid_bin
Create Table: CREATE TABLE `innodb_uuid_bin` (
  `id` binary(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

UUID_TO_BIN() の第2引数はswap_flagで、UUID の1番目と3番目のグループを入れ替えてくれます。

UUID v1だと3番目にタイムスタンプの上位部分が入っているので入れ替えるとソートされた状態にできるためです。

tools.ietf.org

今回使うUUIDはv4なので、swapしても意味ないので0にしています。

dev.mysql.com

エクステント

innodb_auto_increment	innodb_integer	innodb_uuid	innodb_uuid_bin	innodb_ulid

LSN

innodb_auto_increment	innodb_integer	innodb_uuid	innodb_uuid_bin	innodb_ulid

サイズは小さくなっていますね。

ランダムINSERTなのは変わらずです。

2021/03/05 追記その3

ULIDを使う理由についてまとめました。

なぜPKにULIDを使うか。
一番大きい理由は大量のデータがあるテーブルにおいて古い順に削除したいとき、
作成日でインデックスが効かない(uuid, created_atのindexとか)
ALTERも今更打てない
そんなときにPKがソート可能だと助かることがあること。
大体3年位たって発覚する。

— はるやま | Makoto Haruyama (@Spring_MT) 2021年3月4日

パフォーマンスも含めて、UUID or ULIDならULIDを選ぶほうがよいと思います。

2021/03/08 追記その4

UUID_TO_BIN()を使ってベンチマークとりました。

mysql> SHOW CREATE TABLE innodb_uuid_bin\G
*************************** 1. row ***************************
       Table: innodb_uuid_bin
Create Table: CREATE TABLE `innodb_uuid_bin` (
  `id` binary(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

クエリは INSERT INTO innodb_uuid_bin VALUES (UUID_TO_BIN('#{uuid}')); としています。

UUID(BIN)以外は同じ条件ではベンチマークを実行していないので参考程度に考えてもらえればと思います。

UUIDをchar(36)で保存するときに比べてパフォーマンスの劣化が起こってないように見えます。

UULDを使う場合よりはパフォーマンスは落ちそうです。

ibdファイルのサイズは7500万行INSERTして4.4GBでした。

DBのサイズはULIDと同じくらいのサイズになりました。

参照

dev.classmethod.jp

qiita.com

MySQL :: MySQL Internals Manual :: 22.2.1 High-Altitude View

github.com

https://blog.jcole.us/category/mysql/innodb/

UUIDs are Popular, but Bad for Performance — Let’s Discuss - Percona Database Performance Blog

MySQL InnoDBの領域最適化 - Qiita

MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ

PK/Unique KEY が UUID v4 だと INDEX 作成効率が悪い - id:onk のはてなブログ

mysqlでUUIDをprimary keyにしたいんだけど知見が無いのでどんなもんなのか雑に知見が欲しい。良い/悪い, どうしたらうまくいく、どういうのは苦手、とか。

— シンノスケ (@cnosuke) 2019年8月28日

とあるイルカのデータベース - Speaker Deck

Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databasesを読む(その1 Introduction)

論文はこちらです。

https://www.allthingsdistributed.com/files/p1041-verbitski.pdf

Introduction

最近の分散型クラウドサービスでは、下記2つを実現することで回復力とスケーラビリティを実現するケースが増えている。

• コンピュートをストレージから分離すること
• ストレージを複数のノードにまたがって複製すること

(SpannerとかSQL Server Hekatonとか)

okachimachiorz.hatenablog.com

これにより下記のような運用をハンドリングできるようになる。

• 動作不良のホストや到達不可能なホストの置き換え(resilience)
• レプリカの追加(scalability)
• writerからレプリカへのフェイルオーバー(resilience)
• データベースインスタンスのサイズの拡大縮小などの操作(scalability)

resilience -> 回復力 フォールトに対する耐性

scalability -> 負荷の増大に対してシステムが対応できる能力

I/O のボトルネックは従来のデータベースシステムとは変わる。

I/Oはマルチテナントなインスタンス群の複数のノード、複数のディクスに分散できるので、個々のノード、diskの負荷は上がらない。

その代わりに、I/Oを要求するデータベース層とこれらの I/O を実行するストレージ層との間のネットワークがボトルネックになる。

データベースがストレージのインスタンス群に並行で書き込む場合において、ネットワークのボトルネックはpackets per second (PPS) や帯域幅を以上にボトルネックになることがある。

ネットワークパスが応答時間は、外れ値のストレージの性能に引っ張られる(ノード、ディスク、またはネットワークパス)。

並行処理

データベースにおいて、ほとんどの場合はお互いに重なりあって操作が可能だが、いくつかの操作は同期的に処理しなければならない。

同期的な処理の操作においては、ストール(失速)やコンテキストスイッチが発生する。(例としては、データベースのバッファキャッシュのミスによるディスク読み込み。)

読み込み中のスレッドは、読み込みが完了するまで続行できない。

キャッシュの欠落は、新しいページに対応するためにダーティなキャッシュページを強制排除(eviction)してフラッシュするという余分なペナルティが発生する可能性がある。

チェックポイントやダーティページ書き込みなどをバックグラウンド処理にまわすことでこのペナルティの発生を減らすことができるが、ストールやコンテキストスイッチ、リソースの競合を引き起こす可能性もある。

トランザクション

トランザクションコミットも問題の1つ。

1つのコミットしていないトランザションの詰まりが他の処理中のトランザクションを止めることがある。

2相コミット（2PC）のようなプロトコルでコミットを処理することは、クラウドスケールの分散システムでは困難。

(データ指向アプリケーションデザイン 9.4.1参照)

Auroraでの取り組み

大規模なシステムは複数のデータセンターに分散しているため、これらのプロトコルは高レイテンシーになりがち。

これらの問題を解決するために、Aruroaでは、高度に分散したクラウド環境において、redo ログをより積極的に活用した。

https://www.allthingsdistributed.com/files/p1041-verbitski.pdf より参照

仮想化され、分割されたredoログを抽出して、データベースインスタンス群から疎結合にしたマルチテナントのスケールアウトストレージサービスを使った、新しいサービス指向アーキテクチャ(独立した一つのまとまり的な意味合いくらいに捉えています)になっている。

• データベース層に残っているもの(従来の核となるコンポーネント)

  • クエリ処理、トランザクション、ロック、バッファキャッシュ、アクセス、Undo管理(snapshot管理)

• ストレージ層に切り離したもの

  • redoログ、耐久性のあるストレージ、クラッシュリカバリ、backup/restore

このAuroraのアーキテクチャの利点は従来のアプローチに比べて3つの利点がある。

1. 独立した耐障害性と自己回復機能を備えた複数のデータセンターにまたがってストレージを構築することで、ネットワーク層またはストレージ層のいずれかでパフォーマンスの変動や一時的または恒久的な障害からデータベースを保護する。

   • 耐久性の障害は長期的な可用性イベントとしてモデル化できており、さらに、この可用性イベントは長期的な性能変動としてモデル化できることがわかっています。
   • The Tail at Scale – Google Research をもとにして、モデルに沿って検証すればOKっていうことを言いたいのかと思います

2. redo ログレコードをストレージに書き込むだけで、ネットワークのIOPS(Input/Output Per Second / I/O毎秒)を桁が変わるくらいに削減することができる。

   • このボトルネックを取り除くと、他の多くの競合点を積極的に最適化することができ、ベースとなる MySQL コードベースよりも大幅にスループットを向上させることができる。

3. 最も複雑で重要な機能のいくつか（バックアップとREDOリカバリ）を、データベースエンジン内の一回限りのコストが掛かる操作から、大規模な分散インスタンス群で補償された連続的な非同期処理に移行する。

   • これにより、チェックポイントなしでほぼ瞬時にクラッシュからのリカバリが可能になり、フォアグラウンド処理を妨げないコストが低いなバックアップが可能になった。

本論文では3つの寄与について述べる。

1. クラウド規模での耐久性をどのように推論するか、また、相関障害に強いクォーラムシステムをどのように設計するかの方法。(セクション2)。

2. スマートストレージに従来のデータベースの下位4分の1をこの階層にオフロードし活用する方法(セクション3)。

3. 分散ストレージにおける多相同期、クラッシュリカバリ、チェックポイントをなくす方法(セクション4)。

次に、これら3つのアイデアを組み合わせてAuroraの全体的なアーキテクチャを設計する方法をセクション5で紹介し、セクション6ではパフォーマンスの結果をレビューし、セクション7節では学んだことを紹介する。

最後に、セクション8で関連する作業を簡単に調査し、セクション9で結論を述べる。

REFERENCES

https://cs.nyu.edu/~mwalfish/papers/yesquel-sosp15.pdf

https://15721.courses.cs.cmu.edu/spring2018/papers/levandoski-cidr2015.pdf

The Tail at Scale – Google Research

なぜUsersテーブルの行でロックを取るのか？

下記ツイートを見て改めて考えてみました。

更新系APIでDBトランザクション張る時、対象のリソース群の親となるリソース（大抵はUser）を無条件で最初にロックする手法が当たり前だと思っていたけど、もしかして世間ではそうではないのかな...デッドロックリスクを忘れられる大きなメリットがあると考えていたんだけど。

— やまでぃ🤗 | YOUTRUST (@aiueo4u) 2021年2月16日

並行に書き込みをどのように処理するかという点も含めて整理してみました。

並行性の問題一覧

名称	内容
ダーティリード	あるクライアントが他のクライアントのまだコミットされていない書き込みを見ることができる。 read commited分離レベル以上では生じない。
ダーティライト	あるクライアントが他のクライアントのまだコミットされていない書き込みを上書きできる。
ノンリピータブルリード	トランザクション内でクライアントが異なる時刻で異なるデータを取得してしまう(他のトランザクションでデータが変更されるとその内容が反映されてしまう)。 スナップショット分離で回避
ファントムリード	あるトランザクションでの書き込みが、他のトランザクションの検索クエリの結果を変化させる(行のINSERT、DELETE)。本来ファントムリードはREPEATABLE-READでは防がないが、MySQL InnoDBのREPEATABLE-READの分離レベルでは防ぐことが可能。

スナップショット分離(リピータブルリード)

それぞれのトランザクションがデータベースの一貫性のあるスナップショットから 読み取り(Read) を行うものです。

スナップショットに対して読み取りをすることで矛盾なく読み取りが出来ることを一貫性読み取り (Consistent Read) と言います。

MVCC

スナップショット分離(リピータブルリード)では、トランザクションに一貫したデータを参照させるために過去のバージョンのデータを見せる必要があります。

データベースはあるデータ(オブジェクト)について複数のバージョンを管理すること、これがMVCC(multi-version concurrency control)と呼ばれる手法です。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.12 InnoDB マルチバージョン

MySQL InnoDBにおけるスナップショット

スナップショット(過去のバージョンのデータ)は、UNDOログにコピー(退避)されています。

MySQL :: MySQL 5.7 Reference Manual :: 14.6.7 Undo Logs

それぞれの行にはローバックポインタがあり、 退避したUNDOレコードを指すようになっています。

さらに、UNDOレコードもさらに古いUNDOレコードのポインタを持っており、このローバックポインタをたどれば過去のバージョンのデータがわかるようになっています。

MySQLアーキテクチャ図解講座 from Mikiya Okuno

UNDOレコードの確認

関連しているトランザクションが全て終了しているUNDOレコードは削除できます。

削除(パージ)されていないUNDOレコードは、 SHOW ENGINE INNODB STATUS; の History list length で確認できます。

------------
TRANSACTIONS
------------
Trx id counter 334129
Purge done for trx's n:o < 334128 undo n:o < 0 state: running but idle
History list length 18
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421690295200296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421690295199440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

日々の覚書: InnoDBのHistory list lengthの監視と原因スレッドの特定と

SHOW ENGINE INNODB STATUS の History list length - ablog

MySQLのmetricに関する話 | エンジニアブログ | GREE Engineering

What's New in MySQL 5.7 InnoDB from Mikiya Okuno

MySQL InnoDBにおけるスナップショット分離(REPEATABLE-READの分離レベル)

スナップショット分離の分離レベルでは、 ダーティーリード、ノンリピータブルリードは防ぐ必要がありますが、ファントムリードを防ぐかはその実装依存となります。

SQLの仕様ではREPEATABLE-READでは特にファントムは防がなくてもよいとなっています。

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ANSI SQL 92

qiita.com

A Critique of ANSI SQL Isolation Levels読んだ - tom__bo’s Blog

トランザクション分離レベル - Wikipedia

MySQL InnoDBでは binlog_format=STATEMENT でのレプリケーションの整合性を保証するため、独自のロックのかけ方(ギャップロックとネクストキーロック)でそれを実現しています。

これは、STATEMENTの場合、sourceは並行で処理しているがreplicaはバイナリログを直列で処理しているため、sourceでファントムが起こる状態(並行で処理している)であってもreplicaではファントムが起こらないためデータの整合性が合わないことがあるためです。

MySQL InnoDBではREAD-COMMITEDの分離レベルで binlog_format=STATEMENT にするとエラーになります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.2.4.2 バイナリログ形式の設定

MySQLのレプリケーション設定で起きたトラブルの原因とその解決策 - Yahoo! JAPAN Tech Blog

この独自のロックのかけ方が、ファントムを防止しています。

ファントムとかギャップロックとネクストキーロック

すでにまとまっている資料がたくさんあるので割愛

トランザクション分離レベルについてのまとめ - Qiita

blog.kamipo.net

スナップショットの取得

BEGIN句によるトランザクション開始

BEGIN句からトランザクションを開始すると、そのタイミングではスナップショットの作成されません。

スナップショット取得が行われるのは最初の SELECTのタイミングになります。

mysql> SHOW CREATE TABLE t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int unsigned NOT NULL,
  `b` int NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.transaction_isolation \G
*************************** 1. row ***************************
@@SESSION.transaction_isolation: REPEATABLE-READ
1 row in set (0.00 sec)

スナップショットの取るタイミングの確認その1

No.	session1	session2
1	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | +---+---+ 1 row in set (0.00 sec)
2	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)
3		mysql> INSERT INTO t VALUES(2, 1); Query OK, 1 row affected (0.01 sec)
4		mysql> COMMIT; Query OK, 0 rows affected (0.01 sec)
5	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) (このタイミングでスナップショットの生成が行われるので、ファントムぽく見えるけどこれは正しい挙動)

スナップショットの取るタイミングの確認その2

No.	session1	session2
1	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.01 sec)
2	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)
3	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.01 sec) ここでスナップショット取得	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec)
4		mysql> INSERT INTO t VALUES(3, 1); Query OK, 1 row affected (0.01 sec)
5	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) ファントムが防がれている	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec)
		mysql> COMMIT; Query OK, 0 rows affected (0.01 sec)
	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) ファントムが防がれている	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec)

START TRANSACTION WITH CONSISTENT SNAPSHOTでトランザクションが開始とスナップショットの作成を同時に行う

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.1 START TRANSACTION、COMMIT、および ROLLBACK 構文

No.	session1	session2
1	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec)
2	mysql> START TRANSACTION WITH CONSISTENT SNAPSHOT; Query OK, 0 rows affected (0.01 sec)	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)
3		mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.01 sec)
4		mysql> INSERT INTO t VALUES(4, 1); Query OK, 1 row affected (0.00 sec) 多分ここでスナップショットができていると予想
5		mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | +---+---+ 4 rows in set (0.00 sec)
		mysql> COMMIT; Query OK, 0 rows affected (0.01 sec)
	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec) ファントムが防がれている

SELECT FOR UPDATE

SELECT FOR UPDATEは最新のスナップショットの取得をし、それを使うために行および関連付けられたすべてのエントリに排他ロックをかけます。

この動作は、これらの行に UPDATE ステートメントを発行した場合と同じ挙動になります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.5 ロック読み取り (SELECT ... FOR UPDATE および SELECT ... LOCK IN SHARE MODE)

MySQL InnoDBにおけるスナップショット分離(REPEATABLE-READの分離レベル)でも起き得ること

更新ロスト(Lost Update)

アプリケーションが何らかの値を読み取り、その値を変更して書き戻す( read-modify-write )の場合に生じることがあります。

ひとことでいうと最後の書き込みを勝たせる(last write wins LWW)ことです。

PostgreSQLのリピータブルリードだと更新ロストが発生したことを自動的に検出して問題のトランザクションを中断してくれるとのことですが、MySQLのInnoDBでは更新のロストの検出はしてくれません。

更新ロストを防ぐ方法として、更新対象のデータを明示的にロックをする方法が挙げられます。

なにかしら更新処理を行う最初にSELECT FOR UPDATEで対象となる行をロックし、更新中は他のトランザクションが並行に読みだそうとしても先行するトランザクションが完了するまで待たされるようにします。

この場合、アプリケーションロジックの中で必要なロックの取得を忘れてしまうと簡単にレース条件が発生するの注意が必要です。

また、トランザクション内で単純なSELECTとSELECT FOR UPDATEを使ったデータ取得に差異がでることがあるので、ここも要注意です。

(単純なSELECTだと過去のスナップショットを参照するが、SELECT FOR UPDATEは必ず最新のスナップショットを見る)

漢(オトコ)のコンピュータ道: InnoDBのREPEATABLE READにおけるLocking Readについての注意点

MySQL :: MySQL 5.6 Reference Manual :: MySQL Glossary

書き込みスキュー

2つのトランザクションが同じデータ群から読み取りを行い、それらのいくつかを更新する(特にトランザクションごとに更新するデータが異なっている)場合に生じるものです。

複数のデータが関わっているので、単一のデータに対するアトミックな操作(ロック)では解決できません。

これも、複数のデータに対して明示的にロックを行うことで解消できます。

いろんなAnomaly - Qiita

ただし、特定の条件を満たす行が存在しないことをチェックし書き込みによってその行が追加される場合、ロックする対象となる行が存在しないためロックがかけられないという問題があります。

これを回避するためには、衝突の実体化(materializing conflicts)を行う必要があります。

https://dsf.berkeley.edu/cs286/papers/ssi-tods2005.pdf

これらの回避するために

これらは分離レベルをSERIALIZABLEにすることで解消することがほとんどですが、SERIALIZABLEによって並行に処理できなくなることが多く(事実上1個の処理だけが動くような排他制御が行われる)パフォーマンスに大きな影響を出すことがほとんどです。

ほとんどの場合において書き込みスキューを考慮しなくてよい場合においては、一部分だけ直列にできるようなしくみがあればよさそうです。

そこで、アプリケーション全体でロックを行う対象を決め、それらを順序を守ってロックをかけることで直列に処理を行うようにします。

ロックを行う対象は、必ず存在するデータに対して行います。(そうでないとギャップロックがかかるので)

そのため、自分の大体のケースでUsersテーブルを使って対象となるユーザーの行に対してロックをかけるということになります。 (UsersテーブルにはユーザーIDと作成日くらいしかないことを想定しています)

ロックを掛ける際は、デッドロックを防ぐためUsersテーブルのプライマリキーで ソートしてから ロックをかけます。

例えば、PvPを考えると

• ユーザーAの攻撃でユーザーBのHPを減らし、ユーザーAに経験値を追加する
• ユーザーBの攻撃でユーザーAのHPを減らす、ユーザーBに経験値を追加する

のようにの相互の更新が同時に起こりうるので、ユーザーAとユーザーBのUsersテーブルにロックを掛ける場合ロックを取るときはロックを取る順番がどちらも同じになるようにします。

SELECT id FROM users WHERE a IN ("A", "B") ORDER BY a FOR UPDATE;

実際にどうなるかを簡単なテーブルで試した例も記載しておきます。

デッドロックになる例

No.	session1	session2
1	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec)
2	mysql> START TRANSACTION WITH CONSISTENT SNAPSHOT; Query OK, 0 rows affected (0.01 sec)	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)
3		mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.01 sec)
4		mysql> INSERT INTO t VALUES(4, 1); Query OK, 1 row affected (0.00 sec) 多分ここでスナップショットができていると予想
5		mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | +---+---+ 4 rows in set (0.00 sec)
		mysql> COMMIT; Query OK, 0 rows affected (0.01 sec)
	mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec) ファントムが防がれている

Lock Waitになる例

No.	session1	session2
	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)
	mysql> SELECT * FROM t WHERE a IN (2, 1) ORDER BY a FOR UPDATE; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.01 sec)	mysql> SELECT * FROM t WHERE a IN (2, 1) ORDER BY a FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

しかしこの方法では並行性の制御の仕組み(ロックを掛ける順番やロックを行うデータの選択)がアプリケーションのデータモデルに漏れ出していてアプリケーション側で意識することが増えてしまい、またルールで縛るため間違いも起こりやすくなります。

なので、本来であれば、分離レベルをSERIALIZABLEにするのがよいのかなと思います。。。

ただMySQLのSERIALIZABLEは容易にロックがかかります。

• SERIALIZABLEでロックが掛かる例

No.	session1	session2
	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)	mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)
	mysql> SELECT * FROM t WHERE a IN (2, 1) ORDER BY a FOR UPDATE; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.01 sec)	mysql> SELECT * FROM t WHERE a IN (2, 1) ORDER BY a FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

PostgreSQLのSERIALIZABLEの分離レベルはSSIがだとどうなるかは気になるところです。

SSI - PostgreSQL wiki

参考

InnoDBの分離レベルによるMySQLのパフォーマンスへの影響 | Yakst

漢(オトコ)のコンピュータ道: InnoDBのREPEATABLE READにおけるLocking Readについての注意点

Rails Developers Meetup 2018 で「MySQL/InnoDB の裏側」を発表しました - あらびき日記

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.7 ネクストキーロックによるファントム問題の回避

トランザクション技術とリカバリとInnoDBパラメータを調べた - たにしきんぐダム

トランザクション分離レベルについてのまとめ - Qiita

MySQL InnoDB で大きなトランザクションを見つける - ablog

InnoDBのロールバックがあとどれくらいかかるかをなんとなく見積もる | GMOメディア エンジニアブログ

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.5 ロック読み取り (SELECT ... FOR UPDATE および SELECT ... LOCK IN SHARE MODE)

https://15445.courses.cs.cmu.edu/fall2017/slides/18-indexconcurrency.pdf

MySQL の Repeatable Read と RocksDB の楽観的トランザクション解説 | 株式会社インフィニットループ技術ブログ

一人トランザクション技術 Advent Calendar 2016 - Qiita

the-weekly-paper.github.io

mysqlhighavailability.com

Java SDKでApache Beam(Dataflow)でGradleを使うサンプル

Java SDKを使ったApache Beamのパイプライン構築については公式ドキュメントなどを見るとMavenを使うことが多いのですが、Gradleを使った例が見当たらなかったので作ってみました。

github.com

個人的な感想なのですが、Mavenだと依存関係をpomで定義書くのが辛かったのですが、Gradleのほうが素直に書けてそこだけでもGradleを使うメリットはあるかなあと思っています。

Gradle経由でDataflowにdeployする定義も作っています。

apache-beam-gradle-sample/build.gradle at main · SpringMT/apache-beam-gradle-sample · GitHub

サンプルプロジェクト構成について

pipeline

apache-beam-gradle-sample/src/main/java/sample/pipeline at main · SpringMT/apache-beam-gradle-sample · GitHub

パイプラインの構築だけに責務があります。

pipelineの中には具体的な処理内容は書かず、エントリーポイントと処理を担当するクラス(ステージ)を定義するだけにしています。

transform

apache-beam-gradle-sample/src/main/java/sample/transform at main · SpringMT/apache-beam-gradle-sample · GitHub

PTransform を使い、具体的な処理内容を書きます。

ステージに相当する部分となります。

単一責任の原則に則り1クラス1処理とし、できる限り単体テストを書くようにします。

ステージを跨ぐ場合は、 PCollection を使ってデータを受け渡しを行います。

application

apache-beam-gradle-sample/src/main/java/sample/application at main · SpringMT/apache-beam-gradle-sample · GitHub

アプリケーションロジックなどをここにまとめます。

ステージを跨ぐデータのクラスを置いたりしています。

参考

qiita.com

Apache BeamでPTransformを使って分割したステップについてテストをする(Java)

簡単なまとめ

• Apache Beamでは PTransform PCollectionを使ってパイプラインを小さいステップに分割できる
• Apache Beamは分割してステップを簡単にテストできるテスティングフレームワークが整備されている
• パイプラインを細かく分割して、それらのテストを書くことでパイプラインの開発がしやすくなる

Apache Beamでは Pipeline クラスのオブジェクトがデータ処理のタスク全体を管理しています。

この Pipeline の中におけるデータ処理は PTransform を使って処理を複数のステップに分割することが可能です。

PTransform によって分割されたステップ間における入出力のデータは PCollection という分散データセットとして受け渡すことが可能です。

beam.apache.org

Pipeline は一つの PTransform で記述することも可能ですが、単一責任の原則に則って一つのステップは一つの役割に分割することでコードの管理がしやすくなります。

ステップの分割を PTransform と PCollection で簡単に表現できるのがApache Beamが便利なところかなと思います。(他の分散処理基盤はあんまり触ったことないですが。。。)

やっていることが小さくなると、テスト設計もしやすくなるため、テストを書きたくなるかと思います。

Apache Beamはパイプラインを簡単にテストできるテスティングフレームワークを提供しています。

ここでは、 PTransform を使った単体テストの書き方の例を示せればと思います。

以降ではJavaでの例のみとなります。

パイプラインの分割

まずはパイプラインの最初の入力と最後の出力をステップとして分けます。

最初の入力はマネージドサービスからの入力( Cloud Pub/Subなど )が多く、ここを分離しておくことで後続のステップを単体でテストしやすくなります。

最初のステップでは特にデータの内容に手を加えず、後続のステップにわたすための PCollection への簡単な変換にとどめます。

最後の出力もマネージドサービスへの出力( BigQueryなど )が多いため、ここも分離しておきます。

最後の出力も、前段でデータを加工しておき、そのデータを出力するだけのシンプルなステップにします。

なにかしらの入力を PCollection に変換するステップ
↓
処理したい内容のステップ <- ここのテストを充実させる
↓
出力ステップ

このようにステップを分け、処理の内容の実体を単体でテストできるようにします。

PTransform を使ったステップのテストの書き方

Apcahe Beamには TestPipeline というクラスが用意されています。

TestPipeline (Apache Beam 2.27.0-SNAPSHOT)

このクラスを使って、PTransform のステップのテストを書きます。

ステップ

まずはサンプルとして Foo というステップを用意します

@AllArgsConstructor
public class Foo extends PTransform<PCollection<String>, PCollection<FooDto>> {

  @Override
  public PCollection<FooDto> expand(PCollection<String> input) {                                                                                                              
    return input.apply(ParDo.of(new ParseJson()));
  }

  public static class ParseJson extends DoFn<String, FooDto> {
    @ProcessElement
    public void processElement(@Element String element, OutputReceiver<FooDto> receiver)
        throws IOException {
      ObjectMapper objectMapper = new ObjectMapper();
      FooDto[] parsedData = objectMapper.readValue(element, FooDto[].class);
      for (FooDto d : parsedData) {
        receiver.output(d);
      }
    }
  }
}

PTransform で受け渡すデータはSerializableである必要があります。

したがってontputのデータ FooDto は Serializableなクラスとして定義します。

public class FooDto implements Serializable {                                                                                                                                 
  @JsonProperty("user_id")
  @NonNull
  private String userId;

  @JsonProperty("account_id")
  @NonNull
  private Integer accountId;
}

テストコード

上記の Foo のテストは下記のようになります。

class FooTest {
  @Test
  public void testFoo() throws Exception {
    TestPipeline p = TestPipeline.create().enableAbandonedNodeEnforcement(false);                                                                                                        

    PCollection<String> input =
        p.apply(
            Create.of(
                    "["
                        + "{\"user_id\": \"aaa\", \"account_id\": 123}"
                        + "]")
                .withCoder(StringUtf8Coder.of()));
    PCollection<FooDto> output = input.apply(new Foo());

    PAssert.that(output)
        .containsInAnyOrder(
            new FooDto("aaa", 123);

    p.run().waitUntilFinish();
  }
}

TestPipeline はテスト用のパイプラインを生成します。

ここで生成したパイプラインにテスト対象のステップをapplyすることで、このステップについてのみテストが可能になります。

PAssert は PCollection の内容に関するアサーションを提供しています。

PAssert (Apache Beam 2.27.0-SNAPSHOT)

これらを使うことで、処理結果のoutputを簡単に確認することができます。

テストを作ることで、手元で動作確認をしながら開発を進めることが可能です。

まとめ

Apache Beamのパイプラインはステップを適切に分割しそれぞれのテストを書くことで格段に開発、メンテがしやすくなります。

ぜひテストも書きながらApache Beamのパイプラインを構築してみてください。