Re: Append only tables

Поиск
Список
Период
Сортировка
От Rui DeSousa
Тема Re: Append only tables
Дата
Msg-id 0868F6DF-EA27-4978-867E-40A5E80A209B@crazybean.net
обсуждение исходный текст
Ответ на Re: Append only tables  (Kurt Roeckx <kurt@roeckx.be>)
Ответы Re: Append only tables  (Rui DeSousa <rui@crazybean.net>)
Список pgsql-admin


On Mar 21, 2020, at 4:30 AM, Kurt Roeckx <kurt@roeckx.be> wrote:


I would recommend vacuuming the table to get the existing records frozen; then I believe you should see fewer updates to the all the base files.

If this is needed, why doesn't autovacuum do this for me? Or do I
need to modify some parameters so that autovacuum does get
triggered?

I actually didn't run any select query on that table yet, it's
still importing the data. Can an insert also trigger the freeze?


As Keith stated; due to lack of updates and deletes the counters are not necessarily going to trigger an auto vacuum. Have a look at your auto vacuum settings and freeze settings to determine the best approach.

You want to be more aggressive with those settings or manually vacuum the table periodically.  What you don’t want to occur to an extremely large table that has never been vacuumed is have it issue a freeze wraparound vacuum.   The select freezing the tuples is a good thing here as it helps advance the xmin age.  Really don’t want wraparound vacuums to occur; as that would indicate not enough vacuums are occurring for your current workload/volume.   

Here’s a set of queries that I use to monitor transaction id age of the database and tables.  


create or replace function util.numeric_format(_percision int, _scale int)
returns varchar(100)
as $$
declare 
 _grpCnt int;
 _decCnt int;
begin
    _grpCnt := ((_percision - _scale) / 3);
    _decCnt := ((_percision - _scale) % 3);

    return trim(leading ',' from (repeat('9', _decCnt) || repeat('G999', _grpCnt) || 'D' || repeat('9', _scale)));
end;
$$ language plpgsql
  immutable returns null on null input
;
comment on function numeric_format(int, int)
  is 'Returns a format string for a numeric value given the percision.'
;


with stat as (
  select datname as database
    , (datfrozenxid::text)::bigint as frozen_txid
    , age(datfrozenxid) as txid_age
    , (2^31 - 1000000) as max_txid
    , (2^31 - 1000000) - age(datfrozenxid) as txid_free
    , current_setting('autovacuum_freeze_max_age'::text)::double precision as freeze_age
    , case pg_is_in_recovery() when true then 0 else txid_current() end & (~(-1::bigint << 31)) as current_txid
    , case pg_is_in_recovery() when true then 0 else txid_current() end as epoch_txid
  from pg_database
  where datname = current_database()
)
select stat.database
  , to_char(stat.frozen_txid, util.numeric_format(10,0)) as frozen_txid
  , to_char(stat.current_txid, util.numeric_format(10,0)) as current_txid
  , to_char(stat.epoch_txid, util.numeric_format(10,0)) as epoch_txid
  , to_char(txid_age, numeric_format(10, 0)) as txid_age
  , to_char(txid_free, numeric_format(10, 0)) as txid_free
  , to_char(txid_age / max_txid * 100, '9G999D99%') as used_txid_pct
  , to_char(txid_age / freeze_age * 100, '9G999D99%') as used_freeze_pct
  , now() as asof
from stat
;

select n.nspname as schema
  , c.relname as table_name
  , c.relpages::bigint as pages
  , pg_size_pretty(c.relpages::bigint * current_setting('block_size')::bigint) as size
  , to_char((c.relfrozenxid::text)::bigint, numeric_format(15, 0)) as frozen_xtid
  , to_char(age(c.relfrozenxid), numeric_format(15, 0)) as txid_age
  , to_char((2^31 - 1000000) - age(c.relfrozenxid), numeric_format(15, 0)) as txid_free
  , to_char(age(c.relfrozenxid) / (2^31 - 1000000) * 100, '9G999D99%') as used_txid_pct
  , to_char(age(c.relfrozenxid) / current_setting('autovacuum_freeze_max_age'::text)::double precision * 100, '9G999D99%') as used_freeze_pct
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('r', 'm')
order by (c.relfrozenxid::text)::bigint
  , c.relpages::bigint desc
limit 50
;

В списке pgsql-admin по дате отправления:

Предыдущее
От: Keith
Дата:
Сообщение: Re: Append only tables
Следующее
От: Rui DeSousa
Дата:
Сообщение: Re: Append only tables