Slow page load time due to SQL query

[launchbrigade]

We are seeing 3.5 second page load times with the events calendar plugin enabled, and 0.5 seconds without. I narrowed this down to a similar query others have cited causing performance issues :

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, IF (wp_posts.post_type = ‘tribe_events’, wp_postmeta.meta_value, wp_posts.post_date) AS post_date, tribe_event_end_date.meta_value as EventEndDate FROM wp_posts LEFT JOIN wp_postmeta as wp_postmeta on wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = ‘_EventStartDate’ 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 IN (‘post’, ‘tribe_events’) AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_author = 13 AND wp_posts.post_status = ‘private’) GROUP BY IF( wp_posts.post_parent = 0, wp_posts.ID, wp_posts.post_parent ) ORDER BY post_date DESC LIMIT 0, 10;

Currently there are 1342 posts of type “tribe_event”. We have already installed the hotfix and optimized the database, deleted old revisions, etc.

I’ve seen this on another site in the past. Is there a better way to get this data from the database that doesn’t create this bottleneck?

Hoping for a fix soon.

Chris

[Barry]

Hi Chris,

Sorry you’re experiencing problems.

Do you mean that all pages are being slowed down – or just pages where events are being retrieved? If it’s the latter, I’m not sure quite how much more we can do.

Though I don’t know what your particular server configuration is or how many sites it is home to (or how many posts might be being queried on other pages/when the plugin is not in use), it’s always true that there needs to be sufficient resources to meet the demands of a web application and the volumes of data it is dealing with if we want to see snappy performance.

Are you able to enable page, object or query caching (or all three)/have you done this already? That may help significantly.

[launchbrigade]

Barry,
we have the dev site hosted on our own high performance platform, we own the hardware, and the servers are not loaded with traffic (i.e. Nginx/PHP-FPM/MySQL Galera/Varnish/etc running on dual quad core Xeon server blades). On that note the site’s home page load time is about double at their hosting provider on a old Plesk based server.

The crux of the issue is the SQL query I provided you which is called by the Event Calendar plugin from the home page. When we execute this query on the SQL server directly the response time is 2.6 seconds (vs. 3.5 page load time from the web server).

Just to be clear this is without a doubt a problem with the Events Calendar plugin. The SQL query is extremely inefficient, and we’ve seen this issue on another site that utilizes the same plugin. It seems that anytime the number of event posts approaches or exceeds 1000, the performance drops off drastically.

Please escalate this to your development team for review. There must be a better way to get the posts out of the database without imposing unreasonable computational burden on the SQL server.

Chris

[Barry]

We’ll certainly make a note and see what we can do – as we’re continually working to improve the plugin with each successive release 🙂

Can I ask if you are using object or query caching? Are you still experiencing such slow times if so?

[launchbrigade]

We can’t use any of the major caching plugins such as W3 Total Cache, these conflict with existing caches such as varnish and are disallowed by many hosting providers for this reason.

For grins I installed DB Cache Reloaded Fix and this had no effect on query execution time. If you know of a specific caching plugin that addresses this specific problem, I’m happy to give it a try.

Object caches are really a band aid which hides the underlying problem. While I agree that sometimes caches are appropriate, I would argue that this particular issue is a bug. If an SQL query on 1300 posts takes almost 3 seconds to complete on a high performance server, there’s a problem with that query or database schema.

While I appreciate the commitment to make things better with each release, this is an issue that severely impacts several of your users and I think it deserves a high priority.

I took a look at the query and some alternatives and I’ve come up with a few bits of information that might help guide toward a solution.

1. SQL_CALC_FOUND_ROWS is almost always slower – sometimes up to 10x slower – than running two queries.
2. There are many records stored in wp_postmeta for each event. The way meta data is stored in key/value pairs rather than columns is inherently inefficient.
3. Restructuring this query in a way that does not use SQL_CALC_FOUND_ROWS is likely to provide a performance increase on it’s own, but it’s likely going to require a code change to utilize multiple queries and sort in (PHP) memory.
4. Creating a separate database table to store events data is probably the best possible solution, but obviously the upgrade process of moving data from wp_postmeta to a separate table will be problematic for large event calendars with web server timeouts, etc.

Chris

[launchbrigade]

The real problem with the query is that it uses GROUP BY on two separate rows. This prevents indexing from working. No index means that as the event type post number grows, performance will become steadily worse. Additionally, because it cannot use the index, it is forced to create a temporary table on disk, containing the number of rows returned by “wp_posts.post_type IN (‘post’, ‘tribe_events’) AND (wp_posts.post_status = ‘publish’)”, which in our case is around 1900 rows – per page load.
— Bennett

[Barry]

Hi guys,

If you know of a specific caching plugin that addresses this specific problem, I’m happy to give it a try. Object caches are really a band aid which hides the underlying problem.

I’m afraid I don’t have any specific plugins I’d recommend – every case is different and of course some hosting environments may impose particular restrictions, as you pointed out. In the case of query caching you’d ordinarily need administrative access to the MySQL server to set that up.

While I can sympathize with your position on object caching to a degree, it remains a practical step you can take if you do wish to explore that (even as a temporary solution).

While I appreciate the commitment to make things better with each release, this is an issue that severely impacts several of your users and I think it deserves a high priority.

I took a look at the query and some alternatives and I’ve come up with a few bits of information that might help guide toward a solution.

That’s appreciated and we’ve recorded the notes shared by you (and your colleague) 🙂

We’ll review this and other reports in due course and do what we can – we’re absolutely committed to making this the best plugin it can be – but please understand we also work to a release schedule and this realistically isn’t something we can turn around immediately.

As there’s not too much more we can offer here in the short term I’m inclined to close this thread unless you have any final questions?

[Author]

Posts