A site that I am maintaining got rendered unusable. It contains more than 1,000 groups in it and a View generated page became very slow taking several seconds. I did not notice it slow when the number of groups were small, but after a threshold the site almost crashed.

Hours of investigation and test determined that adding an index for group_nid field of og_ancestry table solves the performance issue; and the Views page loads much faster than ever before.

Declaring a primary key on (nid, group_nid) does not make group_nid an index automatically. I think making group_nid an index is essential for better performance of OG run site because og_ancestry table is queried everywhere groups are involved.

Please consider to create index of group_nid in og_ancestry table at og.install. This will certainly benefit sites with large number of groups. I don't have a number to back up, but I am almost sure it will enhance the overall performance of OG run sites at the start.

CommentFileSizeAuthor
#11 og.788660-11.patch653 bytesGrayside
#7 og.install.patch836 bytesKi
#6 og.install.patch544 bytesKi
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

ajayg’s picture

Do you have a query which may not be using the existing query? That way it would be simple to find using explain if the current key is being used or not? Every index costs in terms of inserts and space so it would be nice to have some data. Since we don't have before and after data, a good start would be what explain shows for a query which would be easier to verify.

Ki’s picture

Following is the actual query generated by the view. (Some real data has been touched up)


SELECT
  node.nid AS nid,
  node.title AS node_title,
  node_data_field_group_type.field_group_type_value AS node_data_field_group_type_field_group_type_value,
  node.type AS node_type,
  node.vid AS node_vid,
  og.og_description AS og_og_description,
  ( SELECT COUNT(*)
    FROM og_uid ou INNER JOIN users u ON ou.uid = u.uid
    WHERE ou.nid = og.nid AND u.status > 0 AND ou.is_active >= 1 AND ou.is_admin >= 0
  ) AS member_count,
  ( SELECT COUNT(n.nid)
    FROM node n INNER JOIN og_ancestry oga ON n.nid = oga.nid
    WHERE n.status = 1 AND oga.group_nid = og.nid
  ) AS post_count,
  node.created AS node_created,
  ( SELECT COUNT(*)
    FROM node n INNER JOIN og_ancestry oga ON n.nid = oga.nid
    LEFT JOIN history h ON n.nid=h.nid AND h.uid = 1
    WHERE n.status = 1 AND oga.group_nid = og.nid AND h.timestamp IS NULL AND n.created > 1270000000
  ) AS post_count_new
FROM node node
  LEFT JOIN og og ON node.nid = og.nid
  INNER JOIN content_type_group node_data_field_group_type ON node.vid = node_data_field_group_type.vid
WHERE
  (node.status <> 0) AND (node.type IN ('group')) AND (og.og_directory <> 0) AND
  (node_data_field_group_type.field_group_type_value = 'GroupType1')
ORDER BY node_title ASC

amitaibu’s picture

Version: master » 6.x-2.x-dev
Status: Active » Needs review

I'd be happy to see some numbers to back up this patch...

Ki’s picture

Version: 6.x-2.x-dev » master
Status: Needs review » Active

These are the numbers for my site:

Organic groups: 1,700
Rows in og_ancestry: 1,100
Rows in og_uid: 155,000
Rows in users: 61,000

The query above is generated by Views to list the organic groups with exposed filters. The list shows group name, description, member count, post count and new post count.

When I ran the query at phpmyadmin before adding the index, it took 12 seconds.
After adding the index, it took 0.2 second.

amitaibu’s picture

@Ki,
Can you please create a patch, with an upgrade path.

Ki’s picture

Version: master » 6.x-2.1
Status: Active » Patch (to be ported)
FileSize
544 bytes

Here it is.

Ki’s picture

FileSize
836 bytes

Sorry, I missed the "upgrade path" part.

Here is the one that takes care of upgrade too.

Update versions seem inconsistent, from 6000 to 6001, 6002, 6203. I just followed the numbers and named mine as 6204.

ajayg’s picture

Status: Patch (to be ported) » Needs review
chuckbar77’s picture

subscribing

pdrake’s picture

Status: Needs review » Reviewed & tested by the community

This patch applied cleanly and provides significant performance improvements. In particular, execution time for one calendar-related query generated by views went from 0.60s to 0.01s.

Grayside’s picture

Status: Reviewed & tested by the community » Fixed
Issue tags: +Performance
FileSize
653 bytes

The hook_update_N() needs to use a comment block so updates are properly logged by drush, and perhaps other tools. The existing single-line comments should be cleaned up. Went ahead and applied as its a comment-only change.

http://drupalcode.org/project/og.git/commit/74ec13d

amitaibu’s picture

Cool :)

Status: Fixed » Closed (fixed)
Issue tags: -Performance

Automatically closed -- issue fixed for 2 weeks with no activity.