Intro to pgBouncer

* Pools are a combination of a user and a db

* doug@hr == pool #1

* doug@security == pool #2

* cj@hr == pool #3

* You can create multiple pools in PgBouncer

* and they can have different settings

* including connection limits

* and timeouts

* Nothing stops you from putting a pooler in front of another pooler

* You can still use VIP and CNAME tricks with PgBouncer for HA !

PgBouncer presents it's internals as a db you can connect to:

doug@ReturnOfTheMac ~ » psql -p 6432 -d pgbouncer -U pgbouncer -X

psql (9.6.3, server 1.7.2/bouncer)

Type "help" for help.

pgbouncer=#

pgbouncer=# show databases;

name | host | port | database | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections

-----------+-----------+------+-----------+------------+-----------+--------------+-----------+-----------------+---------------------

doug | 127.0.0.1 | 5496 | doug | | 20 | 0 | | 0 | 2

pgbouncer | | 6432 | pgbouncer | pgbouncer | 2 | 0 | statement | 0 | 0

(2 rows)

pgbouncer=# show pools;

database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | pool_mode

-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+-------------

doug | doug | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | transaction

doug | pgbouncer | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | transaction

pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement

(3 rows)

Once connected to PgBouncer's internal 'db' you can:

* RELOAD pgbouncer.ini

* PAUSE/RESUME connections to a database

* DISABLE/ENABLE connections to a database

You can also SUSPEND and RESUME the whole of PgBouncer. Can be useful for maintenance tasks. Simply suspend things, do your work, resume things. To the application, it just looks like the connection was stupid slow for a while.

You can also SHUTDOWN the pooler without shell access if you're feeling devious

PgBouncer's internal "db"

Getting fancy

PgBouncer's internal "db"

Use this database to see what PgBouncer is doing:

* SHOW DATABASES/POOLS/CLIENTS/SERVERS

* SHOW STATS/SOCKETS/MEM

JBOSS

Things to Remember

Tips & Tricks

Hide yo kids, Hide yo wife, Hide yo db servers:

[databases]

hr = host=192.168.1.11 auth_user=pgbouncer

payroll = host=192.168.1.12 auth_user=pgbouncer

security = host=192.168.1.11 port=5444 auth_user=pgbouncer

Your application simply points to PgBouncer on 192.168.1.10 and is none-the-wiser!

Fix legacy naming/typos transparently:

[databases]

barr = host=192.168.1.11 dbname=bar auth_user=pgbouncer

Force connection params without code changes:

[databases]

* = host=1902.168.11 auth_user=pgbouncer client_encoding=UNICODE datestyle=ISO

Edit pgbouncer.ini

[databases]

* = host=192.168.1.11 auth_user=pgbouncer

[pgbouncer]

pool_mode = transaction

listen_port = 6543

listen_addr = 127.0.0.1

auth_type = md5

auth_file = authusers.txt

auth_query = SELECT uname, phash FROM user_lookup($1)

logfile = /var/log/pgbouncer.log

pidfile = /run/pgbouncer.pid

admin_users = postgres,doug

stats_users = postgres,doug

c3p0

Prepare for auth_user

edit authuser.txt

pgbouncer@(none):doug> SELECT * FROM user_lookup('pgbouncer');

| uname | phash

| pgbouncer | md509d12ff67352814e4c467c7f55a3a1d7

doug@ReturnOfTheMac ~ » echo "\"pgbouncer\" \"md509d12ff67352814e4c467c7f55a3a1d7\"" > authuser.txt

doug@ReturnOfTheMac ~ » cat authuser.txt

"pgbouncer" "md509d12ff67352814e4c467c7f55a3a1d7"

Create a pgbouncer user:

postgres@(none):doug> CREATE USER pgbouncer ENCRYPTED PASSWORD 'secret';

Create a user lookup function:

postgres@(none):doug> CREATE OR REPLACE FUNCTION user_lookup(in i_username text, out uname text, out phash text)

RETURNS record AS $$

BEGIN

SELECT usename, passwd FROM pg_catalog.pg_shadow WHERE usename = i_username INTO uname, phash;

RETURN;

END;

$$ LANGUAGE plpgsql SECURITY DEFINER;

postgres@(none):doug> REVOKE ALL ON FUNCTION user_lookup(text) FROM public, pgbouncer;

postgres@(none):doug> GRANT EXECUTE ON FUNCTION user_lookup(text) TO pgbouncer;

Pool modes

PgBouncer shares connections in one of three pool modes:

* Session pooling – When a client connects, a connection is assigned to it as long as it remains connected. When the client disconnects, the connection is placed back into the pool.

* Transaction pooling – A connection is assigned to a client for the duration of a transaction. When PgBouncer notices the transaction is done, the connection is placed back into the pool. This mode can be used only with applications that do not use features that depend upon a session.

* Statement pooling – Statement pooling is like transaction pooling, but multi-statement transactions are not allowed.

A default pool mode can be set for the PgBouncer instance and the mode can be overridden for individual databases and users.

No, for reals

PgPool-II

13Mb, in memory dataset, select-only pgbench

$> pgbench -i -s 1 bench

$> pgbench --select-only --connect -T300 -c8 -j2 bench

* no pooling 356 avg. TPS

* PgBouncer 6626 avg. TPS (17x general improvement)

Intro to PgBouncer

Before

Other

choices

* TCP startup / teardown

* SYNWAIT

* ~0.5Kb inside PostgreSQL

* 1 connection == 1 process

PostgreSQL Connection Pooling