Обсуждение: [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 protectsthe 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. .) Transactionsare assigned a dummy xid ReadOnlyTransactionId, that is considered to be "later" than any other xid. .) Aglobal 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 readonlymode - 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 callMarkBufferDirty from within a critical section, and thus elog(ERRROR) is turned into elog(PANIC). This e.g. happenswith 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 totrack 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 willbe 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 postmasterrestart at least), but you get background replay .) No background replay, but once replay is done, readwritequeries 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 readonlymode. 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