Re: Storing hot members of PGPROC out of the band

On Thu, Nov 24, 2011 at 11:54 PM, Pavan Deolasee
<pavan(dot)deolasee(at)gmail(dot)com> wrote:
> +1. These are independent patches and should be pursued like that.
> BTW, I reviewed the pgxact-v2.patch and I have no objections to that
> and it looks good to go in. Thanks Robert for making the necessary
> changes and also running the benchmark tests.

I ran some write tests with master, pgxact-v2, and
pgxact-v2+recentglobalxmin. 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. Seven
five-minute runs at scale factor 100 for each configuration.

Here's the executive summary: On the read-only test,
recentglobalxmin.patch was only a win at the very highest concurrency
I tested (80 clients); on the read-write test, it starts to show a
benefit at much lower concurrencies (32 clients, certainly, perhaps
even 8 clients, on unlogged tables). However, pgxact-v2.patch seems
to be a win on both read and write tests and at any concurrency level,
including the single-client case.

== 1 client, unlogged tables ==
master: low 671.861618 median 677.324867 high 765.824313 (but the
second highest was only 679.491822)
pgxact-v2: low 663.901614 median 689.496716 high 696.812065
pgxact-v2+recentglobalxmin: low 665.554342 median 685.401979 high 688.832906

== 8 clients, unlogged tables ==
master: low 4722.011063 median 4758.201239 high 4920.130891
pgxact-v2: low 4684.759859 median 4840.081663 high 4979.036845
pgxact-v2+recentglobalxmin: low 4723.743270 median 4856.513904 high 4997.528399

== 32 clients, unlogged tables ==
master: low 10878.959662 median 10901.523672 high 10934.699151
pgxact-v2: low 17944.914228 median 18060.058996 high 19281.541088
pgxact-v2+recentglobalxmin: low 18894.860512 median 19637.190567 high
19817.089456

== 80 clients, unlogged tables ==
master: low 7872.934292 median 7897.811216 high 7909.410723
pgxact-v2: low 12032.684380 median 12397.316995 high 13279.998414
pgxact-v2+recentglobalxmin: low 16964.227483 median 17801.478747 high
18107.646170

== 1 client, permanent tables ==
master: low 625.502929 median 628.442284 high 677.451660
pgxact-v2: low 636.755782 median 640.083573 high 645.273888
pgxact-v2+recentglobalxmin: low 633.320412 median 636.898945 high 637.886099

== 8 clients, permanent tables ==
master: low 4497.012143 median 4624.844801 high 4849.233268
pgxact-v2: low 4561.914897 median 4625.443111 high 4776.095552
pgxact-v2+recentglobalxmin: low 4469.742226 median 4789.249847 high 4824.033794

== 32 clients, permanent tables ==
master: low 10468.362239 median 10511.425102 high 10531.069684
pgxact-v2: low 12821.732396 median 14500.067726 high 14546.538281
pgxact-v2+recentglobalxmin: low 14907.122746 median 15129.665408 high
15186.743199

== 80 clients, permanent tables ==
master: low 7601.067552 median 7612.898321 high 7631.487355
pgxact-v2: low 11712.895410 median 12004.807309 high 12512.078569
pgxact-v2+recentglobalxmin: low 15186.695057 median 15810.452158 high
16166.272699

I don't see much reason to wait around any further on the core patch
(pgact-v2.patch) so I'll commit that now.

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