Re: PostgreSQL vs. InnoDB performance

Christopher Browne <cbbrowne@acm.org> writes:

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

Yes, I also think so. One transaction is one transaction, so if
neither database is lying, they really should come out with similar
results.

Having said that, I'm getting much better speed doing very simple
transactions, and that is on the low end hardware (Dual PIII 1GHz, IDE
disk 7200rpm, Linux 2.6, ext3fs with barrier=1 mount option - so the
disk cache can safely be left turned on). I'm getting around 950
transactions with the attached app.

Also, observing the output of the iostat utility, it can be seen that
disk is quite busy and that it is running with the number of writes
comparable to the number of transactions (and the average size of one
write operation is near 8KB, which is the default PostgreSQL's block
size).

                             extended device statistics
device mgr/s mgw/s    r/s    w/s    kr/s    kw/s   size queue   wait svc_t  %b
hda        0   995    0.4  951.3     1.7  7785.3    8.2   4.2    4.4   0.6  59

zcalusic=# \d words
            Table "public.words"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 word   | character varying(256) |

#! /usr/bin/perl

use DBI;
use strict;
use warnings;

$| = 1;

my $dbh = DBI->connect('dbi:Pg:dbname=zcalusic',
               'zcalusic', 'useyours',
               {PrintError => 1, RaiseError => 1, AutoCommit => 1});

my $sth = $dbh->prepare("INSERT INTO words VALUES (?)");

$dbh->do("TRUNCATE TABLE words");

open(WORDS, "</usr/share/dict/words")
  or die "can't opet words file for reading: $!\n";

my $sofar;
my $start = time();
my $time = $start;
my $oldtime = $start;

while (my $word = <WORDS>) {
  chomp $word;
  $sth->execute($word);
  $sofar++;
  if (($time = time()) > $oldtime) {
    print int($sofar / ($time - $start)), " inserts/second     \r";
    $oldtime = $time;
  }
}
print int($sofar / ($time - $start)), " inserts/second\n";

close(WORDS);

exit 0;

--
Zlatko