Re: sinval synchronization considered harmful - Mailing list pgsql-hackers

On Wed, Jul 27, 2011 at 12:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Jul 26, 2011 at 9:57 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> 1. Add a new flag to each procState called something like "timeToPayAttention".
>>> 2. Each call to SIGetDataEntries() iterates over all the ProcStates
>>> whose index is < lastBackend and sets stateP->timeToPayAttention =
>>> TRUE for each.
>>> 3. At the beginning of SIGetDataEntries(), we do an unlocked if
>>> (!stateP->timeToPayAttention) return 0.
>>> 4. Immediately following that if statement and before acquiring any
>>> locks, we set stateP->timeToPayAttention = FALSE.
>
>> There turned out to be a little bit of further subtlety to this, but
>> it seems to work.  Patch attached.
>
> And?
>
> It didn't sound to me like this could possibly be a performance win,
> but I await some numbers ...

On master, with the patch, scale factor 100, pgbench -S -c $CLIENTS -j
$CLIENTS -T 300 results for different client counts, on a 32-core
machine with 128GB RAM, shared_buffers = 8GB:

01 tps = 4444.280459 (including connections establishing)
01 tps = 4438.365576 (including connections establishing)
01 tps = 4423.718466 (including connections establishing)
08 tps = 33187.827872 (including connections establishing)
08 tps = 33288.247330 (including connections establishing)
08 tps = 33304.307835 (including connections establishing)
32 tps = 178876.350559 (including connections establishing)
32 tps = 177293.082295 (including connections establishing)
32 tps = 175577.058885 (including connections establishing)
80 tps = 159202.449993 (including connections establishing)
80 tps = 151541.717088 (including connections establishing)
80 tps = 150454.658132 (including connections establishing)

Without the patch:

01 tps = 4447.660101 (including connections establishing)
01 tps = 4440.830713 (including connections establishing)
01 tps = 4411.610348 (including connections establishing)
08 tps = 33135.773476 (including connections establishing)
08 tps = 33365.387051 (including connections establishing)
08 tps = 33364.859705 (including connections establishing)
32 tps = 175834.280471 (including connections establishing)
32 tps = 176713.118271 (including connections establishing)
32 tps = 176830.687087 (including connections establishing)
80 tps = 135211.036587 (including connections establishing)
80 tps = 130642.264016 (including connections establishing)
80 tps = 133621.549513 (including connections establishing)

I'm fairly certain the results will be even more dramatic with the
lazy-vxid patch applied, since master still has to fight with the lock
manager on this workload.  I haven't tested that yet, but there's not
much reason to suppose that the results will be dramatically different
from any of the other methods of reducing the sinval overhead that
I've benchmarked, many of which I've already posted about.  See, for
example, the OP on this thread.

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