pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHigh-water Mark for number of sessions/connections reached in Postgres
10+ messages / 6 participants
[nested] [flat]
* High-water Mark for number of sessions/connections reached in Postgres
@ 2010-08-07 00:19 Tomeh, Husam <HTomeh@corelogic.com>
0 siblings, 2 replies; 10+ messages in thread
From: Tomeh, Husam @ 2010-08-07 00:19 UTC (permalink / raw)
To: pgsql-admin
I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres database. Is there a view or command that can provide this information. The pg_stat_database shows the current number of connections, but not the high-water mark a database had reached.
Thanks in advance.
Husam
******************************************************************************************
This message may contain confidential or proprietary information intended only for the use of the
addressee(s) named above or may contain information that is legally privileged. If you are
not the intended addressee, or the person responsible for delivering it to the intended addressee,
you are hereby notified that reading, disseminating, distributing or copying this message is strictly
prohibited. If you have received this message by mistake, please immediately notify us by
replying to the message and delete the original message and any copies immediately thereafter.
Thank you.
******************************************************************************************
CLLD
^ permalink raw reply [nested|flat] 10+ messages in thread
* High-water Mark for number of sessions/connections reached in Postgres
@ 2010-08-07 00:26 Tomeh, Husam <HTomeh@corelogic.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tomeh, Husam @ 2010-08-07 00:26 UTC (permalink / raw)
To: pgsql-admin
I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres database. Is there a view or command that can provide this information. The pg_stat_database shows the current number of connections, but not the high-water mark a database had reached.
Thanks in advance.
Husam
Husam
******************************************************************************************
This message may contain confidential or proprietary information intended only for the use of the
addressee(s) named above or may contain information that is legally privileged. If you are
not the intended addressee, or the person responsible for delivering it to the intended addressee,
you are hereby notified that reading, disseminating, distributing or copying this message is strictly
prohibited. If you have received this message by mistake, please immediately notify us by
replying to the message and delete the original message and any copies immediately thereafter.
Thank you.
******************************************************************************************
CLLD
^ permalink raw reply [nested|flat] 10+ messages in thread
* High-water Mark for number of sessions/connections reached in Postgres
@ 2010-08-07 00:46 Tomeh, Husam <HTomeh@corelogic.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tomeh, Husam @ 2010-08-07 00:46 UTC (permalink / raw)
To: pgsql-admin
I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres database. Is there a view or command that can provide this information. The pg_stat_database shows the current number of connections, but not the high-water mark a database had reached.
Thanks in advance.
Husam
Husam
******************************************************************************************
This message may contain confidential or proprietary information intended only for the use of the
addressee(s) named above or may contain information that is legally privileged. If you are
not the intended addressee, or the person responsible for delivering it to the intended addressee,
you are hereby notified that reading, disseminating, distributing or copying this message is strictly
prohibited. If you have received this message by mistake, please immediately notify us by
replying to the message and delete the original message and any copies immediately thereafter.
Thank you.
******************************************************************************************
CLLD
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: High-water Mark for number of sessions/connections reached in Postgres
@ 2010-08-09 14:07 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Tomeh, Husam <HTomeh@corelogic.com>
1 sibling, 0 replies; 10+ messages in thread
From: Kevin Grittner @ 2010-08-09 14:07 UTC (permalink / raw)
To: Husam Tomeh <HTomeh@corelogic.com>; pgsql-admin
"Tomeh, Husam" <HTomeh@corelogic.com> wrote:
> I was wondering if we can query/obtain the high-water mark of
> number of sessions or connections reached in a Postgres database.
I'm not aware of anything like that, although it seems as though it
would have obvious uses in database administration. If you had such
a thing, would you want the ability to reset it?
-Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: High-water Mark for number of sessions/connections reached in Postgres
@ 2010-08-09 22:21 Scott Marlowe <scott.marlowe@gmail.com>
parent: Tomeh, Husam <HTomeh@corelogic.com>
1 sibling, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2010-08-09 22:21 UTC (permalink / raw)
To: Tomeh, Husam <HTomeh@corelogic.com>; +Cc: pgsql-admin
On Fri, Aug 6, 2010 at 6:19 PM, Tomeh, Husam <HTomeh@corelogic.com> wrote:
> I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres database. Is there a view or command that can provide this information. The pg_stat_database shows the current number of connections, but not the high-water mark a database had reached.
It's a pretty easy thing to approximate with a shell script.
while true;do ps ax|grep postgres:|grep -v grep|wc -l ;sleep 10;done >
connects.log &
then just tail the connects.log file. It's a dirty hack and it'll be
a few counts over due to counting the postmaster and a few other
processes, but it'll give you a good idea of what your system is
doing. Add a date in there if you need to know the time it was
happening.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: High-water Mark for number of sessions/connections reached in Postgres
@ 2010-08-09 22:29 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2010-08-09 22:29 UTC (permalink / raw)
To: Tomeh, Husam <HTomeh@corelogic.com>; +Cc: pgsql-admin
If you want a date stamp, you can change the ps ax stuff to look like this:
date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax|grep postgres:|grep -v grep|wc -l
On Mon, Aug 9, 2010 at 4:21 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Aug 6, 2010 at 6:19 PM, Tomeh, Husam <HTomeh@corelogic.com> wrote:
>> I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres database. Is there a view or command that can provide this information. The pg_stat_database shows the current number of connections, but not the high-water mark a database had reached.
>
> It's a pretty easy thing to approximate with a shell script.
>
> while true;do ps ax|grep postgres:|grep -v grep|wc -l ;sleep 10;done >
> connects.log &
>
> then just tail the connects.log file. It's a dirty hack and it'll be
> a few counts over due to counting the postmaster and a few other
> processes, but it'll give you a good idea of what your system is
> doing. Add a date in there if you need to know the time it was
> happening.
>
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: High-water Mark for number of sessions/connections reached in Postgres
@ 2010-08-09 22:39 Alvaro Herrera <alvherre@commandprompt.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2010-08-09 22:39 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Tomeh, Husam <htomeh@corelogic.com>; pgsql-admin
Excerpts from Scott Marlowe's message of lun ago 09 18:29:58 -0400 2010:
> If you want a date stamp, you can change the ps ax stuff to look like this:
>
> date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax|grep postgres:|grep -v grep|wc -l
FWIW the "grep" business is best solved by ps itself, something like
ps ax -C postgres
date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax -C postgres|wc -l
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: High-water Mark for number of sessions/connections reached in Postgres
@ 2010-08-09 23:33 Tomeh, Husam <HTomeh@corelogic.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tomeh, Husam @ 2010-08-09 23:33 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
Thanks for these workarounds I have something similar implemented. It would've been nice to have Postgres maintain the high-water mark inside the database in a pg view such as pg_stat_database. This is useful from a capacity perspective where that can be monitored to alert DBAs when a threshold is reached. Other DB engines has such feature built in such as Oracle. Perhaps something to be on Postgres TO DO list soon :)
Regards,
Husam
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Alvaro Herrera
Sent: Monday, August 09, 2010 3:39 PM
To: Scott Marlowe
Cc: Tomeh, Husam; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] High-water Mark for number of sessions/connections reached in Postgres
Excerpts from Scott Marlowe's message of lun ago 09 18:29:58 -0400 2010:
> If you want a date stamp, you can change the ps ax stuff to look like this:
>
> date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax|grep postgres:|grep -v grep|wc -l
FWIW the "grep" business is best solved by ps itself, something like
ps ax -C postgres
date +"%Y-%m-%d %H:%M:%S"|tr "\n" ":";ps ax -C postgres|wc -l
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
******************************************************************************************
This message may contain confidential or proprietary information intended only for the use of the
addressee(s) named above or may contain information that is legally privileged. If you are
not the intended addressee, or the person responsible for delivering it to the intended addressee,
you are hereby notified that reading, disseminating, distributing or copying this message is strictly
prohibited. If you have received this message by mistake, please immediately notify us by
replying to the message and delete the original message and any copies immediately thereafter.
Thank you.
******************************************************************************************
CLLD
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: High-water Mark for number of sessions/connections reached in Postgres
@ 2010-08-10 00:56 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Tomeh, Husam <HTomeh@corelogic.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rosser Schwarz @ 2010-08-10 00:56 UTC (permalink / raw)
To: Tomeh, Husam <HTomeh@corelogic.com>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
On Mon, Aug 9, 2010 at 5:33 PM, Tomeh, Husam <HTomeh@corelogic.com> wrote:
> This is useful from a capacity perspective where that can be monitored to alert DBAs when a threshold is reached.
If alerting based on connection counts is your concern, you might get
some benefit from the Bucardo project's check_postgres.pl script. One
of the tests it performs is a backend count. It works with any
monitoring package that speaks NRPE, so you can issue alerts based on
either a percentage of your max_connections setting, or on the actual
backend count, as indicated by the arguments with which it's invoked.
It would also be fairly straightforward to store whatever value the
test returns in a db table, log file, Cacti/other RRD-based tools, or
whatever else strikes your fancy, for historical and trending
analysis.
<http://bucardo.org/check_postgres/;
rls
--
:wq
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: High-water Mark for number of sessions/connections reached in Postgres
@ 2011-05-31 18:35 Jesper Krogh <jesper@krogh.cc>
parent: Tomeh, Husam <HTomeh@corelogic.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jesper Krogh @ 2011-05-31 18:35 UTC (permalink / raw)
To: pgsql-admin
> I was wondering if we can query/obtain the high-water mark of number
> of sessions or connections reached in a Postgres database. Is there a
> view or command that can provide this information. The
> pg_stat_database shows the current number of connections, but not the
> high-water mark a database had reached.
Like "show max_connections"
Jesper
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2011-05-31 18:35 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-07 00:19 High-water Mark for number of sessions/connections reached in Postgres Tomeh, Husam <HTomeh@corelogic.com>
2010-08-09 14:07 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-09 22:21 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-09 22:29 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-09 22:39 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-08-09 23:33 ` Tomeh, Husam <HTomeh@corelogic.com>
2010-08-10 00:56 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2010-08-07 00:26 High-water Mark for number of sessions/connections reached in Postgres Tomeh, Husam <HTomeh@corelogic.com>
2011-05-31 18:35 ` Jesper Krogh <jesper@krogh.cc>
2010-08-07 00:46 High-water Mark for number of sessions/connections reached in Postgres Tomeh, Husam <HTomeh@corelogic.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