Re: PostgreSQL vs. InnoDB performance

On Fri, 2005-06-03 at 11:38 +0200, Peter Eisentraut wrote:
> Am Freitag, 3. Juni 2005 00:36 schrieb Peter Eisentraut:
> > On a particular system, loading 1 million rows (100 bytes, nothing
> > fancy) into PostgreSQL one transaction at a time takes about 90
> > minutes. Doing the same in MySQL/InnoDB takes about 3 minutes. InnoDB
> > is supposed to have a similar level of functionality as far as the
> > storage manager is concerned, so I'm puzzled about how this can be.
> > Does anyone know whether InnoDB is taking some kind of questionable
> > shortcuts it doesn't tell me about?
>
> So here's another little gem about our friends from Uppsala: If you create a
> table with InnoDB storage and your server does not have InnoDB configured, it
> falls back to MyISAM without telling you.

Silently falling back to something unexpected seems to be quite common
there. For sure it's not the only case. :-|

> As it turns out, the test done with PostgreSQL vs. real InnoDB results in just
> about identical timings (90 min). The test done using PostgreSQL with fsync
> off vs. MyISAM also results in about identical timings (3 min).

The hardware seems to be the bottleneck. Try improving the performance
of your disk systems. It's very unlikely to get _exactly_ the same
figures from such two different RDBMS. You expect them to be close, but
not identical.

BTW, make sure the test correctly emulated multiple clients (say 25, 50
or 100). There's little point in stressing transaction support of a
RDBMS when there's only one single actor in the system, and therefore no
contention. Transaction code takes always the fast path that way and
you're testing the less important part of it.

Check out some performance tuning pages, you may need to adjust some OS
and PostgreSQL configuration parameters to allow and effectively handle
100+ connections (shared buffers come to mind). I believe the same is
true for MySQL.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo(at)ESI(dot)it