slow query causing site to go down

[kgemmell]

Hi There,

I’ve had a look at some of the other support forums as this seems to have been a pretty common issue.

The Query below seems to use up 99% of the servers resources every time it runs and if more than one user tries to visit a calendar event page the site basically dies.

# Time: 160706 10:13:49
# User@Host: ytn_db[ytn_db] @ localhost []
# Thread_id: 1120 Schema: ytn_db QC_hit: No
# Query_time: 20.848194 Lock_time: 0.000117 Rows_sent: 34 Rows_examined: 7667238
# Rows_affected: 0
SET timestamp=1467825229;
SELECT tribe_event_start.post_id as ID,
tribe_event_start.meta_value as EventStartDate,
tribe_event_end_date.meta_value as EventEndDate
FROM wp_postmeta AS tribe_event_start
LEFT JOIN wp_posts ON tribe_event_start.post_id = wp_posts.ID
LEFT JOIN wp_postmeta as tribe_event_end_date ON ( tribe_event_start.post_id = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ )
WHERE tribe_event_start.meta_key = ‘_EventStartDate’
AND ( (tribe_event_start.meta_value >= ‘2016-06-27 00:00:00’ AND tribe_event_start.meta_value <= ‘2016-07-31 23:59:59’)
OR (tribe_event_start.meta_value <= ‘2016-06-27 00:00:00’ AND tribe_event_end_date.meta_value >= ‘2016-06-27 00:00:00’)
OR ( tribe_event_start.meta_value >= ‘2016-06-27 00:00:00’ AND tribe_event_start.meta_value <= ‘2016-07-31 23:59:59’)
)
AND wp_posts.post_status IN(‘publish’,’private’)
ORDER BY wp_posts.menu_order ASC, DATE(tribe_event_start.meta_value) ASC, TIME(tribe_event_start.meta_value) ASC;

I noticed on some other threads that selecting the cache option in the admin settings has helped in some cases but it doesn’t seem to do the trick here.

In an attempt to speed things up we tried indexing those particular tables that get queried, as they are not usually indexed by default, but that didn’t seem to help as it looks like it is building a temporary table with all the joins every time and then scanning that which is a huge amount to go through and consequently quite slow.

There are currently just over 2000 events and were hoping that removing them will help the issue. I’ve also noticed that wpengine has given some people grief with this plugin. We are NOT using Wp engine but if there are any settings we can adjust on our server to help solve the issue let me know.

If you have any other suggestions, patches or ideas please let me know.

You can see the site here http://www.yourtownnews.ca/

I’ve currently had to disable the plugin in order to keep the site live. Let me know if you need it active to do any investigation.

[kgemmell]

Solution to the problem was basically to keep the events under 1000. If you have any other suggestions that might help to make the query run more efficiently please let me know.

[Brook]

Howdy Kgemmel,

I would love to help you with this. Performance is one of my favorite things to assist with. 🙂

We are caught between a rock and a hard place when it comes to queries like this. WP best practice strongly encourages plugins to not create new database tables for postmeta data. We follow the “best practice” here and store all our event meta data, including start and end dates, in wp_postmeta. This makes our plugin broadly compatible with third party plugins. However, wp_postmeta is extremely slow. All dates are stored as strings. Further this table can get huge fast, and must usually be joined to wp_posts.

I have examined that query and run some tests. I do not see much of anything that can be done to optimize it, except us rearchitecting the plugin to violate the above WP best practice. This is an idea we have frequently tossed discussed. We would love for our calendar to scale better on slower servers. But, for the extreme majority the current implementation is fast enough, and best of all it plays nice in the WP ecosystem. So to date we have decided to keep things as they are.

I just opened up a feature request regarding the above, just to see if gets any traction with the community. If you wish to vote on it please do: Improve performance by storing event data in separate database table

I noticed on some other threads that selecting the cache option in the admin settings has helped in some cases but it doesn’t seem to do the trick here.

The query you’ve shared does not appear to be a Month View query. Our caching system is exclusively for the Month View and won’t speed up anything else.

In an attempt to speed things up we tried indexing those particular tables that get queried, as they are not usually indexed by default, but that didn’t seem to help as it looks like it is building a temporary table with all the joins every time and then scanning that which is a huge amount to go through and consequently quite slow.

Indexing will not help here unfortunately. It’s the nature of this type of query.

There are currently just over 2000 events and were hoping that removing them will help the issue. I’ve also noticed that wpengine has given some people grief with this plugin. We are NOT using Wp engine but if there are any settings we can adjust on our server to help solve the issue let me know.

A couple of years back WP Engine did have some performance problems that were very specific to their platform. It was fixed back then.

Technical details if you’re interested: One of our performance optimizations was to make the WHERE clause exclude everything it possibly could. This made the query’s text unusually long, but also made it execute much faster. WP Engine was measuring the text length of queries, and deprioritizing lengthy ones. This made many queries that we optimized to run faster on most platforms actually run slower on theirs. We worked with WP Engine for a while and helped them identify the problem. I am happy to say this issue was solved quite a while back.

We still get the occasional thread mentioning WP Engine and performance problems. But thus far every topicI have seen is like yours, where the posts database is enormous (your query examined nearly a million rows) and is not actually a WP Engine specific problem.

If you have any other suggestions, patches or ideas please let me know.

I have a few:

1. Are all of your WP Tables InnoDB ?
2. Do you have control over the MySQL server where you can alter the config file?
3. If so have you tuned it? Here’s a decent article on tuning InnoDB.
4. In particular you will want to set the innodb_buffer_pool_size to be as large as you reasonably can.

If everything is already tuned to as fast as it can be then you might just need a faster MySQL server. For run-of-the-mill hosting plans this will entail getting a nicer general hosting plan, perhaps at a competing web host.

Does that all make sense and help clarify what’s going on?

Cheers!

– Brook

[Brook]

I forgot to ask: If you don’t mind sharing, who is your web host? Could you share the specs of your server if you know them?

– Brook

[kgemmell]

Thanks for the reply Brook,

That article was helpful and I have updated the settings to reflect their optimization suggestions. The virtual server is a shared host with about 6 sites on it. 1 Processor, 1.5G Memory, 64bit CentOS, 81.61GB disk space.

Biggest help though was just removing old events. I’ve created a plugin that automatically removes non recurring events when their end date has past. I’ve notice you guys already had an option in the settings for cleaning out old recurring events in the same way. You might want to expand that to include the non recurring events to just to give users an easy route to keep the number of events down.

Thanks again for the suggestions

Cheers

[Brook]

Oh excellent! I am happy deleting posts was an option. So many folks are hesitant to delete pages on their site, even old ones, that it’s usually the last resort.

We actually have a dusty ticket about a cleanup utility like you’ve described. It has not been super high on our priority list, but it is something we plan to eventually build. Thanks for the suggestion, I’ll have to revisit the priority on that ticket.

Let me know if you ever need anything else. Cheers!

– Brook

[Support Droid]

This topic has not been active for quite some time and will now be closed.

If you still need assistance please simply open a new topic (linking to this one if necessary)
and one of the team will be only too happy to help.

[Author]

Posts