View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000520 | Pgpool-II | General | public | 2019-06-07 04:02 | 2019-06-26 00:17 |
| Reporter | MaBikram | Assigned To | pengbo | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | assigned | Resolution | open | ||
| Platform | Linux cemtore01 3.10.0-693.el7.x | OS | Centos 7.3 | ||
| Product Version | 4.0.4 | ||||
| Summary | 0000520: Encountering - psql: ERROR: connection cache is full HINT: increase max_pool | ||||
| Description | Hi 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 | ||||
| Tags | No tags attached. | ||||
|
|
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? |
|
|
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 |
|
|
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 |
|
|
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 ~]# |
|
|
> 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. |
|
|
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 |
|
|
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/ |
|
|
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? |
|
|
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. |
|
|
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? |
|
|
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? |
| 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 |
