'포스트그레스'에 해당되는 글 4건

- Vacuum threshold(autovacuum 임계값) = vacuum base threshold + vacuum scale factor * number of tuples

WITH avt AS (

    SELECT 

            setting AS autovacuum_vacuum_threshold 

    FROM    pg_settings 

    WHERE   name = 'autovacuum_vacuum_threshold'

)

, vsf AS (

    SELECT  setting AS autovacuum_vacuum_scale_factor 

    FROM    pg_settings 

    WHERE   name = 'autovacuum_vacuum_scale_factor'

)

, fma AS (

    SELECT  setting AS autovacuum_freeze_max_age 

    FROM    pg_settings 

    WHERE   name = 'autovacuum_freeze_max_age'

)

, sto AS (

    select  opt_oid

          , split_part(setting, '=', 1) as param

          , split_part(setting, '=', 2) as value 

    from    (

                select 

                        oid opt_oid

                      , unnest(reloptions) setting 

                from    pg_class

            ) opt

)

SELECT  '"'||ns.nspname||'"."'||c.relname||'"' as relation

      , pg_size_pretty(pg_table_size(c.oid)) as table_size

      , age(relfrozenxid) as xid_age

      , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) as autovacuum_freeze_max_age

      , (coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) as autovacuum_vacuum_tuples

      , n_dead_tup as dead_tuples

FROM    pg_class c 

        join pg_namespace ns 

        on  ns.oid = c.relnamespace

        join pg_stat_all_tables stat 

        on  stat.relid = c.oid

        join avt 

        on (1=1) join vsf 

        on (1=1) join fma 

        on (1=1)

        left join sto cavt 

        on cavt.param = 'autovacuum_vacuum_threshold' 

        and c.oid = cavt.opt_oid

        left join sto cvsf 

        on cvsf.param = 'autovacuum_vacuum_scale_factor' 

        and c.oid = cvsf.opt_oid

        left join sto cfma 

        on cfma.param = 'autovacuum_freeze_max_age' 

        and c.oid = cfma.opt_oid

WHERE   c.relkind = 'r' and nspname <> 'pg_catalog'

and     (

            age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)

            or

            coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup

        )

ORDER BY age(relfrozenxid) DESC;

WITH RECURSIVE lt AS (

    SELECT   p.datname

           , p.usename

           , p.pid

           , pg_blocking_pids(p.pid) AS blocking_by

           , p.application_name

           , p.wait_event_type

           , p.state

           , p.query_start

           , p.query

           , ( 

SELECT array_agg(( SELECT relname 

                                    FROM pg_class pc 

                                   WHERE pc.oid = pl.relation ))

                 FROM pg_locks pl

                 WHERE pl.pid = p.pid

                      AND locktype = 'relation'

             ) AS relation

           , 0 AS level

           , row_number() over() AS rn

     FROM pg_stat_activity p

     WHERE cardinality(pg_blocking_pids(p.pid))= 0

     UNION 

    SELECT   p.datname

           , p.usename

           , p.pid

           , pg_blocking_pids(p.pid) AS blocking_by

           , p.application_name

           , p.wait_event_type

           , p.state

           , p.query_start

           , p.query

           , ( SELECT array_agg(( SELECT relname FROM pg_class pc WHERE pc.oid = pl.relation ))

                 FROM pg_locks pl

                WHERE pl.pid = p.pid

                      AND locktype = 'relation'

             ) AS relation

           , level + 1 as level

           , lt.rn

      FROM pg_stat_activity p

           INNER JOIN lt ON lt.pid = any(pg_blocking_pids(p.pid))

) 

SELECT   p.datname

       , p.usename

       , p.pid

       , p.blocking_by

       , p.application_name

       , p.wait_event_type

       , p.state

       , p.relation

       , now() - p.query_start AS elapsed_time

       , p.query

FROM   ( 

      SELECT   lt.rn

     , lt.level

     , lt.datname

     , lt.usename

     , lt.pid

     , lt.blocking_by

     , lt.application_name

     , lt.wait_event_type

                     , lt.state

                     , lt.relation

                     , lt.query_start

                     , lt.query

                     , count( rn ) over( PARTITION BY rn ) AS blocked_cnt

              FROM lt

       ) AS p

WHERE p.blocked_cnt > 1

ORDER by p.rn, p.level, p.blocking_by

;

1) pg_cancel_backend(pid)            -- current query kill and not disconnect

2) pg_terminate_backend(pid)        -- connection disconnect. 

3) kill -9 process