Thread: 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
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
On Mon, Jun 25, 2001 at 04:06:32PM -0400, Dave Cramer wrote: > Jan, > > I'd be interested in your code. This could be a JDBC driver problem. > > > Cheers, > Dave Ok, have a look at http://remotejava.dyndns.org/dbbench The "total times" in seconds seem off, so just ignore these (the real execution times in times.log are ok though; each test run takes about half an hour here). Also, I will be re-running the tests tonight. In the current code, all readers finish before the writers do, I am going to change it so that writers and readers are hitting the database during the whole run. Thanks, JPL
Jan, I took your test and ran it on my machine. 1GHz PIII 512MB ram, 40GB IDE UDMA 100, RH 2.4.5 kernel postgres 7.1.2 straight from rpm. Jdbc built from cvs, jdk 1.3 from sun and here are my results Insert Select Total Max Insert Max Select Max Start 1) 279.639 8.3466 781.31 4408 603 1938 2) 253.918 10.0325 703.88 1853 766 1854 1) with transactions 2) without transactions Looks better, but I would still like to see improvement. Dave -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jan Ploski Sent: June 25, 2001 4:38 PM To: Dave Cramer Cc: pgsql-general@postgresql.org Subject: RE: [GENERAL] PostgreSQL: YMMV? On Mon, Jun 25, 2001 at 04:06:32PM -0400, Dave Cramer wrote: > Jan, > > I'd be interested in your code. This could be a JDBC driver problem. > > > Cheers, > Dave Ok, have a look at http://remotejava.dyndns.org/dbbench The "total times" in seconds seem off, so just ignore these (the real execution times in times.log are ok though; each test run takes about half an hour here). Also, I will be re-running the tests tonight. In the current code, all readers finish before the writers do, I am going to change it so that writers and readers are hitting the database during the whole run. Thanks, JPL ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
(Why is this being CC'ed to everbody and their mothers?) On Tue, Jun 26, 2001 at 12:25:15AM -0400, some SMTP stream spewed forth: > 1GHz PIII 512MB ram, 40GB IDE UDMA 100, RH 2.4.5 kernel postgres 7.1.2 > straight from rpm. Jdbc built from cvs, jdk 1.3 from sun > > Looks better, but I would still like to see improvement. When you say "straight from rpm". Does that mean straight from rpm, or have you altered the -B and sort cache settings? I would consider those basic setup, based on what I have been hearing. Setting -B (buffers, 8192b) to something more reasonable for your system than the default would probably drastically improve your results. Also, altering the sort cache setting may improve your results, but I'm not familiar with that. ...unless I am way off base. Are you trying to prove something based on the default PostgreSQL settings? I think that would be unfair to PostgreSQL, which means well. (Rumor has it that 1/4 system ram is a good round figure for -B total size. Watch for falling SHMMAX though.) gh > Dave
Jan Ploski writes:
> 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):
Did they all use the same Connection object?
> 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) )
That sure looks like an index right there. I don't know if MySQL creates
an index for primary keys, but it might explain a performance drop for
INSERTs.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
In comp.databases.postgresql.questions Jan Ploski <jpljpl@gmx.de> wrote: > 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.