Query Overloading Server, Causing 504 Errors

[Fletcher]

The following query is overloading my WP Engine server, causing regular 504 errors on the 80+ websites I host. It has shown up in our mysql slow logs more than 2,000 times in the past 12 hours. Would you mind helping me fix this issue? Thanks!

# Time: 2017-02-27T13:14:10.967755Z
# User@Host: kvne[kvne] @ [127.0.0.1] Id: 205696
# Schema: wp_kvne Last_errno: 0 Killed: 0
# Query_time: 7.501045 Lock_time: 0.000443 Rows_sent: 1 Rows_examined: 1336595 Rows_affected: 0
# Bytes_sent: 1882
SET timestamp=1488201250;
SELECT
SQL_CALC_FOUND_ROWS *
FROM (
SELECT DISTINCT wp_posts.*, wp_postmeta.meta_value as EventStartDate, 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 (49052) AND (
wp_postmeta.meta_key = ‘_EventStartDate’
AND
(
( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) < ‘2017-02-07 09:30:00’ )
OR (
( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) = ‘2017-02-07 09:30:00’ )
AND mt1.post_id < 49052
)
)
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’) ORDER BY EventStartDate DESC, wp_posts.ID DESC
) a
GROUP BY IF( post_parent = 0, ID, post_parent )
ORDER BY EventStartDate DESC
LIMIT 0, 1
/* From [kvne.com/event/square-1/2017-02-07/] in [/nas/content/live/kvne/wp-content/plugins/the-events-calendar/src/Tribe/Query.php:1017] */;

[Fletcher]

Just checking in on this. It’s affecting every website on our entire server, so I’m feeling a bit of a fire to get this resolved. Thanks in advance for your help!

[Fletcher]

Please help!

[Fletcher]

I’m getting client complaints and am between a rock and a hard place, so I hate to bother, but I would love to get his resolved quickly. Thank you!

[George]

Hi @Fletcher,

I am genuinely sorry about your problems.

The pace of support on these forums is one reply every 24–48 hours, as elaborated upon here: https://theeventscalendar.com/knowledgebase/what-support-is-provided-for-license-holders/

But our queues are ordered by “last updated” date, to help us ensure we try and hit the oldest threads first to minimize people’s wait times.

Your posts in this thread that came after the first post worked against you here—they kept moving your thread back up in the “last updated” queue, thus delaying a response.

So, hopefully that helps going forward — as frustrating as it can be, sit tight once you make your first post, it will help us get to you!

[George]

Now, with the above information all being said, I wanted to confirm with you that we are now taking a look at this.

Sit tight—I’m reviewing the information in that code and may rope in another developer to assist me, as well.

Another reply is forthcoming, stay tuned!

— George

[George]

Please answer each of the following questions:

1. You say, “my WP Engine server, causing regular 504 errors on the 80+ websites I host”. Is this a multisite installation? Can you elaborate on the “80+ websites that I host” comment? Do you mean you have literally 80 separate websites, all of them using Events Calendar Pro on WP Engine, and all of them generating these same errors separately?

2. Next, can you please clarify exactly where the 504 errors arise? Is it only when navigating to Month View of the main calendar, for example? Or does it just happen anywhere on the front-end? Does it happen on the admin, too?

3. Finally (for now!), can you please clarify the volume of events on your site? Do you have thousands of events per month, for example? Hundreds per month? A couple dozen? Just a rough estimate would be helpful.

Thanks!
George

[Fletcher]

Thanks for the heads up on how the support replies work. That’s helpful to know.

I host 80+ WordPress installs, most of which are single site installs and a few of which are multisite. Of those installs, only a couple or 3 use The Events Calendar. I’m under the impression from WP Engine that all of the errors are coming from the KVNE website.

The 504 errors aren’t just happening for those using the calendar. According to WP Engine, the errors are happening on all of the sites because of the load the Events Calendar scripts are placing on the server. The 504s happen on the front and backend of our websites.

I don’t know the exact volume of events listed, but I would guess that it’s in the hundreds per month.

Thanks for your help!

[Brook]

Howdy @Fletcher,

George brought this topic to my attention since I am very passionate about performance. I would love to help you with this.

Would you be interested in sharing a copy of those log files, which show these queries 2000 times? A lot of event queries look distinctly similar to one another, but the devil is in the details. I am interested to understand if all of the queries are straight forward ones like what you shared, and for any other information I can glean from these files. If this does interest, maybe you could upload a zipped copy to Dropbox or something similar, and share a link here in a private reply.

One tip I can offer you right off the bat is to adjust the “Number of events to show per page” setting in WP-Admin > Events > Settings. It’s currently set to show 9999, which is a lot. That’s great if your server can handle it, but you would need something significantly more powerful than a shared host like WP Engine to accommodate this. That’s not meant to detract from WPEngine, whom I adore, it’s just an acknowledgement that WordPress is only designed to show 10-20 posts per page. Anything much more than that is pushing it.

Further, you might also adjust “Month view events per day” setting in WP-Admin > Events > Settings > Display to a lower number like 2 or 3, and check the box “Enable the Month View Cache”.

Cheers!

– Brook

[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