Bug Add-on causes slow down of XFMG

[Seeker-Smith]

This is a weird one.

When Social Groups is disabled my users, and I can access the XFMG on my site like any other link. However when Social Groups is enabled the XFMG link stalls and can take up to 2-3 minutes to load. the link is to /media/

This is reproducible.

 

[CallingHome]

You can enable debug mode. Put this line in src/config.php:

$config['debug'] = true;

Then visit /media/?_debug=1 and look for queries that take very long. I think that may help finding the problem.

 

[Seeker-Smith]

Thank you. I wish someone at Xenforo had suggested this.

Without the add-on media loads:
Page Time: 1.2668s
Memory: 6.8127 MB (Peak: 7.2253 MB)
Queries (22, time: 1.0940s, 86.4%)

With the add-on:
Page Time: 74.1733s
Memory: 6.9749 MB (Peak: 7.3954 MB)
Queries (25, time: 74.0051s, 99.8%)

 

[Seeker-Smith]

You can enable debug mode. Put this line in src/config.php:

$config['debug'] = true;

Then visit /media/?_debug=1 and look for queries that take very long. I think that may help finding the problem.

So I see the queries where the slow down is. Should I post it here?

 

[Fillip H.]

Yes, please do

 

[Seeker-Smith]

Here are the two debug dumps. Perhaps delete them after you get them.

 

[Fillip H.]

Those kind of dumps won’t contain any personal information, but if you’re concerned you can always DM them to me.

As you might imagine, it’ll be some time before I’ll have a chance to do anything other than taking a backup and reinstalling the server with a more secure control panel software

 

[Seeker-Smith]

Yeah sorry to hear you got hit.

I've had to turn Social groups off so I hope a fix isn't a long time off.

 

[CallingHome]

I've had to turn Social groups off so I hope a fix isn't a long time off.

First, you can turn off the XFMG integration for Social groups on admin.php?options/groups/dbtechSocial/. Look for the option "Enable XenForo Media Gallery integration". That should be sufficient for now.

Do you have a big gallery? (About) how many media items / albums are linked to a group?

 

[CallingHome]

Just an idea: maybe an additional index helps. Try this query on your database:

ALTER TABLE `xf_dbtech_social_groups_group_album` ADD INDEX `album_id` (`album_id`) USING BTREE;

 

[Seeker-Smith]

First, you can turn off the XFMG integration for Social groups on admin.php?options/groups/dbtechSocial/. Look for the option "Enable XenForo Media Gallery integration". That should be sufficient for now.

Do you have a big gallery? (About) how many media items / albums are linked to a group?

I thought of that yesterday but turning off XFMG in Social groups made no difference. I'll run the debug against it though just to be sure.

 

[Seeker-Smith]

Just an idea: maybe an additional index helps. Try this query on your database:

ALTER TABLE `xf_dbtech_social_groups_group_album` ADD INDEX `album_id` (`album_id`) USING BTREE;

I can give this s try as well

Do you have a big gallery? (About) how many media items / albums are linked to a group?

Fairly large 1.1 million files. Very little, less than 200 files in Social groups and a couple small albums. We were just rolling out Social groups so its mostly sample data.

 

[Seeker-Smith]

Here is the debug with XFMG support turned off.

Edited replaced debug file

 

[Seeker-Smith]

Just an idea: maybe an additional index helps. Try this query on your database:

ALTER TABLE `xf_dbtech_social_groups_group_album` ADD INDEX `album_id` (`album_id`) USING BTREE;

I just tried added this index. With XFMG support in Social Groups turned off I'm still get a huge delay.

Page Time: 71.0458s
Memory: 6.9632 MB (Peak: 7.3760 MB)
Queries (23, time: 70.8865s, 99.8%)

 

[CallingHome]

Here is the debug with XFMG support turned off.

Oh, I see the JOINs in the SQL query are still there. :-/

Fairly large 1.1 million files.

I see, quite big

Your media index is also without Social groups quite slow.

Last thing I know you can try: go to admin.php?options/groups/xfmg/ and limit the timeframe for the media index (e.g. 30 days). The option is called "Only show media added within the last X days".

That should speed up the media index and hopefully also speed up the media index with social groups activated. Anyhow, when a user chooses to see all items (the url is media/?no_date_limit=1) then you would have the same problem with long execution time - at least for now; I am sure @Fillip H. will find a good solution.

 

[Fillip H.]

I've applied a hotfix to v2.1.0 that will respect the disabling of the integration when fetching the media index.

I'm looking into what can be done about the fact that adding the joins makes MySQL fetch all the rows in the xf_mg_media_item table as opposed to just the 24 being shown.

 

[Seeker-Smith]

I've applied a hotfix to v2.1.0 that will respect the disabling of the integration when fetching the media index.

I'm looking into what can be done about the fact that adding the joins makes MySQL fetch all the rows in the xf_mg_media_item table as opposed to just the 24 being shown.

Awesome, that gets me up again!!!

 

[Seeker-Smith]

Oh, I see the JOINs in the SQL query are still there. :-/


I see, quite big

Your media index is also without Social groups quite slow.

Last thing I know you can try: go to admin.php?options/groups/xfmg/ and limit the timeframe for the media index (e.g. 30 days). The option is called "Only show media added within the last X days".

That should speed up the media index and hopefully also speed up the media index with social groups activated. Anyhow, when a user chooses to see all items (the url is media/?no_date_limit=1) then you would have the same problem with long execution time - at least for now; I am sure @Fillip H. will find a good solution.

I did try limiting the index to 30 days. The problem of course is all the archives are not available. I may try it with that link but my members don't look for options they'll just complain.

 

[Fillip H.]

I wonder if a suitable workaround would be for me to introduce an option to exclude SG images from the index, somehow.

The current set of JOINs are required in order to properly handle permissions on the index, and I couldn't find any way to make them more performant.

Just to clarify; it's only the index, and not the actual Media tab in Social Groups themselves nor any other XFMG page, correct?

 

[Seeker-Smith]

I wonder if a suitable workaround would be for me to introduce an option to exclude SG images from the index, somehow.

The current set of JOINs are required in order to properly handle permissions on the index, and I couldn't find any way to make them more performant.

Just to clarify; it's only the index, and not the actual Media tab in Social Groups themselves nor any other XFMG page, correct?

As far as I've been able to determine it's only the index. Once a user has entered XFMG all the links seem okay. But if they hit the main media link again boom they are waiting.

I loaded a SG discussion thread with 200 image attachments and its runs fine with and without XFMG enabled with SG. Also the few small Albums that were created within SG did not appear to be effected.

BTW I really appreciate the hotfix.