Too Many System Resources Used for MYSQL

[Carol Turnbull]

Hello Support,

The server is reporting too many system resources being used. I have tweaked the calendar using the suggestions from other posts including caching, reducing the amount of posts being displayed. Tried a different view other than month view (week looks terrible!) so anything else is unacceptable. These tweaks have helped, but not quite there yet.

We’ve been running this calendar for at least 3 years now. Is it possible that there are too many old entries that is causing the database to be slow and use too many resources? Is there a way to clean the database to get rid of past years events?

Maybe the error log will point as to what exactly is slowing things down? Additional pointers in the right direction would be appreciated.

Here is what the server spit out:
CPU_TIME:991 table_rows_read:70749375 SELECTS:10614 ROWS_UPDATED:9 ROWS_FETCHED:82667 BUSY_TIME:1180 ONNECTED_TIME:1284 BYTES_SENT:11366112 BYTES_RECEIVED:2136277 WAIT_TIME:189

Top table row reads:
DB_USER: csvac_wrdp1 — TOTAL_CONNECTIONS: 110 — CONNECTED_TIME: 1284 — CPU_TIME: 991 — TABLE_ROW_READS: 70749376 — SELECT_COMMANDS: 10615 — UPDATE_COMMANDS: — BUSY_TIME: 1180 — BYTES_SENT: 11366112 — BYTES_RECEIVED: 2136329 — WAIT_TIME (IO): 189

Top WAIT (IO) TIME:
DB_USER: csvac_wrdp1 — TOTAL_CONNECTIONS: 110 — CONNECTED_TIME: 1284 — CPU_TIME: 991 — TABLE_ROW_READS: 70749376 — SELECT_COMMANDS: 10615 — UPDATE_COMMANDS: — BUSY_TIME: 1180 — BYTES_SENT: 11366112 — BYTES_RECEIVED: 2136329 — WAIT_TIME (IO): 189

csvac 706823 10.0 0.2 292920 44940 ? SN 21:59 0:01 /opt/php71/bin/php-cgi /home/csvac/public_html/index.php
csvac 706912 9.1 0.2 292920 44932 ? SN 21:59 0:00 /opt/php71/bin/php-cgi /home/csvac/public_html/index.php
csvac 706920 10.1 0.2 292532 44188 ? SN 21:59 0:00 /opt/php71/bin/php-cgi /home/csvac/public_html/index.php

Tue Nov 28 21:59:36 CST 2017
Running Processes:
csvac 706823 10.0 0.2 292920 44940 ? SN 21:59 0:01 /opt/php71/bin/php-cgi /home/csvac/public_html/index.php
csvac 706912 9.1 0.2 292920 44932 ? SN 21:59 0:00 /opt/php71/bin/php-cgi /home/csvac/public_html/index.php
csvac 706920 10.1 0.2 292532 44188 ? SN 21:59 0:00 /opt/php71/bin/php-cgi /home/csvac/public_html/index.php

Running Queries:
*************************** 1. row ***************************
USER: csvac_wrdp1
DB: csvac_wrdp1
STATE: Copying to tmp table
TIME: 2
COMMAND: Query
INFO: 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 (11116) AND (
wp_postmeta.meta_key = ‘_EventStartDate’
AND
(
( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) > ‘2016-11-30 09:30:00’ )
OR (
( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) = ‘2016-11-30 09:30:00’ )
AND mt1.post_id > 11116
)
)
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 ASC, wp_posts.ID ASC
) a
GROUP BY IF( post_parent = 0, ID, post_parent )
ORDER BY EventStartDate ASC
LIMIT 0, 1
*************************** 2. row ***************************
USER: csvac_wrdp1
DB: csvac_wrdp1
STATE: Sending data
TIME: 1
COMMAND: Query
INFO: SELECT meta_value
FROM wp_postmeta m
INNER JOIN wp_posts p ON p.ID=m.post_id
AND ( p.post_parent=10482 OR p.ID=10482 )

WHERE meta_key = ‘_EventStartDate’
AND post_type = ‘tribe_events’
AND post_status NOT IN ( ‘inherit’, ‘auto-draft’, ‘trash’ )

ORDER BY meta_value ASC
*************************** 3. row ***************************
USER: csvac_wrdp1
DB: csvac_wrdp1
STATE: Copying to tmp table
TIME: 2
COMMAND: Query
INFO: 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 (12386) AND (
wp_postmeta.meta_key = ‘_EventStartDate’
AND
(
( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) > ‘2017-06-24 13:00:00’ )
OR (
( mt1.meta_key = ‘_EventStartDate’ AND CAST(mt1.meta_value AS DATETIME) = ‘2017-06-24 13:00:00’ )
AND mt1.post_id > 12386
)
)
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 ASC, wp_posts.ID ASC
) a
GROUP BY IF( post_parent = 0, ID, post_parent )
ORDER BY EventStartDate ASC
LIMIT 0, 1

Open connections

Current Site Requests:

[Carol Turnbull]

As of today, MYSQL on my site has been turned off and my whole site is down because of this issue with the Events Calendar Pro server resource issue. This is most unfortunate. Please help ASAP!

[Andras]

Hi Carol,

I’m so sorry to hear that our plugin might be causing you issues like that. That is definitely not our intention. Thanks for reaching out!

We are aware of a bug in Events Calendar PRO 4.4.19 which can result in heavy loads to the database. The current workaround for that is going back to version 4.4.18.

You should be able to download the earlier version from your account.

Since your site is unreachable, you will likely need to do a manual update (or rather downgrade in this case). Here’s an article that might help.

You basically need to log in to your server with FTP, delete the ‘events-calendar-pro’ folder which is in ‘wp-content/plugins’. Unzip the older version which you downloaded and upload it to ‘wp-content/plugins’.

Restart the mySql server and see how the site behaves. Let me know if this does the trick.

Cheers,
Andras

[Carol Turnbull]

Thank you for your reply. I have downgraded the script as per your instruction.

I hope this does resolve the server performance issue. The site is now back up. One major problem that has occurred as a result of this downgrade is that almost all of the calendar events are not showing up when clicked on. Clicking on the individual events causes a:

This is somewhat embarrassing, isn’t it?
It seems we can’t find what you’re looking for. Perhaps searching, or one of the links below, can help.

I can click on individual days and it will list all events, with details of that day, but clicking on individual events does not work.

See for yourself here: http://cookstreetvillageactivitycentre.com/events/

Did the database change between v. 4.4.18 and 4.4.19?

How do I fix this?

[Andras]

The database did not change between the 2 versions.

Please try the following:

• Please clear your cache and disable your caching plugin (WP Fastest Cache)
• Go to Settings > Permalinks and re-save the permalink settings
• clear your browser cache (here‘s how to do that)

Now check if that fixes it.

Meanwhile I will try to get some input from the team as well.

Cheers,
Andras

[Carol Turnbull]

Thank you very much! This seems to have resolved all the link issues. We appreciate the support. I there will be a bug fix for the database issues in the next release. Glad we are back up and running again!

[Andras]

Hi Carol,

Happy to hear that solved it for now.

I checked the status of the bugticket related to this issue. The fix will not be released with the coming maintenance release (this week). Since it’s on high priority it has a high chance to make it into the next one. If all goes according to plan that will be still before Christmas.

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 email notifications checked, then you will receive a notification.

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

Thanks and cheers,
Andras

[Nico]

Hi there,

Just wanted to share with you that a new maintenance release (for the Week of 7th January 2018) is out, including a fix for this issue :slightly_smiling_face:

Find out more about this release → https://theeventscalendar.com/maintenance-release-week-7-january-2018/

Please update the plugins and let us know if the fix works for your site,
Best,
Nico

[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