Re: Drupal and PostgreSQL - performance issues? - Mailing list pgsql-general

On Tue, Oct 14, 2008 at 10:05 PM, Martin Gainty <mgainty@hotmail.com> wrote:
> MG>comments prefixed with MG>

> MG>What about INNODB is Drupal forgetting the default engine for 5.x?

I don't use MySQL for drupal myself except for testing, but Drupal
just uses the default storage engine for MySQL, which happens to be
MyISAM on my Ubuntu Hardy. Doesn't seem to have much difference
between InnoDB or MyISAM, but I don't have a sizable Drupal site to
test that on.

>> esp. when Drupal stupidly issues some of the exact same queries up to
>> 9 times!
> MG>isnt this in query_cache..why is Drupal going braindead on this item?

Yes, it's rather braindead. I'd rather not worry about why, but how'd
we make Drupal use the PostgreSQL more effectively. In it's current
form (Drupal 5 and 6), it even issues a regexp for every query, even
before it hits the database because of some design decisions to use
user definable table prefix as a workaround to the lack of database
SCHEMA in MySQL: take the follow snippet as a representative Drupal
code:

$alias = db_result(db_query("SELECT dst FROM {url_alias} WHERE src =
'%s' AND language IN('%s', '') ORDER BY language DESC", $path,
$path_language));

That's one sprintf() and a number of string replace operations to
replace "{url_alias}" with "url_alias", as well as a number of regexp
to sanitize the query string.

Note this comment:
/*
 * Queries sent to Drupal should wrap all table names in curly brackets. This
 * function searches for this syntax and adds Drupal's table prefix to all
 * tables, allowing Drupal to coexist with other systems in the same database if
 * necessary.
*/
That's an MySQL-ism for working around legacy hosting sites offering
only a single MySQL db bogging postgresql down...

Also betraying MyISAM heritage (in Drupal pgsql driver):
/**
 * Lock a table.
 * This function automatically starts a transaction.
 */
function db_lock_table($table) {
  db_query('BEGIN; LOCK TABLE {'. db_escape_table($table) .'} IN
EXCLUSIVE MODE');
}

/**
 * Unlock all locked tables.
 * This function automatically commits a transaction.
 */
function db_unlock_tables() {
  db_query('COMMIT');
}

> MG>From what i've been reading the author doesnt work on drupal anymore

Not when Dries founded a commercial venture selling Drupal hosting and services.
http://acquia.com/products-services/acquia-frequently-asked-questions#driesrole

Doesn't bode too well though when they don't support PostgreSQL directly:
http://acquia.com/products-services/acquia-drupal-supported-platforms

> MG>If we could get access to the php source maybe we could fix this..?

Eh? Feel free: http://drupal.org/ It's an excellent CMS, with lots of
cool features, but comes with it's own set of wtf surprises.
To me, there're two ways to proceed: dive straight into the
development Drupal release and make the next version work better from
the ground up, or hack the existing Drupal postgresql driver and
frequently used SQLs and optimize them. We're working on the latter
because of some legacy code we have to support, but the former would
be the long term plan.

Optimizing PostgreSQL to make Drupal faster is not the correct way as
PostgreSQL is fast, scalable and robust enough. Just need to be used
more correctly.