Jump to content

Archived

This topic is now archived and is closed to further replies.

achim

WPEngine deletes large pagelines options from database

Recommended Posts

achim

Dear everybody,

I'm Using DMS 2 on WPEngine and after asking myself where my site was gone this morning - all page settings were lost - I just got this message from my host service WPEngine:

 

We run a daily database clean up script to remove autoloaded options larger than 1MB. More often than not options this large are usually caused by transients. Since WordPress will recreate transients needed, it is usually safe to remove them.

******* However the problem lies with the *******

417539  pl-settings
184137  pl-user-templates

options. It is not a transient and it is larger than 1MB. Since it is not a transient and our script removes that large option

 

I wouldn't expect that WPEngine changes their behavior so what could I do to prevent database entries in wp_options to grow that large? What exactly is stored there that can be larger than 1 MB and how can I reduce the size?

Share this post


Link to post
Share on other sites
achim

I looked up my pl_settings content and it is 443KB - too big to upload it. It looks as if it contains the entire page with all content. Why is that so? Did we configure something wrong?

Share this post


Link to post
Share on other sites
Simon

Id be interested to see whats in the option, you can email me the zip if you like.

Here is a query you can run in SQL to check the values:

SELECT length( option_value ) /1024 as kb,
option_name as name
FROM `wp_options`
WHERE 
option_name LIKE 'pl-settings%' OR
option_name = 'pl-user-templates'

Here are the results for our site:

kbname
28.6768pl-settings
63.5811pl-settings-pagelines-com
256.7803pl-user-templates

We are using WPEngine as well now.

here is another useful snippet:

SELECT length( option_value ) /1024 as kb,
option_name as name
FROM `wp_options`
WHERE length( option_value ) /1024 > 64

This will find all options above 64k

Also, what version of DMS are you using? Is it up to date?

Actually i thought i remembered this situation happening a long time ago, after checking my wpengine user tickets, i found one from DEC 2014 here is their reply:

Throughout the whole of the WP Engine platform we have a script running each night at 6:50am UTC time that performs various database management tasks. One of the tasks that this script performs is checking for, and removing, and extra-large rows in the _option table of the database. It determines these as:

  • Any row with autoload='yes' that is over 1MB in size
  • Any other row that is over 4MB in size

So as a temp fix just set the autoload bit to no in phpmyadmin for that option 

Share this post


Link to post
Share on other sites
achim

Hi Simon,

thanks for your help!

This is what your query returns on our end. means pl-settings has 4MB?

4.077.529    pl-settings
20.996    pl-settings-dms-2
31.865    pl-settings-voyant-2
31.016    pl-user-templates

I looked for other large options but only found this:

81.8604pl-user-sections

We're on dms-version 2.1.9.3, will try the autoload-fix - does it come with any negative side effects?

@_Simon_ 

I sent you a pm with the content of our pl-settings. It appears quite large to me, maybe you see something in there that shouldn't be there.

Share this post


Link to post
Share on other sites
Simon

No that means 4k not 4M ;) its value length divided by 1024.

By default WordPress loads all options with the autoload bit set to yes, on every pageload in case it needs them, if they are NOT loaded with autoload WP will load them ad-lib.

Try it, you can always change it back!

Share this post


Link to post
Share on other sites
achim

In fact it seems to help to set autoload to no.

Is there any option to avoid wp_option records to grow that large?

WPEngine suggested me to monitor my wp_options and if one grows larger that 1MB give some alert to make me change the autoload flag, but actually this isn't something I want to develop and it isn't something let lets me sleep well.

This is what WPEninge says:

pl-settings 417539
_transient_plapi_draft_core_raw 390424
_transient_plapi_draft_core_compiled 327497
_transient_pagelines_core_css 265329
_transient_pagelines_core_css_backup 265329
pl-user-templates 184137
_transient_plapi_store_mixed 117632
_transient_pagelines_sections_css 116871
_transient_pagelines_sections_css_backup 116871
_transient_plapi_draft_sections_compiled 116743

Across those rows, there's 2.325MB of data that should be autoloaded, and is not at this time. At their current sizes, each of those rows would not trigger our cleanup functionality by themselves, but having that amount of data pulled in on each uncached request is going to have a negative impact on your site's performance on any hosting environment.

The next limit where our cleanup script will be triggered is four megabytes for non-autoloaded rows, which many of your options are well below.

Due to the issues created by the large amount of data being stored in autoloaded rows, I'd recommend creating some functionality within your site that can monitor the larger rows within your site by size, or open a suggestion with the theme's developer to monitor the sizes of the theme's rows. I'll also be raising a suggestion on our end to see if we can include some customer-facing monitoring for that as well.

Share this post


Link to post
Share on other sites

  • Similar Content

    • dawsonbarber
      By dawsonbarber+
      Trying PL5 on a new WP Engine hosted installation (using a child theme for PL framework) with SSL cert and style is "gone."
    • achim
      By achim
      Dear everybody.
       
      We are using pagelines in an WPEngine environment that is cached by a varnish.
      Whenever an editor makes a change eg on the home page the less will be re-compiled, which takes up to a few seconds.
      What we witness then nearly every time is that the page references css that point to a 404, which makes the entire page unusable of course.
      This lasts until the cache-age expires which is in our case 10 looong minutes
      If we re-publish the page the problem is gone (most times or it re-happens).
       
      Just trying to understand, maybe this is what happens:
      I didn't find a pattern of how compiles-css files are stored or deleted. e.g. yesterday we published a couple of changes and there is no css file in the uploads directory from yesterday but many from last month etc.
      I think the process might be like so:
      1. I publish a page
      2. the old css is deleted
      3. in the meantime the page is broken and will be cached with 404 css references
      4. then new css is re-compiled and saved to the uploads dir
      5. the reference to the new css is saved to the database
      6. after 10 minutes (cache-age expired) the new css is available
       
      If this is the case pagelines doesn't support varnish - am I right?
      Pls. let us know how to proceed. We have a large print campaing leading traffic to our site and it's broken as soon as we edit a thing.
       
      Thanks in advance 
      Best regards
      Achim Koellner
    • Buishi
      By Buishi
      OK, so I've been having some issues with WP Engine, mutlisite, and Pagelines DMS2. I searched the forums, and the closest thing (that didn't exactly work) was this: http://forum.pagelines.com/topic/38307-wp-engine-server-migration-copy-installation-problem/?hl=database
       
      To make a long story short, when I created my staging site, the Site and Home URL were messed up (it was adding "www." to the beginning). After fixing that issue in the DB with a find and replace (using the same interconnectit tool in the above forum post), all the sections are suddenly blank. I can't figure out why... All I did was replace www.[stagingsite].wpengine.com with [stagingsite].wpengine.com. The first one was wrong, so if anything I would have expected to have problems with that one...
       
      Any ideas? Here's the staging site:
      drwright.staging.wpengine.com
       
      Here's the live site:
      naturesoriginalmedicine.com
    • cenal
      By cenal
      Backstory: We had a marketing person who has a license to use Pagelines setup our website last year.  Since then we have had a large increase in traffic and over the last few days the site became unusable.   We do not have the license for Pagelines so we went back to the person who set it up but they were non-responsive.  We host with WPEngine who provide great support.  We asked them to look into it and they ran some diagnostics.  
       
      This is the response from tech support over at WPEngine - http://clicky.strapr.com/image/2N030q143Y1p
       
      Here is the webpagetest.org results from running Pagelines - http://www.webpagetest.org/result/150117_JP_B7D/
       
      We picked a different theme today from Themeforest to relaunch on while we determine our next steps.  
       
      Here is the webpagetest.org results from that new theme - http://www.webpagetest.org/result/150118_7A_5MC/
       
      We went from an over 8 second page load time with freezing to a just under three second page load time.  We are following WPEngine's advice and using a plugin called Smush to compress the images on the site so we can increase the performance even more.  That plugin estimates we have a day and a half left before the compression is done. 
       
      In any case I thought this would be of interest to the Pagelines community so you can investigate what is causing the issue.  
       
      Here is a pastebin of the WPEngine text - http://pastebin.com/bxw6JEF6
    • davidcosgrove
      By davidcosgrove
      Hi,
       
      Our website www.rootstock.com has been running on WPEngine for months without any problems.
       
      Within the past 3 weeks, they have crashed it 5 times.
       
      After much back and forth with their support staff, they came back to us with the following explanation and insist that our theme developer needs to fix this problem (snippet from them is below) -
       
      ----------------------
      There's an autoloaded row that's used by your theme ( pl-settings ) that is storing just past 1MB of data, that is going to be cleared out when our nightly processes are run within the environment. This would explain the header and footer being mangled, but the rest of the page being intact.
       
      To remedy this, you will want to contact your site's developer and review the information being stored within that row and keep it as far under one megabyte as possible. If you would like to see the amount of data stored within rows on your end (in bytes), feel free to use the following command within PHPMyAdmin. You'll need to select your desired database (wp_ for production and snapshot_ for staging), then click on the SQL tab near the top:
       
      max-width: 100%">SELECT option_name, LENGTH(option_value), autoload FROM `wp_options` WHERE autoload = 'yes';
       
      Arrange the rows in descending order by "LENGTH(option_value)" and you'll get the rows holding onto the largest amount of data within your "wp_options" table. Also, to help speed things up, it's best to keep the total amount of autoloaded data as low as possible. Ideally, being under 300KB is best. Here's a query to show the total amount of autoloaded data:
       
      max-width: 100%">SELECT SUM( LENGTH( option_value ) ) FROM  `wp_options` WHERE autoload =  'yes';
       
      Let me know if this helps to move things forward on your end. If there are any other questions that I can answer, please feel free to ask!
      ----------------------
       
      Is this a DMS-centric issue or is WPEngine able to remedy this?  Is this an issue that every website hosting company will have with DMS?
       
      Thank you for your help.
       
      David
×