Re: Turning off HOT/Cleanup sometimes

<p dir="ltr"><br /> On Mon, Sep 29, 2014 at 2:13 AM, Andres Freund <<a
href="mailto:andres@anarazel.de">andres@anarazel.de</a>>wrote:<br /> ><br /> > On 2014-09-28 19:51:36 +0100,
SimonRiggs wrote:<br /> > > On 27 September 2014 09:29, Andres Freund <<a
href="mailto:andres@anarazel.de">andres@anarazel.de</a>>wrote:<br /> > > > On 2014-09-27 10:23:33 +0300,
HeikkiLinnakangas wrote:<br /> > > >> This patch has gotten a fair amount of review, and has been rewritten
once<br/> > > >> during the commitfest. I think it's pretty close to being committable, the<br /> > >
>>only remaining question seems to be what to do with system catalogs. I'm<br /> > > >> marking this
as"Returned with feedback", I take it that Simon can proceed<br /> > > >> from here, outside the
commitfest.<br/> > > ><br /> > > > FWIW, I don't think it is, even with that. As is it seems very
likely<br/> > > > that it's going to regress a fair share of workloads. At the very least<br /> > > >
itneeds a fair amount of benchmarking beforehand.<br /> > ><br /> > > There is some doubt there. We've not
seena workload that does<br /> > > actually exhibit a negative behaviour.<br /> ><br /> > Neither is there
muchdata about the magnitude of positive effect the<br /> > patch has...<br /> ><br /> > > I'm not saying
onedoesn't exist, but it does matter how common/likely<br /> > > it is. If anyone can present a performance test
casethat demonstrates<br /> > > a regression, I think it will make it easier to discuss how wide that<br /> >
>case is and what we should do about it. Discussing whether to do<br /> > > various kinds of limited pruning
aremoot until that is clear.<br /> ><br /> > I doubt it'll be hard to construct a case where it'll show. My first
try<br/> > of using a pgbench scale 100, -M prepared, -cj8 with a custom file with<br /> > 1 write and 5 read
transactionyielded the following on my laptop:<br /> ><br /> > Baseline:<br /> >  relname                |
pgbench_tellers<br/> >  pg_total_relation_size | 458752<br /> >  relname                | pgbench_accounts<br />
> pg_total_relation_size | 1590337536<br /> >  relname                | pgbench_branches<br /> >
 pg_total_relation_size| 286720<br /> >  relname                | pgbench_history<br /> >  pg_total_relation_size
|49979392<br /> > Patched:<br /> >  relname                | pgbench_tellers<br /> >  pg_total_relation_size |
516096<br/> >  relname                | pgbench_accounts<br /> >  pg_total_relation_size | 1590337536<br /> >
 relname               | pgbench_branches<br /> >  pg_total_relation_size | 360448<br /> >  relname             
 | pgbench_history<br /> >  pg_total_relation_size | 49528832<br /> ><br /> > So, there's a noticeable
increasein size. Mostly on the smaller tables,<br /> > so probably HOT cleanup was sometimes skipped during UPDATEs
dueto<br /> > locks.<br /> ><br /> > Baseline was:<br /> > tps = 9655.486532 (excluding connections
establishing)<br/> > Patched was:<br /> > tps = 9466.158701 (including connections establishing)<br /><p
dir="ltr">Wasthis reproducible?  I've run your custom sql file with 4 clients (that is how many CPUs I have) on a
machine<br/> with a BBU.  I had wal_level = hot_standby, but the archive_command just returned true without archiving
anything.And using the latest patch.<p dir="ltr">The size of the pgbench_tellers and pgbench_branches relations were
surprisinglyvariable in both patched and unpatched, but there was no reliable difference between them, just within
them.<pdir="ltr">On the TPS front, there was a hint that patched one was slightly slower but the within sample
variationwas also high, and the p-val for difference was only 0.214 on n of 66.<br />  <br /> test case attached.<p
dir="ltr">>That's not a unrealistic testcase.<br /> ><br /> > I'm pretty sure this could be made quite a bit
morepronounced by not<br /> > using a uniform distribution in the pgbench runs. And selecting a test<br /> >
that'smore vulnerable to the change (e.g. using a wider distribution<br /> > for the read only statements than the
modifyingones) would make the the<br /> > CPU overhead of the additional heap_hot_search_buffer() overhead<br />
>heavier.<br /><p dir="ltr">Sorry I don't understand this description.  Why would queries selecting data that is not
changinghave any extra overhead?<p dir="ltr">Is the idea that the hot part of the table for updates would move around
overtime, but the hot part for selects would be even throughout?  I'm not sure how to put that to the test.<p
dir="ltr">Cheers,<pdir="ltr">Jeff<p dir="ltr">