Calendar Partial fix Sitemap extremely slow when processing events

[Mouth]

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



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



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



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



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




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?

 

[Naz]

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]

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?

 

[Naz]

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]

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?

 

[Naz]

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.

 

[Naz]

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.