Slow PITR restore

"Joshua D. Drake" <jd@commandprompt.com> writes:

... Now I understand that restoring log files can be slow but this is a big
machine.

Yeah, restoring is known to be less than speedy, because essentially
zero optimization work has been done on it.

Heikki has improved matters a bit in 8.3, but I suspect it's still
not great.

regards, tom lane

Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

... Now I understand that restoring log files can be slow but this is a big
machine.

Yeah, restoring is known to be less than speedy, because essentially
zero optimization work has been done on it.

Heikki has improved matters a bit in 8.3, but I suspect it's still
not great.

Wow, o.k. well it is something we (the community) really should look at
for 8.4. I am surprised that it is slower than just walking through the
xlogs on recovery. I am sure there is a reason just surprised.

Thanks for answering.

Sincerely,

Joshua D. Drake

"Joshua D. Drake" <jd@commandprompt.com> writes:

Wow, o.k. well it is something we (the community) really should look at for
8.4. I am surprised that it is slower than just walking through the xlogs on
recovery. I am sure there is a reason just surprised.

Well in the worst case it has to do nearly as much work as the original
database did. And it only gets to use 1 cpu so it can only have one i/o
request pending.

bgwriter is started already when doing recovery, right? Perhaps things could
be helped by telling bgwriter to behave differently during recovery.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

On Wed, 2007-12-12 at 08:55 +0000, Gregory Stark wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

Wow, o.k. well it is something we (the community) really should look at for
8.4. I am surprised that it is slower than just walking through the xlogs on
recovery. I am sure there is a reason just surprised.

It's the same speed because it is the same recovery code.

Well in the worst case it has to do nearly as much work as the original
database did. And it only gets to use 1 cpu so it can only have one i/o
request pending.

That need only be slow in certain circumstances.

bgwriter is started already when doing recovery, right? Perhaps things could
be helped by telling bgwriter to behave differently during recovery.

It would certainly help if bgwriter came up earlier. I've been looking
at that just recently. The main issue is how to structure the code to
tell bgwriter when it can start processing in recovery mode and then
move into normal mode.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Gregory Stark wrote:

bgwriter is started already when doing recovery, right? Perhaps things could
be helped by telling bgwriter to behave differently during recovery.

No.

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Amanece. (Ignacio Reyes)
El Cerro San Crist�bal me mira, c�nicamente, con ojos de virgen"

On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

... Now I understand that restoring log files can be slow but this is a big
machine.

Yeah, restoring is known to be less than speedy, because essentially
zero optimization work has been done on it.

If there was a patch to improve this, would it be applied to 8.3?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Simon Riggs wrote:

On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

... Now I understand that restoring log files can be slow but this is a big
machine.

Yeah, restoring is known to be less than speedy, because essentially
zero optimization work has been done on it.

If there was a patch to improve this, would it be applied to 8.3?

Sheesh Simon you are really pushing this release :). I would love to see
a patch to resolve this, especially since it appears to be a fairly
glaring oversight. We can't really expect people to use PITR if they new
it would take hours to recover even on the size of machine I was working on.

On the other hand... we are about to go to RC1 :)

Joshua D. Drake

On Wed, 2007-12-12 at 07:36 -0800, Joshua D. Drake wrote:

We can't really expect people to use PITR if they new
it would take hours to recover even on the size of machine I was working on.

That's not true statement in all cases and can often be improved with
some monitoring and tuning. Just get your client to call me :-)

Are you doing replication, or a PITR for another reason?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Simon Riggs wrote:

On Wed, 2007-12-12 at 07:36 -0800, Joshua D. Drake wrote:

We can't really expect people to use PITR if they new
it would take hours to recover even on the size of machine I was working on.

That's not true statement in all cases and can often be improved with
some monitoring and tuning. Just get your client to call me :-)

Uhh.. right.

Are you doing replication, or a PITR for another reason?

Warm standby. Normally we pull every 5 minutes which is why we hadn't
noticed this before. However last night we pulled a full sync and
recover and that is when we noticed it.

8 seconds for a single archive recovery is very slow in consideration of
this machine. Even single threaded that seems slow.

Sincerely,

Joshua D. Drake

Simon Riggs <simon@2ndquadrant.com> writes:

On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote:

Yeah, restoring is known to be less than speedy, because essentially
zero optimization work has been done on it.

If there was a patch to improve this, would it be applied to 8.3?

Good grief, no. We have not even done the research to find out where
the bottleneck(s) is/are. We're not holding up 8.3 while we go back
into development mode, especially not when this problem has existed
for seven or eight years (even if JD failed to notice before) and
there are already some improvements for it in 8.3.

regards, tom lane

Tom Lane wrote:

Simon Riggs <simon@2ndquadrant.com> writes:

On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote:

Yeah, restoring is known to be less than speedy, because essentially
zero optimization work has been done on it.

If there was a patch to improve this, would it be applied to 8.3?

Good grief, no. We have not even done the research to find out where
the bottleneck(s) is/are. We're not holding up 8.3 while we go back
into development mode, especially not when this problem has existed
for seven or eight years (even if JD failed to notice before) and
there are already some improvements for it in 8.3.

I would also note that this "problem" is only going to be noticeable on
the highest velocity of databases. This is certainly a 10% of the users
issue. It would be great to get it fixed but there are ways around it
(namely making sure you are running pg_standby and pushing logs at
smaller intervals).

Sincerely,

Joshua D. Drake

On Wed, 2007-12-12 at 12:13 -0500, Tom Lane wrote:

Simon Riggs <simon@2ndquadrant.com> writes:

On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote:

Yeah, restoring is known to be less than speedy, because essentially
zero optimization work has been done on it.

If there was a patch to improve this, would it be applied to 8.3?

Good grief, no.

Understood.

Just checking to see if you were fishing for a patch from me, so I'm
relieved actually.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Dec 12, 2007, at 11:29 AM, Joshua D. Drake wrote:

8 seconds for a single archive recovery is very slow in
consideration of this machine. Even single threaded that seems slow.

I've seen this on my PITR restores (thankfully, they were for
fetching some old data, not because we expoded). On a 2.4ghz opteron
it took 5-50 seconds per wal segment, and there were a LOT of
segments (replay took hours and hours). I asked a few folks and was
told it is the nature of the beast. Hopefully something in 8.4 can
be done.

--
Jeff Trout <jeff@jefftrout.com>
http://www.dellsmartexitin.com/
http://www.stuarthamm.net/

Jeff Trout <threshar@threshar.is-a-geek.com> writes:

I've seen this on my PITR restores (thankfully, they were for
fetching some old data, not because we expoded). On a 2.4ghz opteron
it took 5-50 seconds per wal segment, and there were a LOT of
segments (replay took hours and hours). I asked a few folks and was
told it is the nature of the beast. Hopefully something in 8.4 can
be done.

Before we get all panicked about that, someone should try to measure the
restore speed on 8.3. It's possible that this patch already
alleviated the problem:
http://archives.postgresql.org/pgsql-committers/2007-05/msg00041.php

regards, tom lane

"Jeff Trout" <threshar@torgo.978.org> writes:

On Dec 12, 2007, at 11:29 AM, Joshua D. Drake wrote:

8 seconds for a single archive recovery is very slow in consideration of
this machine. Even single threaded that seems slow.

I've seen this on my PITR restores (thankfully, they were for fetching some
old data, not because we expoded). On a 2.4ghz opteron it took 5-50 seconds
per wal segment,

I'm not sure what you guys' expectations are, but if you're restoring 5
minutes worth of database traffic in 8 seconds I wouldn't be complaining.

Depending on your transaction mix and what percentage of it is read-only
select queries you might reasonably expect the restore to take as long as it
took to generate them...

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 12 Dec 2007 18:02:39 +0000
Gregory Stark <stark@enterprisedb.com> wrote:

I'm not sure what you guys' expectations are, but if you're restoring
5 minutes worth of database traffic in 8 seconds I wouldn't be
complaining.

I would be. This is a database that is doing nothing but restoring.
Zero concurrency. This thing should be flying.

Depending on your transaction mix and what percentage of it is
read-only select queries you might reasonably expect the restore to
take as long as it took to generate t

We archive selects?

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHYCOIATb/zqfZUUQRAlbBAJ0aNmWy8xFKtmaONUmL9LegTfaaywCfSoS3
TRmDPUEhoTJJVSO8REM6YYI=
=bv/t
-----END PGP SIGNATURE-----

On Dec 12, 2007, at 1:02 PM, Gregory Stark wrote:

I'm not sure what you guys' expectations are, but if you're
restoring 5
minutes worth of database traffic in 8 seconds I wouldn't be
complaining.

Depending on your transaction mix and what percentage of it is read-
only
select queries you might reasonably expect the restore to take as
long as it
took to generate them...

in this case it was 24hrs of data - about 1500 wal segments. During
this time the machine was nearly complete idle and there wasn't very
much IO going on (few megs/sec).

I'll have to artifically build up some db traffic on an 8.3 instance
to see if the patch Tom mentioned helps.

--
Jeff Trout <jeff@jefftrout.com>
http://www.dellsmartexitin.com/
http://www.stuarthamm.net/

On Wed, 2007-12-12 at 10:08 -0800, Joshua D. Drake wrote:

Depending on your transaction mix and what percentage of it is
read-only select queries you might reasonably expect the restore to
take as long as it took to generate t

We archive selects?

No, but that is exactly his point.

Whatever proportion of the workload is selects will *not* have to be
reproduced on the client.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Wed, 2007-12-12 at 12:56 -0500, Tom Lane wrote:

Jeff Trout <threshar@threshar.is-a-geek.com> writes:

I've seen this on my PITR restores (thankfully, they were for
fetching some old data, not because we expoded). On a 2.4ghz opteron
it took 5-50 seconds per wal segment, and there were a LOT of
segments (replay took hours and hours). I asked a few folks and was
told it is the nature of the beast. Hopefully something in 8.4 can
be done.

Before we get all panicked about that, someone should try to measure the
restore speed on 8.3. It's possible that this patch already
alleviated the problem:
http://archives.postgresql.org/pgsql-committers/2007-05/msg00041.php

Twas this feature, amongst others, that I had asked to be put back onto
the release notes, so people knew it had improved. I don't really want
to replicate that discussion, but if we're talking about it here then it
probably should be in the notes.

It roughly doubles performance, but there's still more required in some
cases. My recommendation is always measure the performance first.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com