Postgresql – PgPool: distro vs upstream

pgpoolpostgresql

I'm running PostgreSQL 9.6 and would like to trial pgpool. The yum repository shows the following options:

pgpool-II-96
postgresql-pgpool-II

Which version should i use ? The documentation on PgPool references the first option, but I've found posts on here that say I should use the PostgreSQL specific repository.

Also are there any useful tutorials on getting this up and running for a first time user, i find the main documentation at pgpool.net confusing?

When I start pgpool i get the following output.

2018-01-31 17:51:13: pid 41278: DEBUG:  I am 41278
2018-01-31 17:51:13: pid 41278: DEBUG:  initializing backend status
2018-01-31 17:51:13: pid 41278: DEBUG:  authenticate kind = 0
2018-01-31 17:51:13: pid 41278: FATAL:  unable to read data from DB node 0
2018-01-31 17:51:13: pid 41278: DETAIL:  EOF encountered with backend

Best Answer

It just depends on what one you want,

pgpool-II-96 is provided by pgpool.net
postgresql-pgpool-II is provided by your distro.

There is no right answer. Likely, I would go with distro-provided version. It's usually better integrated and I value that more than being more up to date.

As for,

FATAL: unable to read data from DB node 0

That's another question, but it sounds like a connectivity issue.

Related Solutions

Postgresql – PGPool Frequent Queries

I don't think these are issued by PGPool. I think they are instigated by your application (which I am guessing is written in PHP and running over some sort of ORM).

The first entry is DEALLOCATE which means effectively to tell the server to free up memory from a prepared statement. These look like they are issued through some PDO module.

The second and third queries look like they are ORM-related mapping queries. Your application is probably unware that these queries are being issued by the framework it is running on, but these queries only make sense really in an ORM or similar environment.

Postgresql – Postgres pgpool increase num_init_children

You're increasing num_init_children, but there's no sign you've set the PostgreSQL server backend's max_connections high enough to allow that number of connections from PgPool.

Please see the PgPool-II documentation to learn more about configuring PgPool-II. You might find this article useful.

You really don't want thousands of parallel queries to the backend database. What you need is for PgPool to queue them up and send them a few hundred at a time to PostgreSQL. Otherwise you'll tend to see terrible performance; see number of database connections in the Pg wiki. So you need to limit the number of back-end connections PgPool-II creates, while allowing lots of front-end connections. That means you want a low num_init_children. Personally I'd use something like:

num_init_children = 100                        
max_pool = 1

because, per the documentation:

If more than num_init_children clients try to connect to pgpool-II, they are blocked (not rejected) until a connection to any pgpool-II process is closed.

This is what you want for good performance.

That said, I use PgBouncer for this. I find it much simpler to work with, and it tends to perform very well. It also offers transaction-pooling mode, which can be really useful when you have applications that don't close connections, instead leaving them idle for ages. If you're not using PgPool's cluster features I strongly recommend just using PgBouncer, it's way simpler.

Best Answer

Related Solutions

Postgresql – PGPool Frequent Queries

Postgresql – Postgres pgpool increase num_init_children

Related Question