[pgpool-general: 6939] Re: pgpool-general Digest, Vol 101, Issue 16
Nitish Kumar
itcell.mpwz at mp.gov.in
Thu Mar 19 17:48:19 JST 2020
Hi Bo,
The solution provided by you fixed the issue. Thanks a lot.
Just a quick question. I added the configuration of node2 after running my pgpool cluster for around 3 months via PgPoolAdmin UI and restarted my pgpool services.
Is this some kind of bug which requires to edit /etc/sysconfig/pgpool file ?
Regards,
Nitish Kumar
----- Original Message -----
From: pgpool-general-request at pgpool.net
To: pgpool-general at pgpool.net
Sent: Tuesday, March 10, 2020 1:42:32 PM
Subject: pgpool-general Digest, Vol 101, Issue 16
Send pgpool-general mailing list submissions to
pgpool-general at pgpool.net
To subscribe or unsubscribe via the World Wide Web, visit
http://www.sraoss.jp/mailman/listinfo/pgpool-general
or, via email, send a message with subject or body 'help' to
pgpool-general-request at pgpool.net
You can reach the person managing the list at
pgpool-general-owner at pgpool.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of pgpool-general digest..."
Today's Topics:
1. [pgpool-general: 6926] Re: latest PgpoolAdmin problem (Bo Peng)
2. [pgpool-general: 6927] Re: Pgpool postgresql - Idle
connections close problem (Rados?aw Szczygie?)
3. [pgpool-general: 6928] Re: Backend Node Not registering with
PgPool II (Bo Peng)
----------------------------------------------------------------------
Message: 1
Date: Tue, 10 Mar 2020 12:09:16 +0900
From: Bo Peng <pengbo at sraoss.co.jp>
To: Rados?aw Szczygie? <radoslaw.szczygiel at interia.pl>
Cc: "pgpool-general at pgpool.net" <pgpool-general at pgpool.net>
Subject: [pgpool-general: 6926] Re: latest PgpoolAdmin problem
Message-ID: <20200310120916.19d4afbef2910594afd00d39 at sraoss.co.jp>
Content-Type: text/plain; charset=UTF-8
Hello,
On Fri, 06 Mar 2020 08:39:43 +0100
Rados?aw Szczygie? <radoslaw.szczygiel at interia.pl> wrote:
> Hi Bo,I was able to run pgpoolAdmin on php version 7.2 but in addition I had to install
> php-posix (on version 5.6 there was the same error with the hash_equals function and
> according to the documentation in this version this function is there ... strange).
The documentation is old. I will update the documentation.
> Unfortunately, after logging in with postgres user,
> I have the error "superuser: unknown (Connection error)", but the httpd logs are clean.
> On the old version 3.7 there was "superuser: yes" and everything was ok.I have configured
> everything in pcp.conf, added correct permissions
I think the DB connetion may fail.
To check if the login user is a superuser the following command is executed.
Can you execute the command below and obtain the result?
----------------------------
# psql -h <pgpoolAdmin_host> -p 9999 -U <login_user_name> template1 -c "SELECT usesuper FROM pg_user WHERE usename = '<login_user_name>'"
(input login user password)
usesuper
----------
t
----------------------------
> >
> > I found the following error.
> >
> > ====
> > [Wed Mar 04 10:05:56.060383 2020] [:error] [pid 9565] [client 172.30.173.36:51097]
> > PHP Fatal error: Call to undefined function hash_equals() in /var/www/html/pgpoolAdmin/login.php on line 85, referer:
> > http://172.31.74.164/pgpoolAdmin/login.php
> > ====
> >
> > Your php version (php-5.4.16) doesn't support hash_equals function.
> > Your php version must be higher than PHP 5.6 or 7.0.
> >
> > https://www.php.net/manual/en/function.hash-equals.php
> >
> > On Wed, 04 Mar 2020 10:48:12 +0100
> > Rados?aw Szczygie? wrote:
> >
> > > rpm -qa | grep phpphp-pdo-5.4.16-46.el7.x86_64php-cli-5.4.16-46.el7.x86_64php-common-5.4.16-46.el7.x86_64php-pgsql-5.4.16-46.el7.x86_64php-5.4.16-46.el7.x86_64php-process-5.4.16-46.el7.x86_64httpd log when I try to login to pgpoolAdmin[Wed Mar 04 10:05:45.201459 2020] [:error] [pid 9562] [client 172.30.173.36:51080] PHP Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /var/www/html/pgpoolAdmin/templates_c/3a4f6f0d327fc7bc3ea86f63906a1bf934ca50c7.file.footer.tpl.php on line 29[Wed Mar 04 10:05:56.060383 2020] [:error] [pid 9565] [client 172.30.173.36:51097] PHP Fatal error: Call to undefined function hash_equa
ls() in /v
> > ar/www/html/pgpoolAdmin/login.php on line 85, referer: http://172.31.74.164/pgpoolAdmin/login.phpRadoslaw Szczygiel--PozdrawiamRados?aw Szczygie?Temat: Re: [pgpool-general: 6893] latest PgpoolAdmin problemData: 2020-03-04 3:50Nadawca: "Bo Peng" Adresat: "Rados?aw
> >
> > --
> > Bo Peng
> > SRA OSS, Inc. Japan
> >
--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
------------------------------
Message: 2
Date: Tue, 10 Mar 2020 09:04:55 +0100
From: Rados?aw Szczygie? <radoslaw.szczygiel at interia.pl>
To: Bo Peng <pengbo at sraoss.co.jp>
Cc: "pgpool-general at pgpool.net" <pgpool-general at pgpool.net>
Subject: [pgpool-general: 6927] Re: Pgpool postgresql - Idle
connections close problem
Message-ID: <aehzjmannezgtzmdjqfl at nglv>
Content-Type: text/plain; charset="utf-8"
Continued problem:I have 2 clusters - 2xpgpool + 2xpostgres (stream replication) - one production and the other test (test server has much weaker server parameters).The configuration between them differs only in the number of max connections:Production:num_init_children = 300max_pool = 5child_life_time = 600child_max_connections = 1510connection_life_time = 500client_idle_limit = 300test:num_init_children = 50max_pool = 4child_life_time = 600child_max_connections = 205connection_life_time = 500client_idle_limit = 300I did another tests with that python script from previous message and here are the results:1. I increased the number of inserts to 40. The script running into the test cluster created about 8-10 connections on the PG master server (pg_stat_activity table). I noticed that he created the same number of connections to the PG slave server (inserts ??). The running script did not create more connections but only used the existing ones (I understand that this is how con
nection cache works).2. I increased the number of inserts to 40. I run script multiple times into the production cluster and it created further connections on the PG master server and there could have been even 100 or more (I was afraid that I would exceed some parameter and failover) and I don't run script again. The same thing happened to the PG slave server. In this case, he didn't use connection cache ... or I don't understand something in its operation (as a reminder on the production cluster there are higher parameters num_init_children and max_connections).Conclusion:I don't know why the connecion_cache test cluster works great and I can't see it in production. I suspect it may be related to num_init_children.The production server has on average about 700-800 connections to the PG master and a similar number of connections to the PG slave (load balancing works well).I still don't understand how to best adjust the num_init_children and max_pools parameters. The product
ion cluster supports a lot of connections (not necessarily simultaneous).I also noticed, as I mentioned in previous messages, that on PG servers there are IDLE connections much older than times specified in PGPOOL to close them (even those having 1 or 2 days).RegardsRadoslaw SzczygielTemat: [pgpool-general: 6915] Re: Pgpool postgresql - Idle connections close problemData: 2020-03-05 12:37Nadawca: "Rados?aw Szczygie?" <radoslaw.szczygiel at interia.pl>Adresat: "Bo Peng" <pengbo at sraoss.co.jp>; DW: "pgpool-general at pgpool.net" <pgpool-general at pgpool.net>; This is simple python code that make insert 10 times each time making a new connection.Every use this script make 10 connections through pgpool in postgres. I thought that using pgpool connection pooling new connections use cached connecions than close it.script:#!/usr/bin/pythonimport osimport sysimport psycopg2for x in range(1,10): #ustawienia polaczenia i nawiazanie polaczenia &nbs
p; conn = psycopg2.connect("host=pgpool port=9999 dbname=test user=test password=test") cur = conn.cursor() sql="INSERT INTO test1 values (1,1)" try: cur.execute(sql) conn.commit() except: print("BLAD - Nie powiodlo sie wstawianie zapytania %s" % sql) else: print("Wstawiono rekord: %s" % sql) conn.close()--Pozdrawiam Rados?aw Szczygie?Temat: Re: [pgpool-general: 6901] Pgpool postgresql - Idle connections close problemData: 2020-03-05 11:37Nadawca: "Bo Peng" Adresat: "Rados?aw Szczygie?" ; DW: pgpool-general at pgpool.net; > Hello,
>
> I want to reproduce this issue.
> Could you share the test program?
>
> On Wed, 04 Mar 2020 10:43:56 +0100
> Rados?aw Szczygie? wrote:
>
> > Hi Pgpool team,I have a strange problem with hanging connections in postgresql or I don't know something. On postgres in pg_stat_activity I see many idle connections with "query_start" and "state_change" time from the previous day but they should be closed after 500 seconds (pgpool.conf settings).I made a test: create simply python code with loop making connect (using psycopg2), insert and close connection. Each code call creates a lot of postgres connections and does not close them. I understand that "connection cache" works, but according to pgpool documentation, if the same connection is used, using the same database, username and password should use the existing connection??Did I something misunderstand of connection pooling? Why these old connections in postgresql that are longer than the times specified in pgpool.conf not closed ??My cluster is : 2 pgpool 4.1 (watchdog) + 2 postgresql 12 (master slave replication)pgpool.conf# - Concurrent session and poo
l size -
> num_init_children = 300 # Number of concurrent sessions allowed # (change requires restart)max_pool = 5 # Number of connection pool caches per connection # (change requ
ires
> restart)# - Life time -child_life_time = 600 # Pool exits after being idle for this many secondschild_max_connections = 1510 # Pool exits after receiving that many connections # 0 means no exitconnection_life_time = 500 # Connection to backend closes after being idle for this many seconds &n
bsp;&
> nbsp; # 0 means no closeclient_idle_limit = 300 # Client is disconnected after being idle for that many seconds # (even inside an explicit transactions!) # 0 means no disconnectionRegard
sRado
> slaw Szczygiel--PozdrawiamRados?aw Szczygie?
>
>
> --
> Bo Peng
> SRA OSS, Inc. Japan
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200310/e8b5e7ed/attachment-0001.html>
------------------------------
Message: 3
Date: Tue, 10 Mar 2020 17:12:28 +0900
From: Bo Peng <pengbo at sraoss.co.jp>
To: Nitish Kumar <itcell.mpwz at mp.gov.in>
Cc: PgPool General <pgpool-general at pgpool.net>
Subject: [pgpool-general: 6928] Re: Backend Node Not registering with
PgPool II
Message-ID: <20200310171228.6fdf31dab8f172d1ec72ed76 at sraoss.co.jp>
Content-Type: text/plain; charset=US-ASCII
Hello,
On Mon, 9 Mar 2020 14:35:11 +0530 (IST)
Nitish Kumar <itcell.mpwz at mp.gov.in> wrote:
> Hi,
>
> I ran following command on my active pgpool server
I think you added the node2(xxx.xx.xx.41) in pgpool.conf and restarted pgpool.
In this case, you need to discard previous node status restored in pgpool_status file
while starting pgpool.
To discard pgpool_status file, please edit "/etc/sysconfig/pgpool" and restart pgpool.
-------------------------------
# vi /etc/sysconfig/pgpool
(edit OPTS)
OPTS=" -D -n"
# systemctl restart pgpool
-------------------------------
> For first Backend
> [pgpooladmin at ngbpgpool1 ~]$ pcp_node_info --username=pgpool_admin -W --verbose 0
> Password:
> Hostname : xxx.xx.xx.19
> Port : 5432
> Status : 2
> Weight : 0.333333
> Status Name : up
> Role : primary
> Replication Delay : 0
> Replication State :
> Replication Sync State :
> Last Status Change : 2020-03-06 11:24:22
>
> For second backend
> [pgpooladmin at ngbpgpool1 ~]$ pcp_node_info --username=pgpool_admin -W --verbose 1
> Password:
> Hostname : xxx.xx.xx.20
> Port : 5432
> Status : 2
> Weight : 0.333333
> Status Name : up
> Role : standby
> Replication Delay : 0
> Replication State :
> Replication Sync State :
> Last Status Change : 2020-03-06 11:24:22
>
> For third backend
> [pgpooladmin at ngbpgpool1 ~]$ pcp_node_info --username=pgpool_admin -W --verbose 2
> Password:
> Hostname : xxx.xx.xx.41
> Port : 5432
> Status : 0
> Weight : 0.333333
> Status Name : unused
> Role : standby
> Replication Delay : 0
> Replication State :
> Replication Sync State :
> Last Status Change : 2020-03-06 11:24:22
>
> ----- Original Message -----
> From: "Bo Peng" <pengbo at sraoss.co.jp>
> To: "Nitish Kumar" <itcell.mpwz at mp.gov.in>
> Cc: "PgPool General" <pgpool-general at pgpool.net>
> Sent: Friday, March 6, 2020 9:49:52 AM
> Subject: Re: [pgpool-general: 6918] Backend Node Not registering with PgPool II
>
> Hello,
>
> On Thu, 5 Mar 2020 17:44:35 +0530 (IST)
> Nitish Kumar <itcell.mpwz at mp.gov.in> wrote:
>
> >
> > Hi Team,
> >
> > I am using PgPool II 4.1.1 with 3 PostgreSQL 10.11 backend nodes.
> >
> > The issue I am facing is that only 2 nodes have been registered with PgPool II as shown in PgPoolAdmin UI.
> >
> > I am attaching the screenshot for the same.
>
> I think it is not the issue of pgpoolAdmin,
> because I tried the latest pgpoolAdmin, it works well.
>
> Could you show the result of "show pool_nodes"?
>
> # psql -U postgres postgres -p 9999 -c "show pool_nodes"
>
> Please try to edit "/etc/sysconfig/pgpool" and restart pgpool.
>
> -------------------------------
> # vi /etc/sysconfig/pgpool
> (edit OPTS)
> OPTS=" -D -n"
>
> # systemctl restart pgpool
> -------------------------------
>
> > The configuration of all the 3 backend nodes are same and correct. I don't know why its happening as
> > PgPool II is not outputing any explicit logs.
> >
> > I am facing this issue since PgPool II 4.1.0 version. The earlier resolution provided from you guys
> > that I should upgrade to latest rpm of same version ( PgPool II 4.1.0 ).
> > The problem existed even after applying that resolution.
> >
> > I hope it will fixed in the next minor release i.e v4.1.1. But even after updating my both PgPool II instances to
> > v4.1.1 the problem exists.
> >
> > Kindly help me in fixing this issue.
> >
> > Regards,
> > Nitish
> >
> >
>
>
> --
> Bo Peng <pengbo at sraoss.co.jp>
> SRA OSS, Inc. Japan
>
>
--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
------------------------------
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general
End of pgpool-general Digest, Vol 101, Issue 16
***********************************************
More information about the pgpool-general
mailing list