Monster Query flooding our database

[Sonny Parlin]

This query being executed over and over brought down our database, can someone tell me what this query is all about?

SELECT DISTINCT wp_posts.*, wp_postmeta.meta_value as EventStartDate, IF(tribe_event_duration.meta_value IS NULL, tribe_event_end_date.meta_value, DATE_ADD(CAST(wp_postmeta.meta_value AS DATETIME), INTERVAL tribe_event_duration.meta_value SECOND)) as EventEndDate, tribe_event_duration.meta_value as EventDuration FROM wp_posts INNER JOIN 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’ ) LEFT JOIN wp_postmeta as tribe_event_duration ON ( wp_posts.ID = tribe_event_duration.post_id AND tribe_event_duration.meta_key = ‘_EventDuration’ ) WHERE 1=1 AND wp_posts.ID NOT IN (241125,241135,241159,241166,241428,241432,241453,241459,241491,241493,241495,241497,241500,241503,241505,241507,241509,241511,241513,241515,241517,241519,241521,241523,241525,241527,241529,241531,241533,241536,241539,241541,241543,241545,241547,241549,241551,241553,241555,241557,241562,241564,241566,241568,241570,241572,241576,241578,241580,241582,241584,241586,241588,241590,241592,241594,241596,241600,241605,241610,241613,241615,241617,241619,241621,241623,241625,241627,241629,241631,241633,241635,241637,241640,241642,241644,241646,241648,241650,241652,241654,241656,241658,241660,241662,241666,241668,241670,241672,241674,241678,241680,241684,241690,241692,241694,241696,241698,241700,241702,241704,241706,241708,241710,241712,241714,241716,241718,241720,241722,241724,241726,241728,241730,241732,241734,241737,241739,241741,241743,241747,241750,241752,241754,241756,241758,241760,241762,241764,241766,241768,241770,241772,241774,241776,241778,241780,241782,241784,241786,241788,241792,241794,241796,241798,241800,241802,241804,241807,241809,241811,241813,241815,241819,241821,241823,241825,241827,241829,241831,241834,241836,241838,241840,241842,241844,241847,241849,241851,241853,241855,241857,241860,241862,241865,241867,241869,241871,241874,241876,241878,241881,241885,241889,241893,241895,241898,241901,241905,241907,241909,241911,241914,241916,241920,241922,241925,241927,241929,241932,241934,241936,241939,241943,241945,241947,241950,241952,241956,241958,241960,241964,241968,241970,241973,241975,241979,241981,241984,241986,241988,241990,241992,241994,241996,241998,242000,242002,242005,242008,242013,242016,242018,242020,242022,242024,242027,242029,242031,242033,242037,242039,242042,242044,242048,242050,242052,242054,242058,242060,242064,242067,242071,242073,242075,242077,242079,242081,242084,242086,242089,242093,242096,242099,242103,242107,242109,242111,242113,242115,242117,242119,242121,242123,242125,242127,242129,242133,242136,242138,242140,242142,242144,242146,242148,242150,242154,242156,242158,242160,242162,242164,242166,242168,242170,242172,242174,242176,242178,242180,242182,242184,242186,242188,242190,242196,242198,242200,242202,242205,242209,242211,242213,242216,242218,242222,242225,242227,242229,242231,242235,242237,242241,242243,242246,242249,242251,242253,242255,242257,242261,242263,242265,242267,242269,242271,242273,242275,242277,242279,242281,242283,242285,242287,242289,242291,242295,242297,242299,242301,242304,242306,242308,242310,242312,242314,242316,242318,242322,242324,242335,242338,242341,242343,242345,242347,242349,242351,242353,242358,242360,242362,242364,242366,242368,242370,242372,242375,242377,242379,242381,242383,242385,242387,242389,242391,242393,242395,242400,242402,242405,242408,242410,242412,242414,242416,242418,242420,242422,242424,242426,242438,242440,242442,242445,242447,242449,242451,242454,242456,242458,242461,242463,242465,242467,242469,242476,242479,242481,242486,242488,242490,242493,242496,242498,242501,242503,242505,242507,242509,242511,242513,242515,242517,242519,242521,242523,242525,242532,242534,242536,242539,242541,242543,242545,242553,242555,242557,242559,242561,242563,242567,242569,242571,242573,242575,242577,242579,242581,242583,242586,242591,242593,242595,242597,242601,242606,242611,242613,242615,242617,242619,242622,242624,242626,242628,242630,242632,242634,242636,242638,242640,242642,242644,242647,242649,242652,242655,242657,242659,242661,242663,242665,242674,242676,242678,242682,242684,242686,242689,242691,242700,242702,242704,242706,242710,242712,242714,242717,242719,242721,242723,242728,242738,242740,242742,242744,242746,242748,242750,242763,242765,242767,242769,242782,242784,242787,242789,242791,242799,242802,242804,242806,242808,242812,242867,242869,242871,242875,242877,242879,242884) AND wp_posts.post_type = ‘tribe_events’ AND (wp_posts.post_status = ‘publish’) AND (wp_postmeta.meta_key = ‘_EventStartDate’ ) AND ((wp_postmeta.meta_value >= ‘2013-08-15 00:00:00’ AND wp_postmeta.meta_value = ‘2013-08-15 00:00:00’ AND wp_postmeta.meta_value <= '2013-08-15 23:59:59' ) OR (wp_postmeta.meta_value = ‘2013-08-15 23:59:59’ )) ORDER BY wp_posts.menu_order ASC, DATE(wp_postmeta.meta_value) ASC, TIME(wp_postmeta.meta_value) ASC LIMIT 0, 3

[Barry]

The long list of post IDs in particular draws my eye – do you experience quite the same results with a default theme and with no other plugins (except for those by Modern Tribe) in use?

[Sonny Parlin]

I can’t really try that on this site as it’s a large production site for a local news station in Mass. I’m not sure what changing the theme would have to do with the query being generated. Do you know where this particular query comes from?

[Barry]

Hi wggb,

Let me try to answer each of the questions you raised.

I’m not sure what changing the theme would have to do with the query being generated.

It’s not uncommon for themes to modify queries – they might for instance have a setting to allow a certain category or specified set of posts to be excluded from the homepage, or something similar to that, but not be too discriminating in terms of which query they modify.

It could equally be due to another plugin which also needs to manipulate the query to achieve its goals – but again may not discriminate between the query used to build the main blog loop (for instance) and an events query.

Do you know where this particular query comes from?

It certainly looks similar in form to a typical events query issued by our plugin.

It would be worthwhile finding out however A) if it is indeed being modified by another product – I suspect it might be – and B) if changing that resolves the performance issues or C) if it still poses a challenge in terms of performance even if it is not impacted on by other code.

I can’t really try that on this site as it’s a large production site for a local news station in Mass.

I can definitely sympathize, but perhaps you could set up and run through the troubleshooting steps in a duplicate development or staging environment?

There are some aspects of our plugin, particularly the query used to build the month view, for which we are trying hard to improve performance. There is a limit to what can be done though and a lot depends on the resources available in your hosting environment and of course the number of events you have in the system … are you able to share any information along those lines?

[Sonny Parlin]

Okay here’s what I’m thinking… I’m thinking about possibly setting up another web server (we have two right now and one database server). This new server could host the events calendar and it could be isolated with it’s own database. The only hardship I could see would be moving the existing calendar events (for which we have many) from our current server to the new one. This would free me up to try your suggestions and would also isolate the calendar so if a problem like this gets caused it won’t take down the entire site (which is what has been happening). Also, we likely won’t need many of the plugins we have installed now to just host the calendar. Any thoughts on moving the existing events?

I think doing this would be a better option for us because even if I were to set up a staging environment, it wouldn’t be under nearly the same load as the production server, which in my opinion would be taking away one of the major factors associated with the issue. The average on any given day is roughly 25k visitors and 73k pageviews.

[Barry]

Could be worth a try for sure. Given the sort of pressure your production site is under though, is it worth considering if the existing resources simply aren’t sufficient? It may also be worth considering strategies such as query caching at MySQL server level.

It sounds though like you’ve got a good handle on how to approach this, and with regards to importing existing events I think I’d be inclined to make it as simple as possible – perhaps creating a dump of the entire database, perhaps by using the mysqldump utility, and pulling it across to your other server with wget and then importing it directly into your test database – that would reduce this basically to a set of three command line operations.

[Sonny Parlin]

Like everything, I have to run this by the powers that be, but hopefully I’ll get to set this up and I’ll get back to you. The site as a whole handles the traffic fine, notwithstanding large spikes in traffic (i.e. the boston marathon bombings) but even then we didn’t go down. I think as the calendar entries grow the queries are getting larger and are taking longer. Perhaps we could work together to figure out how to make the queries work better for larger scale sites. But doing so will certainly be easier once the calendar is isolated…

[Barry]

Right 🙂

As in my initial response we are aware of some areas where an amount of optimization could take place, either by reducing the total number of event-related queries required to build a given view or by making them individually more efficient (or both of those) and that will be an ongoing work in progress, but if you need to drill into this right now and figure out if it is purely The Events Calendar or if something else is impacting then this could be a worthwhile exercise.

[Rob]

Were you able to make any progress on this, wggb?

[Author]

Posts