Skip site navigation (1) Skip section navigation (2)

Updated propsoal for read-only queries on PITR slaves (SoC 2007)

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Updated propsoal for read-only queries on PITR slaves (SoC 2007)
Date: 2007-03-01 14:45:48
Message-ID: 45E6E71C.2070705@phlo.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi

I've updated (or rather rewritten) my proposal for implementing
read-onyl queries on PITR slaves as a "Summer of Code 2007" project.

I've added are more details description of how I plan implement
a read-only mode suitable for PITR slaves, and put in a few
possible enhancements to the "Big, Global R/W lock" idea for
serializing WAL replay and queries.

I'm looking forward to any kind of suggestions, ideas, or
critism - I'd like my proposal to be as detailed as
possible before I submit it to SoC, so that if
I get a chance to work on it, I can be reasonable sure
that people here are happy with the way I approach the problem.

greetings, Florian Pflug

Implementing support for read-only queries on PITR slaves
=========================================================

Submitter: Florian Pflug <fgp(at)phlo(dot)org>

Abstract:
---------
The support for PITR (Point-In-Time-Recovery) in postgres can be used to built
a simple form a master-slave replication. Currently, no queries can be
executed on the slave, though - it only replays WAL (Write-Ahead-Log) segments
it receives from the master. I want to implement support for running read-onyl
queries on such a PITR slave, making PITR usefull not only for disaster
recovery, but also for loadbalancing.

Course overview of the proposed implementation:
-----------------------------------------------
Currently, postgres does WAL replay soley during the startup of the database,
before all subsystems are fully initialized, and before backend are allowed to
connect. To support read-only queries on PITR slaves, while still guaranteeing
that the database is in a consistens state, the WAL replay will be split into
two parts. The first will replay only enough wal to guarantee a consistens
state, and will run during startup. If read-only mode is disabled, the next
step will be run immediatly after the first. If, however, read-only mode is
enabled, then the database will be brought online in read-only mode after
completing recovery, and the second step will be lauched as a seperate
process. Clients are allowed to connect, and to execute read-only queries as
soon as the database is online, even though WAL replay is still being done in
the background.

Implementation of a read-only mode suitable for PITR slaves
-----------------------------------------------------------
Since replication via PITR runs asynchrously, and runs one-way (master-slave),
queries running on the slave are of course not allowed to insert, update or
delete data, nor to change the schema in any way. But there are still write
operations in the datadir that they _are_ allowed to do.
Those are
   .) Creating temporary files for on-disk sorting and spilling out
      tuplestores
   .) Setting XMIN_COMMITTED and XMAX_COMITTED on heap tuples
   .) Setting LP_DELETE on index tuples.
Note that creating temporary tables is not allowed. This is necessary
because temporary tables have associated entries in pg_class,
which obviously can't be created on PITR slaves.

Postgres already supports "set transaction read only" during normal operation.
On a read-only PITR slave every transaction will automatically be flagged
read-only, which results in nice error messages (like "ERROR: transaction is
read-only") if a user tries to execute insert/updates/deletes or
schema-changing operations. Also any command that has to be execute outside of
a transaction block (VACUUM) is disallowed on PITR slaved. As an additional
protection, a global variable read_only_mode is introduced. If in read-only
mode, this is set to true for all backends except the WAL replay process, and
the following checks are added.
   .) MarkBufferDirty() is changed to throw an error if read_only_mode == 1
      Hint bit updates already use SetBufferCommitInfoNeedsSave() instead of
      MarkBufferDirty(), which just suits use fine.
   .) XLogInsert() and XLogWrite() throws if read_only_mode == 1
   .) SlruPhysicalWritePage() and SimpleLruWritePage() throws if read_only_mode == 1
      This prevents creating or changing multixacts, subtrans and clog entries.
   .) EndPrepare() and FinishPreparedTransaction() throws if read_only_mode == 1
      This prevents preparing transaction, and committing/rolling-back prepared
      transactions.
Those checks serve as a safety measure against holes in the already existing
read-only transaction logic. Note that read-onyl transactions won't generate
clog updates, because those are already skipped for transactions that neither
wrote xlog entries, temporary tables nor deleted files.

The following holes currently exist in the read-only transaction logic. Fixing
those is not critical - the lowlevel checks outlined above catch them all -
but would allow displaying better error messages.
   .) nextval(), setval()
   .) CLUSTER
   .) NOTIFY
Disallowing those in all read-only transactions (Not only on PITR slaves) seems
sensible, but it might create compatibility problems.

Allowing read-only queries and WAL archive replay to run side-by-side
---------------------------------------------------------------------
Of all the interlocks postgres uses to ensure that data is not removed
from under a transaction's feet, three are relevant for PITR slaves.

*) Locks on relations. A select takes an AccessShare lock on every referenced
    relation, thereby locking out concurrent DROP,CLUSTER,.. commands. This
    is ineffective on PITR slaves, because there is no trace in the WAL that
    a lock has been granted.

*) VACUUM and GetOldestXmin(). VACUUM makes sure not to remove tuples still
    needed by some transactions by comparing their xmin and xmax to
    GetOldestXmin(). But the value returned by GetOldestXmin() on the *server*
    has no chance to take queries on the *slave* into account which may
    eventually run when the WAL records generated by VACUUM are replayed.

*) The xmin, xmas and list of currently running xid's in SnapshotData ensure
    that a single statement or a whole transaction see a constant view of the data,
    even if other transactions commit while the exection of the statement or
    transaction is still in progress. Creating such a snapshot on the slave
    is tricky, because the wal contains no information about the transactions
    that were running at a specific point. If a transactions runs for a long time
    without doing updates or deleted, it's xid will not show up in the wal
    during that time.

A read-only slave would still replay a part of the wal during startup (before
queries are executed) - but only enough wal to guarantee that the data is
consistent. The condition for consistency is exactly the same as the one
currently used to decide whether there was enough wal to make a
filesystem-level backup consistent or not.

There a three ways to overcome the problems stated above, presented here in
the order of increasing complexity

1) Don't run WAL replay and queries concurrently - stop WAL replaying before
    starting a transaction.
    This allows the transaction to just use a "empty" snapshot, meaning that
    just the information from the clog is used to determine visiblity. A global
    lock would be acquired in read mode by the WAL replaying process before
    replaying a chunk of WAL records. The same lock would be acquired in read
    mode by a backend before starting a transaction. Since there is no need for
    a real snapshot, and since a read-only transaction's xid never hits the
    disk, read-only transactions could just use a constant dummy xid. To be on
    the safe side, the chunks after which the WAL replaying process releases
    and reacquires the lock would be choosen such that at the end of each chuck
    all *_safe_restartpoint functions return true.

2) Only serialize WAL replay and queries if data is actually removed.
    This is a refined version of (1) where the global lock is only acquired
    before actually removing data. Inserting tuples into the heap or index
    should be safe. (Note: The HOT patch might make this more difficult, but
    that will be judged when there is consus on that patch). The exception to
    this rule are system catalogs, since those are accessed using SnapshotNow -
    but since system catalogs have fixed oids, it seems possible to check for
    that during wal replay.

3) Log information about granted locks and currently running transactions
    into the WAL.
    Upon grating a lock on a relation that would conflict with AccessShare, a
    xlog record is written containing the oid of the relation. The checkpoint
    record is extended to contain a list of currently running transactions on
    the master at the time of the checkpoint. This allows the slave to immitate
    the locking that was going on on the master, and also to maintain a list of
    "concurrent" transactions (In the sense that they were current on the
    master when the WAL records being replayed were written).

    A backend on the slave can then use this list of transactions to construct
    a snapshot, and it is guaranteed that the WAL replay pauses if the changes
    it is about to do would conflict with a read-only query.

    Since replaying the locking will open up the possibilites of deadlocks on
    the slave, it will be necessary to guarantee that it's never the WAL
    replayer that is aborted, but rather one of the other backends.

User-Interface
--------------
A new GUC "recovery_allow_readonly" will be introduced. If set to false, postgres
will behave exactly as it does now. If set to true, postgres will allow read-only
queries while replaying WAL records.

Another possibility would be to move this setting into the recovery.conf. The 
problems
with this approach is that the recovery.conf file is deleted after the information
it contains is incorporated into pg_control. Thus, the readonly setting would 
need to
be stored in pg_control too, making it impossible for the user to change it later
(e.g, after interrupting and restarting WAL replay which is possible with 8.2)

Steps taken during the implementation
-------------------------------------
I will start working on the read-only query support - although I'll only
handle the necessary grade of "read-onlyness" needed for PITR slaves, not for
postgres running on a read-only datadir. Then I'll implement solution (1) of
"Allowing read-only queries and WAL archive replay to run side-by-side", even
though this solution will show limited performance. If this is done, I'll get
my patch into a state where it is considered acceptable for inclusion into the
core. After those goals are archived, I'll try to improve the performance by
relaxing the locking requirements according to either (2), (3) or something
completly different, depending on input from the community.

Costs, Benefits, Open Issues
----------------------------
Costs:
*) Point (3) of "Allowing read-only queries and WAL archive replay to run
    side-by-side" would slightly enlare the WAL. One would need to measure
    the impact, but since a query that does locking will probably also change
    data, it can be assumed that the increase in WAL traffic will hardly be
    noticeable.
*) The changes necessary to support read-only queries touch quite a few functions.
    But only a simpel "if readonly then throw error" has to be added. This could
    even be wrappen inside a macro or function.
*) The WAL replaying could will have to be reorganized - but changes to this part
    are unavoidable when implementing this feature.

Benefits:
*) Can be used for master-slave replication. The master database doesn't need
    to be modified in any way (apart from defining an archive_command).
    This makes this kind of master-slave replication easier to setup and
    maintan then trigger-based solutions.
*) Automatically replicates every type of database object, with any special
    code needed per object. This is another advantage over trigger-based
    solutions.
*) Can be used to run long-running queries (like reporting, or pg_dump)
    without preventing the vacuuming of other tables on the master.

Limitations:
*) Point (1) of "Allowing read-only queries and WAL archive replay to run
    side-by-side" severly query load you may put on the slave before
    it starts falling further and further behind the master. Point (2) and
    (3) are meant to address this, but it isn't yet clear how to implement
    those.
*) Postgres wouldn't automatically switch into read-write mode when the
    replaying process finishes. Thus, failing over to the slave requires
    a postgres restart.

Open Questions/Problems
*) How should the flat files be dealt with? Currently, they are updated
    after wal replay finishes, which is not acceptable on the slave.
    Will have to find out if the wal already contains enough information
    to be more clever, or if this information can be added easily. If
    both fail, they could be recreated periodically (say, at every
    RestartPoint).


Responses

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2007-03-01 14:54:44
Subject: Re: Revitalising VACUUM FULL for 8.3
Previous:From: Simon RiggsDate: 2007-03-01 14:20:13
Subject: Re: [PATCHES] WIP Patch - Updateable Cursors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group