Postgres Pro
Downloads
Home   >   mailing lists

Thread: system catalog performance

system catalog performance

From
Scott Ribe
Date:
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 nwtwork

From
Sanjeev Randhaye
Date:
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.

Re: Pi nwtwork

From
Mladen Gogala
Date:
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