Обсуждение: system catalog performance

Поиск
Список
Период
Сортировка

system catalog performance

От
Scott Ribe
Дата:
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

От
Sanjeev Randhaye
Дата:
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

От
Mladen Gogala
Дата:
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