Calendar Partial fix Sitemap extremely slow when processing events

Affected version
2.3.1

Mouth

Well-Known Member
Following on from Calendar - Cannot Reproduce Rebuilding sitemap - extremely slow when processing Events this is till very much an issue ...

Take this sitemap log, I upgraded from xF 1.5 to 2.2 (and thus this addon too) two weeks ago on Feb 23rd ....

Screen Shot 2021-03-10 at 17.53.16.png

.... sitemap generation went from ~1min to now between 2 and 4 hours.

You can see that, yesterday, I even changed the sitemap generation cron launch time to be in the wee small hours of the morning, so it's during 'quiet' time and not a busy time of day.

And whilst the sitemap generation is running, it 'pegs' mysql CPU usage ...

Screen Shot 2021-03-10 at 18.01.59.png

If I exclude/untick Event content from sitemap options ( /admin.php?options/groups/sitemap/ ) then it generates in less than 1 min ...

Screen Shot 2021-03-10 at 18.07.26.png

If I tick/enable Event content for the sitemap again, then a cli rebuild of the sitemap shows that it's Events being the issue, this below screenshot taken 40 secs after the rebuild was started ...

Screen Shot 2021-03-10 at 18.10.22.png

An untick/exclude Event content from the sitemap, and it takes approx. 5 secs to complete ....

Screen Shot 2021-03-10 at 18.11.35.png


From the mysql processing pegging CPU, it seems perhaps an index or data issue with the Calendar addon data, either from the upgrade or resulting from the upgrade?

What further information do you require to help narrow this down and resolve it?
 

NixFifty

Administrator
Staff member
Thanks for the additional details around this. I'll try it again and hit it with a profiler to see if I can spot what might be happening. I'll be in touch if I need anything further.
 

Mouth

Well-Known Member
Thanks for the additional details around this. I'll try it again and hit it with a profiler to see if I can spot what might be happening. I'll be in touch if I need anything further.
Were you able to identify an issue?
 

NixFifty

Administrator
Staff member
Not looked in to it yet, moving house so haven’t been working over the last couple of days.

I will be looking in to it this week though before I push out the release.
 

Mouth

Well-Known Member
I will be looking in to it this week though before I push out the release.
Any progress yet?
Calendar, in general, also seems a lot slower, in responsiveness and page load, than the rest of my site. Perhaps indexes not recreated, or new ones not created, during xf1 -> xf2 version upgrade? Where could I get a list of relevant table indexes to compare against my DB?
 

NixFifty

Administrator
Staff member
Any progress yet?
Calendar, in general, also seems a lot slower, in responsiveness and page load, than the rest of my site. Perhaps indexes not recreated, or new ones not created, during xf1 -> xf2 version upgrade? Where could I get a list of relevant table indexes to compare against my DB?
Not yet. Moving house took a bit longer than I’d have liked so I’ve been catching up with things since then.

In terms of missing indexes, it’s unlikely there are any missing but you can check the Setup.php file and Install/Data/MySQL.php files to see what indexes are added.
 

NixFifty

Administrator
Staff member
I tested this on a pretty standard machine with a collection of ~80,000 events and the rebuild was still pretty snappy taking just over a minute. I couldn't spot anything obviously amiss but I did make some minor changes to optimise the process further.

I've included them in the next version so I'd be curious to hear if it persists for you.
 
Top