Super Slow Site Speed & Horrifying SQL Query

[Tara Davis]

Hi Tribe!
One of our clients was complaining about site speed, which led to a thorough investigation on our end about what could be causing the problem. Through the use of a plugin called “Debug Queries”, we found out Tribe Events pages and widgets were executing ridiculous queries that significantly impacted site speed.

For example, on http://ab-dev.hdmade.com/events, the mini-calendar widget was executing this query: https://gist.github.com/keccers/8009558/raw/eda31a24246f331dca6502d408f5b9b641a9aa82/tribe_events.sql
… FOR EVERY SINGLE DAY OF THE MONTH. Just change the days and repeat.
Our site load time was around 12 – 13 seconds and over 90% of that load time was attributed to the SQL. After disabling the widget, load time was cut down to HALF A SECOND.

I can’t imagine you would have this many successful users if this is the way your plugin was intended to function.
Any insight, Tribe?

[Barry]

Sorry first of all that you’ve been hitting such performance issues, hdmadedev. That’s not what we want to hear and please do note (and relay to your client) that performance is also a concern for us and very much something we wish to improve so far as is practicable.

Month view in particular (which would encompass the calendar widget to an extent) is definitely something we have been and continue to work on – and you can expect to see incremental improvements over time as the plugin develops.

With that said, it would be most helpful if you could fill in some of the blanks: how many events are typically taking place per month on your clients site – and what proportion approximately might be recurring events? What sort of environment is being used to host the site – and what levels of traffic is it receiving?

The query you referenced could certainly be optimized, but even so we’d certainly take on any specific feedback and criticism: can you clarify what aspects or particular operations/clauses lead you to describe it as “ridiculous”?

[Tara Davis]

Hi Barry!
Thank you for your reply.

First of all, no offense meant by my use of ridiculous!
Sincere apologies, I was honestly being a bit of a sass.
I still need to take a closer look, but what seemed particularly odd to me was the repetition–that this query is being repeated 30+ times (1x per every day of the month). This holds especially true on the sidebar widget, where in our implementation no event data is being rendered until you click through the cal to the “Day” view.

To answer your questions:
Our site is large. We have over 700 events loaded, the majority of which are recurring.
We have a pretty standard environment set up–we’ve installed WordPress on an Amazon EC2 instance.
Our traffic at this time is non-existent–what I’ve linked you to is a staging site.
Any insights you have are greatly appreciated!
best, Katherine

[elixirlab]

Hello
I ve got the same problem with my site which is very slow even on admin page (more than 10 sec for the home page and navigation between page more than 5 sec…when i desactivate my calendar the speed become normal for a wordpress site -> home page 2 – 3 sec max…
My client asked me to do something but i can’t do anything…

[Barry]

This holds especially true on the sidebar widget, where in our implementation no event data is being rendered until you click through the cal to the “Day” view.

That’s definitely a valid point: there are further optimizations to be made, for sure.

This isn’t unfortunately something we can turn out over night, though and – to give some background as to our decision making processes here – there are a number of possible changes on the cards that might impact on further work in this regard, including altering the way recurrence data is stored and potentially enrichening the amount of information offered by the calendar widget, which we need to factor into our workflow and scheduling as of course we wouldn’t want to conduct a piece of work to optimize the calendar widget, only for it to be annulled by a separate change.

At this time then the big takeaway would be: we’re aware of performance issues in relation to the number and nature of some queries and are interested in and actively considering changes – but for now working through this with caching both front-end and at query level is probably the best strategy for mitigating any adverse impact.

[Tara Davis]

Thanks, Barry. Just so I’m clear/to confirm–we cannot expect any further assistance from Tribe, and if we want to address this issue we will have to deactivate or modify your code and optimize on our own.

[Barry]

We’re here to help as best we can.

For a problem like this, however, I don’t think we can realistically do a great deal in the short term. We’re certainly not ignoring the issue – we just have to be realistic about the scope of the problem and how quickly we can bring development resources to bear on this.

It’s also worth noting that for the overwhelming majority of our customer base this is not a problem they are hitting up against – and in the process of delivering support we regularly visit many sites using our code every day: many of these use the calendar widget, have a not insignificant number of events in their calendar but are not experiencing anything like 12 second load times.

Clearly it is a real and valid problem for you and I’m not suggesting otherwise – I simply wanted you to understand our perspective on this. Regarding action you can take right now to help with this, have you enabled query caching and have you considered making use of a caching plugin?

[nakturnal]

Hi there! I seem to be having this same issue with long load times with our site and the plugin. We just moved over to WPEngine.com for hosting and have their CDN working with the install. We constantly have around 50-200 events a month and sometimes that number fluctuates to slightly more. I deleted all of the old events on the site in hopes that it would speed things up and at that time there were only around 1-200 events on the site. Load time slowed to anywhere from 4sec load to 10sec load. @hdmadedev – if you’ve had any luck fixing the issue, please share! Thanks Tribe dev’s, we know you work hard, any other insight would be great!

Is this other article related to the issue? http://wordpress.org/support/topic/plugin-the-events-calendar-slow-sql-queries-and-bad-performance

I spoke with the support at WPEngine yesterday and they said the following was the only thing they could find that’s slowing down our site:

# Query_time: 9.778947 Lock_time: 0.000112 Rows_sent: 1 Rows_examined: 950564 Rows_affected: 0 Rows_read: 950564
/* From [nakyouout.wpengine.com/event/flight-of-fancy?preview=true&preview_id=23876&preview_nonce=60d057dab6] in [hostinginformationhere/wp-content/plugins/events-calendar-pro/lib/tribe-geoloc.class.php:754] */;

Hope this helps with narrowing down the issue. Thanks in advance for any assistance!

[Tara Davis]

Hi @nakturnal,

After a decent amount of time both optimizing our database and trying different modifications on the SQL query what we wound up doing was (taking the easy way out??) caching the whole thing. Tribe actually has caching built into our version of the plugin, it’s just commented out. To get it to work, you need to also enable a plugin like W3 Total Cache.

I can give you further instructions if you need, just let me know. If you have the Events Calendar Pro, it looks like they’ve recently released an update that also addresses some of these performance issues.

[Barry]

Hi nakturnal:

There’s not too much more we can add at this point I’m afraid. We’re continuing to progress things and some larger-scale restructuring is planned which is likely to have a positive impact on this, right now though if you are hitting problems relating to query performance then caching and more caching is the way to go.

# Query_time: 9.778947 Lock_time: 0.000112 Rows_sent: 1 Rows_examined: 950564 Rows_affected: 0 Rows_read: 950564
… in [hostinginformationhere/wp-content/plugins/events-calendar-pro/lib/tribe-geoloc.class.php:754]

That looks like it is the query used to figure out the approximate geographic centre for your events and can indeed be a comparatively intensive query, depending on your particular dataset. We’re aware of that and try to mitigate its impact by caching the result of the query, which is held for slightly less than 1½ hours before it runs again.

What I would ask though is if you do have further questions that you create a fresh thread – and one of the team will be only too happy to help.

Thanks!

[Author]

Posts