PostgreSQLでパフォーマンスを比較してみた (EXISTSとINNER JOIN)

はじめまして。5月に入社したryohei515です。

前職ではOracleを使ってSQLを書く機会がよくあり、パフォーマンスチューニング等も行ってきました。

インゲージに入社してからSQLを書く機会があったのですが、DBがPostgreSQLであるため、これまで使っていた細かなSQLのパフォーマンスの小技がPostgreSQLでも通用するのか気になり、検証してみました。

環境準備

Dockerを使って、PostgreSQLのコンテナを作成します。最新の13.3を使用しています。

ちょっと検証したい時に、ローカルを汚さずに簡単に環境が作れるので便利ですね!

docker run --rm -d -p 15432:5432 -v postgres-tmp:/var/lib/postgresql/data -e POSTGRES_HOST_AUTH_METHOD=trust postgres:13.3

※ パフォーマンスを見たいだけなので、パスワード不要となるよう、POSTGRES_HOST_AUTH_METHODtrueに設定しました。

今回の検証用のデータは、postgrteSQLのTutorialで提供されている、dvdrentalを使用しようと思います。

ER図も用意されており、SQLの動作確認や学習なんかにも使えそうですね。

PostgreSQL Sample Database Diagram

データ登録の前に、事前にDATABASEを作成しておきます。

psql -h localhost -p 15432 -U postgres -c "CREATE DATABASE dvdrental;"

以下のサイトよりzipファイルをダウンロードし、ダウンロードしたディレクトリに移動します。

PostgreSQL Sample Database

ダウンロードファイルを、unzipコマンドで展開し、.tarのファイルを作成します。

$ unzip ./dvdrental.zip
Archive:  /Users/makabe/Library/Mobile Documents/com~apple~CloudDocs/Documents/memo/091_blog/20210719_SQL_performance/dvdrental.zip
  inflating: dvdrental.tar

事前に作成しておいたDBに、以下のコマンドでデータ作成します。

(特にメッセージ等表示されず、完了します。)

pg_restore -U postgres -d dvdrental -h localhost -p 15432 ./dvdrental.tar

これで以下のコマンドを打ち込めば準備完了です。

psql -h localhost -p 15432 -U postgres -d dvdrental

パフォーマンスチェック

環境は準備できたので、パフォーマンスを測ってみたいと思います。

EXISTSではなくINNER JOINを使う」というのを、これまでよく使っていたので、実行時間やコストにどんな差が出るか見てみます。

(ざっと検索してみたところ、PostgreSQLでも同じそう…)

SQLの内容は、Rentalテーブルのデータで、length > 100Filmがどれだけあるかという内容で見ていきます。

EXISTSで書いてみるとこんな感じ。

select
  r.*
from
  rental r
where exists(
  select
    1
  from
    inventory i
  inner join
    film f
  on i.film_id = f.film_id
  where 1=1
  and r.inventory_id = i.inventory_id
  and f.length > 100
)

INNER JOINで書いてみるとこんな感じかと思います。

select
  r.*
from
  rental r
inner join
  inventory i
on r.inventory_id = i.inventory_id
inner join
  film f
on i.film_id = f.film_id
where 1=1
and f.length > 100
;

PostgreSQLでは、SQLの頭にEXPLAIN ANALYZEをつけることで、実行計画を見ることができるので、それぞれ出力してみるとこうなりました。

EXISTS

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=191.88..653.14 rows=9771 width=36) (actual time=4.001..12.163 rows=9723 loops=1)
   Hash Cond: (r.inventory_id = i.inventory_id)
   ->  Seq Scan on rental r  (cost=0.00..310.44 rows=16044 width=36) (actual time=0.014..2.067 rows=16044 loops=1)
   ->  Hash  (cost=157.00..157.00 rows=2790 width=4) (actual time=3.915..3.918 rows=2785 loops=1)
         Buckets: 4096  Batches: 1  Memory Usage: 130kB
         ->  Hash Join  (cost=74.11..157.00 rows=2790 width=4) (actual time=0.564..2.816 rows=2785 loops=1)
               Hash Cond: (i.film_id = f.film_id)
               ->  Seq Scan on inventory i  (cost=0.00..70.81 rows=4581 width=6) (actual time=0.016..0.662 rows=4581 loops=1)
               ->  Hash  (cost=66.50..66.50 rows=609 width=4) (actual time=0.512..0.514 rows=610 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 30kB
                     ->  Seq Scan on film f  (cost=0.00..66.50 rows=609 width=4) (actual time=0.007..0.354 rows=610 loops=1)
                           Filter: (length > 100)
                           Rows Removed by Filter: 390
 Planning Time: 2.396 ms
 Execution Time: 12.817 ms
(15 rows)

INNER JOIN

------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=172.62..596.63 rows=5343 width=36) (actual time=3.954..10.271 rows=9723 loops=1)
   Hash Cond: (r.inventory_id = i.inventory_id)
   ->  Seq Scan on rental r  (cost=0.00..310.44 rows=16044 width=36) (actual time=0.011..1.635 rows=16044 loops=1)
   ->  Hash  (cost=153.55..153.55 rows=1525 width=4) (actual time=3.892..3.895 rows=2785 loops=1)
         Buckets: 4096 (originally 2048)  Batches: 1 (originally 1)  Memory Usage: 130kB
         ->  Hash Join  (cost=70.66..153.55 rows=1525 width=4) (actual time=0.609..3.106 rows=2785 loops=1)
               Hash Cond: (i.film_id = f.film_id)
               ->  Seq Scan on inventory i  (cost=0.00..70.81 rows=4581 width=6) (actual time=0.013..0.632 rows=4581 loops=1)
               ->  Hash  (cost=66.50..66.50 rows=333 width=4) (actual time=0.572..0.574 rows=610 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 30kB
                     ->  Seq Scan on film f  (cost=0.00..66.50 rows=333 width=4) (actual time=0.012..0.409 rows=610 loops=1)
                           Filter: (length > 100)
                           Rows Removed by Filter: 390
 Planning Time: 1.070 ms
 Execution Time: 10.788 ms
(15 rows)

Rentalの件数が16000件しかないので、大した違いはありませんが、Planning TimeExecution TimeともにINNER JOINのSQLの方が速いことがわかります。

各SQLの実行計画で主に違う点は、一番上の計画のようです。

Hash Semi Join  (cost=191.88..653.14 rows=9771 width=36) # EXISTS
Hash Join  (cost=172.62..596.63 rows=5343 width=36)      # INNER JOIN
  • cost
    • 左側の数字: 初期処理の推定コスト。 出力段階が開始できるようになる前に消費される時間。
    • 右側の数字: 全体推定コスト。
  • rows: 行の推定数。
  • width: 行のバイト単位の長さ。

ここでもコストが初期処理の推定コスト・全体推定コスト共にINNER JOINの方が小さく、これが処理時間の違いにつながったのかと思います。

rowsが異なるのは、あくまで推定値だからということでしょうか。

まとめ

環境構築して実行したSQLの実行計画より、INNER JOINの方がEXISTSより早いことを確認することができました。

ただINNER JOINだと、SQLを読む側にとって抽出条件と分かりにくいので、件数が少ないようなテーブルであれば、EXISTSの方がよいでしょう。ケースバイケースですね。

また、PostgreSQLのTutorialのデータ量だと、パフォーマンスも問題にならず、時間がかかると実感できなかったので、同様のことをする機会があれば、今度はデータ量をもっと多くして試してみたいと思います。

参考サイト

QUIC を試してみた

こんにちは masm11 です。 QUIC の RFC が出ましたね。

https://datatracker.ietf.org/doc/html/rfc9000

5月なので2ヶ月も前ですが… というわけで、今回は遅ればせながら QUIC を試してみたいと思います。

nginx を設定

まず、nginx を QUIC 対応版にします。Arch Linux を使っているので AUR からインストールします。

wget https://aur.archlinux.org/cgit/aur.git/snapshot/nginx-quic.tar.gz
tar zxf nginx-quic.tar.gz
cd nginx-quic 
makepkg -s
sudo pacman -U nginx-quic-1.21.0-3-x86_64.pkg.tar.zst

そして /etc/nginx/nginx.conf を設定します。

server ブロックに以下のように quic と http3 を指定します。

   server {
        listen       443 ssl http2;
        listen       [::]:443 ssl http2;
        listen       443 quic http3;
        listen       [::]:443 quic http3;

server ブロックに以下の一行を追加します。

       add_header Alt-Svc 'h3=":443"; ma=86400, h3-29=":443"; ma=86400';

これは「QUIC も使えるよ〜」という宣言です。

nginx を restart します。

sudo systemctl restart nginx

軽く確認してみます。

shiro:nginx % sudo ss -lnp | grep :443
udp   UNCONN 0      0             0.0.0.0:443              0.0.0.0:*     users:(("nginx",pid=21153,fd=9),("nginx",pid=21152,fd=9))
udp   UNCONN 0      0                [::]:443                 [::]:*     users:(("nginx",pid=21153,fd=10),("nginx",pid=21152,fd=10))
tcp   LISTEN 0      511           0.0.0.0:443              0.0.0.0:*     users:(("nginx",pid=21153,fd=7),("nginx",pid=21152,fd=7))
tcp   LISTEN 0      511              [::]:443                 [::]:*     users:(("nginx",pid=21153,fd=8),("nginx",pid=21152,fd=8))
shiro:nginx % 

TCP に加えて UDP も bind してますね。

ファイアウォールに穴を開ける

使っているツールによりますが、iptables ならこんな感じでできます。

sudo iptables -I INPUT -p udp --destination-port 443 -j ACCEPT

確認

ではブラウザからアクセスしてみましょう。

サーバで以下のように実行しておき、

sudo tcpdump -i ens3 -n -s 0 -w cap.dat port 443

Firefox からアクセスしてみます。

Firefox のデベロッパーツールで見ると、alt-svc ヘッダと HTTP/3 であることが確認できます。

f:id:masm11:20210716215941p:plain

また、tcpdump が生成した cap.dat を手元にコピーして wireshark で見ると、 QUIC で通信できていることが確認できます。

f:id:masm11:20210716215953p:plain

なお QUIC は、まずは TCP でアクセスし、UDP で接続できそうなら (alt-svc で指定されていたら) 次から UDP を使います。なので、最初のうちは TCP になります。 確認する際は多少まとまった通信をした方が良いです。

まとめ

今回は QUIC をテストしてみました。 最新技術っていいですよね 。

インゲージでは最新技術もわくわくなエンジニアを募集しています。以下のページからお願いします。

https://ingage.co.jp/recruit/

ChatOps: SlackからLambdaに連携してみる(4)

id:kizashi1122 です。

f:id:ingage:20210620201734p:plain

ひとまず Slack から Lambda を呼び出すところまでは終わりました。 (といってもURL確認用のリクエストを受け取っただけです)

ワークスペースにアプリをインストールする

まだコードは何も書いてないですが、Slack から使ってみたいのでインストールします。

f:id:ingage:20210628122716p:plain

クリックします。次の画面では「Accept」すればよいです。 チェックが入りましたね。

f:id:ingage:20210628122833p:plain

ではこの「echoapp」をSlackのチャンネルに追加してみましょう。最初はテスト用に作ったチャンネルで試すのがいいかもしれません。ここでは #app-test としています。

f:id:ingage:20210628123503p:plain

上記の「Add an App」をクリックして「echoapp」を選択して追加します。

f:id:ingage:20210628123608p:plain

追加されました。ではメンションしてみましょう。

f:id:ingage:20210628124724p:plain

何も起きません・・・。
そもそも Lambda まで届いているのかすらわかりません。ログを確認しましょう。

ここから確認できます。

f:id:ingage:20210628124903p:plain

タイムスタンプを見るにちゃんと届いてそうです。ログを出してみます。Ruby なので p で引数の event を標準出力に出力してみるだけです。
ログ出力コードを入れてデプロイし、更にメンションしてみます。

同じようにログを確認し、さらにリンクを飛んで CloudWatch Logs までいきます。

f:id:ingage:20210628130034p:plain

へー、イベントの中身ってこうなってるのねーというのがよくわかります。

中で気になるものを赤枠で囲ってます。1つ目が token です。簡単なリクエストチェック(Slack からのリクエストであるということの検証)に使えます。
2つ目はエコーする内容です。ここをそのまま返せばエコーできそうだなとわかります(このときは「@echoapp hello2」と投稿してました)。

1つ目について、トークンの一致性を見るだけなら簡単なので実装しておきましょう。本当は署名チェックをするのがよいようです。

Verifying requests from Slack | Slack

Slack から送られるトークンについてはここにあります。

f:id:ingage:20210628131244p:plain

これを Lambda 側の環境変数にいれておき(画面省略)、コード上ではこう比較すればよいでしょう。

require 'json'

def lambda_handler(event:, context:)
    # p event
    req = JSON.parse(event['body'])
    if ENV['SLACK_VERIFICATION_TOKEN'] != req['token']
        return { statusCode: 401, body: "VERIFICATION TOKEN UNMATCHED" }
    end
    
    { statusCode: 200, body: 'done' }
end

さて、次はいよいよLambda側からエコーする部分を作りましょう。

続きはこちら。

ChatOps: SlackからLambdaに連携してみる(5) - インゲージ開発者ブログ

ChatOps: SlackからLambdaに連携してみる(3)

id:kizashi1122 です。

前回のエントリで発行した API Gatewayの URL をメモしておきます。

f:id:ingage:20210620201734p:plain

では Slack にアプリの設定をしましょう。

Slack 上でアプリを作る

こちらにアクセスします。

Slack API: Applications | Slack

基本設定

Create New App ボタンを押します。ダイアログで「From Scratch」と「From an App Manifest」を選択しろと言われるので、「From Scratch」を選択します。

アプリ名は「echoapp」としておきましょう。

f:id:ingage:20210626230506p:plain

Event Subscriptions

まずは有効にしましょう。

ここはSlack側で発生したイベントを設定する画面です。
「イベント」と「イベントを受け取るURL」を設定することができます。

f:id:ingage:20210626231553p:plain

Request URLの設定

では、前回のエントリで発行したURLを設定してみましょう。

f:id:ingage:20210628113002p:plain

まだ Lambda を設定してないのでエラーになります。 URLの Verificationとして Slack から飛んでくるリクエストに対して正しくレスポンスを返す必要があります。

https://api.slack.com/events/url_verification

要はリクエストで送られてきた challenge パラメータを、そのままレスポンスに返してくれということですね。 Lambda を以下のように編集してデプロイします。

require 'json'

def lambda_handler(event:, context:)
    # TODO implement
    # { statusCode: 200, body: JSON.generate('Hello from Lambda!') }
    req = JSON.parse(event['body']) # event['body'] は文字列
    { statusCode: 200, body: req['challenge'] }
end

これをデプロイ後に URL にある「Retry」をクリックすると、

f:id:ingage:20210628115420p:plain

「Verified」になりました。

イベントの設定

次にイベントを設定します。イベントは以下で一覧を確認することができます。

Events API types | Slack

いろんなイベント(作る側からするとトリガー)があることがわかります。今回は前回のエントリで書いたとおり

@echoapp hoge

とメンションされたときに発動するようにしたいので app_mention を選択することになります。

f:id:ingage:20210626231915p:plain

保存して終了です。

まだ Lambda のソースコードは URL 確認用のままです。 次はこれを編集します。

続きはこちら。

ChatOps: SlackからLambdaに連携してみる(4) - インゲージ開発者ブログ

ChatOps: SlackからLambdaに連携してみる(2)

id:kizashi1122 です。

f:id:ingage:20210620201734p:plain

最初にSlack のアプリの設定をしたくなりますが、Slack 側から叩かれる URL の準備を先にする必要がありますので、先に Lambda の設定をしましょう。

Lambda の設定

Function を作成します。名前は何でもいいですが、slack-echoapp にしましょう。 使用言語も何でもかまいません。ここでは Ruby を選択します。

f:id:ingage:20210627012315p:plain

次にトリガーを設定します。

f:id:ingage:20210627013014p:plain

API Gateway を選択し、以下のように選択して Add します。

f:id:ingage:20210627013731p:plain

API Gateway がトリガーとして設定されました。
API Gateway 側の設定をします。

f:id:ingage:20210629173602p:plain

API Gateway の設定

デフォルトではリクエストメソッドは ANY になっています。 実際は Event Subscription では POST のみを使います。 なので実際の運用では ANY ではなく POST に限定したほうがよいです。

今回は、ANYをクリックして「Integration Request」 の設定をします。 ここが、Lambda との連携を設定する箇所になります。が、そもそも Lambda から飛んできているので初期値としてちゃんと設定されています。

f:id:ingage:20210627014519p:plain

URL がすでに発行されているので確認しましょう。 左メニューの「Stages」をクリックして、Stages から「default」をクリックすると上部にURLが表示されます。

f:id:ingage:20210627015029p:plain

このURLを Slack のアプリに設定することになります。

では今回はこれで終わります。

次はSlack アプリの設定です。

続きはこちら。

ChatOps: SlackからLambdaに連携してみる(3) - インゲージ開発者ブログ

Sophos Anti-Virus for Linux Free Edition が終わったので、今更 ClamAV をインストールした

こんにちは、masm11 です。

f:id:masm11:20210623014007p:plain

普段、家庭内サーバに Sophos Anti-Virus for Linux Free Edition をインストールして使ってました。 が、ある日突然、こんな通知が来ました。

Sophos Anti-Virus for Linux Free Edition の製品サポートは終了しています。 アップデートは引き続き入手できますが、後日終了される予定です。 詳細は、次のサイトを参照してください。 https://community.sophos.com/free-antivirus-tools-for-desktops/b/blog/posts/retirement-of-sophos-anti-virus-for-linux-free-edition

仕方なくアンインストールし、代わりに今更 ClamAV をインストールしたので、メモ代わりに残しておきます。

SAV をアンインストール

まず service を止めましょう。

様子を確認して、

[root@mike bin]# systemctl list-unit-files | grep sav
sav-protect.service                                                           enabled         disabled
sav-rms.service                                                               disabled        disabled
sav-update.service                                                            enabled         disabled

ひとつずつ止めていきます。

[root@mike bin]# systemctl stop sav-update
[root@mike bin]# systemctl disable sav-update
Removed /etc/systemd/system/multi-user.target.wants/sav-update.service.
[root@mike bin]# systemctl stop sav-protect
[root@mike bin]# systemctl disable sav-protect
Removed /etc/systemd/system/multi-user.target.wants/sav-protect.service.

もう一度確認します。

[root@mike bin]# systemctl list-unit-files | grep sav
sav-protect.service                                                           disabled        disabled
sav-rms.service                                                               disabled        disabled
sav-update.service                                                            disabled        disabled

全部 disable になってますね。では reboot します。

[root@mike bin]# reboot

残り作業であるファイルの削除は script に任せます。

[root@mike ~]# /opt/sophos-av/uninstall.sh 
Uninstalling Sophos Anti-Virus.
Sophos Anti-Virus has been uninstalled.

ClamAV のインストール

私は Arch Linux なので、↓こちらの資料を参考にしながら進めました。

https://wiki.archlinux.jp/index.php/ClamAV

まずインストールします。

mike:~ % sudo pacman -S clamav

freshclam を起動します。

mike:~ % sudo systemctl start clamav-freshclam.service 
mike:~ % sudo systemctl enable clamav-freshclam.service
Created symlink /etc/systemd/system/multi-user.target.wants/clamav-freshclam.service → /usr/lib/systemd/system/clamav-freshclam.service.

ここで、clamav-freshclam.service の中身を見るとわかるのですが、 /etc/cron.d/ で設定してあると、それは cron で定期的に起動することが 期待されるので、boot 時に起動しないようになっています。 私の場合はその設定はしてないので、boot 時に起動します。

で、ここでデータベースのダウンロードが始まります。 少し時間がかかるので、時々

mike:~ % sudo ls -l /var/lib/clamav/

を実行しながら、書き込みが収まるまで待ちましょう。

終わったら scan してみます。ウイルスのいそうなディレクトリ目がけて、えいやっと。

mike:~ % clamscan --recursive --infected --max-scansize=4000M /home/backup/luna-asus/masm/Mail/spam
/home/backup/luna-asus/masm/Mail/spam/6369: Img.Dropper.PhishingLure-6329856-0 FOUND
/home/backup/luna-asus/masm/Mail/spam/6759: Xls.Dropper.Generic-6595971-0 FOUND
/home/backup/luna-asus/masm/Mail/spam/6907: Xls.Dropper.Generic-6595971-0 FOUND
/home/backup/luna-asus/masm/Mail/spam/6370: Img.Dropper.PhishingLure-6329856-0 FOUND
/home/backup/luna-asus/masm/Mail/spam/6131: Xls.Dropper.Generic-6595971-0 FOUND

おぉ、ちゃんと検出してくれました。

では cron に仕掛けてしましょう。

mike:~% sudo crontab -e
0 2 1 * * /usr/bin/clamscan --recursive --infected --max-scansize=4000M --exclude-dir='^/sys|^/dev|^/proc' / | syslog -t clamscan

毎月1日の午前2時からスキャンを開始するようにしました。 この家庭内サーバはアクティブに使ってるサーバではないので、 この程度の頻度で定期的にスキャンするだけで十分です。 また、realtime scan とかやる気は全くありません。

ただ、本当はメール通知くらいはしたいんですけどね。 SAV の時はやってました (そのチャンネルを使ってサービス終了通知が来たわけです)。

まとめ

さすが ClamAV は簡単でいいですね。

スキャンにかかる時間にも注目したいです。 SAV の時はファイルシステム全体のチェックに丸2日程かかってました。 毎月3日くらいになると通知メールが来てたわけですね。 ClamAV でどのくらいかかるのでしょうね。

インゲージではエンジニアを募集しています。以下のページからお願いします!

https://ingage.co.jp/recruit/