(作業中)PostgreSQL その61 shared_buffersの算定その2

PostgreSQL その59 shared_buffersの算定　その1のつづき

インデックス付きテーブルを検索する場合のキャッシュ使用量調査

インデックスを貼ったカラムの値をキーに検索する場合、カラムの値がユニークなときとユニークでないときでキャッシュ使用量に違いがあるか調べる。

※カラムの値がユニークでなくてもインデックスを作成することはできる

１．インデックスを貼ったカラムの値がユニークな場合

【検証0】

2500万行（テーブルサイズ1.5GB）のテーブルのへのselect結果が100行ヒットする場合

・where句で絞り込むカラムにインデックスが張られていないとき

→初回検索後、kernelキャッシュ使用量が1.5GB増大した

・where句で絞り込むカラムにインデックスが張られてたとき

→初回検索後、kernelキャッシュ使用量が数百KB増大した

【結果0】

・インデックスが張られてない場合、ほぼ2500万回ディスクアクセスされるためテーブル全体がキャッシュに乗ると考えらる。

・インデックスが張られてた場合、平均log 2500万 ×100回しかディスクアクセスされないために、キャッシュに乗る量も少なくなる。

【検証1】

【検証0】の追検証。

別のテーブル（PostgreSQL その44 性能評価とチューニング検証用データベース作成　その3で作成した「table0i」・・・インデックス有り、1000万行、テーブルサイズ=781MB、インデックスサイズ=429MB）で検索結果が約100万行になるクエリを実行して【検証0】を追検証する

【懸念点1】

・1000万行のテーブルのうち10％に当たる100万行がヒットする検索の場合、where句で指定するカラムにユニークなインデックスを貼ってても、実行計画で総当たりスキャンが選択されて、結果的にインデックス無しの「table0」と同じく、キャッシュにテーブル全行書き込みが為されてしまわないだろうか？

・職場の有識者からの話だと、PostgreSQLは共有メモリに乗せるデータが満タンになったときに優先して残すデータを取捨択一するアルゴリズムになっているらしい。

例えば、 joinしたデータ＞インデックス＞テーブルのような感じになってるらしい（うろおぼえ）

・レスポンス時間をbashから実行してbashに返ってきた時間をtimeコマンドで相対比較しているが、厳密に測るにはpg_stat_statusを使った方がよい（あくまで相対比較だし・・・）

【検証1-1】　

一度に全テーブルの10％（100万行）がヒットする検索の場合

せ70> systemctl restart postgresql-10

せ70> echo 3 > /proc/sys/vm/drop_caches
せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 537M 2.0G 51M 222M 2.0G
Swap: 2.0G 0B 2.0G

ちなみに、インデックスを張ってない「shohin_name」カラムで検索すると、「table0」と同じようにsequential scan（非インデックススキャン）になり、下記のようにレスポンスに14秒かかった。

せ70> time PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c "select count(*) from punisuke.table0i where shohin_name='商品8'"
count
--------
999745
(1 row)

real 0m14.089s
user 0m0.008s
sys 0m0.026s
せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 539M 1.3G 53M 993M 2.0G ←kernel cacheに一気に771MB乗った
Swap: 2.0G 0B 2.0G

・kernel cacheを一回フラッシュ

せ70> echo 3 > /proc/sys/vm/drop_caches

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 537M 2.0G 53M 226M 2.0G
Swap: 2.0G 0B 2.0G

・100万行がヒットする検索をインデックス行（uriage_no）に対して実行

せ70> time PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c "select count(*) from punisuke.table0i where uriage_no >= 6000000 and uriage
_no <= 6999999;"
count
---------
1000000
(1 row)

real 0m2.172s　　　　　←約2.2秒
user 0m0.002s
sys 0m0.023s

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 539M 1.8G 149M 446M 1.9G
Swap: 2.0G 0B 2.0G

→一発でkernel cacheに223MB乗り、共有メモリに96MB乗った

・もう一度同じ検索範囲で実行

→レスポンス時間は0m0.163s（約0.2秒）に改善。kernel cache使用量(buff/cache)と共有メモリ使用量(shared)は変化なし

・今度は検索範囲を変えて実行

せ70> time PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c "select count(*) from punisuke.table0i where uriage_no >= 8000000 and uriage
_no <= 8999999;"
count
---------
1000000
(1 row)

real 0m2.554s
user 0m0.006s
sys 0m0.005s
せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 539M 1.6G 245M 650M 1.8G
Swap: 2.0G 0B 2.0G

→一発でkernel cacheに224MB乗り、共有メモリに96MB乗った

cacheに乗った量が前回とほぼ同じ！

・もう一度同じ検索範囲で実行

→レスポンス時間は0m0.163s（約0.2秒）に改善。kernel cache使用量(buff/cache)と共有メモリ使用量(shared)は変化なし

改善されたレスポンス時間とそれ以降cache使用量に増減が起こらないことも完全に一致！

・もう一度同じ検索範囲を残りのuriage_no=1～999999、1000000～1999999、2000000～2999999、3000000～3999999、4000000～4999999、5000000～5999999、7000000～7999999、9000000～9999999で実行

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 538M 128M 1.0G 2.1G 1.1G
Swap: 2.0G 0B 2.0G

→kernel cacheに1450MB乗り、共有メモリが1GB（shared_buffers値）に達した

計算では、kernel cacheに8×224MB=1792MB乗ると思われたが、メモリ管理してるkernelの判断でfree領域に128MBが確保されてpostgresqlデータはすべてkernel cacheに乗り切らなかったと思われる。

【検証1-2】

一度に全テーブルの0.01％未満（1000行未満）がヒットする検索の場合

下記のように、「table0i」に張られているもう一つのインデックス列「comment」には、「11111a1」のようなランダムな数字と文字列が入っている。

testdb=# select * from punisuke.table0i where comment='12345a1';
uriage_no | uriage_date | kokyaku_name | shohin_name | price | number | comment
---------+------------------+------------+-----------+-----+------+---------
6462 | 1991-01-29 00:00:00 | 顧客1 | 商品8 | 800 | 71 | 12345a1
15247 | 1991-02-07 00:00:00 | 顧客10 | 商品1 | 100 | 88 | 12345a1
133244 | 1991-06-05 00:00:00 | 顧客1 | 商品6 | 600 | 23 | 12345a1
---(略)---

9708850 | 2017-08-22 00:00:00 | 顧客4 | 商品3 | 300 | 55 | 12345a1
9936919 | 2018-04-07 00:00:00 | 顧客8 | 商品5 | 500 | 88 | 12345a1
(62 rows)

・postgresqlの共有メモリとkernel cacheをフラッシュする

せ70> systemctl restart postgresql-10
せ70> echo 3 > /proc/sys/vm/drop_caches
・クエリ実行前の共有メモリとkernel cache使用量

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 532M 2.0G 51M 224M 2.0G
Swap: 2.0G 0B 2.0G

・一度に全テーブルの0.00062％（62行）がヒットする検索を実行

せ70> time PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c "select count(*) from punisuke.table0i where comment='12345a1';"
count
-------
62
(1 row)

real 0m1.125s ←レスポンスは約1.1秒
user 0m0.009s
sys 0m0.030s

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 532M 2.0G 53M 242M 2.0G
Swap: 2.0G 0B 2.0G

→一発でkernel cacheに18MB乗り、共有メモリに2MB乗った

・もう一度同じ検索をする

→レスポンス時間は0m0.015sに改善。kernel cache使用量(buff/cache)は2MB増大し、共有メモリ使用量(shared)は変化なし

※kernel cacheの増大は値が小さいのでもしかしたら別の要因の可能性あり

【わかったこと】

・インデックス（テーブルも？）がキャッシュに乗った場合と乗ってない場合で応答時間が約100倍違った。

・インデックススキャンの方がシーケンシャルスキャンよりキャッシュの使用量が少ないが、それでも長期的には少なくともインデックスがすべてキャッシュに乗り切ってパフォーマンスが最大限発揮されると思われる。

２．インデックスを貼ったカラムの値がユニークじゃない場合

【準備】

[テーブル作成]

インデックスが張られたユニークなカラム「id1」とユニークでないカラム「id2」をもつテーブル「table1」を作成する。

testdb=> create table table1 (id1 serial,id2 int,comment text);
CREATE TABLE

[データ挿入]

id1カラムはユニークで、id2カラムは例えば下記のようにユニークでないようにデータを挿入する

testdb=> insert into table1(id2,comment) values
(1,'hoge'),
(1,'fuge'),
(1,'piyo'),
(1,'puni'),
(1,'pyororin'),
(1,'oppekepe');

INSERT 0 6

testdb=> insert into table1(id2,comment) values
(2,'hoge'),
(2,'fuge'),
(2,'piyo'),
(2,'puni'),
(2,'pyororin'),
(2,'oppekepe');
INSERT 0 6
testdb=> select * from table1;
id1 | id2 | comment
---+---+----------
1 | 1 | hoge
2 | 1 | fuge
3 | 1 | piyo
4 | 1 | puni
5 | 1 | pyororin
6 | 1 | oppekepe
7 | 2 | hoge
8 | 2 | fuge
9 | 2 | piyo
10 | 2 | puni
11 | 2 | pyororin
12 | 2 | oppekepe
(12 rows)

・スクリプトでこれに600万行挿入する　（4時間かかる？→3時間45分でした）

せ70> cat /tmp/20180812/insert.sh
#!/bin/bash
for ((i=1;i<=1000000;i++))
do
PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c \
"insert into punisuke.table1 (id2,comment) values
(${i},'hoge'),
(${i},'fuge'),
(${i},'piyo'),
(${i},'puni'),
(${i},'pyororin'),
(${i},'oppekepe');" > /dev/null
done

【検証2】

下記の①、②の検索をした場合のキャッシュ使用量とレスポンス時間を比較する

①ユニークでないカラム「id2」をキーにして「comment」を検索

select comment from table1 where id2=n;

→6レコードヒットする

②ユニークなカラム「id1」をキーにして「comment」を検索

ただし、必ず6レコードヒットするように下記のように検索する

select comment from table1 where id1=6m+1 or id1=6m+2 or id1=6m+3 or id1=6m+4 or id1=6m+6;

→6レコードヒットする

【結果2】

せ70> systemctl restart postgresql-10

せ70> echo 3 > /proc/sys/vm/drop_caches
せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 527M 2.0G 51M 226M 2.0G
Swap: 2.0G 0B 2.0G

・ユニークでないインデックスで検索

せ70> time PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c "select * from punisuke.table1 where id2=500000;"
id1 | id2 | comment
---------+--------+----------
3000000 | 500000 | oppekepe
2999999 | 500000 | pyororin
2999998 | 500000 | puni
2999997 | 500000 | piyo
2999996 | 500000 | fuge
2999995 | 500000 | hoge
(6 rows)

real 0m1.206s ←レスポンスは約1.2秒
user 0m0.001s
sys 0m0.046s
せ70> free -h
　　total used free shared buff/cache available
Mem: 2.8G 526M 2.0G 52M 240M 2.0G ←kernel cacheに約14MB乗った
Swap: 2.0G 0B 2.0G
せ70> time PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c "select * from punisuke.table1 where id2=500000;"
---（略）---
real 0m0.042s ←レスポンスは約0.04秒
user 0m0.004s
sys 0m0.006s
せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 526M 2.0G 52M 241M 2.0G
Swap: 2.0G 0B 2.0G

・ユニークなインデックスで検索

せ70> systemctl restart postgresql-10
せ70> echo 3 > /proc/sys/vm/drop_caches
せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 526M 2.0G 51M 224M 2.0G
Swap: 2.0G 0B 2.0G
せ70> time PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c "select * from punisuke.table1 where id1=2999995 or id1=2999996 or id1=2999997 or id1=2999998 or id1=2999999 or id1=3000000;"
id1 | id2 | comment
---------+--------+----------
2999995 | 500000 | hoge
2999996 | 500000 | fuge
2999997 | 500000 | piyo
2999998 | 500000 | puni
2999999 | 500000 | pyororin
3000000 | 500000 | oppekepe
(6 rows)

real 0m0.610s ←レスポンスは約0.6秒
user 0m0.004s
sys 0m0.021s
せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 527M 2.0G 52M 244M 2.0G ←kernel cacheに約20MB乗った
Swap: 2.0G 0B 2.0G
せ70> time PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c "select * from punisuke.table1 where id1=2999995 or id1=2999996 or id1=2999997 or id1=2999998 or id1=2999999 or id1=3000000;"
---（略）---
real 0m0.017s ←レスポンスは約0.02秒
user 0m0.001s
sys 0m0.010s
せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 527M 2.0G 52M 244M 2.0G
Swap: 2.0G 0B 2.0G

・ユニークでないインデックスで検索を1万回実行

せ70> for((i=10000;i<=20000;i++));do PGPASSWORD=postgres /opt/PostgreSQL/10/bin/psql -U postgres testdb -c "select * from punisuke.table1 where id2=1${i};";done

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 526M 2.0G 56M 262M 2.0G ←kernel cacheに約18MB、共有メモリに４MB乗った
Swap: 2.0G 0B 2.0G

【わかったこと】

ユニークなカラムをキーにして検索した場合とユニークでないカラムをキーにして検索した場合では、

・キャッシュ使用量はユニークな方がちょっとだけ使用量が大きかった。

・レスポンス時間はユニークな方がちょっとだけ速かった

・初回検索でいきなり10～20MBキャッシュに乗るが、2回目以降はキャッシュの乗り方が少なくなる（1万回検索で20MBとか）

※試行回数一回だけだし、あいまいな表現だけど極端な違いが出なかったのでこれ以上深追いしないんご

【検証3】

insert文で挿入されたデータはinsertしただけでPostgreSQLのshared_buffersに乗るのか？

それともselect などでreadされるまでshared_buffersに乗らないのか？

・20万レコード挿入した時点の共有メモリ使用量

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 562M 1.6G 77M 673M 2.0G
Swap: 2.0G 0B 2.0G

・40万レコード挿入した時点の共有メモリ使用量

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 568M 1.5G 88M 727M 1.9G
Swap: 2.0G 0B 2.0G

・100万レコード挿入した時点の共有メモリ使用量

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 564M 1.4G 142M 858M 1.9G
Swap: 2.0G 0B 2.0G

・420万レコード挿入した時点の共有メモリ使用量

せ70> free -h
total used free shared buff/cache available
Mem: 2.8G 592M 621M 424M 1.6G 1.6G　　　←shared_buffersの512MBには一応達してないけど
Swap: 2.0G 0B 2.0G

「table1」テーブルの場合、20万レコードinsertするごとに共有メモリに約18MB乗った。

【分かったこと】

insertしただけでデータがPostgreSQLのshared_buffersに乗る

■shared_buffers算定

よく見かける資料では、PostgreSQLのshared_buffersの値は、おおむねシステムメモリの1/4と書かれているが、これはおそらくすべてのケースには当てはまらない。

例えば、下記のような場合。

「PostgreSQLデータ以外のファイルのread/write量が多いため、kernel cacheからPostgreSQLデータがすぐに追い出されてしまうために、kernel cacheのヒット率が当てにならないケース。この場合はPostgreSQLのキャッシュ場所はshared_buffersだけになる。

ホストにPostgreSQLだけインストールしているような場合にはこのようなケースに陥ることはない」

たとえば、PostgreSQLデータ（テーブル＋インデックス）が１GB程度なら、shared_buffers値を同程度の1GBにしておけば、PostgreSQLデータはすべてshared_buffersに乗り切る。

【課題】　

■お題

テーブルにログのようなデータも持たせていて、これが大量に挿入されるために、ログ以外の高速レスポンスが要求されるデータがキャッシュから追い出されるケース。

kernel cacheは当てにならない場合、PostgreSQLの共有メモリからもデータが追い出されてキャッシュのヒット率が低減する可能性がある。

上記の【検証3】で調べた通りログデータはinsert文で挿入されただけでPostgreSQLの共有メモリに乗る。

■検討項目

・大量insertされるログデータがinsertされることによって実際にどのくらい他のデータをキャッシュから追い出すかシミュレーションして確認する

・ログデータがテーブル内に大量に登録された状態でヒット数の多いクエリを実行した場合、どのくらいキャッシュに乗って、他のデータをキャッシュから追い出すかシミュレーションして確認する

・クエリの応答速度が求められるデータ（テーブル、インデックス）を優先的にキャッシュに乗せたり、追い出されないようにする方法はないか？

・クエリの応答速度が求められるデータとそうでないデータを別のクラスタにすれば共有メモリも別々になって影響が及ばなくなる

(作業中)PostgreSQL その61 shared_buffersの算定 その2

インデックス付きテーブルを検索する場合のキャッシュ使用量調査

１．インデックスを貼ったカラムの値がユニークな場合

【検証0】

【結果0】

【検証1】

【懸念点1】

【検証1-1】

・100万行がヒットする検索をインデックス行（uriage_no）に対して実行

・もう一度同じ検索範囲で実行

・今度は検索範囲を変えて実行

・もう一度同じ検索範囲で実行

・もう一度同じ検索範囲を残りのuriage_no=1～999999、1000000～1999999、2000000～2999999、3000000～3999999、4000000～4999999、5000000～5999999、7000000～7999999、9000000～9999999で実行

【検証1-2】

・もう一度同じ検索をする

【わかったこと】

２．インデックスを貼ったカラムの値がユニークじゃない場合

【準備】

[テーブル作成]

[データ挿入]

【検証2】

【結果2】

・ユニークでないインデックスで検索

・ユニークなインデックスで検索

・ユニークでないインデックスで検索を1万回実行

【わかったこと】

【検証3】

・20万レコード挿入した時点の共有メモリ使用量

・40万レコード挿入した時点の共有メモリ使用量

・100万レコード挿入した時点の共有メモリ使用量

・420万レコード挿入した時点の共有メモリ使用量

【分かったこと】

■shared_buffers算定

【課題】

■お題

■検討項目

(作業中)PostgreSQL その61 shared_buffersの算定その2

【検証1-1】　

【課題】　