pgbench를 통한 Postgresql 성능 분석
pgbench란...
pgbench는 PostgreSQL이 기본적으로 제공하는 Benchmark Tool이다. 기본적으로 Select, Insert, Update등의 command를 조합해서 simulate하고, 이를 통해 얻어지는 TPC-B라 불리는 초당 Transaction 횟수로 성능을 평가한다. 즉, 현재 설치되어 있는 HW 및 OS 환경에서 일정 Pattern의 Transaction을 수행해서 성능을 측정한다. DB와 연동하는 Application들의 특성 등은 고려하지 않는다는 단점이 있으나 사용 방법이 쉬워서 필자의 경우에는 postgreSQL을 처음 설치할 때 postgres.conf에 있는 몇몇 환경 변수 값을 정하는데 사용한다.
설치
예전에는 postgres-contrib라는 Package에 포함되어 있어서 따로 설치했던 거 같은데(?), 11 버전에서는 server package에 포함되어 있는 것으로 보인다. 필자가 postgresql11과 postgresql11-server package만 install 했음에도 postgreSQL 실행 파일이 있는 곳에 pgbench가 아래와 같이 있다.
[root@localhost conf]# ls -la /usr/pgsql-11/bin
total 10028
drwxr-xr-x. 2 root root 4096 Jun 26 18:33 .
drwxr-xr-x. 5 root root 41 Jun 13 11:15 ..
...
-rwxr-xr-x. 1 root root 121976 Jun 20 02:51 initdb
-rwxr-xr-x. 1 root root 28888 Jun 20 02:51 pg_archivecleanup
-rwxr-xr-x. 1 root root 109816 Jun 20 02:51 pg_basebackup
-rwxr-xr-x. 1 root root 142984 Jun 20 02:51 pgbench
-rwxr-xr-x. 1 root root 28824 Jun 20 02:51 pg_config
-rwxr-xr-x. 1 root root 41344 Jun 20 02:51 pg_controldata
-rwxr-xr-x. 1 root root 54320 Jun 20 02:51 pg_ctl
-rwxr-xr-x. 1 root root 392656 Jun 20 02:51 pg_dump
...
다만, psql, pg_dump 등과 달리 설치 시에 전역에서 사용할 수 있는 Symbolic Link는 만들어지지 않아(/etc/alternative에서 확인 가능) 실행하려면 절대 경로를 다 명시해서 실행하거나, 별도로 Symbolic Link를 만들거나 아니면 path 설정을 통해 전역에서 실행 가능하게 한 후 실행해야 한다. (필자는 .bash_profile에서 path를 설정해서 사용하고 있다.)
pgbench 사용할 때 Option들은 아래와 같다. Option들은 여러가지가 있으나 초기화를 위한 -i, -s option과 실제 성능을 측정할 때 -c, -j, -t option이 주로 사용된다. 그리고 측정 대상인 DB server와의 connection을 위한 option은 psql과 동일한 common option을 사용한다.
[root@localhost ~]# /usr/pgsql-11/bin/pgbench --help
pgbench is a benchmarking tool for PostgreSQL.
Usage:
pgbench [OPTION]... [DBNAME]
Initialization options:
-i, --initialize invokes initialization mode
-I, --init-steps=[dtgvpf]+ (default "dtgvp")
run selected initialization steps
-F, --fillfactor=NUM set fill factor
-n, --no-vacuum do not run VACUUM during initialization
-q, --quiet quiet logging (one message each 5 seconds)
-s, --scale=NUM scaling factor
--foreign-keys create foreign key constraints between tables
--index-tablespace=TABLESPACE
create indexes in the specified tablespace
--tablespace=TABLESPACE create tables in the specified tablespace
--unlogged-tables create tables as unlogged tables
Options to select what to run:
-b, --builtin=NAME[@W] add builtin script NAME weighted at W (default: 1)
(use "-b list" to list available scripts)
-f, --file=FILENAME[@W] add script FILENAME weighted at W (default: 1)
-N, --skip-some-updates skip updates of pgbench_tellers and pgbench_branches
(same as "-b simple-update")
-S, --select-only perform SELECT-only transactions
(same as "-b select-only")
Benchmarking options:
-c, --client=NUM number of concurrent database clients (default: 1)
-C, --connect establish new connection for each transaction
-D, --define=VARNAME=VALUE
define variable for use by custom script
-j, --jobs=NUM number of threads (default: 1)
-l, --log write transaction times to log file
-L, --latency-limit=NUM count transactions lasting more than NUM ms as late
-M, --protocol=simple|extended|prepared
protocol for submitting queries (default: simple)
-n, --no-vacuum do not run VACUUM before tests
-P, --progress=NUM show thread progress report every NUM seconds
-r, --report-latencies report average latency per command
-R, --rate=NUM target rate in transactions per second
-s, --scale=NUM report this scale factor in output
-t, --transactions=NUM number of transactions each client runs (default: 10)
-T, --time=NUM duration of benchmark test in seconds
-v, --vacuum-all vacuum all four standard tables before tests
--aggregate-interval=NUM aggregate data over NUM seconds
--log-prefix=PREFIX prefix for transaction time log file
(default: "pgbench_log")
--progress-timestamp use Unix epoch timestamps for progress
--random-seed=SEED set random seed ("time", "rand", integer)
--sampling-rate=NUM fraction of transactions to log (e.g., 0.01 for 1%)
Common options:
-d, --debug print debugging output
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=USERNAME connect as specified database user
-V, --version output version information, then exit
-?, --help show this help, then exit
Report bugs to <pgsql-bugs@postgresql.org>.
성능 측정
아래에 필자가 pgbench를 사용하는 방법을 단계별로 정리해봤다.
Step 1 ; 성능 측정을 위한 임시 DB Instance 생성
기존에 있는 DB Instance를 사용해도 되지만 성능 측정을 위해 만들어지는 임시 Table들을 쉽게 정리하려면 임시 DB Instance를 생성하는 편이 좋다. 이 포스팅에서는 아래와 같이 'pgbenchtest'라는 table을 별도로 만들었다.
[root@localhost ~]# psql -h localhost -U postgres postgres
psql (11.4)
Type "help" for help.
# 'pgbenchtest' DB 생성
postgres=# CREATE DATABASE pgbenchtest OWNER postgres;
CREATE DATABASE
postgres=# \c pgbenchtest postgres
You are now connected to database "pgbenchtest" as user "postgres".
# 생성 직후에는 아무 Table이 존재하지 않는다.
pgbenchtest=# \dt
Did not find any relations.
Step 2 : DB Intance 'pgbenchtest'에 성능 Test를 위한 초기화 작업.
아래는 pgbench에 -i option을 사용해서 'pgbenchtest' DB instance에 Test를 위한 Table들을 생성하고, Data를 초기화 하는 예이다. 실행이 끝나면 pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers 등 4개의 Table이 만들어지게 된다.
# pgbench 초기화 이전 상태
pgbenchtest=# \dt
Did not find any relations.
pgbenchtest=# \q
#pgbench 초기화 실행
[root@localhost ~]# pgbench -h localhost -p 5432 -U postgres -i pgbenchtest
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.02 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
#pgbench 초기화 실행 이후 Test용 Table 들이 생성된 상황
pgbenchtest=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
(4 rows)
만들어진 내용을 좀 더 자세히 살펴 보면 아래와 같다. DB Instance의 전체 크기는 약 23MB, 제일 Size가 큰 Table은 pgbench_accounts이고 약 100,000 row의 Data를 가지고 있고 크기는 약 13MB이다. 이건 DB 성능을 분석하기에는 너무 작은 크기이다.
pgbenchtest=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+-------+----------+---------+-------------
public | pgbench_accounts | table | postgres | 13 MB |
public | pgbench_branches | table | postgres | 40 kB |
public | pgbench_history | table | postgres | 0 bytes |
public | pgbench_tellers | table | postgres | 40 kB |
(4 rows)
pgbenchtest=# SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables;
schemaname | relname | n_live_tup
------------+------------------+------------
public | pgbench_accounts | 100000
public | pgbench_history | 0
public | pgbench_branches | 1
public | pgbench_tellers | 10
(4 rows)
pgbenchtest=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
--------------------+------------+----------+-------------+-------------+-----------------------+---------+------------+------------------------------------------
pgbenchtest | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | | 23 MB | pg_default |
이런 문제를 해결하기 위해 -s option 을 사용해야 한다. -s 뒤에 적는 숫자만큼 Table의 tuple들이 숫자의 배수만큼 커지게 된다. 아래는 '-s 10' option을 추가했을 때의 예이다.
[root@localhost ~]# pgbench -h localhost -p 5432 -U postgres -i -s 10 pgbenchtest
dropping old tables...
creating tables...
generating data...
100000 of 1000000 tuples (10%) done (elapsed 0.02 s, remaining 0.16 s)
200000 of 1000000 tuples (20%) done (elapsed 0.04 s, remaining 0.14 s)
300000 of 1000000 tuples (30%) done (elapsed 0.33 s, remaining 0.76 s)
400000 of 1000000 tuples (40%) done (elapsed 0.48 s, remaining 0.73 s)
500000 of 1000000 tuples (50%) done (elapsed 0.92 s, remaining 0.92 s)
600000 of 1000000 tuples (60%) done (elapsed 0.96 s, remaining 0.64 s)
700000 of 1000000 tuples (70%) done (elapsed 1.29 s, remaining 0.55 s)
800000 of 1000000 tuples (80%) done (elapsed 1.56 s, remaining 0.39 s)
900000 of 1000000 tuples (90%) done (elapsed 2.55 s, remaining 0.28 s)
1000000 of 1000000 tuples (100%) done (elapsed 3.52 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
[root@localhost ~]# psql -h localhost -U postgres pgbenchtest
pgbenchtest=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+-------+----------+---------+-------------
public | pgbench_accounts | table | postgres | 128 MB |
public | pgbench_branches | table | postgres | 40 kB |
public | pgbench_history | table | postgres | 0 bytes |
public | pgbench_tellers | table | postgres | 40 kB |
(4 rows)
pgbenchtest=# SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables;
schemaname | relname | n_live_tup
------------+------------------+------------
public | pgbench_tellers | 100
public | pgbench_history | 0
public | pgbench_branches | 10
public | pgbench_accounts | 1000000
(4 rows)
pgbenchtest=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
--------------------+------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
pgbenchtest | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | | 158 MB | pg_default |
각 Table의 row(live tuple)의 수가 딱 10배만큼 증가하게 된다. (실제 DB의 물리적인 크기는 배수만큼 증가하지는 않는다. 기존 Size는 23M 이지만, DB data를 10배 증가했을 때 실제 Size는 158M 이다.)
* 위의 예에서 사용한 option 외에도 '--foreign-keys'나 '--index-tablespace' option을 사용해 실제 DB 환경과 좀 더 유사하게 simulate하기도 한다.
Step 3. 성능 Test
Benchmarking option이 여러가지가 있으나 필자는 -c, -j, -t option 외에는 잘 사용하지 않았다. -c, -j, -t option 들은 각각 다음과 같다.
- -c : DB에 접속하는 가상의 client의 수를 설정
- -j : -c에서 설정한 client를 몇 개의 thread에 걸쳐 동작시킬 것인지 설정.
- -t : Simulate할 transaction의 수
-c와 -t에 의해 설정되는 값에 의해 전체 transaction의 수가 결정되고, -j는 pgbench에서 내부적으로 몇 개의 thread를 사용해 전체 client를 나눠서 simulate할 것인가를 결정한다. 아래의 예는 DB에 동시에 접속하는 Client가 8개, 각각의 Client가 10회의 Transaction을 수행했을 때의 예를 보여준다. (8개의 Client는 4개의 thread에 골고루 분산되어 Simulate된다.)
[root@localhost ~]# pgbench -h localhost -p 5432 -U postgres -c 8 -j 4 -t 10 pgbenchtest
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 4
number of transactions per client: 10
number of transactions actually processed: 80/80
latency average = 53.421 ms
tps = 149.753520 (including connections establishing)
tps = 150.478908 (excluding connections establishing)
8개의 Client가 10회씩 Transaction을 수행해서 전체 80회의 Transaction이 수행되었다는 점을 이해하면 된다.
또, 한가지 알아둘 점이 -c에서 사용한 값은 -j에서 사용한 값보다 항상 크거나 같아야된다. 그렇지 않으면 -j의 값은 -c 값으로 대체되게 된다.
전체 Transaction의 수가 작으면 동일한 환경에서도 변동폭이 좀 크게 나오는 경향이 있어 필자의 경우에는 Client와 Transaction의 수를 좀 크게 해서 성능을 구한다. (-j의 값은 CPU의 thread 수 만큼 설정한다.) 아래는 32개의 Client 에서 10,000회의 Transaction이 있을 때를 가정해서 구한 값이다.
[root@localhost ~]# pgbench -h localhost -p 5432 -U postgres -c 32 -j 8 -t 10000 pgbenchtest
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 32
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 320000/320000
latency average = 757.040 ms
tps = 42.269897 (including connections establishing)
tps = 42.269935 (excluding connections establishing)
위의 Test를 위해 pgbench를 사용한 환경은 다음과 같다.
- OS : RHEL 7 - 64 bit (Oracle Linux 7.3)
- Memory : 32G
- CPU : intel I7-6700 (4core / 8 thread)
- PostgreSQL Version : 11.1
- PostgreSQL 환경 변수는 초기값 그대로 사용.
- 전체 Test 소요 시간 : 약 3 시간
위와 같은 결과를 바탕으로 postgres.conf에 checkpoint, shared_buffers, wal_buffers, work_mem, max_worker_processes 등과 같이 Performance에 영향을 주는 parameter를 바꿔가면서 pgbench를 반복적으로 실행하면서 환경 인자들을 tuning하면 된다.
Step 4 : Test 환경 정리
pgbench를 마치면 성능 Test를 위해 만들어졌던 Table들을 정리해야 하는데 이를 위한 option은 없다. 때문에 Step 1과 같이 별도의 성능 측정을 위한 임시 DB Instance를 만들고 정리 시에 해당 DB Instance를 DROP command로 삭제하는 것이 가장 간단한 방법이다.
[root@localhost ~]# psql -h localhost -U postgres postgres
psql (11.4)
Type "help" for help.
postgres=# DROP DATABASE pgbenchtest;
DROP DATABASE
postgres=# \q
[root@localhost ~]개인적인 생각
pgbench는 간단하게 PostgreSQL의 성능을 측정하기 좋다. 하지만 TPC-B라는 측정 방법이 실제 Application에서 DB를 이용하는 방법과 차이가 크고, tuning시에 필요한 Detail한 정보를 얻기가 힘들어 '인자 변경'과 'pgbench 수행'을 다소 무식하게 반복해야 하는 경우가 많다. 특히 때때로 동일 HW에 동일한 환경 설정으로 Test를 해도 성능이 30% 이상 편차가 발생할 때가 있는데 이런 상황이면 사용할 수도 없다.(아직도 왜 그런 결과가 나왔는 지 모르겠다.) 이런저런 단점도 있고 제약도 있지만, 그래도 사용하기 쉽기 때문에 사용법을 알아두면 여러가지로 유용하다. 적어도 알아둬서 손해볼 일은 없을 것이다.