Re: Turning off HOT/Cleanup sometimes

El 15/09/14 18:13, Simon Riggs escribió:
> On 15 September 2014 17:09, Robert Haas <robertmhaas@gmail.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 |
2044192pgbench_accounts|    5109728 |     310842 |       1969264pgbench_history  |    2265882 |          0 |
0pgbench_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 |
0pgbench_tellers |        500 |          0 |       1969675pgbench_branches |         50 |          0 |
2150655pgbench_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