pg_stat_monitor 基于pg_stat_statements 提供了更多的性能监控metrics
参考dockerfile
FROM postgres:12.3 as build
LABEL AUTHOR="dalongrong"
ENV TZ=Asia/Shanghai \
LANG=zh_CN.UTF-8
RUN set -x \
&& ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone \
&& localedef -i zh_CN -c -f UTF-8 -A /usr/share/locale/locale.alias $LANG \
&& echo 'LANG="$LANG"' > /etc/default/locale \
&& apt-get update \
&& apt-get install -y --no-install-recommends patch ca-certificates git wget unzip make postgresql-server-dev-"${PG_MAJOR}" gcc libc6-dev libssl-dev libkrb5-dev libaio1 gnupg \
&& wget -O /tmp/REL0_7_1.tar.gz https://github.com/percona/pg_stat_monitor/archive/REL0_7_1.tar.gz \
&& cd /tmp && tar xvzf REL0_7_1.tar.gz \
&& cd /tmp/pg_stat_monitor-REL0_7_1 \
&& make USE_PGXS=1 install \
&& apt-get autoremove \
&& apt-get autoclean \
&& rm -rf /var/lib/apt/lists/* \
&& rm -rf /tmp/*
FROM postgres:12.3
COPY --from=build /usr/lib/postgresql/12/lib/pg_stat_monitor.so /usr/lib/postgresql/12/lib/pg_stat_monitor.so
COPY --from=build /usr/lib/postgresql/12/lib/bitcode/pg_stat_monitor /usr/lib/postgresql/12/lib/bitcode/pg_stat_monitor
COPY --from=build /usr/share/postgresql/12/extension/pg_stat_monitor.control /usr/share/postgresql/12/extension/pg_stat_monitor.control
COPY --from=build /usr/share/postgresql/12/extension/pg_stat_monitor--1.0.sql /usr/share/postgresql/12/extension/pg_stat_monitor--1.0.sql
使用(需要重启)
- 启动时候配置
# - Shared Library Preloading -
shared_preload_libraries = 'pg_stat_monitor' # (change requires restart)
#local_preload_libraries = ''
#session_preload_libraries = '
- 运行时配置
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_monitor';
- 查询效果
说明
pg_stat_monitor 是比pg_stat_statements更加强大的一个pg 分析工具，很值得使用
参考资料
https://github.com/percona/pg_stat_monitor/blob/master/docs/USER_GUIDE.md
https://github.com/percona/pg_stat_monitor