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.
Comment | File | Size | Author |
---|---|---|---|
#11 | og.788660-11.patch | 653 bytes | Grayside |
#7 | og.install.patch | 836 bytes | Ki |
#6 | og.install.patch | 544 bytes | Ki |
Comments
Comment #1
ajayg CreditAttribution: ajayg commentedDo 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.
Comment #2
Ki CreditAttribution: Ki commentedFollowing is the actual query generated by the view. (Some real data has been touched up)
Comment #3
amitaibuI'd be happy to see some numbers to back up this patch...
Comment #4
Ki CreditAttribution: Ki commentedThese 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.
Comment #5
amitaibu@Ki,
Can you please create a patch, with an upgrade path.
Comment #6
Ki CreditAttribution: Ki commentedHere it is.
Comment #7
Ki CreditAttribution: Ki commentedSorry, 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.
Comment #8
ajayg CreditAttribution: ajayg commentedComment #9
chuckbar77 CreditAttribution: chuckbar77 commentedsubscribing
Comment #10
pdrake CreditAttribution: pdrake commentedThis 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.
Comment #11
Grayside CreditAttribution: Grayside commentedThe 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
Comment #12
amitaibuCool :)