kikeda1104's blog

備忘録・技術に関することを書いています。(webエンジニア)

Redshiftへのデータ流し込み(Batch)

Batchを作ることになったので、そちらを書いていきます。warningを解消する際にすでに公開していたバッチですが、コードを改善したのでまた載せていきます。

サンプルコードは参考までにしてください。

前提

検討・方針

AWS DATAPIPEを利用することも検討したけれど、テーブル毎に定義を増やさなければいけないので、管理しづらいと思いこちらは辞めた。APPにタスクを作成して、バッチ処理することで対応する。ただし、migrationに伴うテーブル定義の変更をRedShift側に伝播できる仕組みはないので、こちらは手動でsqlを回すことになる。(データコピーで利用するテーブルカラムを限定して、設計の時間を稼ぐこともできるがそれも今回はやらない)

処理の流れは、

  • dumpファイルの作成し1GBを超える場合は分割(updated_atで期間は絞る)
  • S3へアップロード
  • コピー用テーブルを作成
  • コピー用テーブルにデータコピー
  • コピー用テーブルとターゲットテーブルでマージして、UPSERTを実行
  • コピー用テーブルを削除

です。

RDBに処理するファイルとステータスを登録・更新し、タスク完了時には、Slackへ通知。(サンプルコードには載せていません)

dumpファイルの作成し1GBを超える場合は分割(updated_atで期間は絞る)

RedshiftCOPY SQLコマンドは、データを複数ファイルに分割することが推奨されています。

注記 データを複数のファイルに分割し、並列処理の長所を最大限に活用することをお勧めします。

分割するファイル数は、ノード数 x スライス数の倍数になります。ds2.xlargeのノードタイプを利用しているので4ファイルに分割します。

gist.github.com

S3へのアップロード

分割したファイル全てを所定のフォルダにアップロードします。今回model.idのフォルダを作成して、その直下にファイルをアップロードします。

gist.github.com

コピー用テーブルを作成

テーブルの属性値は、実際に使われているテーブルを元に作成します。そうすることでコピー用テーブルの管理コストをなくします。

gist.github.com

コピー用テーブルにデータコピー

COPYコマンドによりコピー用テーブルにデータをコピーします。

gist.github.com

コピー用テーブルとターゲットテーブルでマージして、UPSERTを実行

gist.github.com

コピー用テーブルの削除

gist.github.com

課題(解決済み含む)

  • ノードタイプの変更に際して、ノードのストレージ使用量をみているが、テンポラリテーブルを利用することで一時的に使用量が増えることになる。 => タスクが動いた際に使用量の閾値を超えた場合にもアラートが出るので、このアラートが出たらタイプを変更する想定です。 アラートに起因しているかは、その時に調査する。

  • DBのマイグレーションの追随 => これが課題。Rails migrationを利用しているが、この変更にRedshift側のテーブルを追随させる必要がある。今回は, READMEに追加して、注意喚起しているが、migrationコマンドにフックする形でスクリプトを走らせることと、定義をmigrationから解析して反映させるツールを作ることを検討する。

調査と実装で重く、放置しても運用面で負荷が大きい。

参考

docs.aws.amazon.com

ディープコピーを実行する - Amazon Redshift

kikeda1104.hatenablog.com

Redshiftへのデータ流し込み(Batch)

Batchを作ることになったので、そちらを書いていきます。warningを解消する際にすでに公開していたバッチですが、コードを改善したのでまた載せていきます。

サンプルコードは参考までにしてください。

前提

検討・方針

AWS DATAPIPEを利用することも検討したけれど、テーブル毎に定義を増やさなければいけないので、管理しづらいと思いこちらは辞めた。APPにタスクを作成して、バッチ処理することで対応する。ただし、migrationに伴うテーブル定義の変更をRedShift側に伝播できる仕組みはないので、こちらは手動でsqlを回すことになる。(データコピーで利用するテーブルカラムを限定して、設計の時間を稼ぐこともできるがそれも今回はやらない)

処理の流れは、

  • dumpファイルの作成し1GBを超える場合は分割(updated_atで期間は絞る)
  • S3へアップロード
  • コピー用テーブルを作成
  • コピー用テーブルにデータコピー
  • コピー用テーブルとターゲットテーブルでマージして、UPSERTを実行
  • コピー用テーブルを削除

です。

RDBに処理するファイルとステータスを登録・更新し、タスク完了時には、Slackへ通知。(サンプルコードには載せていません)

dumpファイルの作成し1GBを超える場合は分割(updated_atで期間は絞る)

RedshiftCOPY SQLコマンドは、データを複数ファイルに分割することが推奨されています。

注記 データを複数のファイルに分割し、並列処理の長所を最大限に活用することをお勧めします。

分割するファイル数は、ノード数 x スライス数の倍数になります。ds2.xlargeのノードタイプを利用しているので4ファイルに分割します。

gist.github.com

S3へのアップロード

分割したファイル全てを所定のフォルダにアップロードします。今回model.idのフォルダを作成して、その直下にファイルをアップロードします。

gist.github.com

コピー用テーブルを作成

テーブルの属性値は、実際に使われているテーブルを元に作成します。そうすることでコピー用テーブルの管理コストをなくします。

gist.github.com

コピー用テーブルにデータコピー

COPYコマンドによりコピー用テーブルにデータをコピーします。

gist.github.com

コピー用テーブルとターゲットテーブルでマージして、UPSERTを実行

gist.github.com

コピー用テーブルの削除

gist.github.com

課題(解決済み含む)

  • ノードタイプの変更に際して、ノードのストレージ使用量をみているが、テンポラリテーブルを利用することで一時的に使用量が増えることになる。 => タスクが動いた際に使用量の閾値を超えた場合にもアラートが出るので、このアラートが出たらタイプを変更する想定です。 アラートに起因しているかは、その時に調査する。

  • DBのマイグレーションの追随 => これが課題。Rails migrationを利用しているが、この変更にRedshift側のテーブルを追随させる必要がある。今回は, READMEに追加して、注意喚起しているが、migrationコマンドにフックする形でスクリプトを走らせることと、定義をmigrationから解析して反映させるツールを作ることを検討する。

調査と実装で重く、放置しても運用面で負荷が大きい。

参考

docs.aws.amazon.com

ディープコピーを実行する - Amazon Redshift

kikeda1104.hatenablog.com

Rubyからシェルコマンドを実行する(Kernel.#system ..etc)

調べていたので、書いていきます。 

前提

外部コマンドの呼び出し

実装で利用したは、#system, #`を利用していて、コマンドを実行して返却値、ステータスは無視するケースと返却値を利用するケースで分けて使っています。

  • Kernel.#system

    引数を外部コマンドとして実行して、成功した時に真を返します。 子プロセスが終了ステータス 0 で終了すると成功とみなし true を返します。 それ以外の終了ステータスの場合は false を返します。 コマンドを実行できなかった場合は nil を返します。 終了ステータスは変数 $? で参照できます。 コマンドを実行することができなかった場合、多くのシェルはステータス 127 を返します。(したがって $? の数値は、0x7f00)、シェルを介 さない場合は Ruby の子プロセスがステータス 127 で終了します。 コマンドが実行できなかったのか、コマンドが失敗したのかは、普通 $? を参照することで判別可能です。

  • Kernel.#`

    command を外部コマンドとして実行し、その標準出力を文字列として 返します。このメソッドは command の形式で呼ばれます。

  • exec

    引数で指定されたコマンドを実行します。 プロセスの実行コードはそのコマンド(あるいは shell)になるので、 起動に成功した場合、このメソッドからは戻りません。

  • Kernel.#spawn

    引数を外部コマンドとして実行しますが、生成した 子プロセスの終了を待ち合わせません。生成した子プロセスのプロセスIDを返します。

サンプル

コードの切り抜きを載せます。このままだと動かないです。

gist.github.com

参考

docs.ruby-lang.org

docs.ruby-lang.org

docs.ruby-lang.org

docs.ruby-lang.org

AWSのメモリ使用量のモニタリング

EC2のメモリ使用量を継続的に監視するために、cloudwatchに転送するAWS公式で出しているスクリプトを利用した。new_rericが入ったんだけれども、みれる状況じゃなかった。(諸事情)

前提

  • AWS EC2
  • CloudWatch
  • Cloud SNS
方針

EC2のメモリ・スワップ使用量は、EC2のモニタリングでは確認できないので、perlスクリプトをEC2上で実行・5分感覚でスケジュールする。 かつ、障害の問題になったプロセス・タスクは特定できたので、その実行頻度を毎分から毎日1回に変更した。

これにより原因が解消されているかを監視して、事象が発生した場合に検知できる仕組みとして、CPU使用率が75%以上で5分継続した場合にアラートメールを配信するようにSNSに連絡先のメールアドレスを設定した。

EC2への設定

Amazon Linux AMI Amazon Linux AMI バージョン 2014.03 以降を実行している場合は、追加の Perl モジュールをインストールする必要があります。

上記に当てはまるので、この手順に従います。

$ ssh ec2-user@example.com
$ sudo yum install perl-Switch perl-DateTime perl-Sys-Syslog perl-LWP-Protocol-https -y
モニタリングスクリプトをダウンロードする
$ curl https://aws-cloudwatch.s3.amazonaws.com/downloads/CloudWatchMonitoringScripts-1.2.2.zip -O
モニタリングスクリプトのインストール
unzip CloudWatchMonitoringScripts-1.2.2.zip
rm CloudWatchMonitoringScripts-1.2.2.zip
cd aws-scripts-mon

IAMをユーザに

cloudwatch:PutMetricData # このロールはなし
ec2:DescribeTags # このロールはなし
スクリプトのフォルダ内の認証情報を入力する。

ファイルをリネームする。

$ cp awscreds.template awscreds.conf

IAMユーザのaccess keyとsecret keyを入力する。

# awscreds.conf

AWSAccessKeyId=my-access-key-id
AWSSecretKey=my-secret-access-key

テストラン(Cloud Watchにデータは送らない)

./mon-put-instance-data.pl --mem-util --verify --verbose

CloudWatchにレポートするメトリクスをのcronスケジュールを設定する

crontab -e

*/5 * * * * /home/user_name/aws-scripts-mon/mon-put-instance-data.pl --mem-used-incl-cache-buff --mem-util --disk-space-util --disk-path=/ --from-cron

これでAWS consoleからサービス「CloudWatch」から「メトリクス」を選択する

--disk-space-util --disk-space-used --disk-space-avail 

今回は外していますが、ディスクの情報についてもレポートでみたいので、オプションもつけています。

f:id:kikeda1104:20180710182911p:plain

確認できるようになる。

続いて、「アラート」を選択する

CPUUtilization (CPUUtilization)閾値値が75%以上で、5分継続

名前: [ec2 name] CPUUtilization ALERT

説明:CPUUtilization ALERT

>= 75

欠落データの処理方法: 見つかりません

アラートが次の時: 警告

通知の送信先: SNS(トピックに登録し、メールアドレスにメールが配信されるので許可する)

gyazo.com

アラートを作成する。

以上。

課題

参考

Amazon EC2 Linux インスタンスのメモリとディスクのメトリクスのモニタリング - Amazon Elastic Compute Cloud

mysql([Warning] Using a password on the command line interface can be insecure.)

コマンドファイルを作っている際に出てきたので、警告を表示しないようにする。

前提

解決方法

confファイルを利用して、client情報を渡すことにする。

confファイルから読み込む

gist.github.com

以上。

参考

MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.1.2.1 パスワードセキュリティーのためのエンドユーザーガイドライン

sidekiq + redisの構築(Rails 4系) 4-1

インフラの準備を分割して書いていきます。

前提環境:

検証環境として、立てていきます。(EC2はのぞく)

EC2

EC2は立てている前提で、サーバにsshで接続して、redis(client)とmonitをインストール

# ssh -i ~/.ssh/[秘密鍵] ec2-user@example.co.jp
$ sudo yum install -y monit
$ sudo rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
$ sudo yum --enablerepo=remi install redis

ElasticCache(Redis 4)

費用面を抑えたいということで冗長性を犠牲にし、将来的に依存する構成・機能になった際には、タイプを上げること、ノードを増やして1ノードが落ちた際の対応を進めて、冗長性を高める(想定)。

要件が集計用のデータを扱うので、redisの障害により止まったとして、復旧後、再アップロードすることでよしとしてもらう(相談済)

アプリ側の制限として

  • redisのメモリ不足を発生させないように、クラスのインスタンスを渡せるのだが、idを渡す。
  • キューにエンキューするジョブは、再度ジョブを入力しても同じ結果になるようにする(通知系、データ集計.. etc)
  • キューに保存する前に、DBにアクションのレコードを保存する

非同期で扱えるキューが有用なのは判断できているので、信頼性をそのほか(アプリケーション、DB)で担保します。(完全には不可能な場合は、再実行で対応できるように)

Elastic Cache

サービスからElastic Cacheを選択する。

f:id:kikeda1104:20180706151242p:plain

サイドメニューからRedisを選択する。

作成するボタンを選択する

f:id:kikeda1104:20180706152013p:plain

Redisの設定
  • 名前: redis_
  • 説明:
  • エンジンバージョン:4.0.10
  • ポート: 6379
  • パラメータグループ: default.redis4.0
  • ノードのタイプ: t2.cache.t2.micro
  • レプリケーション数: 2
  • 自動フェイルオーバーを備えたマルチAZ: チェック無し
  • サブネット: デフォルト
  • 優先アベイラビリティゾーン: 指定なし
セキュリティ
  • セキュリティグループ: redis-cache: 6379をインバウンドで許可したセキュリティグループ(EC2に追加)
  • 保管時の暗号化: チェック無し
  • 送信時の暗号化: チェック無し

クラスターへのデータのインポート シードするRDBファイルのS3の場所:

バックアップ: 自動バックアップの有効か: チェック無し

メンテナンス メンテナンスウィンドウ: 指定無し

SNS通知のトピック: 通知の無効化

作成ボタンを選択する

次にクラスター名をクリックして、プライマリエンドポイントをコピーしてアプリケーションから参照できるようにする。

f:id:kikeda1104:20180706152052p:plain

課題

サイズ変更に伴う指標の確認

スケーリング問題の早期通知の取得 – リシャーディングは計算処理能力を集中的に使用するオペレーションであるため、リシャーディング中は CPU 使用率をマルチコアインスタンスで 80% 未満、シングルコアインスタンスで 50% 未満にすることをお勧めします。Redis 用 ElastiCache メトリックスをモニタリングして、アプリケーションでスケーリングの問題が発生する前にリシャーディングを開始します。追跡すると有用なメトリックスは、CPUUtilization、NetworkBytesIn、NetworkBytesOut、CurrConnections、NewConnections、FreeableMemory、SwapUsage、BytesUsedForCache です。

上記の通知が発生した段階で利用するノードタイプを変更するタイミングの指標にする。

クラスター・レプリケーションへの変更タイミングの時間帯

また、メンテナンスのタイミングについて、今回指定無しにしているが、クラスター・レプリケーションへの変更を延期している場合にメンテナンスのタイミングで変更が行われる。この期間を指定して、変更時のアクセス障害が起きるのを回避もしくは起きづらい時間帯に指定する。(深夜帯にアクセスが起きづらいタイプのサービスでは、深夜帯に指定する) (ダウンタイムが発生する恐れがあり、ユーザのアクセスを防げない場合には、アプリのメンテナンスとして、ユーザに通知する措置が必要になってくる)

参考

Amazon ElastiCache (インメモリキャッシュ管理・操作サービス) | AWS

ベストプラクティス: オンラインクラスターのサイズ変更 - Redis 用 Amazon ElastiCache

メンテナンスを実行するタイミングの管理 - Redis 用 Amazon ElastiCache

Redshiftへのデータの流し込みについて

環境の違いでスクリプトを変えたので、そちらを載せます kikeda1104.hatenablog.com

kikeda1104.hatenablog.com

SQLファイル -> sh

# tab区切りでファイル出力: dump.shとする
PASSWORD=password
mysql -u username -p"$PASSWORD" -h hostname db -e "select * from table_name" > /tmp/table_name.txt

コマンド実行

bash dump.sh

s3へのアップロード

s3へのアップロード aws認証情報を登録 awsのIAMからaccess_key, secret_access_keyを取得、なければ作成する。defaultは登録されているので、プロファイル名は別で定義する。 $ aws configure --profile profile_name AWS Access Key ID [None]: access_key AWS Secret Access Key [None]: secret_access_key Default region name [None]: ap-northeast-1 Default output format [None]: json shファイルを作る syncコマンドを利用してアップロードします。shファイルを作ります。 # upload_to_s3.sh aws s3 sync /tmp/txts s3://bucket_name/txts --profile profile_name --exclude '*' --include '*.txt' .... shファイルを実行 bash upload_to_s3.sh # 結果 move: /tmp/table_name.txt to s3://bucket_name/txts/table_name.txt # 成功 move failed: /tmp/table_name.txt to s3://bucket_name/txts/table_name.txt Could not connect to the endpoint URL: "endpoing" #失敗

tsvファイル出力(MySQL)とaws s3アップロード - kikeda1104's blog

テーブル定義

redshiftにテーブルを作る。 # tables.sql create table tables ( id BIGINT NOT NULL PRIMARY KEY, name VARCHAR(255), created_at TIMESTAMP, updated_at TIMESTAMP ); ソートキーは無視します。 mysql側に定義したテーブルのカラム順番とredshiftのカラムの順番を一致させている。 値を流し込む場合にredshiftの仕様により流し込みが失敗・エラーになるケースがあります。 String length exceeds DDL length Mysqlで扱うVARCHARの仕様のズレによりRedshiftではsizeを変更。 渡された数値を文字数として扱うかバイト数で扱うかの違い postgresql clientからredshiftに接続して、テーブルを作ります。 コマンドを簡易化するためにshファイルを作っておく。 # redshift.sh psql -Uusername poscalc --host=endpoint -p port # redshiftのusernameに対応したpasswordを入力する bash redshift.sh < tables.sql

テーブル定義/データインポート(Amazon Redshift) - kikeda1104's blog

s3からRedShift

#copy_to_redshift.sh

\timing

copy table_name
from 's3://bucket_name/txts/table_name.txt'
iam_role 'arn:aws:iam::664020506447:role/RedshiftRoleForClient'
region 'ap-northeast-1'
blanksasnull
emptyasnull
delimiter '\t'
NULL AS 'NULL'
;

...

参考

COPY - Amazon Redshift

Redshiftの環境構築

Redshiftの環境構築について書いていきます。

前提

  • RedShift

  • データサイズが増加量を計測して毎月1GB増える

  • 将来的にデータサイズが、クラスターのストレージを超える可能性も検討してサイズ変更ができるように設計
    • Snapshotを利用したデータサイズの変更により停止せずにスケールを予定する。

構築

サービスから「Amazon Redshift」を選択する。

クラスターを起動」を選択。

f:id:kikeda1104:20180704213108p:plain

項目を入力する。
  • クラスター識別子: my-dw-instance
  • データベース名: mydb (省略するとdev)
  • データベースポート: 5439
  • マスタユーザ名: awsuser

f:id:kikeda1104:20180704212800p:plain

f:id:kikeda1104:20180704213555p:plain

f:id:kikeda1104:20180704214030p:plain

f:id:kikeda1104:20180704214039p:plain

確認画面になりますので、「クラスターの起動」を選択します。

IAM(ロールを作成)

aws cliからCOPYコマンドを実行するために、ロールを登録します。

サービスから「IAM」を選択する。 「ロール」-> 「ロール作成」を選択

f:id:kikeda1104:20180704214749p:plain

「Redshift」 -> 「RedShift -Customizable」を選択して「次のステップ: アクセス権限」

「AmazonRedshiftFullAccess」、「AmazonS3ReadOnlyAccess」を割り当てます。

「次のステップ: 確認」を選択する。

f:id:kikeda1104:20180704225012p:plain

  • ロール名: ClientForRedshift
  • ロール説明: デフォルト
  • 「ロールを作成」を選択します。
概要
  • このロールのARNをRedShiftのコマンドで利用する(今回はここまで) f:id:kikeda1104:20180704225508p:plain

RedshiftにIAMをsyncする。

サービスから「Amazon Redshift」を選択する。

クラスターをチェックボックスをチェックして、「IAMロールの管理」を選択。

追加したロール「ClientForRedshift」を選択して、「変更の適用」を選択。

クラスターステータスが、「使用可能」になれば完了です。

費用感

無料枠の場合は、750時間まで無料になっており、複数ノードの場合はより速く無料期間が無くなります。

ちなみに、(2018/07/04時点では) タイプ別の費用感はこちら。 f:id:kikeda1104:20180704212351p:plain

参考

料金 - Amazon Redshift(ビッグデータ向けデータウェアハウス)|AWS

Basic認証(S3 + CloudFront + Lamdba)

コーポレートサイトを作っていて検証として公開したいが、閲覧の制限をかけたかったので急ぎめでBasic認証をかけました。

前提

  • httpsでの公開まで終えている
  • Lamdba CloudFrontと連携させてBasic認証を行う

環境構築

  • Lamdbaで、リュージョンをバージニア北部にする。
  • 画像は、一度lambdaの関数を作成したことがない場合です。「関数」を作成ボタンをクリックする。 f:id:kikeda1104:20180703191838p:plain

  • 名前: CorporateWebsite

  • ランタイム: Node js 6.10
  • ロール: デフォルト(テンプレートから新しいロールを作成)
  • ロール名: lambda_edge_exection
  • ポリシーテンプレート: Basic Edge Lamdba アクセス権限
  • 関数の作成をクリックする

f:id:kikeda1104:20180703192202p:plain

関数コードにコードを貼り付ける。user, passwordは変更すること。

gist.github.com

  • ヘッダーの「保存」をクリックする。(画像は切れています) f:id:kikeda1104:20180703192419p:plain

  • ヘッダーのアクションからバージョン発行をクリックする。

  • バージョンの説明を書き、発行をクリックする。

  • ARNをコピー(保存)する。($lastestではなく、発行したバージョンになっていることを確認)(画像は$LATESTです)

gyazo.com

CloudFront

  • Distributionsを選択して、Distribution Settingsをクリックする。
  • タブからBehaviorsを選択する。

  • 該当のBehaviorsを選択して、Editボタンをクリックする。

f:id:kikeda1104:20180703193235p:plain

Lambda Function Associations Event Typeは、 viewer Requestを選択して、Lambda Function ARNにコピーしておいたARNを入力する。

Yes, Editをクリックする。

確認

サイトにアクセスして、Basic認証がかかっているか確認する。

かかっていることが確認できたので、完了です。

f:id:kikeda1104:20180703193720p:plain

参考

qiita.com

Redshiftのソートキー

前回の記事の続きでRedShiftのチューニング(ソートキー)を定義します。今回は結論はなしで、ベンチマークを残します。

前提

ソートキー

RedShiftでは、ソートキーを元に並び替えてデータをディスクに格納します。

最新のデータが最も頻繁にクエリ処理される場合は、タイムスタンプ列をソートキーの主要な列として指定します。 クエリは時間範囲外のブロック全体をスキップできるので、効率性が高まります。

1 つの列に対して範囲フィルタリングまたは等価性フィルタリングを頻繁に実行する場合は、その列をソートキーとして指定します。 Amazon Redshift は、各ブロックに格納された列の最小値と最大値を追跡し、述語範囲に当てはまらないブロックをスキップできるので、その列のブロック全体のデータを読み込む必要がなくなります。

テーブルを頻繁に結合する場合は、結合列をソートキーと分散キーの両方として指定します。 これにより、クエリオプティマイザは、より時間のかかるハッシュ結合ではなくソートマージ結合を選択できるようになります。データが結合キーですでにソートされているので、クエリオプティマイザはソートマージ結合のソートフェーズをバイパスできます。

ベンチマーク

複合ソートキーの指定ありで、copyコマンドの実行時間とサイズを検証してみた。

ファクト1つに対して、ディメンションが2つという仮定です。

copyコマンド(ソートキー指定なし)

INFO:  Load into table 'table_a' completed, 28000000 record(s) loaded successfully.
Time: 80379.260 ms

INFO:  Load into table 'table_b' completed, 1400 record(s) loaded successfully.
Time: 3378.901 ms

INFO:  Load into table 'table_c' completed, 178 record(s) loaded successfully.
Time: 3766.339 ms

copyコマンド(ソートキー指定あり)

INFO:  Load into table 'table_a' completed, 28000000 record(s) loaded successfully.
Time: 138134.710 ms

INFO:  Load into table 'table_b' completed, 1400 record(s) loaded successfully.
Time: 35942.661 ms

INFO:  Load into table 'table_c' completed, 178 record(s) loaded successfully.
Time: 47404.385 ms

サイズ

table ソートキー指定なしmb ソートキー指定ありmb
table_a 30 30
table_b 444 602
table_c 36 72

SQL

クエリの実行時間を比較するときは、最初にクエリを実行するときの結果を使用しないでください。その代わり、各クエリの 2 回目の実行時間を比較します。 1回目のクエリ実行時間は、破棄します。

# どちらも設定値を更新。セッション時にキャッシュを無効化

set enable_result_cache_for_session to off;

# ソートキーなし
select * from table_a limit 10000;
Time: 316.842 ms

select * from table_a, table_b
where table_a.b_id = table_b.id AND table_a.c_id = table_c.id
AND table_a.created_date > '2017/06/01';

Time: 10859.859 ms

# ソートキーあり
select * from table_b limit 10000;
Time: 1245.379 ms

select * from table_a, table_b, table_c
where table_a.b_id = table_b.id AND table_a.c_id = table_c.id
AND table_a.created_at  > "2018/06/01";

Time: 217966.788 ms

課題

頻繁に結合する外部キーをソートキーとしましたが、COPYの実行時間、クエリ実行時間、サイズが増えています。 複合ソートキーを指定して、順番も合わせて条件式に加えましたがクエリの実行時間が20倍遅くなっています。前後の調査を含めて、エンコードは、ソートキーを除いてCOPYコマンドで自動設定し、ソートキーも結合キーではなく、条件で頻繁に使われる列に限定することにします。 分散キーについては、結合を頻繁に行われるテーブルでは、ソートキーと分散キー共に設定することが推奨されていますが、速度低下の可能性もありベンチマークを取り個別で設定を進めていきます。

参考

https://kikeda1104.hatenablog.com/entry/2018/06/29/210000

最良のソートキーの選択 - Amazon Redshift

ステップ 3: ソートキーを選択する - Amazon Redshift

ソートキーの選択 - Amazon Redshift

クエリ実行時間の表示(PostgreSQL, RedShift)

PostgreSQLクライアントから操作する際に、クエリの実行時間が、表示されていない。計測で必要だったので設定を調べた。

前提

コマンド

\timing

db=# \timing
Timing is on.

# COPYコマンド
INFO:  Load into table 'table_name' completed, 32 record(s) loaded successfully.
Time: 2905.679 ms

参考

PostgreSQL: Documentation: 8.0: psql