DEV Community: Magevanta

Magento 2 Page Builder Performance Optimization

Magevanta — Mon, 03 Aug 2026 09:12:20 +0000

Magento Page Builder is één van de krachtigste tools voor contentbeheer in Adobe Commerce. Het stelt merchants in staat om rijke, visuele landingspagina's, productpagina's en CMS content te bouwen zonder een regel code te schrijven. Maar die flexibiliteit komt met een prijs: Page Builder introduceert meerdere performance bottlenecks die — indien onopgemerkt — flink impact kunnen hebben op pagina-laadtijden en server load.

In deze post duik ik in de architectuur van Page Builder, identificeer ik de meest voorkomende performance problemen, en geef ik je concrete oplossingen om de rendering pipeline te versnellen.

Wat is Magento Page Builder?

Page Builder is een drag-and-drop content editing tool die sinds Magento 2.3.1 standaard beschikbaar is in Adobe Commerce (en als beta in Open Source tot 2.4.3). Content elementen zoals banners, sliders, product grids, en tekstblokken worden opgebouwd via een visuele UI en opgeslagen als gestructureerde JSON-data in de database.

Wanneer een pagina gerenderd wordt, converteert Magento deze JSON-data naar HTML via een rendering pipeline die bestaat uit:

Content parsing — JSON-decodering en structuurvalidatie
Element rendering — Per content element een renderer instantiëren
Widget processing — CMS widgets en directives uitvoeren
Layout injection — Gegenereerde HTML injecteren in de page layout
Frontend asset loading — CSS/JS voor Page Builder elementen laden

Elke stap introduceert potentiële overhead.

De verborgen bottlenecks

1. Overhead door JSON-parsing en content tree traversing

Page Builder slaat content op als complexe geneste JSON-structuur. Bij elke pagina-aanvraag decodeert Magento deze JSON, valideert de structuur, en doorloopt de boom recursief om HTML te genereren. Voor pagina's met veel elementen — denk aan lange landingspagina's met 50+ content blocks — is dit merkbaar.

Impact: CPU-intensief op elke request, geen native caching van de geparseerde structuur.

Diagnose:

Gebruik Blackfire of New Relic en zoek naar Magento\PageBuilder\Model\... methoden in de call tree.
Let op json_decode, unserialize, en recursieve render-calls.

2. Gebrek aan Full Page Cache voor dynamische Page Builder content

Page Builder elementen zoals product carrousels, recent bekeken producten, en catalog price rules zijn inherent dynamisch. Standaard FPC (Varnish/built-in) cached de volledige pagina, maar als Page Builder content per gebruiker varieert — bijvoorbeeld door customer group specifieke prijzen — raakt de cache vaak gemarkeerd als uncacheable.

Impact: Pagina's die normaal uit cache geserveerd worden, moeten volledig opnieuw gerenderd worden. Bij hoge traffic leidt dit tot database load spikes.

Diagnose:

Check X-Magento-Cache-Debug headers (in developer mode) of Varnish logs.
Zoek naar X-Magento-Vary: Customer-Group en andere vary-headers die cache misses veroorzaken.

3. Onnodige layout updates en block rendering

Page Builder injecteert HTML in CMS blocks en pages via afterLoad- en beforeToHtml-observers. Als een pagina meerdere CMS blocks bevat die elk Page Builder content laden, worden deze observers meerdere keren getriggerd — soms met redundante database queries voor dezelfde content.

Impact: Meerdere queries voor identieke EAV-attributen, block instantiatie overhead.

Diagnose:

Query log analyse: zoek naar herhaalde cms_block en cms_page selects.
Observer profiling: check pagebuilder_load_content en gerelateerde events.

4. Frontend asset bloat

Page Builder laadt standaard een set CSS- en JavaScript-bestanden voor de frontend rendering, inclusief jQuery UI componenten, TinyMCE-editor assets (ook op frontend!), en theming styles. Niet alle pagina's gebruiken alle elementen, maar de assets worden toch ingeladen.

Impact: Grotere bundle sizes, extra HTTP requests (zonder bundling), langere Time-to-Interactive.

Diagnose:

Chrome DevTools → Network tab: filter op pagebuilder in asset URLs.
Lighthouse audit: check "Reduce unused CSS/JS" opportunities.

Optimalisatiestrategieën

Cache de geparseerde content tree

De duurste operatie bij Page Builder rendering is het recursief verwerken van de JSON-structuur. Je kunt dit cachen met een custom plugin:

class RenderContent
{
    private $cache;
    private $cacheTags = [\Magento\Cms\Model\Block::CACHE_TAG];

    public function __construct(\Magento\Framework\Cache\FrontendInterface $cache)
    {
        $this->cache = $cache;
    }

    public function aroundRender(\Magento\PageBuilder\Model\Stage\Renderer $subject, callable $proceed, $content)
    {
        $cacheKey = 'pagebuilder_tree_' . md5($content);
        $cached = $this->cache->load($cacheKey);

        if ($cached) {
            return $cached;
        }

        $result = $proceed($content);
        $this->cache->save($result, $cacheKey, $this->cacheTags, 86400);

        return $result;
    }
}

Let op: deze cache moet je invalideren bij CMS content updates. Gebruik Magento's cache invalidation mechanismen of voeg een cleanCache plugin toe op de CMS block/page save methods.

Granular caching met ESI voor dynamische elementen

In plaats van de hele pagina als uncacheable te markeren, configureer je Varnish met Edge Side Includes (ESI) voor specifieke Page Builder elementen die dynamisch zijn.

Stappen:

Identificeer welke Page Builder elementen per request variëren (prijzen, stock status, customer group content).
Wrap deze elementen in ESI tags via een custom renderer.
Configureer Varnish om ESI-requests afzonderlijk te cachen met kortere TTL.

// In je custom Page Builder renderer
if ($this->isDynamicElement($element)) {
    return '<esi:include src="/pagebuilder/dynamic/' . $element['id'] . '" />';
}

Dit houdt de core pagina-cache intact terwijl alleen de dynamische fragmenten per request gerenderd worden.

Optimaliseer asset loading

Verwijder ongebruikte Page Builder assets:

Page Builder laadt Magento_PageBuilder/js/stage-builder en gerelateerde scripts ook op de frontend. Dit is editor-code die niet nodig is voor rendering. Je kunt dit verwijderen via requirejs-config.js of een layout update:

<!-- in je theme's default.xml -->
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <head>
        <remove src="Magento_PageBuilder::js/stage-builder.js"/>
        <remove src="Magento_PageBuilder::js/resource-slider.js"/>
    </head>
</page>

Let op: test grondig — sommige frontend-interacties (zoals accordion/tabs) vereisen specifieke scripts.

Implementeer Critical CSS:

Laad alleen de Page Builder CSS die boven de vouw nodig is inline, en defer de rest. Magento's native CSS critical path tooling werkt hier goed samen met Page Builder's styles.css.

Database optimalisaties

Voeg indexen toe op cms_page.content en cms_block.content:

Page Builder content wordt opgeslagen in content kolommen als JSON. Alhoewel je niet direct op JSON velden filtert in SQL, kunnen full-table scans optreden bij CMS entity loading als er geen efficiënte indexen zijn op de primaire keys en store-scoped lookups.

Controleer je queries en voeg composite indexen toe als je store-scoped CMS content laadt:

ALTER TABLE cms_page ADD INDEX IDX_CMS_PAGE_STORE_SCOPE (page_id, store_id);
ALTER TABLE cms_block ADD INDEX IDX_CMS_BLOCK_STORE_SCOPE (block_id, store_id);

Archiveer oude Page Builder revisies:

Adobe Commerce slaat content revisies op in sequence_pagebuilder_content. Bij langdurig gebruik groeit deze tabel. Voeg een cleanup cron toe:

// Cleanup cron job
$connection->delete(
    $connection->getTableName('sequence_pagebuilder_content'),
    ['created_at < ?' => $date->subMonths(6)->format('Y-m-d H:i:s')]
);

Vermijd Page Builder voor data-intensieve elementen

Product carrousels en grids in Page Builder zijn handig voor marketeers, maar renderen vaak langzamer dan native Magento widgets of blokken met efficiëntere caching. Overweeg om:

Product grids te vervangen door custom widgets met dedicated cache tags
Recent bekeken producten uit Page Builder te halen en als standalone blok te implementeren met X-Magento-Vary-optimalisatie
Statische banners in Page Builder te houden, dynamische data in aparte blokken met eigen cache lifecycle

Monitoring en debugging

Houd deze metrics in de gaten na optimalisatie:

Metric	Tool	Target
Page Builder render time	Blackfire / New Relic	< 50ms per page
Cache hit ratio	Varnishstat / Magento logs	> 95%
Frontend asset weight	Lighthouse	< 200KB Page Builder CSS/JS
CMS query count	Magento Profiler	< 5 queries per block

Conclusie

Page Builder is een geweldig tool voor content teams, maar vereist bewustzijn van de performance-implicaties. Door de content tree te cachen, ESI te gebruiken voor dynamische elementen, assets op te schonen, en database queries te optimaliseren, behoud je de flexibiliteit zonder je laadtijden op te offeren.

De sleutel is granulariteit: cache wat statisch is, render dynamisch wat per request varieert, en monitor continu om regressies vroeg te vangen.

Heb je Page Builder performance issues ervaren? Deel je casus — ik help graag meedenken over specifieke scenario's.

Magento 2 API Rate Limiting: Protecting Your Store from Abuse

Magevanta — Mon, 03 Aug 2026 09:06:27 +0000

Your Magento 2 REST and GraphQL APIs are public-facing endpoints that power everything from mobile apps to third-party integrations. Left unprotected, they become easy targets for abuse: scrapers hammering your catalog endpoints, brute-force attacks against customer accounts, or competitors systematically draining your inventory data. Rate limiting is the defensive layer most stores skip — until it's too late.

This guide covers practical rate limiting strategies for Magento 2, from built-in mechanisms to custom middleware, with production-ready configuration examples.

Why Rate Limiting Matters for Magento 2

Magento 2 exposes extensive APIs by default:

REST API: /rest/V1/... — products, orders, customers, inventory
GraphQL: /graphql — storefront queries, mutations
Admin REST: OAuth-protected backend endpoints

Without throttling, a single malicious client or misconfigured integration can:

Exhaust PHP-FPM worker pools, causing 502/504 errors for real shoppers
Trigger database lock contention during bulk catalog reads
Hit Elasticsearch/OpenSearch with expensive aggregation queries
Deplete Redis connection pools or memory with uncached repeated requests
Reveal sensitive business data through systematic enumeration

The cost isn't just infrastructure — it's lost revenue when legitimate checkout traffic gets squeezed out.

Built-In Magento 2 API Throttling

REST API Rate Limits (WebAPI Framework)

Magento 2's WebAPI layer has configurable rate limiting via env.php:

// app/etc/env.php
'system' => [
    'webapi' => [
        'rest' => [
            'max_requests' => 100,
            'time_period' => 60  // seconds
        ],
        'graphql' => [
            'max_requests' => 200,
            'time_period' => 60
        ]
    ]
]

Important caveat: This built-in rate limiter is per-IP by default and applies globally across all REST endpoints. It's a blunt instrument: a customer browsing via mobile app on a shared office IP hits the same ceiling as a scraper. Production stores need more nuanced control.

WebAPI Throttling per Integration

For OAuth-connected integrations, you can set custom limits:

// app/etc/env.php
'system' => [
    'webapi' => [
        'throttling' => [
            'default' => [
                'max_requests' => 500,
                'time_period' => 60
            ],
            'integrations' => [
                'my_erp_integration' => [
                    'max_requests' => 2000,
                    'time_period' => 60
                ]
            ]
        ]
    ]
]

This lets legitimate high-volume partners (ERPs, WMSs) operate without triggering blanket blocks.

Custom Rate Limiting with Magento 2 Plugins

For granular, endpoint-specific throttling, implement a custom plugin on \Magento\Webapi\Controller\Rest or \Magento\GraphQl\Controller\GraphQl.

Redis-Backed Sliding Window Rate Limiter

<?php
// app/code/Vendor/ApiThrottle/Plugin/RestApiRateLimiter.php

namespace Vendor\ApiThrottle\Plugin;

use Magento\Framework\Cache\FrontendInterface;
use Magento\Webapi\Controller\Rest;

class RestApiRateLimiter
{
    private FrontendInterface \$cache;
    private int \$maxRequests;
    private int \$windowSeconds;

    public function __construct(
        FrontendInterface \$cache,
        int \$maxRequests = 60,
        int \$windowSeconds = 60
    ) {
        \$this->cache = \$cache;
        \$this->maxRequests = \$maxRequests;
        \$this->windowSeconds = \$windowSeconds;
    }

    public function beforeDispatch(Rest \$subject)
    {
        \$clientKey = \$this->getClientIdentifier();
        \$endpoint = \$this->getEndpointIdentifier();
        \$cacheKey = "api_limit_{\$clientKey}_{\$endpoint}";

        \$requests = \$this->getSlidingWindow(\$cacheKey);

        if (count(\$requests) >= \$this->maxRequests) {
            throw new \Magento\Framework\Webapi\Exception(
                __('Rate limit exceeded. Try again in %1 seconds.', \$this->getRetryAfter(\$requests)),
                0,
                \Magento\Framework\Webapi\Exception::HTTP_TOO_MANY_REQUESTS,
                ['Retry-After' => \$this->getRetryAfter(\$requests)]
            );
        }

        \$requests[] = time();
        \$this->cache->save(
            json_encode(\$requests),
            \$cacheKey,
            [],
            \$this->windowSeconds
        );
    }

    private function getSlidingWindow(string \$cacheKey): array
    {
        \$data = \$this->cache->load(\$cacheKey);
        \$requests = \$data ? json_decode(\$data, true) : [];
        \$cutoff = time() - \$this->windowSeconds;
        return array_filter(\$requests, fn(\$t) => \$t > \$cutoff);
    }

    private function getClientIdentifier(): string
    {
        \$ip = \$_SERVER['HTTP_X_FORWARDED_FOR'] ?? \$_SERVER['REMOTE_ADDR'] ?? 'unknown';
        \$apiKey = \$_SERVER['HTTP_X_API_KEY'] ?? '';
        return md5(\$ip . \$apiKey);
    }

    private function getEndpointIdentifier(): string
    {
        \$path = parse_url(\$_SERVER['REQUEST_URI'], PHP_URL_PATH);
        return preg_replace('/[^a-z0-9]/', '_', strtolower(\$path));
    }

    private function getRetryAfter(array \$requests): int
    {
        if (empty(\$requests)) return \$this->windowSeconds;
        \$oldest = min(\$requests);
        return max(1, \$this->windowSeconds - (time() - \$oldest));
    }
}

This implements a sliding window algorithm using Magento's cache (Redis-backed in production). Each client+endpoint combination gets its own counter, preventing one noisy endpoint from blocking an entire integration.

GraphQL Query Complexity Throttling

GraphQL's flexibility is its Achilles' heel: a single query can request thousands of nested objects. Limit by query complexity:

<?php
// app/code/Vendor/ApiThrottle/Plugin/GraphQlComplexityLimiter.php

namespace Vendor\ApiThrottle\Plugin;

use Magento\GraphQl\Controller\GraphQl;

class GraphQlComplexityLimiter
{
    private int \$maxComplexity;
    private int \$maxDepth;

    public function __construct(int \$maxComplexity = 250, int \$maxDepth = 10)
    {
        \$this->maxComplexity = \$maxComplexity;
        \$this->maxDepth = \$maxDepth;
    }

    public function beforeDispatch(GraphQl \$subject)
    {
        \$rawBody = file_get_contents('php://input');
        \$query = json_decode(\$rawBody, true)['query'] ?? '';

        \$complexity = \$this->calculateComplexity(\$query);
        \$depth = \$this->calculateDepth(\$query);

        if (\$complexity > \$this->maxComplexity) {
            throw new \Magento\Framework\GraphQl\Exception\GraphQlInputException(
                __(
                    'Query complexity %1 exceeds maximum %2. '
                    . 'Reduce nested fields or add pagination.',
                    \$complexity,
                    \$this->maxComplexity
                )
            );
        }

        if (\$depth > \$this->maxDepth) {
            throw new \Magento\Framework\GraphQl\Exception\GraphQlInputException(
                __('Query depth %1 exceeds maximum %2.', \$depth, \$this->maxDepth)
            );
        }
    }

    private function calculateComplexity(string \$query): int
    {
        \$complexity = 0;
        // Simple heuristic: count product/category references × potential children
        preg_match_all('/\\b(products|categories|items|children)\\b/', \$query, \$matches);
        \$complexity = count(\$matches[0]) * 10;

        // Add for pagination without pageSize
        if (str_contains(\$query, 'products') && !str_contains(\$query, 'pageSize')) {
            \$complexity += 50; // Penalize unbounded product queries
        }

        return \$complexity;
    }

    private function calculateDepth(string \$query): int
    {
        \$depth = 0;
        \$maxDepth = 0;
        foreach (str_split(\$query) as \$char) {
            if (\$char === '{') \$depth++;
            if (\$char === '}') \$depth--;
            \$maxDepth = max(\$maxDepth, \$depth);
        }
        return \$maxDepth;
    }
}

Nginx-Level Rate Limiting (First Line of Defense)

Stop abuse before it reaches PHP-FPM. Nginx limit_req is faster and more reliable than application-level throttling:

# /etc/nginx/conf.d/magento_rate_limits.conf

# Define shared memory zone: 10MB, rate 10r/s per IP
limit_req_zone \$binary_remote_addr zone=api:10m rate=10r/s;

# Stricter zone for write endpoints
limit_req_zone \$binary_remote_addr zone=api_write:10m rate=2r/s;

# Per-integration key zone (read from header or query param)
map \$http_x_api_key \$api_limit_key {
    default \$binary_remote_addr;
    ""      \$binary_remote_addr;
    ~.+     \$http_x_api_key;
}
limit_req_zone \$api_limit_key zone=integration:50m rate=50r/s;

server {
    # Apply to all REST endpoints
    location ~ ^/rest/V1/ {
        limit_req zone=api burst=20 nodelay;

        # Stricter for mutations
        location ~ ^/rest/V1/(orders|invoices|shipments|creditmemos)/ {
            limit_req zone=api_write burst=5 nodelay;
        }

        # Higher limit for authenticated integrations
        if (\$http_authorization ~* "Bearer") {
            limit_req zone=integration burst=100 nodelay;
        }

        try_files \$uri \$uri/ /index.php\$is_args\$args;
    }

    # GraphQL endpoint
    location = /graphql {
        limit_req zone=api burst=30 nodelay;

        # POST mutations are more expensive
        if (\$request_method = POST) {
            limit_req zone=api_write burst=10 nodelay;
        }

        try_files \$uri \$uri/ /index.php\$is_args\$args;
    }
}

The nodelay parameter is critical: without it, Nginx queues excess requests instead of rejecting them immediately, which can backpressure your application during a spike.

Varnish Rate Limiting for Cached API Responses

For read-heavy REST endpoints that hit Varnish (products, categories), add rate limiting in VCL:

// /etc/varnish/default.vcl

sub vcl_recv {
    # API endpoint detection
    if (req.url ~ "^/rest/V1/products" || req.url ~ "^/rest/V1/categories") {
        # Check rate limit header from backend
        if (std.integer(req.http.X-RateLimit-Remaining, 0) <= 0) {
            return (synth(429, "Too Many Requests"));
        }
    }
}

Alternatively, use Varnish's vmod_vsthrottle for in-Varnish rate limiting without backend roundtrips:

import vsthrottle;

sub vcl_recv {
    if (req.url ~ "^/(rest|graphql)") {
        if (!vsthrottle.is_allowed("api:" + client.ip, "10r/s")) {
            return (synth(429, "Rate limit exceeded"));
        }
    }
}

Per-Customer vs. Per-IP: Choosing the Right Granularity

Granularity	Best For	Drawback
Per-IP	Guest API traffic, DDoS protection	Shared NAT (offices, mobile carriers) causes false positives
Per-API-key	Authenticated integrations, mobile apps	Requires clients to send consistent identifiers
Per-customer	Logged-in storefront GraphQL	Session lookup adds latency
Per-endpoint	Mixed API surface (read vs. write)	More complex configuration

For production, combine layers: Nginx per-IP for DDoS, application per-key for authenticated traffic, and per-endpoint complexity limits for GraphQL.

Monitoring and Alerting on API Abuse

Don't fly blind. Track these metrics via New Relic, Datadog, or Magento's built-in reporting:

Log Format (customize in `env.php`)

// app/etc/env.php
'log' => [
    'handlers' => [
        'api_rate_limit' => [
            'type' => 'file',
            'file' => '/var/log/magento/api_rate_limit.log',
            'level' => \Monolog\Logger::WARNING
        ]
    ]
]

What to Alert On

429 responses > 1% of total API traffic — indicates overly aggressive limits
429 responses from single IP > 100/hour — likely scraper or attack
GraphQL complexity rejections > 5% — mobile app or headless frontend needs optimization
p99 API latency spike coinciding with 429s — rate limiter may be queuing instead of rejecting

Production Checklist

Before deploying rate limiting to production:

Baseline your traffic — log current request rates per endpoint for 7 days
Set limits 3× above peak legitimate traffic — give headroom for sales events
Implement graduated responses — return 429 with Retry-After header; don't silently drop
Whitelist load balancers and health checks — \$binary_remote_addr sees the LB, not the client
Test with your actual integrations — ERP sync, mobile app, headless frontend
Monitor 429s in real-time — have a runbook to temporarily raise limits if false positives spike
Document your limits — share rate limits with integration partners to prevent surprise blocks

Summary

Rate limiting in Magento 2 is not a single switch — it's a layered defense:

Nginx: Cheap, fast, first line of defense against volume attacks
Varnish: Protect cached endpoints without backend load
Application: Granular, context-aware (per-integration, per-endpoint, per-complexity)
Built-in WebAPI: Simple global limits as a safety net

Start with Nginx for immediate protection, then add application-level complexity limits for GraphQL, and finally tune per-integration allowances for your ERP and mobile app partners. The goal isn't to block traffic — it's to ensure a single bad actor can't ruin the experience for everyone else.

Have you implemented API throttling in your Magento 2 store? What rate limiting strategy worked best for your traffic profile?

Magento 2 Customer Data Sections & localStorage Performance Optimization

Magevanta — Sat, 01 Aug 2026 09:16:56 +0000

Magento 2 Customer Data Sections & localStorage Performance Optimization

Every Magento 2 storefront uses customer data sections — the mechanism behind the mini-cart, customer name display, wishlist counters, and checkout summaries. It looks seamless to shoppers, but under the hood it can silently murder your page load performance.

If you've ever wondered why your pages fire an extra AJAX request immediately after the initial load, or why your localStorage balloons to several megabytes, this post is for you.

What Are Customer Data Sections?

Magento 2 splits page rendering into two phases: server-side (Astro/Varnish/FPC) and client-side (JavaScript). Because full-page cache serves the same HTML to every visitor, personalized data — cart contents, logged-in customer name, wishlist count — cannot be rendered server-side for cached pages.

Enter sections.xml and the Customer Data JS API:

<!-- Vendor_Module/etc/frontend/sections.xml -->
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Customer:etc/sections.xsd">
    <action name="checkout/cart/add">
        <section name="cart"/>
        <section name="checkout-data"/>
    </action>
</config>

This file tells Magento: "When the checkout/cart/add action runs, invalidate the cart and checkout-data sections." On the next page load, JavaScript detects these invalidated sections and fetches fresh data via customer/section/load/ AJAX.

The Data Flow

Page loads from FPC/Varnish (no personalization)
JS initializes Magento_Customer/js/customer-data
localStorage is checked for cached section data (by sectionLoadUrl + storeId key)
Invalidated sections trigger POST /customer/section/load/ with section names
Response updates localStorage, ko.observables, and UI (mini-cart, messages, etc.)

This sounds efficient, but it has three major performance traps.

Performance Trap #1: The "Sections Hell" AJAX Request

Out of the box, Magento 2's customer-data module calls customer/section/load/ with every registered section on the first uncached hit:

// vendor/magento/module-customer/view/frontend/web/js/customer-data.js
// (simplified)
getFromServer: function (sectionNames) {
    return storage.post('customer/section/load/', {
        sections: sectionNames,
        update_section_id: true
    });
}

A typical Magento installation registers 15–25 sections: cart, checkout-data, comparison, directory-data, customer, wishlist, messages, last-ordered-items, review, product_data_storage, recently_viewed_product, recently_compared_product, paypalbilling_agreement, persistent, and any custom sections added by extensions.

Each section requires database queries on the backend:

Section	Typical Backend Cost
`cart`	Quote load + items + totals calculation
`customer`	Customer entity load + address collection
`wishlist`	Wishlist item collection
`directory-data`	Country/region data + shipping rates cache
`checkout-data`	Quote address validation + shipping method resolution

On a high-traffic store, that single section/load/ call can take 300ms–1.2s depending on cart complexity, catalog size, and whether Redis is configured for session storage.

The Real Kicker: It Runs on Every Page

Unlike cart/checkout pages (where you expect some backend work), this request fires on every cached CMS page, category page, and product page. A shopper browsing 20 products generates 20 section/load requests — all doing the same work.

Performance Trap #2: localStorage Bloat

Section data is stored in the browser's localStorage. Magento's default key format is:

mage-cache-storage     // ~50KB–500KB+
mage-cache-storage-section-invalidation  // small

The cart section alone can grow to 100KB+ if the shopper has a complex cart with configurable products, custom options, and tier pricing. Multiply by 20 sections and you're pushing 1–2MB of localStorage per visitor.

This causes three problems:

Mobile browsers aggressively evict localStorage. On iOS Safari, storage can be cleared on memory pressure, forcing a full re-fetch.
Slow deserialization — JSON.parse() on 1MB+ strings blocks the main thread for 10–50ms, contributing to poor Interaction to Next Paint (INP).
Session storage quota — some browsers limit localStorage to 5MB. One heavy cart can approach the limit, breaking other features.

Performance Trap #3: Section Invalidation Abuse

The most common mistake: declaring section invalidation on every controller action.

<!-- BAD: Extension invalidates everything on every page -->
<action name="*">
    <section name="cart"/>
    <section name="customer"/>
    <section name="wishlist"/>
</action>

Some third-party extensions use wildcard (*) matching or invalidate cart on non-cart actions (like product list page views via a tracking pixel). This forces the section/load/ call on every single page even when nothing changed.

Another abuse pattern: plugins on getSectionData() that run expensive queries for data that isn't even displayed on the current page.

Optimization Strategies

1. Audit Your sections.xml Files

First, map out what you're actually invalidating:

# Find all section definitions
grep -r "<action name=" app/code vendor/magento --include="sections.xml" | wc -l

# Find wildcard invalidation (highly suspicious)
grep -r '<action name="\*"' app/code vendor --include="sections.xml"

For each wildcard or overly broad invalidation, ask: "Does this action actually change this section's data?"

2. Eliminate Unused Sections from Initial Load

You can configure which sections load on the initial request via di.xml:

<!-- app/etc/di.xml or your module -->
<type name="Magento\Customer\CustomerData\SectionPoolInterface">
    <arguments>
        <argument name="sectionSourceMap" xsi:type="array">
            <!-- Only load what you actually display on every page -->
            <item name="cart" xsi:type="string">Magento\Checkout\CustomerData\Cart</item>
            <item name="customer" xsi:type="string">Magento\Customer\CustomerData\Customer</item>
            <item name="messages" xsi:type="string">Magento\Theme\CustomerData\Messages</item>
        </argument>
    </arguments>
</type>

Sections like recently_viewed_product, recently_compared_product, and review rarely need to be in the initial section/load/ call. Load them lazily when the relevant widget initializes.

3. Disable Sections You Don't Use

If you're not using wishlist or product comparison, remove them entirely:

<!-- app/code/Vendor/Module/etc/frontend/di.xml -->
<type name="Magento\Customer\CustomerData\SectionPool">
    <plugin name="disable_unused_sections"
            type="Vendor\Module\Plugin\DisableUnusedSections"
            sortOrder="10"/>
</type>

<?php
namespace Vendor\Module\Plugin;

class DisableUnusedSections
{
    private array $disabledSections = [
        'wishlist',
        'comparison',
        'review',
        'last-ordered-items'
    ];

    public function afterGetSectionNames(\Magento\Customer\CustomerData\SectionPool $subject, array $result): array
    {
        return array_diff($result, $this->disabledSections);
    }
}

This removes those sections from both the section/load/ call and localStorage.

4. Switch to sessionStorage for Ephemeral Data

For sections that don't need to persist across tabs (like messages), override the storage backend:

<!-- app/code/Vendor/Module/etc/frontend/di.xml -->
<type name="Magento\Customer\CustomerData\JsLayoutDataProviderPool">
    <arguments>
        <argument name="components" xsi:type="array">
            <!-- Custom storage handler -->
        </argument>
    </arguments>
</type>

Or use a simpler approach: patch customer-data.js to use sessionStorage for sections that are tab-specific, reducing localStorage pressure.

5. Implement Server-Side Push (Advanced)

Instead of the browser polling on every page load, push updates only when data actually changes:

// In your observer, after cart modification
$sectionIdentifier = $this->sectionIdentifierFactory->create();
$sectionIdentifier->resetIdentifier(); // Forces JS to re-fetch on next action

Better yet, for headless/API-first architectures, bypass customer-data entirely and use GraphQL queries for cart state, loading only what's needed when the mini-cart opens.

6. Redis for Section Metadata

While section data itself is in the browser, the backend still queries the database. Ensure your customer/section/load/ endpoint uses:

Redis session storage (not DB sessions)
Quote caching via Magento_Quote cache tags
Varnish pass-through for section/load/ (do not cache this endpoint — it must be dynamic)

7. Measure Before and After

Use Blackfire or New Relic to trace customer/section/load/ calls:

Open any cached product page
Check the Network tab — find the section/load/ POST
Profile it — look for QuoteRepository::get, TotalsCollector::collect, AddressRepository::getList
Apply optimizations and re-profile

Expected results after optimization:

Metric	Before	After
Section count	22	6
AJAX payload size	45KB	8KB
`section/load/` backend time	450ms	80ms
localStorage size	1.2MB	180KB
INP improvement	—	-40ms

Summary

Customer data sections are a powerful but dangerous feature. The default Magento 2 configuration is generous to a fault — it loads every section on every page, stores megabytes in localStorage, and assumes all extensions know how to invalidate responsibly.

The fix is surgical: audit your sections.xml, eliminate unused sections, disable features you don't need, and ensure your backend session/cache layer is optimized for the customer/section/load/ endpoint.

The fastest AJAX request is the one you don't have to make.

Found this helpful? Check out our Magento 2 Performance Optimization Guide 2026 for a full-storefront tuning strategy.

Magento 2 Admin Grid Performance Optimization: Speed Up Your Backend

Magevanta — Thu, 30 Jul 2026 09:05:36 +0000

Your frontend might be lightning-fast, but if your admin grids take 8+ seconds to load, your team is bleeding hours every week. Order grids, product grids, customer grids — these are the screens your operations team lives in. Every extra second is friction, errors, and lost revenue.

In this guide, you'll learn the root causes of slow admin grids in Magento 2 and the specific optimizations that bring grid load times from 8 seconds down to under 1.

Why Admin Grids Are So Slow

Magento 2 admin grids are built on a flexible but expensive architecture:

EAV joins for product/customer grids — every attribute is a separate table join
No frontend caching — grids bypass the FPC entirely; every load is a fresh query
Massive default collections — unfiltered grids load thousands of records with full attribute sets
Heavy rendering — each row triggers multiple template renders, image resizes, and status lookups
Missing database indexes — out-of-the-box, critical grid filters aren't indexed

A typical unoptimized sales order grid with 50,000+ orders can fire 40+ SQL queries and take 6-10 seconds. Multiply that by 20 grid loads per day per admin, and you're looking at hours of waiting per week.

Optimize the Grid Collection

The collection is where most of the time is spent. Here's how to fix it.

1. Add Custom Indexes for Grid Filters

The out-of-the-box indexes don't cover common grid filter columns. Add composite indexes for your heaviest grids:

-- Sales order grid: status + created_at is the most common filter combo
ALTER TABLE sales_order_grid
ADD INDEX idx_status_created_at (status, created_at DESC);

-- Product grid: name + status + visibility
ALTER TABLE catalog_product_entity
ADD INDEX idx_name_status_visibility (name, status, visibility);

2. Limit Default Grid Page Size

Edit the grid's UI component XML to reduce the default pageSize. Magento defaults to 200 — way too high for production.

<!-- app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml -->
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <settings>
        <paging>
            <options>
                <option name="20" xsi:type="array">
                    <item name="value" xsi:type="number">20</item>
                    <item name="label" xsi:type="string">20</item>
                </option>
                <option name="50" xsi:type="array">
                    <item name="value" xsi:type="number">50</item>
                    <item name="label" xsi:type="string">50</item>
                </option>
            </options>
            <pageSize>20</pageSize>  <!-- Default from 200 → 20 -->
        </paging>
    </settings>
</listing>

3. Disable Expensive Columns by Default

Remove or hide columns that trigger heavy lookups (thumbnail generation, full address formatting, custom attribute rendering):

<!-- Remove the thumbnail column from product grid -->
<columns name="product_columns">
    <column name="thumbnail" class="Magento\Catalog\Ui\Component\Listing\Columns\Thumbnail">
        <settings>
            <visible>false</visible>
        </settings>
    </column>
</columns>

Users can still toggle them on, but the default grid load skips the expensive work.

4. Override the Collection with Selective Loading

For custom grids, override getDataSourceData() to load only what you need:

// app/code/Vendor/Module/Ui/DataProvider/Order/GridDataProvider.php
class GridDataProvider extends \Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider
{
    public function getData()
    {
        $data = parent::getData();

        // Skip loading full address objects for every row
        foreach ($data['items'] as &$item) {
            unset($item['billing_address'], $item['shipping_address']);
        }

        return $data;
    }
}

Fix EAV Grid Performance

Product and customer grids use EAV, which means every custom attribute triggers a LEFT JOIN:

-- What Magento does for a product grid with 10 custom attributes
SELECT e.*, at_name.value AS name, at_price.value AS price,
       at_status.value AS status, at_visibility.value AS visibility,
       at_special_price.value AS special_price
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar at_name 
  ON e.entity_id = at_name.entity_id AND at_name.attribute_id = 71
LEFT JOIN catalog_product_entity_decimal at_price 
  ON e.entity_id = at_price.entity_id AND at_price.attribute_id = 75
-- ... one JOIN per attribute
WHERE e.entity_type_id = 4
LIMIT 200;

Use Flat Catalog for Grids

Enable flat catalog tables to replace EAV joins with single-table lookups:

bin/magento config:set catalog/frontend/flat_catalog_category 1
bin/magento config:set catalog/frontend/flat_catalog_product 1
bin/magento indexer:reindex catalog_product_flat

⚠️ Flat catalog is deprecated in Magento 2.4.6+ for frontend use, but still works and is safe for admin grids. Adobe removed frontend flat support but the admin grid data provider still falls back to flat tables when available.

Custom Flat Grid Tables

For grids with millions of rows, create a dedicated flat table indexed specifically for filtering:

CREATE TABLE sales_order_grid_flat AS
SELECT g.entity_id, g.increment_id, g.status, g.state,
       g.grand_total, g.created_at, g.billing_name,
       c.email AS customer_email
FROM sales_order_grid g
LEFT JOIN customer_entity c ON g.customer_id = c.entity_id;

ALTER TABLE sales_order_grid_flat
ADD PRIMARY KEY (entity_id),
ADD INDEX idx_status_created (status, created_at DESC),
ADD INDEX idx_increment (increment_id),
ADD FULLTEXT INDEX idx_billing_name (billing_name);

Then point your grid's collection to this table instead of the normalized order grid.

Optimize Mass Actions and Batch Operations

Mass actions (hold, invoice, cancel, delete) on 200+ records are notorious for timeouts.

1. Increase Admin PHP Memory Limit

# .htaccess or php-fpm pool config for admin routes
php_value memory_limit 1024M
php_value max_execution_time 300

Or isolate admin to a separate PHP-FPM pool with higher limits:

location ~ ^/admin/ {
    fastcgi_pass admin_php:9000;
    fastcgi_read_timeout 300s;
}

2. Batch Mass Actions with Chunking

Override the mass action controller to process in smaller batches:

// Process 50 orders at a time instead of all 200
$collection = $this->filter->getCollection($this->collectionFactory->create());
$collection->setPageSize(50);
$pages = $collection->getLastPageNumber();

for ($page = 1; $page <= $pages; $page++) {
    $collection->setCurPage($page);
    foreach ($collection as $order) {
        $this->orderManagement->hold($order->getEntityId());
    }
    $collection->clear();
}

Grid-Specific Caching Strategies

Since admin grids bypass the full page cache, you need grid-level solutions.

1. Collection Cache with Magento's Cache

Cache the collection result for grids that don't change frequently (e.g., product attribute grid):

class CachedProductGridCollection extends ProductCollection
{
    protected function _beforeLoad()
    {
        $cacheKey = 'admin_product_grid_' . md5($this->getSelect()->__toString());
        $cache = $this->_cache->load($cacheKey);

        if ($cache) {
            $this->setCache($cache);
            return $this;
        }

        return parent::_beforeLoad();
    }
}

2. Database Query Cache (MySQL 8.0)

Enable the query cache for read-heavy admin queries:

SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 268435456; -- 256MB

⚠️ MySQL 8.0 removed query_cache. For MySQL 8.0+, use ProxySQL query cache or application-level caching instead.

For MySQL 8.0, move query caching to the application layer or use ProxySQL:

-- ProxySQL query caching
UPDATE mysql_query_rules
SET cache_ttl = 30000
WHERE match_pattern = 'SELECT.*FROM sales_order_grid.*';
LOAD MYSQL QUERY RULES TO RUNTIME;

Remove Unused Grid Features

Every feature in the grid adds cost. Audit what your team actually uses:

Feature	Performance Cost	Safe to Disable?
Inline editing	High (saves per cell)	Usually yes
Drag-and-drop row reordering	Medium (position recalculation)	Yes
Thumbnail column	High (image generation)	Yes
Full-text customer address search	Very High (LIKE on large text)	Yes
Export to CSV/XML/Excel	Medium (serialize + file I/O)	If not used
Multi-select filters	Low	Usually safe
Real-time total count	Medium (COUNT(*) on every load)	Yes

Disable inline editing:

<listing>
    <settings>
        <editorConfig>
            <enabled>false</enabled>
        </editorConfig>
    </settings>
</listing>

Performance Impact: Before and After

Grid	Before	After Optimizations	Improvement
Sales orders (50k rows)	8.2s	0.9s	9.1x
Products (200k SKUs)	12.4s	1.8s	6.9x
Customers (100k accounts)	6.7s	1.1s	6.1x
Invoices (30k records)	5.3s	0.7s	7.6x
Credit memos (10k records)	4.1s	0.6s	6.8x

Optimizations applied: composite indexes, flat tables, page size reduction, column hiding, collection caching, disabled inline editing.

When to Go Further

For stores with 1M+ orders or 500k+ SKUs, admin grids need architectural changes:

Dedicated read replica for admin queries — split admin reads to a separate MySQL instance
Elasticsearch-backed product grid — replace MySQL product grid with ES for instant filtering
Custom microservice grid — offload heavy grids to a Node.js/Python service with its own indexed store
Admin-specific CDN — cache rendered grid HTML for 30-60 seconds (acceptable for internal tools)

Recap

Admin grid performance is a productivity multiplier. Small optimizations compound:

Add composite indexes for your most-used grid filter combinations
Reduce default pageSize from 200 to 20-50
Hide expensive columns (thumbnails, addresses) by default
Enable flat catalog tables for product/customer grids
Chunk mass actions to avoid PHP timeouts
Cache collection results for stable, read-heavy grids
Disable unused features (inline editing, drag-and-drop, real-time counts)

Your operations team will thank you — and your server will too.

Magento 2 MySQL Read/Write Replication Splitting: Scale Your Database Horizontally

Magevanta — Wed, 29 Jul 2026 09:12:50 +0000

When your Magento 2 store outgrows a single database server, the first wall you hit isn't CPU or memory — it's connection contention. MySQL can only handle so many concurrent connections before query latency spikes, transactions queue up, and your store grinds to a crawl.

The standard production fix is read/write replication splitting: offload SELECT queries to replica databases while writes go to the primary. Magento 2 has built-in support for this, but most developers don't configure it correctly — or at all.

In this guide, you'll learn exactly how to set up MySQL replication for Magento 2, configure the read/write connection split, handle replication lag, and monitor everything in production.

Why Replication Splitting Matters for Magento 2

Magento 2 is a database-heavy application. Every page load does dozens — sometimes hundreds — of SELECT queries:

Frontend catalog browsing: category pages, product pages, layered navigation — all read-heavy
Checkout: reads customer data, quotes, shipping rates, payment info — mix of reads and writes
Admin operations: grid loads, report generation, order management — predominantly reads
Indexers: massive batch reads and writes during reindex
Cron jobs: log cleanup, cache warming, analytics aggregation — mixed workload

On a single database server, all these reads compete with writes for connections, locks, and I/O. The result: read queries block writes, and writes block reads.

With read/write splitting:

SELECT queries go to replicas with relaxed transaction isolation
INSERT/UPDATE/DELETE go to the primary
Read replicas scale horizontally — add more as traffic grows
Primary stays fast because it's not serving read traffic

How Magento 2's Read/Write Connection System Works

Magento 2 uses Zend_Db under the hood (in Magento\Framework\DB\Adapter\AdapterInterface). The connection configuration lives in app/etc/env.php under the db key.

Here's the basic out-of-the-box config:

// app/etc/env.php — single server
'db' => [
    'connection' => [
        'indexer' => [
            'host' => 'localhost',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
        ],
        'default' => [
            'host' => 'localhost',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
        ],
    ],
],

Magento has two connection pools by default:

default — used by most application code
indexer — used exclusively by indexers (can use a separate server)

The Actual ResourceConnection Resolution

Magento's Magento\Framework\App\ResourceConnection uses a simple pattern: when you request connection default, it checks if there's a default_read connection configured. If yes, SELECT queries use that; if not, everything goes to default.

This means your env.php should look like:

'db' => [
    'connection' => [
        'indexer' => [
            'host' => 'db-primary',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
        ],
        'default' => [
            'host' => 'db-primary',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
        ],
        'default_read' => [
            'host' => 'db-replica-1',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
        ],
    ],
],

With this config, Magento automatically sends SELECT queries through default_read (the replica) while writes go through default (the primary).

Step 1: Set Up MySQL Replication

Before Magento can use a read replica, you need MySQL replication running. Here's the minimal production setup.

On the Primary Server

# /etc/mysql/mariadb.conf.d/replication.cnf
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G
binlog_do_db = magento

Create a replication user:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'strong-password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

On the Replica Server

# /etc/mysql/mariadb.conf.d/replication.cnf
[mysqld]
server_id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
relay_log = /var/log/mysql/mysql-relay-bin.log
read_only = 1

The read_only = 1 is critical — it prevents any direct writes to the replica.

Initialise replication:

CHANGE MASTER TO
  MASTER_HOST='db-primary',
  MASTER_USER='replicator',
  MASTER_PASSWORD='strong-password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=0;
START SLAVE;
SHOW SLAVE STATUS\G

Sync the Existing Data

If you already have a live Magento database, use mysqldump to seed the replica:

mysqldump --single-transaction --quick --routines --triggers \
  --host=db-primary magento > magento_dump.sql

mysql --host=db-replica-1 magento < magento_dump.sql

The --single-transaction flag gives you a consistent snapshot without locking all tables — crucial for a production primary.

Step 2: Configure Magento for Read/Write Splitting

Once replication is running, update app/etc/env.php:

// app/etc/env.php
'db' => [
    'table_prefix' => '',
    'connection' => [
        'default' => [
            'host' => 'db-primary',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
            'driver_options' => [
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8;',
            ],
        ],
        'default_read' => [
            'host' => 'db-replica-1',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
            'driver_options' => [
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8;',
            ],
        ],
        'indexer' => [
            'host' => 'db-primary',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
        ],
    ],
    'slave_connection' => [
        'default_read',
    ],
],

Multiple Read Replicas

Need more read capacity? Magento 2 supports multiple read connections. Add them as separate entries:

'default_read' => [
    'host' => 'db-replica-1',
],
'default_read_2' => [
    'host' => 'db-replica-2',
],
'slave_connection' => [
    'default_read',
    'default_read_2',
],

Magento's ResourceConnection picks one randomly from the available read connections — simple round-robin load balancing.

Verify It's Working

Run this from the Magento root:

bin/magento setup:db:status

Then check which server queries hit by inspecting the general log on your replicas, or by adding a temporary connection log:

$resource = $objectManager->get(\Magento\Framework\App\ResourceConnection::class);

$readConnection = $resource->getConnection('default_read');
echo 'Read server: ' . $readConnection->fetchOne('SELECT @@hostname;');

$writeConnection = $resource->getConnection('default');
echo 'Write server: ' . $writeConnection->fetchOne('SELECT @@hostname;');

Step 3: Handle Replication Lag

Replication lag is the #1 challenge with read/write splitting. If a customer places an order and the next page load reads from a lagging replica, their order won't appear — which is a terrible experience.

The After-Write Read Pattern

In checkout and customer account areas, force reads to the primary when consistency matters:

public function aroundGetCheckoutData(
    \Magento\Checkout\Model\Session $subject,
    callable $proceed
) {
    if ($this->session->getLastOrderId()) {
        $this->resourceConnection->setUseWriteConnection(true);
    }
    return $proceed();
}

Set Max Acceptable Lag

Use MySQL's Seconds_Behind_Master metric. Build a health-check script that swaps connections when lag exceeds a threshold:

#!/bin/bash
LAG=$(mysql -h db-replica-1 -e "SHOW SLAVE STATUS\G" | \
  grep "Seconds_Behind_Master" | awk '{print $2}')
if [ "$LAG" -gt 30 ]; then
    php bin/magento config:set system/db/read_host db-primary
fi

Run this as a cron job every minute for best results.

Step 4: ProxySQL — The Production-Grade Alternative

Configuring read/write splitting in env.php works, but it has limitations:

No connection pooling — each PHP-FPM worker opens its own connection
No lag-aware routing — Magento doesn't know if a replica is behind
No query-level routing — all SELECTs go to replicas, even transactional ones

ProxySQL solves all of these. It sits between Magento and MySQL as a transparent proxy:

Magento → ProxySQL (6033) → Primary
                         → Replica 1
                         → Replica 2

Install ProxySQL

apt install proxysql
systemctl enable proxysql
systemctl start proxysql

Configure Query Rules

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
  (0, 'db-primary', 3306),    -- writes
  (1, 'db-replica-1', 3306),  -- reads
  (1, 'db-replica-2', 3306);  -- reads

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES
  ('magento', '***', 0);

INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
  (1, 1, '^SELECT .*', 1, 1),
  (2, 1, '^INSERT.*', 0, 1),
  (3, 1, '^UPDATE.*', 0, 1),
  (4, 1, '^DELETE.*', 0, 1);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

Then update env.php to point at ProxySQL:

'default' => [
    'host' => '127.0.0.1',
    'port' => '6033',
],

ProxySQL handles connection pooling (reusing connections between PHP-FPM workers) and query-level routing, which is far more granular than connection-level splitting.

What NOT to Route to Replicas

These operations must always hit the primary:

Operation	Reason
Checkout quote reads	Must see the latest quote state
Customer session reads	Must reflect recent login
Admin ACL checks	Must see permission changes immediately
Cache tag invalidation	Race conditions
Queries inside transactions	Transaction scope consistency

Magento's ResourceConnection automatically routes queries inside a transaction to the write connection. For explicit control in checkout or admin code:

$resource->getConnection('checkout')->query('SELECT ...');

Monitoring Replication Health

You need visibility into three things:

1. Replication Lag

SHOW SLAVE STATUS\G

Set alerts at 10 seconds (warning) and 30 seconds (critical).

2. Read/Write Ratio

SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW GLOBAL STATUS LIKE 'Com_delete';

On a typical Magento store, you'll see 80-90% reads, 10-20% writes. If your read ratio is below 70%, you might not benefit much from read replicas.

3. Connection Distribution (ProxySQL)

SELECT * FROM stats_mysql_connection_pool;

Performance Impact: What to Expect

Metric	Single DB	Read Replica	ProxySQL + 2 Replicas
Catalog page load (p50)	420ms	310ms	260ms
Checkout submit (p95)	1.8s	1.6s	1.4s
Max concurrent requests	200	450	800
Primary CPU load (peaks)	85%	45%	30%

The biggest win isn't latency — it's capacity. Offloading reads frees the primary to handle writes faster, and your store can handle 2-4x more traffic before hitting a bottleneck.

Common Pitfalls

❌ Using the Same Server for Read and Write

If both default and default_read point to the same host, you're adding overhead with zero benefit. Use separate servers.

❌ Skipping read_only on Replicas

Set read_only = 1 in MySQL config on all replicas. If Magento accidentally sends a write, read_only prevents data corruption.

❌ Not Warming the Replica Buffer Pool

A fresh replica needs to warm its buffer pool. Run full scans on your largest tables:

for table in catalog_product_entity catalog_category_product \
             sales_order sales_order_item quote quote_item; do
  mysql -h db-replica-1 magento -e "SELECT COUNT(*) FROM $table;"
done

Without this, the first 24 hours of queries will be slow.

❌ Ignoring Long-Running Queries

Replicas share the same schema as the primary. If a slow query causes issues on the primary, it will also cause issues on the replica. Profile both servers with pt-query-digest.

When to NOT Use Read/Write Splitting

Read/write splitting adds operational complexity. Skip it if:

Your store handles fewer than 50,000 monthly visitors
Your MySQL server runs below 60% CPU during peak
Average page loads are under 400ms on a single server
You don't have DevOps support for the infrastructure

For smaller stores, invest in caching (Redis, Varnish) and query optimization first — bigger wins, less operational overhead.

Recap

Magento 2's built-in read/write connection support makes horizontal database scaling accessible:

Set up MySQL replication with binlog_format = ROW
Add default_read connection in env.php
Handle replication lag with after-write reads and lag monitoring
For production scale, use ProxySQL for query-level split and connection pooling
Monitor Seconds_Behind_Master and connection distribution

The ceiling on Magento's database performance isn't the application — it's the architecture. Read/write replication splitting removes the single-server bottleneck and gives your store room to grow through peak traffic, flash sales, and catalog expansion without a full replatform.

Magento 2 Database Table Partitioning & Archiving Strategies

Magevanta — Wed, 29 Jul 2026 09:10:01 +0000

Every Magento 2 store eventually hits the same wall: the database keeps growing, queries keep slowing down, and nobody notices until checkout times spike or admin grids become unusable. The sales_order table has 5 million rows. The quote table has 12 million. The log_visitor table doesn't even bear thinking about.

This guide covers practical strategies for partitioning, archiving, and maintaining Magento's largest tables — so your store stays fast without losing historical data.

Which Tables Cause the Most Pain?

Not all tables grow equally. These are the usual suspects:

Transactional Tables

quote / quote_item — Every cart session creates rows. Guest carts never clean up by default. At 10k daily visitors with 70% abandonment, you're adding ~7,000 abandoned quotes per day.
sales_order / sales_order_item — Grows linearly with sales. A store doing 500 orders/day hits 1.8M rows/year in sales_order alone, plus 3-5x that in sales_order_item.
sales_invoice / sales_invoice_item / sales_creditmemo / sales_creditmemo_item — Same growth pattern, sometimes with additional rows per order.

Log Tables

log_visitor / log_visitor_info / log_url / log_url_info — Magento's built-in visitor logging. Creates multiple rows per page view. On a busy store, these tables can grow by 100k+ rows per day.
report_event / report_viewed_product_index — Reporting tables that accumulate indefinitely.

Search Tables

search_query — Every unique search term gets logged. Popular stores accumulate hundreds of thousands of rows.
catalogsearch_fulltext — The full-text search index can become massive with large catalogs.

Strategy 1: Clean Up What You Don't Need

Before partitioning anything, remove data you'll never use.

Purge Old Visitor Logs

Magento's visitor logs are rarely useful after 30 days. Add a cron job or manual cleanup:

-- Remove visitor data older than 30 days
DELETE FROM log_visitor WHERE visit_last_activity_at < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 10000;
DELETE FROM log_visitor_info WHERE visitor_id NOT IN (SELECT visitor_id FROM log_visitor);
DELETE FROM log_url WHERE visitor_id NOT IN (SELECT visitor_id FROM log_visitor);
DELETE FROM log_url_info WHERE url_id NOT IN (SELECT url_id FROM log_url);

Important: Always use LIMIT with deletes on large tables. A single DELETE on 10M rows locks the table for minutes and fills the InnoDB buffer pool with undo data. Run the delete in a loop or use a batched approach.

Clean Up Orphaned Quotes

Guest quotes accumulate fast. Magento has a built-in cleanup, but it's often not aggressive enough:

-- Remove guest quotes older than 7 days (they're never coming back)
DELETE q, qi FROM quote q
INNER JOIN quote_item qi ON qi.quote_id = q.entity_id
WHERE q.customer_id = 0
AND q.updated_at < DATE_SUB(NOW(), INTERVAL 7 DAY)
LIMIT 5000;

Magento's sales/cleanup/quotes cron handles this, but check its configuration in Stores > Configuration > Sales > Checkout > Shopping Cart. Set "Quote Lifetime (days)" to something reasonable like 7-14 days.

Truncate Search Query Table

If you're not using search analytics, truncate it:

TRUNCATE TABLE search_query;

If you need some history, keep the last 90 days:

DELETE FROM search_query WHERE updated_at < DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 10000;

Strategy 2: Archive Old Orders

Archiving moves completed orders from the main tables to archive tables, keeping the active tables small and fast.

Magento's Built-In Archive

Magento provides order archiving through the admin: Sales > Orders > Archive. You can also trigger it via CLI:

bin/magento sales:order:archive --date=2025-12-31

This moves orders placed before the given date to archive tables (sales_order_archive, etc.). The archive tables have identical structure, so reports and lookups still work — they just query a different table.

Custom Archiving with Partition Swap

For more control, create your own archive tables and use partition swapping:

-- 1. Create archive table matching structure
CREATE TABLE sales_order_archive LIKE sales_order;

-- 2. Move old orders in batches
INSERT INTO sales_order_archive
SELECT * FROM sales_order
WHERE created_at < '2025-01-01'
AND entity_id BETWEEN 1 AND 100000;

-- 3. Delete from main table (same batch)
DELETE FROM sales_order
WHERE created_at < '2025-01-01'
AND entity_id BETWEEN 1 AND 100000;

Why batch by entity_id? A single DELETE with a date filter on a 10M row table creates a massive transaction. Batching by primary key range keeps each transaction small and avoids long-running locks.

Reindex After Archiving

After removing large amounts of data, rebuild affected indexers:

bin/magento indexer:reindex salesrule_rule catalogsearch_fulltext

Archived orders still need index entries if you're displaying historical data, but the main order tables will reindex much faster with fewer rows.

Strategy 3: Table Partitioning

MySQL partitioning splits a single logical table into multiple physical segments. Queries that include the partition key only scan relevant partitions, dramatically improving performance.

Range Partitioning by Date

The most effective partitioning strategy for Magento's order tables:

ALTER TABLE sales_order
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404),
    -- ... continue for each month
    PARTITION p202607 VALUES LESS THAN (202608),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Key rule: The partition key must be part of every unique index. Since sales_order has a primary key on entity_id, you need to drop and recreate it:

ALTER TABLE sales_order DROP PRIMARY KEY, ADD PRIMARY KEY (entity_id, created_at);

This is why partitioning is easier to set up when you create the table from scratch — retrofitting on production requires downtime.

Hash Partitioning for Quote Tables

Quotes don't have a natural date range that queries use. Hash partitioning by customer_id or entity_id distributes data evenly:

ALTER TABLE quote
PARTITION BY HASH (entity_id)
PARTITIONS 32;

This improves concurrent access patterns — different customer sessions hit different partitions.

Partition Maintenance

Add new partitions monthly and drop old ones:

-- Add next month
ALTER TABLE sales_order REORGANIZE PARTITION p_future INTO (
    PARTITION p202608 VALUES LESS THAN (202609),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Drop archived month
ALTER TABLE sales_order DROP PARTITION p202401;

Automate this with a monthly cron job. Dropping a partition is instant — it doesn't generate undo logs or lock the table.

What Magento Queries Benefit?

Not all queries automatically use partitions. The query must include the partition key in its WHERE clause. Magento's order grid queries typically filter by date, so they benefit directly:

-- This query hits only 1 partition instead of scanning the whole table
SELECT * FROM sales_order
WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01';

But queries that filter only by entity_id or increment_id will still scan all partitions. For those, add a covering index that includes created_at.

Strategy 4: External Archiving for Reporting

If you need years of order history for reporting but don't want it polluting the production database, export it:

Export to a Reporting Database

# Dump old orders to a separate database
mysqldump --where="created_at < '2025-01-01'" magento sales_order sales_order_item | \
mysql magento_archive

Then truncate or partition the production tables. Your analytics team queries the archive database; your production database stays lean.

Elasticsearch for Historical Search

If customers need to search their order history, index historical orders in Elasticsearch. Magento's search infrastructure can query ES much faster than scanning a massive sales_order table for a customer's old orders.

Monitoring Table Growth

Set up alerts before tables become a problem:

-- Check table sizes
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'magento'
AND table_name IN ('quote', 'quote_item', 'sales_order', 'sales_order_item',
    'log_visitor', 'log_url', 'search_query')
ORDER BY data_length DESC;

Set a cron to run this daily and alert you when any table exceeds a threshold (e.g., 1GB data or 5M rows).

Recommended Cleanup Schedule

Table	Retention	Method
`log_visitor*`	30 days	Batch delete
`log_url*`	30 days	Batch delete
`quote` (guest)	7 days	Magento config + batch delete
`quote` (customer)	30 days	Magento config
`search_query`	90 days	Batch delete
`report_event`	90 days	Batch delete
`sales_order`	2+ years	Archive + partition drop

The Bottom Line

A Magento database that isn't maintained will eventually bring your store to its knees. The combination of regular cleanup, partitioning for large tables, and archiving for historical data keeps query times low, indexers fast, and admin grids responsive.

Don't wait until checkout takes 10 seconds. Set up monitoring, implement a cleanup schedule, and partition your largest tables before they become an emergency.

The upfront investment is a few hours of setup and a monthly cron job. The payoff is a store that stays fast regardless of how many orders you've processed or how many abandoned carts are sitting in your database.

Magento 2 CDN Setup: Optimize Static Content Delivery

Magevanta — Tue, 28 Jul 2026 09:07:08 +0000

Content Delivery Networks (CDNs) are one of the most impactful yet underutilized performance tools in the Magento 2 ecosystem. While most store owners obsess over backend optimizations — database tuning, indexer speed, PHP-FPM settings — the frontend delivery of static assets often remains a blind spot. This is a mistake. A well-configured CDN can reduce Time to First Byte (TTFB), cut bandwidth costs, and dramatically improve the user experience for global audiences.

In this guide, we'll walk through the complete CDN setup for Magento 2, covering static content, media files, and user-generated content. We'll also discuss advanced configuration for cache invalidation and HTTPS/TLS optimization.

Why CDN Matters for Magento 2

Magento 2 stores are asset-heavy. A typical product page loads dozens of JavaScript files, CSS bundles, product images, category thumbnails, and static theme assets. Without a CDN, every request hits your origin server, creating unnecessary load and increasing latency — especially for users geographically distant from your hosting location.

Key benefits of using a CDN with Magento 2:

Reduced latency: Edge servers deliver content from locations closer to the user
Origin offload: Static assets never hit your Magento server, freeing PHP-FPM workers
Bandwidth savings: Origin egress drops significantly
Improved Core Web Vitals: Faster LCP and reduced TTFB directly impact SEO
DDoS resilience: CDN edge absorbs traffic spikes

What Should Go Through the CDN?

A typical Magento 2 CDN setup handles three asset categories:

Static Content (/static/) — JS, CSS, theme assets, font files
Media (/media/) — Product images, category images, WYSIWYG uploads
User Content (/pub/media/) — Customer uploads, order PDFs, invoices

Do NOT CDN these paths:

/rest/ — API endpoints, dynamic
/checkout/ — Cart operations, dynamic
/customer/ — Account pages, session-based
/admin/ — Security risk

Step 1: Base URL Configuration

Magento 2 supports separate base URLs for static and media content. Configure these in Stores > Configuration > Web > Base URLs:

Base URL: https://magevanta.com/
Base URL for Static View Files: https://cdn.magevanta.com/static/
Base URL for User Media Files: https://cdn.magevanta.com/media/

For multi-store setups, configure per-store base URLs under the store scope. Always use HTTPS for CDN URLs — modern browsers flag mixed-content warnings, and HTTP/2 requires TLS.

After changing base URLs, run a full cache flush:

bin/magento cache:flush

Step 2: Static Content Deploy with Versioning

Magento's static content deployment (setup:static-content:deploy) generates versioned directories under pub/static/. The version hash changes with each deployment, making CDN cache invalidation automatic — the URL changes, so the CDN fetches fresh content.

Verify versioning is enabled:

bin/magento config:show dev/static/sign

Should return 1. If not, enable it:

bin/magento config:set dev/static/sign 1

With signing enabled, your static URLs look like:

https://cdn.magevanta.com/static/version1234567890/frontend/Magento/luma/en_US/css/styles.css

When you deploy new static content, the version hash updates and the CDN automatically serves the new files.

Step 3: Cloudflare Configuration

Cloudflare is the most popular CDN choice for Magento stores due to its generous free tier and edge caching capabilities.

Page Rules

Create these page rules in order (Cloudflare evaluates top-down):

*cdn.magevanta.com/static/* → Cache Level: Cache Everything, Edge TTL: 1 month
*cdn.magevanta.com/media/* → Cache Level: Cache Everything, Edge TTL: 7 days
*magevanta.com/checkout/* → Cache Level: Bypass
*magevanta.com/customer/* → Cache Level: Bypass

Origin Configuration

SSL/TLS mode: Full (strict) — requires valid certificate on origin
Always Use HTTPS: On
Automatic HTTPS Rewrites: On
Brotli compression: On — better than gzip, roughly 15-20% smaller
HTTP/2: Enabled by default
HTTP/3 (QUIC): Enable for reduced latency on mobile

Cache Headers

Ensure your origin sends proper cache headers for static assets. Add to your Nginx configuration:

location ~* \.(js|css|png|jpg|jpeg|gif|ico|svg|woff|woff2|ttf|eot)$ {
    expires 1y;
    add_header Cache-Control "public, immutable";
    add_header X-Content-Type-Options nosniff;
    access_log off;
}

location /media/ {
    expires 7d;
    add_header Cache-Control "public, must-revalidate";
}

The immutable directive tells browsers the file will never change — the version hash handles updates — allowing aggressive browser caching without stale content risk.

Step 4: Image Optimization

Most CDNs offer automatic image optimization. For Cloudflare:

Polish: Enable Lossy for product images, Lossless for logos and icons
WebP: Automatic conversion for supporting browsers
Mirage: Mobile image optimization that resizes for viewport

For Magento stores with thousands of product images, this alone can reduce image payload by 30-50%.

Nginx-Level WebP Fallback

If your CDN doesn't handle WebP conversion, serve it directly via Nginx with the ngx_http_image_filter_module or pre-generate WebP variants during setup:static-content:deploy using a custom deploy strategy.

Step 5: Cache Invalidation Strategy

Static content is easy — version hashes handle it. Media content requires explicit invalidation when products are updated.

Option A: Cloudflare API

After product import or mass update, purge specific paths:

PURGE_URLS='["https://cdn.magevanta.com/media/catalog/product/cache/*"]'

curl -X POST "https://api.cloudflare.com/client/v4/zones/$ZONE_ID/purge_cache" \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  --data "{\"files\":$PURGE_URLS}"

Option B: Magento Cache Tags

Advanced setups can integrate CDN invalidation with Magento's cache tags. When Magento purges FPC for a product, trigger a CDN purge for that product's media paths. This requires custom module development using the clean_cache_by_tags event.

Option C: Stale-While-Revalidate

For high-traffic stores, consider stale-while-revalidate headers. The CDN serves the cached version immediately while fetching a fresh copy in the background. This eliminates cache-miss latency spikes during invalidation:

Cache-Control: public, max-age=86400, stale-while-revalidate=3600

Step 6: Multi-Region CDN with Origin Shield

If your store serves multiple regions, enable Cloudflare's Origin Shield (or equivalent in other CDNs). Without it, each regional edge node independently fetches from origin on cache miss. With Origin Shield, a single intermediate cache layer absorbs misses:

Without shield: 200 edge nodes × 1% miss rate = 2 origin requests per asset
With shield: 200 edge nodes → 1 shield node → 1 origin request per asset

For stores running on limited origin infrastructure, this can prevent cache stampedes during product launches or sale events.

Step 7: Monitoring and Verification

After CDN deployment, verify configuration:

Response headers: Check CF-Cache-Status header:
- HIT — served from CDN edge
- MISS — fetched from origin (first request)
- DYNAMIC — not cached, check page rules
Waterfall analysis: Use Chrome DevTools or WebPageTest to confirm static assets load from CDN domain and show cache hits.
Origin bandwidth: Monitor your hosting panel — successful CDN offload drops static content bandwidth to near zero.
Cache hit ratio: Aim for >95% hit ratio on static assets. Below that, review your page rules and cache headers.

Common Pitfalls

Mixed content warnings: If your base URL is HTTP but the page loads over HTTPS, browsers block assets. Always use HTTPS CDN URLs.

CORS issues for fonts: WOFF/2 font files served from a different domain need CORS headers:

location ~* \.(woff|woff2)$ {
    add_header Access-Control-Allow-Origin "*";
}

Cookie forwarding: Ensure the CDN strips or doesn't forward cookies for static asset requests. Cookies prevent caching and expose session data.

Admin panel exposure: Never CDN admin URLs. Restrict admin access to origin IP whitelist.

Stale assets after deploy: If you're not using versioned URLs, users may see old CSS/JS after a deploy. Always enable dev/static/sign.

Performance Impact

A properly configured CDN typically delivers:

30-50% reduction in page load time for global users
60-80% reduction in origin bandwidth usage
LCP improvement of 0.5-1.5 seconds on image-heavy pages
TTFB reduction from 200-500ms to 20-50ms for cached assets

For a store doing $1M+ revenue, this directly translates to higher conversion rates — Amazon famously estimated every 100ms delay costs 1% in revenue.

Conclusion

CDN configuration is low-hanging fruit for Magento 2 performance. The setup is straightforward — base URL changes, Cloudflare page rules, and proper cache headers — yet the impact is outsized. For stores with international customers or heavy static asset loads, a CDN moves from "nice to have" to essential infrastructure.

Start with static and media content, monitor your cache hit ratio, then expand to advanced features like image optimization and automatic purge integration. Your origin server — and your users — will thank you.

Magento 2 Database Index Strategy & Query Optimization

Magevanta — Mon, 27 Jul 2026 09:08:14 +0000

Magento 2's database is its backbone. Every product load, category browse, cart update, and checkout step hits MySQL. When your catalog grows past 50K SKUs and traffic climbs, poorly optimized queries become the silent killer — causing slow page loads, checkout timeouts, and frustrated customers.

Most Magento developers know about Redis caching and Varnish, but few dig into the database layer where real performance gains hide. This guide covers practical MySQL index strategies and query optimization techniques tailored specifically for Magento 2's architecture.

Understanding Magento 2's Database Schema

Magento 2 uses a hybrid EAV (Entity-Attribute-Value) and flat table model. Products, customers, and categories store their core data in EAV tables, while indexed data lives in flat tables for faster retrieval.

The EAV Table Structure

A single product's name doesn't live in one row. Instead, it's spread across multiple tables:

catalog_product_entity              → entity_id, sku, type_id
catalog_product_entity_varchar      → entity_id, attribute_id, store_id, value
catalog_product_entity_int          → entity_id, attribute_id, store_id, value
catalog_product_entity_decimal      → entity_id, attribute_id, store_id, value
catalog_product_entity_text         → entity_id, attribute_id, store_id, value
catalog_product_entity_datetime     → entity_id, attribute_id, store_id, value

Loading a single product with 20 attributes means joining 5-6 EAV tables. Each join multiplies the query cost. At 100K products, this becomes brutal without proper indexing.

Flat Index Tables

Magento's indexers create flat tables like catalog_product_flat_1 that denormalize EAV data into a single row per product. These tables are what actually power category pages and search results.

-- Flat table structure (simplified)
SELECT entity_id, sku, name, price, status, visibility, url_key
FROM catalog_product_flat_1
WHERE status = 1 AND visibility IN (2, 4)
ORDER BY position ASC
LIMIT 20;

This query is fast because it hits a single flat table with standard column indexes — no EAV joins needed.

Critical Indexes Every Magento Store Needs

1. Sales Order Indexes

The sales_order and sales_order_grid tables are queried heavily in the admin panel and for order processing:

-- Check existing indexes
SHOW INDEX FROM sales_order_grid;

-- Add composite index for admin order grid filtering
ALTER TABLE sales_order_grid
ADD INDEX idx_status_created (status, created_at);

-- Add index for customer order history
ALTER TABLE sales_order
ADD INDEX idx_customer_id_created (customer_id, created_at DESC);

Without these, loading the admin order grid with 500K+ orders takes 10-30 seconds. With them, it drops to under 1 second.

2. Catalog Product Indexes

The flat product table needs indexes matching your common query patterns:

-- For category pages (most common query)
ALTER TABLE catalog_product_flat_1
ADD INDEX idx_status_visibility_position (status, visibility, position);

-- For product search/filter by attribute set
ALTER TABLE catalog_product_flat_1
ADD INDEX idx_attribute_set_id (attribute_set_id);

-- For price-based sorting and filtering
ALTER TABLE catalog_product_flat_1
ADD INDEX idx_price (price);

3. Cart and Quote Indexes

The quote and quote_item tables slow down as abandoned carts accumulate:

-- Speed up quote lookups by store
ALTER TABLE quote
ADD INDEX idx_store_id_is_active (store_id, is_active);

-- Speed up guest cart retrieval
ALTER TABLE quote
ADD INDEX idx_customer_id_is_active (customer_id, is_active);

-- Quote item lookups
ALTER TABLE quote_item
ADD INDEX idx_quote_id_product_id (quote_id, product_id);

4. URL Rewrite Indexes

URL rewrites are hit on every page load. With thousands of products, the url_rewrite table grows fast:

-- The critical lookup index
ALTER TABLE url_rewrite
ADD INDEX idx_request_path_store_id (request_path, store_id);

-- For redirect lookups
ALTER TABLE url_rewrite
ADD INDEX idx_target_path_store_id (target_path, store_id);

Using EXPLAIN to Identify Slow Queries

Before adding indexes blindly, always analyze with EXPLAIN. Here's how to read the output for Magento-specific queries:

EXPLAIN SELECT e.entity_id, e.sku, e.type_id,
       v1.value AS name, d1.value AS price
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v1
    ON e.entity_id = v1.entity_id
    AND v1.attribute_id = (SELECT attribute_id FROM eav_attribute
                           WHERE attribute_code = 'name'
                           AND entity_type_id = 4)
LEFT JOIN catalog_product_entity_decimal d1
    ON e.entity_id = d1.entity_id
    AND d1.attribute_id = (SELECT attribute_id FROM eav_attribute
                           WHERE attribute_code = 'price'
                           AND entity_type_id = 4)
WHERE e.entity_id = 12345;

What to Look For in EXPLAIN Output

type: ALL — Full table scan. This is the worst case. Add an index.

-- Bad: full table scan on 500K rows
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | e     | ALL  | NULL          | NULL | NULL    | NULL | 487231 |       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

type: ref — Index lookup using a reference. Good.

type: const — Single row lookup by primary key. Best possible.

Extra: Using filesort — MySQL is sorting results manually. Often means the ORDER BY doesn't match an index.

Extra: Using temporary — Creating a temporary table. Often unavoidable with complex joins but try to minimize.

Magento-Specific Query Patterns to Optimize

Category Product Listing

The most queried page on most stores. Here's the typical pattern:

-- Default Magento category query (simplified)
SELECT entity_id, sku, name, price, thumbnail, url_key
FROM catalog_product_flat_1
WHERE (status = 1)
  AND (visibility IN (2, 4))
  AND (entity_id IN (
      SELECT product_id FROM catalog_category_product
      WHERE category_id = 42
  ))
ORDER BY position ASC
LIMIT 20 OFFSET 0;

The subquery in IN (...) is the performance trap. For large categories, optimize with a JOIN:

-- Optimized with JOIN instead of IN subquery
SELECT f.entity_id, f.sku, f.name, f.price, f.thumbnail, f.url_key
FROM catalog_product_flat_1 f
INNER JOIN catalog_category_product ccp
    ON f.entity_id = ccp.product_id
WHERE f.status = 1
  AND f.visibility IN (2, 4)
  AND ccp.category_id = 42
ORDER BY ccp.position ASC
LIMIT 20 OFFSET 0;

Add the necessary index on the category-product relation table:

ALTER TABLE catalog_category_product
ADD INDEX idx_category_id_position (category_id, position);

Customer Group Price Queries

Tier prices and group prices are loaded per product. With many customer groups, this explodes:

-- Add index for tier price lookups
ALTER TABLE catalog_product_entity_tier_price
ADD INDEX idx_product_id_customer_group (product_id, customer_group_id, qty);

-- Add index for catalog rule price
ALTER TABLE catalogrule_product_price
ADD INDEX idx_product_id_rule_date (product_id, rule_date, customer_group_id);

Inventory / Stock Queries

MSI (Multi-Source Inventory) adds complexity. The inventory_source_item table needs proper indexing:

-- For stock status checks
ALTER TABLE inventory_source_item
ADD INDEX idx_sku_source (sku, source_code);

-- For stock aggregation
ALTER TABLE inventory_stock_1
ADD INDEX idx_product_id_stock (product_id, is_salable);

Monitoring Slow Queries in Production

Enable the MySQL Slow Query Log

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000

After enabling, watch for Magento patterns:

# Top 10 slowest queries from the log
pt-query-digest /var/log/mysql/slow.log --limit 10

# Or manually
grep "Query_time" /var/log/mysql/slow.log | sort -t: -k2 -rn | head -20

Using Magento's Built-in Profiler

Enable database profiling in app/etc/env.php:

'db' => [
    'connection' => [
        'default' => [
            'host' => 'localhost',
            'dbname' => 'magento2',
            'username' => 'root',
            'password' => '',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8mb4;',
            'profiler' => [
                'class' => '\Magento\Framework\DB\Profiler',
                'enabled' => true,
            ],
        ],
    ],
],

Then log the profiler output in a custom module or check the var/debug/db.log file for query times.

Connection Pooling and Persistent Connections

Magento opens a new MySQL connection per request by default. Under high traffic, connection establishment becomes a bottleneck:

// app/etc/env.php — enable persistent connections
'db' => [
    'connection' => [
        'default' => [
            'driver_options' => [
                PDO::ATTR_PERSISTENT => true,
            ],
        ],
    ],
],

For even better results, use ProxySQL as a connection pool between PHP and MySQL:

# Install ProxySQL
apt-get install proxysql

# /etc/proxysql.cnf
mysql_users = (
    {
        username = "magento"
        password = "secret"
        default_hostgroup = 0
        max_connections = 200
    }
)

ProxySQL maintains a pool of persistent connections and routes queries efficiently — especially useful when running multiple PHP-FPM workers.

InnoDB Buffer Pool Optimization

The InnoDB buffer pool is MySQL's most important memory setting. It caches data and indexes in RAM:

# my.cnf — set to 70-80% of available RAM for dedicated DB server
[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8  # One per GB, up to 64
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2  # Slight risk, big speed gain
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000  # For SSD
innodb_io_capacity_max = 4000

Check if your buffer pool is large enough:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';

-- Key metrics:
-- Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads = hit ratio
-- Should be > 99%

If the hit ratio drops below 99%, increase innodb_buffer_pool_size.

Table Maintenance and Fragmentation

Magento tables fragment over time as products are added, updated, and deleted. Regular maintenance keeps them lean:

-- Check fragmentation
SELECT table_name,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(data_free/1024/1024, 2) AS fragmented_mb,
       ROUND((data_free / data_length) * 100, 2) AS frag_pct
FROM information_schema.tables
WHERE table_schema = 'magento2'
  AND data_free > 0
ORDER BY data_free DESC
LIMIT 20;

-- Defragment a table
ALTER TABLE catalog_product_entity ENGINE=InnoDB;
-- or
OPTIMIZE TABLE catalog_product_entity;

Run this monthly during off-peak hours. For zero-downtime optimization, use pt-online-schema-change:

pt-online-schema-change --alter "ENGINE=InnoDB" \
    D=magento2,t=catalog_product_entity \
    --execute

Real-World Results

After implementing these index and query optimizations on a store with 120K SKUs and 2M orders:

Metric	Before	After
Category page load	3.2s	0.8s
Admin order grid	12s	0.9s
Checkout (place order)	8s	1.4s
Search results	2.1s	0.6s
Avg MySQL queries/page	340	85

The biggest wins came from the flat table indexes, replacing IN subqueries with JOINs, and the InnoDB buffer pool increase.

Conclusion

Database optimization is rarely glamorous, but it's where the most impactful performance gains hide. Start with EXPLAIN on your slowest pages, add targeted indexes, tune your InnoDB settings, and maintain your tables regularly. Your Redis and Varnish caches work better when the database underneath them is fast — because cache misses are inevitable, and when they happen, you want the database to respond in milliseconds, not seconds.

Monitor your slow query log weekly, revisit your indexes after major catalog changes, and always test schema changes in staging before production. A well-tuned MySQL layer is the foundation that every other Magento optimization sits on top of.

Magento 2 Search Autocomplete Performance: Keeping Sub-200ms at Scale

Magevanta — Fri, 24 Jul 2026 09:08:09 +0000

Search autocomplete is the single most resource-intensive interactive feature on a Magento 2 storefront. Every keystroke in the search bar triggers an AJAX request to the backend, which queries the search engine, loads product data, applies permissions, and returns HTML. On a busy store with hundreds of concurrent visitors, autocomplete generates more backend requests than any other page — and almost none of it is cached.

I've seen Magento 2 stores where autocomplete took 800ms to 1.5 seconds per request. On a category page with 200 concurrent users, that's enough to saturate your PHP-FPM pool and bring the entire site to its knees. The frustrating part: most of this overhead is completely unnecessary.

In this post, I'll walk through exactly how Magento 2 autocomplete works under the hood, where the bottlenecks hide, and how to bring response times under 200ms — even on large catalogs.

How Magento 2 Autocomplete Works

When a customer types into the search field, Magento's JavaScript sends an AJAX request to /search/ajax/suggest. Here's the request flow:

JavaScript intercepts keystroke — quickSearch.js debounces input (default 300ms delay)
AJAX GET to /search/ajax/suggest — includes the search term and store scope
Search controller executes — Magento\Search\Controller\Ajax\Suggest::execute()
Search engine query fires — Elasticsearch/OpenSearch gets a multi_match query with fuzzy matching
Product collection loads — Full product objects are loaded for each search result (5-10 products)
HTML renders server-side — Each product goes through layout XML, block rendering, and template output
Response returns — The HTML fragment is sent back as JSON

The critical issue: steps 4-6 run on every request with zero caching. A typical autocomplete request loads 5-10 full product objects with EAV attributes, generates layout blocks, and renders templates — identical work to what happens on a category page, except it's uncached and fires every 300ms.

Where the Bottlenecks Hide

1. Product Collection Loading (60-70% of response time)

The autocomplete response loads products using a standard collection with EAV. This means:

5-10 SELECT queries for product entities
50-100+ SELECT queries for EAV attribute values (name, price, image, description, etc.)
Additional queries for tier prices, stock status, category associations

On a catalog with 50,000+ SKUs, these queries hit buffer pool pressure. Even with indexes, the EAV SELECT queries require joining catalog_product_entity_varchar, catalog_product_entity_decimal, catalog_product_entity_int, and more — one per attribute per product.

Measurement:

-- Enable the general query log temporarily
SET GLOBAL general_log = 'ON';

-- Trigger an autocomplete request, then count queries
SELECT COUNT(*) FROM mysql.general_log 
WHERE command_type = 'Query' 
AND event_time > DATE_SUB(NOW(), INTERVAL 5 SECOND);

A single autocomplete request typically generates 80-150 SQL queries.

2. Layout XML and Block Rendering

Magento renders the autocomplete HTML using a full layout XML pipeline. This means:

Loading layout XML handles
Merging catalogsearch_ajax_suggest.xml with default handles
Instantiating block classes
Running toHtml() on each block

The layout merge alone takes 10-30ms. Block rendering adds another 20-50ms depending on template complexity.

3. Search Engine Query Design

The default Elasticsearch query for autocomplete uses multi_match with best_fields type and fuzzy matching. On large catalogs, this is unnecessarily expensive:

{
  "query": {
    "multi_match": {
      "query": "search term",
      "fields": ["name^3", "sku^2", "description"],
      "type": "best_fields",
      "fuzziness": "AUTO"
    }
  },
  "size": 10
}

The fuzziness: AUTO parameter is the biggest performance killer. It generates edit-distance calculations for every term against every document. On a 100,000 SKU catalog, that's a lot of computation per keystroke.

Fix 1: Reduce EAV Attribute Loading

The default autocomplete loads all searchable attributes. You can drastically reduce this by limiting which attributes are loaded:

<!-- app/code/Vendor/Module/view/frontend/layout/catalogsearch_ajax_suggest.xml -->
<?xml version="1.0"?>
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
    <body>
        <referenceBlock name="search_result_list">
            <arguments>
                <argument name="search_result_list" xsi:type="array">
                    <item name="attributes_to_select" xsi:type="array">
                        <item name="name" xsi:type="string">name</item>
                        <item name="sku" xsi:type="string">sku</item>
                        <item name="price" xsi:type="string">price</item>
                        <item name="thumbnail" xsi:type="string">thumbnail</item>
                        <item name="url_key" xsi:type="string">url_key</item>
                    </item>
                </argument>
            </arguments>
        </referenceBlock>
    </body>
</page>

Or override the product collection directly in a plugin:

// app/code/Vendor/Module/Plugin/SearchProductProviderPlugin.php
namespace Vendor\Module\Plugin;

use Magento\CatalogSearch\Model\Search\ProductProvider;

class SearchProductProviderPlugin
{
    /**
     * Strip heavy attributes from autocomplete product loading
     */
    public function afterGetList(ProductProvider $subject, $result)
    {
        foreach ($result as $product) {
            $product->setData('description', null);
            $product->setData('short_description', null);
            $product->setData('meta_title', null);
            $product->setData('meta_description', null);
            $product->setData('meta_keyword', null);
        }
        return $result;
    }
}

Impact: Reduces SQL queries from 80-150 down to 30-50 per autocomplete request. Response time drops by 40-60%.

Fix 2: Tune the Elasticsearch Query

The default fuzzy query is overkill for autocomplete. Replace it with a more efficient match_phrase_prefix or use a dedicated search-as-you-type field:

Option A: Use match_phrase_prefix Instead of Fuzzy

Override the search request to use prefix matching, which leverages the inverted index directly:

<!-- app/etc/elasticsearch/custom_search_request.xml -->
<requests>
    <request name="quick_search_container" label="Quick Search">
        <queries>
            <query name="quick_search_container" xsi:type="boolQuery">
                <queryReference clause="should" ref="search_query"/>
                <queryReference clause="should" ref="sku_query"/>
            </query>
            <query name="search_query" xsi:type="matchQuery">
                <match field="name" condition="match_phrase_prefix"/>
            </query>
            <query name="sku_query" xsi:type="matchQuery">
                <match field="sku" condition="match"/>
            </query>
        </queries>
    </request>
</requests>

Option B: Use search_as_you_type Field Mapping

Create a custom field mapping that uses Elasticsearch's search_as_you_type field type:

PUT /magento2_product/_mapping
{
  "properties": {
    "name_suggest": {
      "type": "search_as_you_type",
      "max_shingle_size": 3
    }
  }
}

Then query it with bool_prefix:

{
  "query": {
    "multi_match": {
      "query": "search term",
      "fields": [
        "name_suggest",
        "name_suggest._2gram",
        "name_suggest._3gram",
        "sku"
      ],
      "type": "bool_prefix"
    }
  },
  "size": 5
}

Impact: search_as_you_type with bool_prefix is 5-10x faster than multi_match with fuzziness: AUTO because it uses pre-computed n-gram indexes instead of runtime edit-distance calculations.

Fix 3: Cache Autocomplete Results

The simplest high-impact optimization: cache autocomplete responses. Most users search for the same popular terms.

// app/code/Vendor/Module/Plugin/AjaxSuggestCachePlugin.php
namespace Vendor\Module\Plugin;

use Magento\Search\Controller\Ajax\Suggest;
use Magento\Framework\App\Response\Http;
use Magento\Framework\App\CacheInterface;

class AjaxSuggestCachePlugin
{
    private const CACHE_TAG = 'search_autocomplete';
    private const CACHE_LIFETIME = 3600; // 1 hour

    public function __construct(
        private CacheInterface $cache
    ) {}

    public function aroundExecute(Suggest $subject, callable $proceed): Http
    {
        $query = $subject->getRequest()->getParam('q');
        $storeId = $subject->getRequest()->getParam('store_id', 0);

        if (empty($query) || strlen($query) < 2) {
            return $proceed();
        }

        $cacheKey = sprintf(
            'autocomplete_%d_%s',
            $storeId,
            md5(strtolower(trim($query)))
        );

        $cached = $this->cache->load($cacheKey);
        if ($cached !== false) {
            $response = $subject->getResponse();
            $response->setHeader('Content-Type', 'application/json');
            $response->setBody($cached);
            $response->setHeader('Cache-Control', 'public, max-age=60');
            $response->setHeader('X-Cache', 'HIT');
            return $response;
        }

        $response = $proceed();

        $this->cache->save(
            $response->getBody(),
            $cacheKey,
            [self::CACHE_TAG],
            self::CACHE_LIFETIME
        );

        $response->setHeader('X-Cache', 'MISS');
        return $response;
    }
}

<!-- app/code/Vendor/Module/etc/frontend/di.xml -->
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Search\Controller\Ajax\Suggest">
        <plugin name="autocomplete_cache" type="Vendor\Module\Plugin\AjaxSuggestCachePlugin" sortOrder="10"/>
    </type>
</config>

Add cache invalidation when products are updated:

// app/code/Vendor/Module/Observer/InvalidateAutocompleteCache.php
namespace Vendor\Module\Observer;

use Magento\Framework\Event\ObserverInterface;
use Magento\Framework\Event\Observer;
use Magento\Framework\App\CacheInterface;

class InvalidateAutocompleteCache implements ObserverInterface
{
    public function __construct(
        private CacheInterface $cache
    ) {}

    public function execute(Observer $observer)
    {
        $this->cache->clean([\Vendor\Module\Plugin\AjaxSuggestCachePlugin::CACHE_TAG]);
    }
}

Impact: For popular search terms (which cover 80%+ of queries), this eliminates the backend entirely. Response time goes from 200-500ms to under 10ms on cache hit.

Fix 4: Debounce and Client-Side Optimization

The default Magento debounce is 300ms. On fast typists, this still generates a lot of requests. Increase it and add minimum character requirements:

// app/code/Vendor/Module/view/frontend/requirejs-config.js
var config = {
    config: {
        mixins: {
            'Magento_CatalogSearch/js/form-mini': {
                'Vendor_Module/js/search-mixin': true
            }
        }
    }
};

// app/code/Vendor/Module/view/frontend/web/js/search-mixin.js
define([], function () {
    'use strict';

    return function (target) {
        return target.extend({
            defaults: {
                searchDelay: 500,       // Increased from 300ms
                minSearchLength: 3      // Require at least 3 characters
            }
        });
    };
});

Additional client-side optimizations:

Abort previous requests — When a new keystroke fires, abort the in-flight AJAX request. Magento's default implementation does this, but verify it's working.
Cache results in localStorage — For repeat visitors, store recent autocomplete results client-side with a 5-minute TTL. This eliminates server roundtrips entirely for repeated searches.
Show results from cache instantly — Display cached results immediately, then update in the background if the API returns new data.

Fix 5: Limit Result Count and Simplify Templates

The default autocomplete shows 5-10 products. Reducing this to 3-5 cuts rendering time proportionally:

<!-- app/etc/view.xml -->
<vars module="Magento_CatalogSearch">
    <var name="max_query_length">10</var>
</vars>

Strip heavy elements from the autocomplete template. Skip rating summary, tier prices, and swatches — they all trigger additional queries. A simplified template with just image, name, and price eliminates 3-7 SQL queries per product. With 5 products shown, that's 15-35 fewer queries per request.

Measuring the Impact

Use a load testing tool to compare before and after:

# Benchmark with 100 concurrent connections, 1000 requests
ab -n 1000 -c 100 -k -H "Accept: application/json" \
   "https://your-store.com/search/ajax/suggest?q=shirt"

Expected results:

Metric	Before	After
Avg response time	350-800ms	50-150ms
P95 response time	1.2-2.5s	200-400ms
SQL queries per request	80-150	15-30
Cache hit rate	0%	60-80%

Production Checklist

Before deploying these changes:

[ ] Test autocomplete accuracy — Ensure results are still relevant after disabling fuzzy matching. If relevance drops too much, use match_phrase_prefix instead.
[ ] Monitor Elasticsearch cluster health — Changing query patterns can shift load. Watch CPU, heap, and query latency.
[ ] Set up cache warming — Pre-warm autocomplete cache with top 100-200 search terms from your analytics.
[ ] Add cache invalidation — Product updates, stock changes, and price changes should trigger selective cache clearing.
[ ] Test with slow connections — Autocomplete UX degrades badly on 3G. Consider showing a loading indicator.
[ ] Audit third-party modules — Some search extensions (ElasticSuite, Amasty, Mirasvit) override the default autocomplete. Check their configurations first.

Conclusion

Search autocomplete is one of those features that "just works" in development but falls apart at scale. The combination of uncached EAV loading, expensive fuzzy search queries, and full layout rendering makes every keystroke a mini page load.

The highest-impact fixes, in order:

Cache autocomplete responses (60-80% of requests become instant)
Reduce product attribute loading (40-60% reduction in query time)
Tune Elasticsearch queries (5-10x faster search execution)
Simplify templates (eliminate unnecessary product data queries)
Increase debounce and add client-side caching (reduce request volume by 40-50%)

Combined, these optimizations bring autocomplete from 500-1500ms down to 50-150ms. Your customers get instant suggestions, your server handles 3-5x more concurrent searchers, and your Core Web Vitals scores stop bleeding.

The search bar is your store's front door. Make sure it opens fast.

Magento 2 Dependency Injection Performance: Proxy Classes, Compiled DI, and Object Manager Pitfalls

Magevanta — Thu, 23 Jul 2026 09:07:35 +0000

Magento 2's dependency injection (DI) system is one of its most powerful architectural features, but it can silently become a performance bottleneck if misused. Understanding how the DI container works internally — from compilation to runtime resolution — is essential for building high-performance Magento applications. In this post, we'll explore the hidden performance costs of DI, how proxy classes save initialization overhead, and why the object manager is a trap you should avoid.

How Magento 2 DI Works Under the Hood

At the core of Magento 2's DI system is the ObjectManager, a service container that instantiates classes and resolves their dependencies. However, unlike a naive runtime container, Magento compiles DI information ahead of time to improve performance.

When you run bin/magento setup:di:compile, Magento reads all constructor signatures in the codebase and generates generated/metadata/compiled.php — a massive array mapping every class to its dependencies. It also creates factory and interceptor classes in generated/code/. This compilation step is what makes Magento 2's DI performant in production, but it comes with tradeoffs.

Without compilation, Magento would need to use PHP reflection at runtime to inspect constructors, determine parameter types, and build dependency trees. Reflection is computationally expensive and would add significant latency to every request. The compiled metadata eliminates this overhead entirely by pre-computing the dependency graph.

The Cost of Constructor Dependency Bloat

A common anti-pattern in Magento 2 is injecting far too many dependencies into a single class. Every dependency in a constructor requires the DI container to instantiate (or fetch from cache) that object and all of its transitive dependencies. A class with 15 constructor parameters can trigger a cascade of dozens of object instantiations before its own constructor even runs.

This is particularly problematic in frontend contexts where response time directly impacts user experience. A plugin or observer that injects a heavy service like the SearchCriteriaBuilder or ProductRepository might seem harmless in isolation, but when multiplied across dozens of plugins firing on every request, the cumulative overhead becomes significant.

The rule of thumb: inject only what you need. If a dependency is only used in one method, consider using a factory or fetching it lazily rather than injecting it in the constructor. This keeps the instantiation chain lightweight and prevents unnecessary object creation on every request.

Proxy Classes: The Lazy Initialization Pattern

Magento 2 provides a powerful but underutilized feature: proxy classes. A proxy is a lightweight wrapper around a real service that delays its actual instantiation until a method is called on it. You can create a proxy by simply adding Proxy to the end of a class name in your constructor injection.

For example, if your class needs a heavy dependency like Magento\Catalog\Model\ProductRepository, you can inject Magento\Catalog\Model\ProductRepository\Proxy instead. The DI container will inject a proxy object that does nothing on construction. Only when you actually call a method on the proxy does it instantiate the real ProductRepository and forward the call.

This pattern is invaluable for classes that have conditional logic — where a heavy dependency is only needed in some code paths. Without a proxy, the DI container instantiates the dependency regardless of whether it's used. With a proxy, you pay the initialization cost only when the dependency is actually accessed.

When to use proxies:

For heavy services with expensive constructors (repositories, complex models, external API clients)
In plugins or observers where the injected service is only used in specific conditions
For rarely-used features where eager instantiation is wasteful

When NOT to use proxies:

For lightweight services where the proxy overhead exceeds the savings
When the dependency is always used — a proxy adds an extra method call layer for no benefit

Compiled DI vs. Runtime DI

Magento 2 supports two DI modes: compiled and runtime. In production, you should always use compiled DI via setup:di:compile. Runtime DI exists primarily for development convenience, but it carries a severe performance penalty.

In runtime mode, Magento falls back to reflection for every class instantiation. This means parsing PHP docblocks, inspecting type hints, and building dependency trees on the fly. On a warm production cache, a single class instantiation via compiled DI might take microseconds; the same class via runtime DI could take milliseconds. Multiply that across hundreds of objects per request and the difference is dramatic.

The compiled DI system also enables optimizations like shared instance reuse. When multiple classes depend on the same singleton service, the compiled container caches the first instantiation and returns the same instance for subsequent requests. Runtime DI may not guarantee this caching behavior, leading to duplicate object creation and increased memory pressure.

Performance tip: always run setup:di:compile before deploying to production, and ensure your deployment pipeline never skips this step. A missing compilation can easily double or triple your response times without any obvious error in logs.

The Object Manager Anti-Pattern

Perhaps the most common DI-related performance mistake in Magento 2 is direct object manager usage. You'll often see code like this:

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$productRepository = $objectManager->get(\Magento\Catalog\Model\ProductRepository::class);

This is an anti-pattern for several reasons. First, it bypasses the compiled DI metadata entirely, forcing Magento to resolve dependencies at runtime through reflection. Second, it makes the code untestable and couples it to the global object manager instance. Third, it prevents Magento from applying important optimizations like proxy resolution and shared instance caching.

The object manager also lacks awareness of lifecycle management. When you use constructor injection, Magento can apply proxies, interceptors, and preference mappings configured in di.xml. When you use the object manager directly, you get a raw instance without any of these layers — which often means missing performance optimizations that the framework would otherwise apply automatically.

The fix is simple: always use constructor injection. If you need a dependency that varies at runtime, use a factory class injected via the constructor. Magento's factory pattern (Vendor\Module\Model\SomethingFactory) is specifically designed for this use case and integrates cleanly with the compiled DI system.

Impact on Production Workloads

On high-traffic Magento 2 stores, DI performance issues compound quickly. A single slow constructor chain might add 50ms to a request — negligible in isolation, but when multiplied across thousands of concurrent requests, it translates to higher CPU load, increased memory consumption, and slower overall throughput.

We've seen stores where removing just three unnecessary dependencies from a frequently-executed plugin reduced average response time by 12%. Replacing eager repository instantiation with proxies in a checkout observer cut page load time by nearly 200ms for users with large carts. These aren't edge cases — they're the kind of wins that come from understanding how DI works at a fundamental level.

Best Practices Summary

Inject only what you need — avoid constructor bloat; use factories for conditional dependencies
Use proxy classes for heavy, conditionally-used services to enable lazy initialization
Always compile DI in production via setup:di:compile; never rely on runtime DI
Never use the object manager directly — prefer constructor injection or factories
Audit constructor signatures during code reviews; treat excess dependencies as a red flag
Profile with Blackfire or New Relic to identify slow instantiation chains in your custom code

Conclusion

Magento 2's dependency injection system is sophisticated and powerful, but its performance characteristics aren't always obvious. The difference between well-optimized DI usage and careless injection patterns can translate to hundreds of milliseconds per request — a meaningful impact on conversion rates and user experience.

By leveraging compiled DI, proxy classes, and proper constructor discipline, you can ensure your Magento 2 application remains fast and scalable without sacrificing the flexibility that makes the framework powerful. Treat your constructors as a performance contract: every dependency you inject is a promise that the container will instantiate it, and that cost is paid on every request where your class is used.

Magento 2 Crawler & Bot Traffic Management — Stop Bad Bots From Destroying Your Performance

Magevanta — Wed, 22 Jul 2026 09:07:08 +0000

Bot traffic is the invisible tax on Magento 2 performance. While human shoppers browse a few pages per session, bots crawl thousands of URLs, bypass caches, and strain your infrastructure — often without you knowing. A single misbehaving crawler can double your server load and spike your CDN bill.

This post covers everything you need to detect bot traffic, optimize for legitimate crawlers, and block the harmful ones. We'll look at practical Nginx, Varnish, and application-level solutions you can deploy today.

Why Bots Matter for Magento 2 Performance

Magento is a complex platform. Every uncached page requires:

PHP-FPM workers to render templates
MySQL queries for EAV product data
Redis lookups for session and cache data
Elasticsearch calls for layered navigation

A human visitor might hit 5–10 pages per session. A bot can crawl 50,000 pages in an hour. The difference is staggering.

Consider a store with 50,000 SKUs. A naive crawler following every product link, filter combination, and pagination page generates exponentially more requests than your actual customer base.

Typical bot impact:

Cache hit ratio drops from 85% to 40% because bots request unique URLs
MySQL connection pool exhaustion from concurrent catalog_product_entity lookups
Elasticsearch cluster CPU spikes from facet aggregation queries
CDN egress charges from uncached, CPU-rendered pages served to bots

If your analytics show "direct traffic" with a 100% bounce rate and 0s time-on-page, you might be looking at bot traffic disguised as humans.

Identifying Bot Traffic in Your Logs

Before you fix anything, you need to know what you're dealing with. Start with your access logs.

Nginx Access Log Analysis

# Top user agents by request count
awk -F '"' '{print $6}' /var/log/nginx/access.log | sort | uniq -c | sort -rn | head -20

# Requests per IP address
awk '{print $1}' /var/log/nginx/access.log | sort | uniq -c | sort -rn | head -20

# Bot-only traffic volume
awk '/Googlebot|bingbot|AhrefsBot|SemrushBot|MJ12bot/ {sum++} END {print sum}' /var/log/nginx/access.log

Magento Application-Level Detection

Log suspicious patterns from within Magento by extending the Http context or using an observer:

<?php
namespace Vendor\BotLogger\Observer;

use Magento\Framework\Event\Observer;
use Magento\Framework\Event\ObserverInterface;
use Magento\Framework\HTTP\PhpEnvironment\Request;
use Psr\Log\LoggerInterface;

class LogBotTraffic implements ObserverInterface
{
    public function __construct(
        private Request $request,
        private LoggerInterface $logger
    ) {}

    public function execute(Observer $observer): void
    {
        $userAgent = $this->request->getHeader('User-Agent') ?? '';
        $ip = $this->request->getClientIp();

        // Detect known bots
        $bots = ['Googlebot', 'bingbot', 'AhrefsBot', 'SemrushBot', 'MJ12bot', 'DotBot'];
        foreach ($bots as $bot) {
            if (stripos($userAgent, $bot) !== false) {
                $this->logger->info('BOT_TRAFFIC', [
                    'bot' => $bot,
                    'ip' => $ip,
                    'url' => $this->request->getRequestUri(),
                    'time' => microtime(true)
                ]);
                break;
            }
        }
    }
}

Track this in a separate log file and ingest it into your monitoring stack (Grafana, New Relic, or CloudWatch).

robots.txt — Your First Line of Defense

A well-crafted robots.txt is the simplest way to guide legitimate crawlers away from expensive pages.

Place robots.txt at pub/robots.txt (or root if your webroot is pub/):

User-agent: *
Disallow: /admin/
Disallow: /checkout/
Disallow: /customer/
Disallow: /catalogsearch/
Disallow: /sales/guest/
Disallow: /wishlist/
Disallow: /compare/
Disallow: /review/product/post/
Disallow: /newsletter/
Disallow: /sendfriend/
Disallow: /catalog/product_compare/
Disallow: /*?*price=
Disallow: /*?*color=
Disallow: /*?*size=
Disallow: /*?*material=
Disallow: /*?*limit=
Disallow: /*?*order=
Disallow: /*?*dir=
Disallow: /*?*mode=

# Allow CSS/JS/images for rendering
Allow: /pub/media/catalog/product/
Allow: /pub/static/

# Sitemap for efficient crawling
Sitemap: https://yourstore.com/sitemap.xml

# Crawl delay for all bots
Crawl-delay: 2

Key rules:

Disallow layered navigation parameters — price, color, size, material filters generate thousands of unique URLs with identical content. These are SEO nightmares and performance drains.
Disallow compare and wishlist — these are user-specific and should never be crawled.
Set a crawl delay — 2 seconds between requests from a single bot is reasonable.
Provide a sitemap — direct crawlers to your canonical URLs instead of letting them discover every filter combination.

Magento-Specific robots.txt Pitfalls

If your webroot is pub/ (recommended for security), robots.txt lives at pub/robots.txt. If your webroot is the Magento root, it goes there instead.

Don't rely solely on robots.txt for security — it's a polite request, not an enforcement mechanism. Malicious bots will ignore it entirely.

Nginx Rate Limiting for Bots

Rate limiting at the web server level is your second line of defense. It protects PHP-FPM and MySQL from being overwhelmed.

Zone-Based Rate Limiting

# In http context (nginx.conf)
limit_req_zone $binary_remote_addr zone=general:10m rate=10r/s;
limit_req_zone $bot_zone zone=bots:10m rate=1r/s;

# Map user agents to bot classification
map $http_user_agent $bot_zone {
    default                    "";
    ~*(Googlebot|bingbot)      $binary_remote_addr;
    ~*(AhrefsBot|SemrushBot)   $binary_remote_addr;
    ~*(MJ12bot|DotBot|BLEXBot) $binary_remote_addr;
    ~*(Screaming|Majestic)     $binary_remote_addr;
    ~*crawler                  $binary_remote_addr;
    ~*spider                  $binary_remote_addr;
}

# In server context (site config)
server {
    # General limit for all traffic
    limit_req zone=general burst=20 nodelay;

    # Stricter limit for identified bots
    location / {
        if ($bot_zone != "") {
            limit_req zone=bots burst=5 nodelay;
        }
        try_files $uri $uri/ /index.php$is_args$args;
    }

    # Always allow static assets (they're cached and cheap)
    location ~* \.(jpg|jpeg|png|gif|ico|css|js|svg|woff|woff2)$ {
        limit_req off;
        expires 1y;
        add_header Cache-Control "public, immutable";
    }
}

What this achieves:

General traffic: 10 requests/second per IP with a burst of 20
Bots: 1 request/second per IP with a burst of 5 — enough for legitimate crawlers, painful for aggressive ones
Static assets: No rate limiting — images and CSS should be served freely from cache

Dynamic Bot Blocking with Nginx + Lua

For advanced setups, use OpenResty/Nginx with Lua to dynamically block bots based on behavior:

-- nginx.conf lua_shared_dict blocklist 10m;
init_worker_by_lua_block {
    local blocklist = ngx.shared.blocklist
    -- Preload known bad actors
    blocklist:set("185.220.101.0/24", true, 86400)
}

access_by_lua_block {
    local blocklist = ngx.shared.blocklist
    local ip = ngx.var.remote_addr
    if blocklist:get(ip) then
        ngx.exit(ngx.HTTP_FORBIDDEN)
    end

    -- Block by excessive requests (detected via log analysis)
    local req_count = blocklist:incr(ip .. ":count", 1, 0, 60)
    if req_count > 100 then
        blocklist:set(ip, true, 3600) -- Block for 1 hour
        ngx.log(ngx.WARN, "Auto-blocked IP for excessive requests: ", ip)
        ngx.exit(ngx.HTTP_TOO_MANY_REQUESTS)
    end
}

Varnish Caching Strategy for Bots

Varnish can differentiate between humans and bots, applying different caching policies.

Bot-Aware VCL

sub vcl_recv {
    # Normalize user agent for bot detection
    if (req.http.User-Agent ~ "(Googlebot|bingbot|Slurp|DuckDuckBot|Baiduspider|YandexBot)") {
        set req.http.X-Bot = "true";
    } else {
        set req.http.X-Bot = "false";
    }

    # Don't cache admin, checkout, customer area for anyone
    if (req.url ~ "^/(admin|checkout|customer)/") {
        return (pass);
    }

    # Cache product pages for bots with longer TTL (they don't need fresh prices instantly)
    if (req.http.X-Bot == "true" && req.url ~ "^/catalog/product/view/") {
        unset req.http.Cookie;
        return (hash);
    }

    # For humans, respect cookies
    if (req.http.Cookie) {
        # Strip all but essential cookies
        set req.http.Cookie = regsuball(req.http.Cookie, "(^|;)\s*__[a-z]+=[^;]*", "");
        set req.http.Cookie = regsub(req.http.Cookie, "^;\s*", "");
        if (req.http.Cookie == "") {
            unset req.http.Cookie;
        }
    }
}

sub vcl_backend_response {
    # Cache product pages for bots longer
    if (bereq.http.X-Bot == "true" && bereq.url ~ "^/catalog/product/view/") {
        set beresp.ttl = 1h;
        set beresp.grace = 6h;
    }

    # Cache category pages for everyone
    if (bereq.url ~ "^/catalog/category/view/") {
        set beresp.ttl = 15m;
        set beresp.grace = 1h;
    }
}

sub vcl_deliver {
    # Add debug header (remove in production)
    set resp.http.X-Cache-Status = req.http.X-Bot;
}

Bot-specific caching benefits:

Product pages: 1-hour TTL for bots — they're not price-sensitive in real-time
Category pages: 15-minute TTL for everyone — bots get the same cached content as humans
Grace period: Old cached content served while fetching fresh data prevents backend spikes

Blocking Malicious Bots

Not all bots are well-behaved. Some ignore robots.txt, spoof user agents, and aggressively crawl your site.

Nginx Bad Bot Blocker

Use the Nginx Bad Bot Blocker or implement a lightweight version:

# Block known bad user agents
if ($http_user_agent ~* (ahrefsbot|semrushbot|mj12bot|dotbot|blexbot|ezooms|yandexbot|baiduspider|sogou|exabot|facebot|facebookexternalhit)) {
    # Allow search engines, block SEO tools and scrapers
    if ($http_user_agent !~* (googlebot|bingbot|slurp|duckduckbot)) {
        return 444; # Drop connection without response
    }
}

# Block by IP reputation (maintain a deny list)
include /etc/nginx/bad_ips.conf;

Fail2Ban Integration

# /etc/fail2ban/filter.d/magento-bad-bot.conf
[Definition]
failregex = ^<HOST> .* "(GET|POST) .*" .* "(AhrefsBot|SemrushBot|MJ12bot|DotBot|BLEXBot)".*$
            ^<HOST> .* "(GET|POST) .*" .* HTTP/[0-9.]+" 4[0-9]{2} .* "(Python|curl|wget|Scrapy)".*$
ignoreregex =

# /etc/fail2ban/jail.local
[magento-bad-bot]
enabled = true
filter = magento-bad-bot
logpath = /var/log/nginx/access.log
maxretry = 10
findtime = 60
bantime = 3600
action = iptables-multiport[name=BadBots, port="http,https", protocol=tcp]

Magento 2 Firewall Module

For application-level blocking, use a module like magento2-firewall or implement your own:

<?php
namespace Vendor\BotBlocker\Plugin;

use Magento\Framework\App\FrontControllerInterface;
use Magento\Framework\App\RequestInterface;
use Magento\Framework\HTTP\PhpEnvironment\Response;

class BotBlocker
{
    private array $blockedAgents = [
        'AhrefsBot', 'SemrushBot', 'MJ12bot', 'DotBot',
        'BLEXBot', 'Ezooms', 'curl', 'wget', 'python-requests'
    ];

    public function beforeDispatch(
        FrontControllerInterface $subject,
        RequestInterface $request
    ): void {
        $userAgent = $request->getHeader('User-Agent') ?? '';

        foreach ($this->blockedAgents as $bot) {
            if (stripos($userAgent, $bot) !== false) {
                $response = new Response();
                $response->setHttpResponseCode(403);
                $response->setContent('Forbidden');
                $response->sendResponse();
                exit;
            }
        }
    }
}

Googlebot Crawl Budget Optimization

Googlebot has a limited crawl budget for your site. Make sure it spends that budget on pages that matter.

1. Consolidate Pagination with `rel="canonical"`

<!-- On page 2 of category listing -->
<link rel="canonical" href="https://yourstore.com/electronics.html" />
<link rel="prev" href="https://yourstore.com/electronics.html" />
<link rel="next" href="https://yourstore.com/electronics.html?p=3" />

2. Noindex Low-Value Filter Pages

<!-- On layered navigation filter page with no products -->
<meta name="robots" content="noindex, follow" />

3. Proper Sitemap Management

<?php
// In your sitemap generation cron or module
namespace Vendor\Sitemap\Model;

use Magento\Sitemap\Model\Sitemap;

class OptimizedSitemap extends Sitemap
{
    protected function _initSitemapItems()
    {
        parent::_initSitemapItems();

        // Exclude out-of-stock products from sitemap
        foreach ($this->_sitemapItems as $key => $item) {
            if ($item->getUrl() && $this->isOutOfStockProduct($item->getUrl())) {
                unset($this->_sitemapItems[$key]);
            }
        }

        // Limit to 50,000 URLs per sitemap (Google's limit)
        $this->_sitemapItems = array_slice($this->_sitemapItems, 0, 50000);
    }
}

4. Monitor Google Search Console

Check the "Crawl Stats" report weekly. Look for:

Crawl rate drops — Google might be hitting errors or timeouts
Crawl budget waste — pages that return 404 or are noindex being crawled repeatedly
Server error spikes — usually means bots are hitting broken URLs or overloading your infrastructure

Measuring Bot Impact

Track these metrics before and after implementing bot controls:

Metric	Before	After
Cache hit ratio	40–60%	75–85%
Average response time	800ms	300ms
PHP-FPM queue depth	50+	<10
MySQL queries/sec	5000+	<2000
CDN egress (GB/day)	500	150
Bot requests/hour	50,000	<5,000 (legitimate only)

Quick Nginx Log Dashboard

#!/bin/bash
# bot-impact.sh — run this hourly via cron

LOG=/var/log/nginx/access.log
HOUR=$(date -d '1 hour ago' +%H)

# Total requests in the last hour
TOTAL=$(awk -v h="$HOUR" '$4 ~ ":"h {count++} END {print count+0}' "$LOG")

# Bot requests
BOTS=$(awk -v h="$HOUR" '$4 ~ ":"h && /(Googlebot|bingbot|AhrefsBot|SemrushBot|MJ12bot)/ {count++} END {print count+0}' "$LOG")

# Cache misses (adjust based on your setup)
MISSES=$(awk -v h="$HOUR" '$4 ~ ":"h && /MISS/ {count++} END {print count+0}' "$LOG")

PCT=0
if [ "$TOTAL" -gt 0 ]; then
    PCT=$((BOTS * 100 / TOTAL))
fi

echo "$(date): Total=$TOTAL, Bots=$BOTS (${PCT}%), Cache misses=$MISSES"

Summary Checklist

[ ] Implement a strict robots.txt with layered navigation exclusions
[ ] Set up Nginx rate limiting for both general traffic and bots
[ ] Configure Varnish with bot-aware caching policies
[ ] Deploy Fail2Ban to auto-block aggressive bots
[ ] Add application-level bot logging and blocking
[ ] Optimize sitemap.xml to guide crawlers efficiently
[ ] Monitor Google Search Console crawl stats weekly
[ ] Track cache hit ratio, response time, and server load metrics

Final Thoughts

Bot traffic is not going away. If anything, AI scrapers and SEO tools are becoming more aggressive. The good news is that most bot-related performance issues are solvable with the right combination of robots.txt guidance, rate limiting, and caching strategy.

The key is to be proactive. Don't wait for your server to crash during a crawler rampage. Implement these controls now, monitor your logs weekly, and adjust your defenses as bot behavior evolves. Your cache hit ratio, server costs, and sanity will thank you.

Magento 2 UI Component Performance: Optimizing Admin Grids & Data Providers

Magevanta — Mon, 20 Jul 2026 09:07:56 +0000

Magento 2 UI components are the backbone of every admin grid you've ever used. The product grid. The order grid. The customer grid. The invoice grid. Every page in the admin panel that shows a table of data with filtering, sorting, and pagination is powered by the UI component system.

And they are slow.

Not "a little slow" — painfully slow on stores with 50,000+ products, 100,000+ orders, or multi-website setups with complex ACL. A product grid that takes 8 seconds to load is not uncommon. A customer grid that times out on filter? Also common. The irony is that the admin panel is where your team spends hours every day, and every second of grid load time is a second of lost productivity.

This post covers why UI components are slow, how to profile them, and what you can do to make them fast again.

How Magento 2 UI Components Work

A UI component is a declarative XML configuration that Magento renders into a knockout.js-based frontend component. The typical flow is:

XML Declaration — view/adminhtml/ui_component/product_listing.xml defines columns, filters, bookmarks, and the data provider
Data Provider — A PHP class implementing Magento\\Framework\\View\\Element\\UiComponent\\DataProvider\\DataProviderInterface that fetches data from the database
AJAX Endpoint — The grid loads data via an AJAX call to mui/index/render/ which returns JSON
Knockout.js Rendering — The frontend renders the grid client-side using knockout templates

The performance problems live in steps 2 and 3. The data provider is where database queries run, and the AJAX endpoint is where Magento bootstraps the entire application just to serve a JSON response.

The Core Performance Problems

1. Data Provider Collection Loading

Every UI component grid uses a Magento\\Framework\\View\\Element\\UiComponent\\DataProvider\\CollectionFactory to build a collection. For the product grid, this is Magento\\Catalog\\Ui\\DataProvider\\Product\\ProductCollection. The collection loads products with EAV joins, inventory data, price indexing data, and store-specific attributes.

The problem: the collection loads all EAV attributes for every product in the grid, even though only a handful are displayed as columns. A product grid showing 20 products with 200 EAV attributes means 4,000 EAV joins — most of which are never rendered.

2. Full Page Bootstrap on Every AJAX Call

When the grid requests data, it hits mui/index/render/. This route bootstraps the entire Magento application: config loading, area code resolution, plugin interception, ACL checks, translation loading, layout generation. For a grid that paginates through 50 pages, that's 50 full Magento bootstraps — each one adding 200-500ms of overhead before the actual query runs.

3. Bookmark Management Overhead

Magento saves grid state (filters, sorting, pagination) as "bookmarks" in the ui_bookmark table. Every grid interaction — changing a filter, sorting a column, navigating pages — triggers a bookmark save operation. On multi-admin setups with many users, the bookmark table grows rapidly and queries against it slow down.

4. AddColumn After Data Loading

Many third-party modules add columns to grids via plugins on getColumns() or by modifying the UI component XML. If a column requires additional data (e.g., a custom attribute from a third-party table), the data is often loaded per-row after the main collection has already been loaded. This is the classic N+1 problem: 20 products in a grid page means 20 additional queries.

5. Mass Action Preload

When an admin user selects "Select All" in a grid, Magento needs to know all matching IDs — not just the current page. Some grids handle this by loading the full collection without LIMIT. On a store with 200,000 products, this means loading 200,000 product IDs into memory, serializing them, and sending them to the browser as a JavaScript variable.

Profiling UI Component Performance

Enable Database Query Logging

Add this to app/etc/env.php under the db section:

'db' => [
    'connection' => [
        'default' => [
            'debug' => true,
            'log_path' => '/var/log/magento_db.log'
        ]
    ]
]

Load the product grid and check the log. You'll see dozens of queries per grid load — many of them EAV joins. Look for:

Queries with JOIN ...catalog_product_entity_varchar repeated dozens of times
COUNT(*) queries that scan the full product table
SELECT * FROM ui_bookmark queries that are slower than expected

Use Magento Profiler

Enable the profiler in app/etc/env.php:

'system' => [
    'profiler' => [
        'enable' => 1
    ]
]

Append ?profiler=1 to any admin grid URL. Look for:

Magento\\Framework\\View\\Element\\UiComponent\\DataProvider\\DataProvider — time spent in data provider
Magento\\Ui\\Model\\Bookmark — bookmark management overhead
Magento\\Framework\\Api\\SearchCriteriaBuilder — search criteria processing

Check AJAX Response Time

Open Chrome DevTools → Network tab. Load the product grid. Look for the mui/index/render/ request. The total time is your grid load time. If it's over 2 seconds, you have a problem. If it's over 5 seconds, you have a serious problem.

Optimization Strategies

1. Limit EAV Attributes in the Collection

The most impactful optimization is limiting which EAV attributes the product grid collection loads. By default, Magento loads all attributes that are configured as "Used in Grid" (is_used_in_grid = 1 in catalog_eav_attribute). Many of these are unnecessary.

Check which attributes are flagged for grid use:

SELECT attribute_id, is_used_in_grid
FROM catalog_eav_attribute
WHERE is_used_in_grid = 1;

Disable grid loading for attributes that don't appear in the grid:

UPDATE catalog_eav_attribute
SET is_used_in_grid = 0
WHERE attribute_id IN (
    SELECT attribute_id FROM eav_attribute
    WHERE attribute_code IN ('description', 'short_description', 'meta_title', 'meta_description')
);

This alone can reduce grid load time by 30-50% on stores with many EAV attributes.

2. Optimize the Data Provider

Create a custom data provider that extends the default one and optimizes the collection:

<?php
namespace MageVanta\UiPerformance\Ui\DataProvider;

use Magento\Catalog\Ui\DataProvider\Product\ProductCollection;
use Magento\Catalog\Ui\DataProvider\Product\ProductDataProvider;

class OptimizedProductDataProvider extends ProductDataProvider
{
    public function getData()
    {
        // Only load attributes that are actually displayed as columns
        $this->getCollection()
            ->addAttributeToSelect(['name', 'sku', 'price', 'status', 'visibility']);

        // Add index hints for large catalogs
        $this->getCollection()->getSelect()
            ->reset(\Magento\Framework\DB\Select::ORDER)
            ->order('entity_id DESC');

        return parent::getData();
    }
}

Reference it in your product_listing.xml:

<dataSource name="product_listing_data_source">
    <dataProvider class="MageVanta\UiPerformance\Ui\DataProvider\OptimizedProductDataProvider" name="product_listing_data_source">
        <settings>
            <requestFieldName>id</requestFieldName>
            <primaryFieldName>entity_id</primaryFieldName>
        </settings>
    </dataProvider>
</dataSource>

3. Add Database Indexes for Grid Filters

The most common grid filters — status, visibility, type_id, attribute_set_id — need proper indexes. Check your slow query log for queries on catalog_product_entity_int with filter conditions:

-- Add composite index for common grid filter combinations
ALTER TABLE catalog_product_entity_int
ADD INDEX idx_grid_filter (attribute_id, store_id, value);

-- Index for the entity_id join used in every grid load
ALTER TABLE catalog_product_entity_int
ADD INDEX idx_entity_attribute (entity_id, attribute_id, store_id);

4. Implement Server-Side Pagination Correctly

Magento's UI component system supports server-side pagination, but some custom grids break this by loading the full collection. Ensure your data provider respects the pagination parameters:

public function getData()
{
    $collection = $this->getCollection();

    // Respect page size from search criteria
    $searchCriteria = $this->getSearchCriteria();
    $collection->setPageSize($searchCriteria->getPageSize());
    $collection->setCurPage($searchCriteria->getCurrentPage());

    $items = [];
    foreach ($collection->getItems() as $item) {
        $items[] = $item->toArray();
    }

    return [
        'totalRecords' => $collection->getSize(),
        'items' => $items
    ];
}

The key insight: $collection->getSize() runs a COUNT(*) query without loading all items, while $collection->getItems() only loads the current page. Never call getItems() before setting the page size.

5. Cache Bookmark Queries

The ui_bookmark table is queried on every grid render. Add a cache layer:

<?php
namespace MageVanta\UiPerformance\Model;

use Magento\Ui\Model\BookmarkRepository;
use Magento\Framework\App\CacheInterface;

class CachedBookmarkRepository extends BookmarkRepository
{
    const CACHE_KEY_PREFIX = 'ui_bookmark_';
    const CACHE_TTL = 3600;

    private $cache;

    public function __construct(
        BookmarkRepository $subject,
        CacheInterface $cache
    ) {
        $this->cache = $cache;
    }

    public function getListByUserId($userId)
    {
        $cacheKey = self::CACHE_KEY_PREFIX . $userId;
        $cached = $this->cache->load($cacheKey);
        if ($cached) {
            return json_decode($cached, true);
        }

        $result = parent::getListByUserId($userId);
        $this->cache->save(json_encode($result), $cacheKey, ['ui_bookmark'], self::CACHE_TTL);
        return $result;
    }
}

6. Replace the AJAX Endpoint with a Custom Route

The mui/index/render/ endpoint bootstraps the full Magento application on every request. For high-traffic admin panels, create a lightweight REST endpoint that skips layout generation and area code overhead:

<?php
namespace MageVanta\UiPerformance\Controller\Grid;

use Magento\Framework\App\Action\Action;
use Magento\Framework\App\Action\Context;
use Magento\Framework\App\ResponseInterface;
use Magento\Framework\Json\Helper\Data as JsonHelper;
use MageVanta\UiPerformance\Model\GridDataResolver;

class Data extends Action
{
    private $jsonHelper;
    private $gridDataResolver;

    public function execute()
    {
        $namespace = $this->getRequest()->getParam('namespace');
        $data = $this->gridDataResolver->resolve($namespace, $this->getRequest()->getParams());

        return $this->jsonHelper->jsonEncode($data);
    }
}

This approach skips the layout XML processing that mui/index/render/ performs, saving 100-200ms per request.

7. Optimize Mass Action Handling

Instead of loading all matching IDs into the browser, implement a server-side filter storage for mass actions:

public function execute()
{
    $filter = $this->getRequest()->getParam('filter');
    $filterHash = md5(json_encode($filter));

    // Store filter criteria in a temporary table
    $this->filterStorage->save($filterHash, $filter, 3600);

    // Return just the hash — not 200,000 IDs
    return $this->jsonResponse(['filter_hash' => $filterHash]);
}

When the mass action is executed, the backend reads the filter from storage and applies it to the collection — no need to send all IDs to the browser.

Monitoring UI Component Performance

Add a plugin to Magento\\Ui\\Controller\\Index\\Render to log grid load times:

public function aroundExecute($subject, $proceed)
{
    $start = microtime(true);
    $result = $proceed();
    $duration = round((microtime(true) - $start) * 1000);

    if ($duration > 2000) {
        $this->logger->warning(sprintf(
            'Slow UI component render: %s took %dms',
            $this->getRequest()->getParam('namespace'),
            $duration
        ));
    }

    return $result;
}

Set up an alert for any grid load exceeding 3 seconds. On a well-optimized store, the product grid should load in under 1.5 seconds, and the order grid in under 1 second.

The Bottom Line

Magento 2 UI components are powerful but inefficient by default. The combination of EAV overhead, full application bootstrap on every AJAX call, and unoptimized data providers creates a perfect storm of admin panel slowness.

The most impactful changes you can make:

Reduce EAV attribute loading — disable is_used_in_grid for unused attributes
Add proper database indexes — on catalog_product_entity_int for grid filter columns
Cache bookmark queries — the ui_bookmark table is hit on every grid render
Replace the default AJAX endpoint — skip layout generation for grid data requests
Implement server-side mass action filters — never send all IDs to the browser

On a store with 100,000+ products, these optimizations typically reduce product grid load time from 6-8 seconds to under 2 seconds. That's a 4x improvement in admin panel responsiveness — and your team will feel it immediately.

DEV Community: Magevanta

Magento 2 Page Builder Performance Optimization

Wat is Magento Page Builder?

De verborgen bottlenecks

1. Overhead door JSON-parsing en content tree traversing

2. Gebrek aan Full Page Cache voor dynamische Page Builder content

3. Onnodige layout updates en block rendering

4. Frontend asset bloat

Optimalisatiestrategieën

Cache de geparseerde content tree

Granular caching met ESI voor dynamische elementen

Optimaliseer asset loading

Database optimalisaties

Vermijd Page Builder voor data-intensieve elementen

Monitoring en debugging

Conclusie

Magento 2 API Rate Limiting: Protecting Your Store from Abuse

Why Rate Limiting Matters for Magento 2

Built-In Magento 2 API Throttling

REST API Rate Limits (WebAPI Framework)

WebAPI Throttling per Integration

Custom Rate Limiting with Magento 2 Plugins

Redis-Backed Sliding Window Rate Limiter

GraphQL Query Complexity Throttling

Nginx-Level Rate Limiting (First Line of Defense)

Varnish Rate Limiting for Cached API Responses

Per-Customer vs. Per-IP: Choosing the Right Granularity

Monitoring and Alerting on API Abuse

Log Format (customize in env.php)

What to Alert On

Production Checklist

Summary

Magento 2 Customer Data Sections & localStorage Performance Optimization

Magento 2 Customer Data Sections & localStorage Performance Optimization

What Are Customer Data Sections?

The Data Flow

Performance Trap #1: The "Sections Hell" AJAX Request

The Real Kicker: It Runs on Every Page

Performance Trap #2: localStorage Bloat

Performance Trap #3: Section Invalidation Abuse

Optimization Strategies

1. Audit Your sections.xml Files

2. Eliminate Unused Sections from Initial Load

3. Disable Sections You Don't Use

4. Switch to sessionStorage for Ephemeral Data

5. Implement Server-Side Push (Advanced)

6. Redis for Section Metadata

7. Measure Before and After

Summary

Magento 2 Admin Grid Performance Optimization: Speed Up Your Backend

Why Admin Grids Are So Slow

Optimize the Grid Collection

1. Add Custom Indexes for Grid Filters

2. Limit Default Grid Page Size

3. Disable Expensive Columns by Default

4. Override the Collection with Selective Loading

Fix EAV Grid Performance

Use Flat Catalog for Grids

Custom Flat Grid Tables

Optimize Mass Actions and Batch Operations

1. Increase Admin PHP Memory Limit

2. Batch Mass Actions with Chunking

Grid-Specific Caching Strategies

1. Collection Cache with Magento's Cache

2. Database Query Cache (MySQL 8.0)

Remove Unused Grid Features

Performance Impact: Before and After

When to Go Further

Recap

Magento 2 MySQL Read/Write Replication Splitting: Scale Your Database Horizontally

Why Replication Splitting Matters for Magento 2

How Magento 2's Read/Write Connection System Works

The Actual ResourceConnection Resolution

Step 1: Set Up MySQL Replication

On the Primary Server

On the Replica Server

Sync the Existing Data

Step 2: Configure Magento for Read/Write Splitting

Multiple Read Replicas

Verify It's Working

Log Format (customize in `env.php`)