High SQL Usage

[Brad Mitchell]

My host is complaining again about the high amount of resources used by this plugin.

They think that the following query is responsible for high SQL usage.

===============================================================================================================================================
SELECT DISTINCT wp_pfh3w2370f_posts.*, wp_pfh3w2370f_postmeta.meta_value as EventStartDate, tribe_event_end_date.meta_value as EventEndDate FROM wp_pfh3w2370f_posts LEFT JOIN wp_pfh3w2370f_term_relationships ON (wp_pfh3w2370f_posts.ID = wp_pfh3w2370f_term_relationships.object_id) INNER JOIN wp_pfh3w2370f_postmeta ON ( wp_pfh3w2370f_posts.ID = wp_pfh3w2370f_postmeta.post_id ) INNER JOIN wp_pfh3w2370f_postmeta AS mt1 ON ( wp_pfh3w2370f_posts.ID = mt1.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 (53076) AND (
wp_pfh3w2370f_term_relationships.term_taxonomy_id IN (163,66)
) AND (
wp_pfh3w2370f_postmeta.meta_key = ‘_EventStartDate’
AND
(
mt1.meta_key = ‘_EventStartDate’
)
) AND wp_pfh3w2370f_posts.post_type = ‘tribe_events’ AND (wp_pfh3w2370f_posts.post_status = ‘publish’ OR wp_pfh3w2370f_posts.post_status = ‘acf-disabled’ OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-success’ OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-failed’ OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-schedule’ OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-pending’ OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-draft’) AND (wp_pfh3w2370f_postmeta.meta_value >= ‘2018-09-25 06:53:00’ OR (wp_pfh3w2370f_postmeta.meta_value <= ‘2018-09-25 06:53:00’ AND tribe_event_end_date.meta_value >= ‘2018-09-25 06:53:00’ )) ORDER BY EventStartDate ASC, wp_pfh3w2370f_postmeta.meta_value ASC
) a
GROUP BY IF( post_parent = 0, ID, post_parent )
ORDER BY EventStartDate ASC
===============================================================================================================================================

High SQL usage is consistent. Here is a graph showing MySQL usage over the past week: http://take.ms/uOjEa.

They think that the plugin is starting some automatic job and it is doing it for couple of hours then everything reverts to normal. During periods of high SQL usage, the website slows down and sometimes resource limits are hit, resulting in time out errors.

[Brendan]

Hi there,

Thanks for emailing support and sorry you are experiencing this issue again.

Would you be willing to share your system information? That will allow me to check for possible conflicts and see your current calendar settings. You can snag that by heading to Events > Help, copying the information that is at the bottom of the screen, then paste it here for me as a private reply.

How many events, venues, and organizers do you currently have? Do you experience a slow down in performance in specific pages? Please let me know which ones.

Few articles that may be helpful: Caching: What, when and how and Caching month view HTML in transients

Finally, there is a good article George put up here that groups a set of developer plugins. It’s a bit more technical than the others but will definitely be of help if you are into development. Especially check out the cron plugin and see what jobs are running and how often.

Let me know how it goes.

Thanks,
Brendan

[Brad Mitchell]

Hi,

The settings page for the plugin already has the cache month view enabled. The website also uses WP Super Cache in expert mode with preloading enabled.

There are about 5600 events, with the oldest event occurring on September 1, 2017. We regularly delete events more than a year old to keep the database clean.

Below is the system information that you asked for.

============================================================================================

HOME URL
https://www.northeastohioparent.com
SITE URL
https://www.northeastohioparent.com
SITE LANGUAGE
English
CHARACTER SET
UTF-8
NAME
Matt Thompson
EMAIL
[email protected]
INSTALL KEYS
events-calendar-pro = 4ec2################################2396
events-community = 19f4################################5a0d
WORDPRESS VERSION
4.9.8
PERMALINK STRUCTURE
https://www.northeastohioparent.com/%category%/%postname%/
PHP VERSION
7.0.32
PHP
max_execution_time = 90
memory_limit = 256M
upload_max_filesize = 32M
post_max_size = 32M
display_errors = Off
log_errors = 1
SERVER
Apache
SAPI
litespeed
PLUGINS
AdRotate Professional version 4.15 by Arnan de Gans(https://www.arnan.me/)
Advanced Custom Fields version 5.7.6 by Elliot Condon(http://www.elliotcondon.com/)
Advanced Excerpt version 4.2.5 by WPKube(https://wpkube.com)
Akismet Anti-Spam version 4.0.8 by Automattic(https://automattic.com/wordpress-plugins/)
Transient Cleaner version 1.5.6 by David Artiss(https://artiss.blog)
Autoptimize version 2.4.0 by Frank Goossens (futtta)(https://autoptimize.com/)
Awesome Author Info version 1.0 by Cyberspace Builder(http://www.cyberspacebuilder.com)
iThemes Security version 7.1.0 by iThemes(https://ithemes.com)
Cloudflare version 3.3.2 by John Wineman, Furkan Yilmaz, Junade Ali (Cloudflare Team)
Cyberspace Shortcodes version 1.0 by Cyberspace Builder(http://cyberspacebuilder.com)
Current Issue version 1.0 by Cosmin Stan
Custom Forms version 1.0 by Cosmin Stan
Custom Sidebars version 3.2.1 by WPMU DEV(http://premium.wpmudev.org/)
Download Monitor – Gravity Forms Extension version 4.0.1 by Never5(http://www.never5.com/)
Download Monitor version 4.1.1 by Never5(https://www.never5.com)
Easy Custom Auto Excerpt version 2.4.10 by tonjoo(https://www.tonjoostudio.com/)
The Events Calendar PRO version 4.4.32 by Modern Tribe, Inc.(http://m.tri.be/20)
EWWW Image Optimizer version 4.4.1 by Shane Bishop(https://ewww.io/)
Google Analytics for WordPress by MonsterInsights version 7.2.0 by MonsterInsights(https://www.monsterinsights.com/?utm_source=liteplugin&utm_medium=pluginheader&utm_campaign=authoruri&utm_content=7%2E0%2E0)
Gravity Forms version 2.3.4 by rocketgenius(https://www.rocketgenius.com)
Gravity Forms Stripe Add-On version 2.5 by rocketgenius(https://www.rocketgenius.com)
Hide Featured Image version 1.3.1 by shahpranaf(http://shahpranav.com/)
InPost Gallery version 2.1.2.1 by Rostislav Sofronov (https://www.pluginus.net/)
Lazy Load version 0.6.1
Mailchimp Custom Form version 1.0 by Cosmin Stan
MailChimp for WordPress version 4.2.5 by ibericode(https://ibericode.com/)
MailChimp for WordPress – Premium version 3.3.6 by ibericode(https://ibericode.com/)
Newsletter Template Creator version 1.0 by Nicu S(http://URI_Of_The_Plugin_Author)
Page Links To version 3.0.1 by Mark Jaquith(https://coveredweb.com/)
Permalinks to Category/Permalinks version 1.0.2 by Svetoslav Marinov (Slavi)(http://orbisius.com)
Blubrry PowerPress version 7.3 by Blubrry(http://www.blubrry.com/)
Profile Builder version 2.9.0 by Cozmoslabs(https://www.cozmoslabs.com/)
Relevanssi version 4.0.11 by Mikko Saari(http://www.mikkosaari.fi/)
Replace Image version 1.1.6 by Potent Plugins(http://potentplugins.com/?utm_source=replace-image&utm_medium=link&utm_campaign=wp-plugin-author-uri)
Search Everything version 8.1.9 by Sovrn, zemanta(http://www.sovrn.com)
Facebook Feed Plus version 1.0.0 by Scott Moses(http://www.cheekyapps.com/)
Sponsored content version 1.0 by Cosmin Stan
The Events Calendar: Community Events version 4.5.13.1 by Modern Tribe, Inc.(http://m.tri.be/21)
The Events Calendar version 4.6.23 by Modern Tribe, Inc.(http://m.tri.be/1x)
TinyMCE Advanced version 4.8.0 by Andrew Ozz(http://www.laptoptips.ca/)
UpdraftPlus – Backup/Restore version 1.15.2 by UpdraftPlus.Com, DavidAnderson(https://updraftplus.com)
URL Redirect version 1.3 by Ninja Press(http://www.ninjapress.net)
User Switching version 1.4.0 by John Blackbourn & contributors(https://github.com/johnbillion/user-switching/graphs/contributors)
Easy Video Widget Box version 1.6 by Purple Turtle Productions(http://www.w3bdesign.ca/)
Hustle version 6.0.4.2 by WPMU DEV(https://premium.wpmudev.org)
Yoast SEO version 8.3 by Team Yoast(https://yoa.st/1uk)
WordPress Default Featured Image version 1.5 by Sanny Srivastava(https://sannysrivastava.wordpress.com)
WP Crontrol version 1.6.2 by John Blackbourn & contributors(https://github.com/johnbillion/wp-crontrol/graphs/contributors)
WP External Links version 2.2.0 by Victor Villaverde Laan(http://www.finewebdev.com)
WP-PageNavi version 2.93 by Lester ‘GaMerZ’ Chan(https://lesterchan.net)
WP Super Cache version 1.6.4 by Automattic(https://automattic.com/)
WP User Frontend Pro – personal version 2.7.0 by weDevs(https://wedevs.com)
WP User Frontend version 2.9.2 by Tareq Hasan(https://tareq.co)
NETWORK PLUGINS
–
MU PLUGINS
–
THEME
Magic Mag Child
MULTISITE
–
SETTINGS
recurring_events_are_hidden = exposed
tribeEventsTemplate = default
tribeEventsBeforeHTML =
tribeEventsAfterHTML = Find the best family-friendly events in Greater Cleveland! Also be sure to Like us on Facebook where we giveaway Free tickets to great events like these.
previous_ecp_versions =
Array
(
[0] => 0
[1] => 3.4.1
[2] => 3.6.1
[3] => 4.1.2
[4] => 4.1.3
[5] => 4.2.2
[6] => 4.2.7
[7] => 4.3
[8] => 4.3.0.1
[9] => 4.3.1.1
[10] => 4.3.3
[11] => 4.3.4.1
[12] => 4.3.4.2
[13] => 4.4.0.1
[14] => 4.4.1.1
[15] => 4.4.2
[16] => 4.4.3
[17] => 4.4.4
[18] => 4.4.5
[19] => 4.5.1
[20] => 4.5.2
[21] => 4.5.2.1
[22] => 4.5.8.1
[23] => 4.5.9
[24] => 4.5.11
[25] => 4.6.1
[26] => 4.6.2
[27] => 4.6.7
[28] => 4.6.8
[29] => 4.6.9
[30] => 4.6.10.1
[31] => 4.6.11.1
[32] => 4.6.12
[33] => 4.6.13
[34] => 4.6.14.1
[35] => 4.6.18
[36] => 4.6.21
[37] => 4.6.22
[38] => 4.6.22.1
)
latest_ecp_version = 4.6.23
disable_metabox_custom_fields = 1
welcome_notice = 1
viewOption = list
custom-fields =
Array
(
[2] => Array
(
[name] => _ecp_custom_2
[label] => Organizer Name
[type] => text
[values] =>
)

[3] => Array
(
[name] => _ecp_custom_3
[label] => Organizer Phone
[type] => text
[values] =>
)

[4] => Array
(
[name] => _ecp_custom_4
[label] => Organizer Email
[type] => text
[values] =>
)

)
stylesheetOption = full
tribeEnableViews =
Array
(
[0] => list
[1] => month
[2] => week
[3] => day
)
tribeDisableTribeBar =
hideLocationSearch = 1
hideRelatedEvents =
monthEventAmount = 5
donate-link =
postsPerPage = 10
liveFiltersUpdate = 1
hideSubsequentRecurrencesDefault = 1
userToggleSubsequentRecurrences =
recurrenceMaxMonthsBefore = 12
recurrenceMaxMonthsAfter = 12
showComments =
showEventsInMainLoop =
eventsSlug = events
singleEventSlug = event
multiDayCutoff = 00:00
defaultCurrencySymbol = $
reverseCurrencyPosition =
embedGoogleMaps = 1
geoloc_default_geofence = 25
geoloc_default_unit = miles
embedGoogleMapsZoom = 10
debugEvents =
defaultValueReplace =
schema-version = 4.6.23
last-update-message = 4.2.7
earliest_date = 2017-09-01 00:00:00
earliest_date_markers =
Array
(
[0] => 40009
[1] => 40075
[2] => 40566
)
latest_date = 2067-04-04 18:00:00
latest_date_markers =
Array
(
[0] => 54155
)
enable_month_view_cache = 1
dateWithYearFormat = F j, Y
dateWithoutYearFormat = F j
monthAndYearFormat = F Y
dateTimeSeparator = @
timeRangeSeparator = –
datepickerFormat = 6
pro-schema-version = 4.4.32
week_view_hide_weekends =
weekDayFormat = D jS
eventsDefaultOrganizerID = 0
eventsDefaultVenueID = 0
eventsDefaultAddress =
eventsDefaultCity =
eventsDefaultState = OH
eventsDefaultProvince =
eventsDefaultZip =
defaultCountry =
Array
(
[0] => US
[1] => United States
)
eventsDefaultPhone =
tribeEventsCountries =
last-update-message-the-events-calendar = 4.6.22.1
mobile_default_view = default
custom-fields-max-index = 5
google_maps_js_api_key = AIza###############################DacE
tribe-events-community-schema-version = 4.5.13.1
trash-past-events = 9
delete-past-events = 12
COMMUNITY ADD
https://www.northeastohioparent.com/events/community/add
COMMUNITY LIST
https://www.northeastohioparent.com/events/community/list
COMMUNITY OPTIONS
maybeFlushRewrite =
allowAnonymousSubmissions = 1
useVisualEditor =
defaultStatus = pending
communityRewriteSlug = community
emailAlertsEnabled = 1
emailAlertsList = [email protected]
allowUsersToEditSubmissions = 1
allowUsersToDeleteSubmissions =
trashItemsVsDelete = 1
eventsPerPage = 10
eventListDateFormat = M j, Y
blockRolesFromAdmin =
blockRolesList =
blockRolesRedirect = https://www.northeastohioparent.com/event-thank-you/
defaultCommunityVenueID = 0
defaultCommunityOrganizerID = 0
single_geography_mode = 1
recaptchaPublicKey =
recaptchaPrivateKey =
WP TIMEZONE
America/New_York
WP GMT OFFSET
-4
DEFAULT PHP TIMEZONE
UTC
WP DATE FORMAT
F j, Y
WP TIME FORMAT
g:i a
WEEK STARTS ON
–
COMMON LIBRARY DIR
/home/northeas/public_html/wp-content/plugins/the-events-calendar/common/src/Tribe
COMMON LIBRARY VERSION
4.7.20

[Brendan]

Hi there,

Thanks for the info. Can you install the WP Crontrol and WP Query plugins from the link I posted earlier and check to see if anything stands out? Cron jobs with a high number of arguments are one thing to look for and the Query monitor will give us some details on what is running and taxing the server/DB.

Please reply with the findings.

Thanks,
Brendan

[Brad Mitchell]

Here are the hook names for the cron jobs for The Events Calendar according to WP Crontrol. None of these have any arguments.

– tribe_aggregator_process_insert_records
– tribe_events_pro_process_recurring_events
– tribe_trash_event_cron
– tribe_del_event_cron
– tribe-recurrence-cron

Query Monitor shows a few slow queries, and they are all for The Events Calendar. See a few of the queries below:

==========================================================

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_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 >= ‘2018-09-23 00:00:00’
AND wp_pfh3w2370f_postmeta.meta_value <= ‘2018-09-30 00:00:00’)
OR (tribe_event_end_date.meta_value >= ‘2018-09-23 00:00:00’
AND wp_pfh3w2370f_postmeta.meta_value <= ‘2018-09-30 00:00:00’ )
OR (wp_pfh3w2370f_postmeta.meta_value < ‘2018-09-23 00:00:00’
AND tribe_event_end_date.meta_value >= ‘2018-09-30 00:00:00’ ))
GROUP BY wp_pfh3w2370f_posts.ID
ORDER BY EventStartDate ASC, wp_pfh3w2370f_posts.post_date ASC

==========================================================

SELECT SQL_CALC_FOUND_ROWS *
FROM (
SELECT DISTINCT wp_pfh3w2370f_posts.*, wp_pfh3w2370f_postmeta.meta_value as EventStartDate, 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_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 = ‘acf-disabled’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-success’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-failed’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-schedule’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-pending’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-draft’
OR wp_pfh3w2370f_posts.post_status = ‘private’)
AND (wp_pfh3w2370f_postmeta.meta_value >= ‘2018-09-29 11:09:00’
OR (wp_pfh3w2370f_postmeta.meta_value <= ‘2018-09-29 11:09:00’
AND tribe_event_end_date.meta_value >= ‘2018-09-29 11:09:00’ ))
ORDER BY EventStartDate ASC, wp_pfh3w2370f_posts.post_date ASC ) a
GROUP BY IF( post_parent = 0, ID, post_parent )
ORDER BY EventStartDate ASC
LIMIT 0, 10

==========================================================

SELECT SQL_CALC_FOUND_ROWS *
FROM (
SELECT DISTINCT wp_pfh3w2370f_posts.*, wp_pfh3w2370f_postmeta.meta_value as EventStartDate, 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_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 = ‘acf-disabled’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-success’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-failed’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-schedule’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-pending’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-draft’
OR wp_pfh3w2370f_posts.post_status = ‘private’)
AND tribe_event_end_date.meta_value < ‘2018-09-29 11:09:12’
ORDER BY EventStartDate ASC, wp_pfh3w2370f_posts.post_date ASC ) a
GROUP BY IF( post_parent = 0, ID, post_parent )
ORDER BY EventStartDate ASC
LIMIT 0, 1

==========================================================

SELECT SQL_CALC_FOUND_ROWS *
FROM (
SELECT DISTINCT wp_pfh3w2370f_posts.*, wp_pfh3w2370f_postmeta.meta_value as EventStartDate, 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_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 = ‘acf-disabled’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-success’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-failed’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-schedule’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-pending’
OR wp_pfh3w2370f_posts.post_status = ‘tribe-ea-draft’
OR wp_pfh3w2370f_posts.post_status = ‘private’)
AND tribe_event_end_date.meta_value < ‘2018-09-29 11:09:13’
ORDER BY EventStartDate ASC, wp_pfh3w2370f_posts.post_date ASC ) a
GROUP BY IF( post_parent = 0, ID, post_parent )
ORDER BY EventStartDate ASC
LIMIT 0, 1

[Brendan]

Hi there,

So the calendar view does look like its loading quite a few events so queries will take longer as expected. I also see you’ve already implemented decent caching options. Performance complaints are not uncommon but there’s a limit to what we can do within the current structure especially when displaying a large number of events.

I would suggest looking at a more powerful hosting solution based on the number of events. We do have a contact (Don Grover) who has solid experience in optimizing TEC sites for better performance, though of course, that would cost to get his help. Might be an option though if you would like his information.

Thanks,
Brendan

[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