Slow Database Performance

[Brad Mitchell]

I’m trying to resolve slow database performance on a site.

The Query Monitor plugin shows all database queries and alerts of slow queries (any query that takes >0.05 seconds). On every page that includes a calendar from The Events Calendar it complains about the following query. It takes 0.3569 seconds.

SELECT DISTINCT wp_pfh3w2370f_posts.*, MIN(wp_pfh3w2370f_postmeta.meta_value) as EventStartDate, MIN(tribe_event_end_date.meta_value) as EventEndDate
FROM wp_pfh3w2370f_posts
INNER JOIN wp_pfh3w2370f_postmeta
ON ( wp_pfh3w2370f_posts.ID = wp_pfh3w2370f_postmeta.post_id )
LEFT JOIN wp_pfh3w2370f_postmeta as tribe_event_end_date
ON ( wp_pfh3w2370f_posts.ID = tribe_event_end_date.post_id
AND tribe_event_end_date.meta_key = ‘_EventEndDate’ )
WHERE 1=1
AND wp_pfh3w2370f_posts.ID NOT IN (12982)
AND ( wp_pfh3w2370f_postmeta.meta_key = ‘_EventStartDate’ )
AND wp_pfh3w2370f_posts.post_type = ‘tribe_events’
AND ((wp_pfh3w2370f_posts.post_status = ‘publish’
OR wp_pfh3w2370f_posts.post_status = ‘private’))
AND ((wp_pfh3w2370f_postmeta.meta_value >= ‘2016-09-26 00:00:00’
AND wp_pfh3w2370f_postmeta.meta_value <= ‘2016-10-03 00:00:00’)
OR (tribe_event_end_date.meta_value >= ‘2016-09-26 00:00:00’
AND wp_pfh3w2370f_postmeta.meta_value <= ‘2016-10-03 00:00:00’ )
OR (wp_pfh3w2370f_postmeta.meta_value < ‘2016-09-26 00:00:00’
AND tribe_event_end_date.meta_value >= ‘2016-10-03 00:00:00’ ))
GROUP BY wp_pfh3w2370f_posts.ID
ORDER BY EventStartDate ASC, wp_pfh3w2370f_posts.post_date ASC
WP_Query->get_posts()
wp-includes/query.php:3655

The site has over 4500 events.

I upgraded the site to 4.2.2.1 (per https://theeventscalendar.com/release-events-calendar-pro-4-2-2-1/) and cleaned up duplicate events from the database using https://gist.github.com/elimn/a998d7b67f6166dab5e17ec071eb35ee.

I’ve searched the forums and the above query and concerns about database performance come up a lot, but I haven’t found any solutions. The site uses WP Super Cache.

• This topic was modified 7 years, 7 months ago by Brad Mitchell.

[Cliff]

Hi Drew.

Thanks for the specifics here.

I see from your system information report that you’re using PRO version 4.2.6 (the latest), but then you said you upgraded to 4.2.2.1 (from July). If you’re not on 4.2.6, please let me know.

As for the runaway recurring events issue, we now have this plugin available: tribe-extension-recurring-cleanup.zip (Recurring Events fixer plugin wp-admin page preview) to make it easy to determine if you have any leftovers from that bug (sorry again about that).

Regarding performance, we’ve seen sites with more than double this number of events work fine and sites with many fewer events struggle significantly. It is on our to-do list to see where we can make our plugins more performant, but I’m uncertain if this specific query is on our developers’ radar.

Could you please send me a screenshot of the Debug Bar output for this specific issue?

Thanks.

[Brad Mitchell]

I’m running the latest version of The Events Calendar Pro and already ran the query to clean up duplicate recurring events.

Attached is a screenshot of the debug bar.

[Cliff]

Thanks for that. Could you please display the query it is identifying as slow? (It won’t display in the WP Admin Bar.)

[Brad Mitchell]

The SQL query is in my first post on this thread.

[Brad Mitchell]

A screenshot of the query and locations is attached.

[Cliff]

Thanks. That’s what I was looking for.

How many Events do you have (you can look in wp-admin > Events), including posts in the Trash that haven’t been emptied?

Do you have a lot of Additional Fields or any other postmeta values that you know of?

Anything else a little out of ordinary or at larger scale than a smaller site would have? It might give us an idea where/how else to look.

Thanks.

[Brad Mitchell]

The site has 4600 events and 1200 posts.

There aren’t any additional/custom fields setup through the Events Calendar.

[Cliff]

Drew, we’ve seen quite a few sites with thousands of events that run well.

You might want to ask your host about setting up object caching (memcached or similar) and query caching; we cannot advise how to set such things up.

Another idea is to remove (i.e. “clean up”) expired/unneeded event posts (including revisions if you have those enabled).

We also have a Performance Considerations KB article that may offer some guidance.

[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