> "=?iso-8859-1?Q?Vegard_B=F8nes?=" <vegard.bones@met.no> 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
