Re: Determining server load - Mailing list pgsql-general

-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------




> On Sep 27, 2016, at 10:48 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 09/27/2016 11:40 AM, Israel Brewster wrote:
>> On Sep 27, 2016, at 9:55 AM, John R Pierce <pierce@hogranch.com> wrote:
>>>
>>> On 9/27/2016 9:54 AM, Israel Brewster wrote:
>>>>
>>>> I did look at pgbadger, which tells me I have gotten as high as 62 connections/second, but given that most of
thoseconnections are probably very short lived that doesn't really tell me anything about concurrent connections. 
>>>
>>> Each connection requires a process fork of the database server, which is very expensive.  you might consider using
aconnection pool such as pgbouncer, to maintain a fixed(dynamic) number of real database connections, and have your
appsconnect/disconnect to this pool.    Obviously, you need a pool for each database, and your apps need to be
'stateless'and not make or rely on any session changes to the connection so they don't interfere with each other.
Doingthis correctly can make an huge performance improvement on the sort of apps that do (connect, transaction,
disconnect)a lot. 
>>
>> Understood. My main *performance critical* apps all use an internal connection pool for this reason - Python's
psycopg2pool, to be exact. I still see a lot of connects/disconnects, but I *think* that's psycopg2 recycling
connectionsin the background - I'm not 100% certain how the pools there work (and maybe they need some tweaking as
well,i.e. setting to re-use connections more times or something). The apps that don't use pools are typically
data-gatheringscripts where it doesn't mater how long it takes to connect/write the data (within reason). 
>
> http://initd.org/psycopg/docs/pool.html
>
> "Note
>
> This pool class is mostly designed to interact with Zope and probably not useful in generic applications. "
>
> Are you using Zope?

You'll notice that note only applies to the PersistentConnectionPool, not the ThreadedConnectionPool (Which has a note
sayingthat it can be safely used in multi-threaded applications), or the SimpleConnectionPool (which is useful only for
single-threadedapplications). Since I'm not using Zope, and do have multi-threaded applications, I'm naturally using
theThreadedConnectionPool :-) 

>
>>
>> That said, it seems highly probable, if not a given, that there comes a point where the overhead of handling all
thoseconnections starts slowing things down, and not just for the new connection being made. How to figure out where
thatpoint is for my system, and how close to it I am at the moment, is a large part of what I am wondering. 
>>
>> Note also that I did realize I was completely wrong about the initial issue - it turned out it was a network issue,
nota postgresql one. Still, I think my specific questions still apply, if only in an academic sense now :-) 
>>
>> -----------------------------------------------
>> Israel Brewster
>> Systems Analyst II
>> Ravn Alaska
>> 5245 Airport Industrial Rd
>> Fairbanks, AK 99709
>> (907) 450-7293
>> -----------------------------------------------
>>
>>
>>>
>>>
>>>
>>> --
>>> john r pierce, recycling bits in santa cruz
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general