Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group