Changes between Initial Version and Version 1 of Ticket #590


Ignore:
Timestamp:
09/06/13 11:52:42 (3 years ago)
Author:
jim
Comment:

Work already done and sent in email, plus additional tweaks since then from item 6...

1.. Downloaded and processed the slow query log with ​https://github.com/LeeKemp/mysql-slow-query-log-parser -- example output file attached at ​https://tech.transitionnetwork.org/trac/attachment/ticket/585/sql.report.log

2.. (on call) Installed and used DB Tuner module to add an index for field_region_value field on content_field_region table.

3.. Looked at the last items and worked back... The 'access' table seems to cause a lot, so I've used DB Tuner module to add indexes for: ALTER TABLE {access} ADD INDEX mask (mask) ALTER TABLE {access} ADD INDEX status (status) ALTER TABLE {access} ADD INDEX type (type)

4.. Disabled DB Tuner, enabled Variable Cleanup... Removed the following variables not needed that have not been cleaned up by their modules:

  • ~40 autosave_* variables (module no longer used)
  • 4 fancy_login_* - no longer used
  • 7 purl_* - no longer used
  • 8 varnish_ - no longer used

5.. cleared the caches to force the smaller variable table to be loaded.

6.. Examined the slow log and saw lots of SELECT name FROM users WHERE LOWER(mail) = LOWER(XXX); type ones -- investigation showed this is caused by LoginTobogan? that allows people to login via email too, however the LOWER() makes these queries very slow. On MySQL that call is also unnecessary (since string comparisons are case-insensitive as standard). I've patched our PROD version of LoginToboggan? to avoid the LOWER() calls, and only scan the email column if there's an '@' in the username field... I'll convert these changes to a patch so the updates will become part of #582.

7.. I notice some calls to the News Sharing Engine are slow, so I've upped the caching to 6 hours for all views queries there.

8.. PROPOSAL A & B: I notice there are plenty of spam entries in the Taxonomy tables, and listing all entries with > 50 chars shows ~200 entries that could safely be removed. Adding this to 'proposed fixes' list above needing approval. Taxonomy could use a cleanup.

9.. PROPOSAL C: I see plenty of SELECT * FROM variable calls, which imply a cache clear due to a variable being set. In normal use variables shouldn't be set (admin screens tend to do this), so I'd like to try to see what module it causing this and patch/remove it.

more to come...

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #590

    • Property Add Hours to Ticket changed from 0.0 to 2.25
    • Property Status changed from new to assigned
    • Property Total Hours changed from 0.0 to 2.25

  • Ticket #590 – Description

    initial v1  
    11This ticket is to track the work and changes done within the Drupal sphere in relation to performance enhancements done since #585. 
     2 
     3More information is needed and will come when ticket:586 New Relic Monitoring for BOA is completed. 
     4 
     5I also note that many of these cleanup operations will also help make the move to D7 smoother and better. 
     6 
     7 
     8'''Proposed fixes''' 
     9Ed, please confirm you'd like me to do these, or if you need more info. 
     10 
     11'''A) Remove spam taxonomy entries''' Low risk, low reward -- See item 8 below. A simple delete from taxo term table where length > 50 is worth doing IMHO, and nothing I saw that would be clobbered is not spam. 
     12 
     13'''B) Try a Taxonomy Cleanup''':  Medium risk, medium reward -- style module to try to merge terms with the same names and clean up the link tables back to nodes. Further, we can remove any taxonomies or relations to certain CTs that don't really add value. 
     14 
     15'''C) Find Variable table writes and kill them''' -- Per item 9 below:  I see plenty of SELECT * FROM variable calls, which imply a cache clear due to a variable being set. In normal use variables shouldn't be set (admin screens tend to do this), so I'd like to try to see what module it causing this and patch/remove it. 
     16 
     17'''D) Review Views caching''' low risk, high reward -- this was done a while back but I think 
     18 
     19'''E) Kill Microsites and the Forums''' -- Low risk, '''game changer''', high reward: -- then remove OG and Forum modules. In D6 these are known to be performance killers. We could alternatively migrate the forum to a simpler setup (not using forum module) that leverages Disqus or other services to offload comments and moderation.