インスタンスの作成
パフォーマンス関連の設定値
- クラスタ構成オプション: Aurora I/O-Optimized
- r6i.large(ARMプロセッサ, 2vCPU, 16GBメモリ, NW帯域5Gbps弱(4500とか))
- 選べるのは、メモリ最適化クラスorバースト最適化クラス
- RDS Proxy利用しない
- 暗号を有効化: ON(パフォーマンスに影響あるか不明)
パフォーマンスモニタリング関連
- Performance Insights: ON
- DevOps Guru: ON
- 「DB インスタンスのパフォーマンスの異常を自動的に検出し、レコメンデーションを提供します。」
- 拡張モニタリング: ON
- 詳細度: 1秒(デフォルト60秒)
他
- Aurora(Postgres)15.3(2023.7.13時点最新)
- マルチAZ配置: Auroraレプリカを作成しない
- EC2コンピューティングリソースに接続: ON, 既存のEC2インスタンスを選択
- ログのエクスポート: PostgreSQLログ: ON
- 「CloudWatch Logs に発行するログタイプを選択します」
テーブル作成
T1テーブルを作成
set search_path to "PFS";
create table t1( c1 integer ) ;1億件投入
参考サイト
INSERT INTO t1( c1 ) SELECT i FROM generate_series(1, 100000000) as i ;5分弱で返ってきた
INSERT 0 100000000
Query returned successfully in 4 min 51 secs.パフォーマンスモニタリング系の見え方
Performance Insights
ディメンションタブ
CPU: 35%
IO:AuroraStorageLogAllocate: 22%
合計すると57%
CPU35%ってのは、これ待機イベントだから、CPU待ちのことかな..? IO待ちが表示されてないけど、IO待ちないとかありえないよね..? CPU待ちがあるならIO待ちがあるよね?? ない?? 遅延書き込みとか??
詳細が良く分からない..
メトリクス – 新規 タブ
画面左上～中央
画面右上～中央
画面下側
- メトリクス一覧
CPU 使用率 (%)
空きメモリ (バイト)
FreeableMemoryIO レイテンシー (ミリ秒)
IO オペレーション (/秒)
IO スループット (バイト/秒)
IO ディスクキューの深さ (リクエスト)
バキューム: 使用されている最大のトランザクション ID(トランザクション)
トランザクションの最大アイドル時間 (秒)
ネットワークスループット (バイト/秒)
接続の使用率 (接続)
セッション (セッション)
クエリ (/秒)
タプル: DML(タプル/秒)
タプル: DML(タプル/秒)
トランザクション (1 秒あたりのトランザクション)
進行中のトランザクション (1 秒あたりのトランザクション)
IO キャッシュとディスク読み取り (ブロック/秒)
デッドロック (デッドロック/分)
日本語表記なのと、後述の、クラスタ/ライターインスタンスのメトリクス数と比べるとかなり絞られてる感じ。必要そうなものは人とおりある感じはするけど。
クラスター/ライターインスタンスを選択して詳細画面で、モニタリングタブ見る
クラスター/ライターインスタンスでそれぞれメトリクス違うな..
- クラスタ
AuroraEstimatedSharedMemoryBytes
BackupRetentionPeriodStorageUsed
BufferCacheHitRatio
CommitLatency
CommitThroughput
CPUUtilization
Deadlocks
DiskQueueDepth
EBSByteBalance%
EBSIOBalance%
EngineUptime
FreeableMemory
FreeLocalStorage
MaximumUsedTransactionIDs
NetworkReceiveThroughput
NetworkThroughput
NetworkTransmitThroughput
OldestReplicationSlotLag
RDSToAuroraPostgreSQLReplicaLag
ReadIOPS
ReadLatency
ReadThroughput
ReplicationSlotDiskUsage
StorageNetworkReceiveThroughput
StorageNetworkThroughput
StorageNetworkTransmitThroughput
SwapUsage
TotalBackupStorageBilled
TransactionLogsDiskUsage
VolumeBytesUsed
VolumeReadIOPs
VolumeWriteIOPs
WriteIOPS
WriteLatency
WriteThroughput
- ライターインスタンス
AuroraEstimatedSharedMemoryBytes
BufferCacheHitRatio
CommitLatency
CommitThroughput
CPUUtilization
DatabaseConnections
DBLoad
DBLoadCPU
DBLoadNonCPU
Deadlocks
DiskQueueDepth
EBSByteBalance%
EBSIOBalance%
EngineUptime
FreeableMemory
FreeLocalStorage
MaximumUsedTransactionIDs
NetworkReceiveThroughput
NetworkThroughput
NetworkTransmitThroughput
OldestReplicationSlotLag
RDSToAuroraPostgreSQLReplicaLag
ReadIOPS
ReadLatency
ReadThroughput
ReplicationSlotDiskUsage
StorageNetworkReceiveThroughput
StorageNetworkThroughput
StorageNetworkTransmitThroughput
SwapUsage
TransactionLogsDiskUsage
WriteIOPS
WriteLatency
WriteThroughput
diffってみる
左がクラスタ、右がライターインスタンス
ウィンドウの横幅が足りないと縦に文字が重複したような表示になるので、画像+アコーディオン(テキストで見たい人向け)で貼ります
/tmp \ diff -y cluster instance
#cluster | #instance
AuroraEstimatedSharedMemoryBytes AuroraEstimatedSharedMemoryBytes
BackupRetentionPeriodStorageUsed <
BufferCacheHitRatio BufferCacheHitRatio
CommitLatency CommitLatency
CommitThroughput CommitThroughput
CPUUtilization CPUUtilization
> DatabaseConnections
> DBLoad
> DBLoadCPU
> DBLoadNonCPU
Deadlocks Deadlocks
DiskQueueDepth DiskQueueDepth
EBSByteBalance% EBSByteBalance%
EBSIOBalance% EBSIOBalance%
EngineUptime EngineUptime
FreeableMemory FreeableMemory
FreeLocalStorage FreeLocalStorage
MaximumUsedTransactionIDs MaximumUsedTransactionIDs
NetworkReceiveThroughput NetworkReceiveThroughput
NetworkThroughput NetworkThroughput
NetworkTransmitThroughput NetworkTransmitThroughput
OldestReplicationSlotLag OldestReplicationSlotLag
RDSToAuroraPostgreSQLReplicaLag RDSToAuroraPostgreSQLReplicaLag
ReadIOPS ReadIOPS
ReadLatency ReadLatency
ReadThroughput ReadThroughput
ReplicationSlotDiskUsage ReplicationSlotDiskUsage
StorageNetworkReceiveThroughput StorageNetworkReceiveThroughput
StorageNetworkThroughput StorageNetworkThroughput
StorageNetworkTransmitThroughput StorageNetworkTransmitThroughput
SwapUsage SwapUsage
TotalBackupStorageBilled <
TransactionLogsDiskUsage TransactionLogsDiskUsage
VolumeBytesUsed <
VolumeReadIOPs <
VolumeWriteIOPs <
WriteIOPS WriteIOPS
WriteLatency WriteLatency
WriteThroughput WriteThroughputDatabaseCOnnectionsとかDBLoad/DBLoadCPU/DBLoadNonCPUを見るときはライターインスタンスのメトリクスを見ると。ふむふむ。
あとCloudWatchでもそう思うときがあるんだけど気になるのが、メトリクスの間隔を丸めたとき(1分とか10分とか1時間とか)に、見える値って変わるんじゃ??ってこと。この機に見てみる。
上記は1秒おきのデータで見てるので、これを1時間に変えてみる。
Performance Insights(の、メトリクス – 新規タブ)には、間隔を変えるオプションはなかった。範囲を6分間で絞ってるけど、1分間ごとに棒グラフの棒が60個くらいあるように見えるので、1秒おきのデータ表示になってそう(DBの設定で詳細モニタリングの間隔を1秒にしてある影響かな??)。
クラスタ/ライターインスタンスのモニタリングタブを、1秒置きから広げてみる(テーブルのデータ投入時間が5分弱だったので1分～5分に伸ばすくらいが限界)
- クラスタ
特にCPU使用率と、ディスクキューを見る
1秒間隔のCPU使用率: 80%でしばらく継続
1秒間隔のディスクキュー: max900くらい
1分間隔の場合
1分間隔のCPU使用率: 丸めこみ差異なし
1分間隔のディスクキュー: 丸めこみ差異なし
なぜか1分間隔にすると5:54時点のデータも見えたけど、丸めこみはなかった。
5分間隔にすると、たぶん範囲で6分間を指定してるせいで、データが取れない。もう少し前後の範囲を広げて、5分(CloudWatchのデフォ)とか1時間とかで見てみる
範囲の前後を5分広げる (5:54-6:00だったのを、5:49-6:05に変更)
5分間隔
CPU: 丸まってない
ディスクキュー: 4分の1くらいに丸まった
やっぱこうなるのか.. これは要注意。
範囲を前後30分に広げて1時間間隔のデータを見てみる
範囲を、元々5:54-6:00なので、切り良く5:30-6:30を設定
1時間間隔はなぜか全然表示されない(範囲を2時間とかにしてもなぜか表示されない)
15分間隔だと表示されるので15分間隔を見る
CPU: 1秒間隔のときより半分に丸め込み
ディスクキュー: 1秒間隔のときより10分の1に丸め込み
うーん、これだとかなり判断変わるよなあ..
ちなみに、時間の範囲指定、は全然影響なかった。負荷かけてる5分間でも、前後1時間とか間延びしてても、グラフの横幅の傾斜が変わるくらいで、値の丸め込みはなかった。
なので、start/endの範囲指定は負荷かけてる時間帯が含まれてるなら良くて、でもそのデータの間隔は1秒おきじゃないと正しいデータが取れない。
参考) 1時間範囲での1秒間隔データ
CPU
ディスクキュー
1秒->5分の間を刻んで見てみる
5秒(今回はキャプチャのしやすさの都合で間にDBLoadCPUが見えてます)
丸め込みなし
10秒: 丸め込みなし
30秒: 丸め込みなし
1分(画面表示は1 minute)
丸め込みなし
5分
CPU丸め込みなし、ディスクキュー 3分の1前後に丸め込み
15分
CPU半分、ディスクキュー10分の1に丸め込み
1時間(表示は1 hour)
データ取れず
1hour、直近3時間にしたら取れた。でも意味はないデータ
つまり、CloudWatch見るときのデータ間隔は、最低でも1分にしないとちゃんとデータ見れないよと。デフォ5分だとディスクキューが小さい値が表示されてるので、1分に変更してから見たり、データもらう必要があるよと。
ディスクだと、性能限界かとかベースライン/バースト性能のスペックと比較してどうかとか、数値で見るので、正しい値取れないと、ただしい対策取れなくなる。
CloudWatchのデータ間隔での丸め込みが分かってすっきりした
1億件のテーブルについて、データ確認
ここでデータといってるのはレコードの内容ではなく(レコードは上記で一括で登録したものなので)、占有してるサイズとかそういう情報です
pgAdminのstatistics
| 件数 | 100000000(1億件) |
| Live tuples | 100003053 |
| Heap blocks read | 131074 |
| Heap blocks hit | 102117915 |
| Last autovacuum | 2023-07-13 05:59:40.024014+00 |
| Last autoanalyze | 2023-07-13 05:59:40.95705+00 |
| Table size | 3458 MB |
全体は以下。データがない項目多し
1億件を、1カラム(平均バイト長は5くらいだろうか..)で投入すると、3458MB(大体3.37GB)になる。
そして、その作成には4分51秒かかる。
CloudWatch(クラスタ/ライターインスタンスのメトリクスタブ), Performance Insightsより、大体の待機時間は「CPU: 35%, IO:AuroraStorageLogAllocate: 22%」で、他の待機イベントと合わせてざっくり50%なので、残りの50%にIO待機が含まれてる感じか??(いや、IO待機が含まれてるならそう出るような。IO待機ないのか??でもデータ投入でIO待機なしでCPUフルに使えてるとかありえる??遅延書き込み??)
4分51秒 = 291s
3458MB / 291s = 約11.88MB/s
1億 / 291s =
秒間のレコード生成
レコード数: 343,642行
(正確: 343642.6116838488)
サイズ: 11MB/s
サイズでいうと11MB/sってちょっと少なく感じるけど、秒間34万件書き込めてると思うと早いか..
Aurora Postgresqlインスタンス作成時にEBSの選択なかったけど、ストレージタイプとIOPS/スループットってどのくらいなんだろ??
大量レコード投入時のIOPS見てみる
データ投入したの、7/13 14:54-15:00(UTC: 5:54-6:00)なのだが、今モニタリングタブ見ると、1秒間隔のデータが見れなくなっていた.. 1秒間隔のデータ見れる期限は短いのか??
5秒、10秒、30秒も表示されず、1分間隔は表示された。
1分間隔のデータは、丸め込みでの小さいものとなっていなかった(CPU約85%, ディスクキュー約900)ので、これを利用可と考える。
IOPSはPerformanceInsightsにはないので、クラスタのモニタリングタブを見る。(クラスタは、ライターインスタンスの方のメトリクスを大概含んでる※connectionsとかはライターインスタンス見る必要あり なので、クラスタのモニタリングタブ見る)
メトリクスの検索窓でReadと入力して読み込み系見る
検索窓でWriteを入力して書き込み系見る
これ見ると、データ投入後2分間に若干の読み込み(140IOPSくらい, ハードディスクだとそれなりのIOだけど.. 高性能のクラウドのSSDだと低く見える)が発生してる。書き込みと比べると無風くらいに感じる。
書き込みの方は、グラフで読み取りづらいけど360kのIOPSが継続してて、スループットは50MB/sで継続。360k = 36万IOPSかー！Auroraのディスク速いな！
ここ数年標準になってきたgp3のベースラインが3000とかで、それでも十分だし、自分の仕事の大規模環境でもgp3のIOPSを15000, スループット1GB(だったっけ??たぶんその辺)とかで使えるようにインスタンスタイプもX1の最新のやつとか選ぶ感じ。
そう考えるとAuroraの36万IOPSはかなりすごいな。
データ件数を大量登録しようとして、なんとなく、1000万件でも結構早く終わりそうだから、1億件か10億件か迷って、自分が思う世間の大規模環境(データが多い環境)でよくありそうな1億件を投入してみたけど、これが5分弱で終わるって相当な性能なんだな..
カラム数は1つで、投入データもintegerで数桁で、テーブルサイズは3.37GBと、そこまでおおごとに考えなかったけど、これ逆に多数のカラムで構成した同じサイズより、カラム1個でレコード数大量の方が、フェッチサイズ的なもの(トランザクション単位とかカーソル進行とか)で負荷かける感じになってるような気がする。
たしかに、過去に金融系のDBリプレイスしたとき、20億行あるテーブルのサイズって3.37GBとかじゃなかった気がする。DB全体で2TBとかあって、その中の巨大なコアなテーブル100GBレベルだったような。oracleで、統計取れないので統計取るときのパーセンテージで全体の10%?とか指定してたし、データファイル格納してるNetAppから新しいハードのNetAppへの移行(スナップショット的な機能を利用しての移行)も20時間くらいかかったような..
カラム数もっと商用/業務アプリ的に100個～とかにしてやってみると良さそうだよなあ。
翌日、DevOps Guruに機械学習かけたパフォーマンス情報見れると知って見てみたけど特に何もでてなかった
テーブル再作成、データ再投入(100万件)
1億件だと時間かかるので100万件にしてみる
テーブルdropして再作成
set search_path to "PFS";
create table t1( c1 integer ) ;100万件投入
INSERT INTO t1 (c1)
SELECT
i
FROM
generate_series(1, 1000000) as i
;3秒で完了
INSERT 0 1000000
Query returned successfully in 3 secs 379 msec.単純にselectしてみる
初回実行(キャッシュなし?テーブル作成時にはキャッシュ作成されてないかな??)
3.263sで返却
select * from t1;2回目(キャッシュあり?)
2.955s
3回目
3.195s
キャッシュ効いてこれ??キャッシュ効いてない??
PostgreSQLの実行計画とかキャッシュ状態見る方法はと..
そうだ、explainを先頭につけるだけだった
で、anayze/bufferとかもつけると、より詳細な情報やバッファ情報出してくれる
ここまでpgAdminでやってたけど、SQLの実行結果を記事にコピペしたいので、psqlのクライアントツールをEC2にインストールする
sudo yum search postgresql | grep -i client
yum install -y postgresql.aarch64インストールされるRPM2個だけですごい軽くて良い
Total download size: 3.3 M
Installed size: 17 M
Downloading packages:
(1/2): postgresql-libs-9.2.24-8.amzn2.0.1.aarch64.rpm
(2/2): postgresql-9.2.24-8.amzn2.0.1.aarch64.rpm接続したらエラーでた
# psql -h RDSエンドポイント -U ユーザー -d DB
psql: SCRAM authentication requires libpq version 10 or aboveさっきインストールしたのに含まれるのはver9
# yum deplist postgresql.aarch64 | grep libpq -A1
dependency: libpq.so.5()(64bit)
provider: postgresql-libs.aarch64 9.2.24-8.amzn2.0.1新しいクライアントverを入れてみようか..
参考サイト
# yum remove postgresql.aarch64 -y
# rpm -qa | grep -i postg
postgresql-libs-9.2.24-8.amzn2.0.1.aarch64
# rpm -e postgresql-libs-9.2.24-8.amzn2.0.1.aarch64pgdg-redhat… rpmをインストールして、yum updateしたらいっぱいエラーでた
# sudo yum update
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
pgdg-common/aarch64/signature | 490 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
pgdg-common/aarch64/signature | 2.9 kB 00:00:03 !!!
download.postgresql.org [Errno -1] repomd.xml signature could not be verified for pgdg-common
Trying other mirror.
One of the configured repositories failed (PostgreSQL common RPMs for RHEL / CentOS 7 - aarch64),
and yum doesn't have enough cached data to continue. At this point the only
safe thing yum can do is fail. There are a few ways to work "fix" this:
1. Contact the upstream for the repository and get them to fix the problem.
2. Reconfigure the baseurl/etc. for the repository, to point to a working
upstream. This is most often useful if you are using a newer
distribution release than is supported by the repository (and the
packages for the previous distribution release still work).
3. Run the command with the repository temporarily disabled
yum --disablerepo=pgdg-common ...
4. Disable the repository permanently, so yum won't use it by default. Yum
will then just ignore the repository until you permanently enable it
again or use --enablerepo for temporary usage:
yum-config-manager --disable pgdg-common
or
subscription-manager repos --disable=pgdg-common
5. Configure the failing repository to be skipped, if it is unavailable.
Note that yum will try to contact the repo. when it runs most commands,
so will have to try and fail each time (and thus. yum will be be much
slower). If it is a very temporary problem though, this is often a nice
compromise:
yum-config-manager --save --setopt=pgdg-common.skip_if_unavailable=true
failure: repodata/repomd.xml from pgdg-common: [Errno 256] No more mirrors to try.
中止
PostgreSQLのユーザ会?でRPM配ってるみたいなのでそれ試してみる
ここからwgetするだけ
現時点最新15.3のをインストールしたらエラーでたので
# ls -Ahl
total 1.9M
-rw-r--r-- 1 root root 1.6M May 11 06:25 postgresql15-15.3-1PGDG.rhel7.aarch64.rpm
-rw-r--r-- 1 root root 269K May 11 06:25 postgresql15-libs-15.3-1PGDG.rhel7.aarch64.rpm
# rpm -ivh *
warning: postgresql15-15.3-1PGDG.rhel7.aarch64.rpm: Header V4 RSA/SHA1 Signature, key ID 69246c03: NOKEY
error: Failed dependencies:
libzstd >= 1.4.0 is needed by postgresql15-15.3-1PGDG.rhel7.aarch64
libzstd.so.1()(64bit) is needed by postgresql15-15.3-1PGDG.rhel7.aarch6412をインストール
# ls -Ahl
total 2.0M
-rw-r--r-- 1 root root 1.7M May 11 05:32 postgresql12-12.15-1PGDG.rhel7.aarch64.rpm
-rw-r--r-- 1 root root 364K May 11 05:32 postgresql12-libs-12.15-1PGDG.rhel7.aarch64.rpm
# rpm -ivh *
warning: postgresql12-12.15-1PGDG.rhel7.aarch64.rpm: Header V4 RSA/SHA1 Signature, key ID 69246c03: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql12-libs-12.15-1PGDG.rhe################################# [ 50%]
2:postgresql12-12.15-1PGDG.rhel7 ################################# [100%]おー、つながった
こういうのは別記事にして、qiitaとかみんなに見てもらえるメディアにのせたほうが役に立ちそう
->書いた(このアカウントで初投稿)
以降、psqlでつなぐ
100万件select文の実行計画確認
set search_path to "PFS";
explain (analyze, buffers) select * from t1; QUERY PLAN
----------------------------------------------------------------------------------------------
---------------
Seq Scan on t1 (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.003..44.923 rows=10
00000 loops=1)
Buffers: shared hit=4425
Planning:
Buffers: shared hit=19
Planning Time: 0.127 ms
Execution Time: 79.971 ms
(6 rows)shared hit=4425。これはブロック数か何か?? PostgreSQLのセグメント格納方式知らないけど..
statistics見る
Heap Blocks readが4425だから、全部キャッシュから取れてるってことかな。
インデックスアクセスしてみる
create index i1 on t1(c1);再度普通に全件選択
インデックス未使用
Seq Scan on t1 (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.004..46.975 rows=10
00000 loops=1)
Buffers: shared hit=4425
Planning:
Buffers: shared hit=20
Planning Time: 0.113 ms
Execution Time: 82.263 ms
(6 rows)1カラムだけ指定してみる
explain (analyze, buffers) select * from t1 where c1=1;
Index Only Scan using i1 on t1 (cost=0.42..4.44 rows=1 width=4) (actual time=0.024..0.025 ro
ws=1 loops=1)
Index Cond: (c1 = 1)
Heap Fetches: 0
Buffers: shared hit=4
Planning:
Buffers: shared hit=8
Planning Time: 0.074 ms
Execution Time: 0.049 ms
(8 rows)インデックス使われた。
インデックス利用状況の確認SQLがある
select * from pg_stat_user_indexes;
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_
fetch
-------+------------+------------+---------+--------------+----------+--------------+---------
------
16444 | 16447 | PFS | t1 | i1 | 1 | 1 |
0情報少な..
これ直近1回の結果だろうか??再実行
PFS=> explain (analyze, buffers) select * from t1 where c1=1;
QUERY PLAN
----------------------------------------------------------------------------------------------
--------------
Index Only Scan using i1 on t1 (cost=0.42..4.44 rows=1 width=4) (actual time=0.025..0.026 ro
ws=1 loops=1)
Index Cond: (c1 = 1)
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.067 ms
Execution Time: 0.046 ms
(6 rows)
PFS=>
select * from pg_stat_user_indexes;
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_
fetch
-------+------------+------------+---------+--------------+----------+--------------+---------
------
16444 | 16447 | PFS | t1 | i1 | 2 | 2 |
0
(1 row)idx_scan/idx_tup_readが2になったので、インデックスごとに、そのインデックスの累積値を出すっぽいな.. 運用でよく使うとして紹介されてるけど、これの有用性は今の自分にはまだわかっていない
あとポスグレって、インデックス貼った時にoracleみたいに統計情報取る必要ないのかな??
いまはインデックス貼るだけで実行計画変化したけど。
統計見てみる
参考サイト
PFS=> \x
Expanded display is on.
PFS=> SELECT * FROM pg_stat_all_tables where relname ='t1';
-[ RECORD 1 ]-------+------------------------------
relid | 16444
schemaname | PFS
relname | t1
seq_scan | 9
seq_tup_read | 7000000
idx_scan | 2
idx_tup_fetch | 0
n_tup_ins | 1000000
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 1000000
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2023-07-13 21:45:13.436876+00
last_analyze |
last_autoanalyze | 2023-07-13 21:45:13.603786+00
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 1last_autoanalyzeは昨日の日付、last_analyzeはnull。
これは統計は昨日のままで直近では取れてないと思って良いだろうか..
pg_stat_statementsというのものがあるので試してみる
参考サイト
select name, comment from pg_available_extensions order by 1;
name | comment
------------------------------+---------------------------------------------------------------------------------------------------------------------
address_standardizer | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
address_standardizer_data_us | Address Standardizer US dataset example
amcheck | functions for verifying relation integrity
apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
aurora_stat_utils | Statistics utility functions
autoinc | functions for autoincrementing fields
aws_commons | Common data types across AWS services
aws_lambda | AWS Lambda integration
aws_ml | ml integration
aws_s3 | AWS S3 extension for importing data from S3
babelfishpg_common | Transact SQL Datatype Support
babelfishpg_money | babelfishpg_money
babelfishpg_tds | TDS protocol extension
babelfishpg_telemetry | Transact SQL Telemetry
babelfishpg_tsql | Transact SQL compatibility
bloom | bloom access method - signature file based index
bool_plperl | transform between bool and plperl
btree_gin | support for indexing common datatypes in GIN
btree_gist | support for indexing common datatypes in GiST
citext | data type for case-insensitive character strings
cube | data type for multidimensional cubes
dblink | connect to other PostgreSQL databases from within a database
dict_int | text search dictionary template for integers
dict_xsyn | text search dictionary template for extended synonym processing
earthdistance | calculate great-circle distances on the surface of the Earth
fuzzystrmatch | determine similarities and distance between strings
hll | type for storing hyperloglog data
hstore | data type for storing sets of (key, value) pairs
hstore_plperl | transform between hstore and plperl
insert_username | functions for tracking who changed a table
intagg | integer aggregator and enumerator (obsolete)
intarray | functions, operators, and index support for 1-D arrays of integers
ip4r |
isn | data types for international product numbering standards
jsonb_plperl | transform between jsonb and plperl
lo | Large Object maintenance
log_fdw | foreign-data wrapper for Postgres log file access
ltree | data type for hierarchical tree-like structures
moddatetime | functions for tracking last modification time
old_snapshot | utilities in support of old_snapshot_threshold
oracle_fdw | foreign data wrapper for Oracle access
orafce | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
pg_bigm | text similarity measurement and index searching based on bigrams
pg_buffercache | examine the shared buffer cache
pg_cron | Job scheduler for PostgreSQL
pg_freespacemap | examine the free space map (FSM)
pg_hint_plan |
pg_partman | Extension to manage partitioned tables by time or ID
pg_prewarm | prewarm relation data
pg_proctab | Access operating system process table
pg_repack | Reorganize tables in PostgreSQL databases with minimal locks
pg_similarity | support similarity queries
pg_stat_statements | track planning and execution statistics of all SQL statements executed
pg_tle | Trusted Language Extensions for PostgreSQL
pg_trgm | text similarity measurement and index searching based on trigrams
pg_visibility | examine the visibility map (VM) and page-level visibility info
pg_walinspect | functions to inspect contents of PostgreSQL Write-Ahead Log
pgaudit | provides auditing functionality
pgcrypto | cryptographic functions
pglogical | PostgreSQL Logical Replication
pglogical_origin | Dummy extension for compatibility when upgrading from Postgres 9.4
pgrouting | pgRouting Extension
pgrowlocks | show row-level locking information
pgstattuple | show tuple-level statistics
pgtap | Unit testing for PostgreSQL
plcoffee | PL/CoffeeScript (v8) trusted procedural language
plls | PL/LiveScript (v8) trusted procedural language
plperl | PL/Perl procedural language
plpgsql | PL/pgSQL procedural language
plprofiler | server-side support for profiling PL/pgSQL functions
pltcl | PL/Tcl procedural language
plv8 | PL/JavaScript (v8) trusted procedural language
postgis | PostGIS geometry and geography spatial types and functions
postgis_raster | PostGIS raster types and functions
postgis_tiger_geocoder | PostGIS tiger geocoder and reverse geocoder
postgis_topology | PostGIS topology spatial types and functions
postgres_fdw | foreign-data wrapper for remote PostgreSQL servers
prefix | Prefix Range module for PostgreSQL
rdkit | Cheminformatics functionality for PostgreSQL.
rds_activity_stream | RDS ACTIVITY Agent
rds_tools | miscellaneous administrative functions for Aurora PostgreSQL
refint | functions for implementing referential integrity (obsolete)
seg | data type for representing line segments or floating-point intervals
sslinfo | information about SSL certificates
tablefunc | functions that manipulate whole tables, including crosstab
tcn | Triggered change notifications
tds_fdw | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
tsm_system_rows | TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time | TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent | text search dictionary that removes accents
uuid-ossp | generate universally unique identifiers (UUIDs)
vector | vector data type and ivfflat access method
(92 rows)利用可能、なものはいっぱいある.. (Aurora PostgreSQL 15.3)
頑張って全体を理解してみる
めっちゃ簡単に整形できたよ.. すご
手順)
- コンソールを全画面化して変な改行入れないように再実行
- 出力結果をsakuraにコピペして不要な行(区切り文字だけの行)消すとか整形
- libreofficeに貼る。貼るときに区切り文字を「|」で設定するとちゃんと表になる
- comment列をコピーしてGoogle翻訳
- Google翻訳結果をコピーして表に戻す(ちゃんと元々のセルの位置に戻る)
- libreofficeのその表をコピーしてWordPressに貼るだけ(できたのが以下)
- MLがミリリットルに翻訳されてるのとか見つけて修正
| name | comment |
| address_standardizer | 住所を構成要素に解析するために使用されます。 通常、ジオコーディング住所正規化ステップをサポートするために使用されます。 |
| address_standardizer_data_us | Address Standardizer US データセットの例 |
| amcheck | 関係の整合性を検証するための関数 |
| apg_plan_mgmt | PostgreSQL との互換性を備えた Amazon Aurora クエリプラン管理 |
| aurora_stat_utils | 統計ユーティリティ関数 |
| autoinc | フィールドを自動インクリメントする関数 |
| aws_commons | AWS のサービス全体で共通のデータ型 |
| aws_lambda | AWS Lambda の統合 |
| aws_ml | ML統合 |
| aws_s3 | S3 からデータをインポートするための AWS S3 拡張機能 |
| babelfishpg_common | トランザクション SQL データ型のサポート |
| babelfishpg_money | babelfishpg_money |
| babelfishpg_tds | TDSプロトコル拡張 |
| babelfishpg_telemetry | トランザクション SQL テレメトリ |
| babelfishpg_tsql | トランザクション SQL の互換性 |
| bloom | ブルームアクセス方法 – 署名ファイルベースのインデックス |
| bool_plperl | bool と plperl 間の変換 |
| btree_gin | GIN での一般的なデータ型のインデックス作成のサポート |
| btree_gist | GiST での一般的なデータ型のインデックス作成のサポート |
| citext | 大文字と小文字を区別しない文字列のデータ型 |
| cube | 多次元キューブのデータ型 |
| dblink | データベース内から他の PostgreSQL データベースに接続する |
| dict_int | 整数用のテキスト検索辞書テンプレート |
| dict_xsyn | 拡張同義語処理用のテキスト検索辞書テンプレート |
| earthdistance | 地球表面の大圏距離を計算する |
| fuzzystrmatch | 文字列間の類似性と距離を決定する |
| hll | hyperloglog データを保存するためのタイプ |
| hstore | (キー、値) ペアのセットを格納するためのデータ型 |
| hstore_plperl | hstore と plperl 間の変換 |
| insert_username | 誰がテーブルを変更したかを追跡する関数 |
| intagg | 整数アグリゲータと列挙子 (廃止) |
| intarray | 整数の 1 次元配列の関数、演算子、およびインデックスのサポート |
| ip4r | |
| isn | 国際的な製品番号付け標準のデータ型 |
| jsonb_plperl | jsonb と plperl の間で変換する |
| lo | ラージオブジェクトのメンテナンス |
| log_fdw | Postgres ログ ファイル アクセス用の外部データ ラッパー |
| ltree | 階層ツリー状構造のデータ型 |
| moddatetime | 最終変更時刻を追跡するための関数 |
| old_snapshot | old_snapshot_threshold をサポートするユーティリティ |
| oracle_fdw | Oracle アクセス用の外部データ ラッパー |
| orafce | Oracle RDBMS の関数とパッケージのサブセットをエミュレートする関数と演算子 |
| pg_bigm | テキストの類似性測定とバイグラムに基づくインデックス検索 |
| pg_buffercache | 共有バッファキャッシュを調べる |
| pg_cron | PostgreSQL のジョブ スケジューラ |
| pg_freespacemap | 空き領域マップ (FSM) を調べる |
| pg_hint_plan | |
| pg_partman | パーティション化されたテーブルを時間または ID で管理するための拡張機能 |
| pg_prewarm | ウォーム前の関係データ |
| pg_proctab | オペレーティング システムのプロセス テーブルにアクセスする |
| pg_repack | 最小限のロックで PostgreSQL データベースのテーブルを再編成する |
| pg_similarity | 類似性クエリのサポート |
| pg_stat_statements | 実行されたすべての SQL ステートメントの計画と実行統計を追跡する |
| pg_tle | PostgreSQL 用の信頼できる言語拡張機能 |
| pg_trgm | テキストの類似性測定とトリグラムに基づくインデックス検索 |
| pg_visibility | 可視性マップ (VM) とページレベルの可視性情報を調べる |
| pg_walinspect | PostgreSQL 先行書き込みログの内容を検査する関数 |
| pgaudit | 監査機能を提供します |
| pgcrypto | 暗号化機能 |
| pglogical | PostgreSQL 論理レプリケーション |
| pglogical_origin | Postgres 9.4 からアップグレードする場合の互換性のためのダミー拡張機能 |
| pgrouting | pgRouting 拡張機能 |
| pgrowlocks | 行レベルのロック情報を表示する |
| pgstattuple | タプルレベルの統計を表示する |
| pgtap | PostgreSQL の単体テスト |
| plcoffee | PL/CoffeeScript (v8) 信頼できる手続き型言語 |
| plls | PL/LiveScript (v8) 信頼できる手続き型言語 |
| plperl | PL/Perl手続き型言語 |
| plpgsql | PL/pgSQL手続き型言語 |
| plprofiler | PL/pgSQL関数のプロファイリングに対するサーバー側のサポート |
| pltcl | PL/Tcl 手続き型言語 |
| plv8 | PL/JavaScript (v8) 信頼できる手続き型言語 |
| postgis | PostGIS のジオメトリと地理の空間タイプと関数 |
| postgis_raster | PostGIS ラスターのタイプと関数 |
| postgis_tiger_geocoder | PostGIS Tiger ジオコーダーとリバース ジオコーダー |
| postgis_topology | PostGIS トポロジの空間タイプと機能 |
| postgres_fdw | リモート PostgreSQL サーバーの外部データ ラッパー |
| prefix | PostgreSQL のプレフィックス範囲モジュール |
| rdkit | PostgreSQL のケモインフォマティクス機能。 |
| rds_activity_stream | RDS アクティビティ エージェント |
| rds_tools | Aurora PostgreSQL のその他の管理機能 |
| refint | 参照整合性を実装するための関数 (廃止) |
| seg | 線分または浮動小数点間隔を表すデータ型 |
| sslinfo | SSL証明書に関する情報 |
| tablefunc | クロス集計を含むテーブル全体を操作する関数 |
| tcn | トリガーによる変更通知 |
| tds_fdw | TDS データベース (Sybase または Microsoft SQL Server) をクエリするための外部データ ラッパー |
| tsm_system_rows | 行数を制限として受け入れる TABLESAMPLE メソッド |
| tsm_system_time | ミリ秒単位の時間を制限として受け入れる TABLESAMPLE メソッド |
| unaccent | アクセントを削除するテキスト検索辞書 |
| uuid-ossp | 汎用一意識別子 (UUID) を生成する |
| vector | ベクトル データ型と ivfflat アクセス方法 |
この中でパフォーマンス系に絞ると以下
| name | comment |
| apg_plan_mgmt | PostgreSQL との互換性を備えた Amazon Aurora クエリプラン管理 |
| aurora_stat_utils | 統計ユーティリティ関数 |
| pg_buffercache | 共有バッファキャッシュを調べる |
| pg_hint_plan | |
| pg_prewarm | ウォーム前の関係データ |
| pg_stat_statements | 実行されたすべての SQL ステートメントの計画と実行統計を追跡する |
| pg_visibility | 可視性マップ (VM) とページレベルの可視性情報を調べる |
| pg_walinspect | PostgreSQL 先行書き込みログの内容を検査する関数 |
| pgrowlocks | 行レベルのロック情報を表示する |
| pgstattuple | タプルレベルの統計を表示する |
パフォーマンス系じゃないかもだけどなんか気になるものを念のため以下に残しとく(気が向いたら調べる)
| name | comment |
| aws_ml | ML統合 |
| lo | ラージオブジェクトのメンテナンス |
| pg_partman | パーティション化されたテーブルを時間または ID で管理するための拡張機能 |
| pg_proctab | オペレーティング システムのプロセス テーブルにアクセスする |
| pg_repack | 最小限のロックで PostgreSQL データベースのテーブルを再編成する |
| pgrouting | pgRouting 拡張機能 |
| pg_similarity | 類似性クエリのサポート |
| rds_activity_stream | RDS アクティビティ エージェント |
| rds_tools | Aurora PostgreSQL のその他の管理機能 |
| tablefunc | クロス集計を含むテーブル全体を操作する関数 |
| tsm_system_rows | 行数を制限として受け入れる TABLESAMPLE メソッド |
| tsm_system_time | ミリ秒単位の時間を制限として受け入れる TABLESAMPLE メソッド |
んで、話は戻って、pg_stat_statementsを使えるようにする
参考サイト
インストール
PFS=> select * from pg_available_extensions where name = 'pg_stat_statements';
name | default_version | installed_version | c
omment
--------------------+-----------------+-------------------+---------------------------------
---------------------------------------
pg_stat_statements | 1.10 | | track planning and execution sta
tistics of all SQL statements executed
(1 row)
PFS=>
PFS=> CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
PFS=>
PFS=> select * from pg_available_extensions where name = 'pg_stat_statements';
name | default_version | installed_version | c
omment
--------------------+-----------------+-------------------+---------------------------------
---------------------------------------
pg_stat_statements | 1.10 | 1.10 | track planning and execution sta
tistics of all SQL statements executed
(1 row)使えるようになった
けどレコード多いな
PFS=> select count(*) from pg_stat_statements;
count
-------
4774
(1 row)直近で自分が実行したSQLに絞るには..?
pg_stat_statementsのカラム一覧
PFS=> \d pg_stat_statements
View "PFS.pg_stat_statements"
Column | Type | Collation | Nullable | Default
------------------------+------------------+-----------+----------+---------
userid | oid | | |
dbid | oid | | |
toplevel | boolean | | |
queryid | bigint | | |
query | text | | |
plans | bigint | | |
total_plan_time | double precision | | |
min_plan_time | double precision | | |
max_plan_time | double precision | | |
mean_plan_time | double precision | | |
stddev_plan_time | double precision | | |
calls | bigint | | |
total_exec_time | double precision | | |
min_exec_time | double precision | | |
max_exec_time | double precision | | |
mean_exec_time | double precision | | |
stddev_exec_time | double precision | | |
rows | bigint | | |
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
shared_blks_dirtied | bigint | | |
shared_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
temp_blks_read | bigint | | |
temp_blks_written | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
temp_blk_read_time | double precision | | |
temp_blk_write_time | double precision | | |
wal_records | bigint | | |
wal_fpi | bigint | | |
wal_bytes | numeric | | |
jit_functions | bigint | | |
jit_generation_time | double precision | | |
jit_inlining_count | bigint | | |
jit_inlining_time | double precision | | |
jit_optimization_count | bigint | | |
jit_optimization_time | double precision | | |
jit_emission_count | bigint | | |
jit_emission_time | double precision | | |
クエリで絞れた
select * from pg_stat_statements where query like '%t1%';1つだけに絞って縦表示で見てみる
\x
select * from pg_stat_statements where query like 'explain (analyze, buffers) select * from t1 where c1=1';PFS=> \x
Expanded display is on.
PFS=> select * from pg_stat_statements where query like 'explain (analyze, buffers) select * from t1 where c1=1';
-[ RECORD 1 ]----------+-------------------------------------------------------
userid | 16400
dbid | 16401
toplevel | t
queryid | 6415160146743782283
query | explain (analyze, buffers) select * from t1 where c1=1
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 2
total_exec_time | 0.980962
min_exec_time | 0.178104
max_exec_time | 0.802858
mean_exec_time | 0.490481
stddev_exec_time | 0.312377
rows | 0
shared_blks_hit | 19
shared_blks_read | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
blk_read_time | 0
blk_write_time | 0
temp_blk_read_time | 0
temp_blk_write_time | 0
wal_records | 0
wal_fpi | 0
wal_bytes | 0
jit_functions | 0
jit_generation_time | 0
jit_inlining_count | 0
jit_inlining_time | 0
jit_optimization_count | 0
jit_optimization_time | 0
jit_emission_count | 0
jit_emission_time | 0情報取れた。今のところどの辺が有用な値なのかはわかっていない。
もちっとちゃんとしたパフォーマンステスト用データ投入
ポイントカードをイメージする
顧客テーブル
ID, 氏名, 誕生日, 性別, 住んでる県
create table pfs.customer(cardnumber integer, name varchar(20), birthday date, gender integer, state varchar(20));商品テーブル
ID, 商品名, 価格, カテゴリ, メーカー
create table pfs.item(itemid integer, name varchar(20), price integer, category varchar(20), manufacturer varchar(20));店舗テーブル
店舗ID, 店舗名, 県
create table pfs.shop(shopid integer, shopname varchar(20), state varchar(20));購入履歴テーブル
購入ID, カードNo, 商品ID, ショップID, 個数, 購入日
カードNo, 商品ID, ショップIDは他テーブルを参照する (外部キーは利用しない。SQLで整合性取る)
create table pfs.purchase(purchaseid integer, cardnumber integer, itemid integer, shopid integer, quantity integer, purchasedate date);できたもの
pfs=> \d pfs.customer
Table "pfs.customer"
Column | Type | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
cardnumber | integer | | |
name | character varying(20) | | |
birthday | date | | |
gender | integer | | |
state | character varying(20) | | |
pfs=> \d pfs.item
Table "pfs.item"
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+---------
itemid | integer | | |
name | character varying(20) | | |
price | integer | | |
category | character varying(20) | | |
manufacturer | character varying(20) | | |
pfs=> \d pfs.shop
Table "pfs.shop"
Column | Type | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
shopid | integer | | |
shopname | character varying(20) | | |
state | character varying(20) | | |
pfs=> \d pfs.purchase
Table "pfs.purchase"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
purchaseid | integer | | |
cardnumber | integer | | |
itemid | integer | | |
shopid | integer | | |
quantity | integer | | |
purchasedate | date | | |プライマリーキーとか制約(ユニーク, null許可), インデックス貼るのとかやらないと..
データ投入面倒そうだなあ
apg_plan_mgmt
実行計画の固定化機能。クラスメソッドさんの記事のとおりな感じ
aurora_stat_utils
この関数は、aurora_stat_system_waits() 関数と pg_stat_database PostgreSQL 統計ビューからの統計データの 2 つのスナップショットを比較することにより、インスタンスレベルの待機イベントを計算します。
インストールする
pfs=> select * from pg_available_extensions where name = 'aurora_stat_utils';
name | default_version | installed_version | comment
-------------------+-----------------+-------------------+------------------------------
aurora_stat_utils | 1.0 | | Statistics utility functions
(1 row)
pfs=> select * from pg_available_extensions where name = 'aurora_stat_utils';
pfs=> CREATE extension aurora_stat_utils;
CREATE EXTENSION
pfs=> select * from pg_available_extensions where name = 'aurora_stat_utils';
name | default_version | installed_version | comment
-------------------+-----------------+-------------------+------------------------------
aurora_stat_utils | 1.0 | 1.0 | Statistics utility functions
(1 row)使ってみる
pfs-> SELECT * FROM aurora_wait_report();
NOTICE: committed 31 transactions in 10 seconds (tps 3)
type_name | event_name | waits | wait_time | ms_per_wait | waits_per_xact | m
s_per_xact
-----------+--------------------------+-------+-----------+-------------+----------------+--
-----------
Client | ClientRead | 40 | 30989.00 | 774.725 | 1.29 |
999.645
Timeout | PgSleep | 400 | 20074.86 | 50.187 | 12.90 |
647.576
Activity | BgWriterHibernate | 2 | 13101.69 | 6550.845 | 0.06 |
422.635
Activity | WalWriterMain | 50 | 10047.61 | 200.952 | 1.61 |
324.116
Activity | AutoVacuumMain | 24 | 10005.42 | 416.892 | 0.77 |
322.755
Activity | BgWriterMain | 1 | 201.01 | 201.011 | 0.03 |
6.484
IO | XactSync | 15 | 43.45 | 2.897 | 0.48 |
1.402
IO | RelationMapRead | 16 | 0.51 | 0.032 | 0.52 |
0.016
IO | AuroraStorageLogAllocate | 77 | 0.15 | 0.002 | 2.48 |
0.005
(9 rows)単に実行すると10秒間の待機レポートとなる。
60秒で取るには60を指定すれば良い
実行に時間かかるのか..? と思ったけど、これ実行開始してから、指定秒数待ってるのか..
つまりリアルタイムのものを取る..? 1秒にしたら1秒で返ってくるし
pfs=> SELECT * FROM aurora_wait_report(60);
NOTICE: committed 260 transactions in 60 seconds (tps 4)
type_name | event_name | waits | wait_time | ms_per_wait | waits_per_xact | m
s_per_xact
-----------+--------------------------+-------+-----------+-------------+----------------+--
-----------
Client | ClientRead | 354 | 292838.78 | 827.228 | 1.36 |
1126.303
Activity | LogicalLauncherMain | 1 | 180099.87 | 180099.872 | 0.00 |
692.692
Timeout | PgSleep | 2389 | 120115.46 | 50.279 | 9.19 |
461.983
Activity | BgWriterHibernate | 7 | 63319.74 | 9045.677 | 0.03 |
243.537
Activity | WalWriterMain | 300 | 60276.48 | 200.922 | 1.15 |
231.833
Activity | CheckpointerMain | 1 | 60099.68 | 60099.675 | 0.00 |
231.153
Activity | AutoVacuumMain | 138 | 60048.72 | 435.136 | 0.53 |
230.957
Activity | BgWriterMain | 6 | 1205.42 | 200.903 | 0.02 |
4.636
IO | XactSync | 91 | 261.67 | 2.875 | 0.35 |
1.006
IO | RelationMapRead | 96 | 3.32 | 0.035 | 0.37 |
0.013
IO | AuroraStorageLogAllocate | 241 | 0.82 | 0.003 | 0.93 |
0.003
IO | DataFileExtend | 1 | 0.02 | 0.015 | 0.00 |
0.000
IO | ControlFileWriteUpdate | 2 | 0.02 | 0.009 | 0.01 |
0.000
IO | ControlFileSyncUpdate | 1 | 0.00 | 0.001 | 0.00 |
0.000
(14 rows)まあこれでDBの待機イベントトップは分かるか..
リアルタイム実行なので、定期取得しておかないと、あとから分からなそう
pg_buffercache
インストール
pfs=> create table pfs.customer(id integer, name varchar(20), birthday date, gender integer, state varchar(20));^C
pfs=>
pfs=> select * from pg_available_extensions where name = 'pg_buffercache';
name | default_version | installed_version | comment
----------------+-----------------+-------------------+---------------------------------
pg_buffercache | 1.3 | | examine the shared buffer cache
(1 row)
pfs=> CREATE EXTENSION pg_buffercache;
CREATE EXTENSION
pfs=>
pfs=> select * from pg_available_extensions where name = 'pg_buffercache';
name | default_version | installed_version | comment
----------------+-----------------+-------------------+---------------------------------
pg_buffercache | 1.3 | 1.3 | examine the shared buffer cache
(1 row)使ってみる
件数確認
pfs=> select count(*) from pg_buffercache;
count
---------
1301958
(1 row)10件だけ見てみる
pfs=> select * from pg_buffercache FETCH FIRST 10 ROWS ONLY;
bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount | pi
nning_backends
----------+-------------+---------------+-------------+---------------+----------------+---------+------------+---
---------------
1 | 1262 | 1664 | 0 | 0 | 0 | f | 5 |
0
2 | 1260 | 1664 | 0 | 0 | 0 | f | 5 |
0
3 | 1259 | 1663 | 16384 | 0 | 0 | f | 5 |
0
4 | 1259 | 1663 | 16384 | 0 | 1 | f | 5 |
0
5 | 1259 | 1663 | 16384 | 0 | 2 | f | 5 |
0
6 | 1259 | 1663 | 16384 | 0 | 3 | f | 5 |
0
7 | 1249 | 1663 | 16384 | 0 | 0 | f | 5 |
0
8 | 1249 | 1663 | 16384 | 0 | 1 | f | 5 |
0
9 | 1249 | 1663 | 16384 | 0 | 2 | f | 5 |
0
10 | 1249 | 1663 | 16384 | 0 | 3 | f | 5 |
0
pfs=>なるほど、バッファキャッシュがID管理されてて、IDごとにバッファキャッシュにのせてるものを一覧できると。
リファレンスより、以下部分で、reltablespace/reldatabaseのOIDをpg_class/pg_databaseと紐づけると、テーブル/インデックス名を拾える。
サンプルを実行してみる
SELECT n.nspname, c.relname, count(*) AS buffers
FROM pg_buffercache b JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
JOIN pg_namespace n ON n.oid = c.relnamespace
GROUP BY n.nspname, c.relname
ORDER BY 3 DESC
LIMIT 10;
pfs=> SELECT n.nspname, c.relname, count(*) AS buffers
pfs-> FROM pg_buffercache b JOIN pg_class c
pfs-> ON b.relfilenode = pg_relation_filenode(c.oid) AND
pfs-> b.reldatabase IN (0, (SELECT oid FROM pg_database
pfs(> WHERE datname = current_database()))
pfs-> JOIN pg_namespace n ON n.oid = c.relnamespace
pfs-> GROUP BY n.nspname, c.relname
pfs-> ORDER BY 3 DESC
pfs-> LIMIT 10;
nspname | relname | buffers
------------+--------------------------------+---------
pg_catalog | pg_proc | 100
pg_toast | pg_toast_2618 | 68
pg_catalog | pg_attribute | 62
pg_catalog | pg_description | 48
pg_catalog | pg_collation | 37
pg_catalog | pg_proc_proname_args_nsp_index | 31
pg_catalog | pg_description_o_c_o_index | 28
pg_catalog | pg_statistic | 25
pg_catalog | pg_type | 19
pg_catalog | pg_depend | 18
(10 rows)なるほどなあ。
これも、定期取得しておけば、パフォーマンス問題(処理時間長いかつディスク負荷が高い)が起きた時に、キャッシュヒット率の低下がひとつの要因かが分かりそう。
バッファキャッシュを簡単に確認できるのは、oracleより簡単な感じでいいなあ(oracleでもv$bfでできるけど、公式な方法じゃなかったと思うのと、SQLが重め)。
buffersはブロック?単位で表示されてるけど、これバイト表記にできるのかな??
また気が向いたらやる
pg_hint_plan
インストール
pfs=> select * from pg_available_extensions where name = 'pg_hint_plan';
name | default_version | installed_version | comment
--------------+-----------------+-------------------+---------
pg_hint_plan | 1.5 | |
(1 row)
pfs=> CREATE extension pg_hint_plan;
CREATE EXTENSION
pfs=>
pfs=> select * from pg_available_extensions where name = 'pg_hint_plan';
name | default_version | installed_version | comment
--------------+-----------------+-------------------+---------
pg_hint_plan | 1.5 | 1.5 |
(1 row)ヒント句。
pg_prewarm
インストール
pfs=> select * from pg_available_extensions where name = 'pg_prewarm';
name | default_version | installed_version | comment
------------+-----------------+-------------------+-----------------------
pg_prewarm | 1.2 | | prewarm relation data
(1 row)
pfs=> CREATE extension pg_prewarm;
CREATE EXTENSION
pfs=>
pfs=> select * from pg_available_extensions where name = 'pg_prewarm';
name | default_version | installed_version | comment
------------+-----------------+-------------------+-----------------------
pg_prewarm | 1.2 | 1.2 | prewarm relation data
(1 row)構文
SELECT pg_prewarm('テーブル名', 'buffer', 'main');実行してみる