Re: autovacuum not freeing up unused space on 8.3.0 - Mailing list pgsql-general
From | Stuart Brooks |
---|---|
Subject | Re: autovacuum not freeing up unused space on 8.3.0 |
Date | |
Msg-id | 47C42714.70409@cat.co.za Whole thread Raw |
In response to | Re: autovacuum not freeing up unused space on 8.3.0 ("Pavan Deolasee" <pavan.deolasee@gmail.com>) |
List | pgsql-general |
>> What concerns me is that once the size has grown, even a VACUUM FULL >> doesn't recover the space. Regular external VACUUMs keep the table at >> around 10MB but if I use autovacuum and it grows to 40MB, a VACUUM FULL >> will only get it down to 35MB. Is it possible that a canceled autovacuum >> could result in permanently lost space? >> >> > > AFAIK it should not. Can you also post VACUUM FULL VERBOSE output ? > > I have attached the vacuum output below, along with the table definition and a before and after of the table size. In this case a full vacuum (on the 20000 row table) took it down from 34MB to 21MB. Maybe you can tell me if this is reasonable, bearing in mind that after inserting 20000 rows at the start the size is about 6MB, and under normal vacuuming conditions it sits around 10-12MB. This is better than the last time I ran it though. Thanks for the help, Stuart metadb=> \d test.transactions Table "test.transactions" Column | Type | Modifiers -----------------+--------------------------------+----------------------------------------------------------------------------- transaction_key | bigint | not null default nextval('test.transactions_transaction_key_seq'::regclass) time | timestamp(6) without time zone | not null cashier | text | not null till | integer | not null ring | integer | not null ev_tstamp | integer | not null ev_id | integer | not null camera | integer | not null Indexes: "transactions_pkey" PRIMARY KEY, btree (transaction_key) "transactions_camera_index" btree (camera) "transactions_cashier_index" btree (cashier, transaction_key) "transactions_event_index" btree (ring, ev_tstamp, ev_id) "transactions_time_index" btree ("time", transaction_key) metadb=> select pg_total_relation_size('test.transactions'); pg_total_relation_size ------------------------ 34242560 (1 row) metadb=> vacuum full verbose test.transactions; INFO: vacuuming "test.transactions" INFO: "transactions": found 0 removable, 19996 nonremovable row versions in 1592 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 64 to 68 bytes long. There were 2109 unused item pointers. Total free space (including removable row versions) is 10199944 bytes. 1416 pages are or will become empty, including 0 at the end of the table. 1347 pages containing 10194740 free bytes are potential move destinations. CPU 0.00s/0.01u sec elapsed 0.24 sec. INFO: index "transactions_pkey" now contains 19996 row versions in 100 pages DETAIL: 0 index row versions were removed. 42 index pages have been deleted, 42 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "transactions_event_index" now contains 19996 row versions in 215 pages DETAIL: 0 index row versions were removed. 93 index pages have been deleted, 93 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.05 sec. INFO: index "transactions_camera_index" now contains 19996 row versions in 146 pages DETAIL: 0 index row versions were removed. 56 index pages have been deleted, 56 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.02 sec. INFO: index "transactions_cashier_index" now contains 19996 row versions in 429 pages DETAIL: 0 index row versions were removed. 290 index pages have been deleted, 290 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.05 sec. INFO: index "transactions_time_index" now contains 19996 row versions in 1496 pages DETAIL: 115518 index row versions were removed. 1412 index pages have been deleted, 1412 are currently reusable. CPU 0.00s/0.11u sec elapsed 4.28 sec. INFO: "transactions": moved 19996 row versions, truncated 1592 to 208 pages DETAIL: CPU 0.12s/0.73u sec elapsed 20.75 sec. INFO: index "transactions_pkey" now contains 19996 row versions in 112 pages DETAIL: 19996 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.06 sec. INFO: index "transactions_event_index" now contains 19996 row versions in 215 pages DETAIL: 19996 index row versions were removed. 77 index pages have been deleted, 77 are currently reusable. CPU 0.00s/0.01u sec elapsed 0.04 sec. INFO: index "transactions_camera_index" now contains 19996 row versions in 152 pages DETAIL: 19996 index row versions were removed. 58 index pages have been deleted, 58 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.12 sec. INFO: index "transactions_cashier_index" now contains 19996 row versions in 429 pages DETAIL: 19996 index row versions were removed. 273 index pages have been deleted, 273 are currently reusable. CPU 0.00s/0.01u sec elapsed 0.13 sec. INFO: index "transactions_time_index" now contains 19996 row versions in 1496 pages DETAIL: 19996 index row versions were removed. 1339 index pages have been deleted, 1339 are currently reusable. CPU 0.00s/0.01u sec elapsed 0.30 sec. INFO: vacuuming "pg_toast.pg_toast_18356" INFO: "pg_toast_18356": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 0 to 0 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 0 bytes. 0 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pg_toast_18356_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM metadb=> select pg_total_relation_size('test.transactions'); pg_total_relation_size ------------------------ 21405696 (1 row)
pgsql-general by date: