Re: Postgres Benchmark Results - Mailing list pgsql-performance

> Well that matches up well with my experience, better even yet, file a
> performance bug to the commercial support and you'll get an explanation
> why your schema (or your hardware, well anything but the database
> software used) is the guilty factor.

    Yeah, I filed a bug last week since REPEATABLE READ isn't repeatable : it
works for SELECT but INSERT INTO ... SELECT switches to READ COMMITTED and
thus does not insert the same rows that the same SELECT would have
returned.

> but you know these IT manager journals consider mysql as the relevant
> opensource database. Guess it matches better with their expection than
> PG or say MaxDB (the artist known formerly as Sap DB).

    Never tried MaxDB.

    So far, my MyISAM benchmarks show that, while on the CPU limited case,
Postgres is faster (even on small simple selects) , when the dataset grows
larger, MyISAM keeps going much better than Postgres. That was to be
expected since the tables are more compact, it can read indexes without
hitting the tables, and of course it doesn't have transaction overhead.

    However, these good results are slightly mitigated by the massive data
corruption and complete mayhem that ensues, either from "transactions"
aborting mid-way, that can't be rolled back obviously, leaving stuff with
broken relations, or plain simple engine bugs which replace your data with
crap. After about 1/2 hour of hitting the tables hard, they start to
corrupt and you get cryptic error messages. Fortunately "REPAIR TABLE"
provides good consolation in telling you how much corrupt data it had to
erase from your table... really reassuring !

    I believe the following current or future Postgres features will provide
an interesting answer to MyISAM :

    - The fact that it doesn't corrupt your data, duh.
    - HOT
    - the new non-logged tables
    - Deferred Transactions, since adding a comment to a blog post doesn't
need the same guarantees than submitting a paid order, it makes sense that
the application could tell postgres which transactions we care about if
power is lost. This will massively boost performance for websites I
believe.
    - the patch that keeps tables in approximate cluster order

    By the way, about the ALTER TABLE SET PERSISTENCE ... for non-logged
tables, will we get an ON RECOVER trigger ?
    For instance, I have counts tables that are often updated by triggers. On
recovery, I could simply re-create the counts from the actual data. So I
could use the extra speed of non-crash proof tables.

>
> Andreas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>