Chunk multiple cache sets into groups of 100 to avoid OOM/max_allowed_packet issues

Could you please execute git blame to find the issue related to that change and link the issue here?

I think it is #3164428: Use cacheBackend->setMultiple in ContentEntityStorageBase::setPersistentCache.

What about limiting the number of entities per call to $this->cacheBackend->setMultiple(), as a middle ground?

It would avoid the memory consumption explosion risk of having unbounded $items, and still be faster than calling $this->cacheBackend->set() per entity.

Something like:

    $chunks = array_chunk(array_keys($entities), self::MAX_ENTITIES_PER_CACHE_SET);
    foreach ($chunk as $ids) {
      $items = [];
      foreach ($ids as $id) {
        $items[$this->buildCacheId($id)] = [
          'data' => $entities[$id],
          'tags' => $cache_tags,
        ];
      }
      $this->cacheBackend->setMultiple($items);
    }

If you're loading that many items at once, you could run out of memory before the change, are you able to post memory usage before and after? Would be good to see how much difference this actually makes - i.e. whether it's doubling the memory usage or just tipping over the edge.

If you want to get all the terms in a vocabulary without triggering this there at least a couple of ways that don't involve the database API.

1. you could pass $load_tree = FALSE to TermStorage::loadTree(), then load the terms in chunks when you actually need them. https://api.drupal.org/api/drupal/core%21modules%21taxonomy%21src%21Term...

2. ou could use an entity query - which will give you all the term IDs in a vocabulary, which again you can then load in chunks.

You say in the title that this leads to "MySQL server gone away", what is max_allowed_packet set to? Is the underlying issue that we are sending more to the database in a bulk insert query, rather than a RAM issue on the web server?

Maybe we should consider chunking in DatabaseBackend::doSetMultiple(), as this is possibly a backend-dependent problem.

Adding a related issue, but it'll be a long time before we're in a position to use that for the return value of existing entity methods.

Setting to NR to see what the tests think, but I think this chunking should be pushed to \Drupal\Core\Cache\DatabaseBackend instead, as the issue (as far as we know) only exists in MySQL; other cache backends may be able to handle caching all entities in a single call, which would be better for performance. This would also solve the same problem if large numbers of items are cached by other callers.

+++ b/core/lib/Drupal/Core/Cache/DatabaseBackend.php
@@ -32,6 +32,11 @@ class DatabaseBackend implements CacheBackendInterface {
+  /**
+   * The chunk size for inserting cache entities.
+   */
+  const MAX_ENTITIES_PER_CACHE_SET = 100;
+

This will no longer just be entities now, just MAX_ITEMS_PER_CACHE_SET is probably OK.

try to fix ccf in #15.

Not sure that setMultiple() is the right place, given doSetMultiple() is called twice - that is the thing that does the work and so I think the chunking should be moved there.

Also just spotted this:

  /**
   * The default maximum number of rows that this cache bin table can store.
   *
   * This maximum is introduced to ensure that the database is not filled with
   * hundred of thousand of cache entries with gigabytes in size.
   *
   * Read about how to change it in the @link cache Cache API topic. @endlink
   */
  const DEFAULT_MAX_ROWS = 5000;

  /**
   * The maximum number of rows that this cache bin table is allowed to store.
   *
   * @see ::MAXIMUM_NONE
   *
   * @var int
   */
  protected $maxRows;

As a further optimisation there seems little point caching more $maxRows items in doSetMultiple(), if they will quickly be deleted again.

I am also facing the issue on a taxonomy term page.

Before searching in core issue queue, I realized my MariaDB configuration was the default one and I upgraded it according to https://www.drupal.org/node/259580 with a single effect: the "MySQL Server has gone away error" has been replaced by a PHP out of memory error (512 Mo allowed).

Patch from #15 has fixed the situation (thanks Defcon0).

Having this issue as well, MySQL goes away when it tries to cache all the site users (~12k) in cache_entity table, and then dblog does its job by trying to set a single log message for that insert with @message that contains the 85k placeholders.

The patch in #15 does get rid of the error but has issues, including all cids being wrong in all cache tables due to not preserving keys.

Here's a patch that does the following:
- Move the chunking into doSetMultiple() to have it done only once. Not really a fan of the looping the chunks and then the items but here it is anyways. No other logic changes inside the function. Doing it this way it will of course increase the number of inserts by quite much so not sure what kind of implications that would have.
- Preserve the cids
- Update MAX_ENTITIES_PER_CACHE_SET to MAX_ITEMS_PER_CACHE_SET

Another take here #3368704: SqlContentEntityStorage::loadMultiple needs to limit the number of items stored at once.

FWIW, in the other issue I had to raise max_allowed_packet = 1024M to support the use case. Maybe we could look up the value of the parameter at some point, maybe during cache flushes/container rebuilds and adjust that MAX_ITEMS_PER_CACHE_SET constant accordingly: 100 seems low for most setups.

I don't think the 100 limit is much of a problem even on systems that can handle more, the ::setMultiple() is an optimisation against setting items one by one. Even with 1,000 items to set, that's only nine extra database queries, and each database query is likely to be faster given they're smaller.

Yeah, maybe just set it as a public or even protected property so that anyone with an actual need for higher values can tweak it from the outside (using setAccessible if it is protected).

Hi I have a website with about 30 Taxonomy, since 9.4.9 2 of my taxo crash, the two more big about 700 taxonomy, soo I applied this patch now works nice thx :)

But, I have a content Books, each content has about 100-200 pages, impossible to acces now and my View crach, 9.4.9 make Big limitations and Crash many many site! xD

Now Worpress do better for classification.... xD In world No Website a bit big can past more 9.3.X with Drupal, and Drupal go go go next now new version each Day! xD

I tried all 9.5.x and 10.1 same, bug still here. :(

Other problem my BDD MySQL go up to 70giga! I don't now if it is because thise bug or not.... But my Internet fournisser send me Mail "You depassed you size BDD".

PHP version 7 or 8.1/8.2 change nothing too this bug.

What I can do? Thx

Bug correct the viewss for taxonomy, but Big content (about 100 pages) block, and in Back-end report the LOG is break, message:

"The website encountered an unexpected error. Please try again later."

All page with a bit data seem BUG, patch no correct this. :(

PS: Problem appear on my content with more 1700pages and 2500pages in a body text, other works of this Content if 800pages is inside in body text.

I have 3 Log error PHP, here 2, the 3 is big:

1:
Warning : session_write_close(): Failed to write session data using user defined save handler. (session.save_path: /tmp) dans main() ( ligne )
#0 /home/anarkiadsg/www2/web/core/includes/bootstrap.inc(347): _drupal_error_handler_real(2, 'session_write_c...', 'Unknown', 0)
#1 [internal function]: _drupal_error_handler(2, 'session_write_c...', 'Unknown', 0)
#2 [internal function]: session_write_close()
#3 {main}

2:
User error : Drupal\Core\Database\DatabaseExceptionWrapper thrown while calling __toString on a Drupal\Core\StringTranslation\TranslatableMarkup object in /home/anarkiadsg/www2/web/core/modules/mysql/src/Driver/Database/mysql/ExceptionHandler.php on line 53: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away: SELECT "s".*, "t"."language" AS "language", "t"."translation" AS "translation", "t"."customized" AS "customized" FROM "locales_source" "s" LEFT OUTER JOIN "locales_target" "t" ON t.lid = s.lid AND t.language = :langcode WHERE ("s"."source" IN (:db_condition_placeholder_0)) AND ("s"."context" IN (:db_condition_placeholder_1)); Array ( [:db_condition_placeholder_0] => The website encountered an unexpected error. Please try again later. [:db_condition_placeholder_1] => [:langcode] => fr ) dans Drupal\Core\StringTranslation\TranslatableMarkup->__toString() (/home/anarkiadsg/www2/web/core/lib/Drupal/Component/Utility/ToStringTrait.php ligne 20)
#0 /home/anarkiadsg/www2/web/core/includes/bootstrap.inc(347): _drupal_error_handler_real(256, 'Drupal\\Core\\Dat...', '/home/anarkiads...', 20)
#1 [internal function]: _drupal_error_handler(256, 'Drupal\\Core\\Dat...', '/home/anarkiads...', 20)
#2 /home/anarkiadsg/www2/web/core/lib/Drupal/Component/Utility/ToStringTrait.php(20): trigger_error('Drupal\\Core\\Dat...', 256)
#3 /home/anarkiadsg/www2/web/core/lib/Drupal/Core/EventSubscriber/FinalExceptionSubscriber.php(127): Drupal\Core\StringTranslation\TranslatableMarkup->__toString()
#4 [internal function]: Drupal\Core\EventSubscriber\FinalExceptionSubscriber->onException(Object(Symfony\Component\HttpKernel\Event\ExceptionEvent), 'kernel.exceptio...', Object(Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher))
#5 /home/anarkiadsg/www2/web/core/lib/Drupal/Component/EventDispatcher/ContainerAwareEventDispatcher.php(142): call_user_func(Array, Object(Symfony\Component\HttpKernel\Event\ExceptionEvent), 'kernel.exceptio...', Object(Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher))
#6 /home/anarkiadsg/www2/vendor/symfony/http-kernel/HttpKernel.php(229): Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher->dispatch(Object(Symfony\Component\HttpKernel\Event\ExceptionEvent), 'kernel.exceptio...')
#7 /home/anarkiadsg/www2/vendor/symfony/http-kernel/HttpKernel.php(92): Symfony\Component\HttpKernel\HttpKernel->handleThrowable(Object(Drupal\Core\Database\DatabaseExceptionWrapper), Object(Symfony\Component\HttpFoundation\Request), 1)
#8 /home/anarkiadsg/www2/web/core/lib/Drupal/Core/StackMiddleware/Session.php(58): Symfony\Component\HttpKernel\HttpKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#9 /home/anarkiadsg/www2/web/core/lib/Drupal/Core/StackMiddleware/KernelPreHandle.php(48): Drupal\Core\StackMiddleware\Session->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#10 /home/anarkiadsg/www2/web/core/modules/page_cache/src/StackMiddleware/PageCache.php(106): Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#11 /home/anarkiadsg/www2/web/core/modules/page_cache/src/StackMiddleware/PageCache.php(85): Drupal\page_cache\StackMiddleware\PageCache->pass(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#12 /home/anarkiadsg/www2/web/core/lib/Drupal/Core/StackMiddleware/ReverseProxyMiddleware.php(48): Drupal\page_cache\StackMiddleware\PageCache->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#13 /home/anarkiadsg/www2/web/core/lib/Drupal/Core/StackMiddleware/NegotiationMiddleware.php(51): Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#14 /home/anarkiadsg/www2/vendor/stack/builder/src/Stack/StackedHttpKernel.php(23): Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#15 /home/anarkiadsg/www2/web/core/lib/Drupal/Core/DrupalKernel.php(718): Stack\StackedHttpKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#16 /home/anarkiadsg/www2/web/index.php(19): Drupal\Core\DrupalKernel->handle(Object(Symfony\Component\HttpFoundation\Request))
#17 {main}

I find solution for the moment, I put on my View with BIG TAXO to show only 10 content and works, more 20 no works Error server....

#21 seems OK to me, it would be worth adding explicit test coverage to the database cache backend that you can set at least 101 cache items.

Re-titling to make it clearer what the problem is.

Added a new test case to the DatabaseBackendTest::testSetGet() to test exceeding the chunk size. No other changes made.

Fixing stan fails, hiding previous patches

Only moving to NW for IS update.

Updated IS.

Thanks @acbramley!

IS lines up to solution and test coverage seems to cover #31

Committed/pushed to 11.x and cherry-picked to 10.1.x, thanks!

Patch 34 is still Oki for TAxo big, but problem is still here for show big content, all my COntent BIG is bloque with error:

Error 503 Backend fetch failed
Backend fetch failed

Guru Meditation:
XID: 699419813

Varnish cache server

Impossible to make sitemap too or make view with this content.... Problem limitation is still here and break all big website, please help us to find solution :)

My version PHP on Apache: 8.1.23 and 512Mo limit memory.

Thx for all works Team.

The 503 in Varnish is not necessarily related to that issue: any page that is too long to serve will trigger it if your VCL has too short a timeout.

Can you reproduce the issue without Varnish ?

Patch no works with Drupal 10.1.5....
Patch oki D10.1.0 to 10.1.4.

Still problem big content no works....

All Big site is break and bloque with Drupal 10....

Great to see this merged. However, unless I miss something it does not address to potential issue of saturating the static cache, as mentioned as part #2577417: Add an entity iterator to load entities in chunks, does it ?

Do we want to track that along with the 2577417 iterator, or separately ?

I correct it on my website Drupal 10:

I up the SQL "max_allowed_packet" on my hosting to maximum 64MB and all is oki, it is configured at 1MB basically and too low for Drupal, best ways may be is to write in Tutorial to check this??? I read 8MB is a minimum for Drupal 9....