Re: Proposal for Implenting read-only queries during wal replay (SoC 2007) - Mailing 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>