SEO

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@gmail.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@gmail.com>
wrote:

>
>
> 2008/4/28 Gauri Kanekar <meetgaurikanekar@gmail.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.


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

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

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@ca.afilias.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@quietcaresystems.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@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

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

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