Magento 2

Unmasking Magento 2's Hidden Performance Drain: The Uncached Store Scopes Bottleneck

SQL queries for store, store_website, and store_group tables being repeatedly executed in Magento 2.
SQL queries for store, store_website, and store_group tables being repeatedly executed in Magento 2.

Unmasking Magento 2's Hidden Performance Drain: The Uncached Store Scopes Bottleneck

As e-commerce migration experts at Shopping Mover, we are constantly delving into the intricacies of the Magento platform to ensure optimal performance and stability for our clients. Our deep dives often reveal subtle yet significant architectural nuances that can dramatically impact a store's efficiency. A recent discovery, highlighted in Magento 2 GitHub Issue #41730, has brought to light a long-standing performance bottleneck that has persisted since Magento 2.2.0: the persistent, uncached querying of critical store configuration data.

This issue reveals that essential information about your store views, websites, and store groups is being fetched directly from the database on every single storefront request, even when the Full Page Cache (FPC) is actively serving content. For any business running on Magento 2 (both Adobe Commerce and Open Source), understanding and addressing this can be a game-changer for site speed and server load.

The Persistent Database Burden: What's Being Queried?

The core of the problem lies in how Magento handles its 'scopes' configuration type. Unlike the 'system' configuration, which is efficiently cached, the 'scopes' data—comprising your store, store_website, and store_group tables—is not. This means that with every page load, Magento executes three full-table SELECT queries:

SELECT `store_website`.* FROM `store_website`
SELECT `store_group`.* FROM `store_group`
SELECT `store`.* FROM `store`

The impact is measurable and significant. As meticulously demonstrated by lbajsarowicz in the GitHub issue, even a clean Magento 2.4-develop instance with all caches warmed exhibits this behavior. Consider these findings:

  • A typical FPC bypass request (e.g., accessing the customer login page) results in 10 total database queries, with one query each for store, store_website, and store_group.
  • More strikingly, a built-in FPC HIT, which should ideally be near-zero database interaction, still generates 4 queries. Of these, three are precisely the store, store_website, and store_group selects, with the fourth being a simple SELECT @@version.

This means that on a cached page, these three unnecessary queries account for a staggering 75% of all database interactions. This overhead grows proportionally with the number of store views, websites, and store groups configured in your Magento instance, directly impacting performance and scalability.

Under the Hood: Why Magento Queries Repeatedly

The technical explanation points to the implementation of Magento\Store\App\Config\Type\Scopes::get(). This method, responsible for retrieving scope-related data, relies on an in-memory instance property for memoization within a single request, but crucially, it does not leverage the persistent configuration cache. On the first call of each request, it bypasses any external cache and directly invokes its source.

This source is a virtual type called scopesConfigSourceAggregated, which combines initial configuration data (from app/etc/config.php / env.php) with Magento\Store\App\Config\Source\RuntimeConfigSource. The ConfigSourceAggregated::get() method then unconditionally calls every source, leading RuntimeConfigSource::get() to execute the SELECT * queries on the three tables whenever the database is available.

An important detail is that the first caller in a request, often StoreManager::getStore() through StoreResolver, is triggered during the early stages of Magento's request processing (specifically, while Http::launch() builds the session configuration). This happens before the built-in full page cache is even checked, explaining why FPC hits still incur this performance penalty.

Interestingly, a common recommendation to improve performance, bin/magento app:config:dump scopes, does not resolve this issue. While it dumps file-based configuration, the runtime database source is still read on every request, adding to the overhead rather than eliminating it.

Historical context reveals that the Scopes config type initially had a cache frontend, serializer, and cache tags during its development for Magento 2.2. However, these caching mechanisms were removed before the 2.2.0 release, leaving only in-memory memoization, unlike the `system` config type which continues to benefit from robust caching.

The cost per request isn't just three SQL queries; it's three database round trips, plus the fetching and hydration of every row from these tables. This overhead is paid on every uncached storefront, GraphQL, and REST API request, and critically, on every built-in FPC hit.

The Real-World Impact on Your Magento Store

For any Magento store, especially those with a significant number of store views, websites, or store groups, this uncached behavior translates directly into:

  • Increased Page Load Times: Even milliseconds add up across multiple requests, contributing to a slower user experience and potentially impacting SEO rankings.
  • Higher Server Resource Consumption: Constant database queries put unnecessary strain on your database server, leading to increased CPU and memory usage. This can necessitate more expensive hosting plans or lead to performance degradation during peak traffic.
  • Reduced Scalability: As your business grows and you add more store views or websites, the performance overhead from these queries will escalate, making it harder to scale your infrastructure efficiently.
  • Suboptimal FPC Effectiveness: The very purpose of FPC is to serve content without hitting the database. This issue undermines that goal, making your FPC less effective than it should be.

Addressing the Bottleneck: The Path to Resolution

The good news is that this issue has been identified and is being addressed. The proposed solution involves re-introducing proper caching for the 'scopes' configuration type, similar to how the 'system' configuration is handled. Once implemented, the store, website, and store group data will be read from the configuration cache when warm, eliminating the need for repetitive database queries.

The expected outcome, as per the proposed release note, is that "The scopes configuration (websites, store groups and store views) is now read from the configuration cache, so storefront requests no longer query the store, store_website and store_group tables when the cache is warm." This fix promises a tangible performance boost for all Magento 2.2+ installations.

Shopping Mover's Perspective: Proactive Performance for Migrations

At Shopping Mover, understanding such deep-seated architectural nuances is central to our expertise in Magento migrations and optimizations. Issues like this underscore why a thorough performance audit is crucial, not just after a migration, but as an ongoing practice for any e-commerce business.

When planning a migration to or within Magento 2, or even optimizing an existing Adobe Commerce or Open Source store, identifying and mitigating these hidden performance drains is paramount. Our team leverages this kind of insight to:

  • Pre-empt Performance Issues: By being aware of known bottlenecks, we can implement best practices and monitor for their resolution during development and deployment.
  • Optimize Database Interactions: We focus on minimizing unnecessary database calls, ensuring your Magento store runs lean and fast.
  • Ensure Scalability: Our solutions are built with future growth in mind, ensuring your infrastructure can handle increased traffic and complexity without compromising speed.

This particular fix will be a welcome improvement for the entire Magento ecosystem, reducing server load and enhancing the user experience across countless stores. For businesses considering a migration or seeking to optimize their current Magento setup, partnering with experts who understand these critical development and integration details is invaluable.

Conclusion

The discovery and impending resolution of Magento 2's uncached store scopes issue represent a significant step forward in optimizing the platform's core performance. By eliminating these persistent, unnecessary database queries, Magento stores can look forward to faster page loads, reduced server strain, and a more efficient full page cache. Staying informed about such developments and proactively addressing them is key to maintaining a competitive edge in the fast-paced world of e-commerce.

Share:

Start with the tools

Explore migration tools

See options, compare methods, and pick the path that fits your store.

Explore migration tools