Re: Pooling in Core WAS: Need help in performance tuning.

On Sat, 2010-07-24 at 14:36 +0800, Craig Ringer wrote:
> On 24/07/10 13:23, Greg Smith wrote:
> > Joshua Tolley wrote:
> >> Relatively minor, but it would be convenient to avoid having to query
> >> $external_pooler to determine the client_addr of an incoming connection.
> >>
> >
> > You suggest this as a minor concern, but I consider it to be one of the
> > most compelling arguments in favor of in-core pooling. A constant pain
> > with external poolers is the need to then combine two sources of data in
> > order to track connections fully, which is something that everyone runs
> > into eventually and finds annoying. It's one of the few things that
> > doesn't go away no matter how much fiddling you do with pgBouncer, it's
> > always getting in the way a bit. And it seems to seriously bother
> > systems administrators and developers, not just the DBAs.
>
>
> Putting a pooler in core won't inherently fix this, and won't remove the
> need to solve it for cases where the pooler can't be on the same machine.
>
> 9.0 has application_name to let apps identify themselves. Perhaps a
> "pooled_client_ip", to be set by a pooler rather than the app, could be
> added to address this problem in a way that can be used by all poolers
> new and existing, not just any new in-core pooling system.
>
> If a privileged set of pooler functions is was considered, as per my
> other recent mail, the pooler could use a management connection to set
> the client ip before handing the connection to the client, so the client
> couldn't change pooled_client_ip its self by accident or through malice.
> But even without that, it'd be awfully handy.

Or maybe we can add some command extensions to the protocol for passing
extra info, so that instead of sending just the "(run_query:query)"
command over socket we could send both the extra info and execute
"(set_params:(proxy_client_ip:a.b.c.d)(proxy_client_post:n)(something
else))(run_query:query)" in one packet (for performance) and have these
things be available in logging and pg_stat_activity

I see no need to try to somehow restrict these if you can always be sure
that they are set by the direct client. proxy can decide to pass some of
these from the real client but it would be a decision made by proxy, not
mandated by some proxying rules.

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training