Re: No flamefest please, MySQL vs. PostgreSQL AGAIN

From: Will LaShell <will(at)lashell(dot)net>
To: Michael A Nachbaur <mike(at)nachbaur(dot)com>
Cc: PostgreSQL Admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: No flamefest please, MySQL vs. PostgreSQL AGAIN
Date: 2003-05-13 21:20:13
Message-ID: 1052860814.10316.21.camel@lyric.ofsloans.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Tue, 2003-05-13 at 12:32, Michael A Nachbaur wrote:
> On Tuesday 13 May 2003 11:36 am, Will LaShell wrote:
> > On Tue, 2003-05-13 at 05:08, Andrew Sullivan wrote:
> > > On Mon, May 12, 2003 at 02:21:21PM -0400, Robert Treat wrote:
> > > > On Mon, 2003-05-12 at 10:32, Tom Lane wrote:
> > > > > in 7.4 either. Possibly 7.5. In the meantime, third-party solutions
> > > > > are still your only option, and PostgreSQL Inc's one is probably the
> > > > > best.
> > > > I wouldn't say they are your only options. there is the rserv code in
> > > > contrib which I've seen people post they have gotten working. There is
> > > The contrib/rserv code does indeed work for some people, and it is
> > > useful. It is nowhere close to handling large volumes, but for fewer
> > > than a few thousand writes an hour, it seems to be good.
> >
> > I'd like to put my two cents in on this one. We use rserv on our
> > cluster here, and we do a few hundred writes every 2 minutes. The
> > biggest thing that will cause slowdowns with rserv is not indexing the
> > replication id field. If there is an index for that it should work
> > fine.
>
> I tried rserv with a database that has over 1000 inserts per minute, and it
> would just sit there for days at the "Preparing snapshot" (on a
> Dual-Xeon/2GHz). I hadn't tried indexing the id column though.

heh, yea, we had a similar problem. you should index the replication
id, and make sure the _rserv_log_ table has appropriate indexs on it.
You can enable the logging functions of rserv in one of the perl
modules. Look for DEBUG if I recall correctly. This will happily spam
you with information on what it is doing.

The other thing that should be remembered ( your email didn't mention it
which is why I bring it up ) is that when doing replication your master
server can potentially have double the read access on it during a cycle.
If you don't have a strong disk subsystem you'll send your server and
postgresql into a death spiral.

> > I do have some rudimentary documentation on how we did it all, and I
> > suppose I should really get that sent in.
>
> Yes, Please, the documentation out there could definitely be improved with
> some other case studies.

Indeed! One of the important things we learned when we set up rserv is
that bigint's suck when it comes to indexes! Heh, after we modified the
rserv code to cast appropriately the world worked spectacularly.

Sincerely,

Will

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Murthy Kambhampaty 2003-05-13 22:16:04 Pausing the pg_xlog/ filesystem
Previous Message Philip Geer 2003-05-13 20:20:43 Re: [OT]:Database design question