View Issue Details

IDProjectCategoryView StatusLast Update
0000520Pgpool-II[All Projects] Generalpublic2019-06-26 00:17
ReporterMaBikramAssigned Topengbo 
PrioritynormalSeverityminorReproducibilityalways
Status assignedResolutionopen 
PlatformLinux cemtore01 3.10.0-693.el7.xOSCentos 7.3OS Version
Product Version4.0.4 
Target VersionFixed in Version 
Summary0000520: Encountering - psql: ERROR: connection cache is full HINT: increase max_pool
DescriptionHi

I am trying to test pgpool-ii for a few scenarios - 1 Test if all new pgpool processes fire up if all the existing connections' processes are waiting or idle?
To do the above test - I did a bounce of postgresql and pgpool in the following manner:

========================================
[root@cemtore01 ~]# systemctl stop postgresql
[root@cemtore01 ~]# systemctl stop pgpool
[root@cemtore01 ~]# date
Thu Jun 6 07:50:32 CDT 2019
[root@cemtore01 ~]# systemctl start pgpool
[root@cemtore01 ~]# systemctl start postgresql
[root@cemtore01 ~]# date
Thu Jun 6 07:52:33 CDT 2019
========================================

pgpool is running on port 5432 and postgresql is running from port 5433. But, now when I try to run psql using pgpool I get the following error:
root@192.168.40.143's password:
Last login: Thu Jun 6 07:54:20 2019 from 192.168.40.110
[root@cemtore01 ~]# psql -p 5432 -U cemtore
psql: ERROR: connection cache is full
HINT: increase max_pool

Any idea what is going on?

Thanks
Bikram
TagsNo tags attached.

Activities

pengbo

2019-06-07 09:46

developer   ~0002643

I think the error message below has already been fixed in 4.0.5 by commit:
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=6e925e84a8cc49d724395471d14b2dc6af9ac07e

===
psql: ERROR: connection cache is full
HINT: increase max_pool
===

How did you install Pgpool-II?

MaBikram

2019-06-08 00:00

reporter   ~0002645

I did the RPM install:

yum install pgpool-II-pg10.

I have uploaded the installation output image.

PLEASE NOTE
I do not see the issue if we change the order of bouncing of postgresql and pgpool:
For e.g, Bouncing in the following order encounters the error:
==================================================
[root@cemtore01 ~]# systemctl stop postgresql
[root@cemtore01 ~]# systemctl stop pgpool

[root@cemtore01 ~]# systemctl start pgpool
[root@cemtore01 ~]# systemctl start postgresql

However, bouncing in the following order does not encounter the error:
===========================================================
root@cemtore01 ~]# systemctl stop pgpool
[root@cemtore01 ~]# systemctl stop postgresql

[root@cemtore01 ~]# systemctl start postgresql
[root@cemtore01 ~]# systemctl start pgpool

pengbo

2019-06-09 09:53

developer   ~0002646

If you start pgpool before start postgresql,, or stop postgresql before stop pgpool,
pgpool considers all backend nodes are down, and the error may occur.

Actually the error message is not correct, so we have changed the error message in 4.0.5.
Are you sure you are using pgpool 4.0.5?

Could you show the the following command results?

rpm -qa | grep pgpool
pgpool -v

MaBikram

2019-06-11 23:42

reporter   ~0002648

Hi Pengbo,

Ok I understand I need to bounce pgpool and postgresql in proper order --- 1. stop pgpool and then stop postgresql 2. start postgresql and then start pgpool

Here is the output of rpm:
[root@cemtore01 ~]# rpm -qa | grep pgpool
pgpool-II-pg10-debuginfo-4.0.4-1pgdg.rhel7.x86_64
pgpool-II-pg10-4.0.4-1pgdg.rhel7.x86_64
pgpool-II-pg10-extensions-4.0.5-1pgdg.rhel7.x86_64
pgpool-II-release-4.0-1.noarch

[root@cemtore01 ~]# pgpool -v
pgpool-II version 4.0.4 (torokiboshi)
[root@cemtore01 ~]#

pengbo

2019-06-12 12:31

developer   ~0002650

> Ok I understand I need to bounce pgpool and postgresql in proper order --- 1. stop pgpool and then stop postgresql 2. start postgresql and then start pgpool

Yes. You should to start PostgreSQL before pgpool and stop pgpool before PostgreSQL.

You are using Pgpool-II 4.0.4 not 4.0.5.
The wrong error messages are already fixed in Pgpool-II 4.0.5.

MaBikram

2019-06-13 05:11

reporter   ~0002651

Hi Pengo,

Q : As you may have noted we installed pgpool 4.0.4 RPM. Now how would one upgrade pgpool from 4.0.4 to 4.0.5 -

Thanks and regards,
Bikram

pengbo

2019-06-13 15:24

developer   ~0002652

As you are going to do a minor version upgrade, you can use the same config files.

Upgrade procedure:
(1) Backup config files. If you installed 4.0.4 using RPM, you can backup /etc/pgpool-II/ directory.
(2) Uninstall pgpool-II-pg10-*-4.0.4-*
(3) Install pgpool-II-pg10-*-4.0.5-*
(4) Apply backup config files to /etc/pgpool-II/

MaBikram

2019-06-13 23:53

reporter   ~0002654

Hi Pengbo - Thx. I have a question/clarification regarding the use of max_pool. We are expecting about a maximum of 100 client concurrent connections to our database. Most of our connections will use the same username and database. We have the default settings of 32 for the num_init_children and 4 for max_pool.
As per our understanding from pgpool documentation, pools within a child process should pick up connections if they are free. But, only the first pool - pool id 0 of the pgpool processes are picking up connections,. And, when we have more than 32 connections, connections no 33 is hung. Can you explain what is the purpose of the pools (pools 1-3) within a child process -- why they are not getting picked up?

pengbo

2019-06-18 10:34

developer   ~0002661

Your config is:

 num_init_children = 32
 max_pool = 4

Pgpool-II will open "num_init_children * max_pool connections" to backend in total.
But Pgpool-II can accept 32 concurrent connections from clients in total.
So, if you have more than 32 connections, then the connection no. 33 will wait until a client connection to Pgpool-II is closed.

You can find more details in the doc below:

http://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-NUM-INIT-CHILDREN

 | num_init_children is also the concurrent connections limit to Pgpool-II from clients.
 | If more than num_init_children clients try to connect to Pgpool-II,
 | they are blocked (not rejected with an error, like PostgreSQL) until a connection to any Pgpool-II process is closed.

MaBikram

2019-06-18 23:32

reporter   ~0002666

Thanks. But my question was more on the purpose/use of the pools (max_pool setting) within these 32 child processes? 99% of the time will be connecting with the same user to our database. Should we not be able to use the addition pools in each of these child processes (max_pool = 4) immediately when we have more than 32 client connections, instead of going into the waiting queues?

MaBikram

2019-06-26 00:17

reporter   ~0002679

I am still scratching my head - can anyone please explain the use of pools (max_pool ) within a child process(num_init_children) - and if possible with very good examples?

Issue History

Date Modified Username Field Change
2019-06-07 04:02 MaBikram New Issue
2019-06-07 09:46 pengbo Note Added: 0002643
2019-06-08 00:00 MaBikram Note Added: 0002645
2019-06-09 09:53 pengbo Note Added: 0002646
2019-06-11 09:18 pengbo Assigned To => pengbo
2019-06-11 09:18 pengbo Status new => feedback
2019-06-11 23:42 MaBikram Note Added: 0002648
2019-06-11 23:42 MaBikram Status feedback => assigned
2019-06-12 12:31 pengbo Note Added: 0002650
2019-06-12 12:31 pengbo Product Version 4.0.5 => 4.0.4
2019-06-13 05:11 MaBikram Note Added: 0002651
2019-06-13 15:24 pengbo Note Added: 0002652
2019-06-13 15:24 pengbo Status assigned => feedback
2019-06-13 23:53 MaBikram Note Added: 0002654
2019-06-13 23:53 MaBikram Status feedback => assigned
2019-06-18 10:34 pengbo Note Added: 0002661
2019-06-18 23:32 MaBikram Note Added: 0002666
2019-06-26 00:17 MaBikram Note Added: 0002679