Re: profiling connection overhead

On Sun, Dec 5, 2010 at 11:59 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> * no coordination of restarts/configuration changes between the cluster
>> and the pooler
>> * you have two pieces of config files to configure your pooling settings
>> (having all that available say in a catalog in pg would be awesome)
>> * you lose all of the advanced authentication features of pg (because
>> all connections need to go through the pooler) and also ip-based stuff
>> * no SSL support(in the case of pgbouncer)
>> * complexity in reseting backend state (we added some support for that
>> through explicit SQL level commands in recent releases but it still is a
>> hazard)
>
> More:
>
> * pooler logs to separate file, for which there are (currently) no anaysis
> tools
> * pooling is incompatible with the use of ROLES for data security
>
> The last is a major issue, and not one I think we can easily resolve. MySQL
> has a pooling-friendly user system, because when you connect to MySQL you
> basically always connect as the superuser and on connection it switches you
> to your chosen login role.  This, per Rob Wulsch, is one of the things at
> the heart of allowing MySQL to support 100,000 low frequency users per cheap
> hosting system.
>
> As you might imagine, this behavior is also the source of a lot of MySQL's
> security bugs.  I don't see how we could imitate it without getting the bugs
> as well.
>
>

I think you have read a bit more into what I have said than is
correct. MySQL can deal with thousands of users and separate schemas
on commodity hardware. There are many design decisions (some
questionable) that have made MySQL much better in a shared hosting
environment than pg and I don't know where the grants system falls
into that.

MySQL does not have that many security problems because of how grants
are stored. Most MySQL security issues are DOS sort of stuff based on
a authenticated user being able to cause a crash. The decoupled
backend storage and a less than awesome parser shared most of the
blame for these issues.

One thing I would suggest that the PG community keeps in mind while
talking about built in connection process caching, is that it is very
nice feature for memory leaks caused by a connection to not exist for
and continue growing forever.

NOTE: 100k is not a number that I would put much stock in. I don't
recall ever mentioning that number and it is not a number that would
be truthful for me to throw out.

--
Rob Wultsch
wultsch(at)gmail(dot)com