Be careful when using Events By Week widget – massive resource usage

Hi @bozzmedia

Thanks for reporting this!

We ran some test on a staging site and have not encountered a similar load.

I would love to help you track down the source of the issue, also it can help us find out if the issue is really with our plugin, the widget, or something else.

Maybe you tried already, but in case not, could you please run a conflict test and see if you get the same experience?

Also, I’d like to ask how many events do you have in your calendar and how many of those are recurring events.
Do you have tickets or RSVPs set up for events?

Thanks and looking forward to tacking this with your help!
Andras

4,000 events, many instances of recurring events too.

Yes, we checked for conflicts and it happened either way.

I took a look at our settings and noticed we have set recurrences to 24 months in the past and 24 months into the future — I can see that being part of the issue. That said, this is a capable server hardware wise so I’m surprised there isn’t a way to optimize the db calls in this particular widget — we don’t seem to run into the same issue using the main calendar page.

Thanks!

Hi @bozzmedia

Thank you for providing us with that information. We’ve not been able to replicate the issue, unfortunately. Have you tried using a plugin like Query Monitor to isolate the DB query that might be slowing things down for you?

I hope that helps!

Thanks,
Jaime

We managed to clean up past and future events to get the event # down significantly which has helped.

We have used query monitor to narrow the issue to TEC event related queries. It seems to be an issue on the backend as well, particularly when the # of events is in the thousands.

One example on the main WP dashboard of slow query:

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, IF (wp_posts.post_type = 'tribe_events', wp_postmeta.meta_value, wp_posts.post_date) AS post_date
FROM wp_posts
LEFT JOIN wp_postmeta as wp_postmeta on wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = '_EventStartDateUTC'
WHERE 1=1
AND wp_posts.post_type IN ('post', 'page', 'attachment', 'rl_gallery', 'tribe_venue', 'tribe_events')
AND ((wp_posts.post_status = 'publish'))
ORDER BY post_date DESC
LIMIT 0, 100

On the frontend of the month view of the calendar, slow query:

SELECT wp_posts.ID, CAST( orderby_event_date_utc_meta.meta_value AS DATETIME ) AS event_date_utc, CAST( orderby_event_duration_meta.meta_value AS DECIMAL ) AS event_duration
FROM wp_posts
LEFT JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = '_EventHideFromUpcoming' )
LEFT JOIN wp_postmeta tribe_start_date_utc
ON ( wp_posts.ID = tribe_start_date_utc.post_id
AND tribe_start_date_utc.meta_key = '_EventStartDateUTC' )
LEFT JOIN wp_postmeta tribe_end_date_utc
ON ( wp_posts.ID = tribe_end_date_utc.post_id
AND tribe_end_date_utc.meta_key = '_EventEndDateUTC' )
LEFT JOIN wp_postmeta AS orderby_event_date_utc_meta
ON ( orderby_event_date_utc_meta.post_id = wp_posts.ID
AND orderby_event_date_utc_meta.meta_key = '_EventStartDateUTC' )
LEFT JOIN wp_postmeta AS orderby_event_duration_meta
ON ( orderby_event_duration_meta.post_id = wp_posts.ID
AND orderby_event_duration_meta.meta_key = '_EventDuration' )
WHERE 1=1
AND ( wp_postmeta.post_id IS NULL )
AND wp_posts.post_type = 'tribe_events'
AND ((wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'))
AND (( TIMESTAMPDIFF ( SECOND, tribe_start_date_utc.meta_value, '2022-03-07 07:59:59' ) >= 2
AND TIMESTAMPDIFF ( SECOND, '2022-01-30 08:00:00', tribe_end_date_utc.meta_value ) >= 2 ))
GROUP BY wp_posts.ID
ORDER BY event_date_utc ASC, event_duration ASC, wp_posts.menu_order ASC

Are there suggested indexes for some of the events tables that would improve this performance? Thanks again.

Hi @bozzmedia

Thanks for your patience on this! I needed to pull in some help from a dev. Here’s what I got:

The queries look normal. As in: they are the ones the plugins are generating.

You are correct when you say the queries are slow, the number of JOINs will grind them down.

To give a bit of an explanation:
An Event, to be called such, is a union of a post and its meta that characterizes it as an Event. Each meta is an entry in the postmeta table. Each time we need to filter events by a meta key (start_date, start_date_utc, end_date, end_date_utc, hide_from_upcoming …) we need to JOIN the posts table with the meta one.

The reason we, currently, use caching for Month and Week View is exactly that: avoid calling those expensive queries on each request.
That, however, doesn’t work on the back-end.

Also, the Events By Week widget is likely not being cached. This is something that we should do, so I will ticket this.

Andras

First, thank you very much for the detailed and thorough response. Worth the wait ??

Aha, well that makes me feel a little less anxious that the problem is related to caching and that particular widget because it was alone bringing the server to a crawl.

I appreciate you also pointing out this caching doesn’t work on the backend, our Event Manager page was unable to load until I got events under 1,000 posts. Redis object caching also helped tremendously.

Looking forward to utilizing caching in that widget, if you’re able to post back here when that ends up in a production release I would appreciate it. Thanks again!

HI @bozzmedia

Did a double check and I didn’t manage to get back to you yesterday anymore.

If you have “Enable the month view cache” checked under Events > Settings > Display tab, then both month view and week view are being cached, including the month and week related widgets.

Also, when you add a new event, then the cache gets invalidated and it’s re-created, re-cached on the next request. As a result, if you are adding events frequently, then the cache will be recreated frequently as well.

A workaround for the back-end could be using the event list as the default and limiting the number of events to 20-50 per page. If you are not using tickets, then it could be higher.
Using the Advanced Post Manager plugin (if you have Events Calendar Pro) will give you some nice filtering options for the back-end.

I know it doesn’t look as nice as the event manager. Just wanted to share this other option.

Thousands of events, just like thousands of posts or pages will have an impact on performance, unfortunately, due to how the data is structured (posts and postmeta).

Andras

Hi @bozzmedia,

Since the topic has been silent for a while and you also opened a ticket with our premium support team I will mark this as resolved here.

If that is not the case, please let me know and I will gladly remove the “Resolved” tag and try to help you further. Or you can create a new topic with the problem if you prefer.

Cheers,
Andras