この記事は Percona Live 2019 in Texas Austin 現地レポ(Session Day 1) Side.A の3つ目です。

Tips and Tricks with MariaDB ColumnStore

Jim Tommaney氏 (元InfiniDB CTO)

Columnstoreでクエリを行う際の高速化のHOWTOやInnoDBと組み合わせて使用する際のTIPSが紹介されていました。
30分の短いセッションとは思えないほどに数多くのTIPSが紹介されていました。

以下スライドと解説を記載します。

Narrow column with aggregation (1-1 mapping)

Columnstoreは文字列型を最大で8byteまでしかinlineで格納しません(l_shipinstructはchar(25))。
そのため、8byteを超える文字列の他に8byte超のコード列を用意してグルーピングしたほうが格段にIOが減り高速化できます

Narrow column as filter(1-1 mapping)

WHERE(述部)でフィルタする場合のケースです。この場合も同様にinlineで収まる列のコードを使用したほうが早いという結果になります。
例ではsub queryでl_shipinstruct_codeによる絞り込みを行っています

Deferred case statement

最終的な行数が少ないのであれば、Group byしたものを直接CASE..WHENするよりも、一旦サブクエリでマテリアライズした結果に対して
CASE..WHENを実行することで、評価する回数を格段に減らし、高速化が図れるという例示です。

Create table, insert few ros

少ない行数のinsert(100万以下)ではInnoDBとColumnstoreを比較してInnoDBがかなり高速(10x)であることがわかります

Deferred join

遅延評価のテクニックはJOINにも使用することができます。
素直なInner joinよりも、一旦サブクエリでマテリアライズした結果とJOINしたほうが最終的な評価回数が減り高速に完了します。

Insert select many rows(10 million)

多くの行にインデックスが作成されているようなテーブルでは、InnoDBの場合更新系クエリには非常に時間がかかります。
Columnstoreの場合、インデックスはそもそも存在しないのでオーバーヘッドはありませんし、更新系クエリについてもパラレルで動作するので比較的高速です。

Load Data Infile, cpimport 10 million

6と同様ですが、LOAD DATA INFILEでもColumnstoreは高速です。
ただし、Columnstoreではcpimportというインポートツールが存在しており、cpimportを使うと更に高速化されます(3x)

Cross-Engine Join: 7 rows x .48 billion

ColumnstoreとInnoDBのテーブルのJOINも可能です。
この例では7行と48億行のテーブルのJOINを実行した際の時間が例示されており、少量の行の表であればInnoDB EngineとのJOINのほうが若干早いという結果になっています。

Cross-Engine Join + deferred join

ここでも遅延結合のテクニックを使います。
lineitemからの行を少量に絞ることで、概ね変わらない実行速度になっています。

Comment, add 2byte field on leading

これまでのTIPSでもベースになっていましたが、予めLIKEで使用する文字列がわかっていれば、少ないバイト数の列を追加して高速化が図れます。
また、列志向のデータベースでは物理的にカラムのファイルは異なるので他のカラム長に影響されることもありません。

Cartesian hack

Cross joinを行うケースがある場合注意が必要です。Columnstore ではCross joinはサポートされておらず、エラーが返ります。

この例のように、通常のInner joinとしてすべてのフィールドとマッチするようにするハックで対応ができます。
例では値の符号を返すSIGN関数を使っています。

Pull InnoDB into Columnstore

通常InnoDB同士のJOINでは大量データを扱う場合遅いですが、このハックではわざとダミーのテーブル(num)をjoinすることで
Columnstoreの範囲にジョブを引き込み、パラレル処理のメリットを活かしています。
Columnstore同士の処理よりは遅いですが、1度目のInnoDB同士のJOINと比較して15x程度の処理速度がでています。

Select * Views on InnoDB + Columnstore

12のハックを活かしてColumnstoreとInnoDB(パーティション)のテーブルをJOINしたVIEWを作成しています
Experiment 1はColumnstoreテーブルとInnoDB テーブルをJOINしたもので、2はInnoDBのテーブルに対して12のハックを追加してJOINしています。
期待されるのは、Experiment 2が1に比較して高速に完了することです。しかし..

Experiment 2が思ったように高速化できていません。つまり、パーティション表とのJOINは高速化が難しいようです。

Query specific views

それぞれ単体で実行した時には画像の通りの速度のクエリをunion allでJOINしたケースを考えると、遅い方に実行速度は引きずられるという事を言っていたように思いますが、聞きそびれてしまいました。

Outer sort = MySQL, nested sort = Column

最も外側のソートはMySQL(MariaDB)によってUserModule上で行われますが、ネストしたソートはColumnstore上で行われるのでサブクエリでソートし、マテリアライズした表をクエリしたほうが早い(!)というハックです

Top N optimization

Top Nをlimit .. offsetで導出したい場合に、直接limitするのではなく、offset 10した際のvalueを外側のクエリと比較しフィルタ処理を行うことで、最終的な行数が削減されて高速になるというハックです

Additional Top N optimization

16のハックに加えて更に日付条件でサブクエリ内の結果を絞り込むと、48億行とのJOINが0.1秒で完了するという結果がでています。

Brief Overview of Joins

JOINの高速化についての説明です。

• ColumnstoreのJOINはすべてハッシュジョインです。
• 小さい方のテーブルのハッシュマップを全ノードに配布して大きいテーブルとJOINします。
• 最低、最大値はextentに格納されています。

Insert順が昇順の日付やインクリメントされるキーはクエリに役立ちます。Insert時も可能であれば意識したほうがパフォーマンス上のメリットがあります。

MySQL Shell: The Best DBA tool? How to Use the MySQL Shell as a Framework for DBAs

Frédéric Descamps氏 (Oracle)
MySQLエバンジェリスト

MySQL Shellを使用してデータベースを操作する方法についてのセッションでした。
MySQL Shellはモードを切り替えて様々な言語を使用することができます(SQL/JS/Python)。例ではPythonを使用したケースについて紹介されていました。

MySQL Shell 8.0.16からReporting Frameworkが追加されています。
また、自作のモジュールでレポーティング処理の拡張が行えるようになりました。

例えば \show を実行すると有効なレポートの一覧が確認でき、 \show report_name とするとレポートを実行し結果が表示されます。
例えば \show locks_info (locks_infoはユーザ拡張モジュール)とすると現在のロックの一覧が表示できます。
\watch locks_info とすると定期的にレポーティング結果が表示されます。

locks_info.py等は@lefredさんのレポジトリにあります(プルリクウェルカムとのことです)。

https://github.com/lefred/mysql-shell-udr

MySQL8.0から、Data Dictionary(DD)から、eventやroutineを削除する事ができなくなりました

しかし、MySQL Shell経由で一括削除のような操作が可能です。
例えば、 mydba.getProcedures('test','FUNCTION') のように実行できる関数を作成してプロシージャの一覧を取得し、 mydba.deleteProcedures('test') のように削除することができます。

例では、テーブルの断片化を確認する getFragmentedTables() や、カラムのデフォルト値を確認する getDefaults() 等が紹介されていました。

InnotopというInnoDBに関するリアルタイムな負荷状況を確認できるツールがあります。
これは非常に便利でしたがPerlのメンテをするのは難易度が高いというlefredさんの弁。
なので、MySQL Shellの拡張モジュールとしてpythonで書き直したとのこと。

どうやって新しいInnotopを使うの?という点については、以下を参照とのこと。

https://github.com/lefred/mysql-shell-innotop

~/.mysqlsh/mysqlshrc.py に以下のようにパスを追加すると、自在に拡張モジュールをimportできます。

innotopでは以下のファイルから構成されています。
拡張モジュールはmodulesディレクトリに配置します。

すべての拡張モジュールは、必要なモジュールのimportから始まります。

innotopモジュールには、ショートカットが設定できます。

return: モジュール(dummy.py)
stdscr: 画面更新をして表示するかどうか

画面に表示するコードはinnotopのコードをベースにすれば簡単に書けそうです。

DBAにとって非常に心強いツールになっていると感じました。
一方DBAにもプログラミングの素養を求められる時代になりました。。
現場ごとの拡張モジュールを作ればヒューマンエラーを防いだり、複雑な手順のオペレーションも間違いのない順序で実行できたりと、メリットが沢山あります。

まとめ

1日目(Side.A)のレポートは以上です。

英語と技術用語で溺れそうでしたが、早速帰って試してみたい内容が盛りだくさんの一日でした。

2日目のレポートに続きます。