Thread: New 8.4 hot standby feature
I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased
with the experience. This is promising stuff. Perhaps it is a bit too soon to
ask questions here but here it is:
1. Speed of recovery
With a archive_timeout of 60 seconds, it can take about 4 minutes before I see
the reflected changes in the replica. This is normal since, in addition to
the WAL log shipping, it takes more time to do the recovery itself. Still, is
there any way besides the archive_timeout config option to speed up the
recovery of WAL logs on the hot standby?
2. last modified since timestamp:
Is there a way to get the "last modified since" timestamp on the hot standby
replica? Since the replication is asynchronous, it is necessary to know how
up to date the replication is. In our case, the timestamp is used this way:
select * from resource where not_modified_since >=
to_timestamp('$not_modified_since', 'YYYY/MM/DD HH:MI:SS');
The $not_modified_since is set to now() for the next time this query will be
run on the master database. This way the application keeps a cache and it is
not necessary to fetch everything every time. With an asynchronous replica
however, "now()" cannot be used and so I am looking into other possibilities
to get an accurate "last modified since" on the hot standby itself. I
tried "select pg_last_recovered_xact_timestamp();"
(http://wiki.postgresql.org/wiki/Hot_Standby) but this is not 100% accurate.
It looks like it has more to do with recovery transactions then the original
database itself.
So the question is: is there any clean way to get the last_modified_since
without making modifications to the schema?
On 1/27/09, Gabi Julien <gabi.julien@broadsign.com> wrote: > I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased > with the experience. This is promising stuff. Perhaps it is a bit too soon to > ask questions here but here it is: > > 1. Speed of recovery > > With a archive_timeout of 60 seconds, it can take about 4 minutes before I see > the reflected changes in the replica. This is normal since, in addition to > the WAL log shipping, it takes more time to do the recovery itself. Still, is > there any way besides the archive_timeout config option to speed up the > recovery of WAL logs on the hot standby? you can manually throw pg_switch_xlog(), In practice, this is more of an issue on development boxes than anything if you server is at all busy. see: http://developer.postgresql.org/pgdocs/postgres/functions-admin.html merlin
On Tue, 2009-01-27 at 12:58 -0500, Merlin Moncure wrote:
> you can manually throw pg_switch_xlog(), In practice, this is more of
> an issue on development boxes than anything if you server is at all
> busy.
>
That won't speed up recovery, that will just force the WAL segment to be
archived. It's still up to the standby to find the log and replay it.
Regards,
Jeff Davis
On Tue, 2009-01-27 at 12:53 -0500, Gabi Julien wrote: > I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased > with the experience. This is promising stuff. Perhaps it is a bit too soon to > ask questions here but here it is: > > 1. Speed of recovery > > With a archive_timeout of 60 seconds, it can take about 4 minutes before I see > the reflected changes in the replica. This is normal since, in addition to > the WAL log shipping, it takes more time to do the recovery itself. Still, is > there any way besides the archive_timeout config option to speed up the > recovery of WAL logs on the hot standby? > Is the recovery itself the bottleneck? There's a performance improvement submitted here: http://archives.postgresql.org/message-id/a778a7260810280033n43f70d36x8c437eacf9a5461e@mail.gmail.com But I haven't been following the development of it closely, so you'll have to read the thread to see whether it will meet your needs or not. Regards, Jeff Davis
On Tue, 2009-01-27 at 12:53 -0500, Gabi Julien wrote: > I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased > with the experience. This is promising stuff. Thanks, > Perhaps it is a bit too soon to > ask questions here but here it is: Thanks very much for the bug report. > 1. Speed of recovery > > With a archive_timeout of 60 seconds, it can take about 4 minutes before I see > the reflected changes in the replica. This is normal since, in addition to > the WAL log shipping, it takes more time to do the recovery itself. Still, is > there any way besides the archive_timeout config option to speed up the > recovery of WAL logs on the hot standby? There was a reported bug whose apparent symptoms were delay of WAL files. The bug was not in fact anything to do with that at all, it was just delayed *visibility*. So I doubt very much that you have a performance problem. The bug fix patch is attached, verified to solve the problem. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
Attachment
On Thursday 29 January 2009 02:43:18 you wrote: > On Tue, 2009-01-27 at 12:53 -0500, Gabi Julien wrote: > > I have merged the last hot standby patch (v9g) to 8.4 devel and I am > > pleased with the experience. This is promising stuff. > > Thanks, > > > Perhaps it is a bit too soon to > > ask questions here but here it is: > > Thanks very much for the bug report. > > > 1. Speed of recovery > > > > With a archive_timeout of 60 seconds, it can take about 4 minutes before > > I see the reflected changes in the replica. This is normal since, in > > addition to the WAL log shipping, it takes more time to do the recovery > > itself. Still, is there any way besides the archive_timeout config option > > to speed up the recovery of WAL logs on the hot standby? > > There was a reported bug whose apparent symptoms were delay of WAL > files. The bug was not in fact anything to do with that at all, it was > just delayed *visibility*. So I doubt very much that you have a > performance problem. > > The bug fix patch is attached, verified to solve the problem. Thanks. Please discard all my previous comments. This was the true source of the issue that I was experiencing.
Hi, > > There's a performance improvement submitted here: > http://archives.postgresql.org/message-id/a778a7260810280033n43f70d36x8c437eacf9a5461e@mail.gmail.com > > But I haven't been following the development of it closely, so you'll > have to read the thread to see whether it will meet your needs or not. I've posted the lastest set of the patch to speed-up the recovery without FPW. You'll find this in the thread. I hope anyone of you try this. > > Regards, > Jeff Davis > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- ------ Koichi Suzuki