pgBouncer 설치

pgBouncer 설치에 대해 설명한다.

 

설치

시나리오

1. 의존 라이브러리 설치
2. 소스 다운로드
3. configure
4. make
5. make install 
6. pgbouncer 설치 확인
7. pgbouncer.ini 파일 생성
8. users.txt 파일 생성 
9. pgbouncer 실행
10. pgbouncer 접속
11. pgbouncer 관리
12. pgbench 테스트
13. 10 클라이언트 테스트
14. 80 클라이언트 테스트

다음은 위 시나리오를 실행한 명령어이다.

1.의존성 설치
$ dnf install libevent-devel pkgconfig
 
2.소스다운로드
[bylee@bylee5 install]$ wget https://github.com/pgbouncer/pgbouncer/releases/download/pgbouncer_1_9_0/pgbouncer-1.9.0.tar.gz
[bylee@bylee5 install]$ tar xvfz pgbouncer-1.9.0.tar.gz
[bylee@bylee5 install]$ cd pgbouncer-1.9.0/
[bylee@bylee5 pgbouncer-1.9.0]$ ll
합계 448
-rw-r--r--. 1 bylee bylee    762  7월 27 06:33 AUTHORS
-rw-r--r--. 1 bylee bylee    831  7월 19 13:57 COPYRIGHT
-rw-r--r--. 1 bylee bylee   3685  7월 27 06:33 Makefile
-rw-r--r--. 1 bylee bylee  39210  8월 13 15:55 NEWS.rst
-rw-r--r--. 1 bylee bylee   4936  7월 23 20:27 README.rst
-rwxr-xr-x. 1 bylee bylee     42  7월 27 06:33 autogen.sh
-rwxr-xr-x. 1 bylee bylee  42938  8월 13 15:59 config.guess
-rw-r--r--. 1 bylee bylee   1500  7월 27 06:33 config.mak.in
-rwxr-xr-x. 1 bylee bylee  35994  8월 13 15:59 config.sub
-rwxr-xr-x. 1 bylee bylee 249856  8월 13 15:59 configure
-rw-r--r--. 1 bylee bylee   5198  8월  8 05:07 configure.ac
drwxr-xr-x. 2 bylee bylee   4096 11월  5 16:44 debian
drwxr-xr-x. 2 bylee bylee   4096 11월  5 16:44 doc
drwxr-xr-x. 2 bylee bylee   4096 11월  5 16:44 etc
drwxr-xr-x. 2 bylee bylee   4096 11월  5 16:44 include
-rwxr-xr-x. 1 bylee bylee  14675  8월 13 15:59 install-sh
drwxr-xr-x. 5 bylee bylee   4096 11월  5 16:44 lib
drwxr-xr-x. 2 bylee bylee   4096  8월 13 16:00 src
drwxr-xr-x. 3 bylee bylee   4096 11월  5 16:44 test
drwxr-xr-x. 2 bylee bylee   4096 11월  5 16:44 win32
 
3.configure
[bylee@bylee5 pgbouncer-1.9.0]$ ./configure --prefix=$(pwd)
checking target host type... unix
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for a BSD-compatible install... /usr/bin/install -c
checking for grep that handles long lines and -e... /usr/bin/grep
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for gcc option to accept ISO C99... none needed
checking for gcc option to accept ISO Standard C... (cached) none needed
checking how to run the C preprocessor... gcc -E
checking whether compiler supports __func__... yes
checking whether linker supports --as-needed... yes
checking whether compiler supports dependency generation... yes
checking for working warning switches... done
checking whether ln -s works... yes
checking for egrep... /usr/bin/grep -E
checking for gawk... gawk
checking for a sed that does not truncate output... /usr/bin/sed
checking for strip... strip
checking for ranlib... ranlib
checking for ar... ar
checking for pkg-config... /usr/bin/pkg-config
checking pkg-config is at least version 0.9.0... yes
checking for rst2man... no
checking for strip... (cached) strip
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for inttypes.h... (cached) yes
checking stdbool.h usability... yes
checking stdbool.h presence... yes
checking for stdbool.h... yes
checking for unistd.h... (cached) yes
checking sys/time.h usability... yes
checking sys/time.h presence... yes
checking for sys/time.h... yes
checking sys/socket.h usability... yes
checking sys/socket.h presence... yes
checking for sys/socket.h... yes
checking poll.h usability... yes
checking poll.h presence... yes
checking for poll.h... yes
checking sys/poll.h usability... yes
checking sys/poll.h presence... yes
checking for sys/poll.h... yes
checking sys/un.h usability... yes
checking sys/un.h presence... yes
checking for sys/un.h... yes
checking arpa/inet.h usability... yes
checking arpa/inet.h presence... yes
checking for arpa/inet.h... yes
checking netinet/in.h usability... yes
checking netinet/in.h presence... yes
checking for netinet/in.h... yes
checking netinet/tcp.h usability... yes
checking netinet/tcp.h presence... yes
checking for netinet/tcp.h... yes
checking sys/param.h usability... yes
checking sys/param.h presence... yes
checking for sys/param.h... yes
checking sys/uio.h usability... yes
checking sys/uio.h presence... yes
checking for sys/uio.h... yes
checking pwd.h usability... yes
checking pwd.h presence... yes
checking for pwd.h... yes
checking grp.h usability... yes
checking grp.h presence... yes
checking for grp.h... yes
checking sys/wait.h usability... yes
checking sys/wait.h presence... yes
checking for sys/wait.h... yes
checking sys/mman.h usability... yes
checking sys/mman.h presence... yes
checking for sys/mman.h... yes
checking syslog.h usability... yes
checking syslog.h presence... yes
checking for syslog.h... yes
checking netdb.h usability... yes
checking netdb.h presence... yes
checking for netdb.h... yes
checking dlfcn.h usability... yes
checking dlfcn.h presence... yes
checking for dlfcn.h... yes
checking err.h usability... yes
checking err.h presence... yes
checking for err.h... yes
checking pthread.h usability... yes
checking pthread.h presence... yes
checking for pthread.h... yes
checking endian.h usability... yes
checking endian.h presence... yes
checking for endian.h... yes
checking sys/endian.h usability... no
checking sys/endian.h presence... no
checking for sys/endian.h... no
checking byteswap.h usability... yes
checking byteswap.h presence... yes
checking for byteswap.h... yes
checking malloc.h usability... yes
checking malloc.h presence... yes
checking for malloc.h... yes
checking regex.h usability... yes
checking regex.h presence... yes
checking for regex.h... yes
checking getopt.h usability... yes
checking getopt.h presence... yes
checking for getopt.h... yes
checking fnmatch.h usability... yes
checking fnmatch.h presence... yes
checking for fnmatch.h... yes
checking langinfo.h usability... yes
checking langinfo.h presence... yes
checking for langinfo.h... yes
checking xlocale.h usability... no
checking xlocale.h presence... no
checking for xlocale.h... no
checking linux/random.h usability... yes
checking linux/random.h presence... yes
checking for linux/random.h... yes
checking for ucred.h... no
checking for sys/ucred.h... no
checking sys/resource.h usability... yes
checking sys/resource.h presence... yes
checking for sys/resource.h... yes
checking for sys/wait.h... (cached) yes
checking for inline... inline
checking for C/C++ restrict keyword... __restrict
checking whether byte ordering is bigendian... no
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking for pid_t... yes
checking for uid_t in sys/types.h... yes
checking for size_t... yes
checking for uint8_t... yes
checking for uint32_t... yes
checking for uint64_t... yes
checking for strlcpy... no
checking for strlcat... no
checking for strnlen... yes
checking for strsep... yes
checking for getpeereid... no
checking for sigaction... yes
checking for sigqueue... yes
checking for memmem... yes
checking for memrchr... yes
checking for mempcpy... yes
checking for inet_ntop... yes
checking for inet_pton... yes
checking for poll... yes
checking for getline... yes
checking for regcomp... yes
checking for err... yes
checking for errx... yes
checking for warn... yes
checking for warnx... yes
checking for getprogname... no
checking for setprogname... no
checking for posix_memalign... yes
checking for memalign... yes
checking for valloc... yes
checking for explicit_bzero... yes
checking for memset_s... no
checking for reallocarray... yes
checking for getopt... yes
checking for getopt_long... yes
checking for getopt_long_only... yes
checking for fls... no
checking for flsl... no
checking for flsll... no
checking for ffs... yes
checking for ffsl... yes
checking for ffsll... yes
checking for fnmatch... yes
checking for mbsnrtowcs... yes
checking for nl_langinfo... yes
checking for strtod_l... yes
checking for strtonum... no
checking for asprintf... yes
checking for vasprintf... yes
checking for timegm... yes
checking for localtime_r... yes
checking for gettimeofday... yes
checking for recvmsg... yes
checking for sendmsg... yes
checking for usleep... yes
checking for getrusage... yes
checking for syslog... yes
checking for mmap... yes
checking for getpeerucred... no
checking for arc4random_buf... no
checking for getentropy... yes
checking for getrandom... yes
checking for library containing WSAGetLastError... no
checking whether strerror_r is declared... yes
checking for strerror_r... yes
checking whether strerror_r returns char *... yes
checking for integer enc/dec functions... not found
checking for library containing clock_gettime... none required
checking for library containing getsockname... none required
checking for library containing gethostbyname... none required
checking for library containing hstrerror... none required
checking for lstat... yes
checking for libevent... found
checking for event_loopbreak... yes
checking for event_base_new... yes
checking for evdns_base_new... yes
checking whether to use c-ares for DNS lookups... auto
checking for libcares >= 1.6.0... no
checking whether to use libudns... no
checking whether to use libevent for DNS lookups... yes
checking for OpenSSL... found
checking for SSL_CTX_use_certificate_chain_mem... no
checking for SSL_CTX_load_verify_mem... no
checking for asn1_time_parse... no
checking for root CA certs... /etc/ssl/cert.pem
checking whether to build debug binary... yes
checking whether to enable asserts... no
checking whether to fail on warnings... no
configure: creating ./config.status
config.status: creating config.mak
config.status: creating lib/usual/config.h
config.status: lib/usual/config.h is unchanged
 
Results
  c-ares = no
  evdns = yes
  udns = no
  tls = yes
  PAM = no
 
[bylee@bylee5 pgbouncer-1.9.0]$
 
4. make
[bylee@bylee5 pgbouncer-1.9.0]$ make
     CC       src/admin.c
     CC       src/client.c
     CC       src/dnslookup.c
     CC       src/hba.c
     CC       src/janitor.c
     CC       src/loader.c
     CC       src/main.c
     CC       src/objects.c
     CC       src/pam.c
     CC       src/pktbuf.c
     CC       src/pooler.c
     CC       src/proto.c
     CC       src/sbuf.c
     CC       src/server.c
     CC       src/stats.c
     CC       src/system.c
     CC       src/takeover.c
     CC       src/util.c
     CC       src/varcache.c
     CC       lib/usual/aatree.c
     CC       lib/usual/base.c
     CC       lib/usual/cbtree.c
     CC       lib/usual/cfparser.c
     CC       lib/usual/crypto/chacha.c
     CC       lib/usual/crypto/csrandom.c
     CC       lib/usual/crypto/digest.c
     CC       lib/usual/crypto/entropy.c
     CC       lib/usual/crypto/keccak.c
     CC       lib/usual/crypto/keccak_prng.c
     CC       lib/usual/crypto/md5.c
     CC       lib/usual/cxalloc.c
     CC       lib/usual/cxextra.c
     CC       lib/usual/err.c
     CC       lib/usual/event.c
     CC       lib/usual/fileutil.c
     CC       lib/usual/getopt.c
     CC       lib/usual/hashing/lookup3.c
     CC       lib/usual/heap.c
     CC       lib/usual/list.c
     CC       lib/usual/logging.c
     CC       lib/usual/mbuf.c
     CC       lib/usual/mempool.c
     CC       lib/usual/netdb.c
     CC       lib/usual/pgutil.c
     CC       lib/usual/regex.c
     CC       lib/usual/safeio.c
     CC       lib/usual/signal.c
     CC       lib/usual/slab.c
     CC       lib/usual/socket.c
     CC       lib/usual/socket_ntop.c
     CC       lib/usual/socket_pton.c
     CC       lib/usual/string.c
     CC       lib/usual/strpool.c
     CC       lib/usual/time.c
     CC       lib/usual/tls/tls.c
     CC       lib/usual/tls/tls_cert.c
     CC       lib/usual/tls/tls_client.c
     CC       lib/usual/tls/tls_compat.c
     CC       lib/usual/tls/tls_config.c
     CC       lib/usual/tls/tls_conninfo.c
     CC       lib/usual/tls/tls_ocsp.c
     CC       lib/usual/tls/tls_peer.c
     CC       lib/usual/tls/tls_server.c
     CC       lib/usual/tls/tls_util.c
     CC       lib/usual/tls/tls_verify.c
     CCLD     pgbouncer
 
5. make install
[bylee@bylee5 pgbouncer-1.9.0]$ make install
     INSTALL  pgbouncer /home/bylee/install/pgbouncer-1.9.0/bin
     INSTALL  README.rst /home/bylee/install/pgbouncer-1.9.0/share/doc/pgbouncer
     INSTALL  NEWS.rst /home/bylee/install/pgbouncer-1.9.0/share/doc/pgbouncer
     INSTALL  etc/pgbouncer.ini /home/bylee/install/pgbouncer-1.9.0/share/doc/pgbouncer
     INSTALL  etc/userlist.txt /home/bylee/install/pgbouncer-1.9.0/share/doc/pgbouncer
     INSTALL  doc/pgbouncer.1 /home/bylee/install/pgbouncer-1.9.0/share/man/man1
     INSTALL  doc/pgbouncer.5 /home/bylee/install/pgbouncer-1.9.0/share/man/man5
 
[bylee@bylee5 pgbouncer-1.9.0]$ ll
합계 2228
-rw-r--r--. 1 bylee bylee     762  7월 27 06:33 AUTHORS
-rw-r--r--. 1 bylee bylee     831  7월 19 13:57 COPYRIGHT
-rw-r--r--. 1 bylee bylee    3685  7월 27 06:33 Makefile
-rw-r--r--. 1 bylee bylee   39210  8월 13 15:55 NEWS.rst
-rw-r--r--. 1 bylee bylee    4936  7월 23 20:27 README.rst
-rwxr-xr-x. 1 bylee bylee      42  7월 27 06:33 autogen.sh
drwxrwxr-x. 2 bylee bylee    4096 11월  5 16:52 bin
-rwxr-xr-x. 1 bylee bylee   42938  8월 13 15:59 config.guess
-rw-rw-r--. 1 bylee bylee  143578 11월  5 16:49 config.log
-rw-rw-r--. 1 bylee bylee    1798 11월  5 16:49 config.mak
-rw-r--r--. 1 bylee bylee    1500  7월 27 06:33 config.mak.in
-rwxrwxr-x. 1 bylee bylee   31312 11월  5 16:49 config.status
-rwxr-xr-x. 1 bylee bylee   35994  8월 13 15:59 config.sub
-rwxr-xr-x. 1 bylee bylee  249856  8월 13 15:59 configure
-rw-r--r--. 1 bylee bylee    5198  8월  8 05:07 configure.ac
drwxr-xr-x. 2 bylee bylee    4096 11월  5 16:44 debian
drwxr-xr-x. 2 bylee bylee    4096 11월  5 16:44 doc
drwxr-xr-x. 2 bylee bylee    4096 11월  5 16:44 etc
drwxr-xr-x. 2 bylee bylee    4096 11월  5 16:44 include
-rwxr-xr-x. 1 bylee bylee   14675  8월 13 15:59 install-sh
drwxr-xr-x. 5 bylee bylee    4096 11월  5 16:44 lib
-rwxrwxr-x. 1 bylee bylee 1627952 11월  5 16:52 pgbouncer
drwxrwxr-x. 4 bylee bylee    4096 11월  5 16:52 share
drwxr-xr-x. 2 bylee bylee    4096  8월 13 16:00 src
drwxr-xr-x. 3 bylee bylee    4096 11월  5 16:44 test
drwxr-xr-x. 2 bylee bylee    4096 11월  5 16:44 win32
 
 
6. pgbouncer 설치 확인
[bylee@bylee5 pgbouncer-1.9.0]$ bin/pgbouncer -V
pgbouncer version 1.9.0
 
-------------------2설정
7. pgbouncer.ini 파일 생성
[databases]
bylee = host=127.0.0.1 port=5432 dbname=bylee
pgbouncer_bench = host=127.0.0.1 port=5432 dbname=pgbouncer_bench
 
[pgbouncer]
listen_port = 6543
listen_addr = *
auth_type = trust
auth_file = users.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = bylee
 
8. users.txt 파일을 생성
"bylee" "1234"
 
9. pgbouncer 실행
[bylee@bylee5 pgbouncer-1.9.0]$ bin/pgbouncer -d pgbouncer.ini
2018-11-05 17:09:22.885 1753 LOG file descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 130
[bylee@bylee5 pgbouncer-1.9.0]$ ps -ef | grep pgbouncer
bylee     1755  7410  0 17:09 ?        00:00:00 bin/pgbouncer -d pgbouncer.ini
bylee     2603  5532  0 17:12 pts/5    00:00:00 grep --color=auto pgbouncer
 
 
10. pgbouncer 접속
[bylee@bylee5 pgbouncer-1.9.0]$ agens -p 6543 -U bylee -d bylee
agens (AgensGraph 1.4devel, based on PostgreSQL 10.3)
Type "help" for help.
 
bylee=#
 
 
11. pgbouncer 관리
[bylee@bylee5 pgbouncer-1.9.0]$ agens -p 6543 -U bylee -d pgbouncer
agens (10.3, server 1.9.0/bouncer)
Type "help" for help.
 
pgbouncer=# show help;
NOTICE:  Console usage
DETAIL: 
    SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
    SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
    SHOW DNS_HOSTS|DNS_ZONES
    SHOW STATS|STATS_TOTALS|STATS_AVERAGES
    SET key = arg
    RELOAD
    PAUSE [<db>]
    RESUME [<db>]
    DISABLE <db>
    ENABLE <db>
    RECONNECT [<db>]
    KILL <db>
    SUSPEND
    SHUTDOWN
 
SHOW
pgbouncer=#
 
6) pgbouncer.ini 파일이 변경되면 재로드할 수 있다.
pgbouncer=# reload;
RELOAD
 
 
 
 
------------------
12.pgbench 테스트
[bylee@bylee5 pgbouncer-1.9.0]$ createdb pgbouncer_bench
[bylee@bylee5 pgbouncer-1.9.0]$ pgbench -i -s 10 pgbouncer_bench
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.26 s, remaining 2.35 s)
200000 of 1000000 tuples (20%) done (elapsed 0.42 s, remaining 1.67 s)
300000 of 1000000 tuples (30%) done (elapsed 0.82 s, remaining 1.91 s)
400000 of 1000000 tuples (40%) done (elapsed 1.24 s, remaining 1.86 s)
500000 of 1000000 tuples (50%) done (elapsed 1.46 s, remaining 1.46 s)
600000 of 1000000 tuples (60%) done (elapsed 1.80 s, remaining 1.20 s)
700000 of 1000000 tuples (70%) done (elapsed 2.25 s, remaining 0.96 s)
800000 of 1000000 tuples (80%) done (elapsed 2.30 s, remaining 0.58 s)
900000 of 1000000 tuples (90%) done (elapsed 2.79 s, remaining 0.31 s)
1000000 of 1000000 tuples (100%) done (elapsed 3.26 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
[bylee@bylee5 pgbouncer-1.9.0]$
 
13. 10 클라이언트 테스트
[bylee@bylee5 pgbouncer-1.9.0]$ pgbench -c 10 -C -T 60 pgbouncer_bench
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 1
duration: 60 s
number of transactions actually processed: 29125
latency average = 20.604 ms
tps = 485.349253 (including connections establishing)
tps = 528.305051 (excluding connections establishing)
[bylee@bylee5 pgbouncer-1.9.0]$ pgbench -c 10 -C -T 60 -p 6543 pgbouncer_bench
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 1
duration: 60 s
number of transactions actually processed: 51425
latency average = 11.669 ms
tps = 856.965336 (including connections establishing)
tps = 894.628804 (excluding connections establishing)
[bylee@bylee5 pgbouncer-1.9.0]$
 
14. 80 클라이언트 테스트
[bylee@bylee5 pgbouncer-1.9.0]$ pgbench -c 80 -C -T 60 pgbouncer_bench
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 80
number of threads: 1
duration: 60 s
number of transactions actually processed: 24654
latency average = 194.933 ms
tps = 410.398408 (including connections establishing)
tps = 415.117490 (excluding connections establishing)
[bylee@bylee5 pgbouncer-1.9.0]$ pgbench -c 80 -C -T 60 -p 6543 pgbouncer_bench
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 80
number of threads: 1
duration: 60 s
number of transactions actually processed: 61237
latency average = 78.466 ms
tps = 1019.554507 (including connections establishing)
tps = 1026.745048 (excluding connections establishing)