RE: PostgreSQL: YMMV? - Mailing list pgsql-general

Jan,

I'd be interested in your code. This could be a JDBC driver problem.


Cheers,
Dave

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jan Ploski
Sent: June 25, 2001 2:43 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostgreSQL: YMMV?

Hello,

I did a little private MySQL (3.23.28) vs PostgreSQL (7.1r1) benchmark.
I heard about MySQL's problems with locking/concurrency. So I expected
that my code would highlight them. It did not. It revealed a problem (?)
with PostgreSQL, however. Here is how it all went:

The code is written in Java. 10 writer threads and 20 reader threads
are started. Each thread inserts or selects 500 rows in a loop from
this table (selects are done on random id's, no indices involved):

    CREATE TABLE bench (
       id          int4            DEFAULT nextval('bench_id_seq'),
       body        text            NOT NULL,
       subject     varchar(255)    NOT NULL,
       fld1        varchar(80)     NOT NULL,
       fld2        varchar(80)     NOT NULL,
       fld3        varchar(80)     NOT NULL,
       fld4        varchar(80)     NOT NULL,
       fld5        varchar(80)     NULL,
       fld6        varchar(80)     NULL,
       fld7        int2            NOT NULL,
       fld8        int2            NULL,
       PRIMARY KEY(id) )

MySQL version of the table:

    CREATE TABLE bench (
       id          int             NOT NULL AUTO_INCREMENT,
       body        text            NOT NULL,
       subject     varchar(255)    NOT NULL,
       fld1        varchar(80)     NOT NULL,
       fld2        varchar(80)     NOT NULL,
       fld3        varchar(80)     NOT NULL,
       fld4        varchar(80)     NOT NULL,
       fld5        varchar(80)     NULL,
       fld6        varchar(80)     NULL,
       fld7        int             NOT NULL,
       fld8        int             NULL,
       PRIMARY KEY(id) )


              1        2          3             4          5
            Insert  Select    Max Insert  Max Select  Max Start
MySQL       225.63   33.28       766           804         470
PgSQL(1)    342.92   40.81      7714           957        1954
PgSQL(2)    333.65   46.93     17499         17263        1879
PgSQL(3)    308.97   44.16      6008          4060        1003


1 - Avg insert time in ms
2 - Avg select time in ms
3 - Max insert time in ms
4 - Max select time in ms
5 - Max time until first select/insert in ms

PgSQL(1) - autocommit on = each insert in its own transaction
PgSQL(2) - autocommit off, one transaction per 500 inserts
PgSQL(3) - same as PgSQL(2), but I repeated the test on another disk,
           which is somewhat faster (and on which MySQL was running
           in the earlier tests -- I noticed too late)



The values in column 3 and 4 are what I call a problem.
In an interactive application, waiting 4-17 seconds for an insert
or select to complete can be very bad. This is what makes it appear
slow to the user. Compare it to a maximum wait time of < 1s
in MySQL's case.

Am I missing something?

Also, I admit to have written and run this benchmark without any tuning
(neither on PostgreSQL nor on MySQL). Are there any extra steps which
could change the results radically?


Thanks for attention -
JPL


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster