Persistent Memory Leak During Dynamic Sitemap Generation in Laravel for Large-Scale SEO Optimization

We're currently developing a robust dynamic XML sitemap solution for a high-traffic Laravel application, which is absolutely crucial for effective Laravel SEO optimization. The overarching goal is to achieve an auto-updating and future-proof system that can handle a vast number of URLs seamlessly.

However, we are encountering a severe and persistent memory leak when generating this sitemap, particularly as the number of entries scales into hundreds of thousands or even millions. The artisan command or scheduled task consistently crashes due to memory exhaustion, despite increasing PHP's memory limits significantly.

Attempts & Failed Solutions:

• Initial Approach: Our first attempt involved querying all relevant records at once and then iterating through them to build the sitemap. This, predictably, quickly led to memory exhaustion, making it unsuitable for large datasets.
• Attempt 1 (Chunking): We then implemented chunkById() to process records in batches, typically chunks of 500 or 1000. While this provided a noticeable improvement in overall execution time and initially reduced memory spikes, the memory usage still steadily climbs and eventually leaks. This is especially true with models that have complex relationships (e.g., eager loading with(['category', 'tags', 'author'])) that need to be included in the sitemap entry data.
• Attempt 2 (Model Events): Suspecting that model event listeners might be holding onto references, we tried disabling model events using withoutEvents() during the generation process. This yielded no significant impact on memory consumption, indicating that the leak source is likely elsewhere.
• Attempt 3 (PHP GC & Unset): We experimented with explicit PHP garbage collection calls (gc_collect_cycles()) at the end of each chunk processing iteration and meticulously unset() variables after processing each record and chunk. This provided only minimal, temporary relief and did not resolve the fundamental memory accumulation issue.
• Attempt 4 (Eloquent Hydration): We've also investigated Eloquent's hydration process and the potential memory footprint of large model collections, even when chunked. We suspect that underlying references or internal caches might not be fully released, even after processing and unsetting, leading to gradual memory accumulation.

Simulated Error Log:

php artisan sitemap:generate
...
[2023-10-27 10:30:05] local.ERROR: Allowed memory size of 2048MB exhausted (tried to allocate 128 bytes) in /var/www/html/app/Services/SitemapGenerator.php on line 187
Stack trace:
#0 /var/www/html/app/Services/SitemapGenerator.php(187): App\Models\Product->getSitemapEntry()
#1 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(610): App\Services\SitemapGenerator->App\Services\{closure}(Object(App\Models\Product))
#2 /var/www/html/app/Console/Commands/GenerateSitemap.php(75): Illuminate\Database\Eloquent\Builder->chunkById(500, Object(Closure))
#3 [internal function]: App\Console\Commands\GenerateSitemap->handle()
#4 ...

Specific Questions/Help Needed:

• What are the most effective strategies for truly optimizing Eloquent queries and model processing for massive datasets to prevent memory accumulation during long-running scripts, beyond standard chunking?
• Are there specific Laravel or PHP patterns for large-scale sitemap generation that address memory efficiency more robustly than standard chunkById(), especially for models with multiple, complex relationships that need to be loaded?
• Can anyone recommend robust third-party packages or custom architectural approaches for Laravel SEO optimization regarding sitemap generation that inherently handle these memory concerns at extreme scale?
• Could this issue be related to specific PHP FPM configurations, OPcache settings, or other server-side optimizations for long-running artisan commands that I might be overlooking, perhaps related to persistent connections or internal caches?