pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedlogging or debugging to report time to establish a database connection
4+ messages / 2 participants
[nested] [flat]
* logging or debugging to report time to establish a database connection
@ 2013-07-12 15:00 Mike Broers <mbroers@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mike Broers @ 2013-07-12 15:00 UTC (permalink / raw)
To: pgsql-admin
I was recently asked how long it takes for postgres (or in my case
pgbouncer) to create a database connection and could not find a way within
postgres logging or psql to report this information.
I came across depesz's great article on pgbouncer utilizing tcpdump:
http://www.depesz.com/2012/12/02/what-is-the-point-of-bouncing/ but Im not
familiar with tcpdump and was hoping there was a way to see this using
postgres tools.
Is there something I am overlooking in postgres logging or psql client
switches that would make this information available or am I stuck with
lower level tools?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: logging or debugging to report time to establish a database connection
@ 2013-07-12 15:21 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Mike Broers <mbroers@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Ribe @ 2013-07-12 15:21 UTC (permalink / raw)
To: Mike Broers <mbroers@gmail.com>; +Cc: pgsql-admin
On Jul 12, 2013, at 9:00 AM, Mike Broers wrote:
> Is there something I am overlooking in postgres logging or psql client switches that would make this information available or am I stuck with lower level tools?
Well, if you want to log how long pgbouncer takes to hand out a connection from a pool, you're certainly not going to find that answer anywhere in postgresql logging ;-)
Unfortunately, it doesn't look to me as though the pgbouncer console will show you exactly that. Maybe using -v or -vv options would help? But yes, average/max/min maybe even stddev of time to hand out a connection would seem to be information that would be good to collect, so perhaps a feature request is in order...
Of course there will also be overhead in the libraries that will vary from one database manager to another, so in my opinion the only way to really benchmark this would be from the client side anyway. Which is maybe what you were thinking when asking about psql switches? Again, a feature request might be in order--I can think of two possibilities: 1) a command-line verbose-mode switch that would provide details of the connection process, which could be good also for debugging connection problems, showing what host is being attempted, what the resolution (if any) of the host name is, connection opened or not, authentication passed or not, and so on; 2) enhancements to the /conninfo command to provide more details.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
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] 4+ messages in thread
* Re: logging or debugging to report time to establish a database connection
@ 2013-07-12 15:53 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Ribe @ 2013-07-12 15:53 UTC (permalink / raw)
To: Mike Broers <mbroers@gmail.com>; +Cc: pgsql-admin
BTW, just for the heck of it, I decided to try it out on my dev system (several year old 17" MacBook Pro, meaning core i7 processor), PostgreSQL 9.2, pgbouncer 1.5. So, connecting via libpq to postgres directly takes 2-3 ms, connecting to pgbouncer takes typically 70-150 us, with the occasional outlier at 500+ us--of course that's with a pgbouncer config with a big enough pool that I never have to wait for a connection to become free.
On Jul 12, 2013, at 9:21 AM, Scott Ribe wrote:
> On Jul 12, 2013, at 9:00 AM, Mike Broers wrote:
>
>> Is there something I am overlooking in postgres logging or psql client switches that would make this information available or am I stuck with lower level tools?
>
> Well, if you want to log how long pgbouncer takes to hand out a connection from a pool, you're certainly not going to find that answer anywhere in postgresql logging ;-)
>
> Unfortunately, it doesn't look to me as though the pgbouncer console will show you exactly that. Maybe using -v or -vv options would help? But yes, average/max/min maybe even stddev of time to hand out a connection would seem to be information that would be good to collect, so perhaps a feature request is in order...
>
> Of course there will also be overhead in the libraries that will vary from one database manager to another, so in my opinion the only way to really benchmark this would be from the client side anyway. Which is maybe what you were thinking when asking about psql switches? Again, a feature request might be in order--I can think of two possibilities: 1) a command-line verbose-mode switch that would provide details of the connection process, which could be good also for debugging connection problems, showing what host is being attempted, what the resolution (if any) of the host name is, connection opened or not, authentication passed or not, and so on; 2) enhancements to the /conninfo command to provide more details.
>
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> (303) 722-0567 voice
>
>
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
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] 4+ messages in thread
* Re: logging or debugging to report time to establish a database connection
@ 2013-07-12 16:03 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 4+ messages in thread
From: Scott Ribe @ 2013-07-12 16:03 UTC (permalink / raw)
To: Mike Broers <mbroers@gmail.com>; +Cc: pgsql-admin
On Jul 12, 2013, at 9:59 AM, Mike Broers wrote:
> Thanks, did you use tcpdump to determine that? I'm happy to learn a new tool (to me), but wanted to make sure I wasnt overlooking something at my disposal within postgres.
When I said "connecting via libpq" I meant I inserted timers in some C code, and looped through some calls to PQconnectdb.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
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] 4+ messages in thread
end of thread, other threads:[~2013-07-12 16:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-12 15:00 logging or debugging to report time to establish a database connection Mike Broers <mbroers@gmail.com>
2013-07-12 15:21 ` Scott Ribe <scott_ribe@elevated-dev.com>
2013-07-12 15:53 ` Scott Ribe <scott_ribe@elevated-dev.com>
2013-07-12 16:03 ` Scott Ribe <scott_ribe@elevated-dev.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