Performance issue

[Pablo]

Hello,

After the last update I suffered a huge impact on the performance of my site. I analyzed the slow queries executed by mysql, and I found that the following function is having a terrible impact on the performance, with queries that need several minutes to be executed:

public function get_min_max_coords() {
		global $wpdb;

		/** @var Tribe__Cache $cache */
		$cache  = tribe( 'cache' );
		$coords = $cache->get_transient( self::ESTIMATION_CACHE_KEY, 'save_post' );

		// We have a cached value!
		if ( is_array( $coords ) ) {
			return $coords;
		}

		// Since we are just getting the IDs this is rather performant.
		$published_events_query = tribe_get_events(
			array(
				'post_type'      => Tribe__Events__Main::POSTTYPE,
				'posts_per_page' => - 1,
				'fields'         => 'ids',
				'orderby'        => 'none',
			)
		);

		$event_ids_prepared = implode( ', ', $published_events_query );
		$latitude_key = self::LAT;
		$longitude_key = self::LNG;

		$sql = "
			SELECT
				MAX( <code>coords</code>.<code>lat</code> ) AS <code>max_lat</code>,
				MAX( <code>coords</code>.<code>lng</code> ) AS <code>max_lng</code>,
				MIN( <code>coords</code>.<code>lat</code> ) AS <code>min_lat</code>,
				MIN( <code>coords</code>.<code>lng</code> ) AS <code>min_lng</code>
			FROM (
				SELECT <code>post_id</code> AS <code>venue_id</code>,
				CASE
					WHEN <code>meta_key</code> = '{$latitude_key}'
					THEN CAST( <code>meta_value</code> AS DECIMAL( 10, 6 ) )
				END AS <code>lat</code>,
				CASE
					WHEN <code>meta_key</code> = '{$longitude_key}'
					THEN CAST( <code>meta_value</code> AS DECIMAL( 10, 6 ) )
				END AS <code>lng</code>
			FROM <code>{$wpdb->postmeta}</code>
			WHERE
				(
					<code>meta_key</code> = '{$latitude_key}'
					OR <code>meta_key</code> = '{$longitude_key}'
				)
				AND <code>post_id</code> IN (
					SELECT <code>meta_value</code>
					FROM <code>{$wpdb->postmeta}</code>
					WHERE
						<code>meta_key</code> = '_EventVenueID'
						AND <code>post_id</code> IN ( {$event_ids_prepared} )
				)
			) AS <code>coords</code>
		";

		$data = $wpdb->get_row( $sql, ARRAY_A );

		if ( ! empty( $data ) ) {
			// If there is no geoloc data then each result will be null - we cannot pass null values
			// to the Google Maps API however
			$data = array_map( 'floatval', $data );
		}

		$cache->set_transient( self::ESTIMATION_CACHE_KEY, $data, 86400, 'save_post' );

		return $data;
	}

Please, consider a system where we have several hundreds or even thousands of events.

Am I right with this issue? Do you have any solution for this?

Kind regards,

Pablo

[Cliff]

Hi, Pablo.

I see that method’s SQL from /wp-content/plugins/events-calendar-pro/src/Tribe/Geo_Loc.php did change from version 4.4.18 to 4.4.19

I can report it to our developers for their investigation. Are there any other details to pass along to them, such as benchmarks or specific pages where it’s causing slowness?

[Pablo]

Hello Cliff,

Thanks for taking care. No, I have no extra information concerning the issue, just that I have a lot of events. I am not sure when it is really called… let me know if it is something that I can disable with a piece of code or something similar. At the moment I have hardcoded the function to set the coordinates manually.

Thanks,

Pablo

[mynorthmedia]

+1

Same thing happened to me. My site also has thousands of events, venues, and organizers.

Released the code updates on Saturday and MySQL CPU usage quickly shot up to over 1000%. Queries wouldn’t finish and pages wouldn’t complete, so PHP-FPM kept spawning new workers. Had to keep killing MariaDB just to keep the site on while I reverted all the stuff that was deployed trying to find the culprit. (Lessons learned: smaller deploys & turn on and dig into the query logs first).

Also probably didn’t help that search engines looked to be crawling at the time.

Mostly commenting to find out more as this progresses, but also hoping that the “thousands of events, venues, and organizers” and “CPU usage of over 1000%” are also of some use in troubleshooting.

Just as a slight tangent, and I’m positive your developers have thought of this before, but there’s nothing out there saying not to add extra tables to the WordPress database—particularly useful for things like those venue coordinates and event times, drastically reducing all these subqueries and joins happening everywhere. 🙂

[Cliff]

I marked this thread as Pending Fix, which means it should receive a reply once the applicable fix has been released. I cannot guarantee when it will be fixed as it’s in the development team’s hands now. They need to assign it, code it, test it, and schedule it for release.

I apologize for this issue and appreciate your understanding and patience.

Initial investigation from one of our developers came away with this information:

The query is pretty much the same as before, that didn’t change in any meaningful way (other than how we prepare the strings that go into the query).

What did change was how we get/set the transient. Previously we used get_transient and set_transient. Now it’s using a special $cache object to handle that, which has more functionality, but perhaps it isn’t operating correctly.

Also, the expiration is much, much longer than before on that cached object. Previously 5000 seconds (83 minutes), now it’s 86400 seconds (24 hours). So maybe the transients/object cache is getting too full and never truly able to store all the requests…

[Pablo]

Thanks a lot Cliff!

Yes, I can not tell why the changes made the function inefficient… but there must be something about that cache then…

Please let us know!

Thanks,

Pablo

[Cliff]

Other users have reported this as well and our developers will get to work on it.

At least one has said that downgrading back to 4.4.18 solved it for them. That’s the only temporary solution for now.

This thread will receive an update once the fix is released.

[Tabytha Rourke]

We are experiencing this issue as well and have rolled back to the previous version – will this be fixed in the next version? Will we run into issues if we are running the newer versions of the event calendar/event tickets plugins?

[Cliff]

Once the fix is released, we will update this thread.

It will not be fixed until 2018. Sorry for the trouble here.

[Joanna]

I experienced this also. And now that I rolled back to 4.4.18, it’s much better. But not entirely resolved. CPU is at 50% instead of 99% after about 3-4 posts in a row.

Like the last question, having the other plugins updated (tickets/community events etc) but only Pro rolled back to 4.4.18… is that likely to also affect resource usage?

Am looking forward to any updates on a fix for this

[Cliff]

Please note that our latest release (https://theeventscalendar.com/maintenance-release-week-7-january-2018/) should resolve this.

Please let me know how this goes for you after updating to Events Calendar PRO version 4.4.21

[Support Droid]

Hey there! This thread has been pretty quiet for the last three weeks, so we’re going to go ahead and close it to avoid confusion with other topics. If you’re still looking for help with this, please do open a new thread, reference this one and we’d be more than happy to continue the conversation over there.

Thanks so much!
The Events Calendar Support Team

[Author]

Posts