Re: autovac issue with large number of tables

On 7/31/20 1:26 AM, Kasahara Tatsuhito wrote:

On Tue, Jul 28, 2020 at 3:49 AM Jim Nasby <nasbyj@amazon.com> wrote:

I'm in favor of trying to improve scheduling (especially allowing users
to control how things are scheduled), but that's a far more invasive
patch. I'd like to get something like this patch in without waiting on a
significantly larger effort.

BTW, Have you tried the patch suggested in the thread below?

https://www.postgresql.org/message-id/20180629.173418.190173462.horiguchi.kyotaro%40lab.ntt.co.jp

The above is a suggestion to manage statistics on shared memory rather
than in a file, but I think this feature may mitigate your problem.
I think that feature has yet another performance challenge, but it
might be worth a try.
The above patch will also require a great deal of effort to get into
the PostgreSQL-core, but I'm curious to see how well it works for this
problem.

Without reading the 100+ emails or the 260k patch, I'm guessing that it won't help because the problem I observed was spending most of it's time in

  42.62%  postgres          [.] hash_search_with_hash_value

I don't see how moving things to shared memory would help that at all.

BTW, when it comes to getting away from using files to store stats, IMHO the best first pass on that is to put hooks in place to allow an extension to replace/supplement different parts of the existing stats infrastructure.