728x90
반응형
먼저 pg_stat_statements 에 대해서 알아보자.
Postgresql 공식사이트 - www.postgresql.org/docs/13/pgstatstatements.html
pg_stat_statements를 한문장으로 정리하면 실행되는 쿼리의 통계(빈도,실행시간,Row수등)를 확인할 수 있는 view 라고 할 수 있을 것이다.
## 설치환경
* CentOS 7.6
* Postgresql 13.1
1. Postgresql contrib 설치
pg_stat_statements 를 사용하려면 Postgresql contrib 가 설치되어 있어야 한다.
su - root
yum list *postgresql*contrib*
yum -y install postgresql13-contrib.x86_64
[root@postgres ~]# yum list *postgresql*contrib*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.kakao.com
* extras: mirror.kakao.com
* updates: mirror.kakao.com
Installed Packages
postgresql13-contrib.x86_64 13.1-3PGDG.rhel7 @pgdg13
Available Packages
postgresql-contrib.x86_64 9.2.24-4.el7_8 base
postgresql10-contrib.x86_64 10.15-1PGDG.rhel7 pgdg10
postgresql11-contrib.x86_64 11.10-1PGDG.rhel7 pgdg11
postgresql12-contrib.x86_64 12.5-1PGDG.rhel7 pgdg12
postgresql95-contrib.x86_64 9.5.24-1PGDG.rhel7 pgdg95
postgresql96-contrib.x86_64 9.6.20-2PGDG.rhel7 pgdg96
[root@postgres ~]#
[root@postgres ~]# yum -y install postgresql13-contrib.x86_64
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.kakao.com
* extras: mirror.kakao.com
* updates: mirror.kakao.com
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
pgdg-common | 2.9 kB 00:00:00
pgdg10 | 3.6 kB 00:00:00
pgdg11 | 3.6 kB 00:00:00
pgdg12 | 3.6 kB 00:00:00
pgdg13 | 3.6 kB 00:00:00
pgdg95 | 3.6 kB 00:00:00
pgdg96 | 3.6 kB 00:00:00
updates | 2.9 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package postgresql13-contrib.x86_64 0:13.1-3PGDG.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================
Installing:
postgresql13-contrib x86_64 13.1-3PGDG.rhel7 pgdg13 608 k
Transaction Summary
==============================================================================================================================
Install 1 Package
Total download size: 608 k
Installed size: 2.1 M
Downloading packages:
postgresql13-contrib-13.1-3PGDG.rhel7.x86_64.rpm | 608 kB 00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql13-contrib-13.1-3PGDG.rhel7.x86_64 1/1
Verifying : postgresql13-contrib-13.1-3PGDG.rhel7.x86_64 1/1
Installed:
postgresql13-contrib.x86_64 0:13.1-3PGDG.rhel7
Complete!
[root@postgres ~]#
2. postgresql.conf - shared_preload_libraries 설정 변경
shared_preload_libraries 설정의 변경사항을 적용하려면 postgresql 를 재기동하여야 한다.
su - postgres
cd $PGDATA
cat postgresql.conf | grep -i shared_preload_libraries
vi postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
su - root
systemctl restart postgresql-13
[root@postgres ~]# su - postgres
Last login: Mon Jan 11 09:46:28 KST 2021
[postgres@postgres ~]$
[postgres@postgres ~]$
[postgres@postgres ~]$ cd $PGDATA
[postgres@postgres /data/pgsql/13/data]$ cat postgresql.conf | grep -i shared_preload_libraries
#shared_preload_libraries = '' # (change requires restart)
[postgres@postgres /data/pgsql/13/data]$
[postgres@postgres /data/pgsql/13/data]$ vi postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
:wq
[postgres@postgres /data/pgsql/13/data]$ su - root
[root@postgres ~]# systemctl restart postgresql-13
3. postgres db 접속 후 pg_stat_statements extension 을 생성
pg_stat_statements 을 사용할 데이터베이스에 접속해서 create extension pg_stat_statements; 명령어를 실행하여야 public.pg_stat_statements view 를 조회할 수 있다.
만약 사용하는 db가 2개이면 create extension pg_stat_statements; 명령어를 각각 2번 실행해주어야 한다.
팁 : 운영환경에서는 postgres db 는 사용하지 않고 업무db를 만들어서 pg_stat_statements 를 사용하면 부하를 적게 줄 수 있을 것이다.
psql
create extension pg_stat_statements;
\dx
[postgres@postgres /data/pgsql/13/data]$ psql
psql (13.1)
Type "help" for help.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------+------+----------
public | pg_stat_statements | view | postgres
(1 row)
postgres=#postgresql 13에 설치되는 pg_stat_statements 의 버전은 1.8 로 확인된다.
4. pg_stat_statements view 조회
select * from public.pg_stat_statements
너무 항목이 많고 userid, dbid 등을 알아볼 수가 없기 때문에 쿼리를 조금 바꿔보자.
/* 수행시간이 제일 긴 쿼리를 상위 50개만 조회 */
select a.userid
, b.usename
, a.dbid
, c.datname
, a.queryid
, substr(a.query, 1, 100) as query
, a.calls
, a.total_exec_time
, a.min_exec_time
, a.max_exec_time
, a.rows
from public.pg_stat_statements a
join pg_catalog.pg_user b on a.userid = b.usesysid
join pg_catalog.pg_stat_database c on a.dbid = c.datid
order by a.max_exec_time desc
limit 50
조회가 되지 않고 "ERROR: pg_stat_statements must be loaded via shared_preload_libraries" 에러가 발생하면 2번항목을 다시 확인해보자. (postgresql.conf 설정 및 서비스 재기동)
## 정리
- pg_stat_statements 만 잘 사용해도 특정한 솔루션 사용없이 부하를 많이 주는 쿼리, 실행시간이 오래 걸리는 쿼리를 찾아낼 수 있어 운영시 postgresql를 잘 관리 할 수 있을 것이다.
- 다만 pg_stat_statements 적용을 하려면 서비스 재기동이 필요하기 때문에 초기 세팅시 적용하는 것이 좋을 듯하다.
- pg_stat_statements 를 사용하면 아주 적게라도 시스템에 부하를 줄 수 있어 pg_stat_statements 적용을 고민해야할 것이다.
728x90
반응형
'Database > Postgresql' 카테고리의 다른 글
| Postgresql function 개발 및 예제 (0) | 2021.01.22 |
|---|---|
| pgagroal 란? (0) | 2021.01.15 |
| Postgresql 13 - pg_stat_statements 정의 및 기능정리 (0) | 2021.01.12 |
| Postgresql 13 - 운영환경 구성방법 (2) | 2021.01.10 |
| Postgresql 13을 CentOS 7.6에 RPM으로 설치하기 (2) | 2021.01.07 |
