INSERT, SELECT のパフォーマンスチェック
- せっかく Go+postgres の環境を構築したので、耐久度チェック的なものをやってみた
- DBのインデックスとは
- どれくらい違うのか
- 実行計画とは
goでのDBの操作方法
- db をまず open する
- グローバルぽい変数に値を保持しておくことで、毎回 sql.Open を呼ばなくても良いようにした
- 下のように、dbパッケージを作り、その中に初期化関数を作る
package db
import (
"context"
"database/sql"
"os"
_ "github.com/lib/pq" // FOR postgres driver
)
var (
// DriverName = os.Getenv("DRIVERNAME")
DriverName = "postgres"
DbName = os.Getenv("POSTGRES_DB")
UserInfo = "host=postgres port=5432 user=" + os.Getenv("PGUSER") + " password=" + os.Getenv("POSTGRES_PASSWORD") + " dbname=" + DbName + " sslmode=disable"
DB *sql.DB
Ctx context.Context
)
func DbInit() *sql.DB {
con := Connect()
Ctx = context.Background()
return con
}
func Connect() *sql.DB {
connection, _ := sql.Open(DriverName, UserInfo)
DB = connection
return connection
}
実際にデータベースにアクセスしてクエリを送るには、
_, qerr := db.DB.Exec(str_query)
row, qerr := db.DB.Query("SELECT email FROM users WHERE user_id=" + strconv.Itoa(v) + ";")
などのように、直接クエリ文を投げ込む方法で（とりあえずは）いける
insert におけるバルクインサートの性能
そもそもバルクインサートとは？
以下では次のような定義で作られた users テーブルを想定している
CREATE TABLE IF NOT EXISTS users(
user_id serial PRIMARY KEY,
name VARCHAR (50) NOT NULL,
password VARCHAR (100) NOT NULL,
email VARCHAR (300) UNIQUE NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
このとき、通常のINSERT文は次のようにかけている
INSERT INTO users (name, password, email) VALUES ('kokoichi', 'hogehoge', 'test');
何回も同じテーブルにデータを挿入したい！と思ったとする
素直に考えれば上のを何行もコピーすれば良いが、次のようなバルク（bulk）インサートという方法の方が早いらしい
INSERT INTO users (name, password, email) VALUES
('kokoichi', 'hogehoge', 'test'),
('kokoichi1', 'hogehoge', 'test1'),
('kokoichi2', 'hogehoge', 'test2');
複数行実際に繰り返してその違いを調べる
- 今回行った方法
- クエリ文が記述されたstringの文字列をgoの中で定義
- 後述するが、このstring文が巨大になってまった
- クエリ文が記述されたstringの文字列をgoの中で定義
回数
- 10回から10万回まで10n
結果
| num | make query | normal | bulk |
|---|---|---|---|
| 10 | 265.7µs | 2.6124ms | 13.3443ms |
| 100 | 1.2496ms | 15.0987ms | 11.0266ms |
| 1000 | 33.3354ms | 44.0621ms | 22.8602ms |
| 1_0000 | 1.7961325s | 429.8875ms | 134.0671ms |
| 10_0000 | 1m33.4020931s | 4.253422s | 1.611765s |
| 100_0000 | 1h46m41.2067612s | 50.4609002s | failed(->err) |
err: pq: the database system is in recovery mode
最後のクエリでは、ノーマルモードのインサート文（の書かれた）stringで88000000byte、バルクモードのインサート文（の書かれた）stringで40000048byteもの大きさがあった。
ここの読み込みで死ぬほど時間かかっており『メモリ不足によるスワップが起こったのか』とか思ったけど、その辺りわからないので詳しい人教えて欲しいです。。
- 結論
- まあ、数倍は早い
- ORM を使った時にどうやってバルクインサートを実現するか気になる
SELECT によるインデックスの効能
- 「PKにはインデックスが貼られているため検索が高速」と聞いたので、その意味を調べてみた
user_idでの検索と email での検索
上のテーブルにおいて、PKである user_id から email を検索する『id -> email』と、email から user_id を検索する『email -> id』で時間がどのように変わるか調べた
なお、実際に検索テーブルは上でのインサートチェックを用いて2万件のデータを挿入してある
1000件の検索結果
選ぶ位置によって時間が変わると思ったので、ランダムで選んだ1000件に対して先の2つの検索を行なった
----1000---- ----- TIME FOR 1000 TIMES email SELECT from id ----- 513.773481ms ----- TIME FOR 1000 TIMES id SELECT from email ----- 425.235829m
あれ？？どちらも変わらない？？
原因
- どうやら UK もインデックスが貼られるみたい
- 下のコマンドを psql で実行することで確認できる
explain SELECT email FROM users WHERE user_id=1; > QUERY PLAN ------------------------------------------------------------------------- Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=11) Index Cond: (user_id = 1) (2 rows)
explain SELECT user_id FROM users WHERE email='hoge'; > QUERY PLAN ----------------------------------------------------------------------------- Index Scan using users_email_key on users (cost=0.29..8.31 rows=1 width=4) Index Cond: ((email)::text = 'hoge'::text) (2 rows)
実行計画がどちらも Index Scan になっている
email から UNIQUE 制約を外して同じことを繰り返す
----1000---- ----- TIME FOR 1000 TIMES email SELECT from id ----- 291.46202ms ----- TIME FOR 1000 TIMES id SELECT from email ----- 2.253570096s
10倍ほどの差がつくことが確認できた
一応実行計画も explain で確認しておく
explain SELECT user_id FROM users WHERE email='hoge'; > QUERY PLAN ------------------------------------------------------- Seq Scan on users (cost=0.00..457.00 rows=1 width=4) Filter: ((email)::text = 'hoge'::text) (2 rows)
