mysql queries crashing server

[crharrison]

We have many instances of Pro installed on multiple sites, some in multisite. When we asked about slow-responding sites and dashboards, hosting company investigated and said:

At around 6 AM CST you started having mysql procs running for all your wordpress sites. I see that mysql has been using over 30 % of your CPU pretty much all day. Looking at the queries that have run it seems to be a similar looking query for all wordpress sites.

The query in question is a variation of this:
SELECT DISTINCT 72768752722_posts.*, 72768752722_postmeta.meta_value as EventStartDate, tribe_event_end_date.meta_value as EventEndDate FROM 72768752722_posts LEFT JOIN 72768752722_postmeta ON ( 72768752722_posts.ID = 72768752722_postmeta.post_id ) LEFT JOIN 72768752722_postmeta AS mt1 ON ( 72768752722_posts.ID = mt1.post_id ) LEFT JOIN 72768752722_postmeta AS mt2 ON (72768752722_posts.ID = mt2.post_id AND mt2.meta_key = ‘_EventHideFromUpcoming’ ) LEFT JOIN 72768752722_postmeta as tribe_event_end_date ON ( 72768752722_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ ) WHERE 1=1 AND 72768752722_posts.ID NOT IN (1288) AND (
72768752722_postmeta.meta_key = ‘_EventStartDate’

You may want to consult a wordpress developer familiar with the load issues due to wordpress mysql queries to further troubleshoot this issue.

[Geoff]

Hi @crharrison,

Geez, it’s been a busy day for you!

Let’s see if we can figure this out together. I may even need to pull in another colleague from my team as we get some more information to work with.

For starters, I see that the host noted the increase in activity started at 6am CST. Do you happen to know if something was happening right around then? For example, was there a rather large recurring event posted, a WordPress update, theme update, or anything else? Any clues you are able to offer would be a huge help.

Next, I see you noted the following in the system information box:

sites are down; unable to access
As of last week we were running latest versions of events cal & pro

Are you still unable to access the Events > Help screen where the system information can be shared? If so, would it be possible to get a copy of your database for testing? Either way will also help a ton.

Thirdly, and again if you are able, would you be willing to install the Query Monitor plugin? While the queries that the hosting company do not jump out at me as anything concerning right out of the gate, the Query Monitor plugin can give a report of slow queries that are dragging down performance, and help us associate them with functions from our plugins so we can isolate the issue. Once you have that up and running (if you are able to) then sharing a report of the slow queries with us here would be great.

Let’s start with these three things and go from there. Like I said, I will likely need to get some other eyes on this, but getting these bits of information will get us off to a good start.

Thanks so much!
Geoff

[crharrison]

This reply is private.

[crharrison]

This reply is private.

[Geoff]

Hi @crharrison,

Thanks so much for the system information!

I’m definitely going to look at the system information more but, as I do, will you please do two things for me:

• Provide me with a copy of your database. This will allow us to apply it to one of our sites for a hands-on test to see what’s going on.
• Run the Query Monitor plugin. Now that it is installed and activated, you can visit any page of your site (while logged in) and it will show you  report of your queries in the footer of the page. If you could visit one of the slow sites/pages in question, look at the queries, and share with me which ones are dragging things down, that would be a super help.

In the meantime, I am not seeing any serious red flags in the system information. I mean, there are a lot of installed plugins which often gives me pause because that increases the likelihood of a conflict between one or more other plugins, but I also know you’ve been using our plugins for years, so it would be sort of strange that this just now pops up. I’m hoping that having a copy of your database and a report of underperforming queries will help us zero in on where to look.

Thanks so much!
Geoff

[crharrison]

Thanks, Geoff.

I have a database backup for our largest WP network, but it’s too large to attach to email so I will try uploading it to the support thread.

A PDF of the query monitor results on a problematic page, the calendar for unityoffortmyers.org, is attached. That is one site in our largest network that is not performing well; Friday afternoon, the client couldn’t get the calendar to load. Very unfortunate, as we are promoting this as our “Website of the Week” on Facebook.

We do have a whole bunch of plug-ins installed on the network, but not all are activate on all sites, and several of the sites get very little traffic.

As a result of performance issues, I have moved some of the busiest sites into different networks and upgraded hosting, but hosting says we continue to have problems because of the mysql queries.

Looking forward to a solution…

[crharrison]

This reply is private.

[Geoff]

This reply is private.

[crharrison]

This reply is private.

[Geoff]

This reply is private.

[crharrison]

Thanks for your help, Geoff.

Everything seems okay at this moment, and that can change at any moment…
and when it does, hosting tech support always says it’s because of the huge
number of mysql queries coming from the calendar.

I’m attaching a recent Pingdom report showing how many outages we’ve had
recently. This report is for just one of the sites on our largest network,
but we’ve found that when it’s down, everything is down.

Sorry I still don’t understand how changing settings for storing recurring
events longer would improve calendar performance. But I’m not a programmer,
so it may be hopeless… 🙁

[Geoff]

Thanks, @crharrison — I’ll keep this thread in Pending Fix and we’ll follow-up with you when we find some insights based on what you’ve provided so far, whether that’s from me or someone else on the team.

I definitely appreciate your help and patience while we dig into this — performance issues are never cut and dry, but we do take them seriously and want to figure this out with you.

Cheers,
Geoff

[crharrison]

This reply is private.

[Geoff]

This reply is private.

[Brook]

This reply is private.

[Author]

Posts