More info on what is bloating:
It's only in one database (the one that's most used), and after running oid2name on the bloated files, the result is (mysteriously) empty. Here's the run on the three enormous files:
$ /usr/local/bin/oid2name -d smt -o 160779 From database "smt": Filenode Table Name ----------------------
$ /usr/local/bin/oid2name -d smt -o 65782869 From database "smt": Filenode Table Name ----------------------
$ /usr/local/bin/oid2name -d smt -o 83345634 From database "smt": Filenode Table Name ----------------------
The file list looks like this (with normal sized files mostly removed): 1.0G ./106779 1.0G ./106779.1 1.0G ./106779.2 1.0G ./106779.3 978M ./106779.4 1.0G ./65782869 248M ./65782869.1 0B ./65782871 8.0K ./65782873 780M ./83345634 0B ./83345636 8.0K ./83345638
So does the empty result mean it's a temporary table? There is one temporary table (in the function previously mentioned) that does get created and dropped with some regularity.
Thanks again, Richard
On Apr 20, 2005, at 2:06 PM, Richard Plotkin wrote:
Hi Tom,
Q: what have you got the FSM parameters set to?
Here's from postgresql.conf -- FSM at default settings. # - Memory -
shared_buffers = 30400 # min 16, at least max_connections*2, 8KB each
work_mem = 32168 # min 64, size in KB
#maintenance_work_mem = 16384 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
max_files_per_process = 750 #1000 # min 25 #preload_libraries = ''
Q: what exactly is bloating? Without knowing which tables or indexes
are growing, it's hard to speculate about the exact causes. Use du and
oid2name, or look at pg_class.relpages after a plain VACUUM.
This I do not know. I've disabled the cron jobs and will let the system bloat, then I will gather statistics (I'll give it 12-24 hours).
It's likely that the real answer is "you need to vacuum more often than every six hours", but I'm trying not to jump to conclusions.
That could be it, except that I would expect the problem to then look more like a gradual increase in CPU usage and a gradual increase in use of disk space. Mine could be an invalid assumption, but the system here looks like it goes from no problem to 100% problem within a minute.
Thanks again! Richard
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
