To all,
The facts:
PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI
drives in hardware RAID 0 configuration. Database size with indexes is
currently 122GB. DB size before we completed the vacuum full was 150GB.
We have recently done a major update to a table, f_pageviews, in our
data warehouse. The f_pageviews table contains about 118 million rows.
Schema is at the end of this message. We probably touched 80% of those
rows with the update. We then commenced to drop all indexes on said
table, except the primary key, and attempted to do a vacuum full on the
entire DB. You can see the output below it failed. We then tried to do
the vacuum full on the f_pageviews table alone and the same error
occurred. I did vacuum full on other tables in the schema, one of them
about 8 times larger but with very few dead tuples, and all complete
successfully. We ended up dumping the table and reloading it to
eliminate the dead tuples. After the reload we did the vacuum full with
no problems.
Does anyone have an explanation as to why this might occur?
Thanks.
--sean
nohup /usr/local/pgsql/bin/vacuumdb -d tripmaster -U tripmaster -f -z -v
-t f_pageviews > & /tmp/vacuum2.log &
tail -f /tmp/vacuum2.log
INFO: vacuuming "public.f_pageviews"
INFO: "f_pageviews": found 17736235 removable, 111796026 nonremovable
row versions in 1552349 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 156 to 244 bytes long.
There were 134565418 unused item pointers.
Total free space (including removable row versions) is 27898448080 bytes.
583420 pages are or will become empty, including 0 at the end of the table.
1088195 pages containing 27860101432 free bytes are potential move
destinations.
CPU 238.91s/27.44u sec elapsed 1261.80 sec.
INFO: index "f_pageviews_pkey" now contains 111796026 row versions in
210003 pages
DETAIL: 15618120 index row versions were removed.
734 index pages have been deleted, 734 are currently reusable.
CPU 96.09s/139.03u sec elapsed 1569.93 sec.
vacuumdb: vacuuming of table "f_pageviews" in database "tripmaster"
failed: ERROR: out of memory
DETAIL: Failed on request of size 350.
\d f_pageviews
Table "public.f_pageviews"
Column | Type | Modifiers
------------------------+---------+-------------------------------------------------------------
id | integer | not null default
nextval('public.f_pageviews_id_seq'::text)
date_key | integer | not null
time_key | integer | not null
content_key | integer | not null
location_key | integer | not null
session_key | integer | not null
subscriber_key | text | not null
persistent_cookie_key | integer | not null
ip_key | integer | not null
referral_key | integer | not null
servlet_key | integer | not null
tracking_key | integer | not null
provider_key | text | not null
marketing_campaign_key | integer | not null
orig_airport | text | not null
dest_airport | text | not null
commerce_page | boolean | not null default false
job_control_number | integer | not null
sequenceid | integer | not null default 0
url_key | integer | not null
useragent_key | integer | not null
web_server_name | text | not null default 'Not Available'::text
cpc | integer | not null default 0
referring_servlet_key | integer | default 1
first_page_key | integer | default 1
newsletterid_key | text | not null default 'Not Available'::text
Indexes:
"f_pageviews_pkey" primary key, btree (id)
"idx_page_views_content" btree (content_key)
"idx_pageviews_date_dec_2003" btree (date_key) WHERE ((date_key >=
335) AND (date_key <= 365))
"idx_pageviews_date_nov_2003" btree (date_key) WHERE ((date_key >=
304) AND (date_key <= 334))
"idx_pageviews_referring_servlet" btree (referring_servlet_key)
"idx_pageviews_servlet" btree (servlet_key)
"idx_pageviews_session" btree (session_key)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
