Re: Drupal and PostgreSQL - performance issues?

On 14/10/2008, at 23.22, Merlin Moncure wrote:

> On Tue, Oct 14, 2008 at 5:04 PM, Mikkel Høgh <mikkel(at)hoegh(dot)org> wrote:
>> After disabling SSL for localhost, I ran the tests again, and the
>> performance gap is reduced to about 30%.
>
> ok, now consider you are on a read only load, with lots (if I read the
> thread correctly) repetitive queries. mysql has a feature called the
> query cache which optimizes sending the same query multiple times, but
> invalidates when a table changes.
>
> postgresql has a very efficient locking engine, so it tends to beat
> mysql up when you have lots of writing going on from different users.
> myisam has an edge on mainly read only data in some cases...but 30% is
> a small price to pay for all the extra power you get (and that 30%
> will flip quickly if you have to do any real work).
>
> merlin

Well more or less. In this case, there are only two repeating queries,
at the measly cost of 0.68ms. We do however have 31 lookups in to the
same table, where one is the dreaded "SELECT COUNT(pid) FROM
url_alias" which takes PostgreSQL a whopping 70.65ms out of the
115.74ms total for 87 queries.
Also, we're not comparing to MyISAM. The 30% is against MySQLs InnoDB-
engine, which is a lot closer to PostgreSQL feature-wise than MyISAM,
but there is of course still the query cache (which is quite small in
this case, given the low default settings, but still have a hit rate
around 80%).

In any case, I like PostgreSQL better, and MySQLs future is a bit
uncertain, with Innobase taken over by Oracle and MySQL AB taken over
by Sun, so I'm going to continue to play with PostgreSQL, also to take
advantage of stuff like vsearch, which I wouldn't be able to in like a
million years with MySQL - And thank you, Magnus, for coming to
Copenhagen to tell us about it :)
--
Kind regards,

Mikkel Høgh <mikkel(at)hoegh(dot)org>