How to eliminate excessive queries from addons?

[tyteen4a03]

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?

Thanks.

 

[Chris D]

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

It may be simple.

 

[Luke F]

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.

 

[Chris D]

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.

 

[tyteen4a03]

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.

 

[Slavik]

Jesus thats a lot of addons. Enable debug and see which ones are adding the largest numbers.

 

[tyteen4a03]

Jesus thats a lot of addons. Enable debug and see which ones are adding the largest numbers.

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*)

 

[Digital Doctor]

Here we go...

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

Fight !

 

[Luke F]

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

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

 

[tyteen4a03]

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

I enabled File Cache.

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

Fight !

My god no!


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

 

[rdn]

'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.

 

[Luke F]

I enabled File Cache.

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

 

[tyteen4a03]

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

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

 

[Luke F]

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

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

 

[rdn]

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

Even if we don't have memcache,xcache,apc, etc?

 

[Luke F]

Even if we don't have memcache,xcache,apc, etc?

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.

 

[tyteen4a03]

Admin Search User IP 1.0.0 < prefer the built in feature <- My staff come from vBulletin so this addon will save them some time... keeping.
Clickable Rows 2.0.0 < I think this can be done with manual edit <- Yes, but since there are two templates and one js file... I'll probably leave it.
Custom Footer customFooter < Modify it yourself, not by an addon <- Looks confusing for other admins to modify the content... will leave it for now.
Developer Kit 1.1 < Are you a xenforo developer/programmer? <- Yes, but not sure why it slipped in my production site.
Digital Point Spy 1.0.5 < Could be resource hog <- No additional queries detected... turning it off doesn't really slow performance
Find users sharing the same IP 0.0.2 < Use the built in feature <- This is built-in? Where? (If you mean ACP I need it to be readily accessible from postbit)
Floating Login by SolidMean 1.0.0 < Can be done by adding 2 lines of css code on extra.css <- How? The code shows that a whole template is needed but uninstalled anyway.
Separate Sticky and Normal Threads 1.0 < Can be done with template edits. <- Read the code, not sure how.
Signature Restriction 1.2.1 < redundant, you have FreddysHouse Tools: Signature Once 1.2.0 already <- Not redundant, Signature Restrictions adds permissions
[dc] Smiley Manager 1.1.0 < redundant, you have Smilie Importer by Waindigo 1.0.1 already <- Not redundant, Smiley Manager adds Smiley Categories

And many more addon's to point.

Comments in bold.

 

[Shelley]

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.

The amount of add-ons in the list scares me.

 

[tyteen4a03]

The amount of add-ons in the list scares me.

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.