Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))

you should first cluster the table on primary key.
The table is probably already bloated from the 3 hr delay it had before.
First
CLUSTER "primary key index name" ON group_fin_account_tst;
Then
vacuum it every 3 minutes.
NB! clustering takes an access exclusive lock on table

Kristo

On 25.05.2007, at 15:30, Bastian Voigt wrote:

> No, this did not help. The vacuum process is still running far too
> long and makes everything slow. It is even worse than before, cause
> now the system is slow almost all the time while when vacuuming
> only every 3 hours it is only slow once every three hours.....
>
>
> I now did the following. Well, no comment.....
>
>
> Shellscript A:
>
> while true
> do
> psql -U $user -d $database -c "vacuum analyze verbose binary_cache"
> echo "Going to sleep"
> sleep 60
> done
>
>
> Shellscript B:
>
> while true
> do
> ps aux > $tempfile
> numwaiting=`grep UPDATE.waiting $tempfile | grep -c -v grep`
> echo "Number of waiting updates: $numwaiting"
>
> vacuumpid=`grep VACUUM $tempfile| grep -v grep | awk '{print
> $2}'`
> echo "PID of vacuum process: $vacuumpid"
>
> if [ $numwaiting -gt 5 ]
> then
> echo "Too many waiting transactions, killing vacuum
> process $vacuumpid..."
> kill $vacuumpid
> fi
> echo "Sleeping 30 Seconds"
> sleep 30
> done
>
> --
> Bastian Voigt
> Neumünstersche Straße 4
> 20251 Hamburg
> telefon +49 - 40 - 67957171
> mobil +49 - 179 - 4826359
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings