Re: Drupal and PostgreSQL - performance issues?

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD