pg_stat_statements 확장 모듈을 설치하면 pg_stat_statements 딕션너리에 SQL 들이
수집되어 튜닝대상을 가려낼수 있습니다.
설치과정은 다음과 같습니다.
[root@pg-00:/root]#yum install postgresql92-contrib
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: ftp.neowiz.com
* extras: ftp.neowiz.com
* updates: ftp.neowiz.com
Setting up Install Process
Resolving Dependencies
There are unfinished transactions remaining. You might consider running yum-complete-transaction first to finish them.
--> Running transaction check
---> Package postgresql92-contrib.i686 0:9.2.2-1PGDG.rhel6 will be installed
--> Processing Dependency: libossp-uuid.so.16 for package: postgresql92-contrib-9.2.2-1PGDG.rhel6.i686
--> Running transaction check
---> Package uuid.i686 0:1.6.1-10.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=========================================================================================================
Package Arch Version Repository Size
=========================================================================================================
Installing:
postgresql92-contrib i686 9.2.2-1PGDG.rhel6 pgdg92 430 k
Installing for dependencies:
uuid i686 1.6.1-10.el6 base 54 k
Transaction Summary
=========================================================================================================
Install 2 Package(s)
Total download size: 484 k
Installed size: 1.6 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql92-contrib-9.2.2-1PGDG.rhel6.i686.rpm | 430 kB 00:02
(2/2): uuid-1.6.1-10.el6.i686.rpm | 54 kB 00:00
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 67 kB/s | 484 kB 00:07
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : uuid-1.6.1-10.el6.i686 1/2
Installing : postgresql92-contrib-9.2.2-1PGDG.rhel6.i686 2/2
Verifying : postgresql92-contrib-9.2.2-1PGDG.rhel6.i686 1/2
Verifying : uuid-1.6.1-10.el6.i686 2/2
Installed:
postgresql92-contrib.i686 0:9.2.2-1PGDG.rhel6
Dependency Installed:
uuid.i686 0:1.6.1-10.el6
Complete!
[root@pg-00:/root]#
postgres@pg-00:5432:postgres]
SQL> select * from pg_available_extensions order by name;
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+----------------------------------------------------------------------
....
pg_stat_statements | 1.1 | 1.1 | track execution statistics of all SQL
....
(47 rows)
postgres@pg-00:5432:postgres]
SQL> CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
postgres@pg-00:5432:postgres]
SQL> select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------------------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | NULL | NULL
pltcl | 10 | 11 | f | 1.0 | NULL | NULL
pg_stat_statements | 10 | 2200 | t | 1.1 | NULL | NULL
(3 rows)
## extension 을 설치한후 바로 관련 딕션너리를 조회해 봤지만 에러가 납니다.
SQL> SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
ERROR: pg_stat_statements must be loaded via shared_preload_libraries
postgres@pg-00:5432:postgres]
SQL>
postgresql.conf 설정화일을 아래와 같이 수정후 PostgreSQL 을 restart 합니다.
.....
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
.....
## 설정화일 변경후 조회가 잘 됩니다.
postgres@pg-00:5432:postgres]
SQL> SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
query | calls | total_time | rows | hit_percent
---------------------------------------------------------------
