最近のMariaDBでは、10.4以降従来のMariaDB Serverに加え、DHW向け列ストアデータベースであるMariaDB Columnstoreとの統合が推進されています。
MariaDB Server(InnoDBを始めとする行ベースストレージエンジン)の強みであるOLTP処理が高速であることと、MariaDB Columnstoreの強みである、分析系クエリが高速であることを組み合わせ、HTAP(Hybrid transactional/analytical processing)を実現するための施策として、MariaDB Maxscale 2.4に新たにSmartRouterが追加されました。
SmartRouter
https://mariadb.com/kb/en/mariadb-maxscale-24-smartrouter/
今回は、このSmartRouterについてご紹介したいと思います。
SmartRouterとは
この機能は、複数のデータベースに対し、最も高速に処理結果を返す事ができるサーバに処理をルーティングするための機能です。
データベース自体は複数用意する必要がありますが、各データベースのデータがレプリケーションされていれば、MaxScaleにより1つのシステムとしてより効率的にまとめる事が可能となりました。
また、今回ご紹介する内容には含まれませんが、MaxScaleには障害が発生したレプリケーション環境のフェイルオーバ機能も備えています。
加えて、MariaDB Columnstoreでは、1.2.5よりInnoDBからColumnstoreストレージエンジンのテーブルへのレプリケーションをサポートしています。
MariaDBはMariaDB ServerとColumnstore、Galera Clusterを1つのバイナリにまとめる等、多様な機能を実現する事を推進していますが、その目標の総まとめとなる機能のように感じます。
MaxScaleについてのおさらい
ここで、MaxScaleの基本的なモジュールの役割について簡単におさらいします。
MaxScaleは個別の役割を持つモジュールが協調し、プロキシサーバとしての機能を実現しています。
| モジュール | 役割 |
|---|---|
| Server | MariaDBサーバの定義 |
| Service | 複数のMariaDBサーバをまとめる定義。Service内にRouter/Filter等の設定を含めます。 |
| Router | Service内のサーバへのルーティング基準を決定する機能。マスタ/スレーブを考慮したルーティングなどを実現します。 |
| Monitor | Service内のサーバの死活監視を行う機能。Monitorによって異常が検知されたサーバは接続候補から外れます。また、フェイルオーバ処理も担当します。 |
| Listener | Serviceへの接続先エンドポイントの定義 |
SmartRouterはその名の通りRouterに所属する機能です。
前提となる環境情報
今回は、以下の環境を作成しました。
| ホスト名 | OS | 説明 |
|---|---|---|
| htap1 | CentOS 7 | MariaDB Columnstore 1.2.5(コンポジット構成) |
| htap2 | CentOS 7 | MariaDB Server 10.4.12 |
| htap3 | CentOS 7 | MaxScale |
※コンポジット構成とは、PM/UMが同居する構成です。
検証環境の構築
各環境を以下の手順で作成します。
MariaDB Serverのmysqldにログインする場合は、 mariadb> 、 MariaDB Columnstoreの場合は、 columnstore> としています。
MaxScaleは maxscale> とします。
MariaDB Columnstore
パッケージをインストールします。
|
1 2 3 4 5 6 7 8 9 |
$ cat >> /etc/yum.repos.d/columnstore.repo <<-EOS [mariadb-columnstore] name=MariaDB ColumnStore baseurl=https://downloads.mariadb.com/MariaDB/mariadb-columnstore/latest/yum/centos/7/x86_64/ gpgkey=https://downloads.mariadb.com/MariaDB/mariadb-columnstore/RPM-GPG-KEY-MariaDB-ColumnStore gpgcheck=1 EOS $ yum install -y epel-release # v1.2.3からの依存パッケージにjemallocが追加されたために必要です。 $ yum install -y mariadb*columnstore* |
コンポジット構成でColumnstoreを起動します。
|
1 2 |
$ . /usr/local/mariadb/columnstore/bin/columnstoreAlias $ LANG=C postConfigure -qs |
レプリケーション機能を有効化します。
|
1 2 |
$ configxml.sh setconfig SystemConfig ReplicationEnabled Y $ mcsadmin restartSystem y |
MariaDB Server
パッケージをインストールします。
|
1 2 |
$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash $ yum install -y MariaDB-server |
バイナリログを有効化します。
また、InnoDB – Columnstoreのレプリケーションはステートメントベースレプリケーションのみという制約があるため、
追加の設定を行います。
|
1 2 3 4 5 6 |
$ cat >> /etc/my.cnf.d/rep.cnf <<-EOF [mysqld] log_bin binlog_format = STATEMENT server_id = 100 EOF |
起動します。
|
1 |
$ systemctl start mariadb |
MaxScale
MaxScaleのパッケージをインストールします。
|
1 2 |
$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash $ yum install -y maxscale MariaDB-client |
設定ファイルは本ファイルを使用します。
各パラメータの意味については、マニュアルをご確認ください。
/etc/maxscale.cnfを置き換えてください。
|
1 2 |
$ mv /etc/maxscale.cnf{,.org} $ cp maxscale-htap.cnf /etc/maxscale.cnf |
レプリケーションの開始
レプリケーションユーザをMariaDB Serverに追加します。
|
1 2 3 |
mariadb> set sql_log_bin = 0; mariadb> grant replication slave on *.* to <code>rep</code>@<code>%</code> identified by 'P@ssw0rd'; mariadb> set sql_log_bin = 1; |
MariaDB Columnstore でスレーブ設定を行い、レプリケーションを開始します。
|
1 2 |
columnstore> change master to master_user="rep" , master_password="P@ssw0rd", master_host="htap2", master_use_gtid = slave_pos; columnstore> start slave; |
maxscale用ユーザを作成します。
|
1 |
mariadb> grant all on *.* to maxscale identified by 'P@ssw0rd'; |
SmartRouterのアーキテクチャ
SmartRouterは、特性の異なる同じスキーマ構造、データを持つService(MariaDBクラスタ)をターゲットとする事を目的にした機能です。
ターゲットがいずれも通常のMariaDB Serverだった場合、概ねクエリは同じ時間で返ってくるためあまり意味を成しません。
Service Aは列ストア、Service Bは行ストアであるような環境である時に、ユーザからは透過的に最善のルーティングを行うことができます。
1つのRouterはあくまで1つのService内でのルーティング方法のみを定義できますので、SmartRouterにより直接2つのServiceを統合できません。
また、ターゲットのサービスによって、細かに別のRouting方式を使い分けたい場合もあります。
これらを実現するために、SmartRouterはRouterのRouterとして機能するように設計されています。
そして、以下の基準でルーティングを行います。
- 実行されたクエリを正規化(条件句の値を?に置き換えた形式)し、保存します
- 1がこれまでに実行された事がない場合は、全てのターゲットサーバにクエリを実行します
- 最も早く応答を返した結果をクライアントに返却、その他のクエリ結果は破棄されます
- 3のルーティング先をクエリとともに保存します
2回目からは4の結果が照合され、クエリがルーティングされます
データ量の変化とともに最適なサーバは異なってくるため、定期的にクエリは再測定されます。
また、一般的なクエリではないコマンド(内部的にはCOM_QUERYであるかどうか)は、masterに定義したホストにルーティングされる動作となります。
検証データの準備
今回は、1GB程度のTPC-H用データを利用しました。
データ生成については、以下のスクリプトを利用しました。
詳しい利用方法についてご説明は省略させていただきます。
https://sourceforge.net/projects/osdldbt/files/dbt3/
ここで注意点があります。
InnoDB(マスタ)とColumnstore(スレーブ)では、Columnstoreにはインデックスが無い等の差異があるため、一部のCREATE構文はエラーになります。
ですので、InnoDBでは通常のテーブルを レプリケーションせずに 作成し互換性のある形式のテーブルをColumnstore側に作成します。
|
1 2 3 |
mariadb> set sql_bin_log = 0; mariadb> CREATE TABLE .. ENGINE INNODB; mariadb> exit |
|
1 2 |
columnstore> CREATE TABLE .. ENGINE COLUMNSTORE; columnstore> exit |
これについては、運用の際も重要となりますのでご注意ください。
クエリログの有効化
クエリの実行状況を確認するために、クエリロギングを有効化しておきます。
|
1 2 |
mariadb> set global slow_query_log = 1; mariadb> set global long_query_time = 0; |
Columnstoreでは、内部的にクエリは分割実行されるため、スロークエリログで出力することはできません。
その代替としてinfinidb_querystats.querystatsテーブルに実行されたクエリをロギングする機能があり、今回はそれを有効化します。
|
1 2 |
$ setConfig QueryStats Enabled Y $ mcsadmin restartSystem y |
なお、infinidb_querystats.querystatsは秒以下の精度で速度を測定できませんので、概ねの速度の比較となります。
検証
プライマリキーを指定し、単一の結果を得るSQLと、複数テーブルを使用し結果を集計するSQLを実行します。
Query1
|
1 |
select * from lineitem where l_orderkey = 1 and l_linenumber = 1; |
Query2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date_sub('1998-12-01', interval '101' day) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus; |
それぞれのサーバで直接クエリを実行した場合、実行時間は以下の通りです。
| 名称 | 実行時間(InnoDB) | 実行時間(Columnstore) |
|---|---|---|
| Query1 | 0.002 sec | 0.615 sec |
| Query2 | 31.386 sec | 6.280 sec |
これは状況にもよりますが、Query1はInnoDBのほうが早く、Query2はColumnstoreのほうが早いということがわかります。
SmartQuery RouterからのQuery実行
それではMaxScaleサーバ上で、SmartRouterに接続しクエリを実行します。
Query1
|
1 2 3 4 |
$ mysql -umaxscale -pP@ssw0rd -h127.0.0.1 -P3333 tpch maxscale> select * from lineitem where l_orderkey = 1 and l_linenumber = 1; : 1 row in set (0.383 sec) |
InnoDBで要した時間をスロークエリログから確認してみると、0.000921秒要していました。
|
1 2 3 4 5 6 7 |
# Time: 200425 12:34:01 # User@Host: maxscale[maxscale] @ htap3 [192.168.121.101] # Thread_id: 49 Schema: tpch QC_hit: No # Query_time: 0.000921 Lock_time: 0.000154 Rows_sent: 1 Rows_examined: 1 # Rows_affected: 0 Bytes_sent: 1233 SET timestamp=1587818041; select * from lineitem where l_orderkey = 1 and l_linenumber = 1; |
Columnstoreでも同じクエリが実行されたことが確認できます。
1秒以下で実行は完了したようですが、実績からInnoDBよりは遅かったと予想されます。
|
1 2 3 4 5 |
columnstore> select query, startTime, endTime from querystats\G : query: select * from lineitem where l_orderkey = 1 and l_linenumber = 1 startTime: 2020-04-25 12:34:01 endTime: 2020-04-25 12:34:01 |
もう一度SmartRouterから実行してみましょう。
|
1 2 3 |
maxscale> select * from lineitem where l_orderkey = 1 and l_linenumber = 1; : 1 row in set (0.002 sec) |
InnoDBでの実行速度に近づきました。1回目では解析等の処理から速度が通常より落ちてしまいますが、2回目は前処理なく実行されたと考えられます。
InnoDBのスロークエリログには、以下の出力が確認できました。
|
1 2 3 4 5 6 7 |
# Time: 200425 12:44:26 # User@Host: maxscale[maxscale] @ htap3 [192.168.121.101] # Thread_id: 49 Schema: tpch QC_hit: No # Query_time: 0.000605 Lock_time: 0.000176 Rows_sent: 1 Rows_examined: 1 # Rows_affected: 0 Bytes_sent: 1233 SET timestamp=1587818666; select * from lineitem where l_orderkey = 1 and l_linenumber = 1; |
一方Columnstoreでは同じ時刻に実行されたクエリは確認できませんでした。
つまり、InnoDBでクエリは実行したほうが早いと判断されたことがわかりました。
|
1 2 |
columnstore> select query, startTime, endTime from querystats where startTime > "2020-04-25 12:44:20"\G Empty set (0.007 sec) |
条件句の変更
次に条件句の値を変更して実行してみましょう。
|
1 |
maxscale> select * from lineitem where l_orderkey = 2 and l_linenumber = 1; |
InnoDBには以下のログが出力されました。
|
1 2 3 4 5 |
# Time: 200425 12:56:27 : # Query_time: 0.000537 Lock_time: 0.000161 Rows_sent: 1 Rows_examined: 1 : select * from lineitem where l_orderkey = 2 and l_linenumber = 1; |
Columnstoreにはクエリログは出力されません。
つまり、条件句を変えるごとに解析が行われるようなことが無いということが確認できました。
|
1 2 |
columnstore> select query, startTime, endTime from querystats where startTime > "2020-04-25 12:56:20"\G Empty set (0.002 sec) |
Query2
では、Query2を実行してみましょう。
|
1 2 3 |
mariadb> select ..(omit).. ; : 4 rows in set, 1 warning (31.132 sec) |
最初の実行ではInnoDBに要する実行時間がかかってしまいました。
InnoDBのスロークエリログには以下の出力が確認できます。
※Time = ログが出力された時刻
|
1 2 3 4 5 6 7 8 |
# Time: 200425 13:00:36 : # Query_time: 31.130536 Lock_time: 0.000207 Rows_sent: 4 Rows_examined: 6001223 : select l_returnflag, l_linestatus, : |
ColumnStoreではやはり6秒程度要しているようです。
|
1 2 3 |
query: select ..(omit).. ; startTime: 2020-04-25 13:00:04 endTime: 2020-04-25 13:00:10 |
2回目の実行では、やはりColumnstoreにルーティングされました。
|
1 2 3 |
MySQL [tpch]> select ..(omit).. ; : 4 rows in set, 1 warning (5.780 sec) |
Columnstoreには以下のクエリログが確認できました。
|
1 2 3 |
query: select ..(omit).. ; startTime: 2020-04-25 13:12:52 endTime: 2020-04-25 13:12:58 |
InnoDBのログには、クエリは出力されませんでした。
|
1 2 |
$ grep "13:" hostname-slow.log | tail -1 # Time: 200425 13:09:01 |
SmartRouterの制限と注意点
- LOAD DATA LOCAL INFILE はサポートされていません。
|
1 2 |
$ seq 100 | mysql -umaxscale -pP@ssw0rd -h127.0.0.1 -P3333 -e 'load data local infile "/dev/stdin" into table test.t' ERROR 1927 (08S01) at line 1: Connection killed by MaxScale |
- パフォーマンスデータは永続化されていませんので、MaxScaleの停止により失われます。
- 解析タイミングのクエリ実行は遅いサーバがクエリのキャンセル応答に応じるまでレスポンスは返却されません。
- 定期的に統計の更新が行われる都合上、クエリの実行時間が変化するタイミングがあります。
まとめ
リリースされたばかりの機能ということもあり、いくつかの制限事項から現時点ではまだ利用に適したシチュエーションは限られているようにも思いますがこの試み自体は今後の可能性を感じました。
開発が進み、更にスマートなターゲットを切り替えが実装されれば、MariaDBに収まらないMySQLフォーク製品も対象にする等うまく利用できるものと思います。
今後要チェックな機能ですので、今のうちから検証してみてはいかがでしょうか。
