PostgreSQL 튜닝 대상 쿼리 추출 설정

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       

---------------------------------------------------------------

....

(5 rows)

출처: PostgreSQL is so special PostgresDBA.com