Re: [PERFORM] Big number of connections

[Pavel Stehule]

2016-04-04 16:43 GMT+02:00 Moreno Andreo :

> Il 04/04/2016 15:33, Pavel Stehule ha scritto:
>
>>
>>
>> PostgreSQL doesn't contain integrated pooler - so any connection to
>> Postgres enforces one PostgreSQL proces. A performance benchmarks is
>> showing maximum performance about 10x cores.  With high number of
>> connections you have to use low size of work_mem, what enforces can have
>> negative impact on performance too. Too high number of active PostgreSQL
>> processes increase a risk of performance problems with spin locks, etc.
>>
>
> :-O
> I wasn't absolutely aware of this thing... is there a way to monitor
> active connections, or at least to report when they grow too much?
> (say, I have an 8-core system and want to track down if, and when, active
> connections grow over 80)
>

100 connections are probably ok, 200 is over the optimum - there is some
tolerance.

We are speaking about optimum - I had possibility to work with system where
max connections was 300, 600 - and it was working. But then the
max_connection doesn't work as safeguard against overloading. And the
system under higher load can be pretty slow.

Regards

Pavel


>
> Thanks
> Moreno.-
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] Big number of connections

[Moreno Andreo]

  
  
Il 04/04/2016 16:54, Artem Tomyuk ha
  scritto:


  

  2016-04-04 17:43 GMT+03:00 Moreno
Andreo :

  s there a
way to monitor active connections, or at least to report
when they grow too much?
(say, I have an 8-core system and want to track down if,
and when, active connections grow over 80)

  
  
  You can achieve that just running simple query like
  select count(*) from pg_stat_activity where state = 'active' 








  

Thanks, but this way I get the "sample" on that actual moment: what
I'd need is to monitor, or to have something warning me like "Hey,
You've got 2000 active connections! Time to grow up!" :-)

Cheers,
Moreno.-

  

Re: [PERFORM] Big number of connections

[Artem Tomyuk]

there are two ways:

   1. to write bash script with condition if number of conn. is > 1000 send
   me email and put that script in crontab
   2. monitor it with external monitoring system like zabbix, nagios etc


2016-04-04 18:00 GMT+03:00 Moreno Andreo :

> Il 04/04/2016 16:54, Artem Tomyuk ha scritto:
>
>
> 2016-04-04 17:43 GMT+03:00 Moreno Andreo :
>
>> s there a way to monitor active connections, or at least to report when
>> they grow too much?
>> (say, I have an 8-core system and want to track down if, and when, active
>> connections grow over 80)
>>
>
> You can achieve that just running simple query like
> select count(*) from pg_stat_activity where state = 'active'
>
>
>
>
> Thanks, but this way I get the "sample" on that actual moment: what I'd
> need is to monitor, or to have something warning me like "Hey, You've got
> 2000 active connections! Time to grow up!" :-)
>
> Cheers,
> Moreno.-
>
>

Re: [PERFORM] Big number of connections

[Artem Tomyuk]

2016-04-04 17:43 GMT+03:00 Moreno Andreo :

> s there a way to monitor active connections, or at least to report when
> they grow too much?
> (say, I have an 8-core system and want to track down if, and when, active
> connections grow over 80)
>

You can achieve that just running simple query like
select count(*) from pg_stat_activity where state = 'active'

Re: [PERFORM] Big number of connections

[Moreno Andreo]

Il 04/04/2016 15:33, Pavel Stehule ha scritto:



PostgreSQL doesn't contain integrated pooler - so any connection to 
Postgres enforces one PostgreSQL proces. A performance benchmarks is 
showing maximum performance about 10x cores.  With high number of 
connections you have to use low size of work_mem, what enforces can 
have negative impact on performance too. Too high number of active 
PostgreSQL processes increase a risk of performance problems with spin 
locks, etc.


:-O
I wasn't absolutely aware of this thing... is there a way to monitor 
active connections, or at least to report when they grow too much?
(say, I have an 8-core system and want to track down if, and when, 
active connections grow over 80)


Thanks
Moreno.-



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Big number of connections

[Pavel Stehule]

Hi

2016-04-04 15:14 GMT+02:00 Mike Sofen :

> From: Jim Nasby Sent: Sunday, April 03, 2016 10:19 AM
>
> >>On 4/1/16 2:54 AM, jarek wrote:
> >> I'll be happy to hear form users of big PostgreSQL installations, how
> >> many users do you have and what kind of problems we may expect.
> >> Is there any risk, that huge number of roles will slowdown overall
> >> performance ?
>
> >Assuming you're on decent sized hardware though, 3000-4000 open
> connections shouldn't be much of an >issue *as long as very few are active
> at once*. If you get into a situation where there's a surge of activity
> >and you suddenly have 2x more active connections than cores, you won't be
> happy. I've seen that push >servers into a state where the only way to
> recover was to disconnect everyone.
> >--
> >Jim Nasby
>
> Jim - I don't quite understand the math here: on a server with 20 cores,
> it can only support 40 active users?
>
> I come from the SQL Server world where a single 20 core server could
> support hundreds/thousands of active users and/or many dozens of
> background/foreground data processes.  Is there something fundamentally
> different between the two platforms relative to active user loads?  How
> would we be able to use Postgres for larger web apps?
>

PostgreSQL doesn't contain integrated pooler - so any connection to
Postgres enforces one PostgreSQL proces. A performance benchmarks is
showing maximum performance about 10x cores.  With high number of
connections you have to use low size of work_mem, what enforces can have
negative impact on performance too. Too high number of active PostgreSQL
processes increase a risk of performance problems with spin locks, etc.

Usually Web frameworks has own pooling solution - so just use it. If you
need more logical connection than is optimum against number of cores, then
you should to use external pooler like pgpool II or pgbouncer.

http://www.pgpool.net/mediawiki/index.php/Main_Page
http://pgbouncer.github.io/

Pgbouncer is light with only necessary functions, pgpool is little bit
heavy with lot of functions.

Regards

Pavel


>
> Mike Sofen
>
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] Big number of connections

[Mike Sofen]

From: Jim Nasby Sent: Sunday, April 03, 2016 10:19 AM

>>On 4/1/16 2:54 AM, jarek wrote:
>> I'll be happy to hear form users of big PostgreSQL installations, how 
>> many users do you have and what kind of problems we may expect.
>> Is there any risk, that huge number of roles will slowdown overall 
>> performance ?

>Assuming you're on decent sized hardware though, 3000-4000 open connections 
>shouldn't be much of an >issue *as long as very few are active at once*. If 
>you get into a situation where there's a surge of activity >and you suddenly 
>have 2x more active connections than cores, you won't be happy. I've seen that 
>push >servers into a state where the only way to recover was to disconnect 
>everyone.
>--
>Jim Nasby

Jim - I don't quite understand the math here: on a server with 20 cores, it can 
only support 40 active users?

I come from the SQL Server world where a single 20 core server could support 
hundreds/thousands of active users and/or many dozens of background/foreground 
data processes.  Is there something fundamentally different between the two 
platforms relative to active user loads?  How would we be able to use Postgres 
for larger web apps?

Mike Sofen

 



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance