Urgent – Production issue – Long running query

[Uwe Matern]

Good Day from Germany,

we had already several times issues with long running queries on our hosting environment. Every few weeks we come in a situation where certain queries are extremely long running and block the entire website, so that you can neither reach the backend nor work in the frontend.

Today we could identify a query together with our hosting provider. Attached you find the complete command. Finally we killed the process and the lock situation was solved.

Pls help urgently.

Best regards
Uwe

P.S. because I cannot upload text file you see the code here:

SELECT SQL_CALC_FOUND_ROWS * FROM ( SELECT DISTINCT apgYs_posts.*, apgYs_postmeta.meta_value as EventStartDate, tribe_event_end_date.meta_value as EventEndDate FROM apgYs_posts LEFT JOIN apgYs_term_relationships ON (apgYs_posts.ID = apgYs_term_relationships.object_id) LEFT JOIN apgYs_term_relationships AS tt1 ON (apgYs_posts.ID = tt1.object_id) INNER JOIN apgYs_postmeta ON ( apgYs_posts.ID = apgYs_postmeta.post_id ) INNER JOIN apgYs_postmeta AS mt1 ON ( apgYs_posts.ID = mt1.post_id ) INNER JOIN apgYs_postmeta AS mt2 ON ( apgYs_posts.ID = mt2.post_id ) INNER JOIN apgYs_postmeta AS mt3 ON ( apgYs_posts.ID = mt3.post_id ) INNER JOIN apgYs_postmeta AS mt4 ON ( apgYs_posts.ID = mt4.post_id ) INNER JOIN apgYs_postmeta AS mt5 ON ( apgYs_posts.ID = mt5.post_id ) INNER JOIN apgYs_postmeta AS mt6 ON ( apgYs_posts.ID = mt6.post_id ) LEFT JOIN apgYs_postmeta as tribe_event_end_date ON ( apgYs_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ ) LEFT JOIN apgYs_postmeta AS cost_filter ON (apgYs_posts.ID = cost_filter.post_id)LEFT JOIN apgYs_postmeta AS venue_filter ON (apgYs_posts.ID = venue_filter.post_id AND venue_filter.meta_key = ‘_EventVenueID’)LEFT JOIN apgYs_postmeta AS organizer_filter ON (apgYs_posts.ID = organizer_filter.post_id AND organizer_filter.meta_key = ‘_EventOrganizerID’)LEFT JOIN apgYs_postmeta AS city_filter ON (apgYs_posts.ID = city_filter.post_id AND city_filter.meta_key = ‘_EventVenueID’) WHERE 1=1 AND ( ( apgYs_term_relationships.term_taxonomy_id IN (145) ) AND tt1.term_taxonomy_id IN (5) ) AND ( apgYs_postmeta.meta_key = ‘_EventStartDate’ AND ( mt1.meta_key = ‘_EventVenueID’ AND CAST(mt1.meta_value AS SIGNED) IN (‘3039′,’3142′,’3348′,’3353′,’5195′,’5334’) ) AND ( ( mt2.meta_key = ‘_ecp_custom_2’ AND mt2.meta_value IN (‘Sonia Schadwinkel’) ) AND ( mt3.meta_key = ‘__ecp_custom_10’ AND mt3.meta_value = ‘Einsteiger’ ) AND ( mt4.meta_key = ‘__ecp_custom_11’ AND mt4.meta_value = ‘Mischtechnik’ ) AND ( mt5.meta_key = ‘__ecp_custom_11’ AND mt5.meta_value = ‘Acryl’ ) AND ( mt6.meta_key = ‘__ecp_custom_51’ AND mt6.meta_value = ‘Farbe/Komposition’ ) ) ) AND apgYs_posts.post_type = ‘tribe_events’ AND ((apgYs_posts.post_status = ‘publish’)) AND tribe_event_end_date.meta_value < ‘2017-05-08 23:59:59’ AND (( cost_filter.meta_key = ‘_EventCost’ AND cost_filter.meta_value >= 109 AND cost_filter.meta_value IS NOT NULL AND CAST(cost_filter.meta_value AS SIGNED) BETWEEN 109 AND 1295 ) ) AND venue_filter.meta_value IN (5358) AND organizer_filter.meta_value IN (3995) AND city_filter.meta_value IN ( 3204,4410 ) ORDER BY EventStartDate ASC, apgYs_posts.post_date ASC ) a GROUP BY IF( post_parent = 0, ID, post_parent ) ORDER BY EventStartDate ASC LIMIT 0, 1

[Uwe Matern]

This reply is private.

[Andras]

Hello again Uwe

Wow, I’m really sorry about that frustrating issue. We’ll try to look at it.

While we start, here are some questions which might help us find the cause.

Do you possibly have an idea when this query kicks in? Which page you are visiting?

Are you running any Event Aggregator imports, scheduled or not?

Thanks,
Andras

 

[Uwe Matern]

Dear Andras,

nice to have an ticket with you again 😉

To your questions:

1. When did it happen: yesterday we had 2 cases, one (the first sql command) at around 11:43 am German time and secondly at around 23:30 pm german time

2. The page . hard to say, most probably they come from our main event page: https://finde-deinen-malkurs.de/kurse/list/?tribe_paged=1&tribe_event_display=list&tribeHideRecurrence=1 (this is at least the only place, where you can use the filter and search options)

3. No, there is no Aggretator import running neither scheduled nor manually.

By the way, we also have in the meanwhile a staging environment running. In case you need access, pls let me know.

Best regards and good luck for the investigation

Uwe

[Andras]

Hallo,

Thanks for all the above and the staging site is a great move!

I have couple more questions, if you could answer these as well:

– Does the staging contain exactly the same data / events etc.? Can you reproduce the issue there as well?

– How many events have been published to the calendar?

– What hosting environment is being used? Shared or dedicated? Operating system? Same used for staging and production?

– Does the same performance issues pop up in a default WordPress environment? (Default theme, only calendar plugins.)

– Might be helpful to install the Query Monitor plugin to help diagnose any specific functions that might be at play.

– Could you send me a database dump and possibly the wp-contents folder so we can test your setting as well in different environments?

Thanks Uwe and have a nice evening!

Cheers,
Andras

[Andras]

Two more things came to me:

You can totally switch off Event Aggregator (if you don’t use / need it) under Events > Settings > Imports tab bottom of the page.

If it wasn’t deactivated yet, then please check if that makes any difference.

Also please test this with the filter bar deactivated.

Danke und Grüsse

Andras

[Uwe Matern]

Dear Andras,

a lot of questions… Here are my answers:

– Does the staging contain exactly the same data / events etc.? Can you reproduce the issue there as well?
=> yes, it is a complete copy of the db and all files of the wp installation. It was created on Monday morning, german time. The incidents happened on Monday afternoon and evening.

– How many events have been published to the calendar?
=> approx. 300 (growing)

– What hosting environment is being used? Shared or dedicated? Operating system? Same used for staging and production?
=> shared environement. approx. 50 other customers. staging and production are on the same system, even in the same db (different tables)

– Does the same performance issues pop up in a default WordPress environment? (Default theme, only calendar plugins.)
=> can’t say. Because the problem is created by a search/filter activity from a visitor. And we can’t keep running the production system in this setup.

– Might be helpful to install the Query Monitor plugin to help diagnose any specific functions that might be at play.
=> note sure what you mean by this. Can you explain further?

– Could you send me a database dump and possibly the wp-contents folder so we can test your setting as well in different environments?
=> we do a complete backup every night (db + all files ). Can send you the copy from Monday morning as zip. It’s 1 GB – How can I upload it?

– event aggregator imports are deactivated now in the setup.

Best regards
Uwe

[Andras]

Uwe, thanks for all this information!

You will not be able to upload 1GB here in the forums 🙂 , can you share it via a cloud service like Dropbox, OneDrive or something? You can post the link here in a private reply.

Are you able to see / replicate the issue on the staging site? I’m sorry, this wasn’t clear from your previous answer.

Please hang in there while we investigate this issue. I will be in touch as soon as I have some news.

Thanks and cheers,
Andras

[Uwe Matern]

This reply is private.

[Andras]

This reply is private.

[Uwe Matern]

This reply is private.

[Andras]

This reply is private.

[Andras]

This reply is private.

[Uwe Matern]

This reply is private.

[Uwe Matern]

This reply is private.

[Author]

Posts