pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedConnections "Startup"
6+ messages / 5 participants
[nested] [flat]
* Connections "Startup"
@ 2015-12-22 07:59 Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 6+ messages in thread
From: Artem Tomyuk @ 2015-12-22 07:59 UTC (permalink / raw)
To: pgsql-performance; pgsql-admin@postgresql.org
Hi.
I've noticed huge decrease in performance.
During this in htop i see a lot (200 - 300) of connections in state
"startup", each of them eats 3-3% of CPU time. This processes are not
visible in pg_stat_activity so i cant understand what they are doing, and i
cant kill them. I cant see the bottleneck in Disk IO to. The logs of
postgres says nothing to. I am confused.....
What can be the cause of huge amount of "startup" connections....
Maybe its better to start use connection pooler such as pgbouncer?
Thanks a lot.
PS.
Server config is:
2 * Intel Xeon 2660 CPU with 64 gigs of RAM.
Hardware RAID10.
Centos 6.6, PostgreSQL 9.1.2
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Connections "Startup"
@ 2015-12-22 08:09 Pavel Stehule <pavel.stehule@gmail.com>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 2 replies; 6+ messages in thread
From: Pavel Stehule @ 2015-12-22 08:09 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: pgsql-performance; pgsql-admin <pgsql-admin@postgresql.org>
Hi
2015-12-22 8:59 GMT+01:00 Artem Tomyuk <admin@leboutique.com>:
> Hi.
>
> I've noticed huge decrease in performance.
> During this in htop i see a lot (200 - 300) of connections in state
> "startup", each of them eats 3-3% of CPU time. This processes are not
> visible in pg_stat_activity so i cant understand what they are doing, and i
> cant kill them. I cant see the bottleneck in Disk IO to. The logs of
> postgres says nothing to. I am confused.....
> What can be the cause of huge amount of "startup" connections....
> Maybe its better to start use connection pooler such as pgbouncer?
> Thanks a lot.
>
What is your max_connections? Can you ran "perf top" ? What is there.
Too high number can enforce system overloading. You cannot to see these
connections in pg_stat_activity because the process in this state isn't
fully initialized.
There was lot of bugfix releases after 9.1.2 - currently there is
PostgreSQL 9.2.19. Try to upgrade first.
Regards
Pavel
>
> PS.
> Server config is:
> 2 * Intel Xeon 2660 CPU with 64 gigs of RAM.
> Hardware RAID10.
> Centos 6.6, PostgreSQL 9.1.2
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Connections "Startup"
@ 2015-12-22 11:36 Rick Otten <rottenwindfish@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rick Otten @ 2015-12-22 11:36 UTC (permalink / raw)
To: pgsql-performance
You can definitely overload most systems by trying to start too many
connections at once. (This is actually true for most relational
databases.) We used to see this scenario when we'd start a bunch web
servers that used preforked apache at the same time (where each fork had
its own connection). One temporary work around is to slow start the web
cluster - bringing up one at a time and giving them a chance to complete.
You can kill the processes by looking for them on the unix prompt instead
of inside the database. ( 'ps -fu postgres' ) You can see where they are
coming from using something like 'netstat -an | grep 5432' (or whatever
port your database is listening on.
pgbouncer is a great solution for managing large connection sets that come
and go often. It will really help. You can run it directly on each of the
web servers or client systems, you can run it in between on its own
system(s), or you can run it on the database server (if necessary). You'll
want to tune it so it only opens as many connections as you expect to be
running concurrent queries. It takes a little experimenting to figure out
the optimum settings. If you start pgbouncer first, you can bring up lots
of concurrent connections to pgbouncer, and you will hardly notice it on
the database.
Trying to stay current with the latest patches and releases is a lot of
work and little appreciated. However, in the long run it is far easier to
tackle this incrementally than trying to do one big upgrade - skipping a
bunch of releases - every now and then. This is true for the OS as well as
the Database. It is not always possible to do an upgrade, and when it is,
it can take months of planning. Hopefully you aren't in that situation.
Building processes that make these patches and upgrades routine is much
saner if you can. One nice thing about having pgbouncer in between the
application and the database is you can reconfigure pgbouncer to talk to a
different database and you won't have to touch the application code at
all. Sometimes that is easier to accomplish politically. Swapping out a
database which is running behind a cluster of application servers with
minimal risk and minimal downtime is a technical as well as political
challenge, but worth it when you can get on the latest and greatest. Good
Luck!
On Tue, Dec 22, 2015 at 3:09 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
> Hi
>
> 2015-12-22 8:59 GMT+01:00 Artem Tomyuk <admin@leboutique.com>:
>
>> Hi.
>>
>> I've noticed huge decrease in performance.
>> During this in htop i see a lot (200 - 300) of connections in state
>> "startup", each of them eats 3-3% of CPU time. This processes are not
>> visible in pg_stat_activity so i cant understand what they are doing, and i
>> cant kill them. I cant see the bottleneck in Disk IO to. The logs of
>> postgres says nothing to. I am confused.....
>> What can be the cause of huge amount of "startup" connections....
>> Maybe its better to start use connection pooler such as pgbouncer?
>> Thanks a lot.
>>
>
> What is your max_connections? Can you ran "perf top" ? What is there.
>
> Too high number can enforce system overloading. You cannot to see these
> connections in pg_stat_activity because the process in this state isn't
> fully initialized.
>
> There was lot of bugfix releases after 9.1.2 - currently there is
> PostgreSQL 9.2.19. Try to upgrade first.
>
> Regards
>
> Pavel
>
>
>>
>> PS.
>> Server config is:
>> 2 * Intel Xeon 2660 CPU with 64 gigs of RAM.
>> Hardware RAID10.
>> Centos 6.6, PostgreSQL 9.1.2
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Connections "Startup"
@ 2015-12-23 02:32 Jim Nasby <Jim.Nasby@BlueTreble.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Jim Nasby @ 2015-12-23 02:32 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; Artem Tomyuk <admin@leboutique.com>; +Cc: pgsql-performance; pgsql-admin <pgsql-admin@postgresql.org>
On 12/22/15 2:09 AM, Pavel Stehule wrote:
>
> There was lot of bugfix releases after 9.1.2 - currently there is
> PostgreSQL 9.2.19.
I'm sure Pavel meant 9.1.19, not 9.2.19.
In any case, be aware that 9.1 goes end of life next year. You should
start planning on a major version upgrade now if you haven't already.
9.5 should release in January so you might want to wait for that version.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PERFORM] Connections "Startup"
@ 2015-12-23 03:52 Om Prakash Jaiswal <op12om@yahoo.co.in>
parent: Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 1 reply; 6+ messages in thread
From: Om Prakash Jaiswal @ 2015-12-23 03:52 UTC (permalink / raw)
To: Jim Nasby <Jim.Nasby@BlueTreble.com>; Pavel Stehule <pavel.stehule@gmail.com>; Artem Tomyuk <admin@leboutique.com>; +Cc: pgsql-performance; pgsql-admin <pgsql-admin@postgresql.org>
Postgres is designed in this way. It can handle such problem by adopting the following steps:
1.Increase the kernal level parameters:shmmax and shmallexample for 2GB RAM size for postgres processing is below
vi /etc/sysctl.confkernel.shmmax = 2147483648
kernel.shmall = 2883584
similar way you increase the configuration paramater for half of RAM size of your machine.
2. Edit your postgresql.conf file following settings:
a. Increase the number of connection parameter.
Connection = 500
b.Effective_cache_size = 2GB
c. Shared_memory = 500MB
On Wednesday, 23 December 2015 8:04 AM, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:
On 12/22/15 2:09 AM, Pavel Stehule wrote:
>
> There was lot of bugfix releases after 9.1.2 - currently there is
> PostgreSQL 9.2.19.
I'm sure Pavel meant 9.1.19, not 9.2.19.
In any case, be aware that 9.1 goes end of life next year. You should
start planning on a major version upgrade now if you haven't already.
9.5 should release in January so you might want to wait for that version.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [ADMIN] Connections "Startup"
@ 2015-12-23 04:50 Pavel Stehule <pavel.stehule@gmail.com>
parent: Om Prakash Jaiswal <op12om@yahoo.co.in>
0 siblings, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2015-12-23 04:50 UTC (permalink / raw)
To: Om Prakash Jaiswal <op12om@yahoo.co.in>; +Cc: Jim Nasby <Jim.Nasby@bluetreble.com>; Artem Tomyuk <admin@leboutique.com>; pgsql-performance; pgsql-admin <pgsql-admin@postgresql.org>
2015-12-23 4:52 GMT+01:00 Om Prakash Jaiswal <op12om@yahoo.co.in>:
>
> *Postgres is designed in this way. It can handle such problem by adopting the following steps: *
>
> 1.Increase the kernal level parameters:
> shmmax and shmall
> example for 2GB RAM size for postgres processing is below
>
> *vi /etc/sysctl.conf*
>
> kernel.shmmax = 2147483648
> kernel.shmall = 2883584
>
> similar way you increase the configuration paramater for half of RAM size of your machine.
>
> 2. Edit your postgresql.conf file following settings:
> a. Increase the number of connection parameter.
> Connection = 500
> b.Effective_cache_size = 2GB
> c. Shared_memory = 500MB
>
>
increasing max connection when you have these strange issues isn't good
advice. Running 500 connections on 2GB server is highly risky.
Pavel
>
>
>
>
>
>
>
> On Wednesday, 23 December 2015 8:04 AM, Jim Nasby <Jim.Nasby@BlueTreble.com>
> wrote:
>
>
> On 12/22/15 2:09 AM, Pavel Stehule wrote:
>
> >
> > There was lot of bugfix releases after 9.1.2 - currently there is
> > PostgreSQL 9.2.19.
>
>
> I'm sure Pavel meant 9.1.19, not 9.2.19.
>
> In any case, be aware that 9.1 goes end of life next year. You should
> start planning on a major version upgrade now if you haven't already.
> 9.5 should release in January so you might want to wait for that version.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
> <http://bluetreble.com/;
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-12-23 04:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-12-22 07:59 Connections "Startup" Artem Tomyuk <admin@leboutique.com>
2015-12-22 08:09 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-12-22 11:36 ` Rick Otten <rottenwindfish@gmail.com>
2015-12-23 02:32 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2015-12-23 03:52 ` Om Prakash Jaiswal <op12om@yahoo.co.in>
2015-12-23 04:50 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox