On Fri, Sep 19, 2014 at 5:42 PM, Andres Freund <and...@anarazel.de> wrote: > On 2014-09-19 17:29:08 -0400, Robert Haas wrote: >> > I generally have serious doubts about disabling it generally for >> > read workloads. I imagine it e.g. will significantly penalize >> > workloads where its likely that a cleanup lock can't be acquired >> > every time... >> >> I share that doubt. But I understand why Simon wants to do something, >> too, because the current situation is not great either. > > Right, I totally agree. I doubt a simple approach like this will work in > the general case, but I think something needs to be done. > > I think limiting the amount of HOT cleanup for readonly queries is a > good idea, but I think it has to be gradual. Say after a single cleaned > up page at least another 500 pages need to have been touched till the > next hot cleanup. That way a single query won't be penalized with > cleaning up everything, but there'll be some progress.
I tried this kind of thing several years ago with hint-bit-setting and was unimpressed by the results. http://www.postgresql.org/message-id/aanlktik5qzr8wts0mqcwwmnp-qhgrdky5av5aob7w...@mail.gmail.com http://www.postgresql.org/message-id/aanlktimgkag7wdu-x77gnv2gh6_qo5ss1u5b6q1ms...@mail.gmail.com Granted, I never tried a ratio as low as 500:1, and HOT pruning is not the same thing as setting hint bits, but I think the basic problems are similar, namely: 1. You can't know how many times the page is going to be referenced in the future before it again gets modified. If that number is small, then you shouldn't bother with hint bits, or HOT-pruning, or freezing. But if it's big, you should do all of those things as soon as possible because the benefits are quite significant. Therefore, any change in this area is guaranteed to lose on some easy-to-construct workload, because I just described two of them that want opposing things. 2. Dirtying every N'th page is a great way to generate lots of random I/O that will quite possibly make your disk almost as sad - or even sadder - than dirtying all of them, but without anywhere as near as much performance benefit. Variations on this idea have been proposed so many times over the years that I'm tempted to give some credence to the theory that we ought to adopt one of them. But there will certainly be losers, as well as winners. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers