Re: Drupal and PostgreSQL - performance issues?

On Mon, Oct 13, 2008 at 1:02 AM, Ivan Sergio Borgonovo
<mail(at)webthatworks(dot)it> wrote:
<snip>
> Anyway I don't find myself comfortable with replies in these 2 lines
> of reasoning:
> 1) default configuration of PostgreSQL generally doesn't perform well
> 2) PostgreSQL may be slower but mySQL may trash your data.
>
> I think these answers don't make a good service to PostgreSQL.
>
> 1) still leave the problem there and doesn't give any good reason
> why Postgresql comes with a doggy default configuration on most
> hardware. It still doesn't explain why I've to work more tuning
> PostgreSQL to achieve similar performances of other DB when other DB
> don't require tuning.

This is a useful question, but there are reasonable answers to it. The
key underlying principle is that it's impossible to know what will
work well in a given situation until that situation is tested. That's
why benchmarks from someone else's box are often mostly useless on
your box, except for predicting generalities and then only when they
agree with other people's benchmarks. PostgreSQL ships with a very
conservative default configuration because (among other things,
perhaps) 1) it's a configuration that's very unlikely to fail
miserably for most situations, and 2) it's assumed that if server
performance matters, someone will spend time tuning things. The fact
that database X performs better than PostgreSQL out of the box is
fairly irrelevant; if performance matters, you won't use the defaults,
you'll find better ones that work for you.

> Making performance comparable without expert tuning will a) stop
> most too easy critics about PostgreSQL performances b) give
> developers much more feedback on PostgreSQL performance in "nearer
> to optimal" setup.

Most of the complaints of PostgreSQL being really slow are from people
who either 1) use PostgreSQL assuming its MySQL and therefore don't do
things they way a real DBA would do them, or 2) simply repeat myths
they've heard about PostgreSQL performance and have no experience to
back up. While it would be nice to be able to win over such people,
PostgreSQL developers tend to worry more about pleasing the people who
really know what they're doing. (The apparent philosophical
contradiction between my statements above and the fact that I'm
writing something as inane as PL/LOLCODE doesn't cause me much lost
sleep -- yet)

> If it is easy to write a tool that will help you to tune PostgreSQL,
> it seems it would be something that will really help PostgreSQL
> diffusion and improvements. If it is *complicated* to tune
> PostgreSQL so that it's performance can be *comparable* (I didn't
> write optimal) with other DB we have a problem.

It's not easy to write such a tool; the lists talk about one every few
months, and invariable conclude it's harder than just teaching DBAs to
do it (or alternatively letting those that need help pay those that
can help to tune for them).

As to whether it's a problem that it's a complex thing to tune, sure
it would be nice if it were easier, and efforts are made along those
lines all the time (cf. GUC simplification efforts for a contemporary
example). But databases are complex things, and any tool that makes
them overly simple is only glossing over the important details.

> Then other people added in the equation connection pooling as a MUST
> to compare MySQL and PostgreSQL performances.
> This makes the investment to have PostgreSQL in place of mySQL even
> higher for many, or at least it is going to puzzle most.

Anyone familiar with high-performance applications is familiar with
connection pooling.

> Or maybe... it is false that PostgreSQL doesn't have comparable
> performance to other DB with default configuration and repeating
> over and over the same answer that you've to tune PostgreSQL to get
> comparable performance doesn't play a good service to PostgreSQL.

Why not? It's the truth, and there are good reasons for it. See above.

> 2) I never saw a "trashing data benchmark" comparing reliability of
> PostgreSQL to MySQL. If what I need is a fast DB I'd chose mySQL...
> I think this could still not be the best decision to take based on
> *real situation*.

If you've got an important application (for some definition of
"important"), your considerations in choosing underlying software are
more complex than "is it the fastest option". Horror stories about
MySQL doing strange things to data, because of poor integrity
constraints, ISAM tables, or other problems are fairly common (among
PostgreSQL users, at least :) But I will also admit I have none of my
own; my particular experience in life has, thankfully, prevented me
from much MySQL exposure.

> Do we really have to trade integrity for speed?

Yes. Sanity checks take time.

> Is MyISAM really much
> faster in read only operations?

Yes. See above.

> What I get with that kind of answer is:
> an admission: - PostgreSQL is slow

People aren't saying that. They're saying it works better when someone
who knows what they're doing runs it.

> But is PostgreSQL competitive as a DB engine for apps like Drupal
> for the "average user"?

So are we talking about the "average user", or someone who needs real
performance? The average user certainly cares about performance, but
if (s)he really cares, (s)he will put time toward achieving
performance.

- Josh / eggyknap