わるいことするのに使うやつ
Set-ItemProperty "test.txt" -Name LastWriteTime -Value "2018/06/01 11:22:33"
わるいことするのに使うやつ
Set-ItemProperty "test.txt" -Name LastWriteTime -Value "2018/06/01 11:22:33"
-- セッション中、結果キャッシュを切る -- コンマ数秒で結果が返ってくることはなくなるが、 -- 同じようなレコードを取得するクエリを連続で実行すると、 -- コンピューティングノードで直近ディスクロードした内容が -- オンメモリになってキャッシュされているか何かで -- 読み込みが倍以上に早くなり正確に計測できなくなる。 set enable_result_cache_for_session TO off; -- トランザクション開始 begin; -- 何かクエリ select * from pg_user; -- クエリID指定で、直前のクエリのWLMのクエリ処理ログ確認。トランザクション切っていると、新しく出たのは自トランザクションで切ったものしか見えない。 SELECT userid, xid, query, total_queue_time, exec_end_time FROM stl_wlm_query WHERE query = PG_LAST_QUERY_ID();
golangの値のコピーはClangの代入演算子とほぼ同じ。 structの代入はstruct全内容のコピーをとる。(値渡し)
type Sample struct { Hoge int Huga string } var val, val2 Sample // val == 0x4000、val2 == 0x5000のアドレスがそれぞれ割り当てられたとする val = Sample{10, "test"} // 0x4000 == {10, "test"} val2 = val // 0x4000 == {10, "test"}、0x5000 == {10, "test"} val2.Hoge = 20 // 0x4000 == {10, "test"}、0x5000 == {20, "test"}
ポインタとは、変数のショートカットを作る方法です。 - 苦しんで覚えるC言語 15章:ポインタ変数の仕組み
ローカル変数の&を単純にとると、単純にローカル変数に割り当てられたアドレスをコピって値にする。
type SomeType struct { X int } func main() { type SomeType struct { X int } func main() { pary := []*SomeType var v SomeType // メモリアドレス 0x40000000で確保されたとする v = SomeType{1} // 0x40000000 == {1} pary = append(pary, &v); // pary == [0x40000000] v = SomeType{2} // 0x40000000 == {2} に上書き pary = append(pary, &v); // pary == [0x40000000, 0x40000000] for _, pval := range pary { fmt.Println(*pval); } // {2} // {2} // と表示。 }
paryに別個にallocateされた領域を食わせる場合、newって明示的に領域をアロケートする。
type SomeType struct { X int } func main() { pary := []*SomeType var v *SomeType // メモリアドレス 0x40000000で確保されたとする v = new SomeType{} // 0x40000000 == 0x50000000 v.X = 1 pary = append(pary, v); // pary == [0x50000000] v = &SomeType{2} // 0x40000000 == 0x50000008、↑と同様のシンタクスシュガー pary = append(pary, v); // pary == [0x50000000, 0x50000008] for _, pval := range pary { fmt.Println(*pval); } // {1} // {2} // と表示。 }
Javaの参照型・string型の代入は全部アロケート相当なのでそっち慣れてるとハマる。
ローカル変数のポインタを関数でreturnしてもつかえるのも理解するうえでいやらしい。。CやC++とは違い、ローカル変数のアドレスをスタックから分離して全てガベコレできるようにしている。「コールスタック解放≠コールスタック内の局所変数の領域解放」 Javaの実装と同じ要領で、golangの使用感はCテイストだがコールスタックの処理はCでいうポインタ渡しでうまいことしているんだろうか
やらかした。
SELECT order.orderno, orderdtl.orderidx, order.orderdate, NULL AS customer_id FROM order ;
上記のように、SELECTで型ヒントなしに NULL
を指定すると、型が "unknown"
となる。
CREATE VIEW view_orderlist AS SELECT order.orderno::numeric, orderdtl.orderidx::int, order.orderdate::date, NULL::unknown AS customer_id FROM order ;
通常の選択操作では困らないが、特定のSQLでエラーとなる。
SELECT COUNT(DISTINCT customer_id) FROM view_orderlist; -- postgresql 9.4 : ERROR: failed to find conversion function from unknown to text -- Redshift:[Amazon](500310) Invalid operation: could not identify an equality operator for type "unknown";
エラー内容を読むに、unknown
型から別の型へ変換しようとしたが、暗黙の変換方法が見つからなかった旨のエラーが出力される。
これは、型推論ができない場合の事象らしく、 CASE
や coalesce()
などで具体的な型になる値が与えられているなら問題がない。
ビューの定義や列の選択時に型を指定することで解消する。
CREATE VIEW view_orderlist AS SELECT order.orderno, orderdtl.orderidx, order.orderdate, NULL::numeric(14) AS customer_id FROM order ;
手製のアプリケーションでも count(distinct 列)
なんて書かないから大丈夫だろうと思いきや
Tableauという製品がなにやらデータ取得前に上記相当のSQLを実行するらしく、この項目を使うと画面がバグって使えなくなってしまった。
超悔しい。
仕事でRedshiftのチューニングをすることになりそうなのでメモ
※適宜更新
色々な概念の紹介
https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/working-with-clusters.html
Amazon Redshift データウェアハウスは、ノードと呼ばれるコンピューティングリソースのコレクションであり、これらはクラスターと呼ばれるグループを構成します。各クラスターは、1 つの Amazon Redshift エンジンを実行し、1 つ以上のデータベースを含みます。
1機の仮想DBサーバー。 クラスタ一つサーバーとして落としたり上げたりしたり、稼働時間=課金額になったりする。 クエリを投げるのもクラスタに対して投げる。
https://doc.aws.amazon.com/ja_jp/redshift/latest/dg/c_high_level_system_architecture.html
クラスタの先頭に備えている、1機のコンピューティングリソース。 SQLの構文解析やSQL結果キャッシュ、WMLの機能(コンピューティングノード処理の同時処理数制限など)を担当。
https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/working-with-clusters.html
Amazon Redshift クラスターは、ノードで構成されています。クラスターごとに、リーダーノードと 1 つまたは複数のコンピューティングノードがあります。リーダーノードは、クライアントアプリケーションからクエリを受け取ってクエリを解析し、クエリ実行プランを作成します。次に、これらのプランの並列実行をコンピューティングノードと調整し、コンピューティングノードからの中間結果を集計します。最終的にクライアントアプリケーションに結果を返します。
演算、データ保持担当のコンピューティングリソース。 料金表を眺めたことがある方は、以下のインスタンスタイプに見覚えがあるかもしれない。
現在の名前 | 以前の名前 |
---|---|
ds2.xlarge | ds1.xlarge、dw.hs1.xlarge、dw1.xlarge |
ds2.8xlarge | ds1.8xlarge、dw.hs1.8xlarge、dw1.8xlarge |
dc1.large | dw2.large |
dc1.8xlarge | dw2.8xlarge |
内部に「ノードスライス」という末端コンピューティングリソースを持つ。CPUコア1つにつき1スライス。
コンピューティングノード内の一番最小のリソース割当単位。CPUや記憶領域(HDD・SSDやメモリ)のリソースセット。 コンピューティングノードのCPU 1 コアにつき 1 ノード存在する。テーブルの実データは、どれかのスライスに分散される。
料金表には出てこないが、パフォーマンスチューニングでは重要な概念。(詳しくは後述)
システムテーブル STV_TBL_PERM
を用いれば、テーブルごとの各スライスの分散状況が見れる。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_TBL_PERM.html
Redshiftは、列ごとに、1 MB 区切りのブロックにデータを格納する。このブロックをデータブロックと呼ぶ。 各データブロックごとにゾーンマップと呼ばれるメタ情報があり、値の最大値・最小値やブロック内の値の個数などのメタ情報を保持する。ゾーンマップはメモリに保持される。
データブロックは、システムテーブル STV_BLOCKLIST
から一覧が見れる。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_BLOCKLIST.html
レコードが挿入された時、テーブルごとに設定された分散スタイルに従っていずれかのノードに分散される。
分散スタイルについて
スライスごとに並列で計算するので、均等に分散ができているほど、特定のスライスに負荷がかかりすぎるなどが発生せず高速に演算できる。
2つのテーブルを結合するとき、各結合するデータ同士が同じスライスに分散されている場合は、そのまま計算するが、違うスライスにあった場合は、全ノードにばらまきなおす「再分散」が起きる。 explainでSQLの実行計画を見ると、「DS_BCAST_INNER」など「BCAST」付きの結合をしていると出力される場合がある。これは再分散が発生している。 再分散を避ける設計は2つ。
分散スタイルで「キー分散」を選ぶときは、テーブルのうち1つの項目を指定する。 一般的に、レコード数の偏りがないものを選ぶとよい。(受注番号やidなど連番で重複のないもの) 上記の再分散を避けるため、「ヘッダー」-「明細」のようによく結合すうるテーブル間で共通のキーを設定するとよい。
分散キーは、テーブルのJOINで結合キーに指定していると再分散を防げる。 実際に効果があるかどうかは、explainで実行計画をみてテーブル同士の結合時の分散のしかたを見るとよい。 (コストに関してはあまりアテにならないので見ない)
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_data_redistribution.html
上記にある通り、 DS_BCAST_INNER
や DS_BCAST_BOTH
が出ると結合のパフォーマンスが著しく悪くなる。
いくつかSQLを作って実行計画を見た感じ、2のテーブルをJOINする程度の簡易なクエリであれば DS_BCAST_INNER
は発生せず、
少し複雑になって tableX <-> tableY <-> tableZ <-> tableV
で結合するSQLが分散キーで不一致になると割と出るようになる。
分散の回数、転送バイト数、処理した行数が見れる
-- トランザクションを初めておく BEGIN; -- 測定したいクエリを実行 SELECT col, count(*) FROM some_queries GROUP BY col; -- 最後に想定したクエリのquery idを取得 SELECT PG_LAST_QUERY_ID(); -- BCASTな分散のログ SELECT * FROM pg_catalog.stl_bcast WHERE query = <↑で見たqueryid>; -- DISTな分散のログ SELECT * FROM pg_catalog.stl_dist WHERE query = <↑で見たqueryid>;
条件: マートやFACT、実績データなど、参照のしかたでGROUP BY/WHERE/JOIN キーが不定
条件:EVENだとJOINが遅い、バッチなどでしか使わず結合、GROUP BYのキーなどが一定
例)日次アクセス実績データ
path | logday | logym | ua_category | pvcount |
---|---|---|---|---|
/index.html | 2018/01/01 | 201801 | bot | 3000 |
/index.html | 2018/01/01 | 201801 | pc | 300 |
/index.html | 2018/01/01 | 201801 | sp | 500 |
/index.html | 2018/01/02 | 201801 | bot | 3300 |
/index.html | 2018/01/02 | 201801 | pc | 300 |
/index.html | 2018/01/02 | 201801 | sp | 500 |
危ないパターン:集計はだいたい月ごとでやるから、logymに distkey 設定したら再分散避けれるんじゃないか?(単純に集計単位となるキーをあてる)
--日次のレポートつくるときのSQL SELECT logday, ua_category, SUM(pvcount) FROM taccesslogsummary GROUP BY logday, ua_category WHERE logym='201801'; -- ここ
再分散を避けるより、EVEN分散にして全ノードで分散演算したほうが、データ量によっては速い
分散キーはワイルドカードではなく、実行計画において再分散を避けるだけの効果しかない。
STL_PROJECT
を見ると各スライスから読み出した行数を見れるので、そちらで計測するとよし。チューニングの奥義。ゆえに時と場合、代償が求められる。
通常、INSERTすると一番最後のデータブロックに追記される形でデータが格納。
値が自然とソート順になれば問題ないが、1つのデータブロック内で値の最大/最小値が大きくなるとWhere句による読み込みスキップが発生しづらくなる。
テーブルにソートキーを設定すると、最後にVACUUMした or テーブル0件から INSERT した時点までのデータが、ソートキー順に物理的に並ぶ。この物理的にソートされた列に対してWHERE句をかけると劇的に効く。
もちろんソートキーが設定されている項目をクエリで使ったときのみ有効。理屈では、ソートキー以外の列のゾーンマップは最大・最小値が相対的に拡大するため、そちらのみをフィルタするクエリの性能は数%劣化する。
【2019/06/23追記】詳しい記事をかいた。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Compressing_data_on_disk.html
データの物理的な保存形式を指定する。単純な圧縮ではなくデータ配列の仕方に近く、様々な記録方式がある。(詳しくは公式ドキュメント参照) 最適なものを選べば、スライス内でのディスク→メモリへのページインにおけるディスクI/Oが減る。(・・・が、ディスクReadあたりのメモリに展開される容量が相対的に増える)
一般的には高圧縮であればよいが、例えば1MBのデータブロックを読み出すと10GBにメモリ展開される場合、1行欲しい場合でも1データブロック単位での展開が強要されるため過多なメモリコピーが発生するし、スキャンする際はデータブロックが単位となるので、1データブロックで10GB相当のレコードを全件参照することになる。
勘所としては下記のようなケース。
ディスクサイズのとりかた = STV_BLOCKLISTビューを参照
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_BLOCKLIST.html
-- 特定テーブルの列ごとのデータブロック(1MBのディスク書き込み領域)消費量をサマる SELECT name, col, slice, count(*) AS "using disk(MB)", avg(num_values) AS "num values/1MB" FROM pg_catalog_stv_blocklist WHERE name ='tablename' GROUP BY name, col, slice ORDER BY name, col, slice;
colがテーブルの列のindex。ただし、末尾3列は暗黙的に追加される非表示列。 ここから、1record 読むあたりのI/O処理バイト数が読める。
Read/Writeの際には、別途列圧縮のための演算が各コンピューティングノードにかかる。
(2019/08/25追記) ディスク使用率に関する検証結果をまとめた。
SQL実行したときのリソース割り当て管理をしてくれる。
Amazon Redshiftのワークロード管理(WLM)を使ってミックスワークロードを実行する | Amazon Web Services ブログ
CPUが使用率低いのにSQLが実行されない・・・というボトルネックがある場合こいつを見直すとよいかもしれない。 むしろ運用上、①アプリ用グループ ②バッチ用グループ ③保守用グループ ④特権ユーザー用グループ とわけないといざというときにSQLが実行できなくなるので、もし困っているのであれば早めに検討したほうがよい。
デフォルトは同時実行数 5 。理論上は500まで設定可能。しかし、無制限に開放すると様々なリソースを浪費して逆に遅くなる。
システムテーブル STL_WLM_QUERY
から、スーパーユーザーなら全ユーザーの実行履歴、通常ユーザーならばそのユーザーの分のSQL実行履歴が見れる。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_WLM_QUERY.htmldocs.aws.amazon.com
その中で total_queue_time
が 0 でなければ、そのクエリは同時実行数の制限によりキュー待ちとなっている。(単位はマイクロ秒)
Redshift公式でも、定期実行が勧められている。
DELETEやUPDATEによりできた不使用空間のコレクト、ソートキーに基づいた再ソートを実施する。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_VACUUM_command.html https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Reclaiming_storage_space202.html https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/vacuum-managing-volume-of-unmerged-rows.html
テーブルの統計情報を更新する。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/vacuum-managing-volume-of-unmerged-rows.html
SET
コマンドを利用してANALYZE実行セッションのみ 0 書き換えを推奨。RedhisftはVARCHAR型の列を選択する時、メモリ上に型サイズそのままの領域を確保する・・・といわれおり、パフォーマンスに多大な影響を与えてきた。 過去何回か、対策したらしきアップデートがあったが、どうも日本社内のデータマートではあまり改善が見られなかった。
要件が絡む以上難しくなるが、VARCHAR型を持つ時、下記のようにチューニングできないか検討する。
別記事かく
備忘録メモ
https://wiki.postgresql.org/wiki/Query_Parsing
raw_parserという関数があるらしい。
postgres/parser.c at 9d4649ca49416111aee2c84b7e4441a0b7aa2fac · postgres/postgres · GitHub
構文別にノードツリーレベルまで展開してくれるが、おそらく意味解析は行われない。 自分は意味解析までほしいのでもうちょっと実装しないといけないが、いい感じにparseしてくれるなら使うかもしれない。
Postgresqlは、date
など普通はキーワードだろ?という単語も列名にできる。以下は、テーブル定義によってはエラーとならない。
SELECT date FROM ordertransaction;
例えばSQLにおいて、 ( )
は様々な意味合いを持つ。
SELECT * FROM ( SELECT clientid, orderno, orderym, RANK() OVER(PARTITION BY clientid, orderym ORDER BY orderno) AS idx FROM ordertransaction ) Q ORDER BY clientid, idx;
パーサーは前から順に読み込み構文を判断する。
しかし ( )
のように一節を読み切った後に前まで読んでいた式をどう評価すべきか不確定なときがある。
RDBの実装を見たことはないが、おそらくメタメタなパターンマッチングを行わなければいけない気がする。
SELECT count(*) FROM master;
SELECT
まで読む → 構文のはじまりはSELECT、INSERT、WITHなど固定のキーワードから始まるため、これはSELECT句で確定count
まで読む → SELECTのあとは、DISTINCTなどのキーワードか、各抽出列の指定(サブクエリ、関数、項目名、定数など)をとりえる。この時点では、count
が列名なのか、関数名なのか不明(謎の識別子状態)(
まで読む → ここで count
が関数名だと確定する?*
まで読む → 各抽出列の指定(サブクエリ、関数、項目名、定数など)か、全列を指定するキーワード *
をとりえる。パターンマッチングで唯一である *
が確定
...単なる列指定するところでもサブクエリで複数階層化できるところがメタメタに難しそう。 いい感じの関数のインタフェース考えながら、EBNFとにらめっこして作ることになりそう