1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to eliminate excessive queries from addons?

Discussion in 'XenForo Questions and Support' started by tyteen4a03, Feb 23, 2013.

  1. tyteen4a03

    tyteen4a03 Well-Known Member

    I have 122 mods installed on my forum, and now my forum index runs 80 queries, with a lot of them SELECTing from template_compiled and a fair share of SELECT * FROM xf_node WHERE lft > ? AND rgt < ?. Is there a way to compress these queries so I only need to run 20 instead of 80, without any code edits?

  2. Chris D

    Chris D XenForo Developer Staff Member

    Well, can you give us a list of your installed add-ons from the Admin CP home?

    It may be simple.
  3. Luke F

    Luke F Well-Known Member

    Enable templates as files and make sure you are using an opcode cache such as xcache

    A lot of it is likely due to the arguable oversight in the template system, where multiple calls to create the same template (such as a template hook inside a foreach loop) do not provide even per-request caching and will perform identical select queries several times.
  4. Chris D

    Chris D XenForo Developer Staff Member

    I was hoping to see the list of add-ons to jog my memory of a add-on clash I saw myself which caused these extra queries. I've just remembered what the add-ons were.

    The clash was between Nodes as Tabs and Digitalpoint Ads Positioning. I think, specifically, the fault was with Nodes as Tabs so...

    If you have old versions of these, especially Nodes as Tabs, then update them to the latest available version.
  5. tyteen4a03

    tyteen4a03 Well-Known Member

    Here we go...

    'Keywords' Meta Tag by xenCODE 1.0.0
    Add Poll to Existing Threads 1.0
    Add User Avatar to Last Post by Waindigo 1.1.1
    Add Username Style to Last Post 1.0.1
    Add-on Installer 0.2
    Admin Search User IP 1.0.0
    Admin Tabs Auto Min-Height v1.1
    Alphabetical Pagination by Waindigo 1.0.0
    Attachment Viewer 0.9.1
    Auto Ping by xenCODE 1.0.0 Beta 1
    BB Code Permissions by Waindigo 1.1.1
    ******* - Attention Zero-Posters 1.0.0
    ******* - Auto Close/Move/Delete Thread Inactivity 1.0.0
    ******* - Change Threads/Posts Owner 1.0.3
    ******* – Censorship per Node 1.0.0
    ******* – Reset Password using Secret Question 1.0.1
    Clickable Rows 2.0.0
    Control "nofollow" URLs 2.1
    Conversation From Registered Members Page 1.1
    Conversation Manager 1.0.1
    Conversation on Moderator Action by Waindigo 1.0.3
    Copyright Notice by Waindigo 1.0.1
    Custom BBCode Manager 1.3.4
    Custom Footer customFooter
    DamnComputer - Status In Post 1.0.3
    DaTheme Advanced Styling Rules 1.0.2
    Debug On Post by Waindigo 1.0.0
    Description for RSS 1.2
    Developer Kit 1.1
    Diagnostics Permission by Waindigo 1.0.0
    Digital Point Ad Positioning 1.1.0
    Digital Point Mass Move 1.0.0
    Digital Point Spy 1.0.5
    Digital Point Thread/Post BBCode 1.0.0
    Easy ban users 1.0.1
    Enable Debug From Admin Home 1.1a
    Enforce Post Rules 1.0.1
    Find users sharing the same IP 0.0.2
    Floating Login by SolidMean 1.0.0
    FreddysHouse Tools: Signature Once 1.2.0
    Friends by Waindigo 1.0.2
    GoodForNothing Title Manipulator 1.1.0
    google_adSense 1.0
    Help Pages 1.0.0
    HTML in Custom Titles 1.0
    Ignore What's New by Waindigo 1.1.0
    Join User Group by Waindigo 1.0.9
    LiquidPro Core 1.0.1
    LiquidPro New Thread Quota 1.1.0
    Live Updates 2.0
    LoginUserLocks - Prevent attempts to brute force the login area 1.0.1
    Mass Delete Spam Users 1.0.0
    Member Card View Permissions 1.0
    Member List Manager 1.3
    Mutt's You Code 1.5
    Node Numbers by Waindigo 1.0.0
    Nodes As Tabs 1.1.3
    Number of Posts to Send Personal Conversations 1.0
    Pages 0.2.1 Beta
    Post As User by Waindigo 1.0.3
    Premium Members BBcodes 1.4
    Profile Tab Manager 1.0.1
    Punish System 1.0.0
    Quick Reply Preview 1.3
    Quote de XenFacil 4.0.0
    RCBD - RecentStatus 4.0.2
    Registration Form Timer 2.0
    Remove Upgrade 1.0.0
    Reset Password From ACP 1.0
    Route Changer 1.1.2b
    Search This Thread 2.0
    Separate Sticky and Normal Threads 1.0
    Signature Restriction 1.2.1
    Sitemap for XenForo 1.2.0
    SK Mark Thread as Thread 1.1.0
    Smilie Importer by Waindigo 1.0.1
    Social Groups by Waindigo 1.0.2
    Start Conversation from Post Bit 1.3
    Template Modification System 1.2
    Template Syntax 1.6.1
    Thread Watchers 1.2
    TinyMCE Fix 0.6.2
    Toggle ME 2.1
    Ultimate Attachment manager[DH] 1.0.1
    Undelete Users by Waindigo 1.0.0
    View Members Online Now Group Restriction 1.0
    Warn Users Moderation 1.0
    WF - XenAtendo Upcoming Events 1.0.1
    Widget Framework Widget Pack 1.0.1
    Xen-TR.com Who Has Visited 2.5.1
    XenAtendo More By Waindigo 1.1.0
    XenMerge 0.1 Beta
    XenMoods 1.1.3
    Xenplaza - Limit Votes on Multiple Votes Poll 1.0
    Xenplaza - Number Viewing a Forum 1.0.0
    Xenplaza - Post Editing Reason 1.1.0
    XenTrader 2.1.1
    XFA - BBCode for tabs 1.0.3
    [8wayRun.Com] XenAtendo (Events) 1.4.9
    [8wayRun.Com] XenPorta (Portal) 1.5.6
    [8wayRun.Com] XenUtiles (Tools) 1.2.2
    [bd] Forum Watch 1.1
    [bd] Medal 1.4
    [bd] Tag Me 1.6.6
    [bd] Widget Framework 2.1.1
    [CrazyIM] Profile Links 1.2.1
    [Darth]Administrate Forum 1.0
    [dc] Smiley Manager 1.1.0
    [h] Show mods in Forums Beta 2
    [InfisJSC] Topic starter's rights 1.2.1
    [Iversia] FAQ Manager 1.0.1
    [Karll] With all watched threads 1.0
    [RT] Online Status Ribbon 1.2.2
    [RT] Spoiler 1.0.3
    [RT] User Rank Ribbons (35, w/TMS) 2.3
    [splendidpoint.com] AntiSPAM - Prevent Links and Emails 1.0.1
    [Tinhte] Attach Image Optimization 2.2.10
    [TinhTe] More Xenforo Permissions 1.1.0
    [Tinhte] XenTag 1.5.3
    [ValveTime.net] QuoteCollapse 1.2
    [******] Custom Node Icon 0.9
    [xfr] User Albums 1.0.0 b7

    Opcode cache is not an option because I'm on shared hosting.
  6. Slavik

    Slavik XenForo Moderator Staff Member

    Jesus thats a lot of addons. Enable debug and see which ones are adding the largest numbers.
    Brandon Sheley, Ranger375 and Sasa like this.
  7. tyteen4a03

    tyteen4a03 Well-Known Member

    I do have debug on, but looking at all 80 is a pain... I also have no idea how to tell which ones are adding - they all contribute. But I see this one a lot:
    SELECT moderator_content.*, user.username
    FROM xf_moderator_content AS moderator_content
    INNER JOIN xf_user AS user ON (user.user_id = moderator_content.user_id)
    WHERE (moderator_content.content_type = 'node') AND (moderator_content.content_id = 24)
    ORDER BY user.username
    (Also, I wish I didn't have to install that many addons, but that is how many features XF is missing... sigh. *insert IB rant here*)
  8. Digital Doctor

    Digital Doctor Well-Known Member

    I want an head to head Addon match: tyteen v 8thos.

    Fight !
    0xym0r0n, Shelley, ArnyVee and 2 others like this.
  9. Luke F

    Luke F Well-Known Member

    Enable the option regardless, I would be very surprised if it didn't perform better
  10. tyteen4a03

    tyteen4a03 Well-Known Member

    I enabled File Cache.

    My god no! :p

    Jokes aside, is there some kind of addon like vBOptimise that compresses all similar queries to one at runtime?
  11. RoldanLT

    RoldanLT Well-Known Member

    'Keywords' Meta Tag by xenCODE 1.0.0 < You can do it manually on "page_container" template
    Admin Search User IP 1.0.0 < prefer the built in feature
    Auto Ping by xenCODE 1.0.0 Beta 1 < just remove it, not working as intended
    ******* - Attention Zero-Posters 1.0.0 < Use the notices instead
    Clickable Rows 2.0.0 < I think this can be done with manual edit
    Custom Footer customFooter < Modify it yourself, not by an addon
    Developer Kit 1.1 < Are you a xenforo developer/programmer?
    Digital Point Spy 1.0.5 < Could be resource hog
    Find users sharing the same IP 0.0.2 < Use the built in feature
    Floating Login by SolidMean 1.0.0 < Can be done by adding 2 lines of css code on extra.css
    google_adSense 1.0 < Just use the built-in template ad_* or digital point addon
    Separate Sticky and Normal Threads 1.0 < Can be done with template edits.
    Signature Restriction 1.2.1 < redundant, you have FreddysHouse Tools: Signature Once 1.2.0 already
    Smilie Importer by Waindigo 1.0.1 < Uninstall if you don't have any smile to import
    [dc] Smiley Manager 1.1.0 < redundant, you have Smilie Importer by Waindigo 1.0.1 already

    And many more addon's to point.
    otto, 0xym0r0n and ArnyVee like this.
  12. Luke F

    Luke F Well-Known Member

    Just to make sure - you're talking about the admin CP option for templates as files and not the config.php file cache?
  13. tyteen4a03

    tyteen4a03 Well-Known Member

    I meant the letter. Not sure which cache you were talking about.
  14. Luke F

    Luke F Well-Known Member

    in admin cp, search 'fetch public templates as files' and enable it
  15. RoldanLT

    RoldanLT Well-Known Member

    Even if we don't have memcache,xcache,apc, etc?
  16. Luke F

    Luke F Well-Known Member

    In this situation it's all about choosing the fastest path from ram into the php parser. Unless you have a highly optimised mysql server with adequately large innodb pool on the same box (or with very low latency link), or almost no 'free' ram being used for os cache, then files will be faster than mysql.

    Obviously any opcode cache is the fastest by a long shot - much higher chance of fetching from ram than the alternatives, and less work for the php parser as well.
    Evina likes this.
  17. tyteen4a03

    tyteen4a03 Well-Known Member

    Comments in bold.
  18. Shelley

    Shelley Well-Known Member

    The amount of add-ons in the list scares me.
    Hoffi likes this.
  19. tyteen4a03

    tyteen4a03 Well-Known Member

    Unfortunately 85% of the mods there are missing features of XenForo, compared to vBulletin/IPB etc. I hope 1.2 will allow me to uninstall at least 25% of them.

Share This Page