This tables is original ones, it doesn't have any activity now. We copied data to NEW tables and trying to solve root of the 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)
вт, 14 мар. 2017 г. в 17:37, Glyn Astill <glynastill@xxxxxxxxxxx>:
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;
--
___________________________
С наилучшими пожеланиями,
Антон Тарабрин
With best regards,
Anton Tarabrin
