> "=?iso-8859-1?Q?Vegard_B=F8nes?=" <vegard.bones@xxxxxx> writes: >> I have a problem with large objects in postgresql 8.1: The performance >> of loading large objects into a database goes way down after a few >> days of operation. > >> I have a cron job kicking in twice a day, which generates and loads >> around 6000 large objects of 3.7MB each. Each night, old data is >> deleted, so there is never more than 24000 large object in the >> database. > > Are you sure you're deleting the large objects themselves (ie, > lo_unlink), and not just deleting some references to them? > > A manual "vacuum verbose" on pg_largeobject might be informative. I do call lo_unlink via a trigger function. Also, a SELECT count(distinct loid) FROM pg_largeobject yields the same result as a similar call to the table which references the large objects. Running VACUUM VERBOSE pg_largeobject took quite some time. Here's the output: INFO: vacuuming "pg_catalog.pg_largeobject" INFO: index "pg_largeobject_loid_pn_index" now contains 11060658 row versions in 230587 pages DETAIL: 178683 index pages have been deleted, 80875 are currently reusable. CPU 0.92s/0.10u sec elapsed 199.38 sec. INFO: "pg_largeobject": found 0 removable, 11060658 nonremovable row versions in 6849398 pages DETAIL: 0 dead row versions cannot be removed yet. There were 84508215 unused item pointers. 0 pages are entirely empty. CPU 0.98s/0.10u sec elapsed 4421.17 sec. VACUUM I will try to run VACUUM ANALYZE FULL after the next delete tonight, as suggested by Ivan Voras in another post. But as I understand it, this will put an exclusive lock on whatever table is being vacuumed, so it is not really an option for the database in question, as it needs to be accessitble 24 hours a day. Is there any other possible solution to this? As a side note, I have noticed that loading times seem to have stabilized at just above an hour. Regards, Vegard Bønes -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
