Re: Proposal for Implenting read-only queries during wal replay (SoC 2007) - Mailing list pgsql-hackers
From | Doug Knight |
---|---|
Subject | Re: Proposal for Implenting read-only queries during wal replay (SoC 2007) |
Date | |
Msg-id | 1172249801.29320.34.camel@arc-dknightlx.wsicorp.com Whole thread Raw |
In response to | Proposal for Implenting read-only queries during wal replay (SoC 2007) ("Florian G. Pflug" <fgp@phlo.org>) |
List | pgsql-hackers |
Hi,<br /> Here's some feedback, this is a feature that would be very useful to a project I am currently working on. <br/><br /> Doug<br /><br /> On Fri, 2007-02-23 at 17:34 +0100, Florian G. Pflug wrote: <blockquote type="CITE"><pre> <font color="#000000">Hi</font> <font color="#000000">I plan to submit a proposal for implementing support for</font> <font color="#000000">read-only queries during wal replay as a "Google Summer of Code 2007"</font> <font color="#000000">project.</font> <font color="#000000">I've been browsing the postgres source-code for the last few days,</font> <font color="#000000">and came up with the following plan for a implementation.</font> <font color="#000000">I'd be very interested in any feedback on the propsoal - especially</font> <font color="#000000">of the "you overlooked this an that, it can never work that way" kind ;-)</font> <font color="#000000">greetings, Florian Pflug</font> <font color="#000000">Implementing read-only quries during wal archive replay</font> <font color="#000000">-------------------------------------------------------</font> <font color="#000000">Submitter: Florian Pflug <<a href="mailto:fgp@phlo.org">fgp@phlo.org</a>></font> <font color="#000000">Abstract:</font> <font color="#000000">Implementing full support for read-only queries during</font> <font color="#000000">wal archive replay is splitted into multiple parts, where</font> <font color="#000000">each part offeres additional functionality over what</font> <font color="#000000">postgres provides now. This makes tackling this as a</font> <font color="#000000">"Google Summer of Code 2007" project feasable, and guarantees</font> <font color="#000000">that at least some progress is made, even if solving the</font> <font color="#000000">whole problem turns out to be harder then previously</font> <font color="#000000">thought.</font> <font color="#000000">Parts/Milestones of the implementation:</font> <font color="#000000">A) Allow postgres to be started in read-only mode. After</font> <font color="#000000"> initial wal recovery, postgres doesn't perform writes</font> <font color="#000000"> anymore. All transactions started are implicitly in</font> <font color="#000000"> readonly mode. All transactions will be assigned dummy</font> <font color="#000000"> transaction ids, which never make it into the clog.</font> <font color="#000000">B) Split StartupXLOG into two steps. The first (Recovery) will process</font> <font color="#000000"> only enough wal to bring the system into a consistent state,</font> <font color="#000000"> while the second one (Replay) replays the archive until it finds no</font> <font color="#000000"> more wal segments. This replay happens in chunks, such that</font> <font color="#000000"> after a chunk all *_safe_restartpoint functions return true.</font> <font color="#000000">C) Combine A) and B), in the simplest possible way.</font> <font color="#000000"> Introduce a global R/W lock, which is taken by the Replay part</font> <font color="#000000"> of B) in write mode before replaying a chunk, then released,</font> <font color="#000000"> and immediatly reaquired before replaying the next chunk.</font> <font color="#000000"> The startup sequence is modified to do only the Recovery part</font> <font color="#000000"> where is is doing StartupXLOG now, and to lauch an extra process</font> <font color="#000000"> (similar to bgwriter) to do the second (Replay) part in the background.</font> <font color="#000000"> The system is then started up in read-only mode, with the addition</font> <font color="#000000"> that the global R/W lock is taken in read mode before starting any</font> <font color="#000000"> transaction. Thus, while a transaction is running, no archive replay</font> <font color="#000000"> happens.</font> <font color="#000000">Benefits:</font> <font color="#000000">*) Part A) alone might be of value for some people in the embedded world,</font> <font color="#000000"> or people who want to distribute software the use postgres. You could</font> <font color="#000000"> e.g. distribute a CD with a large, read-only database, and your application</font> <font color="#000000"> would just need to start postmaster to be able to query it directly from</font> <font color="#000000"> the CD.</font> <font color="#000000">*) Read-only hot standby is a rather simple way to do load-balancing, if</font> <font color="#000000"> your application doesn't depend on the data being absolutely up-to-date.</font> <font color="#000000">*) Even if this isn't used for load-balancing, it gives the DBA an</font> <font color="#000000"> easy way to check how far a PITR slave is lagging behind, therefore</font> <font color="#000000"> making PITR replication more user-friendly.</font> <font color="#000000">Open Questions/Problems</font> <font color="#000000">*) How do read-only transactions obtain a snapshot? Is it sufficient</font> <font color="#000000"> to just create an "empty" snapshot for them, meaning that they'll</font> <font color="#000000"> always look at the clog to obtain a transaction's state?</font> <font color="#000000">*) How many places to attempt to issue writes? How hard is it to</font> <font color="#000000"> silence them all while in read-only mode.</font> <font color="#000000">*) How does the user interface look like? I'm currently leaning towards</font> <font color="#000000"> a postgresql.conf setting read_only=yes. This would put postgres</font> <font color="#000000"> into read-only mode, and if a recovery.conf is present, archive</font> <font color="#000000"> replay would run as a background process.</font> <font color="#000000">Limitations:</font> <font color="#000000">*) The replaying process might be starved, letting the slave fall</font> <font color="#000000"> further and further behind the master. Only true if the slave</font> <font color="#000000"> executes a lot of queries, though.</font> <font color="#000000">*) Postgres would continue to run in read-only mode, even after finishing</font> <font color="#000000"> archive recovery. A restart would be needed to switch it into read-write</font> <font color="#000000"> mode again. (I probably wouldn't be too hard to do that switch without</font> <font color="#000000"> a restart, but it seems better to tackle this after the basic features</font> <font color="#000000"> are working)</font> <font color="#000000">---------------------------(end of broadcast)---------------------------</font> <font color="#000000">TIP 5: don't forget to increase your free space map settings</font> </pre></blockquote>
pgsql-hackers by date: