> If your DB is large enough that it takes an hour to run VACUUM, then
> those FSM parameters are surely way too small. I'd try something
> like 1000/1000000 for starters.
> Also, boosting vacuum_mem might help speed up VACUUM, if you have a
> reasonable amount of RAM in the box. (Instead of 8192 = 8Mb, try
> 50000 or so.)
Done. Thanks.
I think it would run faster if there werent all those clients eating up cpu
and memory.
During vacuum i noticed 105 concurrent clients bothering the database.
>
> BTW, what *is* the amount of RAM in the box? I'm eyeing the
> shared_buffers setting with suspicion. It may be too high.
> 500Mb in shared buffers would very likely be more usefully spent
> elsewhere.
Amount of RAM is 1GB. At the moment postgres runs nearly alone on the
machine.
I believed it would be a good idea to set the shared buffers as high as
possible....
i lowered them to 50000.
> It's very likely that the undersized FSM settings have caused the system
> to leak a lot of disk space, and that the only way to recover it will
> now be a VACUUM FULL. Which will be painful :-(. Can you show us the
> output of VACUUM VERBOSE for your larger tables?
Here it comes.
Note: The FSM Values are 1000/1000000 & Vaccum mem increased to 50000.
Seems to be faster now.
_____________Vacuum output for larger tables:_____________________________
# VACUUM VERBOSE user_log;
NOTICE: --Relation user_log--
NOTICE: Pages 232907: Changed 0, Empty 0; Tup 2841267: Vac 0, Keep 0,
UnUsed 18478387
.
Total CPU 17.89s/1.38u sec elapsed 386.31 sec.
VACUUM
# VACUUM VERBOSE fullstatistic;
NOTICE: --Relation fullstatistic--
NOTICE: Index fullstatistic_day_pleid_preid_i: Pages 9631; Tuples 91227:
Deleted 8761
1.
CPU 0.42s/0.64u sec elapsed 55.21 sec.
NOTICE: Removed 87611 tuples in 2382 pages.
CPU 0.03s/0.31u sec elapsed 8.02 sec.
NOTICE: Pages 118815: Changed 895, Empty 0; Tup 90646: Vac 87611, Keep 167,
UnUsed 87
77533.
Total CPU 9.15s/1.76u sec elapsed 321.46 sec.
VACUUM
__________________________________________________________________
Are there other drawbacks but wasted diskspace?
Could you be more specific about the term 'painful'? *fear!*
VACUUM FULL will completely lock the tables?
Thank you
Thilo