On Mon, Apr 28, 2008 at 9:38 AM, Gauri Kanekar
<meetgaurikanekar@gmail.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

--
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:
> 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@acm.org> wrote:

> meetgaurikanekar@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



--
Regards
Gauri

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@gregsmith.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@gregsmith.com http://www.gregsmith.com Baltimore, MD
>



--
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@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

"Gauri Kanekar" <meetgaurikanekar@gmail.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

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

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@commandprompt.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@commandprompt.com
> +1 503 667 4564 x104
> http://www.commandprompt.com/
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.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@gmail.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

--
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 11:16 AM, Gauri Kanekar
<meetgaurikanekar@gmail.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

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

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