Ideal pgbench settings to test pgagroal?

[marklit]

I've run the following against straight PG (TPS: 389), pgbouncer (TPS: 305) and pgagroal (TPS: 278).

PGPASSWORD=test \
pgbench --host=127.0.0.1 \
        --port=6433 \
        --client=100 \
        --jobs=100 \
        --transactions=1000 \
        intel

I would expect a higher TPS from a pooler but straight PG beats pgagroal out. I set up the initial db with pgbench -i intel. Am I missing something?

This is my pgagroal config:

[pgagroal]
host = *
port = 2345

log_type = file
log_level = info
log_path = /tmp/pgagroal.log

max_connections = 100
idle_timeout = 600
validation = off
unix_socket_dir = /tmp/

[primary]
host = localhost
port = 5432

[jesperpedersen]

Adding a layer in your architecture will add an overhead, so you need to consider if the functionality of that layer is needed.

You only need to set max_connections as the other defaults should be ok for most systems - everything is documented in https://agroal.github.io/pgagroal/configuration.html. As for pgbench and PostgreSQL itself there are numerous articles on how to performance test a system.

Do you have a specific use-case in mind that you are testing for ? Or a specific feature of a connection pool deployment ?

[marklit]

I was under the impression that creating new connections to PG has significant overheads. The above was a part of a benchmark I was working on comparing a few PG poolers and comparing their differences. I'm beginning to understand that the use case for poolers is in much more complex setups.

[jesperpedersen]

You can test the effect of prefilling the pool using the -u + -l options.

Having existing connections ready will have a bigger impact on the overall performance if you test the setup across a network - plus it is a good idea to scale the benchmark to a point where the machines are max'ed out with the technologies used (entire stack) - e.g. increasing the number of clients doesn't improve overall TPS.

https://agroal.github.io/pgagroal/configuration.html provides the available configuration options, and the "About" section on the web-site has some presentations and slides on the various subjects within the pooling space.

Hope this helps !