Fixed After Upgrading from 1.5.20 to 2.2.1 MG 2.2 loads totally slow 20sec+

RobaFresca

Active member
The upgrade self was seamless and it's all fine but a click on "gallery" gives everyone a "little break" of around 20 seconds.
It was extremely fast before on 1.5.x with MG 1.x as i run it on a VPS with 10cpu, 60gb ram and SSD only with 1gb network in a modern 460gb network datacenter.
Forum runs on PHP 7.4.11 FPM/nginx and nginx proxy. It's all lightning fast but the first loading of the gallery (always on click).
i experienced that even on my testing-clone before so it is reproducable.

Tried also indexing all again and switch off niginx and on again to delete cache. No change.

Btw. we have 40k+ images in the gallery.

Hope there is soon a solution.

THX
 
I've moved this out of bugs for now. First of all, we have a separate XFMG bug reports forum. Secondly, I don't believe this will be a bug anyway, but we can hopefully at least troubleshoot it a bit more before we ascertain that.

40,000 images in the gallery isn't significant in the grand scheme of things and we definitely have customers with much larger galleries.

Structurally, the media gallery is not particularly different from MG 1.x.

So such a slowdown is unexpected.

I have registered on your forum. If you could approve my account and make sure I have access to the media gallery that would be appreciated.

It might be useful too if you could temporarily enable debug mode in src/config.php which will enable us to see what the root cause of the slowdown is.
 
Thanks.

I've tested it a few times and it does seem to often go over 10 seconds.

I think there might be a database table index missing or, perhaps, even an index that isn't supposed to be there.

Are you able to run the following query on your database directly and let us know the full output?

SQL:
SHOW CREATE TABLE xf_mg_media_item
 
Thank you for the additional access to the server.

We were able to track down perhaps a bug fix we had made to XFMG 1.c three years ago that might not have made it in to XFMG 2.x.

I have applied this change to your site and it has gone from taking 18 seconds down to 0.01 seconds.

Hopefully this is slightly more acceptable!

Can you please confirm this now functions correctly?
 
No worries.

Please note that until the next XFMG release you may see a notice in your admin control panel about files not having the expected contents.

This is expected because I have changed one file on the server. The file changed is src/addons/XFMG/ControllerPlugin/MediaList.php so if you see mention of that it is safe to ignore.
 
Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XFMG release (2.2.1).

Change log:
When displaying a list of media ordered by date, in some cases it is useful to MySQL to force a particular index hint in order to improve performance.
There may be a delay before changes are rolled out to the XenForo Community.
 
No worries.

Please note that until the next XFMG release you may see a notice in your admin control panel about files not having the expected contents.

This is expected because I have changed one file on the server. The file changed is src/addons/XFMG/ControllerPlugin/MediaList.php so if you see mention of that it is safe to ignore.
Sorry to bump an old thread, but do you have a link between this bug report and a commit to remind you what was changed here?

I ask because I have an issue currently with $finder->indexHint('FORCE', 'media_date'); within XFMG/ControllerPlugin/MediaList.php actually making queries slower, and just wanted to see if this was what was added to resolve this report. Albums with only ~5 items were taking ~4-7 seconds to load, by removing this they're down to ~0.1 seconds. XFMG install has ~150,000 albums and ~1,400,000 media items in total.

Edit: oh just seen the bug bot mentions an index hint, and this is the only one in that file. It seems once the gallery gets to a certain size this hint makes things worse.
 
Last edited:
Somewhat ironically, working a little today on XFMG 2.3, I discovered essentially the same thing.

I'm not sure what the final solution is going to be, yet, but for now I recommend commenting out the hint.

At this point, I'm not even certain whether its usefulness depends on the size of the gallery or whether other factors are at play. I feel like in a previous test case (a past dev forum I loaded with over a million images) it drastically improved things.

My current dev environment which I recently set up as a large gallery with, again, over a million images, the hint somewhat makes things worse.

Though it's not even quite that straight forward. Because with the index hint, it seems to help the count query be faster. Without the index hint, it makes the count query even worse.

Though some of this is exacerbated by being on higher numbered pages, due to MySQL's handling of large offsets.

It's also possible that we shouldn't be applying the hint if we know we're only selecting a subset of media items, such as within a category or album, but keep it for the main index, in some cases (maybe).

It's a bit of a mess, which doesn't have a one-size-fits all solution.

Suffice it to say, we're attempting to target some performance improvements in XFMG 2.3 so there's a chance that we can find a robust solution. I just haven't made lots of progress yet.
 
Thanks for confirming - we have a custom helper addon to extend the gallery a bit so I extended the finder to unset that hint a bit more cleanly. It had been a bit of a pain point for a while as viewing albums had always been slow, and it seems this has fixed it right up.

If it helps, I've seen the same behaviour with hints making some queries better and some worse with another feature we built. We have an attachment gallery addon that pulls out attachments from within a forum or thread to display them in a grid, and that came with a whole host of challenges, but I encountered a similar issue with index hints there.

I discovered I needed an index hint for it to be usable, and when querying attachments from forums with 5 or 6 figure attachment counts (the board is very attachment-heavy), the query was running in < 0.1s whichever page I was on. In forums that had very few attachments however, the query ran so utterly abysmally that it took the entire database server down. The only difference between each query was which node ID was being queried. If I removed the index hint, the forum with not many attachments ran fine and the forum with lots of attachments ran just as badly as the other one did with the index hint, it just swapped the problem over. So I didn't know how to possibly address that.

I'd already scrapped even trying to do pagination as the count query was so slow that I just capped it to 100 pages, but what I ended up doing to fix it was taking some conditions out of the WHERE clause and applying them to a join. As soon as I did that the query ran fine regardless of which forum I was in. It still needs the index hint, but it's at least consistently performant regardless of the dataset it's looking at.

To achieve this I also had to mirror the node_id value to xf_attachment_data. Whereas I used to join in everything I needed and then apply conditions in the WHERE clause like WHERE content_type = 'post' and content_id = post_id and node_id = X, this is now all in the join. The start of the query I run is essentially something like select * from xf_attachment join xf_attachment_data on xf_attachment_data.data_id = xf_attachment.data_id join xf_post on (content_type = 'post' and content_id = post_id and node_id = X) - so a bit of extra logic is needed to mirror the value and create this join, but the upshot is that as soon as I moved the node_id condition to the join the issue was resolved immediately, and perhaps a similar approach can be used here and maybe elsewhere too.

Essentially it seems like it's slow filtering the results in the WHERE clause, and I could physically see that happening in my database GUI - the forum with lots of attachments would return results immediately, but with the forum with not many attachments it was returning a row once every few seconds, so it looked like it was running through a huge number of rows and returning the few that matched as it found them, and had to go through a lot of data to find enough to return to reach the limit for that page. Which makes sense, if it's going through 10,000 attachments and only 50 of them are in that forum, it has to go through a lot to find enough for a page. For some reason the where condition for the node_id was just insanely inefficient, which I don't have the knowledge to understand as it's an indexed primary key. From what I can tell, it's a similar issue with the gallery where it's slow filtering by the album_id, and if that condition was moved to the join it might be a lot faster.

The only problem is that adding additional conditions to joins isn't supported by core XF (when using the Finder vs a raw query at least), I had to use Xon's StandardLib addon which allows for it. I think there could be benefits to XF's finder allowing other conditions (similar to Laravel's with function that accepts a callback to apply other conditions), and I wouldn't be surprised if there's other areas that could benefit from conditions being applied in the join itself, as it can massively reduce the number of rows being returned and filtered.
 
Last edited:
Actually, yeah, this fixes it here too - if I:
  • remove the media_date index hint
  • change the xf_mg_album join to be an inner join instead of a left join
  • put the album_id condition as part of the join
the query goes from 5+ seconds to 0.1s.
 
I've seen several cases where left join with a where join.id = ? style condition instead of inner join with the same where clause results in some fairly crazy performance impact as MySQL just picks the wrong join behavior.
 
I've seen several cases where left join with a where join.id = ? style condition instead of inner join with the same where clause results in some fairly crazy performance impact as MySQL just picks the wrong join behavior.
So literally just changing left join to inner join fixes it in that situation? Makes sense as a left join is expecting null rows in the foreign table (if that's the right way of phrasing that) whereas it requires a row with an inner join, and is why the condition on the join is so much more efficient as it's checking it while joining the row in in the first place, rather than returning a huge dataset it then has to filter out later.

With that in mind wouldn't it always be best to use inner joins anyway, as we'll always want there to be a row in the foreign table for the most part. I only tend to use left joins when I explicitly want to find rows that don't have a relation in the foreign table by checking if its primary key is null.
 
So literally just changing left join to inner join fixes it in that situation?
In the most recent case for one of my add-ons; just changing from left join to inner join went from multi-seconds to 1/100s of a second.

I don't know if that is the "fix" for this XFMG issue, since I don't have XFMG, but I would suggest try each of those changes in sequence and see which is the magic condition or if it is a mix of them.

Makes sense as a left join is expecting null rows in the foreign table (if that's the right way of phrasing that) whereas it requires a row with an inner join, and is why the condition on the join is so much more efficient as it's checking it while joining the row in in the first place, rather than returning a huge dataset it then has to filter out later.
Semantically, a left join which requires a non-null join result should be identical to an inner join. But the optimizer isn't propagating that logic condition from the where clause to the join clause.
 
It's also possible that we shouldn't be applying the hint if we know we're only selecting a subset of media items, such as within a category or album, but keep it for the main index, in some cases (maybe).
Definitly. The existing indexes album_id_media_date / category_id_media_date are much better in this case.
 
Back
Top Bottom