Обсуждение: changing MyDatabaseId

I've spent a few hours pouring over the source code with
coarse-toothed comb, trying to figure out just exactly what might
break if we changed MyDatabaseId after backend startup time, or in
other words, allowed a backend to unbind from the database to which it
was originally bound and rebind to a new one.  This is related to the
periodic conversations we've had about a built-in connection pooler,
and/or maintaining a pool of worker threads that could be used to
service parallel query, replication sets, etc.  What follows is not
meant to be a concrete design proposal; it's basic research that may
lead to a proposal at some time in the future.  Still, comments are
welcome.

For the use cases that seem interesting, it is sufficient to think
about changing MyDatabaseId when there is no transaction in progress,
or perhaps, when there is a transaction in progress which does that
and nothing else.  This means that code that simply uses MyDatabaseId
in the course of going about its duties is not going to blow up.
However, anything that stores the current database ID someplace is a
hazard, as is any code that stores information which was obtained
using the current database ID.  Looking through the code, it appears
to me that we'd need to do the following (not necessarily in this
order):

1. Blow away all of our catalog caches as if we've received a sinval
reset.  Since most of our caches are hooked into the catalog cache via
invalidation hooks, this should trigger cascading invalidations of
other caches that need it, at least in most cases.  (Further looking
is probably needed to identify any cases where a
sinval-reset-equivalent is insufficient.)
2. Blow away absolutely everything from the relcache.  It appears to
me that we'd need to repeat the phase 2 and 3 initialization steps.
We can't even keep the entries for shared catalogs, because the
descriptors that formrdesc() coughs up may not exactly match what we
read from the pg_class entry.  It's tempting to propose adding enough
additional shared catalogs to make the relcache entries for shared
catalogs independent of any particular database's contents, but right
now they are not.
3. Reinitialize the relmapper.
4. Release all locks on objects in the old database; and our shared
lock on the database itself.  Take a new shared lock on the new
database.
5. Flush out any backend-local statistics that have been gathered but
not yet sent to the statistics collector.
6. Update our entry in the PgBackendStatus array so pg_stat_activity
sees the new database assignment.
7. Change MyProc->databaseId while holding ProcArrayLock in LW_EXCLUSIVE mode.
8. Update MyDatabaseTableSpace.

I haven't benchmarked this (so perhaps I should STFU) but #2 and maybe
#1 and #3 sounds like the most painful part of this activity.  Still,
there's not much help for it that I can see.  The best you can hope
for is to keep around entries to the shared catalogs, and that's not
going to get you terribly far, and at least ATM even that is unsafe.
I think maybe the thing to do is try to quantify how much time is
being spent in each part of the backend startup process - like connect
to the database lots and lots of times with individual connections,
run a trivial SELECT against a table, and then disconnect and repeat.
The problem is that (unless someone knows how to do magic tricks with
oprofile or dtrace) you're only going to find out the amount of time
actually spent executing each portion of the code.  It won't tell you
how much overhead you have from fork() itself, let alone how much
faster the initialization steps would have run if they had been
running on a warmed-up process address space rather than one that
might well need to fault in a lot of page-table entries in its first
few moments of life.  But it might still provide some useful data.
Thoughts?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> Looking through the code, it appears to me that we'd need to do the
> following (not necessarily in this order):

Don't forget

9. Unload loadable modules that do not exist according to the new
database's catalogs; eg we don't want postgis trying to run when
its supporting tables don't exist in the selected database.
10. Somehow persuade remaining loadable modules to discard whatever
state they have that might be database-local.

We don't have workable APIs for either of those operations ATM.

I believe also that there are probably race conditions in several of
the steps you listed; in particular there is certainly a risk involved
in changing the database-we-advertise-being-connected-to versus a
concurrent DROP DATABASE.  Maybe that's easily soluble, but I'm not sure
about it.
        regards, tom lane

Excerpts from Tom Lane's message of lun nov 15 02:41:40 -0300 2010:

> I believe also that there are probably race conditions in several of
> the steps you listed; in particular there is certainly a risk involved
> in changing the database-we-advertise-being-connected-to versus a
> concurrent DROP DATABASE.  Maybe that's easily soluble, but I'm not sure
> about it.

Perhaps it would help to first disconnect from the original database and
not reconnect to any one in particular, i.e. stay in a state like the
autovac launcher is in.  Then cleanup as per Robert's list, then switch
to a valid database ID.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Nov 15, 2010 at 12:41 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Looking through the code, it appears to me that we'd need to do the
>> following (not necessarily in this order):
>
> Don't forget
>
> 9. Unload loadable modules that do not exist according to the new
> database's catalogs; eg we don't want postgis trying to run when
> its supporting tables don't exist in the selected database.

I might be missing something here, but I wasn't aware that the
catalogs had any say in what loadable modules exist.  Obviously their
contents will determine whether such loadable modules ever get invoked
as a result of actions at the SQL level, but if they don't it may not
matter much that they're loaded but idle.

> 10. Somehow persuade remaining loadable modules to discard whatever
> state they have that might be database-local.

Ouch.

> We don't have workable APIs for either of those operations ATM.

Somewhat independently of this problem, it would be nice to be able to
unload a loadable module, and I think that we currently don't support
that at all.  One thing (the only thing?) that makes that a
show-stopper is that the way we manage hook function chaining makes it
impossible for a module to reliably unload itself.  If you load both
auto_explain and pg_stat_statements, for example, you end up with a
tangle of pointers that can't be easily unwound.  I don't have a real
clear idea how to fix it, but the status quo certainly doesn't seem
optimal.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert,

On 11/15/2010 05:39 AM, Robert Haas wrote:
> I've spent a few hours pouring over the source code with
> coarse-toothed comb, trying to figure out just exactly what might
> break if we changed MyDatabaseId after backend startup time, or in
> other words, allowed a backend to unbind from the database to which it
> was originally bound and rebind to a new one.  This is related to the
> periodic conversations we've had about a built-in connection pooler,
> and/or maintaining a pool of worker threads that could be used to
> service parallel query, replication sets, etc.  What follows is not
> meant to be a concrete design proposal; it's basic research that may
> lead to a proposal at some time in the future.  Still, comments are
> welcome.

Thanks a lot for doing that, saved me a couple hours (presumably more
than it cost you :-)

> Thoughts?

The question obviously is whether or not this is faster than just
terminating one backend and starting a new one. Which basically costs an
additional termination and re-creation of a process (i.e. fork())
AFAICS. Or what other savings do you envision?

If that's it, it certainly seems like a huge amount of work for very
little benefit. Or does this feature enable something that's impossible
to do otherwise?

Regards

Markus Wanner

On Wednesday 17 November 2010 11:04:04 Markus Wanner wrote:
> Robert,
> 
> On 11/15/2010 05:39 AM, Robert Haas wrote:
> > I've spent a few hours pouring over the source code with
> > coarse-toothed comb, trying to figure out just exactly what might
> > break if we changed MyDatabaseId after backend startup time, or in
> > other words, allowed a backend to unbind from the database to which it
> > was originally bound and rebind to a new one.  This is related to the
> > periodic conversations we've had about a built-in connection pooler,
> > and/or maintaining a pool of worker threads that could be used to
> > service parallel query, replication sets, etc.  What follows is not
> > meant to be a concrete design proposal; it's basic research that may
> > lead to a proposal at some time in the future.  Still, comments are
> > welcome.
> 
> Thanks a lot for doing that, saved me a couple hours (presumably more
> than it cost you :-)
> 
> > Thoughts?
> 
> The question obviously is whether or not this is faster than just
> terminating one backend and starting a new one. Which basically costs an
> additional termination and re-creation of a process (i.e. fork())
> AFAICS. Or what other savings do you envision?
Well, one could optimize most of the resetting away if the the old 
MyDatabaseId and the new one are the same  - an optimization which is hardly 
possible with forking new backends.

Also I think it could lower the impact due locking the procarray an related 
areas.

Andres

Andreas,

On 11/17/2010 11:38 AM, Andres Freund wrote:
> Well, one could optimize most of the resetting away if the the old 
> MyDatabaseId and the new one are the same  - an optimization which is hardly 
> possible with forking new backends.

Uh? Why not simply re-use the same backend, then? Or do you think of
re-connecting to the same database as a way to reset your connection?

> Also I think it could lower the impact due locking the procarray an related 
> areas.

That may be, yes. But as pointed out by Tom and Alvaro, you'd have to
adhere to a compatible sequence of changes to shared memory to avoid
race conditions. That possibly requires using a very similar locking
sequence as the combination of a detaching and a newly starting backend
would use.

Regards

Markus Wanner

On Wednesday 17 November 2010 11:58:33 Markus Wanner wrote:
> Andreas,
> 
> On 11/17/2010 11:38 AM, Andres Freund wrote:
> > Well, one could optimize most of the resetting away if the the old
> > MyDatabaseId and the new one are the same  - an optimization which is
> > hardly possible with forking new backends.
> Uh? Why not simply re-use the same backend, then? Or do you think of
> re-connecting to the same database as a way to reset your connection?
I am thinking of a connection-pooler like setup. Quite often your main-load 
goes towards a single database - in that situation you don't have to reset the 
database id most of the time.

Andres

On 11/17/2010 12:09 PM, Andres Freund wrote:
> I am thinking of a connection-pooler like setup. Quite often your main-load 
> goes towards a single database - in that situation you don't have to reset the 
> database id most of the time.

Okay, so that's what I'd call a connection-reset or something. Or
probably a re-authentication feature which allows switching users (and
thus permissions on that same database). Could make sense for a
connection pooler, yes. Not having to flush caches in that case could
even turn out to be a good win. And I think it's a lot simpler to just
switch the user than to switch the database. Such a feature looks more
promising to me. (Do we have other connection related and unchangeable
state?)

As side notes: for the bgworkers in Postgres-R, I'm currently re-using
existing backends. As they only do change set application, the amount of
connection-reset required is minimal (well, there isn't a client
connection for these kinds of backends, in the first place, but that's
another story). Plus they are always acting as superuser, no
authentication or user switching required in that case.

For parallel querying as well as async transactions, it might make sense
to be able to switch users but remain connected to the same database.

Regards

Markus Wanner

Excerpts from Markus Wanner's message of mié nov 17 07:04:04 -0300 2010:

> > Thoughts?
> 
> The question obviously is whether or not this is faster than just
> terminating one backend and starting a new one. Which basically costs an
> additional termination and re-creation of a process (i.e. fork())
> AFAICS. Or what other savings do you envision?

I don't think it's a speed thing only.  It would be a great thing to
have in autovacuum, for example, where we have constant problem reports
because the system failed to fork a new backend.  If we could simply
reuse an already existing one, it would be a lot more robust.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 11/17/2010 01:27 PM, Alvaro Herrera wrote:
> I don't think it's a speed thing only.  It would be a great thing to
> have in autovacuum, for example, where we have constant problem reports
> because the system failed to fork a new backend.  If we could simply
> reuse an already existing one, it would be a lot more robust.

Hm, that's an interesting point.

To actually increase robustness, it would have to be a failure scenario
that (temporarily) prevents forking, but allows an existing backend to
continue to do work (i.e. the ability to allocate memory or open files
come to mind).

Any idea about what's usually causing these fork() failures? I'm asking
because I'm afraid that for example, in case of an out of memory
condition, we'd just hit an OOM error later on, without being able to
perform the VACUUM job, either.

Regards

Markus Wanner

Excerpts from Markus Wanner's message of mié nov 17 09:57:18 -0300 2010:
> On 11/17/2010 01:27 PM, Alvaro Herrera wrote:
> > I don't think it's a speed thing only.  It would be a great thing to
> > have in autovacuum, for example, where we have constant problem reports
> > because the system failed to fork a new backend.  If we could simply
> > reuse an already existing one, it would be a lot more robust.
> 
> Hm, that's an interesting point.
> 
> To actually increase robustness, it would have to be a failure scenario
> that (temporarily) prevents forking, but allows an existing backend to
> continue to do work (i.e. the ability to allocate memory or open files
> come to mind).

Well, the autovacuum mechanism involves a lot of back-and-forth between
launcher and postmaster, which includes some signals, a fork() and
backend initialization.  The failure possibilities are endless.

Fork failure communication is similarly brittle.

> Any idea about what's usually causing these fork() failures? I'm asking
> because I'm afraid that for example, in case of an out of memory
> condition, we'd just hit an OOM error later on, without being able to
> perform the VACUUM job, either.

To be honest I have no idea.  Sometimes the server is just too loaded.
Right now we have this "delay", if the process is not up and running in
60 seconds then we have to assume that "something" happened, and we no
longer wait for it.  If we knew the process was already there, we could
leave it alone; we'd know it would get to its duty eventually.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 11/17/2010 02:19 PM, Alvaro Herrera wrote:
> Well, the autovacuum mechanism involves a lot of back-and-forth between
> launcher and postmaster, which includes some signals, a fork() and
> backend initialization.  The failure possibilities are endless.
> 
> Fork failure communication is similarly brittle.

I certainly agree to that. However, a re-connecting mechanism wouldn't
allow us to get rid of the existing avworker startup infrastructure
entirely.

And for increased robustness, we'd require a less brittle re-connecting
mechanism. Given Robert's list, that doesn't seem trivial, either. (But
still doable, yes).

> Right now we have this "delay", if the process is not up and running in
> 60 seconds then we have to assume that "something" happened, and we no
> longer wait for it.  If we knew the process was already there, we could
> leave it alone; we'd know it would get to its duty eventually.

You are assuming presence of pool here. Which is fine, it's just not
something that a re-connecting feature would solve per se. (Says he who
coded the bgworkers pool thingie).

Regards

Markus Wanner

Markus Wanner <markus@bluegap.ch> writes:
> On 11/17/2010 02:19 PM, Alvaro Herrera wrote:
>> Well, the autovacuum mechanism involves a lot of back-and-forth between
>> launcher and postmaster, which includes some signals, a fork() and
>> backend initialization.  The failure possibilities are endless.
>> 
>> Fork failure communication is similarly brittle.

> I certainly agree to that. However, a re-connecting mechanism wouldn't
> allow us to get rid of the existing avworker startup infrastructure
> entirely.

I'm afraid that any such change would trade a visible, safe failure
mechanism (no avworker) for invisible, impossible-to-debug data
corruption scenarios (due to failure to reset some bit of cached state).
It certainly won't give me any warm fuzzy feeling that I can trust
autovacuum.
        regards, tom lane

On 11/17/2010 04:25 PM, Tom Lane wrote:
> I'm afraid that any such change would trade a visible, safe failure
> mechanism (no avworker) for invisible, impossible-to-debug data
> corruption scenarios (due to failure to reset some bit of cached state).
> It certainly won't give me any warm fuzzy feeling that I can trust
> autovacuum.

Well, Alvaro doesn't quite seem have a warm fuzzy feeling with the
status quo, either. And I can certainly understand his concerns.

But yes, the os-level process separation and cache state reset guarantee
that an exit() / fork() pair provides is hard to match up against in
user space.

So, Alvaro's argument for robustness only stands under the assumption
that we can achieve a perfect cache state reset mechanism. Now, how
feasible is that? Are there any kind of tools that could help us check?

Regards

Markus Wanner

On Wed, Nov 17, 2010 at 5:04 AM, Markus Wanner <markus@bluegap.ch> wrote:
> The question obviously is whether or not this is faster than just
> terminating one backend and starting a new one.

I agree.

> Which basically costs an
> additional termination and re-creation of a process (i.e. fork())
> AFAICS. Or what other savings do you envision?

I think the thing I'd like to do (or see somebody else do) is write a
test harness that connects to the database, runs a single SELECT *
FROM dual (one row, one column table), and then disconnects; and then
oprofile the daylights out of the backend processes.  In other words,
I'd like to measure as exactly as we can the overhead of each part of
the startup process.  I think that would give us a clearer picture of
where the overhead is, and then we could look more directly at which
line items might be avoidable for rebinding to a new database.

However, that test doesn't capture everything.  For example, imagine a
connection pooler sitting in front of PG.  Rebinding to a new database
means disconnecting a TCP connection and establishing a new one.
Switching databases might save some latency there even if we don't
actually save much in terms of CPU instructions.  Maybe that's not
important, though.  I don't know.  I don't want to let my theorizing
get too far ahead of the data.

It also occurs to me to wonder whether there's some way that we can
speed up backend startup, period.  One of the frequent complaints
levied against PostgreSQL is that our connections are too expensive.
AFAICT, this is partly because backend startup costs are high, and
partly because of internal contention, especially around
ProcArrayLock.  Squeezing on the startup overhead will be valuable no
matter what we decide to do about database switching.  A variant on
this theme would be to try to adjust the startup sequence in some way
so that less of it needs to be redone if we switch databases, which
might be possible even if a more general solution isn't.
Unfortunately, I'm not sure how feasible this is, but maybe there's a
way...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Nov 17, 2010 at 4:52 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> However, that test doesn't capture everything.  For example, imagine a
> connection pooler sitting in front of PG.  Rebinding to a new database
> means disconnecting a TCP connection and establishing a new one.
> Switching databases might save some latency there even if we don't
> actually save much in terms of CPU instructions.  Maybe that's not
> important, though.  I don't know.  I don't want to let my theorizing
> get too far ahead of the data.

Everything you said is true but there's more. A freshly created
backend needs to build relcache entries and for every relation in your
query. A reused connection eventually warms up the relcache and
syscaches and can plan new queries using them without doing any
syscalls. And of course if it's a query that's already been planned
might be able to reuse the entire plan structure without replanning
it.


--
greg

On Wed, Nov 17, 2010 at 12:42 PM, Greg Stark <gsstark@mit.edu> wrote:
> On Wed, Nov 17, 2010 at 4:52 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> However, that test doesn't capture everything.  For example, imagine a
>> connection pooler sitting in front of PG.  Rebinding to a new database
>> means disconnecting a TCP connection and establishing a new one.
>> Switching databases might save some latency there even if we don't
>> actually save much in terms of CPU instructions.  Maybe that's not
>> important, though.  I don't know.  I don't want to let my theorizing
>> get too far ahead of the data.
>
> Everything you said is true but there's more. A freshly created
> backend needs to build relcache entries and for every relation in your
> query. A reused connection eventually warms up the relcache and
> syscaches and can plan new queries using them without doing any
> syscalls. And of course if it's a query that's already been planned
> might be able to reuse the entire plan structure without replanning
> it.

I think you're missing the point.  If we switch databases, all cached
relations and plans have to be flushed anyway.  We're talking about
what might NOT need to be flushed on switching databases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Nov 17, 2010 at 6:33 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> I think you're missing the point.  If we switch databases, all cached
> relations and plans have to be flushed anyway.  We're talking about
> what might NOT need to be flushed on switching databases.

Oh sorry, yes, I missed that point.

I will mention that your point about TCP connection establishment
latency is real. TCP connection establishment adds several
milliseconds of latency (for low latency connections -- obviously it's
much much worse for long-haul connections) and then the congestion
control slow start adds more if there's a significant amount of data
to transfer.

--
greg

Alvaro Herrera wrote:
> Excerpts from Tom Lane's message of lun nov 15 02:41:40 -0300 2010:
> 
> > I believe also that there are probably race conditions in several of
> > the steps you listed; in particular there is certainly a risk involved
> > in changing the database-we-advertise-being-connected-to versus a
> > concurrent DROP DATABASE.  Maybe that's easily soluble, but I'm not sure
> > about it.
> 
> Perhaps it would help to first disconnect from the original database and
> not reconnect to any one in particular, i.e. stay in a state like the
> autovac launcher is in.  Then cleanup as per Robert's list, then switch
> to a valid database ID.

One question is whether we keep our caches in the hope we will continue
with the same database, or clear our caches so we don't have to incur
the overhead of clearing them when we connect a new session.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +