Pgpool Issues – Part 2

Please read Part 1 of this series by clicking here https://klouddb.io/pgpool-issues/ . NOTE : This is Part 2 of the PgPool series

Why am I getting bad performance with PgPool?

This is one of the common questions people have . Why is my app slower after introducing Pgpool ? Some queries will be slow because of additional hops as explained below

Without PgPool a query time is broken down into :

  • Time to send a query string to PostgreSQL
  • Time to process the query on PostgreSQL
  • Time to return the query result

With PgPool a query time is broken down into :

  • Time to send a query string to Pgpool
  • Time to parse the query on Pgpool
  • Time to send a query string to PostgreSQL
  • Time to process the query on PostgreSQL
  • Time to return the query result to Pgpool
  • Time to return the query result to client

Reference : Pgpool docs

Benchmarking with and without Pgpool - Let us run through some scenarios :

Scenario 1 – In the first run below we are running against PgPool (Port 9999) and as you can see TPS is 1902 (300 seconds is the test duration and we did not opt for multiple clients for this run . Second run is without Pgpool (Port 5432) and as you can see TPS is 2377 . So 1902 Vs 2377 for this scenario . Latency average is 0.53 ms Vs 0.42 ms – Not a big difference considering the benefits you have with PgPool . Performance is better without PgPool in this case

root@ip-172-31-21-193:/home/ubuntu# pgbench -h 127.0.0.1 -p 9999 -U replicator -T 300 -S -n postgres    [With PgPool]
Password:
transaction type: 
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 300 s
number of transactions actually processed: 570649
latency average = 0.526 ms
tps = 1902.161420 (including connections establishing)
tps = 1902.208723 (excluding connections establishing)
root@ip-172-31-21-193:/home/ubuntu# pgbench -h 18.116.74.151 -p 5432 -U replicator -T 300 -S -n postgres [Without PgPool]
Password:
transaction type: 
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 300 s
number of transactions actually processed: 713236
latency average = 0.421 ms
tps = 2377.452309 (including connections establishing)
tps = 2377.485902 (excluding connections establishing)

Scenario 2 – Unlike scenario 1 we are using 50 clients in pgbench command here . . In the first run below we are running against PgPool (Port 9999) and as you can see TPS is 7606 ; 100 seconds is the test duration . Second run is without Pgpool (Port 5432) and as you can see TPS is 6902 . So 7606 Vs 6902 for this scenario – Performance is better with Pgpool in this scenario as you can see

pgbench -h 127.0.0.1 -p 9999 -U repusr -T 100 -c 50 -S -n postgres  [With PgPool]
Password:
transaction type: 
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 1
duration: 100 s
number of transactions actually processed: 760682
latency average = 6.573 ms
tps = 7606.456501 (including connections establishing)
tps = 7606.939303 (excluding connections establishing)
-bash-4.2$ pgbench -h 18.191.244.53 -p 5432 -U repusr -T 100 -c 50 -S -n postgres [Without PgPool]
Password:
transaction type: 
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 1
duration: 100 s
number of transactions actually processed: 690189
latency average = 7.244 ms
tps = 6901.839696 (including connections establishing)
tps = 6902.122694 (excluding connections establishing)

Scenario 3 – We are using 70 clients in pgbench command here . In the first run below we are running against PgPool (Port 9999) and as you can see TPS is 7227; 200 seconds is the test duration . Second run is without Pgpool (Port 5432) and as you can see TPS is 6616 . So 7227 Vs 6616 for this scenario – Performance is better with Pgpool in this scenario as you can see . But if you observe carefully , the difference is not much when compared to Scenario2 .

-bash-4.2$ pgbench -h 127.0.0.1 -p 9999 -U repusr -T 200 -c 70 -S -n postgres
Password:
transaction type: 
scaling factor: 1
query mode: simple
number of clients: 70
number of threads: 1
duration: 200 s
number of transactions actually processed: 1445483
latency average = 9.686 ms
tps = 7227.295484 (including connections establishing)
tps = 7227.502979 (excluding connections establishing)
-bash-4.2$ pgbench -h 18.191.244.53 -p 5432 -U repusr -T 200 -c 70 -S -n postgres
Password:
transaction type: 
scaling factor: 1
query mode: simple
number of clients: 70
number of threads: 1
duration: 200 s
number of transactions actually processed: 1323381
latency average = 10.579 ms
tps = 6616.636435 (including connections establishing)
tps = 6616.773233 (excluding connections establishing)

So if Pgpool makes my app slower , why should I use Pgpool ?

We have seen that people create streaming replication clusters(with multiple replicas) and only use one server (primary server) . PgPool can automatically load balance your queries and thereby utilize standby servers with minimal app changes. Another scenario could be devoting a replica for reporting purpose

Also PgPool is not slow in all cases . Depending on the queries and workload it can be faster than direct connections in some cases. So benchmark your workload with and without PgPool to see the results(Results vary depending on the workload)

User authentication error . How to fix it ?

ERROR: failed to authenticate with backend using md5

When you see “ERROR: failed to authenticate with backend using md5” , you might need to add the user to pool_passwd file . Check your Pgpool config file to see the value for this variable and take necessary action . Once you add entry to pool_passwd, this error should go away

Below is the default value for pool_passwd in the config file . /etc/pgpool2/pool_passwd is the default location of this file (Depending on Pgpool version). Just add a line for each user using md5 . e.g : replicator:xdsbsss (username:pass). NOTE: Always use encrypted passwords (Pg_enc can be used to accomplish this)

Below is the config variable related to pool_passwd . You can check this to know the location of this file on your server

My application is hung and I don't see any errors in PgPool logs(depending on the level of logging you opt for)

You started using Pgpool and one fine day you got paged for a production issue . You logon to the server and notice that your simple query is getting hung (Via PgPool) . Next thing you do is check Pgpool logs and there is nothing logged there (no clue from logs)

One reason for this could be your pool settings – num_init_children and max_pool . Below are two variables that you need to tune according to your workload

We covered almost 8 different PgPool issues in Part 1 and Part 2 of this series . Please stay tuned and we will post more issues in part 3

Share this Post :