PostgreSQL: YMMV?

From: Jan Ploski <jpljpl(at)gmx(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: PostgreSQL: YMMV?
Date: 2001-06-25 18:42:59
Message-ID: 1188385.993494579666.JavaMail.jpl@remotejava
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2001-06-25 18:52:37 Re: More Red Hat information
Previous Message Tom Lane 2001-06-25 18:40:39 Re: INNER JOIN ON vs ','+WHERE