From most of the reply found that upgrade to higher version of postgres may
be to 8.3.1 may be one of the solution to tackle this problem

Checked about HOT feature in 8.3.1.

Do we need to do any special config changes or any other setting for HOT to
work??

Any special guideline to follow to make HOT working??

~ Gauri

On Tue, Apr 29, 2008 at 2:07 PM, Greg Smith <gsmith@gregsmith.com> wrote:

> On Tue, 29 Apr 2008, Gauri Kanekar wrote:
>
> We do vacuum full, as vacuum verbose analyse dont regain space for us.
> >
>
> Ah, now we're getting to the root of your problem here. You expect that
> VACUUM should reclaim space.
>
> Whenever you UPDATE a row, it writes a new one out, then switches to use
> that version. This leaves behind the original. Those now unused rows are
> what VACUUM gathers, but it doesn't give that space back to the operating
> system.
>
> The model here assumes that you'll need that space again for the next time
> you UPDATE or INSERT a row. So instead VACUUM just keeps those available
> for database reuse rather than returning it to the operating system.
>
> Now, if you don't VACUUM frequently enough, this model breaks down, and
> the table can get bigger with space that may never get reused. The idea is
> that you should be VACUUMing up now unneeded rows at about the same rate
> they're being re-used. When you don't keep up, the database can expand in
> space that you don't get back again. The right answer to this problem is
> not to use VACUUM FULL; it's to use regular VACUUM more often.
>
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>



--
Regards
Gauri

Gauri Kanekar wrote:
> Andrew,
>
> Can you explain me in detail why u said vacuum full is making the things
> worst.
> We do vacuum full, as vacuum verbose analyse dont regain space for us.
>

vacuum full stops all access so that the data files can be re-writen
without the unused space.

normal vacuum will update the records of what space is no longer used so
that it can then be reused with the next update/insert. Your db size
will not shrink straight away but it will stop growing until you use all
the free space left from previous update/delete

The more frequently you do a normal vacuum the less time it will take
and things will run a lot smoother with your file size growing slowly to
accommodate new data.

Expanding on what others have mentioned as a drawback of vacuum full -
you should look at REINDEX'ing as well (maybe one index or table at a
time). You will most likely find this will reclaim some disk space for
you as well.




--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar
<meetgaurikanekar@gmail.com> wrote:

>
> Do we need to do any special config changes or any other setting for HOT to
> work??

No. HOT is enabled by default, on all tables. There is no way and need
to disable it.

>
> Any special guideline to follow to make HOT working??
>

You can do couple of things to benefit from HOT.

1. HOT addresses a special, but common case where UPDATE operation
does not change any of the index keys. So check if your UPDATE changes
any of the index keys. If so, see if you can avoid having index
involving that column. Of course, I won't advocate dropping an index
if it would drastically impact your frequently run queries.

2. You may leave some free space in the heap (fillfactor less than
100). My recommendation would be to leave space worth of one row or
slightly more than that to let first UPDATE be an HOT update.
Subsequent UPDATEs in the page may reuse the dead row created by
earlier UPDATEs.

3. Avoid any long running transactions.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Thanx for the help.

Need some more help.

"table1" has two indices
unique indx1 = "pkfld"
unique indx2 = "fkfld1,fkfld2"

did following steps in the listed order -

1. vacuumed the whole DB
2. "table1"
RecCnt ==> 11970789
Size ==> 2702.41 MB
3.update "table1" set fld7 = 1000 where fld1/1000000 = 999 ;
this UPDATED 1230307 records
4. checked "table1" size again
Reccnt => 11970789
Size ==> 2996.57MB
5. Again did the update, update "table1" set fld7 = 1000 where fld1/1000000
= 999 ;
this UPDATED 1230307 records
6. Got "table1" size as
RecCnt ==> 11970789
Size ==> 3290.64
7. Updated again, update "table1" set fld7 = 1000 where fld1/1000000 = 999 ;
this UPDATED 1230307 records
6. "table1" size as
RecCnt ==> 11970789
Size ==> 3584.66

Found that the size increased gradually. Is HOT working over here ??
Guide me if im doing something wrong.

~ Gauri

On Tue, Apr 29, 2008 at 4:55 PM, Pavan Deolasee <pavan.deolasee@gmail.com>
wrote:

> On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar
> <meetgaurikanekar@gmail.com> wrote:
>
> >
> > Do we need to do any special config changes or any other setting for HOT
> to
> > work??
>
> No. HOT is enabled by default, on all tables. There is no way and need
> to disable it.
>
> >
> > Any special guideline to follow to make HOT working??
> >
>
> You can do couple of things to benefit from HOT.
>
> 1. HOT addresses a special, but common case where UPDATE operation
> does not change any of the index keys. So check if your UPDATE changes
> any of the index keys. If so, see if you can avoid having index
> involving that column. Of course, I won't advocate dropping an index
> if it would drastically impact your frequently run queries.
>
> 2. You may leave some free space in the heap (fillfactor less than
> 100). My recommendation would be to leave space worth of one row or
> slightly more than that to let first UPDATE be an HOT update.
> Subsequent UPDATEs in the page may reuse the dead row created by
> earlier UPDATEs.
>
> 3. Avoid any long running transactions.
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> EnterpriseDB http://www.enterprisedb.com
>



--
Regards
Gauri

Thats how our updates works.
We usually tend to touch the same row many times a day.

~ Gauri

On Tue, Apr 29, 2008 at 6:39 PM, Pavan Deolasee <pavan.deolasee@gmail.com>
wrote:

> On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
> <meetgaurikanekar@gmail.com> wrote:
> >
> >
> > Found that the size increased gradually. Is HOT working over here ??
> > Guide me if im doing something wrong.
> >
>
> You have chosen a bad case for HOT. Since you are repeatedly updating
> the same set of rows, the dead space created in the first step is the
> blocks which are not touched in the subsequent updates. Is this a real
> scenario or are you just testing ? If its just for testing, I would
> suggest updating different sets of rows in each step and then check.
>
> Thanks,
> Pavan
>
>
>
> --
> Pavan Deolasee
> EnterpriseDB http://www.enterprisedb.com
>



--
Regards
Gauri

On Tue, Apr 29, 2008 at 6:42 PM, Gauri Kanekar
<meetgaurikanekar@gmail.com> wrote:
> Thats how our updates works.
> We usually tend to touch the same row many times a day.
>

Then start with a non-100 fillfactor. I would suggest something like
80 and then adjust based on the testing. Since you are anyways have a
update intensive setup, leaving free space in the heap won't harm you
much in the long term.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

"Pavan Deolasee" <pavan.deolasee@gmail.com> writes:

>> Any special guideline to follow to make HOT working??
>>
>
> You can do couple of things to benefit from HOT.
>
> 1. HOT addresses a special, but common case where UPDATE operation
> does not change any of the index keys. So check if your UPDATE changes
> any of the index keys. If so, see if you can avoid having index
> involving that column. Of course, I won't advocate dropping an index
> if it would drastically impact your frequently run queries.
>
> 2. You may leave some free space in the heap (fillfactor less than
> 100). My recommendation would be to leave space worth of one row or
> slightly more than that to let first UPDATE be an HOT update.
> Subsequent UPDATEs in the page may reuse the dead row created by
> earlier UPDATEs.
>
> 3. Avoid any long running transactions.

Perhaps we should put this list in the FAQ.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Greg Smith <gsmith@gregsmith.com> writes:
> The model here assumes that you'll need that space again for the next time
> you UPDATE or INSERT a row. So instead VACUUM just keeps those available
> for database reuse rather than returning it to the operating system.

> Now, if you don't VACUUM frequently enough, this model breaks down, and
> the table can get bigger with space that may never get reused. The idea
> is that you should be VACUUMing up now unneeded rows at about the same
> rate they're being re-used. When you don't keep up, the database can
> expand in space that you don't get back again. The right answer to this
> problem is not to use VACUUM FULL; it's to use regular VACUUM more often.

Also, you need to make sure you have the FSM parameters set high enough
so that all the free space found by a VACUUM run can be remembered.

The less often you run VACUUM, the more FSM space you need, because
there'll be more free space reclaimed per run.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

On Apr 29, 2008, at 10:16 AM, Tom Lane wrote:

> Greg Smith <gsmith@gregsmith.com> writes:
>> The model here assumes that you'll need that space again for the
>> next time
>> you UPDATE or INSERT a row. So instead VACUUM just keeps those
>> available
>> for database reuse rather than returning it to the operating system.
[ ... ]
> Also, you need to make sure you have the FSM parameters set high
> enough
> so that all the free space found by a VACUUM run can be remembered.
>
> The less often you run VACUUM, the more FSM space you need, because
> there'll be more free space reclaimed per run.

I can actually watch one of our applications slow down once the free
space in the table is used up. Extending the data file seems to be
much more expensive than using the free space found in existing pages
of the file.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

meetgaurikanekar@gmail.com ("Gauri Kanekar") writes:
> Basically we have some background process which updates "table1" and
> we don't want the application to make any changes to "table1" while
> vacuum. Vacuum requires exclusive lock on "table1" and if any of
> the background or application is ON vacuum don't kick off. Thats the
> reason we need to get the site down.

VACUUM has not required an exclusive lock on tables since version 7.1.

What version of PostgreSQL are you running?
--
output = ("cbbrowne" "@" "acm.org")
http://linuxdatabases.info/info/sap.html
Rules of the Evil Overlord #192. "If I appoint someone as my consort,
I will not subsequently inform her that she is being replaced by a
younger, more attractive woman. <http://www.eviloverlord.com/>