Re: Storing hot members of PGPROC out of the band

On Tue, Nov 22, 2011 at 12:35 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I'm wondering about the changes to how globalxmin is computed in
> GetSnapshotData().  That seems like it could hurt performance in the
> single-client case, and especially in the case where there is one
> active connection and lots of idle connections, and I'm wondering how
> thoroughly we've tested that particular bit apart from these other
> changes.

I separated this part out - see attached. pgxact-v2.patch is the same
as what I posted on Tuesday with those changes factored out;
recentglobalxmin.patch applies over top of it, and contains just the
changes related to how RecentGlobalXmin is recomputed. I tried a
pgbench -S test, with five minute runs, scale factor 100,
shared_buffers = 8GB, on Nate Boley's 32-core box. I tried this on
unpatched master, with just pgxact-v2.patch, and with both patches.
At 1, 8, and 32, pgxact-v2 is faster than master, but pgxact-v2 +
recentglobalxmin is slower than just pgxact-v2. When I get up to 80
clients, the recentgloblaxmin portion becomes a win.

Just to make sure that I wasn't measuring random noise, I did seven
runs with each configuration rather than my usual three. The table
below shows the lowest, median, and highest tps results for each
tested configuration.

== 1 client ==
master: low 4312.806032 median 4343.971972 high 4395.870212
pgxact-v2: low 4391.273988 median 4408.283875 high 4448.315804
pgxact-v2+recentglobalxmin: low 4328.697157 median 4360.581749 high 4399.763953

== 8 clients ==
master: low 33295.56753 median 33356.682726 high 33639.368929
pgxact-v2: low 33719.004950 median 33927.195872 high 34106.679176
pgxact-v2+recentglobalxmin: low 33565.708794 median 33694.140297 high
33795.072927

== 32 clients ==
master: low 210514.460228 median 213168.532917 high 214169.174032
pgxact-v2: low 216230.082361 median 221678.274469 high 231465.732256
pgxact-v2+recentglobalxmin: low 212202.985015 median 219390.075074
high 223353.421472

== 80 clients ==
master: low 208139.915780 median 209867.352113 high 214868.151835
pgxact-v2: low 217003.716877 median 218360.946541 high 222095.321825
pgxact-v2+recentglobalxmin: low 219390.617005 median 220912.168056
high 221779.348531

I'm going to run some more tests, but my thought is that we should
probably leave the recentglobalxmin changes out for the time being,
pending further study and consideration of other alternatives.

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