Problem/Motivation
I manage a site with (currently) about 4600 biblio entries and something like 21000 contributors. When you edit a biblio node and then save, even if no changes were made, it will hang for upwards of 45 seconds each time. If several people do it at once it will make the site unresponsive for several minutes at least.
Here's the MySQL slow query log entry:
# Query_time: 85.980024 Lock_time: 0.000044 Rows_sent: 0 Rows_examined: 117888631
use msrc_drupal;
SET timestamp=1316028478;
DELETE FROM biblio_contributor_data
WHERE (cid NOT IN (SELECT DISTINCT bc.cid AS cid
FROM
biblio_contributor bc));
Proposed resolution
I tracked the problem down to the delete_orphan_authors() function in biblio.contributors.inc . This runs every time you save a node, called by biblio_update_contributors(). Can we change this to run either only when requested or by cron perhaps once a day, or optimize the query so it doesn't hang on large databases? I currently commented out this call in the update function to make the site usable again when editing entries (currently we create a node, then go in and edit it to attach a PDF).
Comments
Comment #1
rjerome commentedHmm, a problem indeed. The cron idea is probably a viable one, but I will also look at optimizing that query (I think that was a bit of a "lazy mans" approach).
Comment #2
rjerome commentedJust an added point, assuming you don't care if there are orphaned authors, you can turn this feature off on the 'admin/config/content/biblio' page in the "Authors" section. Thus you wouldn't have to comment out any code.
Comment #3
rjerome commentedI've committed some changes to the -dev branch which might help. It would be great if you could test it.
http://drupalcode.org/project/biblio.git/commit/6ce4ebf
Ron.
Comment #4
lightamanonfire commentedI hate to sound like a noob, but I don't see any -dev branch names in the git repo:
remotes/origin/4.6.x-1.x
remotes/origin/4.7.x-1.x
remotes/origin/4.7.x-2.x
remotes/origin/5.x-1.x
remotes/origin/6.x-1.x
remotes/origin/6.x-2.x
remotes/origin/7.x-1.x
remotes/origin/HEAD -> origin/master
remotes/origin/master
remotes/origin/rjerome
Am I missing something? I'm new to git so it's likely.
Comment #5
rjerome commentedNo need to get it from git, just download the -dev version from the project page (http://drupal.org/project/biblio)
I just include those "drupalcode.org" links for my own future reference, should I need to know what was changed to fix a particular issue.
Ron.
Comment #6
lightamanonfire commentedahh, ok. I tested the 7.x-dev on my test site (an old clone of the main site) and it seemed ok. This reduced the lag to a few seconds, which is a big improvement.
Comment #7
ar-jan commented