Re: Dynamic gathering the values for seq_page_cost/xxx_cost

On Sat, Sep 26, 2020 at 1:51 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:

> On Sat, Sep 26, 2020 at 8:17 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> >
> > As for the testing with cache considered, I found how to estimate cache
> hit
> > ratio is hard or how to control a hit ratio to test is hard. Recently I
> am thinking
> > a method that we can get a page_reads, shared_buffer_hit from pg_kernel
> > and the real io (without the file system cache hit) at os level (just as
> what
> > iotop/pidstat do). then we can know the shared_buffer hit ratio and file
> system
> > cache hit ratio (assume it will be stable after a long run). and then do
> a testing.
> > However this would be another branch of manual work and I still have not
> got
> > it done until now.
>
> FWIW pg_stat_kcache [1] extension accumulates per (database, user,
> queryid) physical reads and writes, so you can easily compute a
> shared_buffers / IO cache / disk hit ratio.
>
> [1] https://github.com/powa-team/pg_stat_kcache
>

WOW, this would be a good extension for this purpose. Thanks for sharing
it.

--
Best Regards
Andy Fan