Re: Postgres v MySQL 5.0

On 10/28/06, Christopher Browne <cbbrowne(at)acm(dot)org> wrote:
>
> Quoth johncwang(at)gmail(dot)com ("John Wang"):
>
> > Right now I typically look at the comparison as PostgreSQL isn't
> > proven to scale as well as MySQL for Web 2.0 type community
> > sites. It seems like all Alexa 100 sites that use a OSS database use
> > MySQL (many of them on InnoDB). I've also seen an old eWeek
> > performance test run where Oracle and MySQL compare well from a
> > scalability perspective but MSSQL doesn't (Pg wasn't tested). In
> > that case, MySQL, not Pg, was being compared against Oracle and
> > MSSQL. I haven't found a single Alexa 100 site that runs PostgreSQL
> > yet. If the Pg community doesn't want to compare itself against
> > MySQL, is the implicit recommendation that community type sites
> > planning for growth should use MySQL? That's fine if it is. I'm just
> > curious.
>
> That conclusion seems entirely opposite to the usual perspective that
> comes out on scalability.
>
> - MySQL(tm), with MyISAM, simply can't scale past ~ 10 heavily updating
> users, table locking being a prohibitively troublesome problem
>
> - MySQL(tm) with InnoDB(tm) seems likely to be a performance loss, as
> compared to MyISAM, for small installations, but likely goes
> further, in terms of being able to scale to larger numbers of
> concurrent updates.
>
> It's probably comparable to older versions of PostgreSQL in that
> fashion. Jan Wieck did some benchmarking on a TPC-W-like test that
> showed that there weren't material differences between InnoDB and
> PostgreSQL for moderate loads.

I'm more interested in very large loads than moderate ones.

- It's on PostgreSQL where people are finding it worthwhile to head
> to much heavier levels of "scalability," where we're deploying
> many-CPU heavy iron boxes alongside disk array technology.

It would be nice to see how these sites deployed Pg.

"Web 2.0" strikes me as being fairly much a nonsequitor in all of
> this. "Web 2.0" applications are ones whose purpose is to integrate
> information from various web services. So, if you get an address, you
> send a request to Google Maps to get location information and such.
> There's NO database work involved in that.

Let's call it Alexa 100 sites since you and MySQL are using different
definitions of what constitutes a "Web 2.0" site. Here are some situations
where sites have described their scaling with MySQL (take a look through
some of the presentations just for kicks -
http://www.dev411.com/blog/2006/10/05/mysql-deployment-presentations)

Flickr (Alexa 40): ~25,000 db trasactions/second peak
Mixi.jp (Alexa 42): More than 100 MySQL servers; Add more than 10
servers/month
LiveJournal (Alexa 63): 40-50M dynamic hits/day. 700-800/second at peak
Wikipedia (Alexa 15): >25000 SQL requests per second

Maybe the above is considered "NO database work" but I think potential users
are interested in seeing how sites can scale Pg to, at least, those levels.
MySQL doesn't have to be mentioned at all, just talk about real world
scaling to comparative or even greater levels.

--
John Wang
http://www.dev411.com/blog/