Re: PostgreSQL vs. InnoDB performance

After takin a swig o' Arrakan spice grog, pgsql@esiway.net (Marco Colombo) belched out:
> 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.

If the bottleneck is in the identical place, and they are otherwise
well-tuned, it is actually *not* that surprising that the timings for
"PostgreSQL vs real InnoDB" would be pretty close.

If both are being bottlenecked by the same notion of "how fast does
the disk spin," then the differences in performance won't be dramatic.

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

Actually, if you can demonstrate near-identical performance under a
common set of conditions, that's a really useful datum to start with.

It would then certainly be interesting to see how the behaviour
changes as various stresses are introduced...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://linuxdatabases.info/info/spreadsheets.html
Signs  of a   Klingon Programmer  -  16.  "Klingon programs   don't do
accountancy. For that, you need a Ferengi."