Very slow queries, makes wp-admin unusable

[Fifty]

We had lots of performance issues with our site and just found out that this plugin was causing it. I disabled both plugins (normal + PRO version) and now everything is back to normal. We’re on a high performance hosting cluster, so its not a capacity or shared hosting issue.

Both W3 Total cache and “Enable the Month View Cache” were active.
According to phpmyadmin we have almost 80.000 ‘tribe_events’ in the wp_posts table. Some days have a few events, some days up to 20 or so.

We’re getting slow queries – even over 20 seconds – on queries like this one (from our Slow Query Logger):

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 (10626) AND ( 
  wp_postmeta.meta_key = "_EventStartDate" 
  AND 
   (
					( mt1.meta_key = "_EventStartDate" AND CAST(mt1.meta_value AS DATETIME) < "2017-08-19 20:00:00" )
					OR (
						( mt1.meta_key = "_EventStartDate" AND CAST(mt1.meta_value AS DATETIME) = "2017-08-19 20:00:00" )
						AND mt1.post_id < 10626
					)
				)  
  AND 
  mt2.post_id IS NULL
) AND wp_posts.post_type = "tribe_events" AND (wp_posts.post_status = "publish")  GROUP BY wp_posts.ID ORDER BY EventStartDate DESC, wp_posts.ID DESC LIMIT 0, 1;

Our system:
PHP7
WP 4.5.3
The Events Calendar 4.2.3
The Events Calendar Pro 4.2.3

Main question:
How can we improve the performance and get rid of the slow queries? Are we having too many events? And if so, what is too many?

Also, I backupped everything before deactivating the plugins (by renaming plugin folder in ftp, and Pro version deactivated in admin next) I assume this keeps all event data in place, right?

Thanks in advance!

[Hunter]

Hello and welcome 🙂

Thank you so much for being so thorough in your initial post and including the example troublesome query. We do have a few Knowledgebase articles geared towards performance which I’ll link to below. We are always looking for ways to optimize our coding methods, but ultimately, site performance is up to the site owner/manager.

I can personally attest that speed/performance is one of (if not) the most challenging aspects to web development. From your server configuration, hosting provider, themes, plugins, external scripts, it never really ends! With that said, let me know if you have any follow-up questions or comments for me and best of luck getting your site running at a more optimum level.

Caching: What, when and how
The Events Calendar Performance Considerations
Problems with Minification

[Fifty]

Hi Hunter, thanks for replying. I need to more explicit though because the problem is of a larger scale. I’ve attached the load of our db usage, where you can clearly see that we experienced a growing spike, from an average of roughly 15 to 1500. The graph with database load speaks for itself. After disabling the Calendar, we’re back at (currently) a load of 9.5 so far today, see image. (PS: BPU is the providers own measuring tool, you can neglect that. It’s the graph itself I want to share.)

I came across the post https://theeventscalendar.com/support/forums/topic/site-slow-and-down-due-to-calendar-pro-wp_postmeta-table/ and this looks very similar to our situation. We also used recurring events. We’ve added approx 20 events in the last 3 weeks, with some of them reccurring weekly, but we’ve never created 80.000 events in total (see my first post).

Should we install and run the hotfix mentioned here? How much records will it clean per 30 minutes? And also, please check the last question in my previous post, at the bottom.

Thanks.

• This reply was modified 9 years, 9 months ago by Fifty.

[Hunter]

Hi and welcome back,

My apologies for missing the last question in your initial post. By changing the name of the plugin folder, it shouldn’t cause any data loss for any reason. And yes, you should absolutely give the plugin discussed on the ‘Release: Events Calendar PRO 4.2.2.1’ article a shot as I admittedly missed the connection between your issue and the recent fix we put out.

The plugin will do as much as it can every 30 minutes, assuming no other plugins get in the way. So, it’s a bit of a your-mileage-may-vary situation. I hope I’ve done a better job this time around and please keep me updated with your progress. Have a good day!

[Fifty]

Hi Hunter,

Thanks for your additional info.

Regarding the hotfix-plugin:
1 – Can I keep both plugins TEC / TEC Pro inactive while running the cleanup? I dearly hope so, since it would freeze up our admin again. And put extreme load on our site that would hurt visitors.
2 – Does it also work for TEC / TEC PRO 4.2.3? (Since it says: “Please update to the latest version while making sure that your version of The Events Calendar is at version 4.2.2.”)

Thanks!

[Hunter]

Hi and thank you again for your continued patience.

I still have yet to confirm my suspicions from the more technically-inclined support team members, but I believe the answer to both questions is “Yes” – though it might be best to try it out on a test environment, if that’s an option.

Let me know if you’re able to do that and in the mean time, I’ll continue researching on my end and we’ll touch base once you reply back. Thanks again and enjoy the upcoming weekend!

[Fifty]

Hi Hunter, a test environment is always an option, but it’ll be very time consuming to set it up. The plugin has already asked for a lot of my & my teams time, so if you can get a technical confirm that it should work, that would be best.

Ofcourse I’ll always have my backups in place, should something go wrong. But setting up a test environment only for this is not my fav route.

Thanks and a good weekend to you too 😉

(Though I hope that you can get a confirmation before the weekend starts, cuz the sooner I can start, the better)

[Hunter]

Happy Sunday!

I believe I’m the only one “around” today, but I will bring this thread to the attention of our developers tomorrow morning for their confirmation and reply back ASAP. Thank you for the patience and try and enjoy the rest of your weekend!

[Fifty]

Still no feedback from the developers?

[Hunter]

My apologies we’re still in a holding pattern.

The cleanup plugin will not work without the plugins active. You should update to the latest versions. If you’re still on 4.2.2 then the plugin will be actively causing more problems. Updating stops it from adding to the issue. Installing the cleanup will fix it.

Can you check out this SQL query? It will likely return 1 or maybe 2 event IDs that have the runaway recurrence issue. Report back with what you find if possible.

You’ll need to then edit/update those event IDs in WP Admin. This can usually be accomplished by adding a space to end of the event description and hitting update. Assuming you are on a new version of the plugin, this will cause it to rebuild the recurrence and remove the offending entries.

I hope this helps and please let me know how everything goes. Thanks again for your continued patience and sorry you’re having to clean up after this nasty bug! Cheers 🙂

[Fifty]

Hi Hunter, that query is definately helpful! EventID 15562 is the runaway event. See attached image!

Next I’ve ran the query SELECT * FROM wp_posts WHERE "post_parent" = 15562; and I can see that it’s all the same event, same title, same post date and all.

What happens if I simply run DELETE * FROM wp_posts WHERE "post_parent" = 15562; with the plugin still inactive? Or is the patch the only way to go to clean it up permanently?

We’re running TEC 4.2.4.

Thanks again.

• This reply was modified 9 years, 9 months ago by Fifty.

[Hunter]

Hello 🙂

Go ahead and delete the runaway events with that SQL query (backup before!), just make sure you’re updated to the latest versions of our plugins so you don’t run into that issue again! Let me know how it goes and have a great Monday.

Cheers!

[Fifty]

Was just about to fix the thing with the DELETE-query, but looking at phpmyadmin another question came up: What about wp_postmeta? Isn’t that table ‘contaminated’ too?

Also, do I have to run the patch too, after the Delete-query?
Please advice on the best approach for the quickest & best fix, knowing that we have close to 80.000 duplicates in our database.

I can’t do just alone the patch, since – according to your info – it will take too long to clean the duplicates, since it will run only every 30 minutes and I have no clue how much records it will clean every run.

Or can you give me the raw MySQL query from the patch, so that I do the full cleaning of the database myself? (Yes I do backups and yes I know my way around mysql/phpmyadmin).

Please be as complete as possible with the info/advice, thanks in advance.

[Hunter]

The best way to go about cleaning up the wp_postmeta table would be to run a query cleaning up “orphaned” data, or check out a plugin like WP Sweep. And as long as you’re running the latest versions of our plugins, you won’t need to run the patch.

[Fifty]

Got it fixed now.

Deleted close to 80.000 records from the wp_posts in phpmyadmin. Next tried to remove orphaned records in postmeta with WP Sweep, but that didn’t do anything. Maybe because 1.6 million records was a bit too much to handle for it. ? So removed them thru phpmyadmin too with this query. Took 37 seconds but worked 🙂 (see screenshot)

Phew – case closed! ?

[Author]

Posts