[pgpool-general: 6274] Re: pgpool does not reuse connections

[Tatsuo Ishii]

> On Sat, Nov 10, 2018 at 4:20 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>
>> > pgpool log is full of messages like these:
>> >
>> > pgpool_1    | 2018-11-09 14:53:26: pid 4618: LOG:  new connection received
>> > pgpool_1    | 2018-11-09 14:53:26: pid 4618: DETAIL:  connecting
>> > host=172.22.0.1 port=60030
>>
>> The log does not help too much (it's normal if you set log_connections
>> to on).
> 
> Yes, it's normal. But I suggest there should be less messages than
> number of "client to pgpool" connections if connection cache is used.

I don't understand what you mean. Connection cache is for between
pgpool and PostgreSQL, not "client to pgpool" connections.

>> Another possibility is, if the database is one of template0, template1,
>> postgress or regression, connection cache is not created. Does your
>> application use those database?  Also if client does not disconnect
>> proper way (without sending terminate message 'X') then connection
>> cache is not created.
> 
> I know about this feature and I used it to force pgpool not to use
> connection cache and compare the results then. This behavior is
> observed on database with name different from you have enumerated.
> Could you please point me to the place in pgpool source code where it
> doesn't create the connection cache if 'X' terminate message was not
> send?

In src/protocol/child.c around line 477:

		if ((sp &&
			 (!strcmp(sp->database, "template0") ||
			  !strcmp(sp->database, "template1") ||
			  !strcmp(sp->database, "postgres") ||
			  !strcmp(sp->database, "regression"))) ||
			(*frontend != NULL &&
			 ((*frontend)->socket_state == POOL_SOCKET_EOF ||
			  (*frontend)->socket_state == POOL_SOCKET_ERROR)))
			cache_connection = false;

If the socket to frontend is not in good state, which could happen if
frontend exited abnormaly (did not issue a terminate message),
cache_connection is set to false and connection cache to backend is
removed.

	if (cache_connection == false)
	{
		pool_send_frontend_exits(backend);
		if (sp)
			pool_discard_cp(sp->user, sp->database, sp->major);
	}


>> Finally, if there are too many combinations of user/database (in your
>> case more than 4 = max_pool), Pgpool-II needs to disconnect the oldest
>> connection to create a new connection cache.
> 
> No, all I use only one combination of user/database for testing.
> 
>> Are one of them is your case?

I remember more cases:

child_life_time
child_max_connections
connection_life_time

Depending on those parameters, pgpool will exit or close connections
to backend. So new connections need to be created next time.

>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>> > I wrote test app on golang and succeeded to reproduce this on my local
>> > machine. Go sql driver uses pq library, written on go from scratch and
>> > it doesn't use postgres libpq C library. Would that cause the problem?
>>
>> No, as far as pq library strictly follows PostgreSQL's
>> frontend/backend protocol.
>>
>> > (https://github.com/lib/pq/blob/master/conn.go#L820).
>> > On Fri, Nov 9, 2018 at 8:54 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >>
>> >> > We have a problem at one of our environments with pgpool. It does not
>> >> > use connection pool for some reason. Application written on golang (we
>> >> > use gorm which uses package https://golang.org/pkg/database/sql/)
>> >> > opens connections to pgpool and executes queries. Application executes
>> >> > every transaction in a separate connection. In postgresql logs I see
>> >> > that pgpool constantly connects and disconnects from postgres like as
>> >> > if connection pool is not used. I tried to reproduce this situation
>> >> > with pgbench (option --connect) but everything is OK with it. pgpool
>> >> > works with pgbench as expected - it reuses all connections and doesn't
>> >> > create a new one for each transaction.
>> >> >
>> >> > num_init_children = 100, max_pool =4. max_connections in postgres = 1000.
>> >> >
>> >> > We tried pgpool versions 3.3.4 and 3.7.5.
>> >> >
>> >> > Any ideas?
>> >>
>> >> No idea so far. Can you share pgpool log? 3.7.5 is preferable as 3.3
>> >> is already EOL.
>> >>
>> >> Best regards,
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese:http://www.sraoss.co.jp
>> >
>> >
>> >
>> > --
>> > -
>> > With best regards, Andrey Zhidenkov
> 
> 
> 
> -- 
> -
> With best regards, Andrey Zhidenkov