Re: Turning off HOT/Cleanup sometimes

El 15/09/14 18:13, Simon Riggs escribió:
> On 15 September 2014 17:09, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> Do we really want to disable HOT for all catalog scans?
> The intention of the patch is that catalog scans are treated
> identically to non-catalog scans. The idea here is that HOT cleanup
> only occurs on scans on target relations, so only INSERT, UPDATE and
> DELETE do HOT cleanup.
>
> It's possible that many catalog scans don't follow the normal target
> relation logic, so we might argue we should use HOT every time. OTOH,
> since we now have separate catalog xmins we may find that using HOT on
> catalogs is no longer effective. So I could go either way on how to
> proceed; its an easy change either way.
>

I setup a more concurrent scenario and the difference is quite larger:

Without patch:
1st concurrent with writes:
tps = 5705.261620 (including connections establishing)
tps = 5945.338557 (excluding connections establishing)
2nd no writes being executed:
tps = 9988.792575 (including connections establishing)
tps = 11059.671351 (excluding connections establishing)

Patched version:
1st concurrent with writes:
tps = 9476.741707 (including connections establishing)
tps = 10274.831185 (excluding connections establishing)
2nd no writes being executed:
tps = 12993.644808 (including connections establishing)
tps = 15171.214744 (excluding connections establishing)

Stats (writes have been run with a time limit, not by tx):

hotcleanup=# select relname ,n_live_tup, n_dead_tup, n_tup_hot_upd from
pg_stat_user_tables where relname ~ 'pgbench';
relname | n_live_tup | n_dead_tup | n_tup_hot_upd
------------------+------------+------------+---------------
pgbench_tellers | 500 | 0 | 2044192
pgbench_accounts | 5109728 | 310842 | 1969264
pgbench_history | 2265882 | 0 | 0
pgbench_branches | 50 | 0 | 2237167
(4 rows)

hotcleanup=# select relname ,n_live_tup, n_dead_tup, n_tup_hot_upd from
pg_stat_user_tables where relname ~ 'pgbench';
relname | n_live_tup | n_dead_tup | n_tup_hot_upd
------------------+------------+------------+---------------
pgbench_history | 2148946 | 0 | 0
pgbench_tellers | 500 | 0 | 1969675
pgbench_branches | 50 | 0 | 2150655
pgbench_accounts | 5098774 | 300123 | 1897484
(4 rows)

I ran the regression tests over the patched version and they passed ok.

--
--
Emanuel Calvo http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services