From: Mikkel Høgh <mikkel@hoegh.org>
To: Tomasz Ostrowski <tometzky@batory.org.pl>
Subject: Re: Drupal and PostgreSQL - performance issues?
Date: Thu, 16 Oct 2008 10:34:07 +0200
Message-ID: <4BE94DFB-8917-4175-9083-7DEABC30BD7F@hoegh.org> (raw)
In-Reply-To: <48F6EE94.8010904@batory.org.pl>
References: <a93e6cbe-0000-4000-9a34-4db3620c619d@mm>
<C6449FCD-198D-4A04-BACB-1BB2F20DD5B1@hoegh.org>
<48F4F053.1060201@hagander.net>
<F933F17A-70BC-4DB0-9491-9EEF0E79ABD9@hoegh.org>
<b42b73150810141422i5ad693a8la3966fa6334c23f7@mail.gmail.com>
<7438A981-4B6A-4E5A-A1E0-50432CDEFB00@hoegh.org>
<48F6EE94.8010904@batory.org.pl>
It's not only to avoid one query, but to avoid one query every time
drupal_lookup_path() is called (which is every time the system builds
a link, which can be dozens of time on a page).
So, I think it's probably a worthwhile tradeoff on MyISAM, because
such queries are fast there, and you potentially save a bunch of
queries, if you're not using URL aliases.
Is there a better way to check if a table contains anything in
PostgreSQL? Perhaps just selecting one row?
--
Kind regards,
Mikkel Hřgh <mikkel@hoegh.org>
On 16/10/2008, at 09.34, Tomasz Ostrowski wrote:
> On 2008-10-14 23:57, Mikkel Hogh wrote:
>
>> 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.
>
> This is stupid.
>
> The Drupal code looks like this:
>
> // Use $count to avoid looking up paths in subsequent calls
> // if there simply are no aliases
> if (!isset($count)) {
> $count = db_result(db_query('SELECT COUNT(pid) FROM {url_alias}'));
> }
> /* ... */
> if ($count > 0 /* */) {
> /* one simple query */
> }
>
>
> It is doing count(*) type query (which requires a full table scan in
> Postgres) to avoid one simple, indexable query, which is also often
> cached. It has to be slower in any database, but it is much, much
> slower
> in Postgres.
>
> Try attached patch for drupal-5.11, and rerun your benchmarks.
>
> Regards
> Tometzky
> --
> ...although Eating Honey was a very good thing to do, there was a
> moment just before you began to eat it which was better than when you
> were...
> Winnie the Pooh
> diff -urNP drupal-5.11.orig/includes/path.inc drupal-5.11/includes/
> path.inc
> --- drupal-5.11.orig/includes/path.inc 2006-12-23 23:04:52.000000000
> +0100
> +++ drupal-5.11/includes/path.inc 2008-10-16 09:26:48.000000000 +0200
> @@ -42,18 +42,12 @@
> function drupal_lookup_path($action, $path = '') {
> // $map keys are Drupal paths and the values are the corresponding
> aliases
> static $map = array(), $no_src = array();
> - static $count;
> -
> - // Use $count to avoid looking up paths in subsequent calls if
> there simply are no aliases
> - if (!isset($count)) {
> - $count = db_result(db_query('SELECT COUNT(pid) FROM
> {url_alias}'));
> - }
>
> if ($action == 'wipe') {
> $map = array();
> $no_src = array();
> }
> - elseif ($count > 0 && $path != '') {
> + elseif ($path != '') {
> if ($action == 'alias') {
> if (isset($map[$path])) {
> return $map[$path];
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-generalAttachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mikkel@hoegh.org, tometzky@batory.org.pl
Subject: Re: Drupal and PostgreSQL - performance issues?
In-Reply-To: <4BE94DFB-8917-4175-9083-7DEABC30BD7F@hoegh.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox