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

From: Kristo Kaiv <kristo(dot)kaiv(at)skype(dot)net>
To: Bastian Voigt <post(at)bastian-voigt(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))
Date: 2007-05-25 12:57:23
Message-ID: AA61A5EE-3AF2-4902-9C49-9A8A7A136066@skype.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Steinar H. Gunderson 2007-05-25 13:03:26 Re: general PG network slowness (possible cure) (repost)
Previous Message Bastian Voigt 2007-05-25 12:30:36 My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))