Site times out from transient_keys and cache_keys

Hey @bradtm,

We’d had trouble reproducing this bug, so thank you for reporting it. We’re looking into a fix.

For now, I would recommend manually clearing out that database value (after backing up), then re-authenticate if needed.

I believe I need to understand what _pardot_cache_keys and _pardot_transient_keys does. Right now the wp_option increase over time. I renamed the original ones this morning, and a new cache value has been created as expected. It’s slowly increasing, does this ever clear out or reset itself? What do these fields do?

Here’s a pic of the rows and sizes.

View post on imgur.com

I found the same problem today. Our site was giving 504 errors and after a few hours of troubleshooting we found it was due to this data. The combination of _pardot_transient_keys and _pardot_cache_keys resulted in ~5MB of data in the DB. Since autoload is set it “yes” it was causing 504 errors.

See data: https://imgur.com/a/sTDzTGB
Platform: WP Engine
PHP: 5.6
WordPress: 4.9.8

The solution for us was setting autoload to “no” for these two rows, however the bigger question is why this data exists in the first place. For us it’s a serialized array of 110,000 items all with the same string data “pardot_account”.

Thanks
-Sam

Yeah, what fixed it for me was to rename the option value of ‘_pardot_cache_keys’ to ‘_pardot_cache_keys_old’. Over time it would just create a new giant key and slow the site down. So setting it to ‘No’ fixed it perpetually.

This is definitely a big issue with the plugin being that it will slow your site down gradually until just bringing it down.

I’m working on an actual fix for this, but in the meantime, I’ve got a pending update to the plugin that sets autoload to false to prevent bringing all this into memory: https://github.com/Pardot/pardot-for-wordpress/pull/28/commits/f291de82207d58f51d15ff230912e9677383428a

Apply those changes if you can and let me know how they go. After some testing, we’ll do a release.

Here’s the link the full pull request with some other bug fixes as well: https://github.com/Pardot/pardot-for-wordpress/pull/28

If testing goes well, I’ll try to release this this week.

Thank you Cliff for an update on this. What about the size of the column? We see this value grow to 16+ MB and executing SQL Update commands that size which degrades performance, locks our database and eventually brings the server down.

What do these values in _pardot_cache_keys do? We are currently just deleting this row from the table every 5 minutes as over time it brings down our site.

Below is an example error we see from Pardot updating and sending a 400,000 item array to the database.

WordPress database error Deadlock found when trying to get lock; try restarting transaction for query UPDATE wp_options SET option_value = ‘a:483859:{i:0;s:7:\”account\”;i:1;s:7:\”account\”;i:2;s:7:\”account\”;i:3;s:7:\”account\”;i:4;s:7:\”account\”;i:5;s:7:\”account\”;i:6;s:7:\”account\”;i:7;s:7:\”account\”;i:8;s:7:\”account\”;i:9;s:7:\”account\”;i:10;s:7:\”account\”;i:11;s:7:\”account\”;i:12;s:7:\”account\”;i:13;s:7:\”

@bradtm,

This particular functionality was written years ago and isn’t needed anymore, so I’m doing a full rewrite on how the plugin stores functionality. In the meantime, the pending 1.4.9 release will ensure this row doesn’t get loaded into memory. It should be safe to empty out this row.

Are you saying that the row fills up to 400k items within 5 minute?

(Edit: We’re still on the latest WP repo version)

I would assume it depends on website traffic, for us we last wiped the data in December when we found the array was at ~110,000. We also turned off the autoload option at that time. Yesterday we saw the _pardot_cache_keys array was at ~700,000 and causing downtime so we wiped it again. Right now (24 hours later) it’s at 53864.

This error was being triggered 1-2 times per second:
WordPress database error Got a packet bigger than ‘max_allowed_packet’ bytes for query UPDATE wp_options SET option_value = ‘a:674976:{i:0;s:7:\\”account\\”;i:1;s…….

Looking forward to seeing an updated version
Thanks for your efforts!

• This reply was modified 6 years, 1 month ago by cc_sam. Reason: added clarification

It fills up gradually over time. After about 30 minutes there were 500+ records in there. I assume it’s dependent on traffic.

Thanks @cc_sam, @bradtm. I have a quick suggestion to see if we can get this resolved for you faster. Please note the usual warning to do a full site backup before doing this. ??

If you have access to the plugin files themselves, go into includes/pardot-plugin-class.php. You’ll comment out lines 1052, 1053, 1057, 1058. The call_api function will look like this:

static function call_api( $key, $args ) {
		//$value = self::get_cache( $key );
		//if ( ! $value ) {
			$auth = Pardot_Settings::extract_auth_args( $args );
			$args['new_api_key'] = array( __CLASS__, '_new_api_key' );
			$value = self::get_api( $auth )->{"get_{$key}"}( $args );
			//self::set_cache( $key, $value );
		//}
		return $value;
	}

Once you do that, manually delete the _pardot_cache_keys and _pardot_transient_keys rows. In my test, everything continues to work and the database row doesn’t blow up the same anymore.

Can you give this a shot and let me know if that fixes it for you?

I have implemented these changes and will let you know if we see anything out of the ordinary. Good news is currently I’m not seeing the cache keys increase in size when visiting pages.

Hey @bradtm, I wanted to check in—how are things looking after a week?

select length(option_value) from publicsite_options where option_name=’_pardot_cache_keys’

I cleared out this [option_value] field the other day because it was over 10M characters in length and was causing our site to have TTFB of 5+ seconds. There was an immediate load time different once I cleared out that field. After two days, that field is back up to 483K+ in length.

Questions: What is the downside to clearing out this field on a routine basis? What is the [_pardot_cache_keys] option used for? Will we loose some tracking ability if we clear it out every X days? Thank you

• This reply was modified 6 years ago by npadgett. Reason: noting that field length keeps increasing

@npadgett, if you implement this fix, you shouldn’t have to continue clearing out the field. It’s a bug.