Re: Replication Syatem

All,

We have a table "table1" which get insert and updates daily in high numbers,
bcoz of which its size is increasing and we have to vacuum it every
alternate day. Vacuuming "table1" take almost 30min and during that time the
site is down.

We need to cut down on this downtime.So thought of having a replication
system, for which the replicated DB will be up during the master is getting
vacuumed.

Can anybody guide which will be the best suited replication solution for
this.

Thanx for any help
~ Gauri

2008/4/28 Gauri Kanekar <meetgaurikanekar(at)gmail(dot)com>:

> All,
>
> We have a table "table1" which get insert and updates daily in high
> numbers, bcoz of which its size is increasing and we have to vacuum it every
> alternate day. Vacuuming "table1" take almost 30min and during that time the
> site is down.
>
> We need to cut down on this downtime.So thought of having a replication
> system, for which the replicated DB will be up during the master is getting
> vacuumed.
>
> Can anybody guide which will be the best suited replication solution for
> this.
>
> Thanx for any help
> ~ Gauri
>

I home your not using Vacuum Full....... (Standard Reply for this type of
question)

What version of Postgresql are you using?

Have you tried autovacuum?

Run plain vacuum even more often on this even more often (like ever half
hour) and it should not take as long and save space.

If still have trouble run "vacuum analyse verbose table1;" and see what it
says.

If your doing it right you should be able to vacuum with the database up.

Sounds like you might be happier a fix for the problem rather than a complex
work around which will actually solve a completely different problem.

Regards

Peter.

Peter,

We are doing vacuum full every alternate day. We also do vacuum analyze very
often.
We are currently using 8.1.3 version.
Auto vacuum is already on. But the table1 is so busy that auto vacuum don't
get sufficient chance to vacuum it :(.

Have already tried all the option listed by you, thats y we reached to the
decision of having a replication sytsem. So any suggestion on that :).

Thanx
~ Gauri

On Mon, Apr 28, 2008 at 7:28 PM, Peter Childs <peterachilds(at)gmail(dot)com>
wrote:

>
>
> 2008/4/28 Gauri Kanekar <meetgaurikanekar(at)gmail(dot)com>:
>
> All,
> >
> > We have a table "table1" which get insert and updates daily in high
> > numbers, bcoz of which its size is increasing and we have to vacuum it every
> > alternate day. Vacuuming "table1" take almost 30min and during that time the
> > site is down.
> >
> > We need to cut down on this downtime.So thought of having a replication
> > system, for which the replicated DB will be up during the master is getting
> > vacuumed.
> >
> > Can anybody guide which will be the best suited replication solution for
> > this.
> >
> > Thanx for any help
> > ~ Gauri
> >
>
> I home your not using Vacuum Full....... (Standard Reply for this type of
> question)
>
> What version of Postgresql are you using?
>
> Have you tried autovacuum?
>
> Run plain vacuum even more often on this even more often (like ever half
> hour) and it should not take as long and save space.
>
> If still have trouble run "vacuum analyse verbose table1;" and see what it
> says.
>
> If your doing it right you should be able to vacuum with the database up.
>
> Sounds like you might be happier a fix for the problem rather than a
> complex work around which will actually solve a completely different
> problem.
>
> Regards
>
> Peter.
>

--
Regards
Gauri

On Mon, 2008-04-28 at 19:35 +0530, Gauri Kanekar wrote:
> Peter,
>
> We are doing vacuum full every alternate day. We also do vacuum
> analyze very often.
> We are currently using 8.1.3 version.
> Auto vacuum is already on. But the table1 is so busy that auto vacuum
> don't get sufficient chance to vacuum it :(.

You should seriously consider upgrading to PG 8.3. There have been
substantial improvements to VACUUM since 8.1

Brad.

Gauri Kanekar wrote:
> Peter,
>
> We are doing vacuum full every alternate day. We also do vacuum analyze very
> often.
> We are currently using 8.1.3 version.
> Auto vacuum is already on. But the table1 is so busy that auto vacuum don't
> get sufficient chance to vacuum it :(.
>
> Have already tried all the option listed by you, thats y we reached to the
> decision of having a replication sytsem. So any suggestion on that :).
>
> Thanx
> ~ Gauri
>

We use slony for exactly this type of a situation. It's not the most
user-friendly piece of software, but it works well enough that I can
schedule maintenance windows (we're a 24/7 shop) and do clustering and
other tasks on our DB to reclaim space, etc.

-salman

Thats one of the thingsto be done in near future.
But it need some changes from application point of view. :( ... so just got
escalated for that reason.

But for now, which one will be a well suited replication system ?

~ Gauri

On Mon, Apr 28, 2008 at 7:43 PM, Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
wrote:

>
> On Mon, 2008-04-28 at 19:35 +0530, Gauri Kanekar wrote:
> > Peter,
> >
> > We are doing vacuum full every alternate day. We also do vacuum
> > analyze very often.
> > We are currently using 8.1.3 version.
> > Auto vacuum is already on. But the table1 is so busy that auto vacuum
> > don't get sufficient chance to vacuum it :(.
>
> You should seriously consider upgrading to PG 8.3. There have been
> substantial improvements to VACUUM since 8.1
>
> Brad.
>
>

--
Regards
Gauri

Salman,

Slony don't do automatic failover. And we would appreciate a system with
automatic failover :(

~ Gauri

On Mon, Apr 28, 2008 at 7:46 PM, salman <salmanb(at)quietcaresystems(dot)com>
wrote:

> Gauri Kanekar wrote:
>
> > Peter,
> >
> > We are doing vacuum full every alternate day. We also do vacuum analyze
> > very
> > often.
> > We are currently using 8.1.3 version.
> > Auto vacuum is already on. But the table1 is so busy that auto vacuum
> > don't
> > get sufficient chance to vacuum it :(.
> >
> > Have already tried all the option listed by you, thats y we reached to
> > the
> > decision of having a replication sytsem. So any suggestion on that :).
> >
> > Thanx
> > ~ Gauri
> >
> >
> We use slony for exactly this type of a situation. It's not the most
> user-friendly piece of software, but it works well enough that I can
> schedule maintenance windows (we're a 24/7 shop) and do clustering and other
> tasks on our DB to reclaim space, etc.
>
> -salman
>

--
Regards
Gauri

On Mon, Apr 28, 2008 at 07:35:37PM +0530, Gauri Kanekar wrote:
> Peter,
>
> We are doing vacuum full every alternate day. We also do vacuum analyze very
> often.

VACUUM FULL is making your problem worse, not better. Don't do that.

> We are currently using 8.1.3 version.

You need immediately to upgrade to the latest 8.1 stability and
security release, which is 8.1.11. This is a drop-in replacement.
It's an urgent fix for your case.

> Auto vacuum is already on. But the table1 is so busy that auto vacuum don't
> get sufficient chance to vacuum it :(.

You probably need to tune autovacuum not to do that table, and just
vacuum that table in a constant loop or something. VACUUM should
_never_ "take the site down". If it does, you're doing it wrong.

> Have already tried all the option listed by you, thats y we reached to the
> decision of having a replication sytsem. So any suggestion on that :).

I think you will find that no replication system will solve your
underlying problems. That said, I happen to work for a company that
will sell you a replication system to work with 8.1 if you really want
it.

A

--
Andrew Sullivan
ajs(at)commandprompt(dot)com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Mon, Apr 28, 2008 at 07:48:48PM +0530, Gauri Kanekar wrote:

> Slony don't do automatic failover. And we would appreciate a system with
> automatic failover :(

No responsible asynchronous system will give you automatic failover.
You can lose data that way.

A

--
Andrew Sullivan
ajs(at)commandprompt(dot)com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Mon, 28 Apr 2008, Gauri Kanekar wrote:

> We are doing vacuum full every alternate day. We also do vacuum analyze
> very often. We are currently using 8.1.3 version...Have already tried
> all the option listed by you, thats y we reached to the decision of
> having a replication sytsem.

Andrew Sullivan has already given a response here I agree with, I wanted
to expland on that. You have a VACUUM problem. The fact that you need
(or feel you need) to VACUUM FULL every other day says there's something
very wrong here. The way to solve most VACUUM problems is to VACUUM more
often, so that the work in each individual one never gets so big that your
system takes an unnaceptable hit, and you shouldn't ever need VACUUM FULL.
Since your problem is being aggrevated because you're running a
dangerously obsolete version, that's one of the first things you should
fix--to at least the latest 8.1 if you can't deal with a larger version
migration. The fact that you're happily running 8.1.3 says you most
certainly haven't tried all the other options here.

Every minute you spend looking into a replication system is wasted time
you could be spending on the right fix here. You've fallen into the
common trap where you're fixated on a particular technical solution so
much that you're now ignoring suggestions on how to resolve the root
problem. Replication is hard to get going even on a system that works
perfectly, and replicating a known buggy system just to work around a
problem really sounds like a bad choice.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Mon, Apr 28, 2008 at 9:38 AM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)com> wrote:
> All,
>
> We have a table "table1" which get insert and updates daily in high numbers,
> bcoz of which its size is increasing and we have to vacuum it every
> alternate day. Vacuuming "table1" take almost 30min and during that time the
> site is down.

Slony is an open source replication system built for Postgres.
But the real problem is that you are doing a vaccum full every day.
This is highly invasive.
Take a look at the postgres docs on Vacuuming the db. Analyze is best
on a daily basis. If you have a lot of deletes, then try vacuum
truncate.

The postgres documentation describes the various vaccuum options and
explains the merits of each.

Hope that helps.
Radhika

--
It is all a matter of perspective. You choose your view by choosing
where to stand. --Larry Wall

meetgaurikanekar(at)gmail(dot)com ("Gauri Kanekar") writes:
> We have a table "table1" which get insert and updates daily in high
> numbers, bcoz of which its size is increasing and we have to vacuum
> it every alternate day. Vacuuming "table1" take almost 30min and
> during that time the site is down. We need to cut down on this
> downtime.So thought of having a replication system, for which the
> replicated DB will be up during the master is getting vacuumed. Can
> anybody guide which will be the best suited replication solution for
> this.

The only reason that it would be necessary for VACUUM to "take the
site down" would be if you are running version 7.1, which was
obsoleted in 2002, which, it should be noted, was SIX YEARS AGO.

As has been noted, you seem to be presupposing a remarkably complex
solution to resolve a problem which is likely to be better handled via
running VACUUM rather more frequently.
--
output = reverse("ofni.sesabatadxunil" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/postgresql.html
Rules of the Evil Overlord #181. "I will decree that all hay be
shipped in tightly-packed bales. Any wagonload of loose hay attempting
to pass through a checkpoint will be set on fire."
<http://www.eviloverlord.com/>

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.

~ Gauri

On Tue, Apr 29, 2008 at 3:13 AM, Chris Browne <cbbrowne(at)acm(dot)org> wrote:

> meetgaurikanekar(at)gmail(dot)com ("Gauri Kanekar") writes:
> > We have a table "table1" which get insert and updates daily in high
> > numbers, bcoz of which its size is increasing and we have to vacuum
> > it every alternate day. Vacuuming "table1" take almost 30min and
> > during that time the site is down. We need to cut down on this
> > downtime.So thought of having a replication system, for which the
> > replicated DB will be up during the master is getting vacuumed. Can
> > anybody guide which will be the best suited replication solution for
> > this.
>
> The only reason that it would be necessary for VACUUM to "take the
> site down" would be if you are running version 7.1, which was
> obsoleted in 2002, which, it should be noted, was SIX YEARS AGO.
>
> As has been noted, you seem to be presupposing a remarkably complex
> solution to resolve a problem which is likely to be better handled via
> running VACUUM rather more frequently.
> --
> output = reverse("ofni.sesabatadxunil" "@" "enworbbc")
> http://www3.sympatico.ca/cbbrowne/postgresql.html
> Rules of the Evil Overlord #181. "I will decree that all hay be
> shipped in tightly-packed bales. Any wagonload of loose hay attempting
> to pass through a checkpoint will be set on fire."
> <http://www.eviloverlord.com/>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Regards
Gauri

On Tue, 29 Apr 2008, Gauri Kanekar wrote:

> 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.

VACUUM FULL needs an exclusive lock, the regular one does not in 8.1.
It's one of the reasons FULL should be avoided. If you do regular VACUUM
frequently enough, you shouldn't ever need to do a FULL one anyway.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

But unless we do full vacuum the space is not recovered. Thats y we prefer
full vacuum.

~ Gauri

On Tue, Apr 29, 2008 at 10:38 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:

> On Tue, 29 Apr 2008, Gauri Kanekar wrote:
>
> 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.
> >
>
> VACUUM FULL needs an exclusive lock, the regular one does not in 8.1. It's
> one of the reasons FULL should be avoided. If you do regular VACUUM
> frequently enough, you shouldn't ever need to do a FULL one anyway.
>
>
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>

--
Regards
Gauri

"Gauri Kanekar" <meetgaurikanekar(at)gmail(dot)com> writes:
> 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.

As has been pointed out to you repeatedly, "vacuum" hasn't required
exclusive lock since the stone age. If you are actually running a PG
version in which plain "vacuum" takes exclusive lock, then no amount
of replication will save you --- in particular, because no currently
supported replication solution even works with PG servers that old.
Otherwise, the answer is not so much "replicate" as "stop using
vacuum full, and instead adopt a modern vacuuming strategy".

I am not sure how much more clear we can make this to you.
Replication isn't going to solve your vacuum mismanagement problem.

regards, tom lane

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.

~ Gauri

On Mon, Apr 28, 2008 at 9:52 PM, Andrew Sullivan <ajs(at)commandprompt(dot)com>
wrote:

> On Mon, Apr 28, 2008 at 07:35:37PM +0530, Gauri Kanekar wrote:
> > Peter,
> >
> > We are doing vacuum full every alternate day. We also do vacuum analyze
> very
> > often.
>
> VACUUM FULL is making your problem worse, not better. Don't do that.
>
> > We are currently using 8.1.3 version.
>
> You need immediately to upgrade to the latest 8.1 stability and
> security release, which is 8.1.11. This is a drop-in replacement.
> It's an urgent fix for your case.
>
> > Auto vacuum is already on. But the table1 is so busy that auto vacuum
> don't
> > get sufficient chance to vacuum it :(.
>
> You probably need to tune autovacuum not to do that table, and just
> vacuum that table in a constant loop or something. VACUUM should
> _never_ "take the site down". If it does, you're doing it wrong.
>
> > Have already tried all the option listed by you, thats y we reached to
> the
> > decision of having a replication sytsem. So any suggestion on that :).
>
> I think you will find that no replication system will solve your
> underlying problems. That said, I happen to work for a company that
> will sell you a replication system to work with 8.1 if you really want
> it.
>
> A
>
>
> --
> Andrew Sullivan
> ajs(at)commandprompt(dot)com
> +1 503 667 4564 x104
> http://www.commandprompt.com/
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Regards
Gauri

On Tue, Apr 29, 2008 at 10:41 AM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)com> wrote:
> But unless we do full vacuum the space is not recovered. Thats y we prefer
> full vacuum.

There is no point in recovering the space by moving tuples and
truncating the relation (that's what VACUUM FULL does) because you are
doing frequent updates on the table and that would again extend the
relation. If you run plain VACUUM, that would recover dead space and
update the free space maps. It may not be able to reduce the table
size, but you should not be bothered much about it because the
following updates/inserts will fill in the fragmented free space.

You may want to check your FSM settings as well to make sure that you
are tracking free space properly.

Thanks,
Pavan

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

On Tue, Apr 29, 2008 at 11:16 AM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)com> wrote:
> Andrew,
>
> Can you explain me in detail why u said vacuum full is making the things
> worst.

1. VACUUM FULL takes exclusive lock on the table. That makes table
unavailable for read/writes.

2. VACUUM FULL moves live tuples around. When a tuple is moved, the
old index entry is deleted and a new index entry is inserted. This
causes index bloats which are hard to recover.

> We do vacuum full, as vacuum verbose analyse dont regain space for us.
>

As I mentioned in the other reply, you are not gaining much by
regaining space. The subsequent UPDATEs/INSERTs will quickly extend
the relation and you loose all the work done by VACUUM FULL. Plain
VACUUM will update FSM to track the free space scattered across the
relation which is later reused by updates/inserts.

Thanks,
Pavan

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

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

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(at)gregsmith(dot)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(at)gregsmith(dot)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

On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)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

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(dot)deolasee(at)gmail(dot)com>
wrote:

> On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar
> <meetgaurikanekar(at)gmail(dot)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

Gauri Kanekar escribió:

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

No. HOT is always working, if it can. You don't need to configure it.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Gauri Kanekar escribió:

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

Probably not. Try vacuuming between the updates.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)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

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(dot)deolasee(at)gmail(dot)com>
wrote:

> On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
> <meetgaurikanekar(at)gmail(dot)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(at)gmail(dot)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

"Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)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!

Greg Smith <gsmith(at)gregsmith(dot)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

meetgaurikanekar(at)gmail(dot)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/>

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

> Greg Smith <gsmith(at)gregsmith(dot)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.

Alvaro Herrera wrote:
> Gauri Kanekar escribió:
>
>> Do we need to do any special config changes or any other setting for HOT to
>> work??
>
> No. HOT is always working, if it can. You don't need to configure it.
>

Unless you have upgraded since you started this thread you are still
running 8.1.3.

HOT is only available in 8.3 and 8.3.1

You DO need to upgrade to get the benefits of HOT

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

HOT doesn't seems to be working in our case.

This is "table1" structure :
id integer not null
code integer not null
crid integer not null
status character varying(1) default 'A'::character varying
delta1 bigint default 0
delta2 bigint default 0
delta3 bigint default 0
delta4 bigint default 0
tz_id integer default 0
Indexes:
"idx1" PRIMARY KEY, btree (id)
"idx2" UNIQUE, btree (code, crid)
"idx3" btree (tz_id)
"idx4" btree (status)

code as crid are foreign key.

Here delta* fields get updated through out the day. and most of the time it
may update the same row again n again.

table1 contains around 12843694 records.

Now not understanding y HOT don't work in our case.

Changed fillfactor to 80, 75,70.... but nothing seems to work.

~Gauri
On Tue, Apr 29, 2008 at 10:18 PM, Shane Ambler <pgsql(at)sheeky(dot)biz> wrote:

> Alvaro Herrera wrote:
>
> > Gauri Kanekar escribió:
> >
> > Do we need to do any special config changes or any other setting for
> > > HOT to
> > > work??
> > >
> >
> > No. HOT is always working, if it can. You don't need to configure it.
> >
> >
> Unless you have upgraded since you started this thread you are still
> running 8.1.3.
>
> HOT is only available in 8.3 and 8.3.1
>
> You DO need to upgrade to get the benefits of HOT
>
>
>
>
> --
>
> Shane Ambler
> pgSQL (at) Sheeky (dot) Biz
>
> Get Sheeky @ http://Sheeky.Biz
>

--
Regards
Gauri

On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)com> wrote:
> HOT doesn't seems to be working in our case.
>

Can you please post output of the following query ?

SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_dead_tup
from pg_stat_user_tables WHERE relname = 'table1';

Thanks,
Pavan

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

relid | relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
-------+----------------+-----------+-----------+---------------+------------
16461 | table1 | 0 | 8352496 | 5389 | 8351242

On Wed, Apr 30, 2008 at 11:07 AM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
wrote:

> On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar
> <meetgaurikanekar(at)gmail(dot)com> wrote:
> > HOT doesn't seems to be working in our case.
> >
>
> Can you please post output of the following query ?
>
> SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_dead_tup
> from pg_stat_user_tables WHERE relname = 'table1';
>
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> EnterpriseDB http://www.enterprisedb.com
>

--
Regards
Gauri

On Wed, Apr 30, 2008 at 11:09 AM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)com> wrote:
> relid | relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
> -------+----------------+-----------+-----------+---------------+------------
> 16461 | table1 | 0 | 8352496 | 5389 | 8351242
>

Hmm.. So indeed there are very few HOT updates. What is the fillfactor
you are using for these tests ? If its much less than 100, the very
low percentage of HOT updates would make me guess that you are
updating one of the index columns. Otherwise at least the initial
updates until you fill up the free space should be HOT.

Thanks,
Pavan

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

"table1" structure :
id integer not null
code integer not null
crid integer not null
status character varying(1) default 'A'::character varying
delta1 bigint default 0
delta2 bigint default 0
delta3 bigint default 0
delta4 bigint default 0
tz_id integer default 0
Indexes:
"idx1" PRIMARY KEY, btree (id)
"idx2" UNIQUE, btree (code, crid)
"idx3" btree (tz_id)
"idx4" btree (status)

code as crid are foreign key.

update table1 set delta1 = 100 where code/1000000 =999;

On Wed, Apr 30, 2008 at 12:16 PM, Gauri Kanekar <meetgaurikanekar(at)gmail(dot)com>
wrote:

> fillfactor is set to 80 as you suggested.
> delta* fields r updated and these fields are no where related to any of
> the index fields.
>
>
>
> On Wed, Apr 30, 2008 at 12:13 PM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
> wrote:
>
> > On Wed, Apr 30, 2008 at 11:09 AM, Gauri Kanekar
> > <meetgaurikanekar(at)gmail(dot)com> wrote:
> > > relid | relname | n_tup_ins | n_tup_upd | n_tup_hot_upd |
> > n_dead_tup
> > >
> > -------+----------------+-----------+-----------+---------------+------------
> > > 16461 | table1 | 0 | 8352496 | 5389 | 8351242
> > >
> >
> > Hmm.. So indeed there are very few HOT updates. What is the fillfactor
> > you are using for these tests ? If its much less than 100, the very
> > low percentage of HOT updates would make me guess that you are
> > updating one of the index columns. Otherwise at least the initial
> > updates until you fill up the free space should be HOT.
> >
> > Thanks,
> > Pavan
> >
> >
> > --
> > Pavan Deolasee
> > EnterpriseDB http://www.enterprisedb.com
> >
>
>
>
> --
> Regards
> Gauri

--
Regards
Gauri

On Wed, Apr 30, 2008 at 12:16 PM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)com> wrote:
> fillfactor is set to 80 as you suggested.
> delta* fields r updated and these fields are no where related to any of the
> index fields.
>

That's weird. With that fillfactor, you should have a very high
percentage of HOT update ratio. It could be a very special case that
we might be looking at. I think a self contained test case or a very
detail explanation of the exact usage is what we need to explain this
behavior. You may also try dropping non-critical indexes and test
again.

Btw, I haven't been able to reproduce this at my end. With the given
indexes and kind of updates, I get very high percentage of HOT
updates.

Thanks,
Pavan

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

Gauri Kanekar wrote:
> HOT doesn't seems to be working in our case.
>
> This is "table1" structure :
> id integer not null
> code integer not null
> crid integer not null
> status character varying(1) default 'A'::character varying
> delta1 bigint default 0
> delta2 bigint default 0
> delta3 bigint default 0
> delta4 bigint default 0
> tz_id integer default 0
> Indexes:
> "idx1" PRIMARY KEY, btree (id)
> "idx2" UNIQUE, btree (code, crid)
> "idx3" btree (tz_id)
> "idx4" btree (status)
>
> code as crid are foreign key.
>
> Here delta* fields get updated through out the day. and most of the time it
> may update the same row again n again.
>
> table1 contains around 12843694 records.
>
> Now not understanding y HOT don't work in our case.
>
> Changed fillfactor to 80, 75,70.... but nothing seems to work.

Did you dump and reload the table after setting the fill factor? It only
affects newly inserted data.

Another possibility is that there's a long running transaction in the
background, preventing HOT/vacuum from reclaiming the dead tuples.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas wrote:

> Did you dump and reload the table after setting the fill factor? It only
> affects newly inserted data.

VACUUM FULL or CLUSTER should do the job too, right? After all, they
recreate the table so they must take the fillfactor into account.

--
Craig Ringer

Craig Ringer wrote:
> Heikki Linnakangas wrote:
>
>> Did you dump and reload the table after setting the fill factor? It
>> only affects newly inserted data.
>
> VACUUM FULL or CLUSTER should do the job too, right? After all, they
> recreate the table so they must take the fillfactor into account.

CLUSTER, yes. VACUUM FULL won't move tuples around just to make room for
the fillfactor.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Please keep list in the loop.

On Wed, Apr 30, 2008 at 6:45 PM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)com> wrote:
> Hi,
> We have recreated the indices with fillfactor set to 80, which has improved HOT
> a little,

Wait. Did you say, you recreated the indexes with fill factor ? That's
no help for HOT. You need to recreate the TABLEs with a fill factor.
And as Heikki pointed out, you need to dump and reload, just altering
the table won't affect the current data.

Thanks,
Pavan

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

"Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com> writes:
> That's weird. With that fillfactor, you should have a very high
> percentage of HOT update ratio. It could be a very special case that
> we might be looking at.

He's testing

>> update table1 set delta1 = 100 where code/1000000 =999;

so all the rows being updated fall into a contiguous range of "code"
values. If the table was loaded in such a way that those rows were
also physically contiguous, then the updates would be localized and
would very soon run out of freespace on those pages.

regards, tom lane

fche(at)redhat(dot)com (Frank Ch. Eigler) writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> 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.

> Would it be difficult to arrange FSM parameters to be automatically
> set from the VACUUM reclaim results?

Yeah, because the problem is that FSM is kept in shared memory which
cannot be resized on-the-fly.

In retrospect, trying to keep FSM in shared memory was a spectacularly
bad idea (one for which I take full blame). There is work afoot to
push it out to disk so that the whole problem goes away; so I don't see
much point in worrying about band-aid solutions.

regards, tom lane

On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com> writes:
> > That's weird. With that fillfactor, you should have a very high
> > percentage of HOT update ratio. It could be a very special case that
> > we might be looking at.
>
> He's testing
>

It's "She" :-)

Oh yes. Apologies if I sounded harsh; did not mean that. I was just
completely confused why she is not seeing the HOT updates.

> >> update table1 set delta1 = 100 where code/1000000 =999;
>
> so all the rows being updated fall into a contiguous range of "code"
> values. If the table was loaded in such a way that those rows were
> also physically contiguous, then the updates would be localized and
> would very soon run out of freespace on those pages.
>

Yeah, that seems like the pattern. I tested with the similar layout
and a fill factor 80. The initial few bulk updates had comparatively
less HOT updates (somewhere 20-25%), But within 4-5 iterations of
updating the same set of rows, HOT updates were 90-95%. That's because
after few iterations (and because of non-HOT updates) the tuples get
scattered in various blocks, thus improving chances of HOT updates.

I guess the reason probably is that she is using fill factor for
indexes and not heap, but she hasn't yet confirmed.

Thanks,
Pavan

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

We have tried fillfactor for indices and it seems to work.
Need to try fillfactor for table. May for that reason the bulk update
queries don't get the advantage of HOT
:)

On Wed, Apr 30, 2008 at 9:45 PM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
wrote:

> On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com> writes:
> > > That's weird. With that fillfactor, you should have a very high
> > > percentage of HOT update ratio. It could be a very special case that
> > > we might be looking at.
> >
> > He's testing
> >
>
> It's "She" :-)
>
> Oh yes. Apologies if I sounded harsh; did not mean that. I was just
> completely confused why she is not seeing the HOT updates.
>
> > >> update table1 set delta1 = 100 where code/1000000 =999;
> >
> > so all the rows being updated fall into a contiguous range of "code"
> > values. If the table was loaded in such a way that those rows were
> > also physically contiguous, then the updates would be localized and
> > would very soon run out of freespace on those pages.
> >
>
> Yeah, that seems like the pattern. I tested with the similar layout
> and a fill factor 80. The initial few bulk updates had comparatively
> less HOT updates (somewhere 20-25%), But within 4-5 iterations of
> updating the same set of rows, HOT updates were 90-95%. That's because
> after few iterations (and because of non-HOT updates) the tuples get
> scattered in various blocks, thus improving chances of HOT updates.
>
> I guess the reason probably is that she is using fill factor for
> indexes and not heap, but she hasn't yet confirmed.
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> EnterpriseDB http://www.enterprisedb.com
>

--
Regards
Gauri