Re: [PERFORM] Drupal and PostgreSQL - performance issues?

On Mon, Oct 13, 2008 at 12:00 AM, Mikkel Høgh <mikkel(at)hoegh(dot)org> wrote:
> Alright, my benchmarks might have been a bit naïve.
> When it comes to hardware, my webserver is a SunFire X2100 with an Opteron
> 1210 Dual Core and 4 GB DDR2 RAM, running 64-bit Ubuntu Linux Server 8.04
> LTS.
>
> When it comes to the resource usage section of my postgresql.conf, the only
> thing that are not commented out are:
> shared_buffers = 24MB
> max_fsm_pages = 153600

Well, 24MB is pretty small. See if you can increase your system's
shared memory and postgresql's shared_buffers to somewhere around 256M
to 512M. It likely won't make a big difference in this scenario, but
overall it will definitely help.

> I freely admit that the reason I haven't messed with these values is that I
> have next to no clue what the different things do and how they affect
> performance, so perhaps an apology is in order. As Scott wrote, "Without a
> realistic test scenario and with no connection pooling and with no
> performance tuning, I don't think you should make any decisions right now
> about which is faster". My apologies.

No need for apologies. You're looking for the best database for
drupal, and you're asking questions and trying to test to see which
one is best. You just need to look deeper is all. I would, however,
posit that you're putting the cart before the horse by looking at
performance first, instead of reliability.

On a machine with properly functioning hardware, postgresql is nearly
indestructable. MySQL has a lot of instances in time where, if you
pull the plug / lose power it will scramble your db / lose part or all
of your data. Databases are supposed to be durable. InnoDB, the
table handler, is pretty good, but it's surrounded by a DB that was
designed for speed not reliability.

There was a time when Microsoft was trying to cast IIS as faster than
Apache, so they released a benchmark showing IIS being twice as fast
as apache at delivering static pages. Let's say it was 10mS for
apache and 2mS for IIS. Seems really fast. Problem is, static pages
are cheap to deliver. I can buy a $500 server to serve the static
content and if I need more speed, I can throw more servers at the
problem for $500, no OS license fees.

But for dynamic content, the difference was the other way around, and
the delivery times were much higher for IIS, like 50mS for apache and
250mS for IIS. Suddenly, a handful of dynamic pages and the IIS
server was noticeably slower.

The same type of comparison tends to hold true for MySQL versus
PostgreSQL. MySQL tends to be very very fast at "select * from table
where id=5" while PostgreSQL is much faster at 4 page long reporting
queries with 5 levels of subselects and a couple of unions. Things
that make MySQL run so slow as to be useless. Also, PostgreSQL tends
to keep better read performance as the number of writes increase.
This is the real test, so the point I was making before about
realistic tests is very important.

It's about graceful degradation. PostgreSQL has it, and when your
site is getting 20 times the traffic you ever tested for, it's a
little late to figure out you might have picked the wrong DBMS. Note
I'm not saying MySQL is the wrong choice, I'm saying you don't know
because you haven't proven it capable.