[RFC] GSoC Work on readonly queries done so far
Hi
This is a description of the GSoC work I've so for on readonly
support for PITR slaves. I'm looking for any kind of comments
on this - I want to make sure that I work in a direction that
the community approves.
Work done so far:
-----------------
.) Added a new GUC operational_mode, which can be set to either
readwrite or readonly. If it is set to readwrite (the default),
postgres behaves as usual. All the following changes are only
in effect if operational_mode is set to readonly.
.) Created a macro ASSUME_OPMODE_READWRITE that does elog(ERROR)
if postgre is not in readwrite mode. This macro protects the
following functions to make sure that no writes occur in
readonly mode.
SimpleLruWritePage, SLruPhysicalWritePage
EndPrepare, FinishPreparedTransaction
XLogInsert, XLogWrite, ShutdownXLog
CreateCheckpoint
MarkBufferDirty.
.) All transactions are set to readonly mode (An implicit
SET TRANSACTION READONLY), and are not allowed to do
SET TRANSACTION READWRITE.
.) Don't start autovacuum and bgwriter. Instead of bgwriter, bgreplay
is started, and it takes over that role that bgwriter play in the
shutdown process.
.) Transactions are assigned a dummy xid ReadOnlyTransactionId, that
is considered to be "later" than any other xid.
.) A global ReadOnlySnapshot is maintained in shared memory. This is
copied into backend local memory by GetReadonlySnapshotData (which
replaces GetSnapshotData in readonly mode).
.) Crash recovery is not performed in readonly mode - instead, postgres
PANICs, and tells the DBA to restart in readwrite mode. Archive
recovery of course *will* be allowed, but I'm not that far yet.
Open Problems:
--------------
.) Protecting MarkBufferDirty with ASSUME_OPMODE_READWRITE is troublesome,
because callers usually call MarkBufferDirty from within a critical
section, and thus elog(ERRROR) is turned into elog(PANIC). This e.g.
happens with my patch if you call nextval() in readonly mode.
Does anyone see a better solution then adding checks into
all callers that are not otherwise protected from being called
in readonly mode?
.) Since the slaves needs to track an Snapshot in shared memory, it cannot
resize that snapshot to accomodate however many concurrent transactions
might have been running on the master. My current plan is to detect if
that global snapshot overflows, and to lock out readonly queries on the
slave (and therefore remove the need of keeping the snapshot current)
until the number of active xids on the master has dropped below
max_connections on the slave. A warning will be written to the postgres
log that suggest that the DBA increases the max_connections value on
the slave.
Please tell me what you think about this approach, and especially if you
see any problems that I overlooked.
greetings, Florian Pflug
Florian G. Pflug wrote:
Work done so far:
-----------------
.) Don't start autovacuum and bgwriter.
Do table stats used by the planner get replicated on a PITR slave? I
assume so, but if not, you would need autovac to do analyzes.
On Wed, 2007-06-06 at 16:11 +0200, Florian G. Pflug wrote:
.) Since the slaves needs to track an Snapshot in shared memory, it cannot
resize that snapshot to accomodate however many concurrent transactions
might have been running on the master. My current plan is to detect if
that global snapshot overflows, and to lock out readonly queries on the
slave (and therefore remove the need of keeping the snapshot current)
until the number of active xids on the master has dropped below
max_connections on the slave. A warning will be written to the postgres
log that suggest that the DBA increases the max_connections value on
the slave.
If we did lock the slave while waiting for transactions to complete on
the master, we'd need to document some stronger warnings against idle
transactions so that administrators could notice and correct the
problem.
Are you referring to the size of the xip array being a problem? Would it
help to tie the size of the xip array to max_connections? I understand
that max_connections might be greater on the master, but maybe something
similar?
Regards,
Jeff Davis
Matthew T. O'Connor wrote:
Florian G. Pflug wrote:
Work done so far:
-----------------
.) Don't start autovacuum and bgwriter.Do table stats used by the planner get replicated on a PITR slave? I
assume so, but if not, you would need autovac to do analyzes.
Yes - everything that get wal-logged on the master gets replicated to
the slave. In my design, it isn't possible to do "analyze" on the slave,
because all datafiles are strictly readonly (well, with the small
exception of hit-bit updates actually).
greetings, Florian Pflug
Jeff Davis wrote:
On Wed, 2007-06-06 at 16:11 +0200, Florian G. Pflug wrote:
.) Since the slaves needs to track an Snapshot in shared memory, it cannot
resize that snapshot to accomodate however many concurrent transactions
might have been running on the master. My current plan is to detect if
that global snapshot overflows, and to lock out readonly queries on the
slave (and therefore remove the need of keeping the snapshot current)
until the number of active xids on the master has dropped below
max_connections on the slave. A warning will be written to the postgres
log that suggest that the DBA increases the max_connections value on
the slave.If we did lock the slave while waiting for transactions to complete on
the master, we'd need to document some stronger warnings against idle
transactions so that administrators could notice and correct the
problem.
It's not exactly locking until it complete on the master, it's locking
the slave until we reach a position in the wal on the slave with less
than max_connections concurrent transactions. But yes, I agree, this
will need to be documented.
Are you referring to the size of the xip array being a problem? Would it
help to tie the size of the xip array to max_connections? I understand
that max_connections might be greater on the master, but maybe something
similar?
Thats what I currently do - the xip array on the slave is sized to
hold max_connections entries (Actually, it's max_connections +
max_prepared_xacts I think). The problem occurs exactly if those
values are set too small on the slave - and since shared mem
objects are not resizeable, I don't see how the slave can handle
an xip overflow gracefully other than by not publishing the
information in shared memory as long as it doesn't fit there.
On a further thinking - maybe locking out transactions isn't even
necessary - they would just continue to see the old global snapshot,
so time wouldn't advance for them until the number of concurrent
transactions decreases again.
greetings, Florian Pflug
On Wed, 2007-06-06 at 19:25 +0200, Florian G. Pflug wrote:
Thats what I currently do - the xip array on the slave is sized to
hold max_connections entries (Actually, it's max_connections +
max_prepared_xacts I think). The problem occurs exactly if those
values are set too small on the slave - and since shared mem
objects are not resizeable, I don't see how the slave can handle
an xip overflow gracefully other than by not publishing the
information in shared memory as long as it doesn't fit there.
That seems like a very minor issue then. It's not unreasonable to expect
that the PITR slave is configured very similarly to the master. You may
even want to require it (if there are other reasons, too).
On a further thinking - maybe locking out transactions isn't even
necessary - they would just continue to see the old global snapshot,
so time wouldn't advance for them until the number of concurrent
transactions decreases again.
That sounds better than locking out all reads. Perhaps still a warning
in the logs though. If the situation you describe happens, the
administrator probably needs to know about it.
Regards,
Jeff Davis
On Wed, 2007-06-06 at 12:17 -0400, Matthew T. O'Connor wrote:
Florian G. Pflug wrote:
Work done so far:
-----------------
.) Don't start autovacuum and bgwriter.Do table stats used by the planner get replicated on a PITR slave? I
assume so, but if not, you would need autovac to do analyzes.
The replication is an exact block-level replication of the master. We
can't write very much at all on the slave.
So if a query runs slow because of lack of stats you'd need to run
ANALYZE on the master, which would then propagate the stats to the slave
which could then use them.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote:
On Wed, 2007-06-06 at 12:17 -0400, Matthew T. O'Connor wrote:
Florian G. Pflug wrote:
Work done so far:
-----------------
.) Don't start autovacuum and bgwriter.Do table stats used by the planner get replicated on a PITR slave? I
assume so, but if not, you would need autovac to do analyzes.The replication is an exact block-level replication of the master. We
can't write very much at all on the slave.
Hmm, something to keep in mind is forcing cache invals when the master
causes them (for example relation cache, catalog caches and plan
caches).
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Wed, 2007-06-06 at 16:11 +0200, Florian G. Pflug wrote:
.) Added a new GUC operational_mode, which can be set to either
readwrite or readonly. If it is set to readwrite (the default),
postgres behaves as usual. All the following changes are only
in effect if operational_mode is set to readonly.
Do we need this? We are already InArchiveRecovery.
.) Created a macro ASSUME_OPMODE_READWRITE that does elog(ERROR)
if postgre is not in readwrite mode. This macro protects the
following functions to make sure that no writes occur in
readonly mode.
SimpleLruWritePage, SLruPhysicalWritePage
EndPrepare, FinishPreparedTransaction
XLogInsert, XLogWrite, ShutdownXLog
CreateCheckpoint
MarkBufferDirty.
These are Asserts?
.) All transactions are set to readonly mode (An implicit
SET TRANSACTION READONLY), and are not allowed to do
SET TRANSACTION READWRITE.
OK
.) Don't start autovacuum and bgwriter. Instead of bgwriter, bgreplay
is started, and it takes over that role that bgwriter play in the
shutdown process.
Autovacuum -> understood.
What does bgreplay do? Why not just start bgwriter earlier and disable
some of its other functionality while InRecovery?
.) Transactions are assigned a dummy xid ReadOnlyTransactionId, that
is considered to be "later" than any other xid.
So you are bumping FirstNormalTransactionId up by one for this?
You're assuming then that we will "freeze" replay while we run a query?
Otherwise doing this will mean the snapshot changes as a query executes.
.) A global ReadOnlySnapshot is maintained in shared memory. This is
copied into backend local memory by GetReadonlySnapshotData (which
replaces GetSnapshotData in readonly mode).
.) Crash recovery is not performed in readonly mode - instead, postgres
PANICs, and tells the DBA to restart in readwrite mode. Archive
recovery of course *will* be allowed, but I'm not that far yet.
This is the very heart of the matter. This isn't just a technical issue,
it goes to the heart of the use case for this feature. Can we recover
while running queries? If not, how much time will we spend in replay
mode v query mode? Will we be able to run long running queries *and*
maintain a reasonable time to recover? Is this a mechanism for providing
HA and additional query capacity, or is it just a mechanism for
additional query capacity only? Those are open questions to which I
don't have any answers yet myself.
Will we switch back and forth between replay and query mode.
Do we connect to the master, or to the slave?
If we connect to the slave will we accept new queries when in replay
mode and pause them before we switch back to query mode.
Open Problems:
--------------
.) Protecting MarkBufferDirty with ASSUME_OPMODE_READWRITE is troublesome,
because callers usually call MarkBufferDirty from within a critical
section, and thus elog(ERRROR) is turned into elog(PANIC). This e.g.
happens with my patch if you call nextval() in readonly mode.
Does anyone see a better solution then adding checks into
all callers that are not otherwise protected from being called
in readonly mode?
Do we need to do this at all?
.) Since the slaves needs to track an Snapshot in shared memory, it cannot
resize that snapshot to accomodate however many concurrent transactions
might have been running on the master. My current plan is to detect if
that global snapshot overflows, and to lock out readonly queries on the
slave (and therefore remove the need of keeping the snapshot current)
until the number of active xids on the master has dropped below
max_connections on the slave. A warning will be written to the postgres
log that suggest that the DBA increases the max_connections value on
the slave.
Sized according to max_connections on the master?
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
On Wed, 2007-06-06 at 17:14 -0400, Alvaro Herrera wrote:
Simon Riggs wrote:
On Wed, 2007-06-06 at 12:17 -0400, Matthew T. O'Connor wrote:
Florian G. Pflug wrote:
Work done so far:
-----------------
.) Don't start autovacuum and bgwriter.Do table stats used by the planner get replicated on a PITR slave? I
assume so, but if not, you would need autovac to do analyzes.The replication is an exact block-level replication of the master. We
can't write very much at all on the slave.Hmm, something to keep in mind is forcing cache invals when the master
causes them (for example relation cache, catalog caches and plan
caches).
Many things will need to work radically differently.
Best we think of this as Research rather than Development.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Alvaro Herrera wrote:
Simon Riggs wrote:
On Wed, 2007-06-06 at 12:17 -0400, Matthew T. O'Connor wrote:
Florian G. Pflug wrote:
Work done so far:
-----------------
.) Don't start autovacuum and bgwriter.Do table stats used by the planner get replicated on a PITR slave? I
assume so, but if not, you would need autovac to do analyzes.The replication is an exact block-level replication of the master. We
can't write very much at all on the slave.Hmm, something to keep in mind is forcing cache invals when the master
causes them (for example relation cache, catalog caches and plan
caches).
Perhaps if you are as PITR master and you have active readonly slaves
then there should be a WAL record to note plan invalidations, etc?
On Wed, 2007-06-06 at 22:36 +0100, Simon Riggs wrote:
.) Transactions are assigned a dummy xid ReadOnlyTransactionId, that
is considered to be "later" than any other xid.So you are bumping FirstNormalTransactionId up by one for this?
You're assuming then that we will "freeze" replay while we run a query?
Otherwise doing this will mean the snapshot changes as a query executes.
Is it possible to put a normal xmax for the snapshot?
It wouldn't be a real transaction on the slave, and also the master will
use that ID for a real transaction itself. However, I don't see a real
problem on the slave because it would only be used for the purpose of
the snapshot we need at that moment.
Regards,
Jeff Davis
Florian G. Pflug wrote:
Jeff Davis wrote:
Are you referring to the size of the xip array being a problem? Would it
help to tie the size of the xip array to max_connections? I understand
that max_connections might be greater on the master, but maybe something
similar?Thats what I currently do - the xip array on the slave is sized to
hold max_connections entries (Actually, it's max_connections +
max_prepared_xacts I think). The problem occurs exactly if those
values are set too small on the slave - and since shared mem
objects are not resizeable, I don't see how the slave can handle
an xip overflow gracefully other than by not publishing the
information in shared memory as long as it doesn't fit there.
You could store the value of max_connections in the checkpoint xlog
record, and read it from there in the slave. Though one could still
change it on the master and restart without restarting the slave as well.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote:
On Wed, 2007-06-06 at 16:11 +0200, Florian G. Pflug wrote:
.) Added a new GUC operational_mode, which can be set to either
readwrite or readonly. If it is set to readwrite (the default),
postgres behaves as usual. All the following changes are only
in effect if operational_mode is set to readonly.Do we need this? We are already InArchiveRecovery.
If I understand you correctly, you suggest that readonly queries
are allways allowed during archive recovery - so upon startup
postgres step through these states:
.) Initial recovery (Until we reach a consistent state)
.) Allow readonly queries
.) Finish recovery in the background (might mean recovering "forever"
on a PITR slave)
.) Allow readwrite queries
My plan was to have a global switch, which lets you choose between
.) All queries are readonly (Until the next postmaster restart at least),
but you get background replay
.) No background replay, but once replay is done, readwrite queries
can be execute (Just what PG does now).
The main reason why I invented that global switch operational_mode was
to remove to need to switch between readonly mode and readwrite mode
on the fly.
.) Created a macro ASSUME_OPMODE_READWRITE that does elog(ERROR)
if postgre is not in readwrite mode. This macro protects the
following functions to make sure that no writes occur in
readonly mode.
SimpleLruWritePage, SLruPhysicalWritePage
EndPrepare, FinishPreparedTransaction
XLogInsert, XLogWrite, ShutdownXLog
CreateCheckpoint
MarkBufferDirty.These are Asserts?
The macro ASSUME_OPMODE_READWRITE just does
if (!OperationalModeReadWrite)
elog(ERROR, ...)
.) Don't start autovacuum and bgwriter. Instead of bgwriter, bgreplay
is started, and it takes over that role that bgwriter play in the
shutdown process.Autovacuum -> understood.
What does bgreplay do? Why not just start bgwriter earlier and disable
some of its other functionality while InRecovery?
See above - it seemed simpler to clearly seperate
.) Transactions are assigned a dummy xid ReadOnlyTransactionId, that
is considered to be "later" than any other xid.So you are bumping FirstNormalTransactionId up by one for this?
In fact I changed MaxTransactionId to 0xFFFFFFFE, and set
ReadOnlyTransactionId to 0xFFFFFFFF. Additionally, I changed
IsNormalTransactionId to test not only for >= FirstNormalTransactionid,
but also for <= MaxTransactionId.
You're assuming then that we will "freeze" replay while we run a query?
No. My plan is to first get to a point where replay is freezes while
queries are running, and to then figure out a more intelligent way to do this.
I already have a few ideas how to do this, but I want to complete the "simple
version", before I start with that work.
Otherwise doing this will mean the snapshot changes as a query executes.
Why? It's only the xid of the transaction, not it's xmin and xmax that are
set to ReadOnlyTransactionId.
.) A global ReadOnlySnapshot is maintained in shared memory. This is
copied into backend local memory by GetReadonlySnapshotData (which
replaces GetSnapshotData in readonly mode).
.) Crash recovery is not performed in readonly mode - instead, postgres
PANICs, and tells the DBA to restart in readwrite mode. Archive
recovery of course *will* be allowed, but I'm not that far yet.This is the very heart of the matter. This isn't just a technical issue,
it goes to the heart of the use case for this feature. Can we recover
while running queries?
Yes. My comment only applies only to crash recovery - i.e, recovery that happens
*without* a recovery.conf present, after a crash.
It only really matters if you do following
.) Start pg in readwrite mode.
.) Kill it / It crashes
.) Restart in readonly mode.
The main different between crash recovery, and recovery from a filesystem-level
backup is the additional information that the backup label gives us in the
second case - more specifically, the minRecoveryLoc that we read from the
backup label. Only with that knowledge is "recovering until we reach
a consistent state" a welldefined operation. And readonly queries
can only be executed *after* we did this "minimal recovery". So if there is
crash recovery to be done, we best we could do is to recover, and then start
in readonly mode. If this is *really* what the DBA wants, he can just start
in readwrite mode first, then cleanly shut PG down, and restart in readonly mode.
If not, how much time will we spend in replay
mode v query mode? Will we be able to run long running queries *and*
maintain a reasonable time to recover? Is this a mechanism for providing
HA and additional query capacity, or is it just a mechanism for
additional query capacity only? Those are open questions to which I
don't have any answers yet myself.
My goal is to allow replay and queries to run concurrently, at least as
long as only inserts, updates and deletes to non-system tables are replayed
(Which I assume is 99% of the wal-traffic on a typical productive pg
installation).
Will we switch back and forth between replay and query mode.
Do we connect to the master, or to the slave?
If we connect to the slave will we accept new queries when in replay
mode and pause them before we switch back to query mode.
*If* the replay process has to pause query execution, it will
acquire some global lock in write mode. That same lock will be acquired
in read mode if a client starts a transaction. So waiting for the recovery
process will "feel" just the same as waiting for another query to release
a lock.
Open Problems:
--------------
.) Protecting MarkBufferDirty with ASSUME_OPMODE_READWRITE is troublesome,
because callers usually call MarkBufferDirty from within a critical
section, and thus elog(ERRROR) is turned into elog(PANIC). This e.g.
happens with my patch if you call nextval() in readonly mode.
Does anyone see a better solution then adding checks into
all callers that are not otherwise protected from being called
in readonly mode?Do we need to do this at all?
We need a waterproof protection against writes in readonly mode. The current
"set transaction readonly" is far away from being that - it e.g. allows
nextval(). So protecting MarkBufferDirty seems like a good last-resort
protection - even a PANIC is *much* better than just silently changing data
on the slave I think.
.) Since the slaves needs to track an Snapshot in shared memory, it cannot
resize that snapshot to accomodate however many concurrent transactions
might have been running on the master. My current plan is to detect if
that global snapshot overflows, and to lock out readonly queries on the
slave (and therefore remove the need of keeping the snapshot current)
until the number of active xids on the master has dropped below
max_connections on the slave. A warning will be written to the postgres
log that suggest that the DBA increases the max_connections value on
the slave.Sized according to max_connections on the master?
Well, but how do I get that value? And the master might be restarted with
a different max_connections value while the slave keeps running...
greetings, Florian Pflug
Heikki Linnakangas wrote:
Florian G. Pflug wrote:
Jeff Davis wrote:
Are you referring to the size of the xip array being a problem? Would it
help to tie the size of the xip array to max_connections? I understand
that max_connections might be greater on the master, but maybe something
similar?Thats what I currently do - the xip array on the slave is sized to
hold max_connections entries (Actually, it's max_connections +
max_prepared_xacts I think). The problem occurs exactly if those
values are set too small on the slave - and since shared mem
objects are not resizeable, I don't see how the slave can handle
an xip overflow gracefully other than by not publishing the
information in shared memory as long as it doesn't fit there.You could store the value of max_connections in the checkpoint xlog
record, and read it from there in the slave. Though one could still
change it on the master and restart without restarting the slave as well.
But AFAIK shmem allocation happens before recovery starts... Even if this
was solved, it would only be a partial solution since as you note, the
master might be restarted while the slave keeps running. So I think it's
better not too add too much complexity, and just tell the DBA to increase
max_connections on the slave, together with a comment in the documentation
never to sex max_connections smaller on the slave than on the master.
greetings, Florian Pflug
Jeff Davis wrote:
On Wed, 2007-06-06 at 22:36 +0100, Simon Riggs wrote:
.) Transactions are assigned a dummy xid ReadOnlyTransactionId, that
is considered to be "later" than any other xid.So you are bumping FirstNormalTransactionId up by one for this?
You're assuming then that we will "freeze" replay while we run a query?
Otherwise doing this will mean the snapshot changes as a query executes.Is it possible to put a normal xmax for the snapshot?
It wouldn't be a real transaction on the slave, and also the master will
use that ID for a real transaction itself. However, I don't see a real
problem on the slave because it would only be used for the purpose of
the snapshot we need at that moment.
My plan is the following:
.) Initially, queries and recovery will run interleaved, but not concurrently.
For that, an "empty" snapshot is sufficient, with
xmin=xid=xmax=ReadOnlyTransactionId.
.) Then, I'll work on running them concurrently. The replay process will publish
a "current" snapshot in shared memory, using "real" xmin and xmax values
it generates by maintaining a list of currently active (as in: running when
the wal was written on the master) transactions. In that case, only xid
is set to ReadOnlyTransactionId.
greetings, Florian Pflug