この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
本日は、繰り返し発行される定型的なクエリをキャッシュする事によってクエリのレスポンス速度を向上する機能である、Result Cachingをご紹介します。
特長
- リーダーノードのインメモリーキャッシュで、1秒未満で結果を返す
- 透過的 – キャッシュの存在を意識せずに動作する
- WLMのスキップ、処理のスキップ、最適化のスキップ
- キャッシュはセッション間で横断して持続する
- キャッシュによってAmazon Redshiftクラスタが解放され、他の非反復クエリのパフォーマンスが向上する
Result Caching 処理の流れ
- クエリはリーダーノードに送ります
- キャッシュにクエリ結果が含まれている場合は、処理しないでキャッシュを返します
- キャッシュにクエリ結果がない場合は、クエリが実行され、結果がキャッシュされます
AWSのラボでの検証結果の紹介
re:Invent 2017 セッション ABD327 - Migrating Your Traditional Data Warehouse to a Modern Data LakeではAWSのラボでの検証結果を紹介しています。
Result Cachingによるスループットの向上
上記の図のオレンジのバーがCachingのスループット（1時間あたりのクエリ数(QPH)）を表し、スループットの改善が見られます。
- 読み書きワークロード(スモールクエリ、ラージクエリ、INSERT、COPY、VACUUMの混在)
- ds2.xlargeクラスタ、4ノード構成
Result Cachingによるレイテンシの短縮
上記の図のオレンジのバーがCachingのレイテンシの平均を表し、応答速度の改善が見られます。
- ds2.xlargeクラスタ、4ノード構成
- Tableau Dash board 10-user test
キャッシュが有効になる条件
- クラスタバージョンが、1.0.1582で有効であることを確認しています。
- キャッシュにヒットさせるためにはデータの変更が行われておらずクエリも一致する必要です。
- enable_result_cache_for_sessionパラメーターで変更可能（デフォルトでは有効）
- キャッシュヒットしたクエリは、SVL_QLOGテーブルにsource_queryカラムにキャッシュしたクエリIDが設定されている
実験
レイテンシ
SVL_QLOGテーブルのelapsed(endtime-starttime)がレイテンシになります。以下の例では、25マイクロ秒です。この値はキャッシュしているサイズに依存しますが私の様々な検証の範囲では1000000マイクロ秒（１秒）未満に収まっています。実際にクライアントに転送する時間が生じますが、少なくともRedshiftのリーダーノードでは処理が終わっていることを表します。
cmdb=> select userid,query,starttime,endtime,elapsed,substring,source_query from svl_qlog;
userid | query | starttime | endtime | elapsed | substring | source_query
--------+---------+----------------------------+----------------------------+------------+--------------------------------------------------------------+--------------
111 | 1736699 | 2017-12-17 10:08:10.409498 | 2017-12-17 10:08:10.409523 | 25 | select sum(lo_extendedprice*lo_discount) as revenue from lin | 1736627クエリのキャッシュ動作とSVL_QLOGテーブル
まず、以下のクエリを先に実行します。クエリの実行時間は8.183882秒です。まだ、キャッシュされていないクエリなので、source_queryは、nullです。
userid | query | elapsed | substring | source_query
--------+---------+------------+--------------------------------------------------------------+--------------
111 | 1736627 | 8183882 | select sum(lo_extendedprice*lo_discount) as revenue from lin |以下のクエリを実行すると、先程のクエリID 1736627をキャッシュしていることが確認できます。また、上記と同じクエリですが、先頭に 「/* 1 */」を付けて実行します。これは単純な文字列の一致なのかを確認したいという意図がありました。クエリ履歴に任意のコメントを追加してもクエリキャッシュに悪影響がないことも確認できました。
userid | query | elapsed | substring | source_query
--------+---------+------------+--------------------------------------------------------------+--------------
111 | 1736702 | 28 | /* 1 */ select sum(lo_extendedprice*lo_discount) as revenue | 1736627クエリのキャッシュの有効範囲
初回は、5016.536 ミリ秒でしたが、２回目はキャッシュヒットしたので 218.658 ミリ秒で応答が返りました。
cmdb=> select count(*) from lineorder_cp where lo_orderkey%2 = 1;
count
-----------
300035462
(1 row)
Time: 5016.536 ms (00:05.017)
cmdb=> select count(*) from lineorder_cp where lo_orderkey%2 = 1;
count
-----------
300035462
(1 row)
Time: 218.658 msつぎに、lineorder_cpテーブルにレコードを追加してキャッシュを無効にします。レコード追加後の１回目のクエリは 4795.626 ミリ秒なので、キャッシュが有効でないことは明らかです。２回目のクエリは同様にキャッシュヒットしたので 251.301 ミリ秒で応答が返りました。
cmdb=> INSERT INTO lineorder_cp VALUES(1,1,1,1,1,19950000,'9','8',1,1,1,1,1,1,1,1,'7');
INSERT 0 1
Time: 552.078 ms
cmdb=> select count(*) from lineorder_cp where lo_orderkey%2 = 1;
count
-----------
300035463
(1 row)
Time: 4795.626 ms (00:04.796)
cmdb=> select count(*) from lineorder_cp where lo_orderkey%2 = 1;
count
-----------
300035463
(1 row)
Time: 251.301 msSVL_QLOGテーブルには、上記の動作がそのまま記録されています。
userid | query | starttime | endtime | elapsed | substring | source_query
--------+---------+---------------------------+----------------------------+---------+--------------------------------------------------------------+--------------
111 | 1740192 |2017-12-18 06:34:59.161026 | 2017-12-18 06:34:59.16105 | 24 | select count(*) from lineorder_cp where lo_orderkey%2 = 1; | 1740191
111 | 1740191 |2017-12-18 06:34:51.294518 | 2017-12-18 06:34:55.858847 | 4564329 | select count(*) from lineorder_cp where lo_orderkey%2 = 1; |
111 | 1740189 |2017-12-18 06:34:42.323123 | 2017-12-18 06:34:42.358499 | 35376 | INSERT INTO lineorder_cp VALUES(1,1,1,1,1,19950000,'9','8',1 |
111 | 1740177 |2017-12-18 06:29:53.272336 | 2017-12-18 06:29:53.272362 | 26 | select count(*) from lineorder_cp where lo_orderkey%2 = 1; | 1740173
111 | 1740173 |2017-12-18 06:29:29.884674 | 2017-12-18 06:29:34.51187 | 4627196 | select count(*) from lineorder_cp where lo_orderkey%2 = 1; |執筆時点では、キャシュの保存期間、保存サイズ、アルゴリズムについての情報は提供されていません。
Tableau Desktop でクエリのキャッシュが機能するか？
検証に利用したバージョンは、10.3.5です。このバージョンはTableau Desktop自身も過去のクエリをキャッシュするので、Tableau Desktopを２つ起動して同じ条件でクエリを実行します。
残念ながら、下記の通り、Tableau Desktopのクエリはカーソルを利用しているのでクエリのキャッシュはヒットしませんでした。上が２回目のsource_queryが全てnull、つまりキャッシュしていないということです。
cmdb=> select * from svl_qlog order by starttime desc limit 20;
userid | query | starttime | endtime | elapsed | substring | source_query
--------+---------+----------------------------+----------------------------+----------+--------------------------------------------------------------+--------------
111 | 1740624 | 2017-12-18 08:50:26.68124 | 2017-12-18 08:50:55.084615 | 28403375 | fetch 10000 in "SQL_CUR4"; |
111 | 1740623 | 2017-12-18 08:50:25.216363 | 2017-12-18 08:50:25.229954 | 13591 | fetch 10000 in "SQL_CUR3"; |
111 | 1740622 | 2017-12-18 08:50:23.08607 | 2017-12-18 08:50:44.374169 | 21288099 | fetch 10000 in "SQL_CUR3"; |
111 | 1740621 | 2017-12-18 08:50:21.785079 | 2017-12-18 08:50:21.82325 | 38171 | Undoing 1 transactions on table 896761 with current xid 1474 |
111 | 1740620 | 2017-12-18 08:50:20.091101 | 2017-12-18 08:50:20.102855 | 11754 | fetch 10000 in "SQL_CUR3"; |
111 | 1740617 | 2017-12-18 08:49:07.75114 | 2017-12-18 08:49:36.365425 | 28614285 | fetch 10000 in "SQL_CUR4"; |
111 | 1740616 | 2017-12-18 08:49:06.261073 | 2017-12-18 08:49:06.277422 | 16349 | fetch 10000 in "SQL_CUR3"; |
111 | 1740615 | 2017-12-18 08:49:04.131041 | 2017-12-18 08:49:25.182463 | 21051422 | fetch 10000 in "SQL_CUR3"; |
111 | 1740614 | 2017-12-18 08:49:02.736189 | 2017-12-18 08:49:02.753682 | 17493 | Undoing 1 transactions on table 896759 with current xid 1474 |
111 | 1740612 | 2017-12-18 08:49:01.015853 | 2017-12-18 08:49:01.027947 | 12094 | fetch 10000 in "SQL_CUR3"; |でも、re:inventのセッションでも、”Tableau dashboard ; 10-user test” の結果が出ていたので、なんとかなるはず。ということで、Tableau設定でRedshiftのカーソルを外して検証してみると、いい感じにキャッシュされました。Tableau設定でRedshiftのカーソルを外して検証してみる方法は以下のブログを参照してください。
クエリID 1740697 は、多くの行をキャッシュしているので406マイクロ秒かかっていますが、参照元のクエリID 1740688 の 29637923マイクロ秒（約30秒）と比較すると圧倒的に速く結果が帰ってきていることが確認できます。
userid | query | starttime | endtime | elapsed | substring | source_query
--------+---------+----------------------------+----------------------------+----------+--------------------------------------------------------------+--------------
111 | 1740697 | 2017-12-18 09:15:53.321534 | 2017-12-18 09:15:53.32194 | 406 | SELECT CAST("lineorder"."lo_orderdate" AS TEXT) AS "lo_order | 1740688
111 | 1740696 | 2017-12-18 09:15:52.203751 | 2017-12-18 09:15:52.216802 | 13051 | select count(*) from stv_slices; |
111 | 1740695 | 2017-12-18 09:15:50.466085 | 2017-12-18 09:15:50.466115 | 30 | SELECT "lineorder"."lo_shipmode" AS "lo_shipmode" FROM "cm_i | 1740686
111 | 1740693 | 2017-12-18 09:15:49.290061 | 2017-12-18 09:15:49.307572 | 17511 | Undoing 1 transactions on table 896765 with current xid 1474 |
111 | 1740692 | 2017-12-18 09:15:47.853452 | 2017-12-18 09:15:47.865492 | 12040 | select count(*) from stv_slices; |
111 | 1740690 | 2017-12-18 09:14:52.76766 | 2017-12-18 09:14:52.935084 | 167424 | select * from svl_qlog order by starttime desc limit 20; |
111 | 1740688 | 2017-12-18 09:14:13.89156 | 2017-12-18 09:14:43.529483 | 29637923 | SELECT CAST("lineorder"."lo_orderdate" AS TEXT) AS "lo_order |
111 | 1740687 | 2017-12-18 09:14:12.863443 | 2017-12-18 09:14:12.875411 | 11968 | select count(*) from stv_slices; |
111 | 1740686 | 2017-12-18 09:14:11.209356 | 2017-12-18 09:14:33.36566 | 22156304 | SELECT "lineorder"."lo_shipmode" AS "lo_shipmode" FROM "cm_i |
111 | 1740685 | 2017-12-18 09:14:09.869824 | 2017-12-18 09:14:09.886929 | 17105 | Undoing 1 transactions on table 896763 with current xid 1474 |
111 | 1740684 | 2017-12-18 09:14:08.353572 | 2017-12-18 09:14:08.366022 | 12450 | select count(*) from stv_slices; |今回検証に利用したTable Desktopバージョンは、10.3.5ですが、これよりも古いバージョンの場合は、生成されるSQLにハッシュを含んでいる可能性がありキャッシュヒットしない可能性がありますのでご注意ください。
Redshift Spectrum の外部テーブルに対するクエリ結果もキャッシュするのか？
外部テーブルに対するクエリ結果は、キャッシュヒットしませんでした。
-- cmdb=> select count(*) from cm_user_db.lineorder;
-- count
-- -----------
-- 600037902
-- (1 row)
userid | query | starttime | endtime | elapsed | substring | source_query
--------+---------+----------------------------+----------------------------+----------+--------------------------------------------------------------+--------------
111 | 1740873 | 2017-12-18 10:24:42.603728 | 2017-12-18 10:25:58.398826 | 75795098 | select count(*) from cm_user_db.lineorder; |
111 | 1740870 | 2017-12-18 10:23:16.180889 | 2017-12-18 10:24:35.97845 | 79797561 | select count(*) from cm_user_db.lineorder; |では、上記のファクトである外部テーブル（cm_user_db.lineorder）とRedshift上のマスタを結合した結果も試してみましたが、キャッシュヒットしませんでした。
-- cmdb=> select c_city, s_city, d_year, sum(lo_revenue) as revenue
-- from customer, cm_user_db.lineorder, supplier, dwdate
-- where lo_custkey = c_custkey
-- and lo_suppkey = s_suppkey
-- and lo_orderdate = d_datekey
-- and (c_city='UNITED KI1' or c_city='UNITED KI5')
-- and (s_city='UNITED KI1' or s_city='UNITED KI5')
-- and d_yearmonth = 'Dec1997'
-- group by c_city, s_city, d_year
-- order by d_year asc, revenue desc;
-- c_city | s_city | d_year | revenue
-- ------------+------------+--------+-----------
-- UNITED KI5 | UNITED KI5 | 1997 | 373974482
-- UNITED KI1 | UNITED KI5 | 1997 | 365124652
-- UNITED KI1 | UNITED KI1 | 1997 | 358824801
-- UNITED KI5 | UNITED KI1 | 1997 | 355652352
-- (4 rows)
--
-- Time: 74353.023 ms (01:14.353)
userid | query | starttime | endtime | elapsed | substring | source_query
--------+---------+----------------------------+----------------------------+----------+--------------------------------------------------------------+--------------
111 | 1740921 | 2017-12-18 10:40:19.819709 | 2017-12-18 10:41:33.752625 | 73932916 | select c_city, s_city, d_year, sum(lo_revenue) as revenue fr |
111 | 1740916 | 2017-12-18 10:38:37.705716 | 2017-12-18 10:40:11.670671 | 93964955 | select c_city, s_city, d_year, sum(lo_revenue) as revenue fr |最後に
想像以上に速く、リーダーノードの負担も少なく、特別な設定なして使えるのは大変喜ばしい限りです。想定外だったのは外部テーブルをサポートしていないという点です。きっと将来対応してくれるだろうと信じています。Tableauからクエリキャッシュを利用するにはTableau設定でRedshiftのカーソルを外して利用する必要がありますが、このあたりはTableauの仕様なので誰もが使えるようにうまく調整してほしいです。
恐らく東京リージョンでもクエリキャッシュが有効になっているはずですので、違いを体感していただければ幸いです。
10
