설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 72건, 최근 0 건
   

튜닝 대상 쿼리 수집하기

글쓴이 : PostgresDBA 날짜 : 2012-12-26 (수) 21:17 조회 : 8555
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)

소라 2016-02-19 (금) 17:52
해당 수집으로 인하여 서버에 부하가 가지는 않나요?
댓글주소
PostgresDBA 2016-02-23 (화) 15:39
제가 운영하는 모든 db 에 세팅해서 쓰고 있습니다만 한번도 문제된적 없습니다.
문제될만큼의 부하를 주는건 아니니 활성화해서 쓰길 권장합니다.
댓글주소
   

postgresdba.com