Transients Layered Nav Counts problem (WooCommerce): verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
 
(38 tussenliggende versies door dezelfde gebruiker niet weergegeven)
Regel 90: Regel 90:
  
 
Total size: 31.15MB
 
Total size: 31.15MB
 +
</pre>
 +
 +
and this is how their names more-or-less look like, when using e.g., <code>wp transient list</code>:
 +
 +
<pre>
 +
wc_layered_nav_counts_pa_dim2-x
 +
wc_layered_nav_counts_pa_dim1-x
 +
wc_layered_nav_counts_pa_device
 +
wc_layered_nav_counts_pa_code
 +
wc_layered_nav_counts_pa_automati-x
 +
wc_layered_nav_counts_pa_breed-x
 +
wc_layered_nav_counts_pa_connector
 +
wc_layered_nav_counts_pa_diagram
 +
wc_layered_nav_counts_pa_diep-x
 +
wc_layered_nav_counts_pa_leng-x
 +
wc_layered_nav_counts_pa_model
 +
wc_layered_nav_counts_pa_origine-x
 +
wc_layered_nav_counts_pa_product-x
 +
wc_layered_nav_counts_pa_stro-x
 +
wc_layered_nav_counts_pa_submodel
 +
wc_layered_nav_counts_pa_ve-x
 
</pre>
 
</pre>
  
Regel 118: Regel 139:
  
 
Maybe this came out of this issue about how slow the query was that does this job [https://github.com/woocommerce/woocommerce/issues/15643]. Hence ''cachching'' → [https://github.com/woocommerce/woocommerce/pull/15658]
 
Maybe this came out of this issue about how slow the query was that does this job [https://github.com/woocommerce/woocommerce/issues/15643]. Hence ''cachching'' → [https://github.com/woocommerce/woocommerce/pull/15658]
 +
 +
== Source code ==
 +
 +
=== Roy Lindauer ===
 +
 +
Code that Roy Lindauer refers to:
 +
 +
<pre>
 +
// We have a query - let's see if cached results of this query already exist.
 +
$query_hash    = md5( $query );
 +
$cached_counts = (array) get_transient( 'wc_layered_nav_counts' );
 +
 +
if ( ! isset( $cached_counts[ $query_hash ] ) ) {
 +
    $results                      = $wpdb->get_results( $query, ARRAY_A );
 +
    $counts                      = array_map( 'absint', wp_list_pluck( $results, 'term_count', 'term_count_id' ) );
 +
    $cached_counts[ $query_hash ] = $counts;
 +
    set_transient( 'wc_layered_nav_counts', $cached_counts, DAY_IN_SECONDS );
 +
}
 +
</pre>
 +
 +
This was located in <code>includes » widgets » class-wc-widget-layered-nav.php</code>, but it isn't there anymore.
 +
 +
=== Nowadays ===
 +
 +
The only place where some of the keywords from above come together, is in <code>src » Internal » ProductAttributesLookup » Filterer.php</code> around line 171:
 +
 +
<pre>
 +
// We have a query - let's see if cached results of this query already exist.
 +
$query_hash = md5( $query_sql );
 +
// Maybe store a transient of the count values.
 +
$cache = apply_filters( 'woocommerce_layered_nav_count_maybe_cache', true );
 +
if ( true === $cache ) {
 +
$cached_counts = (array) get_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ) );
 +
} else {
 +
$cached_counts = array();
 +
}
 +
if ( ! isset( $cached_counts[ $query_hash ] ) ) {
 +
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
 +
$results                      = $wpdb->get_results( $query_sql, ARRAY_A );
 +
$counts                      = array_map( 'absint', wp_list_pluck( $results, 'term_count', 'term_count_id' ) );
 +
$cached_counts[ $query_hash ] = $counts;
 +
if ( true === $cache ) {
 +
set_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ), $cached_counts, DAY_IN_SECONDS );
 +
}
 +
}
 +
</pre>
 +
 +
=== More places to search ===
 +
 +
Places where this stuff might reside, below <code>wp-content » plugins » woocommerce</code> (some less-likely places, like theme files, are excluded):
 +
 +
<pre>
 +
* abstracts » abstact-wc-widget.php
 +
* includes  » class-wc-cache-helper.php
 +
* includes  » class-wc-query.php
 +
* includes  » class-wc-template-loader.php
 +
* includes  » wc-conditional-functions.php
 +
* includes  » wc-widget-functions.php
 +
* includes  » widgets » class-wc-widget-layered-nav-filters.php
 +
* includes  » widgets » class-wc-widget-layered-nav.php
 +
* includes  » widgets » class-wc-widget-rating-filter.php
 +
</pre>
 +
 +
Most likely locations:
 +
 +
<pre>
 +
* includes » class-wc-query.php (query answer Stack Exchange)
 +
* includes » widgets » class-wc-widget-layered-nav.php (Roy Lindauer, not there anymore)
 +
* src » Internal » ProductAttributesLookup » Filterer.php - Some relevant keywords
 +
</pre>
  
 
== Potential solutions ==
 
== Potential solutions ==
Regel 127: Regel 218:
 
! #
 
! #
 
! Solution
 
! Solution
−
! Notes
+
! Description
 
! Evaluation
 
! Evaluation
 
|-
 
|-
Regel 148: Regel 239:
 
|-
 
|-
 
|  4
 
|  4
−
| Remove transients from WooCommerce code
+
| Remove transients creation from WooCommerce code
−
| See the graph on this page + details [https://www.roylindauer.com/blog/wp-transients-must-be-used-responsibly.html here]
+
|
−
| Would be a solution, but would it be the best?
+
* This means that the functionality is kept, but only the ''caching'' is disabled
 +
* See the graph on this page + details [https://www.roylindauer.com/blog/wp-transients-must-be-used-responsibly.html here]
 +
|
 +
* Seems like a good solution, especially since I find it hard to believe that such a large transient is actually speeding up stuff
 +
* Much better than deleting existing transients every so often, because both creation and deletion requires CPU power
 +
* Drawback: requires some custom coding.
 
|-
 
|-
 
|  5
 
|  5
 
| Use an ''object cache''
 
| Use an ''object cache''
 
| Use an ''object cache'' like Memcache or Reddis to store these transients in-memory, rather than in a database [https://github.com/woocommerce/woocommerce/issues/17355#issuecomment-361486208]. Also: ''This single record becomes a bottleneck as the field is locked while it is being written to. Redis or Memcache would be a a better solution. WP Transients are just bad on their own.'' [https://www.roylindauer.com/blog/wp-transients-must-be-used-responsibly.html]
 
| Use an ''object cache'' like Memcache or Reddis to store these transients in-memory, rather than in a database [https://github.com/woocommerce/woocommerce/issues/17355#issuecomment-361486208]. Also: ''This single record becomes a bottleneck as the field is locked while it is being written to. Redis or Memcache would be a a better solution. WP Transients are just bad on their own.'' [https://www.roylindauer.com/blog/wp-transients-must-be-used-responsibly.html]
−
| Sounds like a real solution
+
| In our case, these transients are gigabytes in size. Seems like a waste of memory
 
|-
 
|-
 
|  6
 
|  6
−
| Remove filter count
+
| Remove this functionality alltogether
−
| Remove the count in the ''layered navigation widgets'' - What is this??? Seems unlikely. The Woo guy in this article mentioned that this is quite elementary information. Or is this a heck?
+
| The Woo guy in this article mentiones that this would be messy, as these layered navigation thingies, not only counts elements per filter, but also figures out which filters to hide or not
−
| No, unless we're ready and capable to hack plugins like WooCommerce + willing to maintain such a custom infrastructure
+
| Seems a bad idea
 
|-
 
|-
 
|  7
 
|  7
−
| Remove these filter
+
| Remove all filters from the shop page
−
| Good to be reminded, that we can just remove these filters
+
| Always an option, to completely stop using these ''product attribute'' filters
−
| No: That's too big a change currently + what's the alternative? Buttonbars?
+
| That's too drastic
 
|-
 
|-
 
|  8
 
|  8
Regel 175: Regel 271:
 
| Remove some filters
 
| Remove some filters
 
| We have several really big filters, and we don't know if they are all equally used
 
| We have several really big filters, and we don't know if they are all equally used
−
| Yes: Check (gAna?) if some filters are so rarely used, that they can be deleted
+
| We've done this at the beginning of 2024. It seemed to have made the problem a bit less problematic. Just a bit
 
|-
 
|-
 
| 10
 
| 10
Regel 181: Regel 277:
 
| Reduce the amount of data stored in filters? That seems more relevant for the ''product catalog'' than for ''attribute filters'', but you never know
 
| Reduce the amount of data stored in filters? That seems more relevant for the ''product catalog'' than for ''attribute filters'', but you never know
 
| Consider anyway, although it's not a definite solution
 
| Consider anyway, although it's not a definite solution
 +
|-
 +
| 11
 +
| Disable these transients?
 +
| [https://github.com/woocommerce/woocommerce/issues/20494 This post] seems to suggest that you can just disable these transients. The magic keyword seems to be <code>woocommerce_layered_nav_count_maybe_cache</code>. This keyword is also used to check in the sourcecode before creating these transients: <code>src » internal » ProductAttributesLookup » Filterer.ph</code>, line 174. [https://wp-kama.com/plugin/woocommerce/hook/woocommerce_layered_nav_count_maybe_cache]
 +
| Would probably the best solution, especially if it's only a configuration setting that has to be changed
 +
|-
 +
| 12
 +
| Use alternative filter plugins?
 +
| It seems there are plugins that are alternatives for the default filters on the shop page. On the other hand, they seem to have the same issues [https://stackoverflow.com/questions/25674456/woocommerce-incredibly-slow-with-layered-nav-taxonomy-filters], [https://woocommerce.com/products/ajax-layered-navigation/], [https://store.webkul.com/woocommerce-layered-navigation-plugin.html]
 
|}
 
|}
  
 
== Delete transients ==
 
== Delete transients ==
  
−
Albeit a stop-gap measure, let's delete these transients. They may have different names at different shops, but we broadly get their names by filtering for <code>wc_layered_nav_counts_pa_</code>:
+
Albeit a stop-gap measure, let's delete these transients.
 +
 
 +
=== wp transient delete ===
 +
 
 +
They may have different names at different shops, but we broadly get their names by filtering for <code>wc_layered_nav_counts_pa_</code>:
  
 
<pre>
 
<pre>
Regel 197: Regel 306:
 
</pre>
 
</pre>
  
−
This has been included in a script that executes once per hour.
+
=== Optimize database ===
−
 
 
−
Here is an example of the output of a storage size monitoring script (<code>size.sh</code>) for site <code>at_en</code>:
 
−
 
 
−
<pre>
 
−
  Timestamp      storage  mysqldump  md2  /var  /var/lib  /var/lib/mysql
 
−
----------------  -------  ---------  ---  ----  --------  --------------
 
−
2023-12-08-11:48    125M      16.08M    36%  254G    47G          43G
 
−
2023-12-08-11:49    125M      16.33M    36%  255G    47G          43G
 
−
2023-12-08-11:50    125M      16.62M    36%  255G    47G          43G
 
−
2023-12-08-11:51    125M      16.84M    36%  255G    47G          43G
 
−
2023-12-08-11:53    125M      17.11M    36%  255G    47G          43G
 
−
2023-12-08-11:54    125M      17.36M    36%  255G    47G          43G
 
−
2023-12-08-11:55    11M      1.64M    36%  255G    47G          43G
 
−
2023-12-08-11:56    12M      1.90M    36%  255G    47G          43G
 
−
2023-12-08-11:57    15M      2.28M    36%  255G    47G          43G
 
−
</pre>
 
−
 
 
−
The script is executed at :52 each our. Appearantly, this site is only being updated around :54, which is fine. The columns ''storage'' and ''mysqldump'' both relate to table <code>wp_options</code>:
 
−
 
 
−
* ''storage:'' <code>$(sudo du -sh /var/lib/mysql/${db}/wp_options.ibd | cut -f1)</code>
 
−
* ''mysqldump:'' <code>$(mysqldump ${db} wp_options | wc -c)</code>.
 
−
 
 
−
After optimizing <code>wp_options</code>, storage size is about 7 times the size of the SQL-dump.
 
−
 
 
−
== Optimize database ==
 
  
 
Just removing transients through WP-CLI, is not sufficient: Without optimizing the <code>wp_options</code> table, storage consumption stays the same and is likely to crash your server, no matter how big it is.
 
Just removing transients through WP-CLI, is not sufficient: Without optimizing the <code>wp_options</code> table, storage consumption stays the same and is likely to crash your server, no matter how big it is.
Regel 232: Regel 316:
 
</pre>
 
</pre>
  
−
== Appendix: Script: Transients & optimization ==
+
=== Script ===
  
 
We use a script that iterates over all webshops each hour, and executes the following code on each site:
 
We use a script that iterates over all webshops each hour, and executes the following code on each site:
Regel 281: Regel 365:
 
</pre>
 
</pre>
  
−
This is executed through ''cron'' and the output is redirected to a log file:
+
This is executed through ''cron'' once per hour and the output is redirected to a log file:
  
 
<pre>
 
<pre>
 
52 * * * * /usr/local/bin/wp_wc_quick_fix_dbs_and_transients.sh >> /tja/example/wp_wc_quick_fix_dbs_and_transients.log 2>&1
 
52 * * * * /usr/local/bin/wp_wc_quick_fix_dbs_and_transients.sh >> /tja/example/wp_wc_quick_fix_dbs_and_transients.log 2>&1
 
</pre>
 
</pre>
 +
 +
Here is an example of the output of a storage size monitoring script (<code>size.sh</code> - not the same script as mentioned above) for site <code>at_en</code>:
 +
 +
<pre>
 +
  Timestamp      storage  mysqldump  md2  /var  /var/lib  /var/lib/mysql
 +
----------------  -------  ---------  ---  ----  --------  --------------
 +
2023-12-08-11:48    125M      16.08M    36%  254G    47G          43G
 +
2023-12-08-11:49    125M      16.33M    36%  255G    47G          43G
 +
2023-12-08-11:50    125M      16.62M    36%  255G    47G          43G
 +
2023-12-08-11:51    125M      16.84M    36%  255G    47G          43G
 +
2023-12-08-11:53    125M      17.11M    36%  255G    47G          43G
 +
2023-12-08-11:54    125M      17.36M    36%  255G    47G          43G
 +
2023-12-08-11:55    11M      1.64M    36%  255G    47G          43G
 +
2023-12-08-11:56    12M      1.90M    36%  255G    47G          43G
 +
2023-12-08-11:57    15M      2.28M    36%  255G    47G          43G
 +
</pre>
 +
 +
The script is executed at :52 each our. Appearantly, this site is only being updated around :54, which is fine. The columns ''storage'' and ''mysqldump'' both relate to table <code>wp_options</code>:
 +
 +
* ''storage:'' <code>$(sudo du -sh /var/lib/mysql/${db}/wp_options.ibd | cut -f1)</code>
 +
* ''mysqldump:'' <code>$(mysqldump ${db} wp_options | wc -c)</code>.
 +
 +
After optimizing <code>wp_options</code>, storage size is about 7 times the size of the SQL-dump.
 +
 +
== Remove transient creation ==
 +
 +
This solution is based on [https://www.roylindauer.com/blog/wp-transients-must-be-used-responsibly.html this posting by Roy Lindauer]:
 +
 +
* Seems like a good solution, especially since I find it hard to believe that such a large transient is actually speeding up stuff
 +
* Preventing these transients to be created in the first place, is much better than deleting existing transients every so often, because both creation and deletion requires CPU power
 +
* Drawback: requires some custom coding & patching.
 +
 +
=== Patched code ===
 +
 +
It's basically out-commenting 3 lines in file <code>src » Internal » ProductAttributesLookup » Filterer.php</code> around line 171:
 +
 +
<pre>
 +
// We have a query - let's see if cached results of this query already exist.
 +
$query_hash = md5( $query_sql );
 +
// Maybe store a transient of the count values.
 +
$cache = apply_filters( 'woocommerce_layered_nav_count_maybe_cache', true );
 +
if ( true === $cache ) {
 +
$cached_counts = (array) get_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ) );
 +
} else {
 +
$cached_counts = array();
 +
}
 +
if ( ! isset( $cached_counts[ $query_hash ] ) ) {
 +
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
 +
$results                      = $wpdb->get_results( $query_sql, ARRAY_A );
 +
$counts                      = array_map( 'absint', wp_list_pluck( $results, 'term_count', 'term_count_id' ) );
 +
$cached_counts[ $query_hash ] = $counts;
 +
 +
 +
//
 +
// Just don't create this transients - That's all
 +
// Strompf - 2024.06
 +
//
 +
// if ( true === $cache ) {
 +
// set_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ), $cached_counts, DAY_IN_SECONDS );
 +
// }
 +
//
 +
}
 +
</pre>
 +
 +
=== Test: nl_nl ===
 +
 +
* I had to do this on a live site, since otherwise there wouldn't be enough traffic to have a realistic case
 +
* Choosed <code>nl_nl</code> as it is on a separate server
 +
* Applied on 2024.06.10, around 13:30
 +
* Applied the usual script to remove all transients + optimize table: <code>db_navtrans_trim</code>. Subsequent database size: 2.4G
 +
* Disabled this script in ''cron'' around 13:40. Usually, new transients appear within seconds after removing them
 +
* No new NavTrans transients appeared so far.
  
 
== See also ==
 
== See also ==
Regel 297: Regel 453:
 
* https://github.com/woocommerce/woocommerce/issues/17355 - Transient for Layered Nav Counts gets to big » Primary source for this topic
 
* https://github.com/woocommerce/woocommerce/issues/17355 - Transient for Layered Nav Counts gets to big » Primary source for this topic
 
* https://github.com/woocommerce/woocommerce/issues/15643 - Add option to turn off term counts for WC Layered Nav widget
 
* https://github.com/woocommerce/woocommerce/issues/15643 - Add option to turn off term counts for WC Layered Nav widget
−
* https://stackoverflow.com/questions/25202051/woocommerce-layered-nav-product-filter-widget-multiple-attributes-dont-work-a - A similar issue, where an answer suggests to change code in WooCommerce. It doesn't look trustworthy, but maybe doing such things is an option
+
* https://stackoverflow.com/questions/25202051/woocommerce-layered-nav-product-filter-widget-multiple-attributes-dont-work-a - Patching WooCommerce code suggested. Doesn't seem reliable
−
* https://www.roylindauer.com/blog/wp-transients-must-be-used-responsibly.html - From the same guy that participated in the first link. Including how to disable the transient in WooCommerce
+
* https://www.roylindauer.com/blog/wp-transients-must-be-used-responsibly.html - » No, this is the primary source for this topic
 +
* https://github.com/woocommerce/woocommerce/issues/15643 - More details about this specific query
 +
 
 +
''' Searching '''
 +
 
 +
* https://www.google.com/search?q=wc_layered_nav_counts
 +
* https://www.google.com/search?q=woocommerce_layered_nav_count_maybe_cache

Huidige versie van 11 jun 2024 om 05:09

In the autumn of 2023, we encountered sudden but persistant problems on WooCommerce webshops, resulting in database server crashes and running out of storage space at a pace of about 2GB per minute. It turned out that this was caused by transients for WooCommerce layered navigation counts, and we actually already had problems related to this for over a year already.

Context

  • This all plays on our new Hetzner server, which is about 5 to 6 times bigger and faster than our previous server
  • There are about 40 webshops on this server with about 17,000 products each.

Immediate problems

These 'immediate problems' nicely illustrate how amorphous computer problems can be: It seems like twice the same problem, or like one problem and one underlying cause - How to keep these kinds of things structured? → Try to keep it intuitive, rather than trying to be 'logically rigid':

Immediate problems
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 minute, crashing the database server when the server run out of storage space

Cause

We have the problem described here at GitHub, but with a minor difference:

  • The situation described in this article, is the 'old' situation when there was only one transient for all filters, called _transient_wc_layered_nav_countsr
  • To alluviate this problem, Woo decided to split this one transient into separate transients for each filter [1]
  • Instead of this one transient, we now have several transients with names like _transient_wc_layered_nav_counts_pa_<name of the attribute filter>.

Despite this difference, we have the exact symptomps described in the link above.

Symptoms

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 table wp_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

Our symptoms are 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 of our symptoms [2]: 

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.

Transients

As an example, these are those transients, copied from a site when the problem was alreay rather under control thanks to scripts to delete stuff: 

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

Total size: 31.15MB

and this is how their names more-or-less look like, when using e.g., wp transient list: 

wc_layered_nav_counts_pa_dim2-x
wc_layered_nav_counts_pa_dim1-x
wc_layered_nav_counts_pa_device
wc_layered_nav_counts_pa_code
wc_layered_nav_counts_pa_automati-x
wc_layered_nav_counts_pa_breed-x
wc_layered_nav_counts_pa_connector
wc_layered_nav_counts_pa_diagram
wc_layered_nav_counts_pa_diep-x
wc_layered_nav_counts_pa_leng-x
wc_layered_nav_counts_pa_model
wc_layered_nav_counts_pa_origine-x
wc_layered_nav_counts_pa_product-x
wc_layered_nav_counts_pa_stro-x
wc_layered_nav_counts_pa_submodel
wc_layered_nav_counts_pa_ve-x

What seemed to be happening

Each webshop has about 9 to 14 attribute taxonomies + associated filters at the /shop page. The desciption below is not complete nor consistent: Like always, everything is changing all the time. Especially: While I was measuring stuff, someone else was fixing the problems:

  • For each filter, a transient is created (in table wp_options). Such transients easily become up to 20 to 40 MB per field
  • WP-CLI command wp transient list all usually doesn't work anymore by that time
  • These filters are being updated all the time and that's a problem: This field is locked while it is being written to. WP Transients are just bad on their own [3]
  • With every table update, the size of the database log for this table increases. That might be why files like /var/lib/mysql/example_com/wp_options.ibd were growing so fast, that the overall storage was decreasing with about 2GB per minute
  • At the same time, table wp_options still had only about 1,400 lines. Individual fields were becomming megabytes in size. The total size of this table was measured through Bash commands like out4=$(mysqldump ${db} wp_options | wc -c)
  • Eventually, the content of these fields become so big, that they can't be updated anymore, because table locking exceeds time-outs, generating errors like WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATE wp_options SET option_value = 'a:1... - This happened on 2023.12.07 on site at_en for a field of 44MB
  • When transients can't be updated anymore, this will generate errors in /var/log/php/error.log. Each error line includes the complete SQL udpate statement, including the complete content of the new value of the field. By this time, /var/log/php/error.log starts to grow with about 2GB per minute. I don't know if MySQL storage continues to grow at a similar rate at this point
  • Already for some years, we have a script that deletes the transients once a week, because otherwise layout of some pages get messed up. Only now I realize that this is part of the bigger picture described here.

In the following chapters, let's move from the problem to a solution:

Layered navigation

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.

Layered navigation counts

WC Layered navigation counts refers to counting the number of items that are within a filter. Amongst other, this is used by 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 [4]. Hence cachching → [5]

Source code

Roy Lindauer

Code that Roy Lindauer refers to: 

// We have a query - let's see if cached results of this query already exist.
$query_hash    = md5( $query );
$cached_counts = (array) get_transient( 'wc_layered_nav_counts' );

if ( ! isset( $cached_counts[ $query_hash ] ) ) {
    $results                      = $wpdb->get_results( $query, ARRAY_A );
    $counts                       = array_map( 'absint', wp_list_pluck( $results, 'term_count', 'term_count_id' ) );
    $cached_counts[ $query_hash ] = $counts;
    set_transient( 'wc_layered_nav_counts', $cached_counts, DAY_IN_SECONDS );
}

This was located in includes » widgets » class-wc-widget-layered-nav.php, but it isn't there anymore.

Nowadays

The only place where some of the keywords from above come together, is in src » Internal » ProductAttributesLookup » Filterer.php around line 171: 

// We have a query - let's see if cached results of this query already exist.
$query_hash = md5( $query_sql );
// Maybe store a transient of the count values.
$cache = apply_filters( 'woocommerce_layered_nav_count_maybe_cache', true );
if ( true === $cache ) {
	$cached_counts = (array) get_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ) );
} else {
	$cached_counts = array();
}
if ( ! isset( $cached_counts[ $query_hash ] ) ) {
	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	$results                      = $wpdb->get_results( $query_sql, ARRAY_A );
	$counts                       = array_map( 'absint', wp_list_pluck( $results, 'term_count', 'term_count_id' ) );
	$cached_counts[ $query_hash ] = $counts;
	if ( true === $cache ) {
		set_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ), $cached_counts, DAY_IN_SECONDS );
	}
}

More places to search

Places where this stuff might reside, below wp-content » plugins » woocommerce (some less-likely places, like theme files, are excluded): 

* abstracts » abstact-wc-widget.php
* includes  » class-wc-cache-helper.php
* includes  » class-wc-query.php
* includes  » class-wc-template-loader.php
* includes  » wc-conditional-functions.php
* includes  » wc-widget-functions.php
* includes  » widgets » class-wc-widget-layered-nav-filters.php
* includes  » widgets » class-wc-widget-layered-nav.php
* includes  » widgets » class-wc-widget-rating-filter.php

Most likely locations: 

* includes » class-wc-query.php (query answer Stack Exchange)
* includes » widgets » class-wc-widget-layered-nav.php (Roy Lindauer, not there anymore)
* src » Internal » ProductAttributesLookup » Filterer.php - Some relevant keywords

Potential solutions

Improvement in performance by applying a patch to remove the transient
Potential solutions for the Transients Layered Nav Counts (NavTrans) problem
# Solution Description Evaluation
1 Use memcach [6] Using in-memory caching might be a real solution, rather than suppressing symptoms → See Use object cache below.
2 Flush transients through cron when too big [7]. 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
  • Suppressing symptoms, rather than addressing the cause
  • Do it anyway, as a stop-gap measure
3 Upcoming Woo solution Wait until Woo comes with a 'real' solution, alluded to in the main article No: Let's create a dependency on something we can't control and that isn't there yet
4 Remove transients creation from WooCommerce code
  • This means that the functionality is kept, but only the caching is disabled
  • See the graph on this page + details here
  • Seems like a good solution, especially since I find it hard to believe that such a large transient is actually speeding up stuff
  • Much better than deleting existing transients every so often, because both creation and deletion requires CPU power
  • Drawback: requires some custom coding.
5 Use an object cache Use an object cache like Memcache or Reddis to store these transients in-memory, rather than in a database [8]. Also: This single record becomes a bottleneck as the field is locked while it is being written to. Redis or Memcache would be a a better solution. WP Transients are just bad on their own. [9] In our case, these transients are gigabytes in size. Seems like a waste of memory
6 Remove this functionality alltogether The Woo guy in this article mentiones that this would be messy, as these layered navigation thingies, not only counts elements per filter, but also figures out which filters to hide or not Seems a bad idea
7 Remove all filters from the shop page Always an option, to completely stop using these product attribute filters That's too drastic
8 Combine filters We can definitely reduce the number of filters, as we have some redundancy here This should be done anyway, but is not a definite solution
9 Remove some filters We have several really big filters, and we don't know if they are all equally used We've done this at the beginning of 2024. It seemed to have made the problem a bit less problematic. Just a bit
10 Reduce size of filters? Reduce the amount of data stored in filters? That seems more relevant for the product catalog than for attribute filters, but you never know Consider anyway, although it's not a definite solution
11 Disable these transients? This post seems to suggest that you can just disable these transients. The magic keyword seems to be woocommerce_layered_nav_count_maybe_cache. This keyword is also used to check in the sourcecode before creating these transients: src » internal » ProductAttributesLookup » Filterer.ph, line 174. [10] Would probably the best solution, especially if it's only a configuration setting that has to be changed
12 Use alternative filter plugins? It seems there are plugins that are alternatives for the default filters on the shop page. On the other hand, they seem to have the same issues [11], [12], [13]

Delete transients

Albeit a stop-gap measure, let's delete these transients.

wp transient delete

They may have different names at different shops, but we broadly get their names by filtering for wc_layered_nav_counts_pa_: 

wp transient list --fields=name --format=csv | grep wc_layered_nav_counts_pa_

This can be used to delete only the specific transients, and leave other transients untouched: 

wp transient list --fields=name --format=csv | grep wc_layered_nav_counts_pa_ | cut -d',' -f1 | xargs -n 1 wp transient delete

Optimize database

Just removing transients through WP-CLI, is not sufficient: Without optimizing the wp_options table, storage consumption stays the same and is likely to crash your server, no matter how big it is.

We use this: 

optimize table wp_options

Script

We use a script that iterates over all webshops each hour, and executes the following code on each site: 

################################################################################
# fix_dbs_and_transients
################################################################################
#
fix_dbs_and_transients()
{
	#
	# cd
	########################################
	#
	site_path="/var/www/${site_array[$i,url]}"
	echo "site_path: $site_path"
	cd "$site_path"


	# Remove transients
	########################################
	#
	echo ""
	echo "Transients - Before"
	wp transient list --fields=name

	echo ""
	echo "Transients - Deleting..."
	wp transient list --fields=name --format=csv | grep wc_layered_nav_counts_pa_ | cut -d',' -f1 | xargs -n 1 wp transient delete
	#
	echo ""
	echo "Transients - After"
	wp transient list --fields=name


	# Optimize table "wp_options"
	########################################
	#
	sql="optimize table wp_options;"

	echo ""
	echo "Optimize table wp_options - Query: $sql"
	echo ""
	wp db query "$sql"
	#
}

This is executed through cron once per hour and the output is redirected to a log file: 

52 * * * * /usr/local/bin/wp_wc_quick_fix_dbs_and_transients.sh >> /tja/example/wp_wc_quick_fix_dbs_and_transients.log 2>&1

Here is an example of the output of a storage size monitoring script (size.sh - not the same script as mentioned above) for site at_en: 

   Timestamp       storage   mysqldump  md2   /var   /var/lib   /var/lib/mysql
----------------   -------   ---------  ---   ----   --------   --------------
2023-12-08-11:48    125M      16.08M    36%   254G     47G           43G
2023-12-08-11:49    125M      16.33M    36%   255G     47G           43G
2023-12-08-11:50    125M      16.62M    36%   255G     47G           43G
2023-12-08-11:51    125M      16.84M    36%   255G     47G           43G
2023-12-08-11:53    125M      17.11M    36%   255G     47G           43G
2023-12-08-11:54    125M      17.36M    36%   255G     47G           43G
2023-12-08-11:55     11M       1.64M    36%   255G     47G           43G
2023-12-08-11:56     12M       1.90M    36%   255G     47G           43G
2023-12-08-11:57     15M       2.28M    36%   255G     47G           43G

The script is executed at :52 each our. Appearantly, this site is only being updated around :54, which is fine. The columns storage and mysqldump both relate to table wp_options:

  • storage: $(sudo du -sh /var/lib/mysql/${db}/wp_options.ibd | cut -f1)
  • mysqldump: $(mysqldump ${db} wp_options | wc -c).

After optimizing wp_options, storage size is about 7 times the size of the SQL-dump.

Remove transient creation

This solution is based on this posting by Roy Lindauer:

  • Seems like a good solution, especially since I find it hard to believe that such a large transient is actually speeding up stuff
  • Preventing these transients to be created in the first place, is much better than deleting existing transients every so often, because both creation and deletion requires CPU power
  • Drawback: requires some custom coding & patching.

Patched code

It's basically out-commenting 3 lines in file src » Internal » ProductAttributesLookup » Filterer.php around line 171: 

// We have a query - let's see if cached results of this query already exist.
$query_hash = md5( $query_sql );
// Maybe store a transient of the count values.
$cache = apply_filters( 'woocommerce_layered_nav_count_maybe_cache', true );
if ( true === $cache ) {
	$cached_counts = (array) get_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ) );
} else {
	$cached_counts = array();
}
if ( ! isset( $cached_counts[ $query_hash ] ) ) {
	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	$results                      = $wpdb->get_results( $query_sql, ARRAY_A );
	$counts                       = array_map( 'absint', wp_list_pluck( $results, 'term_count', 'term_count_id' ) );
	$cached_counts[ $query_hash ] = $counts;


	//
	// Just don't create this transients - That's all
	// Strompf - 2024.06
	//
	// if ( true === $cache ) {
	// 	set_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ), $cached_counts, DAY_IN_SECONDS );
	// }
	//
}

Test: nl_nl

  • I had to do this on a live site, since otherwise there wouldn't be enough traffic to have a realistic case
  • Choosed nl_nl as it is on a separate server
  • Applied on 2024.06.10, around 13:30
  • Applied the usual script to remove all transients + optimize table: db_navtrans_trim. Subsequent database size: 2.4G
  • Disabled this script in cron around 13:40. Usually, new transients appear within seconds after removing them
  • No new NavTrans transients appeared so far.

See also

Sources

Searching

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Transients_Layered_Nav_Counts_problem_(WooCommerce)&oldid=36201"