Calendar Query causing processes to take longer than 60 seconds-high server load

[Henry Rankin]

Hi, I also have submitted a sales request for information about Enterprise work. But in the meantime, my support at WP Engine has asked me to reach out to you regarding the extreme server load that is being caused by the load below.

For a little context, we publish a large, well-trafficked “Things to Do” website. On days like July 4th, we can easily have up to 80 events. We typically have 10 to 60 events on every day. I recognized this is likely outside the scale of what the plugin was designed to do (we’re looking for a replacement – hence the sales request for Enterprise work). But in the meatime, it’s beating down our page load and making it almost impossible for my staff to update the site.

According to WP Engine, here is the guilty query below the line. Can you tell me what you recommend? I will pass this along to WP Engine to help get this resolved.

2078020 houston365 127.0.0.1:54942 wp_houston365 Query 0 SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, MIN(wp_postmeta.meta_value) as EventStartDate, MIN(tribe_event_end_date.meta_value) as EventEndDate FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id AND mt2.meta_key = ‘_EventHideFromUpcoming’ ) LEFT JOIN wp_postmeta as tribe_event_end_date ON ( wp_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ ) WHERE 1=1 AND wp_posts.ID NOT IN (73435) AND ( wp_postmeta.meta_key = ‘_EventStartDate’ AND ( ( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) < ‘2017-07-06 11:00:00’ ) OR ( ( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) = ‘2017-07-06 11:00:00’ ) AND mt1.post_id < 73435 ) ) AND mt2.post_id IS NULL ) AND wp_posts.post_type = ‘tribe_events’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘tribe-ea-success’ OR wp_posts.post_status = ‘tribe-ea-failed’ OR wp_posts.post_status = ‘tribe-ea-schedule’ OR wp_posts.post_status = ‘tribe-ea-pending’ OR wp_posts.post_status = ‘tribe-ea-draft’) GROUP BY wp_posts.ID ORDER BY EventStartDate DESC, wp_posts.ID DESC LIMIT 0, 1 /* From [365thingsinhouston.com/calendar/shadow-puppet-theatre-woodlands-childrens-museum-2/2017-07-06/] in [/nas/content/live/houston365/wp-content/plugins/the-events-calendar/src/Tribe/Query.php:1017] */

[Andras]

Hello Henry,

Thanks for using our plugins and welcome to the forums!

I’m sorry you are having load issues. I’d be happy to try to help you solve this.

As a first, can you let me know when that query is fired?

Where do you experience slow load times? In the dashboard or on the front-end? Which page(s) specifically?

Please let me know.

Also I would like to recommend 2 readings from our knowledgebase:

The Calendar is Loading Slowly

Caching Basics

Looking forward to helping you with this.

Cheers,
Andras

[Henry Rankin]

I’ve asked the folks at WP Engine to provide more insight into when that query is fired.

However, I can answer the rest. It affected every single page on the site. It was most difficult for me or any of my staff who were logged into Word Press, but even the general public was getting Error 504 messages. The Events Calendar plugin was creating a high volume of server queries and maxing out its CPU. This has been an ongoing problem of the plugin for us (We have 100s of events on a single day at times). This problem began on June 28 with I upgraded the Plugins from the following versions:

The Events Calendar – Version 4.2.7
The Events Calendar PRO – Version 4.2.6

When I originally updated the versions above in 2016, the new versions crashed our site. The heavy CPU usage began on the same date I tried upgrading the 2 plugins to the current version. I have downgraded the 2 plugins back to the older versions above and the problem has gone away.

That said, The Events Calendar plugins still place a heavier usage on the CPU than they should. As I understand it, it is partially because of the automatic linking of recurring events. I could be wrong on that.

I also have a request in to your sales department to identify what may be available for publishers like ours, that aggregate a high number of events for mass consumption.

[Henry Rankin]

Here’s the input from WP Engine:

That query is fired on line 1017 on /nas/content/live/houston365/wp-content/plugins/the-events-calendar/src/Tribe/Query.php when viewing http://365thingsinhouston.com/calendar/shadow-puppet-theatre-woodlands-childrens-museum-2/2017-07-06/ The specific query itself is:

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, MIN(wp_postmeta.meta_value) as EventStartDate, MIN(tribe_event_end_date.meta_value) as EventEndDate FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id AND mt2.meta_key = ‘_EventHideFromUpcoming’ ) LEFT JOIN wp_postmeta as tribe_event_end_date ON ( wp_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ ) WHERE 1=1 AND wp_posts.ID NOT IN (73435) AND ( wp_postmeta.meta_key = ‘_EventStartDate’ AND ( ( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) < ‘2017-07-06 11:00:00’ ) OR ( ( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) = ‘2017-07-06 11:00:00’ ) AND mt1.post_id < 73435 ) ) AND mt2.post_id IS NULL ) AND wp_posts.post_type = ‘tribe_events’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘tribe-ea-success’ OR wp_posts.post_status = ‘tribe-ea-failed’ OR wp_posts.post_status = ‘tribe-ea-schedule’ OR wp_posts.post_status = ‘tribe-ea-pending’ OR wp_posts.post_status = ‘tribe-ea-draft’) GROUP BY wp_posts.ID ORDER BY EventStartDate DESC, wp_posts.ID DESC LIMIT 0, 1 /*
I hope this helps The Events Calendar dev’s gain more insight into the issue.

[Andras]

Hi Henry,

Thanks for providing more details on the issue for us.

While we look into this I have 2 more questions that can help us narrowing down the cause of the issue.

1. What kind of hosting package do you have? Are you on shared hosting or you have a dedicated server? For the amount of events you mentioned a shared hosting space might not provide you with ample resources to serve the site.

2. Do you have any scheduled imports set up with Event Aggregator? How many and how often do they run?

Thanks!

Andras

[Henry Rankin]

1. I have a hosting package on WP Engine, designed for a million monthly sessions (we’re well below that right now). I expect your office is familiar with WP Engine, as some of your team was visiting their office in Austin back in May. The hosting service is optimized for speed, and per my notes above, are the ones who identified the problematic code.

In the attached image, you can see a chart of the transaction time on my site, once I updated the events calendar to the current version on June 28. It’s the action that started the spike in transaction time. (you can see the starting point on the far left hand side). The drop coincides with when I downgraded The Events Calendar to Version 4.2.7 & Events Calendar Pro to version 4.2.6.

(The dip in the middle of the peak transaction time came on July 4 when my account was given an emergency upgrade to handle all the transaction requests. As you can see, it still didn’t come close to compensating for the toll the calendar plugins put on the server)

Before moving to WP Engine, I was on a dedicated server, which repeatedly had too much demand on the CPU as a result of the Events Calendar (version 4.2.7 & 4.2.6). When we deactivated the plugins, the transaction time returned to normal.

2. I originally used Event aggragator but didn’t renew it and I deactivated it years ago. So it never ever runs. It also put too much demand on the server and was not worth the effort. I pay staff to manually create the event entries.

• This reply was modified 8 years, 10 months ago by Henry Rankin.

[Andras]

Henry,

Thank you so much for the detailed information you provided! This gives us valuable insight to the issue at hand. I will forward it to our developers and will increase the priority of the bugticket.

I am going to set the status of this ticket to “pending fix” and we will update it once the fix is released.

If you have any new questions or issues please create a new ticket and we’ll be happy to help.

 

Thanks and cheers,
Andras

[Henry Rankin]

Thank you Andras,

I’m also attaching a screen shot of the resource load on the server at WP Engine when loading my site. As you can see The Events Calendar is a massive resource hog in transaction time. (the 3rd – Newspaper is our WP theme)

I’m also currently evaluating alternatives that might not be as unstable and resource intensive as the The Event Calendar. I do not want to change, but I may not have a choice. I cannot be the only publisher who needs to schedule events at this volume.

Your updates are welcome

[Andras]

Hi Henry,

Thanks for this as well.

I can even give you an update. A developer has been assigned to the issue so we will be looking into this soon.

Cheers,
Andras

[Henry Rankin]

Hi, it’s been about a week. Any updates on this issue? I’m currently evaluating other calendar options. I’d like to stay with The Events Calendar, if the massive drain on resources can be fixed. I’ve reached out to the Enterprise Sales team and just got an email 2 weeks ago saying that they’d get back to me in a week.

[Andras]

Hi Henry,

Thanks for pinging me. We are actively looking into the issue and considering it for our next Maintenance Release. It is on high priority at the moment.

I checked with the sales team and they didn’t find any email referencing your name or the email address linked to your account here. If you can share with me the contact details you used I can ping them directly to check and get back to you as soon as possible.

Cheers,
Andras

[Henry Rankin]

Thanks Andras. I received a reply from Caroline at Support in reply to my sales enquiry (submitted through your site on 7/5/17). Caroline initially replied on 7/6 with a message that the product team was working on something and would get back to me within a week. I emailed her yesterday 7/18, after following up with you and got a message back today that Barry on the product team had emailed me. I never received it, so asked her to resend.

I’m not trying to create duplicate efforts here, just find a solution in the fastest way. Again, I would pay for a service or product that eliminates the massive SQL demand on my server and slows down my website.

[Andras]

Hi Henry,

Thanks for following up on my request.

I’d like to point out that according to our support policy we provide premium support for our plugins only through our premium forums here. Getting in touch with us through email for plugin support will likely redirect you back here and while not taking the case further, it is taking away time from our team members. Thanks for your understanding.

In the meantime I talked to Barry and he confirmed to me that he got in touch with you.

Again, we are actively looking into this issue and will try to ship a fix as soon as time allows. That will surely not be 3-4 days, but hopefully also not longer than 3-4 weeks. It is a complex issue that needs a lot of thought in order not to create further issues down the road.

Thanks for your patience and for hanging in there.

Cheers,
Andras

[Victor]

Hello Henry!

Just wanted to share with you that a new maintenance release (for the Week of 2 October 2017) is out, including a tweak in Filter Bar that improves performance by swapping out LEFT JOINs for INNER JOINs in SQL queries.

Find out more about this release → https://theeventscalendar.com/maintenance-release-week-2-october-2017/

In addition to this improvement, we are also planning to support setting a limit on the number of filters that can be applied concurrently.

For the moment, we’d appreciate if you could update the plugins and let us know how this tweak works for your site.

Best,
Victor

[Support Droid]

Hey there! This thread has been pretty quiet for the last three weeks, so we’re going to go ahead and close it to avoid confusion with other topics. If you’re still looking for help with this, please do open a new thread, reference this one and we’d be more than happy to continue the conversation over there.

Thanks so much!
The Events Calendar Support Team

[Author]

Posts