devot:eeDevoted to ExpressionEngine

I’ve been in contact with my host for a week due to severe performance issues.

One of the things they came back with is the size of the Stash table (exp_stash) which is huge:

I haven’t looked into optimising Stash, and it appears wrong that the Stash table is so big.

I’d appreciate some feedback as to what might have caused this and how I can optimise Stash for speed.

Thank you!

From what I’ve researched I suppose I can safely delete the table “exp_stash”?

Do you know why it has ballooned to such a huge size?

The stash table stores variables that you have cached, and it will grow if the variables are set to never expire and/or expired variables are not being pruned.

First you should ensure that you have set up cache pruning:
https://github.com/croxton/Stash/wiki/Cache-pruning-configuration

If you are a Mustash user, I would highly recommend setting up a CRON to trigger pruning via the Mustash API:
https://github.com/croxton/Stash/wiki/Cache-pruning-configuration#pruning-using-a-cron-job

Next, assuming you are using full page caching and are unsure of how many URLs you could be caching then should ensure that your variables are actually set to expire: either add refresh=“1440” (for e.g. 1 day expiry) to your {exp:stash:cache} tags, or set ‘stash_default_refresh’ in your config:
https://github.com/croxton/Stash/wiki/Installing-&-upgrading

Finally, try to ensure that your website always returns a 404 for non-valid urls, so that those urls don’t bloat your cache. The 404 page should NOT be cached. The {exp:stash:not_found} tag can help if you have trouble with {if no_results} ...{/if} conditionals in nested tags (due to the lack of namespacing in EE):
https://github.com/croxton/Stash/wiki/{exp:stash:not_found}

As an aside, large Innodb tables are not really a performance issue if you have configured MySQL with enough memory to hold the table indexes in memory, and the queries against the table are optimised to make use of the indexes (which Stash absolutely does).

Tuning MySQL is well worth doing and will yield significant performance gains. At the very least you should increase the ‘innodb_buffer_pool_size’ value and the ‘key-buffer-size’ to set how much memory MySQL will use for data and indexes in memory. If you are running apache and mysql on the same server then you would typically want Mysql to use up to 20% of system ram. As you are running Stash which uses Innodb, the ‘innodb_buffer_pool_size’ should be around twice ‘key-buffer-size’ value (also applies with any other adds-on that use Innodb, such as Store). Here’s an example of a config I use (but of course it will depend on your server):

https://gist.github.com/croxton/d2294e9413ed3c4ebde1

SSD has very significant benefits for all database I/O, so that’s something you should consider too.

Thank you, Mark, that are good recommendations.

I was still running Stash 2.3, so the first thing I did was upgrade to the current version.
I’ve then replicated an empty table exp_stash and deleted the bloated copy of it.

I now need to follow up with the 404 treatment, and monitor exp_stash to see if my measures are successful.

I guess I cannot optimise MySQL as my host doesn’t allow that granularity of control? Or can I do that with an INI file?

Thank you for your support!

In fact I was having trouble with EE’s 404 tag (how did you know??). Using

in the code below resulted in an endless wait for the page to render, and only deleting the CE Cache entry finally showed the 404 page.

This is the code I am using now to verify if the category is correct:

EDIT: This doesn’t work either, it now gives me a 404 for *all* pages…

I am looking for new extensions in order to enhance server maintenance and working speed. I found https://www.assignmentholic.co.uk/our-services/dissertation/ that can help you to figure out the main error behind stash table huge.