MySQL and PostgreSQL speed compare

Hi,

I wrote a piece of benchmarking, just to test my classes, and was suprised
of the speed diffs.

So one more entry to the flame war (or the start of a new one) about which
one is faster, PostgreSQL or MySQL.

Well I expected MySQL to be the faster one, but this much.

Inserts on MySQL : 0.71sec/1000 rows
Inserts on PostgreSQL: 10.78sec/1000 rows (15 times slower?)
Inserts on PostgreSQL*: 1.59sec/1000 rows (2 times slower?)

Modify on MySQL : 0.67sec/1000 rows
Modify on PostgreSQL: 10.20sec/1000 rows (15 times slower?)
Modify on PostgreSQL*: 1.61sec/1000 rows (2 times slower?)

Delete on MySQL : 1.04sec/1000 rows
Delete on PostgreSQL: 20.40sec/1000 rows (almost 20 times slower?)
Delete on PostgreSQL*: 7.20sec/1000 rows (7 times slower?)

Search were almost the same (MySQL were faster on some, PostgreSQL on some),
sorting and reading sorted entries from dba was the same. But
insert/modify/delete.

"PostgreSQL*" is postgres whith queries inside transactions. But as long as
transactions are broken in PostgreSQL you cant use them in real life (if a
query fails inside a transactions block, PostgreSQL "RollBack"s the whole
transaction block, and thats broken. You can not convince me of anything
else).

Then I thought that maybe it would even up if I made more than one simul.
call. So I rewrote the utility so that it forked itself several times. With
PostgreSQL I could not try the test with transactions activated
(transactions are broken in PostgreSQL, and the test shows it clearly).
PostgreSQl maxed out my CPU with 5 connections, MySQL used around 75% with
20 connections. At five connections MySQL was 5 times faster, with 20
connections it was 4 times faster.

I do not claim that this is accurate, maybe my classes are broken or
something, or the test might be totally wrong. But *I think* it simulates
quite well a ordinary webserver running the dba locally (on the same server
as the www-server).

The setup is:

PII 450MHz with 256MByte memory.
Linux Redhat 6.0 (almost out of box).
MySQL, latest .rpm (a few weeks ago).
PostgreSQL, from CVS tree (HEAD, a few weeks ago).
MySQL on a SCSI disk.
PostgreSQL on a IDE disk. I moved the "data" dir to the SCSI disk and
tested. Suprise suprise it was slower! Well PostgreSQL was as nice as MySQL
towards the CPU when it was on the SCSI disk.
Used gcc to compile PostgreSQL, using only the --prefix when
./configur(ing).

If you like to run the test (or view the code), download DBA-Test and AFW
package from my site (www.comder.com). No fancy configure scripts exist so
you have to modify the code to make it run on your system.

Comments? Reasons for the result? What was wrong with the test?

I do not want to start a flame war. Just need help to get PostgreSQL up to
speed or MySQL to support sub-selects.

// Jarmo

PS. Posted this to MySQL and PostgreSQL list. Want to hear both sides. DS.

Jarmo Paavilainen wrote:
[ . . . ]
> "PostgreSQL*" is postgres whith queries inside transactions. But as long as
> transactions are broken in PostgreSQL you cant use them in real life (if a
> query fails inside a transactions block, PostgreSQL "RollBack"s the whole
> transaction block, and thats broken. You can not convince me of anything
> else).

What do you think _should_ happen when a query fails inside a transaction block? (I am not
trying to convince you of anything, just being curious.)

Regards,

Frank

> Well I expected MySQL to be the faster one, but this much.
>
> Inserts on MySQL : 0.71sec/1000 rows
> Inserts on PostgreSQL: 10.78sec/1000 rows (15 times slower?)
> Inserts on PostgreSQL*: 1.59sec/1000 rows (2 times slower?)
>
> Modify on MySQL : 0.67sec/1000 rows
> Modify on PostgreSQL: 10.20sec/1000 rows (15 times slower?)
> Modify on PostgreSQL*: 1.61sec/1000 rows (2 times slower?)
>
> Delete on MySQL : 1.04sec/1000 rows
> Delete on PostgreSQL: 20.40sec/1000 rows (almost 20 times slower?)
> Delete on PostgreSQL*: 7.20sec/1000 rows (7 times slower?)
>
> Search were almost the same (MySQL were faster on some, PostgreSQL on
some),
> sorting and reading sorted entries from dba was the same. But
> insert/modify/delete.

To me, all this is pointing toward the possibility that you haven't
switched of fsync. This will make a MASSIVE difference to insert/update
queries. Read the docs on how to do this, and what the implications are.
And in case you cannot be bothered, add the "-o -F" parameters (IIRC) to
your postgres startup line in the postgres startup script in
/etc/rc.d/init.d.

Then try running the benchmark again and re-post the results. On a machine
with that much memory, allowing proper caching will make a huge difference.
I think MySQL does that by default, but PostgreSQL tries to be cautious and
flushes the it's disk cache bufferes after every query. This should even
things out quite a lot.

> "PostgreSQL*" is postgres whith queries inside transactions. But as long
as
> transactions are broken in PostgreSQL you cant use them in real life (if
a
> query fails inside a transactions block, PostgreSQL "RollBack"s the whole
> transaction block, and thats broken. You can not convince me of anything
> else).

They are not as functionally complete as they could be, I'll give you that.
But if you are sticking to good programming (and this applies to more than
just SQL) practices, you should make sure that your code behaves properly
and checks for things before going in head long. It can be slower, but it
is a lot cleaner. In the same way you check for a zero-return when using
malloc in C, and clean up all compiler warnings, or run-time warnings in
perl, you sould consider doing, for example, a SELECT query to make sure
that the records are/aren't already there before inserting them or updating
them.

Just MHO. Yes it is slightly slower, but it does work, and it is a lot
neater than fillijg up the error logs with all sorts of garbage.

> Then I thought that maybe it would even up if I made more than one simul.
> call. So I rewrote the utility so that it forked itself several times.
With
> PostgreSQL I could not try the test with transactions activated
> (transactions are broken in PostgreSQL, and the test shows it clearly).
> PostgreSQl maxed out my CPU with 5 connections, MySQL used around 75%
with
> 20 connections. At five connections MySQL was 5 times faster, with 20
> connections it was 4 times faster.

[snip]

> MySQL on a SCSI disk.
> PostgreSQL on a IDE disk. I moved the "data" dir to the SCSI disk and
> tested. Suprise suprise it was slower! Well PostgreSQL was as nice as
> MySQL towards the CPU when it was on the SCSI disk.

I thought the CPU hit was strange. This exaplains it...

Re-try the test with the fsync() disabled and re-post the results. I'm
interested to learn of your findings.

* Jarmo Paavilainen <netletter(at)comder(dot)com> [001229 04:23] wrote:
> Hi,
>
> I wrote a piece of benchmarking, just to test my classes, and was suprised
> of the speed diffs.
>
> So one more entry to the flame war (or the start of a new one) about which
> one is faster, PostgreSQL or MySQL.
>
> Well I expected MySQL to be the faster one, but this much.
>
> Inserts on MySQL : 0.71sec/1000 rows
> Inserts on PostgreSQL: 10.78sec/1000 rows (15 times slower?)
> Inserts on PostgreSQL*: 1.59sec/1000 rows (2 times slower?)
>
> Modify on MySQL : 0.67sec/1000 rows
> Modify on PostgreSQL: 10.20sec/1000 rows (15 times slower?)
> Modify on PostgreSQL*: 1.61sec/1000 rows (2 times slower?)
>
> Delete on MySQL : 1.04sec/1000 rows
> Delete on PostgreSQL: 20.40sec/1000 rows (almost 20 times slower?)
> Delete on PostgreSQL*: 7.20sec/1000 rows (7 times slower?)
>
> Search were almost the same (MySQL were faster on some, PostgreSQL on some),
> sorting and reading sorted entries from dba was the same. But
> insert/modify/delete.
>
> "PostgreSQL*" is postgres whith queries inside transactions. But as long as
> transactions are broken in PostgreSQL you cant use them in real life (if a
> query fails inside a transactions block, PostgreSQL "RollBack"s the whole
> transaction block, and thats broken. You can not convince me of anything
> else).

Well, I'm not going to try to convince you because you seem to have
made up your mind already, however for anyone else watching there's
nothing majorly broken with the 'all or nothing' approach in
postgresql, in fact it's very handy.

The all or nothing approach doesn't happen if a query fails to
modify or return any results, only if there's a genuine error in
the code, like inserting duplicate values into a column that should
be unique, or if you somehow send malformed sql to the server
mid-transaction. This is actually a pretty convient feature because
it prevents programmer mistakes from proceeding to trash more data
and backs it out.

The fact that MySQL doesn't support transactions at all severly
limits its utility for applications that need data consistancy, it
also makes it very dangerous to try any new queries on a database
because one can't just issue rollbacks after a test run.

> Then I thought that maybe it would even up if I made more than one simul.
> call. So I rewrote the utility so that it forked itself several times. With
> PostgreSQL I could not try the test with transactions activated
> (transactions are broken in PostgreSQL, and the test shows it clearly).
> PostgreSQl maxed out my CPU with 5 connections, MySQL used around 75% with
> 20 connections. At five connections MySQL was 5 times faster, with 20
> connections it was 4 times faster.
>
> I do not claim that this is accurate, maybe my classes are broken or
> something, or the test might be totally wrong. But *I think* it simulates
> quite well a ordinary webserver running the dba locally (on the same server
> as the www-server).
>
> The setup is:
>
> PII 450MHz with 256MByte memory.
> Linux Redhat 6.0 (almost out of box).
> MySQL, latest .rpm (a few weeks ago).
> PostgreSQL, from CVS tree (HEAD, a few weeks ago).
> MySQL on a SCSI disk.
> PostgreSQL on a IDE disk. I moved the "data" dir to the SCSI disk and
> tested. Suprise suprise it was slower! Well PostgreSQL was as nice as MySQL
> towards the CPU when it was on the SCSI disk.
> Used gcc to compile PostgreSQL, using only the --prefix when
> ./configur(ing).
>
> If you like to run the test (or view the code), download DBA-Test and AFW
> package from my site (www.comder.com). No fancy configure scripts exist so
> you have to modify the code to make it run on your system.
>
> Comments? Reasons for the result? What was wrong with the test?

A lot of things went wrong here, first off you didn't contact the
developers to let them know ahead of time and discuss tuning the
system. Both the MySQL and Postgresql developers deserve a chance
to recommend tuneing for your application/bench or ask that you
delay your bench until bug X or Y is addressed.

I also think that while updates and inserts are important (they
sure are for us) you admit that Postgresql achieves the same speed
for queries as MySQL when doing searches.

Most sites are that I know of are dynamic content and perform
selects for the most part.

Some other flaws:

You have an admitted inbalance with the disk systems but don't go
into any details.

You probably didn't tune postgresql worth a damn. I don't see any
mention of you raising the amount of shared memory allocated to
postgresql. I also imagine you may have run the test many times
on Postgresql without vacuuming the database?

Telling both development communities:
> MySQL, latest .rpm (a few weeks ago).
> PostgreSQL, from CVS tree (HEAD, a few weeks ago).
doesn't tell us much, maybe there's some bug in the code that
needed work?

> I do not want to start a flame war. Just need help to get PostgreSQL up to
> speed or MySQL to support sub-selects.

I think your time would be better spent working on actually
impelementing the features you want rather than posting broken and
biased benchmarks that do more harm than good.

bye,
--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."

Hello,

one possible behaviour would be to rollback the transaction to the last
savepoint, which was set before the current statement (not the
transaction!) began. In this case I could commit all changes which have
passed without an error. I think, this is the default case in Oracle -
is this compliant with the SQL-standard?

Regards, Jens

Frank Joerdens schrieb:
>
> Jarmo Paavilainen wrote:
> [ . . . ]
> > "PostgreSQL*" is postgres whith queries inside transactions. But as long as
> > transactions are broken in PostgreSQL you cant use them in real life (if a
> > query fails inside a transactions block, PostgreSQL "RollBack"s the whole
> > transaction block, and thats broken. You can not convince me of anything
> > else).
>
> What do you think _should_ happen when a query fails inside a transaction block? (I am not
> trying to convince you of anything, just being curious.)
>
> Regards,
>
> Frank

=============================================
Jens Hartwig
---------------------------------------------
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax : +49 (0)30 2554-3187
Mobil : +49 (0)170 167-2648
E-Mail : jhartwig(at)debis(dot)com
=============================================

But isn't it recommended to run the server with fsync? If so, you shouldn't
disable it on a benchmark then.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Gordan Bobic" <gordan(at)freeuk(dot)com>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Sent: Friday, December 29, 2000 7:31 AM
Subject: Re: [GENERAL] MySQL and PostgreSQL speed compare

> > Well I expected MySQL to be the faster one, but this much.
> >
> > Inserts on MySQL : 0.71sec/1000 rows
> > Inserts on PostgreSQL: 10.78sec/1000 rows (15 times slower?)
> > Inserts on PostgreSQL*: 1.59sec/1000 rows (2 times slower?)
> >
> > Modify on MySQL : 0.67sec/1000 rows
> > Modify on PostgreSQL: 10.20sec/1000 rows (15 times slower?)
> > Modify on PostgreSQL*: 1.61sec/1000 rows (2 times slower?)
> >
> > Delete on MySQL : 1.04sec/1000 rows
> > Delete on PostgreSQL: 20.40sec/1000 rows (almost 20 times slower?)
> > Delete on PostgreSQL*: 7.20sec/1000 rows (7 times slower?)
> >
> > Search were almost the same (MySQL were faster on some, PostgreSQL on
> some),
> > sorting and reading sorted entries from dba was the same. But
> > insert/modify/delete.
>
> To me, all this is pointing toward the possibility that you haven't
> switched of fsync. This will make a MASSIVE difference to insert/update
> queries. Read the docs on how to do this, and what the implications are.
> And in case you cannot be bothered, add the "-o -F" parameters (IIRC) to
> your postgres startup line in the postgres startup script in
> /etc/rc.d/init.d.
>
> Then try running the benchmark again and re-post the results. On a machine
> with that much memory, allowing proper caching will make a huge
difference.
> I think MySQL does that by default, but PostgreSQL tries to be cautious
and
> flushes the it's disk cache bufferes after every query. This should even
> things out quite a lot.
>
> > "PostgreSQL*" is postgres whith queries inside transactions. But as long
> as
> > transactions are broken in PostgreSQL you cant use them in real life (if
> a
> > query fails inside a transactions block, PostgreSQL "RollBack"s the
whole
> > transaction block, and thats broken. You can not convince me of anything
> > else).
>
> They are not as functionally complete as they could be, I'll give you
that.
> But if you are sticking to good programming (and this applies to more than
> just SQL) practices, you should make sure that your code behaves properly
> and checks for things before going in head long. It can be slower, but it
> is a lot cleaner. In the same way you check for a zero-return when using
> malloc in C, and clean up all compiler warnings, or run-time warnings in
> perl, you sould consider doing, for example, a SELECT query to make sure
> that the records are/aren't already there before inserting them or
updating
> them.
>
> Just MHO. Yes it is slightly slower, but it does work, and it is a lot
> neater than fillijg up the error logs with all sorts of garbage.
>
> > Then I thought that maybe it would even up if I made more than one
simul.
> > call. So I rewrote the utility so that it forked itself several times.
> With
> > PostgreSQL I could not try the test with transactions activated
> > (transactions are broken in PostgreSQL, and the test shows it clearly).
> > PostgreSQl maxed out my CPU with 5 connections, MySQL used around 75%
> with
> > 20 connections. At five connections MySQL was 5 times faster, with 20
> > connections it was 4 times faster.
>
> [snip]
>
> > MySQL on a SCSI disk.
> > PostgreSQL on a IDE disk. I moved the "data" dir to the SCSI disk and
> > tested. Suprise suprise it was slower! Well PostgreSQL was as nice as
> > MySQL towards the CPU when it was on the SCSI disk.
>
> I thought the CPU hit was strange. This exaplains it...
>
> Re-try the test with the fsync() disabled and re-post the results. I'm
> interested to learn of your findings.

Just curious, what kind of tables did you set up in MySQL? My understanding is
that if you want to use the BerkeleyDB transaction support in the 3.23
beta/gamma/whatever, you need to create your tables as BerkeleyDB type rather
than ISAM. (And then the new row-level locking feature from NuSphere adds a
third table type, but that's another story...)

So if you wanted to do a true apples-to-apples comparison of databases that
supported transactions, you'd use BerkeleyDB tables in MySQL. If they were ISAM
tables, then we're just back at the same old "speed versus data integrity" flame
war that has always been the base of the MySQL/Postgres comparison.

Of course, if they *were* Berkeley tables and you still got those results, then
we'll need to dig a little deeper ;-)

Regards,
Ned

Jarmo Paavilainen wrote:

> Hi,
>
> I wrote a piece of benchmarking, just to test my classes, and was suprised
> of the speed diffs.
>
> So one more entry to the flame war (or the start of a new one) about which
> one is faster, PostgreSQL or MySQL.
>
> Well I expected MySQL to be the faster one, but this much.
>
> Inserts on MySQL : 0.71sec/1000 rows
> Inserts on PostgreSQL: 10.78sec/1000 rows (15 times slower?)
> Inserts on PostgreSQL*: 1.59sec/1000 rows (2 times slower?)
>
> Modify on MySQL : 0.67sec/1000 rows
> Modify on PostgreSQL: 10.20sec/1000 rows (15 times slower?)
> Modify on PostgreSQL*: 1.61sec/1000 rows (2 times slower?)
>
> Delete on MySQL : 1.04sec/1000 rows
> Delete on PostgreSQL: 20.40sec/1000 rows (almost 20 times slower?)
> Delete on PostgreSQL*: 7.20sec/1000 rows (7 times slower?)
>
> Search were almost the same (MySQL were faster on some, PostgreSQL on some),
> sorting and reading sorted entries from dba was the same. But
> insert/modify/delete.
>
> "PostgreSQL*" is postgres whith queries inside transactions. But as long as
> transactions are broken in PostgreSQL you cant use them in real life (if a
> query fails inside a transactions block, PostgreSQL "RollBack"s the whole
> transaction block, and thats broken. You can not convince me of anything
> else).
>
> Then I thought that maybe it would even up if I made more than one simul.
> call. So I rewrote the utility so that it forked itself several times. With
> PostgreSQL I could not try the test with transactions activated
> (transactions are broken in PostgreSQL, and the test shows it clearly).
> PostgreSQl maxed out my CPU with 5 connections, MySQL used around 75% with
> 20 connections. At five connections MySQL was 5 times faster, with 20
> connections it was 4 times faster.
>
> I do not claim that this is accurate, maybe my classes are broken or
> something, or the test might be totally wrong. But *I think* it simulates
> quite well a ordinary webserver running the dba locally (on the same server
> as the www-server).
>
> The setup is:
>
> PII 450MHz with 256MByte memory.
> Linux Redhat 6.0 (almost out of box).
> MySQL, latest .rpm (a few weeks ago).
> PostgreSQL, from CVS tree (HEAD, a few weeks ago).
> MySQL on a SCSI disk.
> PostgreSQL on a IDE disk. I moved the "data" dir to the SCSI disk and
> tested. Suprise suprise it was slower! Well PostgreSQL was as nice as MySQL
> towards the CPU when it was on the SCSI disk.
> Used gcc to compile PostgreSQL, using only the --prefix when
> ./configur(ing).
>
> If you like to run the test (or view the code), download DBA-Test and AFW
> package from my site (www.comder.com). No fancy configure scripts exist so
> you have to modify the code to make it run on your system.
>
> Comments? Reasons for the result? What was wrong with the test?
>
> I do not want to start a flame war. Just need help to get PostgreSQL up to
> speed or MySQL to support sub-selects.
>
> // Jarmo
>
> PS. Posted this to MySQL and PostgreSQL list. Want to hear both sides. DS.

* Adam Lang <aalang(at)rutgersinsurance(dot)com> [001229 05:47] wrote:
> But isn't it recommended to run the server with fsync? If so, you shouldn't
> disable it on a benchmark then.

Actually, if he ran Postgresql with WAL enabled, fsync shouldn't
make much of a difference.

Hi,

...
> > "PostgreSQL*" is postgres whith queries inside transactions.
> > But as long as transactions are broken in PostgreSQL you cant use them
in real
> > life (if a query fails inside a transactions block, PostgreSQL
"RollBack"s
> > the whole transaction block, and thats broken. You can not convince me
of anything
> > else).
>
> What do you think _should_ happen when a query fails inside a
> transaction block? (I am not trying to convince you of anything, just
being curious.)

If a query fails it should return an error, and let me decide if I want to
rollback, change my query or continue as nothing happened.

A good example is this:

Make a SELECT to check if the row exist.
If not it make a INSERT, or if its there it make an UPDATE (or something
totally different).

Everything is swell, but when several pids are trying to insert there is a
window of error between the SELECT and INSERT. And the test program
triggered it frequently. What if there were a lot of insert/updated before
and after the failing one (inside this transaction) and it would be ok if
this row was inserted by someone else. The dba does not know about that, *I
do* and can write my program in such a way.

How do you fix that in PostgreSQL! The only way I know of is not to use
transactions. Then if the INSERT fails you can try again with SELECT to
check if the row has been inserted by someone else. And ofcourse you would
need to build your own rollback function.

The "all or nothing approach" ala PostgreSQL is broken!

Nuff about transactions. I do not think I can convince you and you cant
convince me about that they are not. And why do you not check how the other
dbas has solved this. I bet they work as I describe.

// Jarmo

Hi,

...
> Just curious, what kind of tables did you set up in MySQL? My

Ehh... there are more than one kind... I did not know. Still with
transactions on PostgreSQL (unsafe method?) MySQL was 2 times as fast as
PostgreSQL. I will check this out, and return to this list with the results.

...
> tables, then we're just back at the same old "speed versus data
> integrity" flame war that has always been the base of the
> MySQL/Postgres comparison.

Its a question of a compromising between speed and "integrity". *I think*
PostgreSQL should and could lower their "integrity" a bit (15 times slower).

...
> Of course, if they *were* Berkeley tables and you still got those
> results, then we'll need to dig a little deeper ;-)

I do not know, but I will find out.

// Jarmo

...
> > I wrote a piece of benchmarking, just to test my classes, and
> > was suprised of the speed diffs.
...
> > Inserts on MySQL : 0.71sec/1000 rows
> > Inserts on PostgreSQL: 10.78sec/1000 rows (15 times slower?)
> > Inserts on PostgreSQL*: 1.59sec/1000 rows (2 times slower?)

> * Adam Lang <aalang(at)rutgersinsurance(dot)com> [001229 05:47] wrote:
> > But isn't it recommended to run the server with fsync? If so, you
shouldn't
> > disable it on a benchmark then.
>
> Actually, if he ran Postgresql with WAL enabled, fsync shouldn't
> make much of a difference.

What's WAL? Are you referring to autocommit? I will admit that autocomit
already improves performance so much that fsync() isn't going to matter
that much, but it will still make a noticeable improvement. It certainly
did on my system (~20%). FWIW, disabling autocommint, and introducing the
overhead of doing a select for checking before EVERY INSERT and UPDATE,
made an improvement of about 2-3 times on my application... But, different
applications require different things, so... YMMV...

Regards.

Gordan

Hi,

...
> > I wrote a piece of benchmarking, just to test my classes, and
> > was suprised of the speed diffs.
...
> The fact that MySQL doesn't support transactions at all severly
> limits its utility for applications that need data consistancy, it

I thought it does. Well I need to test that before Im going to say anything
more.

...
> A lot of things went wrong here, first off you didn't contact the
> developers to let them know ahead of time and discuss tuning the
> system. Both the MySQL and Postgresql developers deserve a chance
> to recommend tuneing for your application/bench or ask that you
> delay your bench until bug X or Y is addressed.

I did not (and do not) take it so seriously. I do not (did not) claim that
the test is in any way usefull I just wanted peoples response (and did get
some).

...
> Most sites are that I know of are dynamic content and perform
> selects for the most part.

Yes but still.

...
> You have an admitted inbalance with the disk systems but don't go
> into any details.

Yes that was sloppy. I thought of that after I started to write my email. I
did a fast test with both dbas on SCSI (just simple moved PostgreSQL "data"
directory to SCSI). But the result was even slower. Anyway the biggest
differece between SCSI and IDE is throughput and CPU usage. Throughput is
not an issue here (small chunks of data), and CPU should not be (PostgreSQL
peaked CPU with 20 connections on IDE and used 75% on SCSI). It might be a
bigger difference with more connections. Both my IDE and SCSI are quite new
with fast search.

...
> You probably didn't tune postgresql worth a damn. I don't see any

Neither did I tune MySQL. Neither do 90% of the users.

...
> mention of you raising the amount of shared memory allocated to
> postgresql. I also imagine you may have run the test many times
> on Postgresql without vacuuming the database?

The test program DROPS the tables and recreates them. I do not know if you
still would need to VACUUM the dba. Anyway I did run the test several times
wihtout seing any (big) differences.

...
> I think your time would be better spent working on actually
> impelementing the features you want rather than posting broken and
> biased benchmarks that do more harm than good.

I do not think this was biased, maybe broken but not biased. Actually I use
PostgreSQL and all (free?) dbas that Ive installed have been PostgreSQL. The
code Im using was first written for PostgreSQL and the reason why I added
MySQL support was that my ISP refused to install PostgreSQL. I did the test
just to see if my classes also worked on MySQL before starting to port rest
of my code to MySQL (guess if I was suprised).

Implementing features... why? PostgreSQL has (almost) everything I need. Its
MySQL which would need to have some new features (sub-selects, views...).

Looking for the truth and nothing but the truth? Dont look for it among
humans. All they have are opinions.

// Jarmo

* Gordan Bobic <gordan(at)freeuk(dot)com> [001229 07:39] wrote:
> > * Adam Lang <aalang(at)rutgersinsurance(dot)com> [001229 05:47] wrote:
> > > But isn't it recommended to run the server with fsync? If so, you
> shouldn't
> > > disable it on a benchmark then.
> >
> > Actually, if he ran Postgresql with WAL enabled, fsync shouldn't
> > make much of a difference.
>
> What's WAL? Are you referring to autocommit? I will admit that autocomit
> already improves performance so much that fsync() isn't going to matter
> that much, but it will still make a noticeable improvement. It certainly
> did on my system (~20%). FWIW, disabling autocommint, and introducing the
> overhead of doing a select for checking before EVERY INSERT and UPDATE,
> made an improvement of about 2-3 times on my application... But, different
> applications require different things, so... YMMV...

WAL is apparently something that orders writes in such a way that you
may loose data, but ordering is maintained such that if you have
transactions A, B and C (in that order) and you crash, you'll see
one of these:

1) A B and C
2) A and B
3) just C

With fsync on you should see A B and C, but WAL makes data recovery
a lot better.

(I think. :) )

--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."

Jarmo Paavilainen wrote:
[ . . . ]
> The "all or nothing approach" ala PostgreSQL is broken!
>
> Nuff about transactions. I do not think I can convince you and you cant
> convince me about that they are not.

Ok. I won't try then.

- Frank

Gordan Bobic writes:

> > Actually, if he ran Postgresql with WAL enabled, fsync shouldn't
> > make much of a difference.
>
> What's WAL?

Write Ahead Logging

It's a system that writes anything that it wants to do to the database to
a log file first and if the system crashes before the data is in the
database proper then it will replay the log on restart. (I guess you
could think of it as similar to a journalling file system.) That means
that fsync is never necessary on actual data files, only on the log files,
which are much smaller.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

Hi,

Ive got a few tips on what to do (turn off fsync(), could be broken
PostgreSQL from cvs). And few hints on whats wrong with MySQL (transactions
not enabled by default). Ill check these out and return to the list.

But first I want to comment a few things (marked with >>> from different
emails).

>>>Actually, if he ran Postgresql with WAL enabled, fsync shouldn't
>>>make much of a difference.

WAL seems to be enabled by default. What WAL is good for I do not know. But
if I start PostgreSQL without the -S I see a lot of info about WAL this and
WAL that.

...
> But isn't it recommended to run the server with fsync? If so,
> you shouldn't disable it on a benchmark then.

I run both MySQL and PostgreSQL as they are (minimum switches, no tuning, as
default as it can be). That is MySQL as the .rpm installed it
(--datadir --pid-file --skip-locking) and PostgreSQL with -i -S -D. Thats
the way most people would be running them anyway. And default should be good
enought for this test (simple queries, few rows (max 1000) per table).

...
> > > Well I expected MySQL to be the faster one, but this much.
...
> > To me, all this is pointing toward the possibility that you haven't
> > switched of fsync. This will make a MASSIVE difference to insert/update

The idea was to run as recomended and as default as possible. But with the
latest (alpha/beta/development) code.

...
> > And in case you cannot be bothered, add the "-o -F" parameters (IIRC) to
...
> > flushes the it's disk cache bufferes after every query. This should even
> > things out quite a lot.

Ill test that. Even thou it feels like tweaking PostgreSQL away from what
its considered safe by PostgreSQL developers. If it would be safe it would
be default.

...
> > > transaction block, and thats broken. You can not convince me of
anything else).
...
> > They are not as functionally complete as they could be, I'll give you
that.

Thanks, I think ;-)

What if I do a SELECT to check for a row. Then I do a INSERT. But between
SELECT and INSERT someone else inserted a row. NO I do not think that "good
programming" will solve this.

>>> Sir, thanks for sharing this with us. However, unless you can explain
>>> why queries inside of transactions run faster than queries outside of
>>> transactions, I would be inclined to mistrust the test. I haven't

I was suprised too. But the only difference is that I do a "BEGIN" before I
start inserting/modifying/deleting and then when Im done I do a "COMMIT".
Everything between those are exactly the same. Ive been told that MySQL does
not support transactions (by default) so there the test is broken. And with
PostgreSQL, well something inside PostgreSQL is broken (it cant be right
that with transaction PostgreSQL is 10 times faster than without).

...
> > interested to learn of your findings.

Ill update from cvs and rebuild PostgreSQL, and (try to) locate cvs of MySQL
and build it locally. And make the recomended tweaking (no fsync() but with
WAL). Ill also make sure that transactions are supported. Ill also add a
test of rollback to my test program.

// Jarmo

> >>> Sir, thanks for sharing this with us. However, unless you can explain
> >>> why queries inside of transactions run faster than queries outside of
> >>> transactions, I would be inclined to mistrust the test. I haven't
>
> I was suprised too. But the only difference is that I do a "BEGIN" before I
> start inserting/modifying/deleting and then when Im done I do a "COMMIT".
> Everything between those are exactly the same. Ive been told that MySQL does
> not support transactions (by default) so there the test is broken. And with
> PostgreSQL, well something inside PostgreSQL is broken (it cant be right
> that with transaction PostgreSQL is 10 times faster than without).

All PostgreSQL statements are in some transaction. If you're not using
explicit transactions (ie, autocommit) then it's effectively wrapping the
statement in a transaction block of its own, so you're doing the
transaction start/end (including any necessary file access) <n> times
rather than once which is probably most of the difference you're seeing.

Hi.

Just one note...

On Fri, Dec 29, 2000 at 07:01:21PM +0100, netletter(at)comder(dot)com wrote:
[...]
> >>> Sir, thanks for sharing this with us. However, unless you can explain
> >>> why queries inside of transactions run faster than queries outside of
> >>> transactions, I would be inclined to mistrust the test. I haven't
>
> I was suprised too. But the only difference is that I do a "BEGIN" before I
> start inserting/modifying/deleting and then when Im done I do a "COMMIT".
> Everything between those are exactly the same. Ive been told that MySQL does
> not support transactions (by default) so there the test is broken. And with
> PostgreSQL, well something inside PostgreSQL is broken (it cant be right
> that with transaction PostgreSQL is 10 times faster than without).

I don't know PostgreSQL, but I assume that BEGIN/COMMIT locks the
table (as it makes sense). Therefore it seems completely normal to me
that 1000 seperate INSERTs need more time, because they do 1000
seperate "transactions" and therefore seperate 1000 writes.

As transaction the writing behaviour is probably the same as if you
write LOCK/UNLOCK around the INSERTs and all writes will be issued at
one time, which usually is a lot faster (less disk writes, less checks
and so on).

It is a common optimization to use LOCK/UNLOCK to get faster mass
INSERTs. So the above behaviour is what I would expect.

On the other hand, you would have to use LOCK/UNLOCK on MySQL to
compare speed with transactions (given that reliability does not
count) or use BDB tables with BEGIN/COMMIT instead.

Bye,

Benjamin.

On Fri, 29 Dec 2000, Jarmo Paavilainen wrote:

> > > > Well I expected MySQL to be the faster one, but this much.
> ...
> > > To me, all this is pointing toward the possibility that you haven't
> > > switched of fsync. This will make a MASSIVE difference to insert/update
>
> The idea was to run as recomended and as default as possible. But with the
> latest (alpha/beta/development) code.

There's quite a difference between "recommended" and "default" - default
tends to err grossly on the side of protective and safe, while we all can
suggest better ways (maybe not as safe?) to do things.

> Ill test that. Even thou it feels like tweaking PostgreSQL away from what
> its considered safe by PostgreSQL developers. If it would be safe it would
> be default.

Once 7.1 is out, It would probably be safe to do so.
> ...
> What if I do a SELECT to check for a row. Then I do a INSERT. But between
> SELECT and INSERT someone else inserted a row. NO I do not think that "good
> programming" will solve this.

Good design, together with good implementation, gets you a long way.

> >>> Sir, thanks for sharing this with us. However, unless you can explain
> >>> why queries inside of transactions run faster than queries outside of
> >>> transactions, I would be inclined to mistrust the test. I haven't
>
> I was suprised too. But the only difference is that I do a "BEGIN" before I
> start inserting/modifying/deleting and then when Im done I do a "COMMIT".

This will be because of the difference with fsync() - as somebody else
already stated, if you don't explicitly wrap your SQL in BEGIN ... COMMIT,
every SQL query you run, becomes a transaction - and fsync() is called
after each transaction - So, if you do "BEGIN", followed by 7 SQL
queries, followed by "COMMIT" - that's 7 fsync()'s without begin/commit,
while it's only one fsync() with begin/commit. I hope I need not explain
the significance/cost of flushing disk buffers to disk - but it's not
cheap.

> Everything between those are exactly the same. Ive been told that MySQL does
> not support transactions (by default) so there the test is broken. And with
> PostgreSQL, well something inside PostgreSQL is broken (it cant be right
> that with transaction PostgreSQL is 10 times faster than without).

Nothing's broken - you just haven't read the documentation.

--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/

On Fri, 29 Dec 2000, Jarmo Paavilainen wrote:

> > Just curious, what kind of tables did you set up in MySQL? My
>
> Ehh... there are more than one kind... I did not know. Still with
> transactions on PostgreSQL (unsafe method?) MySQL was 2 times as fast as
> PostgreSQL. I will check this out, and return to this list with the results.

From my findings, there are at least 5 different types of tables in mysql
- DBD (Berkeley DB), HEAP, ISAM, MERGE and MyISAM. (Ned did mention
earlier today/yesterday that they implemented row-level locking with adds
(yet) another table type?). Onlt BDB tables support transactions.

> Its a question of a compromising between speed and "integrity". *I think*
> PostgreSQL should and could lower their "integrity" a bit (15 times slower).

I would rather have the integrity that PostgreSQL has, than the speed that
MySQL claims to have.

--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/

Jarmo Paavilainen wrote:
> I run both MySQL and PostgreSQL as they are (minimum switches, no tuning, as
> default as it can be). That is MySQL as the .rpm installed it
> (--datadir --pid-file --skip-locking) and PostgreSQL with -i -S -D. Thats
> the way most people would be running them anyway. And default should be good
> enought for this test (simple queries, few rows (max 1000) per table).

Comment to the list as a whole: believe it or not, most PostgreSQL
newbies who are not DBA's by profession really DO run with the default
settings. Maybe benchmarking with both the default and the recommended
settings (which are not really adequately (read: clearly and concisely)
documented as being the _recommended_ settings) would have its uses.
But just benchmarking with the default settings doesn't in and of itself
invalidate the results.

But, then again, if the default settings are so bad performance-wise,
why _are_ they the default anyway? There should be good reason, of
course, but I think maybe the defaults could or should be revisited as
to applicability.

> > > > Well I expected MySQL to be the faster one, but this much.

The MySQL crowd used to claim an 'order of magnitude' performance
difference. A difference of only two times is an improvement.

> The idea was to run as recomended and as default as possible. But with the
> latest (alpha/beta/development) code.

While I can't fault the use of the default settings, as stated above --
really, very very few are going to use the BETA CODE! If they are going
to install the beta, then they are just as likely to do the recommended
tuning. If you are going to use the default settings, then use the
latest NON-BETA releases.

> Ill test that. Even thou it feels like tweaking PostgreSQL away from what
> its considered safe by PostgreSQL developers. If it would be safe it would
> be default.

While the reasoning here sounds broken for an experienced PostgreSQL
user or developer, I can definitely see his point.

> > > > transaction block, and thats broken. You can not convince me of
> anything else).

> > > They are not as functionally complete as they could be, I'll give you
> that.

> Thanks, I think ;-)

FWIW, I prefer the PostgreSQL transaction block behavior. And it is not
difficult at all to work around -- but, I do see the utility of having
savepoints -- and I am sure we will have those at some point in time.

> What if I do a SELECT to check for a row. Then I do a INSERT. But between
> SELECT and INSERT someone else inserted a row. NO I do not think that "good
> programming" will solve this.

Neither will putting the SELECT and INSERT inside a transaction block,
unless you lock the table -- or use something like a UNIQUE INDEX to
prevent duplicate inserts. Or use a trigger.

It sounds like you are trying to prevent duplicate inserts -- something
like a BBS system which needs guaranteed unique user id's. My
experience is that a UNIQUE INDEX is the ONLY practical way to do this,
as the application code cannot possibly prevent an insert which violates
the uniqueness, thanks to the race condition between the SELECT and the
INSERT -- again, assuming that you don't want to lock the whole table
(and who wants to put a bottleneck like that into the system!).

Of course, if you're wanting uniquesness AND case-insensitive user id's,
you need a UNIQUE INDEX on lower(user-id), not just UNIQUE on user-id.

Now, as to the multiuser aspects of your benchmarks, you should never
have issued results when the two RDBMS's were running on non-identical
hardware (since PostgreSQL had its data on the IDE disk, and MySQL's was
on the SCSI disk, that qualifies as a _massive_ oversight that
completely invalidates your results).

Although, think a minute: if PostgreSQL is that close to MySQL's
performance, with the known extra overhead for transactions, for a
SINGLE USER case, then things are much better.

It's in the multiuser case PostgreSQL _really_ shines anyway -- that is,
given hardware that can handle the multiuser case in a sane fashion (and
IDE isn't sane hardware for multiuser benchmarking). And I say that
knowing that my (lightly loaded) production database server is running
IDE drives -- I don't need a benchmark-grade system to server 25 users.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

* Lamar Owen <lamar(dot)owen(at)wgcr(dot)org> [001229 13:13] wrote:
> Jarmo Paavilainen wrote:
> > I run both MySQL and PostgreSQL as they are (minimum switches, no tuning, as
> > default as it can be). That is MySQL as the .rpm installed it
> > (--datadir --pid-file --skip-locking) and PostgreSQL with -i -S -D. Thats
> > the way most people would be running them anyway. And default should be good
> > enought for this test (simple queries, few rows (max 1000) per table).
>
> Comment to the list as a whole: believe it or not, most PostgreSQL
> newbies who are not DBA's by profession really DO run with the default
> settings. Maybe benchmarking with both the default and the recommended
> settings (which are not really adequately (read: clearly and concisely)
> documented as being the _recommended_ settings) would have its uses.
> But just benchmarking with the default settings doesn't in and of itself
> invalidate the results.
>
> But, then again, if the default settings are so bad performance-wise,
> why _are_ they the default anyway? There should be good reason, of
> course, but I think maybe the defaults could or should be revisited as
> to applicability.

The truth is that it's difficult to do it right no matter what.

Either you try to grab as much shm as possible and possibly DoS the
box or break it for other applications:
"Hey, Postgresql ate all my shared memory and now gnome is broke!"
"MySQL exhausted all my swap space because it mmap'd 2 gigs of data
on my 32meg machine"
or something like that.

So the solution is for people to actually read the docs. :)

I can understand someone buying a car to get to and from work and
the movies, but you don't enter a racing contest without tuning
and knowing a hell of a lot about your vehicle.

I really don't understand why people expect computers to do everything
for them, the burden of using tools properly belongs to the user.

--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."

Alfred Perlstein wrote:
> Lamar Owen wrote:
> > But, then again, if the default settings are so bad performance-wise,
> > why _are_ they the default anyway? There should be good reason, of
> > course, but I think maybe the defaults could or should be revisited as
> > to applicability.

> I can understand someone buying a car to get to and from work and
> the movies, but you don't enter a racing contest without tuning
> and knowing a hell of a lot about your vehicle.

You obviously have never seen the hillbillies around here try to
drag-race their factory stock Corvettes and Camaros. Or the guy who put
a 527 Hemi (yes, they do exist) into his otherwise stock Charger and
kept wondering why the transmission made funny noises, the driveshaft
kept twisting, the differential kept exploding, and the tires kept
wearing out. Saw it. There are far more shadetree mechanics who
couldn't tune a tuning fork try their hand at building a racecar than
mechanics who actually know the balance of power in the drivetrain --
big engine => big transmission => big driveshaft => big punkin with
lockers or limited slip units => wide wheels with large bead surfaces =>
heavy wide tires with a Z speed rating. There are many less that
understand that solid iron rod does not make a good heavy duty
driveshaft. Or that understands that a car that performs well on the
dragstrip may not do so well on the closed track length race.

Likewise with self-proclaimed computer tuners.

> I really don't understand why people expect computers to do everything
> for them, the burden of using tools properly belongs to the user.

I of course agree in principle to this statement (and the whole tone of
your reply) -- but, my statement doesn't reflect my opinion -- it
reflects reality. Facts are stubborn things.

Of course the defaults will never be perfect -- nor will all users RTM.
But, therein lies the utility of 'defaults' benchmarking -- let's see
what the trade offs really are so that we the 'experts' can
intelligently recommend things -- as well as intelligently tweak the
defaults.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

>
>
>
> I really don't understand why people expect computers to do everything
> for them, the burden of using tools properly belongs to the user.
>

Let the congregation say "Amen!"

--
John Burski
Chief IT Cook and Bottlewasher
911 Emergency Products, St. Cloud, MN
(320) 656 0076 www.911ep.com

++++++++++++++++++++++++++++++++++
+ How's your cheese holding out? +
++++++++++++++++++++++++++++++++++

"Dominic J. Eidson" schrieb:

> > ...
> > What if I do a SELECT to check for a row. Then I do a INSERT. But between
> > SELECT and INSERT someone else inserted a row. NO I do not think that "good
> > programming" will solve this.
>
> Good design, together with good implementation, gets you a long way.
>

Ok, now the question may be asked: How can this pattern be solved
(to use a very modern word ..).

a) Prevent such a situation :-)

b) Try to insert a "lock"-mechanism in your code

Marten

Jarmo Paavilainen wrote:
> > Just curious, what kind of tables did you set up in MySQL? My
> Ehh... there are more than one kind... I did not know. Still with
> transactions on PostgreSQL (unsafe method?) MySQL was 2 times as fast as
> PostgreSQL. I will check this out, and return to this list with the results.
> ...
> > tables, then we're just back at the same old "speed versus data
> > integrity" flame war that has always been the base of the
> > MySQL/Postgres comparison.
> Its a question of a compromising between speed and "integrity". *I think*
> PostgreSQL should and could lower their "integrity" a bit (15 times slower).

The test data:

> > > Inserts on MySQL : 0.71sec/1000 rows
> > > Inserts on PostgreSQL: 10.78sec/1000 rows (15 times slower?)
> > > Inserts on PostgreSQL*: 1.59sec/1000 rows (2 times slower?)

It appears to me that the playing field, in this case, is also
"benchmark optimized" for the lowest common denominator (simple insert).
If this is all you are doing, very basic selects and inserts at high
speed, from one user, mySQL will be a much better choice for you.

I've found postgreSQL to be 3 to 5 times faster for my use,
(with fsyncing!) because of the way I've used it... not because
it will do a very simple select or insert as fast as mySQL, but
because it can do very complex joins on foreign keys during selects
efficiently, because it can use sub-selects, and because it scales
better for web applications with 1000 active online users (32+
concurrent db operations at any given time).

The net effect of this is that while single queries/inserts/updates
are 1x or 2x faster with mySQL, my single postgreSQL statements are
performing tasks that would take 3 to 100+ mySQL statements (and outside
logic would be needed for interpreting the results before making the
next mySQL query, further reducing the mySQL speed).

Another way of putting it:
If your application uses 1-3 tables, and they are only used once
each per user task (a simple web guestbook), and there are only
a few users, mySQL is usually the faster choice.

If you application uses 30 tables, and each task works with
ten or more tables, selecting from 5, then inserting into two,
and updating 3 (an accounting or complex ordering and inventory
application), with 25 concurrent users, postgreSQL code *can* be
written to be much faster.... but it can also be much slower.

Something that is interesting about this is that if you
write your database code in an abstraction layer (for example,
a php class, or perl DBI/DBD), that layer may need to assume that
each database will only be doing basic tasks, and cannot always
make use of the advanced features in a given database. So if
your database interaction code is only written for very simple
tasks, mySQL will almost always be faster.

See:
http://www.phpbuilder.com/columns/tim20000705.php3
(with a simple application, and few users, mySQL is 1-2x
faster)

http://www.phpbuilder.com/columns/tim20001112.php3
(with a complex application, and many users, postgreSQL
is 2-100x faster, and mySQL crawls or breaks under
heavy load)

So a simple select/insert/update benchmark does not
accurately show the speed differences of writing code
for complex applications or many users.

-Ronabop

--
Personal: ron(at)opus1(dot)com, 520-326-6109, http://www.opus1.com/ron/
Work: rchmara(at)pnsinc(dot)com, 520-546-8993, http://www.pnsinc.com/
The opinions expressed in this email are not neccesarrily those of myself,
my employers, or any of the other little voices in my head.

John Burski wrote:
> > I really don't understand why people expect computers to do everything
> > for them, the burden of using tools properly belongs to the user.
> Let the congregation say "Amen!"

The counterpoints:
(Complex tool)
A car comes assembled, from the factory, tuned to accelerate, and drive,
at the speeds that most users would *desire*. If it was factory tuned
for maximum safety, it would have a maximum speed of around 5 miles
per hour, wait 30 seconds after turning on a signal before allowing
you to make a drastic steering change, etc. If it was tuned for
maximum racing speed, it would kill most new users in a day or less.

(Simple Tool)
A hammer comes from the factory ready for multipurpose use. Were
is set up for "maximum safety", it would include a thumb protector,
a warning sound when it was swung too quickly, a rubber claw guard
to prevent backswing injury, etc. It is sold in it's most dangerous
state, and there is an assumption that if you're using the tool,
you already know how to use it safely, the burden of that use is
on the user.

I would not say a database is a simple tool, and it is also not
a tool which would injure or maim its new users if tuned for
perfomance. It may lose data, but not sever limbs or cause
permanent paralysis (unlike a hammer or a car can).

Advanced tools do have advanced safety features, but are sold "ready
for most use", not "safely disabled until you read all of the manuals
so you can figure out how to make it work decently". I agree that
reading the manuals is an important part of learning a new tool,
but it shouldn't be *required* to make it work for basic use.

Users shouldn't have to know how to tune the fuel injection system
for *optimum* performance in order to take a car for a test drive
on a fast roadway.

Computer software is, indeed, a tool which does not do everything
for you. But is should come "from the factory" setup for the way
a user would expect it to run, not partially disabled for maximum
safety. It's a power tool, and it can "hurt" if misused. If that's
too much responsibility for a bad user, it won't matter how safely
it's been tuned at the factory, the bad user will *still* modify it
in unsafe ways, and often tune it or use it the wrong way, damaging
the tool in the process.

I don't expect my software to come optimized for my use. I expect
it to come optimized for the most users and uses, not "dumbed down"
for the worst case, or "safely disabled" for the worst users.

-Ron

--
Personal: ron(at)opus1(dot)com, 520-326-6109, http://www.opus1.com/ron/
Work: rchmara(at)pnsinc(dot)com, 520-546-8993, http://www.pnsinc.com/
The opinions expressed in this email are not neccesarrily those of myself,
my employers, or any of the other little voices in my head.

Maybe because when you are "running it without a transaction" you still are.

Say you have this:

Begin
select
update
delete
commit

One transaction. Then you have this:

seelct
update
delete.

You are running three transactions as opposed to one. More overhead I
assume. If you submit a single query to postgres, it is wrapped in a
transaction.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Jarmo Paavilainen" <netletter(at)comder(dot)com>
To: "MYSQL" <mysql(at)lists(dot)mysql(dot)com>; "PostgreSQL General"
<pgsql-general(at)postgresql(dot)org>
Sent: Friday, December 29, 2000 1:01 PM
Subject: SV: [GENERAL] MySQL and PostgreSQL speed compare

<snip>
> >>> Sir, thanks for sharing this with us. However, unless you can explain
> >>> why queries inside of transactions run faster than queries outside of
> >>> transactions, I would be inclined to mistrust the test. I haven't
>
> I was suprised too. But the only difference is that I do a "BEGIN" before
I
> start inserting/modifying/deleting and then when Im done I do a "COMMIT".
> Everything between those are exactly the same. Ive been told that MySQL
does
> not support transactions (by default) so there the test is broken. And
with
> PostgreSQL, well something inside PostgreSQL is broken (it cant be right
> that with transaction PostgreSQL is 10 times faster than without).
<snip>

> >>>Actually, if he ran Postgresql with WAL enabled, fsync shouldn't
> >>>make much of a difference.
>
> WAL seems to be enabled by default. What WAL is good for I do not know.
But
> if I start PostgreSQL without the -S I see a lot of info about WAL this
and
> WAL that.

You seem to be too hung up on defaults. I am not into advocacy, and
whatever database works better for you is the right one to use. However,
using the defaults as the basis for benchmarking is intrinsically flawed.
It ultimately depends on what the person who set up the distribution felt
like at the time of creating the packages. There may be guidelines which
err on the side of caution, to the point of paranoia. All these are quite
common. If you are serious enough about using a database to run into
bottlenecks of whatever sort you are experiencing, then you should also be
serious enough to RTFM and find out about tuning the database for a
particular application (I consider a benchmark to be an application in this
case) before you do it. Posting results of a benchmark on a default
installation will not prove absolutely anything.

> ...
> > But isn't it recommended to run the server with fsync? If so,
> > you shouldn't disable it on a benchmark then.
>
> I run both MySQL and PostgreSQL as they are (minimum switches, no tuning,
as
> default as it can be). That is MySQL as the .rpm installed it
> (--datadir --pid-file --skip-locking) and PostgreSQL with -i -S -D. Thats
> the way most people would be running them anyway. And default should be
good
> enought for this test (simple queries, few rows (max 1000) per table).

There you go with defaults again. And I'm afraid that your argument "Thats
the way most people would be running them anyway." is also flawed in the
same way. People serious enough about using a database in a sufficiently
heavy environment to run up against speed problems whould be serious enough
about reading up on the software they are using to find out how to tune it
for their application.

Is this some kind of Windows induced dementia? Use everything as it was
installed, and expect it to always work in the best possible way for your
particular application? Use everything the way it was installed because
"users are too thick to play with the settings"? What abous sysops? Would
you really want your business, mission critical server to be operated by
someone who cannot even be bothered to read the documentation for the
software he is installing in sufficient depth to find out about things like
tuning?

The problem here is not the lack of knowledge - it is the resistance to the
concept of learning about something before judging it. Can you see what is
wrong with that approach?

> ...
> > > > Well I expected MySQL to be the faster one, but this much.
> ...
> > > To me, all this is pointing toward the possibility that you haven't
> > > switched of fsync. This will make a MASSIVE difference to
insert/update
>
> The idea was to run as recomended and as default as possible. But with
the
> latest (alpha/beta/development) code.

Latest code doesn't matter in this case. If you are running a benchmark,
here are the things you should be considering if you are being serious
about measuring real-world performance AND usefulness.

1) Never benchmark pre-releases. Always use the latest RELEASE version,
with all the required stability/bugfix patches installed.
2) Always tune the software and hardware up for the particular benchmark.
This will allow you to asses the ability of software/hardware to adapt to a
specific application.
3) If you are testing pre-release versions, you should ALWAYS take the
results with a pinch of salt. Pre-releases are not necessarily stable
(although they often are), and they are often set up to allow for easier
bug tracking and reliability testing, rather than pure speed measuring.
4) ALWAYS contact the developers of the software before publishing the
results. They will give you useful hints on how to optimize things.
5) Default installations are usually completely meaningless for
benchmarking purposes.

> ...
> > > And in case you cannot be bothered, add the "-o -F" parameters (IIRC)
to
> ...
> > > flushes the it's disk cache bufferes after every query. This should
even
> > > things out quite a lot.
>
> Ill test that. Even thou it feels like tweaking PostgreSQL away from what
> its considered safe by PostgreSQL developers. If it would be safe it
would
> be default.

OK, I am not a PostgreSQL developer (not quite yet, anyway), so they should
comment on this from their point of view.

However, if you are benchmarking speed, then tune the setup for speed. That
is what you are measuring, right? If you are testing something for
reliability and torture-proof features, then tune the setup for that. Not
tuning the system for the application is like using a sledge hammer to
unscrew a bolt. There is such a thing as the correct tool for the task!

> >>> Sir, thanks for sharing this with us. However, unless you can explain
> >>> why queries inside of transactions run faster than queries outside of
> >>> transactions, I would be inclined to mistrust the test. I haven't
>
> I was suprised too. But the only difference is that I do a "BEGIN" before
I
> start inserting/modifying/deleting and then when Im done I do a "COMMIT".
> Everything between those are exactly the same. Ive been told that MySQL
does
> not support transactions (by default) so there the test is broken. And
with
> PostgreSQL, well something inside PostgreSQL is broken (it cant be right
> that with transaction PostgreSQL is 10 times faster than without).

I can confirm that PostgreSQL is a LOT faster (can't name a figure because
I haven't made a controlled test) with "autocommit" disabled. You just have
to be careful not to have a failing SQL query anywhere.

But for the purposes of your benchmark, if one database is set up to use
the "one query per transaction" method (i.e. no transactions), then the
other one should as well. Depending on how a particular database handles
"transactionless" queries, it may require you to use "autocommit" and
execute each query as a transaction, or disable autocommit and perform all
the queries as a single transaction. I am not sure how MySQL does this, but
I am sure that the developers on the other list will tell you that. All of
that will influence how meaningful a benchmark is.

Note that I don't want to start an advocacy war "my database is better than
your database". Choosing the right database for a particular application is
also a way of "tuning" your system. As I said above, I think everyone
should use what works for them. Diversity is a GOOD thing. It gives us all
an insight into a problem from different points of view.

> ...
> > > interested to learn of your findings.
>
> Ill update from cvs and rebuild PostgreSQL, and (try to) locate cvs of
MySQL
> and build it locally. And make the recomended tweaking (no fsync() but
with
> WAL). Ill also make sure that transactions are supported. Ill also add a
> test of rollback to my test program.

IIRC, if you are tuning for speed, you should disable fsync() and DISABLE
WAL (can someone more clued up please confirm this?) for optimum speed? I
thought that WAL was designed as a "solution inbetween"...

Also, make sure that your benchmark findings include results for EACH test
separately. Different databases will have different performance benefits in
different environments, so make sure that your benchmark is sufficiently
diverse to test for those separate cases.

Are you put off the benchmarking yet?

Regards.

Gordan

[tuning analogies snipped]
>
> Likewise with self-proclaimed computer tuners.

You have no idea how much I agree with you there.

> > I really don't understand why people expect computers to do everything
> > for them, the burden of using tools properly belongs to the user.
>
> I of course agree in principle to this statement (and the whole tone of
> your reply) -- but, my statement doesn't reflect my opinion -- it
> reflects reality. Facts are stubborn things.
>
> Of course the defaults will never be perfect -- nor will all users RTM.
> But, therein lies the utility of 'defaults' benchmarking -- let's see
> what the trade offs really are so that we the 'experts' can
> intelligently recommend things -- as well as intelligently tweak the
> defaults.

Unfortunately, there are two ways you can load the defaults. You can strip
them down for maximum speed, or you can load them up for maximum
reliability. The latter will make the users complain about speed on the
support lists in a generally annoyed fashion.

Stripping things down for sppeed, OTOH, will work great - until someone
gets a powercut, or some other kind of wierd hardware failure that will
wipe out their data. Then they will come back again and complain.

And the answer is always to simply spend an hour or so reading the
documentation...

Some people, eh...

Regards.

Gordan

> Advanced tools do have advanced safety features, but are sold "ready
> for most use", not "safely disabled until you read all of the manuals
> so you can figure out how to make it work decently". I agree that
> reading the manuals is an important part of learning a new tool,
> but it shouldn't be *required* to make it work for basic use.

It isn't *required*. It works lovely the way it is shipped. But if you want
more speed, you should go and read the manual before complaining. It is not
crippled in any way - just tuned on the side of caution. It STILL works
well for MOST users who just want something to work, rather than ultimate
speed or reliability. It is up to the user to decide what is more important
for their particular application, and what is more appropriate given their
setup and budget.

> Users shouldn't have to know how to tune the fuel injection system
> for *optimum* performance in order to take a car for a test drive
> on a fast roadway.

No, they shouldn't. However, for THOSE users, the more appropriate way of
solving the problem would be to buy faster hardware - this is the analogy
you are following, right? If you want to drive faster than the car will let
you, buy a faster car, right?

> Computer software is, indeed, a tool which does not do everything
> for you. But is should come "from the factory" setup for the way
> a user would expect it to run, not partially disabled for maximum
> safety.

It is not "disabled" in any way. It works very well, for a vast majority of
uses. If you are setting up a web site, which you want people to see, then
you should consider yourself serious enough to read the documentation. If
you are intending to stake the future of your business on a server, then
exactly what are you thinking if you still refuse to RTFM?

> It's a power tool, and it can "hurt" if misused. If that's
> too much responsibility for a bad user, it won't matter how safely
> it's been tuned at the factory, the bad user will *still* modify it
> in unsafe ways, and often tune it or use it the wrong way, damaging
> the tool in the process.

There is a valid point in there somewhere. However, there is nothing wrong
with erring on the side of caution. All the functionalityis there - but if
you need more speed, all it takes is reading through the archives for an
hour or so, and you will find all the answers you need.

> I don't expect my software to come optimized for my use. I expect
> it to come optimized for the most users and uses, not "dumbed down"
> for the worst case, or "safely disabled" for the worst users.

Why? What's your reasoning behind that? If all the functionality is there,
and the only penalty is speed, which is still adequate for most uses, what
is the problem? If you are happy with tuning things up for your particular
application, they the chances are that you will go through the tuning
process yourself regardless of how it is shipped. All the default that is
slightly slower will do is encourage you to read the docs that little bit
sooner, if your system becomes large enough for this to be an issue.

Regards.

Gordan

At 04:08 PM 29-12-2000 -0500, Lamar Owen wrote:
>But, then again, if the default settings are so bad performance-wise,
>why _are_ they the default anyway? There should be good reason, of
>course, but I think maybe the defaults could or should be revisited as
>to applicability.

Heh, I've seen someone say that almost all the Oracle defaults are wrong :).

What other databases do the equivalent of Postgresql's default of sync on
every commit?

Thing is, Postgresql has had that sync default for years and because of
that there probably aren't so many tools or mechanisms to deal with the
failure of not syncing on every commit. So it's probably restore from
backup if things go poof, as there's not much info out there on repairing
the database. So far it's just pg_dump, vacuum, restore from backup.

Cheerio,
Link.