tutorial pgbouncer, postgresql & repmgr pada centos 7

halo sobat sekolahlinux, lama tidak membuat artikel nih saya 😀 hehe, dan sekarang saya coba iseng-iseng membuat artikel tentang tutorial menggunakan pgbouncer pada postgresql dan repmgr pada server centos 7

dalam tutorial kali ini kita menggunakan 3 buah node ya, detailnya untuk apa saja bisa kalian lihat dibawah

• pgbouncer = 192.168.10.10
• node1 (postgresql+repmgr master node) = 192.168.10.20
• node2 (postgresql+repmgr slave node) = 192.168.10.30

node1 dan node2 kita gunakan untuk postgresql & repmgr, untuk tutorial postgresql kalian bisa mengikuti tutorial dibawah ini

• https://sekolahlinux.com/tutorial-postgresql-9-6-pada-centos-7-part-2-master-slave-repmgr-with-auto-failover/

jika sudah setup postgresql+repmgr selanjutnya login pada node1/node master repmgr, masuk pada mode user postgres

su - postgres

create user test_user dan db test_db

createuser -d test_user
createdb test_db -O test_user

selanjutnya set password untuk user test_user yang tadi kita buat

psql -c "alter user test_user with password '123456'"

selanjutnya lakukan perintah dibawah pada node1 dan node2, buka file pg_hba.conf, dan tambahkan paramater ini dibaris paling bawah

host    test_db       test_user   192.168.10.0/24         password

jika sudah save lalu restart service postgresql pada node1 dan node2

service postgresql-9.6 restart

selanjutnya kita masuk ke node pgbouncer untuk install repo postgresql 9.6

#install repo postgresql 9.6 pada centos 7
rpm -Uvh https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

#install postgresql 9.6
yum install postgresql96-server postgresql96 repmgr96 -y

#install pgbouncer dan pgcontrib
yum install -y postgresql92-contrib pgbouncer

selanjutnya buat symlink agar perintah pgbench nantinya bisa digunakan dengan mudah

ln -s /usr/pgsql-9.6/bin/pgbench /usr/sbin/pgbench

selanjutnya buka file pgbouncer.ini lalu lakukan perubahan jika ada paramater yang isinya berbeda dan juga penambahan jika paramater tersebut belum ada, menjadi seperti dibawah

[databases]
test_db = host=192.168.10.20 port=5432 dbname=test_db

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

listen_addr = *
listen_port = 6432

auth_type = plain
auth_file = /etc/pgbouncer/userlist.txt
admin_users = test_user
stats_users = stats, postgres, test_user

pool_mode = session
max_client_conn = 1000
default_pool_size = 20

buat file userlist.txt

vim /etc/pgbouncer/userlist.txt

tambahkan paramater ini didalamnya

"test_user" "123456"

jika sudah save lalu restart service pgbouncer

service pgbouncer restart

selanjutnya kita akan coba melakukan benchmark pada postgresql pada node pgbouncer, pada proses benchmark ini kita tidak diizinkan didalam mode root, jadi masuk kedalam mode user pgbouncer

su - pgbouncer

jika sudah selanjutnya sebelum kita melakukan benchmark kita harus generate beberapa table untuk yang nantinya akan digunakan untuk dalam proses benchmark, ikutin perintah dibawah untuk generate tablenya, jika diminta password masukan password yang kita buat untuk user test_user

pgbench -h 192.168.10.20 -p 5432 -i -s 10 -U test_user test_db

hasilnya akan seperti dibawah

Password:
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.07 s, remaining 0.63 s)
200000 of 1000000 tuples (20%) done (elapsed 0.21 s, remaining 0.86 s)
300000 of 1000000 tuples (30%) done (elapsed 0.49 s, remaining 1.14 s)
400000 of 1000000 tuples (40%) done (elapsed 1.13 s, remaining 1.70 s)
500000 of 1000000 tuples (50%) done (elapsed 1.78 s, remaining 1.78 s)
600000 of 1000000 tuples (60%) done (elapsed 2.51 s, remaining 1.67 s)
700000 of 1000000 tuples (70%) done (elapsed 3.36 s, remaining 1.44 s)
800000 of 1000000 tuples (80%) done (elapsed 3.94 s, remaining 0.98 s)
900000 of 1000000 tuples (90%) done (elapsed 4.80 s, remaining 0.53 s)
1000000 of 1000000 tuples (100%) done (elapsed 5.54 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

jika sudah selanjutkan coba kita benchmark dari node pgbouncer, pertama kita benchmark langsung ke server db postgresql tanpa melewati pgbouncer dengan perintah seperti dibawah, jika diminta password masukan password yang kita buat untuk user test_user

pgbench -h 192.168.10.20 -c 10 -C -T 60 -p 5432 -U test_user test_db

hasilnya akan seperti dibawah

Password:
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: 5087
latency average = 118.006 ms
tps = 84.741672 (including connections establishing)
tps = 91.730603 (excluding connections establishing)

jika sudah selanjutkan coba kita benchmark dari node pgbouncer, pertama kita benchmark langsung ke server db postgresql tanpa melewati pgbouncer dengan perintah seperti dibawah, jika diminta password masukan password yang kita buat untuk user test_user

pgbench -h 192.168.10.10 -c 10 -C -T 60 -p 6432 -U test_user test_db

hasilnya akan seperti dibawah

Password:
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: 26757
latency average = 22.429 ms
tps = 445.853658 (including connections establishing)
tps = 454.310900 (excluding connections establishing)

jadi dari hasil benchmark diatas pada jumlah koneksi 10 dan durasi 60 detik terlihat jelas bahwa jika kita menggunakan pgbouncer hasil tps nya lebih besar dan latency nya juga lebih kecil,

bagaimana jika node1 repmgr down, yang harus kalian lakukan pertama kali adalah melakukan promote master pada node2 repmgr slave agar menjadi master untuk caranya ada pada tutorial link rpmgr diatas, lalu rubah host pada pengaturan db di pgbouncer.ini jika sudah refresh rulenya dengan perintah dibawah ini

pgbouncer -d pgbouncer.ini -R

sekian tutorial kali ini 😀