Re: Advantages of PostgreSQL over MySQL 5.0

On Wed, 2006-03-22 at 16:36 -0500, Merlin Moncure wrote:
> Chis Browne wrote:
> > PostgreSQL is likely to be way slower if you submit streams of little
> > queries, each an independent transaction...
>
> When I get around to it I plan on debunking this ;). I recently did
> extensive internal benchmarking of mysql 5.0 vs. postgresql 8.1 and
> it's victories across the board with only a couple of exceptions, and
> I have benchmarks to prove it. Im summary:
>
> 1. mysql is faster when it's query cache hit ratio is high
> 2. mysql opens connections much faster than pg, which is why we use pgpool
> 3. mysql sometimes wins where mvcc delete + insert can be kind of a
> pain (*much* rarer than commonly thought)
>
> While 'out of the box' postgresql is slower at select a,b,c from t
> where k type queries wrt mysql, the performance advantage is
> completely negated if you run those queries via prepared statements.
>
> In postgresql, queries executed over the parameterized/prepared C api
> are particularly fast...as much as a 70% speed reduction over vanilla
> PQexec. Now, the lower level API and prepared statements are not
> available for all applications, but when used they provide extremely
> low-latency access.
>
> Also,
> 1. pg can read off large result sets (50k records +) from the cache
> much faster than mysql
> 2. pg has a generally better query optimizer, altough here and there
> mysql scores a win
> 3. many other advantages you are already quite familiar with, mvcc, etc.
>
> Basically, I am saying that the proverbial bread and butter queries
> are not necessarily faster on mysql, just easier to get running fast,
> if that makes sense. Now, I'm not trying to bash mysql (I was in
> fact, quite impressed with 5.0) generally, but I really think the
> claim that it is faster for a broad array of tasks is highly dubious.
> pg just requires a little bit more specialized knowledge to get
> running up to its level in some cases.
>

Out of interest, what MySQL table type did you compare against? I
personally would be much more interested in such a comparison with
InnoDB tables than MyISAM.

--

Russ