From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
Cc: | Jeff Trout <threshar(at)threshar(dot)is-a-geek(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Slow PITR restore |
Date: | 2007-12-13 01:32:19 |
Message-ID: | 766.1197509539@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Jeff Trout <threshar(at)threshar(dot)is-a-geek(dot)com> wrote:
>> 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).
> Exactly. Which is the point I am making. Five minutes of transactions
> is nothing (speaking generally).. In short, if we are in recovery, and
> we are not saturated the I/O and at least a single CPU, there is a huge
> amount of optimization *somewhere* to be done.
You sure about that? I tested CVS HEAD just now, by setting the
checkpoint_ parameters really high, running pgbench for awhile, and
then killing the bgwriter to force a recovery cycle over all the WAL
generated by the pgbench run. What I saw was that the machine was 100%
disk write bound. Increasing shared_buffers helped, not in that the
write rate got less according to vmstat, but the completion time did.
shared_buffers 32MB 100MB
pgbench -c 5 -t 40000 bench 7m23s 2m20s
subsequent recovery 4m26s 2m21s
Typical "vmstat 1" lines during recovery:
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 9 70024 29232 19876 824368 0 0 0 3152 1447 233 0 1 0 99 0
0 9 70024 29232 19876 824368 0 0 0 3660 1474 252 0 1 0 99 0
0 8 70024 28960 19876 824404 0 0 0 3176 1448 265 0 2 1 97 0
I don't see the machine sitting around doing nothing ...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2007-12-13 02:08:31 | Re: what is the date format in binary query results |
Previous Message | marco santillan | 2007-12-13 00:29:05 | query |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2007-12-13 01:36:31 | Re: [HACKERS] "distributed checkpoint" |
Previous Message | Bruce Momjian | 2007-12-13 00:34:05 | Re: BUG #3774: create table like including index doesn't update pg_constraints with primary key |