Date: Tue, 14 Oct 2008 09:38:29 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: Ivan Sergio Borgonovo <mail@webthatworks.it>
cc: pgsql-general@postgresql.org
Subject: Re: Drupal and PostgreSQL - performance issues?
In-Reply-To: <20081014114011.0ebe495a@dawn.webthatworks.it>
Message-ID: <Pine.GSO.4.64.0810140837390.28645@westnet.com>
References: <2434A118-CEFA-43A9-BA88-006EC1F80326@hoegh.org>
	<200810122214.53558.uwe@oss4u.com>
	<20081013090228.248a665f@dawn.webthatworks.it>
	<e7e0a2570810131945x1bbf10b8r67c0ac2a340b436f@mail.gmail.com>
	<20081014114011.0ebe495a@dawn.webthatworks.it>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Tue, 14 Oct 2008, Ivan Sergio Borgonovo wrote:

> The fact that out of the box on common hardware PostgreSQL under-perform 
> MySQL with default config would matter if few paragraph below you 
> wouldn't say that integrity has a *big* performance cost even on 
> read-only operation.

If you want a more detailed commentary on that subject, I'd suggest 
http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 
which hits all the major sides of the speed vs. integrity trade-offs here.

You suggest putting together a "how much are you paying for integrity?" 
comparison.  That's hard to do from the PostgreSQL side because most 
options don't allow an unsafe mode.  What would be easier is benchmarking 
some application in MySQL with the optional strict mode toggled on and 
off; MyISAM+loose vs. InnoDB+strict would be instructive I think.

Those of us who prefer PostgreSQL don't spend too much time working on 
this area because the very concept of a non-strict mode is horrifying. 
Quantifying how much full data integrity costs is like seeing how much 
faster you can run if you're set on fire:  while you might measure it, far 
better to just avoid the whole possibility.

> Well horror stories about PostgreSQL being doggy slow are quite
> common among MySQL users.

In addition to the default PostgreSQL configuration being optimized for 
size rather speed, there are a few operations that just don't execute well 
at all in Postgres.  The most common example is how counting things 
happens; that's slow in PostgreSQL for reasons that are deeply intertwined 
with the transaction implementation.  I don't believe there any problems 
like that in the Drupal implementation, but there's enough of that in 
other web applications that some percentage of horror stories come from 
that sort of thing--just not using PostgreSQL for a job it's a good choice 
for.  It's hard to distinguish those cases from those where it was 
appropriate, but just wasn't setup properly or compared fairly.

> Anyway making easier to tune PostgreSQL even if not optimally would
> be a good target.

There were two commits to the core PostgreSQL server code last month aimed 
at making it easier to build tools that interact with the server 
configuration.  The tool(s) that use those features are coming soon.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD