Query is too long and keeps timing out.

[Anthony D’Arco]

Hello,

I reached out my hosting provider (WpEngine) first. I can’t access the backend of the website. I know I simply need to delete items, but I keep getting time-out errors, so I can’t get to the event section to delete them. Here is the note from WpEngine support:

Alexander, Aug 06 11:39 AM:
Hi Anthony,

I’ve taken a look at the issue and here is what I found:

I went to this page:

http://boilercatholics.org/wp-admin/edit.php?post_type=tribe_events

It didn’t error out but it took a long time to load.

I then checked the apache error log and found this created at the time when I visited the page:

[Wed Aug 06 16:13:45 2014] [error] [client 198.58.119.71] [WPE Monitoring] Slow mysql_query() call was running query:\nSELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, eventStart.meta_value as EventStartDate, eventEnd.meta_value as EventEndDate FROM wp_posts LEFT JOIN wp_postmeta as eventStart ON ( wp_posts.ID = eventStart.post_id AND eventStart.meta_key = ‘_EventStartDate’ ) LEFT JOIN wp_postmeta as eventEnd ON ( wp_posts.ID = eventEnd.post_id AND eventEnd.meta_key = ‘_EventEndDate’ ) 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.post_type = ‘tribe_events’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘future’ OR wp_posts.post_status = ‘draft’ OR wp_posts.post_status = ‘pending’ OR wp_posts.post_status = ‘private’) ORDER BY eventStart.meta_value DESC, eventEnd.meta_value DESC LIMIT 0, 20 /* From [boilercatholics.org/wp-admin/edit.php?post_type=tribe_events] in [N/A] */
I also checked the SQL slow query log – these are queries that took too long to complete and were shut down, and found several instances related to the events page. Here is an example:

# Thread_id: 19159341 Schema: wp_boilercatholic Last_errno: 0 Killed: 0
# Query_time: 3.375891 Lock_time: 0.000201 Rows_sent: 20 Rows_examined: 270269 Rows_affected: 0 Rows_read: 270269
# Bytes_sent: 8383
use wp_boilercatholic;
SET timestamp=1407335259;
SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, eventStart.meta_value as EventStartDate, eventEnd.meta_value as EventEndDate FROM wp_posts LEFT JOIN wp_postmeta as eventStart ON ( wp_posts.ID = eventStart.post_id AND eventStart.meta_key = ‘_EventStartDate’ ) LEFT JOIN wp_postmeta as eventEnd ON ( wp_posts.ID = eventEnd.post_id AND eventEnd.meta_key = ‘_EventEndDate’ ) 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.post_type = ‘tribe_events’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘future’ OR wp_posts.post_status = ‘draft’ OR wp_posts.post_status = ‘pending’ OR wp_posts.post_status = ‘private’) ORDER BY eventStart.meta_value DESC, eventEnd.meta_value DESC LIMIT 0, 20 /* From [boilercatholics.org/wp-admin/edit.php?post_type=tribe_events] in [N/A] */;
# Time: 140806 14:30:42
# User@Host: boilercatholic[boilercatholic] @ localhost [127.0.0.1]
# Thread_id: 19159710 Schema: wp_boilercatholic Last_errno: 0 Killed: 0
# Query_time: 2.201473 Lock_time: 0.000201 Rows_sent: 5 Rows_examined: 134761 Rows_affected: 0 Rows_read: 134761
# Bytes_sent: 3627
SET timestamp=1407335442;
This query tried to examine 134761 and exceeded the timeout threshold. This is the reason for why you were seeing the 504 gateway timeout errors.

I see that Ethan Kennedy pointed this out in the chat when you originally started the ticket. Basically any activity that outputs information will require a query to database. Since database can be large, if the query is too broad then it could search through much more of the database than it really needs. Because of this, the query can exceed the timeout and generate 504 error.

The solution to this is to make the queries more efficient so that they don’t exceed the timeout threshold.

I see that you’re using The Events Calendar PRO plugin. Since it’s a premium plugin the developer should be able to assist you in making these queries more efficient. Sometimes it’s a matter of adjusting the plugin settings.

Here is the link the plugin developer’s support page:

How can we help?

If they require any additional information from us, we would be happy to provide that information.

Hope that helps. Let us know if you have any questions. I can also give you a call if that might be easier for you.

Regards,

[Barry]

I’m sorry you’re having difficulties here, Anthony.

The first thing I’d like to check on is if you are still locked out – or if WP Engine were able to help you access the admin environment once more?

Beyond that, I would like to highlight that – largely in response to feedback like this – we are investigating new approaches to problems like this one as part of our Summer of Performance. I’d also love to learn more about your specific setup, though – it could be really useful for us.

• Is there a specific hosting package (or tier of hosting) you have with WPEngine?
• Approximately how many events do you currently have in your system?
• What proportion are recurring (again, an approximation is fine)?
• Can you indicate how big your post table is – in terms of the total number of rows (or even just a guess in terms of the total number of pages, posts, events and any other custom post types)?

Thanks!

[Anthony D’Arco]

Yes, unfortunately, I am still locked out.

Thank you for the article about performance. It was a good thing to be aware of. To answer your questions:
We are actually in the the top tier for WpEngine (Enterprise).
We have about 8000 Events (unfortunately).

About 6000 Events (We have a lot of weekly events that never end- we have the settings to create 14 month of events in the future)

That a good questions. I’m not sure if I would have any way of guessing that. In the query above it said it had to query over 200K rows, but that seems strangely high (so I doubt that’s accurate).

Let me know what other information I can provide.

Thank you,
Anthony

[Barry]

OK, that’s definitely useful stuff. Here’s what I’m thinking:

1) If you’re amenable to providing us with access we’d love to take a peek at this problem and see it first hand (and, if possible, do something to at least provide short term resolution). With that in mind I believe WPEngine provides a staging environment and, naturally, we’d rather work within that than your production site:

• Can you “sync things” such that the staging environment has the same database content as the production site, etc?
• Have you already done this and do you see the same problem there if so?
• Would you be happy for us to troubleshoot within that environment if so (which might cause some amount of disruption, albeit it wouldn’t impact your live site)?

2) Just to be clear – you note being locked out of the admin environment, yet reading through the thread I’m not clear if you are completely locked out and can’t perform any admin tasks or if it is only event-related pages (like the events list) that are effectively unavailable – could you confirm?

Thanks so much for your patience and understanding on this one and, if you are happy to go ahead with the approach outlined above, please do share any details necessary for us to do this (we’d love to have (S)FTP access and the ability to interact with your database, in addition to WP admin access) via private reply.

Thanks again!

[Anthony D’Arco]

This reply is private.

[Barry]

Thanks Anthony: bear with us and we’ll update you again as soon as we can.

[Barry]

Hi Anthony:

One problem we’re having is that the WordPress admin account you provided doesn’t seem to work – we can’t login with it – so either the username or password you provided (or both) may be incorrect. Would it be possible for you to check those out and send across a fresh set?

Also, we wondered if access to your WPEngine dashboard/console could be shared – particularly to give us easy access to your database via phpMyAdmin or whatever WPEngine’s preferred tool is.

Let me know!

[Barry]

Just checking in here to see if there’s any further update/if you still need assistance on this one – let me know 🙂

[Barry]

Hi Anthony,

I’m guessing this is now resolved or else you’re focusing on other issues/too busy to progress this at present.

That being the case I’ll close this thread, but if you do need further help certainly let us know and we’ll do our best to assist 🙂

Thanks again!

[Author]

Posts