Re: why do we need two snapshots per query? - Mailing list pgsql-hackers

On Sun, Nov 13, 2011 at 9:40 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sun, Nov 13, 2011 at 8:57 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> In the -M extended case, we take a snapshot from exec_parse_message(),
>> and the same two in the exec_bind_message() call that are taken in the
>> -M prepared case.  So reducing the prepared case from two snapshots to
>> one will reduce the extended case from three snapshots to two, thus
>> saving one snapshot per query regardless of how it's executed.
>
> And here are the revised patches.  Apply refactor-portal-start
> (unchanged) first and then just-one-snapshot-v2.

Some pgbench -S numbers (SELECT-only test) from Nate Boley's 32-core
box.   I benchmarked commit f1585362856d4da17113ba2e4ba46cf83cba0cf2,
patched and unpatched.  I set shared_buffers = 8GB,
maintenance_work_mem = 1GB, synchronous_commit = off,
checkpoint_segments = 300, checkpoint_timeout = 15min,
checkpoint_completion_target = 0.9, wal_writer_delay = 20ms.  All
numbers are median of five-minute runs.  Lines beginning with "m" are
unpatched master; lines beginning with "s" are patched; the number
immediately following is the client count.

== with -M simple ==
m01 tps = 4347.393421 (including connections establishing)
s01 tps = 4336.883587 (including connections establishing)
m08 tps = 33510.055702 (including connections establishing)
s08 tps = 33826.161862 (including connections establishing)
m32 tps = 203457.891154 (including connections establishing)
s32 tps = 218206.065239 (including connections establishing)
m80 tps = 200494.623552 (including connections establishing)
s80 tps = 219344.961016 (including connections establishing)

== with -M extended ==
m01 tps = 3567.409671 (including connections establishing)
s01 tps = 3678.526702 (including connections establishing)
m08 tps = 27754.682736 (including connections establishing)
s08 tps = 28474.566418 (including connections establishing)
m32 tps = 177439.118199 (including connections establishing)
s32 tps = 187307.500501 (including connections establishing)
m80 tps = 173765.388249 (including connections establishing)
s80 tps = 184047.873286 (including connections establishing)

== with -M prepared ==
m01 tps = 7310.682085 (including connections establishing)
s01 tps = 7229.791967 (including connections establishing)
m08 tps = 54397.250840 (including connections establishing)
s08 tps = 55045.651468 (including connections establishing)
m32 tps = 303142.385619 (including connections establishing)
s32 tps = 313493.928436 (including connections establishing)
m80 tps = 304652.195974 (including connections establishing)
s80 tps = 311330.343510 (including connections establishing)

Of course, the fact that this gives good benchmark numbers doesn't
make it correct.  But the fact that it gives good benchmark numbers
seems - to me anyway - like a good reason to think carefully before
rejecting this approach out of hand.

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