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 <glynast...@yahoo.co.uk>:
> 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
