PHP 5.2.4-2ubuntu5.10
calendar module version 2.2 (up-to-date)

When updating the date module from version 2.4 to 2.6 I experience severe performance problems when using calendar views like the sidebar calendar. The mysqld and apache processes, especially mysqld, are using enormous amounts of CPU time for many seconds when eg moving month by month backward in the sidebar calendar view.

Often the page load does not even complete and all I get is a WSOD with message like

Fatal error: Allowed memory size of 335544320 bytes exhausted (tried to allocate 16 bytes) in /var/www/.../drupal-6.19/includes/database.mysql.inc on line 144

or WSOD with simple timeouts because the mysqld keeps working full throttle without coming to an end while I am the only user on an otherwise idle test system:

Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/.../drupal-6.19/sites/all/modules/date/date_api.module on line 1390

With date version 2.6 there all in a sudden seem to be serious MySQL performance problems when using the calendar block and perhaps also other calendar functions.

When reverting back to date version 2.4 things are back to normal.

Comments

hd’s picture

hd commented

I gathered more information using the devel module. The queries performed by views as a result of a customized version of the view provided by the calendar module show a big difference in execution time and in syntax depending whether performed based on date module version 2.4 or version 2.6:

with date 2.4 it took 1.7ms for:

SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, node.changed AS node_changed, node.type AS node_type, 'calendar:calendar_period_5' AS view_name FROM node node INNER JOIN users users ON node.uid = users.uid WHERE ((node.status <> 0) AND (users.uid = 705)) AND ((DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.changed), SEC_TO_TIME(32400)), '%Y-%m') <= '2010-07' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.changed), SEC_TO_TIME(32400)), '%Y-%m') >= '2010-07')) ORDER BY node_changed ASC

with date 2.6 it took 703.27ms!!! for:

SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, node.changed AS node_changed, node.type AS node_type, 'calendar:calendar_block_1' AS view_name FROM node node LEFT JOIN node node2 ON node.tnid = node2.tnid INNER JOIN users users ON node.uid = users.uid WHERE ((node.status <> 0) AND (users.uid = 705)) AND ((DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node2.changed), SEC_TO_TIME(32400)), '%Y-%m') <= '2010-09' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node2.changed), SEC_TO_TIME(32400)), '%Y-%m') >= '2010-09')) ORDER BY node_changed ASC

I do not understand where the difference is coming from, the calendar view has not been changed, only the date module version has. Also the default calender view, which is not selecting on uid, shows dramatic performance differences.

I am not sure whether this is a views problem which only shows with date version 2.6.

hd’s picture

hd commented

Follow up:

The "LEFT JOIN node node2 ON node.tnid = node2.tnid" is nonsensical and causing the problem. When I disable optional core module content translation, the problem goes away completely.

I assume this to be a Views problem "activated" when updating date module from 2.4 to 2.6 while having content translation enabled.

I found the following which likely relates to this problem:

http://drupalfr.org/forum/support/developpement/24916-cherche-lorigine-d.... It is written in French but translates well using Google translate.

http://drupal.org/node/477068 -- Wrong join added to the query when i18n is enabled.

I can reproduce this problem immediately with ease on a totally different up-to-date Drupal site: As soon as I enable module content translation, things go crazy. Just by looking at the calendar block view in admin/build/views/edit/calendar doing a live preview having "Show query above live preview" enabled in admin/build/views/tools one can see what is happening.

djschoone’s picture

djschoone commented

Disabling 'content translation' works for me. Thnx!

ndf’s picture

ndf commented

Hd, thank you for backtracing. I had the same issues after upgrading date2.4 to date2.6 and this thread helped me a lot.

We were pushed to restart mysql on shared hosting environment several times, because the servers were 'smoking'. It's quite odd to me a single sql-command can brake a website this bad.

php 5.2.13, mysql 5.1.41
Drupal 6.19, modules: views, i18n, content translation, date

date2.6. CPU max 95%, >45min
date2.4. CPU max 20% and takes 3 seconds for the same view
I'm back on 2.4

It's still unclear to me if the real reason is in views, content translation or date.

hd’s picture

hd commented

I assume you have a Drupal site with many (more than a thousand) nodes, have you?

Ecio’s picture

Ecio commented

subscribing.

ndf’s picture

ndf commented

True, few thousand nodes. (some cck types with shared fields).

ndf’s picture

ndf commented

Could this be the same issue? It has a patch file.
Double join on the node table when using the date argument for the node creation date

damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Issue summary: View changes
Status: Active » Closed (won't fix)

Unfortunately the Drupal 6 version of the Date module is no longer supported. That said, we appreciate that you took time to work on this issue. Should this request still be relevant for Drupal 7 please feel free to reopen it. Thank you.