On 2014-09-28 19:51:36 +0100, Simon Riggs wrote: > On 27 September 2014 09:29, Andres Freund <and...@anarazel.de> wrote: > > On 2014-09-27 10:23:33 +0300, Heikki Linnakangas wrote: > >> This patch has gotten a fair amount of review, and has been rewritten once > >> during the commitfest. I think it's pretty close to being committable, the > >> only remaining question seems to be what to do with system catalogs. I'm > >> marking this as "Returned with feedback", I take it that Simon can proceed > >> from here, outside the commitfest. > > > > FWIW, I don't think it is, even with that. As is it seems very likely > > that it's going to regress a fair share of workloads. At the very least > > it needs a fair amount of benchmarking beforehand. > > There is some doubt there. We've not seen a workload that does > actually exhibit a negative behaviour.
Neither is there much data about the magnitude of positive effect the patch has... > I'm not saying one doesn't exist, but it does matter how common/likely > it is. If anyone can present a performance test case that demonstrates > a regression, I think it will make it easier to discuss how wide that > case is and what we should do about it. Discussing whether to do > various kinds of limited pruning are moot until that is clear. I doubt it'll be hard to construct a case where it'll show. My first try of using a pgbench scale 100, -M prepared, -cj8 with a custom file with 1 write and 5 read transaction yielded the following on my laptop: Baseline: relname | pgbench_tellers pg_total_relation_size | 458752 relname | pgbench_accounts pg_total_relation_size | 1590337536 relname | pgbench_branches pg_total_relation_size | 286720 relname | pgbench_history pg_total_relation_size | 49979392 Patched: relname | pgbench_tellers pg_total_relation_size | 516096 relname | pgbench_accounts pg_total_relation_size | 1590337536 relname | pgbench_branches pg_total_relation_size | 360448 relname | pgbench_history pg_total_relation_size | 49528832 So, there's a noticeable increase in size. Mostly on the smaller tables, so probably HOT cleanup was sometimes skipped during UPDATEs due to locks. Baseline was: tps = 9655.486532 (excluding connections establishing) Patched was: tps = 9466.158701 (including connections establishing) That's not a unrealistic testcase. I'm pretty sure this could be made quite a bit more pronounced by not using a uniform distribution in the pgbench runs. And selecting a test that's more vulnerable to the change (e.g. using a wider distribution for the read only statements than the modifying ones) would make the the CPU overhead of the additional heap_hot_search_buffer() overhead heavier. > > My memory was that it took months for people to understand the > frequent update use case, since catching it in flagrante delicto was > hard. That may be the case here, or not, but negative-benefit > experimental results very welcome. > > Updated patch attached to address earlier comments. contrib (at least pgstattuple) doesn't currently compile with this... Easily patched up tho. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
