Re: Getting pg_stat_database data takes significant time

On Wed, 11 Aug 2021 at 18:59, hubert depesz lubaczewski <depesz(at)depesz(dot)com>
wrote:

> On Wed, Aug 11, 2021 at 06:52:15PM +0530, Vijaykumar Jain wrote:
> > Just taking a shot, as I have seen in some previous issues? Ignore is
> not
> > relevant.
> >
> > Can you run vacuum on pg_class and check the query again , or do you see
> > pg_class bloated ?
>
> pg_class is large, but vacuuming it didn't help for time of query on
> pg_stat_database.
>
> ok my guess here was, since pg_class is updated every now and then with
stats, it might require some lock while adding the data.
so if it were bloated, that would block the planner to get the estimates ,
and hence delay the query at whole.
but that was a wild guess.
--pg_class not locked
postgres(at)db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null

real 0m0.016s
user 0m0.003s
sys 0m0.000s

-- pg_class locked and the query completed when tx it unlocks
postgres(at)db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null

real 0m7.269s
user 0m0.003s
sys 0m0.000s

> > The other option would be gdb backtrace I think that would help.
>
> backtrace from what? It doesn't *break*, it just takes strangely long time.
>
> I could envision attaching gdb to pg process and getting backtrace, but
> when?
> before running the query? after?
>
>
backtrace of the running query, maybe you might have to run this multiple
times against the raw query directly via psql, since you get this delay
occasionally,
why i say this, --
the backtrace would probably show if it is hanging in a normal plan
execution, or something else.
or maybe perf/strace to trace syscall timings
but i know you know more than me :) , just asking if the backtrace helps
expose something helpful.

--
Thanks,
Vijay
Mumbai, India