Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] Table not cleaning up drom dead tuples - Mailing list pgsql-general

From Антон Тарабрин
Subject Re: [GENERAL] Table not cleaning up drom dead tuples
Date
Msg-id CAJH0_XtuLHShHECCTDj_fZAwXeieBtmUEhzPEcFs6PCjHErJ8A@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Table not cleaning up drom dead tuples  (Glyn Astill <glynastill@yahoo.co.uk>)
Responses Re: [GENERAL] Table not cleaning up drom dead tuples
List pgsql-general
Tree view
We're, in general, pretty carefull with our DB, as it contains important data. 
Most rollback is issued by application (which processes all data inside transactions).

p.s.  Time is in UTC (GMT+0)

<THIS_DB>=# select min(xact_start) from pg_stat_activity where state<>'idle';
              min
-------------------------------
 2017-03-14 15:36:05.432139+00
(1 row)

<THIS_DB>=# select * from pg_stat_activity where state<>'idle' order by xact_start limit 1;
  datid  | datname |  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting | state  | backend_xid | backend_xmin |                      query
---------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+-------------+--------------+--------------------------------------------------
 4906146 | <THIS_DB> | 37235 |       10 | pgsql   |                  |             |                 |             | 2017-03-14 05:55:43.287128+00 | 2017-03-14 15:36:05.432139+00 | 2017-03-14 15:36:05.432139+00 | 2017-03-14 15:36:05.432141+00 | f       | active |             |   1621959045 | autovacuum: VACUUM public.stats_y2017_m3_d13_hk2
(1 row)

вт, 14 мар. 2017 г. в 18:15, Glyn Astill <glynastill@yahoo.co.uk>:
Quite a large quantity of rollbacks there.  In your initial email the longest running transaction was an autovacuum task wasn't it?  Are you sure there are no other long running transactions?


Whats the output of:

select min(xact_start) from pg_stat_activity where state<>'idle';
select * from pg_stat_activity where state<>'idle' order by xact_start limit 1;
--
___________________________
С наилучшими пожеланиями,
Антон Тарабрин

With best regards,
Anton Tarabrin

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING
Next
From: Alexander Farber
Date:
Subject: Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING