今日は、パフォーマンス問題を引き起こす原因になる、サイズの大きいテーブルのパフォーマンスを改善することについて書いてみようと思う。このアドバイスのうちのいくつかは、たくさんのテーブルをまとめて大きくなっているデータベースにも適用できるが、大抵の場合、独立した大きなテーブルというのは特に問題になりやすいものだ。
一般的に知られていると思われるのは、テーブルを変更する時のスピードは、そのサイズが大きくなるにつれて遅くなることだ。以下の図は、一般的なB+ツリーインデックスのパフォーマンスを時系列で見たものだ。
このグラフは、MySQL@Facebookの記事から拝借したものだ。これは、insert bufferを無効にした状態(もちろん推奨はしておらずデモ目的)でテーブルに10億行をinsertした時のパフォーマンスの低下を表したものだ。
ベンチマークはiibenchというもので、TokuTekがデザインしたものだ。
ここで我々はこのパフォーマンスの低下がインデックスの構造によるものだと考えるべきだが、そうは言ってもまだこの曲線を緩くし、急激に低下しないようにやれることはある。
大きなテーブルのパフォーマンスインパクトを小さくする10の方法
MyISAMではなくInnoDBを使う。MyISAMでは、テーブルの最後へのinsertは高速だが、テーブルロック(updateとdeleteのみ)が必要で、競合が起きるためデータをディスクから読み出したり書き込んだりする時にキーバッファを保護するために1つのロックしか使わない。また、後述の通りチェンジバッファ機能を持っていない。
InnoDBは、ユニークでないセカンダリインデックスを遅延して作り、書き込みをマージするチェンジバッファ機能がある(以前はインサートバッファと呼ばれていた機能)。これについて詳しくはFacebookのノートに書かれている。これは上のグラフには書かれていないが、insertのパフォーマンスをかなり高速化するもので、デフォルトで有効になっている。この機能はMySQL 5.5で素晴らしく改善されたので、もしバージョンアップしていないならすぐした方がいい。
パーティショニングはインデックスのサイズを小さくし、テーブル自体も効率的に小さく分けることができる。さらに、MySQL 5.7.2 DMRで大々的に改善された、内部的なインデックスのロック競合(リンク1、リンク2)も減らしてくれる。
InnoDBの圧縮機能を使う。負荷の種類によっては(一般的には、たくさんのchar型・varchar型・text型カラムがある場合)、圧縮機能はデータをコンパクトにしてくれ、パフォーマンス低下のカーブを緩やかにしてくれる。また、これによってHDDよりも容量が小さいSSDを使えるようにもできるだろう。InnoDBの圧縮機能は、Facebookが提供した一連のパッチのおかげで、MySQL 5.6で大きく改善されている。
ソートしたデータをバルクロードする。ソートされたデータをロードすることで、ページスプリット(メモリ上にないテーブルでパフォーマンスが悪化する原因になる)が起きにくくなる。バルクロードはテーブルの容量には関係ないが、redoログへの負荷を軽減させる意味がある。
不要なインデックスを消す。チェンジバッファ機能を無効にしてしまうUNIQUEキーに特に注意しよう。制約を使う必要がない場合はUNIQUEキーを使わず、通常のINDEXを使うこと。
5.と6.に関連して、プライマリキーの種類も意味がある。パフォーマンス低下がむしろ急激に起きてしまう
GUIDのようなデータタイプより、INTあるいはBIGINTを使う。PRIMERY KEYを持たないのもパフォーマンスにはマイナスに影響する。新しいテーブルにバルクロードする場合は、PRIMARY KEY以外のインデックスは後で作る。全てのデータがロードされてからインデックスを作ることで、InnoDBはプレソートやバルクロードのプロセスと言った、高速でインデックスがよりコンパクトになりやすい方法を適用できることになる。この最適化はMySQL 5.5で有効になった。
よりたくさんのメモリを用意すればもちろん有効だ。今日のメモリの実際の価格を考えると少なすぎるメモリしか積んでいない新しいDBサーバを見ることがよくある。単純なアドバイスはこうだ。
SHOW ENGINE INNODB STATUSの結果で、BUFFER POOL AND MEMORYのreads/sで読み込みがあることを示していて、Free buffers(これもBUFFER POOL AND MEMORYの下にある)がゼロなら、メモリを増やせば恩恵が得られるということだ(innodb_buffer_pool_sizeが正しく設定されている前提。こちらの記事を参考に)。メモリと同じく、SSDも役に立つ。グラフのカーブが落ち込んでいるのは、テーブルが大きくなっていることから発生するIOによるものだ。ハードディスクが秒間200オペレーション(IOPS)をさばける一方、一般的なSSDでは20000IOPS以上が可能だ。
