On Thu, May 30, 2013 at 12:06 AM, Jeff Davis <pgsql@j-davis.com> wrote:
>> AFAICS, the main benefit of eliminating PD_ALL_VISIBLE is that we
>> eliminate one write cycle; that is, we won't dirty the page once to
>> hint it and then again to mark it all-visible. But as of 9.3, that
>> should really only be a problem in the insert-only case. And in that
>> case, my proposal to consider all-visible pages as frozen would be a
>> huge win, because you'd only need to emit XLOG_HEAP_VISIBLE for every
>> page in the heap, rather than XLOG_HEAP_FREEZE.
>
> Agreed.
Just to quantify that a bit more, I ran this command a couple of times:
dropdb rhaas ; sleep 5 ; createdb ; sleep 5 ; pgbench -i -s 1000 -n;
sleep 5 ; time psql -c checkpoint ; time psql -c 'vacuum'
And also this one:
dropdb rhaas ; sleep 5 ; createdb ; sleep 5 ; pgbench -i -s 1000 -n;
sleep 5 ; time psql -c checkpoint ; time psql -c 'vacuum freeze'
In the first one, the vacuum at the end takes about 25 seconds. In
the second one, it takes about 15 minutes, during which time there's
one CPU core running at about 10%; the remainder of the time is spent
waiting for disk I/O. A little follow-up testing shows that the
vacuum emits 88MB of WAL, while the vacuum freeze emits 13GB of WAL.
This is on the 16-core, 64-thread IBM POWER box with the following
non-default configuration settings:
shared_buffers = 8GB
maintenance_work_mem = 1GB
synchronous_commit = off
checkpoint_segments = 300
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
log_line_prefix = '%t [%p] '
Andres' proposal for freezing at the same time we mark pages
all-visible relies on emitting FPIs when we mark pages all-visible,
but I hope that the test above is convincing evidence that it would be
*really* expensive for some users. My proposal to consider
all-visible pages as frozen avoids that cost, but as far as I can see,
it also requires PD_ALL_VISIBLE to stick around.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company