Re: [COMMITTERS] pgsql: Make the visibility map crash-safe.

On Tue, Jun 28, 2011 at 12:29 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Jun 28, 2011 at 11:44 AM, Cédric Villemain
> <cedric(dot)villemain(dot)debian(at)gmail(dot)com> wrote:
>> out of curiosity, does it affect the previous benchmarks of the feature ?
>
> I don't think there's much performance impact, because the only case
> where we actually have to do any real work is when vacuum comes
> through and marks a buffer we're about to update all-visible.  It
> would probably be good to run some tests to verify that, though.  I'll
> try to set something up.

I did some pgbench runs on the 32-core box Nate Boley gave me access
to. I'm not sure that pgbench is the best workload to test this with,
but I gave it a shot. I used these settings:

checkpoint_segments=64
shared_buffers=8GB
synchronous_commit=off
checkpoint_completion_target=0.9

I compare the performance of commit
431ab0e82819b31fcd1e33ecb52c2cd3b4b41da7 (post-patch) with commit
431ab0e82819b31fcd1e33ecb52c2cd3b4b41da7 (pre-patch). I tried 3
15-minute pgbench runs each with one client, eight clients, and
thirty-two clients, on each branch. I used scale factor 100,
reinitializing the entire cluster after each run.

one client (prepatch)
tps = 616.412009 (including connections establishing)
tps = 619.189040 (including connections establishing)
tps = 605.357710 (including connections establishing)

one client (postpatch)
tps = 583.295139 (including connections establishing)
tps = 609.236975 (including connections establishing)
tps = 597.674354 (including connections establishing)

eight clients (prepatch)
tps = 2635.459096 (including connections establishing)
tps = 2468.973962 (including connections establishing)
tps = 2592.889454 (including connections establishing)

eight clients (postpatch)
tps = 2602.226439 (including connections establishing)
tps = 2644.201228 (including connections establishing)
tps = 2725.760364 (including connections establishing)

thirty-two clients (prepatch)
tps = 3889.761627 (including connections establishing)
tps = 4365.501093 (including connections establishing)
tps = 3816.119328 (including connections establishing)

thirty-two clients (postpatch)
tps = 3888.620044 (including connections establishing)
tps = 3614.252463 (including connections establishing)
tps = 4090.430296 (including connections establishing)

I think it's pretty difficult to draw any firm conclusions from this
data. The one and thirty-two core results came out a bit slower; the
eight core results came out a bit faster. But the variation between
branches is less than the inter-run variation on the same branch, so
if there is an effect, this test doesn't clearly capture it.

Having thought about it a bit, I think that if we ran this test for
long enough, we probably could measure a small effect. pgbench is a
very write-intensive test, so anything that writes additional WAL
records is going cause checkpoints to happen more frequently, and
there's no denying that this change increases WAL volume slightly.

On another note, these results are also interesting from a scalability
perspective. The eight-core results are about 4.4x the one-core
results, and the 32-client results are about 6.5x the one-core
results. Needless to say, there's a lot of room for improvement
there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company