Search Postgresql Archives

Re: Getting pg_stat_database data takes significant time

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux