Postgresql 에서 shared library 를 이용한 extension 기능 사용법
아래의 Postgresql 을 사용해보자라는 게기물에서 확장 기능에 대한 설명은 빼버렸는데 여기에서 사용법을 정리해본다.
** postgresql-9.2.4/contrib 위치에 다양한 extension 들이 존재함. 추가로 구현해도 됨.
그 중 대표적인 통계 관련 기능인 pg_stat_statements 에 대한 내용은 아래 URL 을 참고하자.
http://www.postgresql.org/docs/8.4/static/pgstatstatements.html
다음은 pg_stat_statements 확장 shared library 를 사용하기 위해 수행하는 절차이다.
원래 default 로는 Postgresql 에 pg_stat_statements 라는 View 가 존재하지 않지만, 아래의 절차를 통해 해당 확장 기능을 add-on 한 후 View 를 통해 통계 data 를 조회해볼 수 있는 유용한 기능이다.
먼저 contrib 디렉토리에 있는 이미 구현된 pg_stat_statements 확장 기능을 add-on 해두고, 마찬가지로 확장 기능으로 미리 구현되어 있는 benchmark test 기능을 이용하여 부하를 준 이후 pg_stat_statements view 를 통해 그 결과 통계를 조회해보는 절차이다.
Postgresql 은 이미 추가되어 있는 extension 기능들(contrib 디렉토리에 존재)이외에도 사용자가 얼마든지 원하는 확장기능을 직접 제작하여 add-on 할 수 있는 편리한 기능을 제공한다.
1. postgresql.conf 설정
shared_preload_libraries = 'pg_stat_statements' # (change requires restart) #pg_stat_statements.max = 10000 #pg_stat_statements.track = all
2. pg_stat_statements extension 빌드 및 설치
[smurf:/home/bitmyer/postgresql-9.2.4/contrib/pg_stat_statements$] make
[smurf:/home/bitmyer/postgresql-9.2.4/contrib/pg_stat_statements$] make install
/bin/mkdir -p '/home/bitmyer/work/postgres/lib'
/bin/mkdir -p '/home/bitmyer/work/postgres/share/extension'
/bin/mkdir -p '/home/bitmyer/work/postgres/share/extension'
/bin/sh ../../config/install-sh -c -m 755 pg_stat_statements.so '/home/bitmyer/work/postgres/lib/pg_stat_statements.so'
/bin/sh ../../config/install-sh -c -m 644 ./pg_stat_statements.control '/home/bitmyer/work/postgres/share/extension/'
/bin/sh ../../config/install-sh -c -m 644 ./pg_stat_statements--1.1.sql ./pg_stat_statements--1.0--1.1.sql ./pg_stat_statements--unpackaged--1.0.sql '/home/bitmyer/work/postgres/share/extension/'
[smurf:/home/bitmyer/postgresql-9.2.4/contrib/pg_stat_statements$]
[smurf:/home/bitmyer/postgresql-9.2.4/contrib/pg_stat_statements$] psql -p 7777 mydb
psql (9.2.4)
Type "help" for help.
mydb=# create extension pg_stat_statements;
CREATE EXTENSION
mydb=#
mydb=# d+ pg_stat_statements;
View "public.pg_stat_statements"
Column | Type | Modifiers | Storage | Description
---------------------+------------------+-----------+----------+-------------
userid | oid | | plain |
dbid | oid | | plain |
query | text | | extended |
calls | bigint | | plain |
total_time | double precision | | plain |
rows | bigint | | plain |
shared_blks_hit | bigint | | plain |
shared_blks_read | bigint | | plain |
shared_blks_dirtied | bigint | | plain |
shared_blks_written | bigint | | plain |
local_blks_hit | bigint | | plain |
local_blks_read | bigint | | plain |
local_blks_dirtied | bigint | | plain |
local_blks_written | bigint | | plain |
temp_blks_read | bigint | | plain |
temp_blks_written | bigint | | plain |
blk_read_time | double precision | | plain |
blk_write_time | double precision | | plain |
View definition:
SELECT pg_stat_statements.userid, pg_stat_statements.dbid,
pg_stat_statements.query, pg_stat_statements.calls,
pg_stat_statements.total_time, pg_stat_statements.rows,
pg_stat_statements.shared_blks_hit, pg_stat_statements.shared_blks_read,
pg_stat_statements.shared_blks_dirtied,
pg_stat_statements.shared_blks_written, pg_stat_statements.local_blks_hit,
pg_stat_statements.local_blks_read, pg_stat_statements.local_blks_dirtied,
pg_stat_statements.local_blks_written, pg_stat_statements.temp_blks_read,
pg_stat_statements.temp_blks_written, pg_stat_statements.blk_read_time,
pg_stat_statements.blk_write_time
FROM pg_stat_statements() pg_stat_statements(userid, dbid, query, calls, total_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);
mydb=# q
3. benchmark test extension 추가 및 실행
[smurf:/home/bitmyer/postgresql-9.2.4/contrib/pgbench$] make clean rm -f pgbench rm -f pgbench.o [smurf:/home/bitmyer/postgresql-9.2.4/contrib/pgbench$] make gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I../../src/interfaces/libpq -I. -I. -I../../src/include -D_GNU_SOURCE -c -o pgbench.o pgbench.c gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS pgbench.o -L../../src/port -lpgport -L../../src/interfaces/libpq -lpq -lpthread -L../../src/port -Wl,--as-needed -Wl,-rpath,'/home/bitmyer/work/postgres/lib',--enable-new-dtags -lpgport -lz -lreadline -lcrypt -ldl -lm -o pgbench [smurf:/home/bitmyer/postgresql-9.2.4/contrib/pgbench$] make install /bin/mkdir -p '/home/bitmyer/work/postgres/bin' /bin/sh ../../config/install-sh -c pgbench '/home/bitmyer/work/postgres/bin' [smurf:/home/bitmyer/postgresql-9.2.4/contrib/pgbench$] [smurf:/home/bitmyer/postgresql-9.2.4/contrib/pgbench$] pgbench -p 7777 -i mydb creating tables... 10000 tuples done. 20000 tuples done. 30000 tuples done. 40000 tuples done. 50000 tuples done. 60000 tuples done. 70000 tuples done. 80000 tuples done. 90000 tuples done. 100000 tuples done. set primary key... NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts" vacuum...done. [smurf:/home/bitmyer/postgresql-9.2.4/contrib/pgbench$] psql -p 7777 mydb psql (9.2.4) Type "help" for help. mydb=# select pg_stat_statements_reset(); pg_stat_statements_reset -------------------------- (1 row) mydb=# mydb=# q [smurf:/home/bitmyer/postgresql-9.2.4/contrib/pgbench$] pgbench -p 7777 -c10 -t300 -M prepared mydb starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 1 number of transactions per client: 300 number of transactions actually processed: 3000/3000 tps = 108.174186 (including connections establishing) tps = 108.225100 (excluding connections establishing) [smurf:/home/bitmyer/postgresql-9.2.4/contrib/pgbench$]
4. pg_stat_statements view 를 통해 통계 결과 조회
[smurf:/home/bitmyer/postgresql-9.2.4/contrib/pgbench$] psql -p 7777 mydb psql (9.2.4) Type "help" for help. mydb=# x Expanded display is on. mydb=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3; -[ RECORD 1 ]-------+--------------------------------------------------------------------- userid | 10 dbid | 16384 query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; calls | 3000 total_time | 124051.534999999 rows | 3000 shared_blks_hit | 30467 shared_blks_read | 8 shared_blks_dirtied | 8 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 2 ]-------+--------------------------------------------------------------------- userid | 10 dbid | 16384 query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; calls | 3000 total_time | 116025.483 rows | 3000 shared_blks_hit | 45094 shared_blks_read | 6 shared_blks_dirtied | 6 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 3 ]-------+--------------------------------------------------------------------- userid | 1
