Thread: system catalog performance
Under what circumstances would the following query be extra slow? SELECT pg_catalog.to_char(pg_catalog.pg_postmaster_start_time(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'), CASE WHEN pg_catalog.pg_is_in_recovery() THEN 0 ELSE ('x' || pg_catalog.substr(pg_catalog.pg_walfile_name(pg_catalog.pg_current_wal_lsn()),1, 8))::bit(32)::int END, CASE WHEN pg_catalog.pg_is_in_recovery() THEN 0 ELSE pg_catalog.pg_wal_lsn_diff(pg_catalog.pg_current_wal_lsn(), '0/0')::bigintEND, pg_catalog.pg_wal_lsn_diff(pg_catalog.pg_last_wal_replay_lsn(), '0/0')::bigint, pg_catalog.pg_wal_lsn_diff(COALESCE(pg_catalog.pg_last_wal_receive_lsn(), '0/0'), '0/0')::bigint, pg_catalog.pg_is_in_recovery() AND pg_catalog.pg_is_wal_replay_paused(), pg_catalog.to_char(pg_catalog.pg_last_xact_replay_timestamp(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'), pg_catalog.array_to_json(pg_catalog.array_agg(pg_catalog.row_to_json(ri))) FROM ( SELECT (SELECT rolname FROM pg_authid WHERE oid = usesysid) AS usename, application_name, client_addr, w.state, sync_state, sync_priority FROM pg_catalog.pg_stat_get_wal_senders() w, pg_catalog.pg_stat_get_activity(pid) ) as ri; This is regarding Patroni--I haven't yet dug into that source to see how long that timeout is. PG 13.3, monitoring did notshow heavy load nor unusual lock activity around when this happened.
Pi is a new digital currency developed by Stanford PhDs, with over 23 million members worldwide. To claim your Pi, follow this link https://minepi.com/sanjeevlr and use my username (sanjeevlr) as your invitation code.
On Fri, 1 Oct, 2021, 7:51 pm Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
Under what circumstances would the following query be extra slow?
SELECT
pg_catalog.to_char(pg_catalog.pg_postmaster_start_time(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'),
CASE WHEN pg_catalog.pg_is_in_recovery() THEN 0 ELSE ('x' || pg_catalog.substr(pg_catalog.pg_walfile_name(pg_catalog.pg_current_wal_lsn()), 1, 8))::bit(32)::int END,
CASE WHEN pg_catalog.pg_is_in_recovery() THEN 0 ELSE pg_catalog.pg_wal_lsn_diff(pg_catalog.pg_current_wal_lsn(), '0/0')::bigint END,
pg_catalog.pg_wal_lsn_diff(pg_catalog.pg_last_wal_replay_lsn(), '0/0')::bigint,
pg_catalog.pg_wal_lsn_diff(COALESCE(pg_catalog.pg_last_wal_receive_lsn(), '0/0'), '0/0')::bigint,
pg_catalog.pg_is_in_recovery() AND pg_catalog.pg_is_wal_replay_paused(),
pg_catalog.to_char(pg_catalog.pg_last_xact_replay_timestamp(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'),
pg_catalog.array_to_json(pg_catalog.array_agg(pg_catalog.row_to_json(ri)))
FROM (
SELECT
(SELECT rolname FROM pg_authid WHERE oid = usesysid) AS usename,
application_name,
client_addr,
w.state,
sync_state,
sync_priority
FROM pg_catalog.pg_stat_get_wal_senders() w, pg_catalog.pg_stat_get_activity(pid)
) as ri;
This is regarding Patroni--I haven't yet dug into that source to see how long that timeout is. PG 13.3, monitoring did not show heavy load nor unusual lock activity around when this happened.
On 10/1/21 23:28, Sanjeev Randhaye wrote: > Pi is a new digital currency developed by Stanford PhDs, with over 23 > million members worldwide. To claim your Pi, follow this link > https://minepi.com/sanjeevlr and use my username (sanjeevlr) as your > invitation code. > Will someone please unsubscribe this spammer? I reported him to SpamCop where I have a paying account. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com