SEO

Hi

I plan to submit a proposal for implementing support for
read-only queries during wal replay as a "Google Summer of Code 2007"
project.

I've been browsing the postgres source-code for the last few days,
and came up with the following plan for a implementation.

I'd be very interested in any feedback on the propsoal - especially
of the "you overlooked this an that, it can never work that way" kind ;-)

greetings, Florian Pflug

Implementing read-only quries during wal archive replay
-------------------------------------------------------

Submitter: Florian Pflug <fgp@phlo.org>

Abstract:
Implementing full support for read-only queries during
wal archive replay is splitted into multiple parts, where
each part offeres additional functionality over what
postgres provides now. This makes tackling this as a
"Google Summer of Code 2007" project feasable, and guarantees
that at least some progress is made, even if solving the
whole problem turns out to be harder then previously
thought.

Parts/Milestones of the implementation:
A) Allow postgres to be started in read-only mode. After
initial wal recovery, postgres doesn't perform writes
anymore. All transactions started are implicitly in
readonly mode. All transactions will be assigned dummy
transaction ids, which never make it into the clog.
B) Split StartupXLOG into two steps. The first (Recovery) will process
only enough wal to bring the system into a consistent state,
while the second one (Replay) replays the archive until it finds no
more wal segments. This replay happens in chunks, such that
after a chunk all *_safe_restartpoint functions return true.
C) Combine A) and B), in the simplest possible way.
Introduce a global R/W lock, which is taken by the Replay part
of B) in write mode before replaying a chunk, then released,
and immediatly reaquired before replaying the next chunk.
The startup sequence is modified to do only the Recovery part
where is is doing StartupXLOG now, and to lauch an extra process
(similar to bgwriter) to do the second (Replay) part in the background.
The system is then started up in read-only mode, with the addition
that the global R/W lock is taken in read mode before starting any
transaction. Thus, while a transaction is running, no archive replay
happens.

Benefits:
*) Part A) alone might be of value for some people in the embedded world,
or people who want to distribute software the use postgres. You could
e.g. distribute a CD with a large, read-only database, and your application
would just need to start postmaster to be able to query it directly from
the CD.
*) Read-only hot standby is a rather simple way to do load-balancing, if
your application doesn't depend on the data being absolutely up-to-date.
*) Even if this isn't used for load-balancing, it gives the DBA an
easy way to check how far a PITR slave is lagging behind, therefore
making PITR replication more user-friendly.

Open Questions/Problems
*) How do read-only transactions obtain a snapshot? Is it sufficient
to just create an "empty" snapshot for them, meaning that they'll
always look at the clog to obtain a transaction's state?
*) How many places to attempt to issue writes? How hard is it to
silence them all while in read-only mode.
*) How does the user interface look like? I'm currently leaning towards
a postgresql.conf setting read_only=yes. This would put postgres
into read-only mode, and if a recovery.conf is present, archive
replay would run as a background process.

Limitations:
*) The replaying process might be starved, letting the slave fall
further and further behind the master. Only true if the slave
executes a lot of queries, though.
*) Postgres would continue to run in read-only mode, even after finishing
archive recovery. A restart would be needed to switch it into read-write
mode again. (I probably wouldn't be too hard to do that switch without
a restart, but it seems better to tackle this after the basic features
are working)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Hi,
Here's some feedback, this is a feature that would be very useful to a
project I am currently working on.

Doug

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

"Florian G. Pflug" <fgp@phlo.org> writes:
> I plan to submit a proposal for implementing support for
> read-only queries during wal replay as a "Google Summer of Code 2007"
> project.

You are discussing this on the wrong list.

> B) Split StartupXLOG into two steps. The first (Recovery) will process
> only enough wal to bring the system into a consistent state,

How will you know what that is?

> C) Combine A) and B), in the simplest possible way.
> Introduce a global R/W lock, which is taken by the Replay part
> of B) in write mode before replaying a chunk, then released,
> and immediatly reaquired before replaying the next chunk.

That seems certain to result in intolerable performance, for both the
queries and the replay process.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane wrote:
> "Florian G. Pflug" <fgp@phlo.org> writes:
>> I plan to submit a proposal for implementing support for
>> read-only queries during wal replay as a "Google Summer of Code 2007"
>> project.
>
> You are discussing this on the wrong list.
So what list would be more appropriate?

>> B) Split StartupXLOG into two steps. The first (Recovery) will process
>> only enough wal to bring the system into a consistent state,
>
> How will you know what that is?
With the same logic that postgres uses now to bring an file-system backup
into a consistent state when doing PITR.

>> C) Combine A) and B), in the simplest possible way.
>> Introduce a global R/W lock, which is taken by the Replay part
>> of B) in write mode before replaying a chunk, then released,
>> and immediatly reaquired before replaying the next chunk.
>
> That seems certain to result in intolerable performance, for both the
> queries and the replay process.

That depends entirely on the usecase. And besides, this limitation could
and probably would be adressed in the future. I think a step-by-step
approach is more likely to be successfull then attempting to solve
all problems at once.

greetings, Florian Pflug




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Florian G. Pflug" <fgp@phlo.org> writes:
> Tom Lane wrote:
>> You are discussing this on the wrong list.

> So what list would be more appropriate?

My mistake, I read the message header and saw "Postgresql-General" ...
did not look at the actual address ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

I'll throw in my vote, I would find this quite useful.

-Glen

> Florian G. Pflug wrote:
>> I plan to submit a proposal for implementing support for
>> read-only queries during wal replay as a "Google Summer of Code 2007"
>> project.
>>
>> I've been browsing the postgres source-code for the last few days,
>> and came up with the following plan for a implementation.
>>
>> I'd be very interested in any feedback on the propsoal - especially
>> of the "you overlooked this an that, it can never work that way" kind ;-)



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> People weren't very interested in having a read-only mode. I think it
> would be a nice feature if it's not too complicated.

Actually, I think there's high demand for it off this list. Effectively it
would allow our "warm backup mode" to become a "hot backup mode". As SoC
admin, I'd vote for such a proposal unless someone explains to me why it's
impossible.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Josh Berkus wrote:
>> People weren't very interested in having a read-only mode. I think it
>> would be a nice feature if it's not too complicated.
>
> Actually, I think there's high demand for it off this list. Effectively it
> would allow our "warm backup mode" to become a "hot backup mode". As SoC
> admin, I'd vote for such a proposal unless someone explains to me why it's
> impossible.

One thing I would like noted, is whoever does SoC work for PostgreSQL
this year, needs to work *with* the community. Otherwise there is no point.

A good example of the wrong way to do it is the Full Disjunctions
project. Great idea, Great project, not bitrot and hard space because it
hasn't been touched or maintained sense release.

In order to get it into core, it would have needed a lot of work. Let's
make sure we don't duplicate the issue.

Joshua D. Drake




--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, Feb 23, 2007 at 10:57:24PM +0000, Heikki Linnakangas wrote:
> Florian G. Pflug wrote:
> >I plan to submit a proposal for implementing support for
> >read-only queries during wal replay as a "Google Summer of Code 2007"
> >project.
> >
> >I've been browsing the postgres source-code for the last few days,
> >and came up with the following plan for a implementation.
> >
> >I'd be very interested in any feedback on the propsoal - especially
> >of the "you overlooked this an that, it can never work that way" kind ;-)
>
> I had the same thought roughly two years ago:
>
> http://archives.postgresql.org/pgsql...1/msg01043.php
>
> People weren't very interested in having a read-only mode. I think it
> would be a nice feature if it's not too complicated.

Every customer I've ever talked to about HA has either asked about it or
thought it was a great idea. We should definitely do it if it's not a
load of difficult..
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Heikki Linnakangas wrote:
> Florian G. Pflug wrote:
>> I plan to submit a proposal for implementing support for
>> read-only queries during wal replay as a "Google Summer of Code 2007"
>> project.
>>
>> I've been browsing the postgres source-code for the last few days,
>> and came up with the following plan for a implementation.
>>
>> I'd be very interested in any feedback on the propsoal - especially
>> of the "you overlooked this an that, it can never work that way" kind ;-)
>
> I had the same thought roughly two years ago:
>
> http://archives.postgresql.org/pgsql...1/msg01043.php
>
> People weren't very interested in having a read-only mode. I think it
> would be a nice feature if it's not too complicated.

I think "main" feature would be supporting read-only queries on PITR
slaves. But creating a read-only mode seemed to me (and to you too, it
seems ;-) ) like a good first step towards that goal.

After reading tom's reply to your original proposal, I agree that
supporting a write-protected datadir is not a true subset of
supporting read-only queries on PITR slaves. But I still think
that tackling the read-only datadir support is a good first step -
not the least because it'll help me to get familiar with the
relevent parts of the backend.

I've been thinking about your "trick" of writing "readonly" into
the postmaster.pid file to switch postgres into read-only mode.
On the one hand, it's really neat - if solves the problem of not
being able to create a pid file in the datadir in ro mode, while
on the other hand making sure that there *is* a pid file. But
if I went that way, it would mean there would be *three* configfiles
you have to get right for a working PITR slave with read-only query
support - postgresql.conf, recovery.conf, and postmaster.pid.

So I think I'll rather go with a postgresql.conf setting. I'd
allow three values "hard", "soft" and "off".
"hard" would prevent all writes to the datadir, while
"soft" would be the setting of choice for a PITR slave.

In the "soft" case, postgres would still write a postmaster.pid,
and so be protected against other running postmasters.
In the "hard" case, there would be no such protection - but since
there would be no writes anyway, you don't risk data corruption
in case another postmaster was running - the worst the would happen
is that the read-only postmaster crashes.

greetings, Florian Pflug


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly