Re: Best practice - Vacuum. Replication suggestions and pg vs mysql question - Mailing list pgsql-admin

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