Transients Layered Nav Counts problem (WooCommerce): verschil tussen versies
Regel 7: | Regel 7: | ||
For context: | For context: | ||
− | * This all plays on our new Hetzner server, which is big and | + | * This all plays on our new Hetzner server, which is big and fast |
* There are about 40 webshops on this server. | * There are about 40 webshops on this server. | ||
Versie van 7 dec 2023 11:02
In the autumn of 2023, we encountered a problem on WooCommerce webshops. It turned out that this was related to some other problems that we have been confronted with for probably one or two years already.
Immediate problems
This is what we experienced as the immediate problem - the cause for this article and our troubshooting around this.
For context:
- This all plays on our new Hetzner server, which is big and fast
- There are about 40 webshops on this server.
These 'immediate problems' nicely illustrate how amorphous computer problems can be: It seems like twice the same problem, isn't it? Or like one problem and one underlying cause - How to keep these kinds of things structured? → Try to keep it intuitive, rather than 'logically rigid'.
Problem | Notes |
---|---|
Random database server crashes? | Every couple of days, the MariaDB server crashed, seemingly due to unlikely issues, like memory shortage or something that seemed like compromized data |
Database server crashes due to storage issues | Somehow, /var/logs/php/php.error was growing with about 2GB per minutes, crashing the database server when the server run out of storage space
|
The problem
We have the problem described here at GitHub, but with a minor difference: The situation described at the link, is from the 'old' situation, when there was only one large transient. In reaction to this issue, this transient has been split in separate transients per filter. That is our situation, but the problem is the same.
Symptomps
What we experience, and that I found back in the article mentioned above:
- There are a couple of transients with names starting with
transient_wc_layered_nav_counts_pa_
that make up about 80% of the size of tablewp_options
- Storage space for table
wp_options
grows with about 2GB per minute, probably because of logging, as these transients are updated constantly - Db server crashes when running out of storage space
- Site performance degredation, even with enough storage space available
- Every couple of days, we have to delete transients - We have been doing this for at least a year already and only now can I connect it with the other issues
- File
/var/log/php/error.log
grows as well with about 2GB/minute. Unknown if this is related
Nicely illustrated by this comment:
I confirm that since WC 3.2.1, I have to purge my MariaDB bin log files every 5 days. With the current traffic I get on my site (which is yet little trafic), approximately every 5 days, this transient string is so huge that it generates up to 8 gigas of log when it's saved to the table.
Another illustration [1]:
We are proactively contacting you because of hyper filling up the storage up to 500GB due to massive transaction logs by the site. The engineering team detected that it’s generating ~150MB/sec of mysql bin logs (transaction logs). From a quick glance, it looks like the wp_options table and a key named _transient_wc_layered_nav_countsr may be the culprit. The key is being overwritten/appended to at a huge rate.
Not the exact same problem, though
Note that in the article cited above, one of the suggested solutions, was to have a separate transient for each widget. That is now the case. So, we don't have something called _transient_wc_layered_nav_countsr
, but a bunch of transients mentioned elsewhere. That's probably created here: https://github.com/woocommerce/woocommerce/pull/18771
In WooCommerce, layered navigation refers to a filtering system that helps customers refine their search results while browsing a store. It allows shoppers to narrow down product selections based on various attributes or criteria, such as price range, size, color, brand, or any custom product attributes set by the store owner.
Layered navigation typically appears as a sidebar or series of filters on category pages or search results. When customers select specific criteria or attributes, the displayed products dynamically update to show only items that match the chosen filters. This feature enhances the user experience by making it easier for customers to find the products that meet their specific preferences or requirements.
WC Layered navigation counts refers to counting the number of items that are within a filter. Amongst other, this used for the WC Layered Nav Widget to decide whether or not to hide an item when count is 0.
Maybe this came out of this issue about how slow the query was that does this job [2]. Hence cachching → [3]
Transients
Example:
option_id option_name First characters Size --------- ----------------------------------------- ------------------------- ----- 2168239 _transient_wc_layered_nav_counts_pa_asxxx a:16:{i:0;b:0;s:32:"c5662 0.28M 2168225 _transient_wc_layered_nav_counts_pa_auxxx a:16:{i:0;b:0;s:32:"4c798 0.60M 2168229 _transient_wc_layered_nav_counts_pa_coxxx a:16:{i:0;b:0;s:32:"c3e8d 0.35M 2168221 _transient_wc_layered_nav_counts_pa_depxx a:16:{i:0;b:0;s:32:"56496 0.36M 2168213 _transient_wc_layered_nav_counts_pa_devxx a:16:{i:0;b:0;s:32:"4c23e 0.34M 2168235 _transient_wc_layered_nav_counts_pa_dixxx a:16:{i:0;b:0;s:32:"258a3 0.39M 2168223 _transient_wc_layered_nav_counts_pa_lexxx a:16:{i:0;b:0;s:32:"099f4 0.37M 2168215 _transient_wc_layered_nav_counts_pa_moxxx a:16:{i:0;b:0;s:32:"cc751 0.38M 2168233 _transient_wc_layered_nav_counts_pa_orxxx a:18:{i:0;b:0;s:32:"65b34 10.28M 2168237 _transient_wc_layered_nav_counts_pa_prxxx a:16:{i:0;b:0;s:32:"4ae79 1.86M 2168231 _transient_wc_layered_nav_counts_pa_sprxx a:16:{i:0;b:0;s:32:"c5e23 0.36M 2168217 _transient_wc_layered_nav_counts_pa_subxx a:16:{i:0;b:0;s:32:"52a9d 0.34M 2168219 _transient_wc_layered_nav_counts_pa_widxx a:16:{i:0;b:0;s:32:"7735d 14.90M 2168227 _transient_wc_layered_nav_counts_pa_wirxx a:16:{i:0;b:0;s:32:"a04c7 0.34M
Potential solutions
- Using memcache [4]
- Flushing via cron when it gets too big [5]. By default, they hang around for about 1 day, so it size is an issue, they should probably be flushed more often, e.g., once per hour
- Removing the transient - See image
- Using an boject cache like Memcache or Reddis to store this in-memory, rather than in a database [6]
- Remove the count in the layered navigation widgets - What is this???