My pg_largeobjects table and another table seem to be continually growing,
and I'm wondering if anybody else is seeing anything similar.
I'm running 7.2 (on this server)
The Story:
I think that we were _not_ vacuuming often enough (every night), but we are
now running vacuum every 2 hours to make sure that the server doesn't forget
about any of the free spaces. We still see steady growth in the size of the
tables and I don't know how to account for it.
Here's (some of) the output of one of the vacuums that ran this morning
(6am):
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 65794; Tuples 6114596:
Deleted 714.
CPU 9.58s/3.12u sec elapsed 107.23 sec.
NOTICE: Removed 714 tuples in 470 pages.
CPU 0.12s/0.04u sec elapsed 0.19 sec.
NOTICE: Pages 3355231: Changed 544, Empty 0; Tup 6113048: Vac 714, Keep 0,
UnUsed 10981320.
Total CPU 328.14s/23.78u sec elapsed 1622.32 sec.
NOTICE: Analyzing pg_largeobject
NOTICE: --Relation builds--
[about 23 index reports deleted]
NOTICE: Removed 1392 tuples in 93 pages.
NOTICE: Pages 348663: Changed 269, Empty 0; Tup 279019: Vac 1392, Keep 26,
UnUsed 10224517.
Total CPU 46.22s/5.63u sec elapsed 214.26 sec.
NOTICE: Analyzing builds
In both cases, the UnUsed numbers seem excessively big, and indicate growth
when compared to a vacuum that ran approximately 24 hours before:
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 65479; Tuples 6043330:
Deleted 385.
CPU 9.96s/2.69u sec elapsed 102.75 sec.
NOTICE: Removed 385 tuples in 318 pages.
CPU 0.06s/0.06u sec elapsed 0.37 sec.
NOTICE: Pages 3334307: Changed 354, Empty 0; Tup 6042879: Vac 385, Keep 0,
UnUsed 10975511.
Total CPU 325.06s/22.00u sec elapsed 1391.18 sec.
NOTICE: Analyzing pg_largeobject
NOTICE: --Relation builds--
[about 23 index reports deleted]
NOTICE: Removed 737 tuples in 77 pages.
CPU 0.01s/0.00u sec elapsed 0.17 sec.
NOTICE: Pages 347106: Changed 185, Empty 0; Tup 276200: Vac 737, Keep 85,
UnUsed 10181385.
Total CPU 47.36s/5.67u sec elapsed 367.04 sec.
NOTICE: Analyzing builds
(There is growth indicated even in the vacuums that are 2 hours apart, but
the increase is smaller.)
I can't imagine that running vacuum every two hours still isn't enough
(expecially since it will take between 1:00 and 1:45 hours to run (depending
on what the machine is doing). Is it likely that I'm now in a catch-22, and
vacuum still needs to run more frequently than every 2 hours, but can't
because of the large amount of free space it has to scan through?
The structure of the builds table (if it helps):
CREATE TABLE BUILDS(
ID INT PRIMARY KEY,
VISIBLE BOOL,
STATE CHAR(1),
EVALUATED BOOL,
PRODUCT VARCHAR(30),
COMPILER VARCHAR(30),
OS VARCHAR(30),
STDLIBTYPE VARCHAR(30),
DEBUG VARCHAR(30),
LINKTYPE VARCHAR(30),
THREADLIB VARCHAR(30),
EXPORTLEVEL VARCHAR(30),
USERMODE VARCHAR(30),
POSTBUILDCLEAN VARCHAR(30),
PREBUILDCLEAN VARCHAR(30),
SUBMITTED TIMESTAMP,
STARTED TIMESTAMP,
FINISHED TIMESTAMP,
MACHINE VARCHAR(100),
ERRORS INT,
WARNINGS INT,
TESTSATTEMPTED INT,
TESTSPASSED INT,
TESTSFAILED INT,
EXAMPLESATTEMPTED INT,
EXAMPLESPASSED INT,
EXAMPLESFAILED INT,
PING TIMESTAMP,
USER1 VARCHAR(50),
USER2 VARCHAR(50),
USER3 VARCHAR(50),
USER4 VARCHAR(50),
USER5 VARCHAR(50),
USER6 VARCHAR(50)
);
-ron
