Обсуждение: [GENERAL] Table not cleaning up drom dead tuples

> From: Антон Тарабрин <tarabanton@gmail.com>
> To: pgsql-general@postgresql.org
> Sent: Tuesday, 14 March 2017, 12:09
> Subject: [GENERAL] Table not cleaning up drom dead tuples

> General info about  our database:

> https://gist.github.com/aCLr/dec78ab031749e517550ac11f8233f70
>
> Information about problematic tables:
> https://gist.github.com/tarabanton/edf7f8dc26eb7ec37a9cfa3424493871
>
> As you can see, there 2 to 10 times dead tuples compared to actual row count.
> We've tried VACUUM FULL and CLUSTER without any success.
> There is no long term locks, idle in transaction requests or prepared transactions.
>
> We are temporarily fixing this like that:
> BEGIN;
> CREATE TABLE _orders_temp (LIKE orders_y2017_m3 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING
COMMENTS);
> INSERT INTO _orders_temp select * from orders_y2017_m3;
> ALTER TABLE orders_y2017_m3 RENAME TO __orders_y2017_m3_to_drop;
> ALTER TABLE __orders_y2017_m3_to_drop NO INHERIT orders;
> ALTER TABLE _orders_temp RENAME TO orders_y2017_m3;
> ALTER TABLE orders_y2017_m3 INHERIT orders;
> COMMIT;
>
> but bloat returns again and again

Some bloat is to be expected unless you've totally static data due to the postgres MVCC model.

Are you saying vacuum full and cluster aren't removing the bloat?  Sounds unlikely to me.


Issues only arise when you can't manage it; from what you've posted we can see autovacuum is running, but perhaps it's
notkeeping up with your workload, or your update patterns make it difficult to keep bloat down; we can see some
rollbackswhich I'm sure are part of your problem. 

You could try updating to the latest minor version of postgres as there are a few fixes to autovacuum in versions after
9.5.3,but I think analyzing your update patterns and/or tuning autovacuum will be your starting point. 

> ________________________________

> From: Антон Тарабрин <tarabanton@gmail.com>
> To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Sent: Tuesday, 14 March 2017, 14:05
> Subject: Re: [GENERAL] Table not cleaning up drom dead tuples
>
>
> Yep. VACUUM FULL not helping us on OLD table, that are not getting updated and not used in any requests. Bloat is
stillthere  
> This is production system, so now we are investigating why it's happening.
>
>
> > Information about problematic tables:
> > https://gist.github.com/tarabanton/edf7f8dc26eb7ec37a9cfa3424493871
> At the link above is some results of VACUUM (ANALYZE, VERBOSE) from source (OLD) table.
>

So what's the output of vacuum full? Or are you saying you can't sustain the exclusive lock vacuum full would require?

Plain vacuum can only reclaim free space at the end of the table, fragmented dead rows can only be marked available for
reuse.

Perhaps give us some idea of activity on your database/tables:


select * from pg_stat_user_tables where relname in ('__orders_y2017_m3_to_drop', '__orders_y2017_m2_to_drop');
select * from pg_stat_database;

> This tables is original ones, it doesn't have any activity now. We copied data to NEW tables and trying to solve root
ofthe problem 
>
> <THIS_DB> - target database where broken tables are located
>
>
> ----- VACUUM FULL VERBOSE
> <THIS_DB>=# VACUUM (FULL, VERBOSE) __orders_y2017_m2_to_drop;
> INFO:  vacuuming "public.__orders_y2017_m2_to_drop"
> INFO:  "__orders_y2017_m2_to_drop": found 0 removable, 3179076 nonremovable row versions in 551423 pages
> DETAIL:  1778770 dead row versions cannot be removed yet.
> CPU 30.92s/102.66u sec elapsed 184.69 sec.
>
> <THIS_DB>=# VACUUM (FULL, VERBOSE) __orders_y2017_m3_to_drop;
> INFO:  vacuuming "public.__orders_y2017_m3_to_drop"
> INFO:  "__orders_y2017_m3_to_drop": found 0 removable, 9103104 nonremovable row versions in 1520371 pages
>
> DETAIL:  8396820 dead row versions cannot be removed yet.
> CPU 65.00s/284.03u sec elapsed 399.66 sec.
>
>
> ----- DB INFO
> <THIS_DB>=# select * from pg_stat_user_tables where relname in ('__orders_y2017_m3_to_drop',
'__orders_y2017_m2_to_drop');
>    relid   | schemaname |          relname          | seq_scan | seq_tup_read | idx_scan  | idx_tup_fetch | n_tup_ins
|n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze |          last_vacuum
|       last_autovacuum        |         last_analyze          |       last_autoanalyze        | vacuum_count |
autovacuum_count| analyze_count | autoanalyze_count  
>
-----------+------------+---------------------------+----------+--------------+-----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
>  179718008 | public     | __orders_y2017_m2_to_drop |     5615 |   7934041177 | 328044580 |    7979850698 |         0
|  3065776 |         0 |         25685 |    3082885 |    1759481 |                   0 | 2017-03-14 11:57:40.388527+00
|2017-03-14 07:37:50.907757+00 | 2017-03-14 11:57:42.656628+00 | 2017-03-13 16:15:55.60846+00  |            5 |
     96 |             4 |                15 
>  207347508 | public     | __orders_y2017_m3_to_drop |     1128 |    794959804 | 129799001 |    1292952066 |    706089
|  8377499 |         0 |        118035 |    8937540 |    8406385 |                   0 | 2017-03-14 11:57:58.026816+00
|2017-03-14 10:09:08.597031+00 | 2017-03-14 11:57:59.117331+00 | 2017-03-14 04:11:11.370923+00 |            4 |
     11 |             4 |                 7 
> (2 rows)
>
> <THIS_DB>=# select * from pg_stat_database;
>    datid   |      datname       | numbackends | xact_commit | xact_rollback | blks_read |   blks_hit   | tup_returned
| tup_fetched  | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks |
blk_read_time| blk_write_time |          stats_reset  
>
-----------+--------------------+-------------+-------------+---------------+-----------+--------------+---------------+--------------+--------------+-------------+-------------+-----------+------------+------------+-----------+---------------+----------------+-------------------------------
>    4906146 | <THIS_DB>            |          62 |    24781721 |       5888121 | 492125811 | 348274702788 |
1127846911908| 250049066062 |    413981238 |   188610068 |      397036 |         0 |         53 | 7507001344 |
1|             0 |              0 | 2017-03-06 02:33:26.466458+00 
>
>  113713583 | sentry             |           0 |      350030 |           342 |     11574 |     33444698 |
22519113|     10577975 |         2438 |       27672 |           2 |         0 |          0 |          0 |         0 |
         0 |              0 | 2017-03-06 02:33:24.156858+00 
>
>  148539615 | test               |           0 |           0 |             0 |         0 |            0 |
0|            0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |
  0 |              0 |  
>  161510793 | ... |           0 |           0 |             0 |         0 |            0 |             0 |
0|            0 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |
    0 |  
> (8 rows)

Quite a large quantity of rollbacks there.  In your initial email the longest running transaction was an autovacuum
taskwasn'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;

> 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-1405: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)
>
>


And no prepared transactions you say?

select * from pg_prepared_xacts;

Perhaps someone else will chime in ...