[pgpool-general: 6341] Re: question regarding pgpool effiecncy in my case

[Pierre Timmermans]

Mariel
This is my understanding: when pgpool is started, it creates num_init_children (it is a config) background processes. You can see that with ps -ef. So for example, if num_init_children is 3, it will start 3 of those processes.
The first connection to [pguser1,pgdb1] will use the first process.The second connection to [pguser1,pgdb1] will use the second process.The third connection, this time to [pguser2,pgdb2] will use the third process
Now if you have a connection to [pguser2,pgdb2] again, the pool (the pool of pgpool's processes) is full but it can still connect if max_pool is not set to 1
I think this is the idea (not sure). So pgpool is not really doing connection pooling, it is more connection caching. Once a process holds a connection, this connection is never used by another process, it is only when the process disconnects from postgres that the pgpool background process becomes available in the "pool".

The important point to remember is the parameter num_init_children: if it is too low, connections attempts will be queued (no error) and then of course performance will be very bad. I think everybody has been hit by this problem...

I don't think pgpool should impact the performance, it would be interesting to analyze your test and understand why pgpool is doing worst than without pgpool. Maybe look in the postgres log which statements are slow ?


Pierre 

    On Thursday, December 13, 2018, 3:01:14 PM GMT+1, Mariel Cherkassky <mariel.cherkassky at gmail.com> wrote:  
 
 someone ?
‫בתאריך יום א׳, 9 בדצמ׳ 2018 ב-15:00 מאת ‪Mariel Cherkassky‬‏ <‪mariel.cherkassky at gmail.com‬‏>:‬

Hi,I'm trying to implement pgpool2 in our environment but in our performance tests it seems that when we use pgpool the performance are worse. Therefore, I wanted to check if pgpool is the right solution based on the next info that I read : " process opens a new backend connection only if the requested [user,database] pair is not already in the cache. So if the application uses only one user to connect to only one database, say [pguser1,pgdb1], then each child will continue to reuse the first connection and will never open a second connection, so in effect pgpool will open no more than 100 backend connections even though max_pool is set to 3."
In my case, I have 3 databases and for every database I have a dedicated user. The application access each of those databases with a dedicated user. Now basicaly, if my max_connections is set to 500, pgpool max_pool set to 3 and the num_of_init_children is set to 100 it means that at every moment I can have at most 100 sessions to each one of the  databases (with pgpool..).
Thanks , Mariel.
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general
  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20181213/4d7a9e40/attachment.html>