Re: Getting pg_stat_database data takes significant time

[Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx>]

On Wed, 11 Aug 2021 at 19:12, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:

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@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@db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null

real    0m7.269s

user    0m0.003s

sys     0m0.000s

ok pls ignore, i think this was wrong.

the delay while pg_class was locked on psql connection, not pg_stat_database query.

strace -r psql -c 'select * from pg_stat_database;' >/dev/null

....

     0.000612 poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])

     4.819832 recvfrom(3, "R\0\0\0\10\0\0\0\0S\0\0\0\32application_name\0p"..., 16384, 0, NULL, NULL) = 396 -- the time till table was locked

I think i'll back off here, do some gdb tracing myself before i make assumptions.