pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: Best practice - Vacuum. Replication suggestions and pg vs mysql question
Date: Thu, 17 Mar 2005 17:19:14 -0500
Message-ID: <m3k6o5ex0d.fsf@knuth.knuth.cbbrowne.com> (raw)
References: <c76ea21c05031712092663f1e8@mail.gmail.com>

Martha Stewart called it a Good Thing when davidb999@gmail.com (David B) wrote:
> Environment. PG v8. (Opteron 2CPU. Raid 5 disks 1TB. 12GB Ram)
> Environment would be one master feeding 3 slaves. Similar configs.
> New transactions coming into master. Cust Service Reps using that box. 
> Analysis being done on slave boxes.
>
>
> Hi Folks,
>
> I'm looking at PG vs MySql for a high volume site.
> Few 10's of millions inserts per day.
> Also some deletes (1% - say 250,000 per day).
> Also updates (perhaps 10% say 2.5m per day)
> Lots of indexes on master table.
>
> When I test vacuum it seems very slow.
> I understand I need to do vacuums on a regular basis. 
> Two reasons right...1. space and 2. better performance.
>
> #1
> I'm thinking with that much activity I should do it nightly and not
> wait for Saturday nights since the #rows affected will be very large
> by that time. Any thoughts?

We have cron jobs that run every few minutes to vacuum certain tables
that are highly updated.

I'd certainly suggest vacuuming more than once a week, particularly in
view that this doesn't lock usual operations.

> #2
> How does vacuum work in a replication environment. 
> If I implement replication so that users can get to 'copy' for their
> analysis I presume the same overhead will apply to replicated box?
> Does the same lock on table on slave box apply?

Yes, "slave" servers will need similar vacuum regimens.


> Will delay hit other tables not being vacuumed...I'd suspect it would.
> E.g. I vacuum main table and it takes 15minutes. Are inserts to other
> tables delayed by the 15minutes the vacuum takes on slave?

That's only the case if you're abusively using VACUUM FULL all the
time, which is unnecessary.

You normally only need to use VACUUM, which doesn't block normal
database operations.

> #3
> 15minute lock is a long time.
> I don't want to tell the CSR's they will have to put customers on hold
> each night for 15mins if customer updates are the basis of their call.
> Any typical workarounds for such a problem?

Um, we run systems 24x7, and VACUUMs _don't_ lead to any such
problems.

If you are having problems of "everything hanging up" for 15 minutes,
you're presumably doing a VACUUM FULL which is the WRONG thing to do.

> #4
> Does Mysql have a similar issue. Does it use something like 'vacuum'.
> And if so does it suffer the same delays. Any thoughts?

MySQL(tm) doesn't have similar functionality, so it doesn't have
similar issues.  It tends to suffer much worse if you hit it with a
lot of concurrent load, but that's a long story...

> #5
> Speaking of replication...I see lots of technologies I can use. 
> Slony appears very active group. Which can be a good thing or mean its
> still a work in progress!!
> Any pro's con's or suggestions on the most popular.

Slony-I is certainly seeing active efforts; we're preparing to get a
1.1 release ready, which will be, in some ways, a fair bit better than
1.0.5, which people were already pretty happy with.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/linuxdistributions.html
Rules of the Evil Overlord  #142. "If I have children and subsequently
grandchildren, I will keep  my three-year-old granddaughter near me at
all times. When  the hero enters to  kill me, I will ask  him to first
explain to her  why it is necessary to kill  her beloved grandpa. When
the hero launches  into an explanation of morality  way over her head,
that will be  her cue to pull the  lever and send him into  the pit of
crocodiles. After  all, small children like crocodiles  almost as much
as Evil Overlords  and it's important to spend  quality time with your
grandkids. <http://www.eviloverlord.com/;



view thread (5+ messages)  latest in thread

Message-ID: <m3k6o5ex0d.fsf@knuth.knuth.cbbrowne.com>
Permalink:  ../m3k6o5ex0d.fsf@knuth.knuth.cbbrowne.com/
Also on:    postgresql.org/message-id/m3k6o5ex0d.fsf@knuth.knuth.cbbrowne.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: Best practice - Vacuum. Replication suggestions and pg vs mysql question
  In-Reply-To: <m3k6o5ex0d.fsf@knuth.knuth.cbbrowne.com>
  References: <c76ea21c05031712092663f1e8@mail.gmail.com> <m3k6o5ex0d.fsf@knuth.knuth.cbbrowne.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox