Magento performance optimization: from caching to query tuning

For high-traffic Magento shops, performance is not a feature but a requirement. A 100ms delay costs 1% conversion. In this article, we cover production-grade optimizations: Varnish VCL configuration for Magento-specific edge cases, Redis architecture for session clustering, MySQL query profiling and indexing strategies. Based on experience with shops handling 10,000+ concurrent users.

Varnish VCL: beyond the basic configuration

Magento's default VCL misses crucial optimizations. Custom VCL for grace mode, saint mode and intelligent cache warming makes the difference between a stable and a failing Black Friday:

vcl 4.1;
 
import std;
import directors;
 
backend default {
    .host = "127.0.0.1";
    .port = "8080";
    .probe = {
        .url = "/health_check.php";
        .timeout = 2s;
        .interval = 5s;
        .window = 5;
        .threshold = 3;
    }
    .first_byte_timeout = 300s;
    .connect_timeout = 5s;
    .between_bytes_timeout = 2s;
}
 
sub vcl_recv {
    set req.grace = 6h;
 
    set req.url = std.querysort(req.url);
 
    if (req.url ~ "(\?|&)(utm_source|utm_medium|utm_campaign|gclid|fbclid)=") {
        set req.url = regsuball(req.url, "&(utm_source|utm_medium|utm_campaign|gclid|fbclid)=[^&]+", "");
        set req.url = regsuball(req.url, "\?(utm_source|utm_medium|utm_campaign|gclid|fbclid)=[^&]+&?", "?");
        set req.url = regsub(req.url, "\?$", "");
    }
 
    if (req.method == "POST" && req.url ~ "/graphql") {
        if (req.http.X-GraphQL-Operation ~ "mutation") {
            return (pass);
        }
    }
}
 
sub vcl_backend_response {
    if (beresp.http.X-Magento-Tags) {
        set beresp.do_esi = true;
    }
 
    if (beresp.status >= 500 && beresp.status < 600) {
        set beresp.saintmode = 30s;
        if (req.method != "POST") {
            return (retry);
        }
    }
 
    if (bereq.url ~ "\.(css|js|jpg|jpeg|png|gif|ico|woff2?)$") {
        set beresp.ttl = 365d;
        unset beresp.http.Set-Cookie;
    }
}

Grace mode is essential: during backend overload, Varnish serves cached content up to 6 hours old. This prevents cascade failures during traffic spikes. Saint mode temporarily marks backends as unhealthy on 5xx errors.

Redis: session clustering and cache segmentation

A single Redis instance is both a single point of failure and a bottleneck. Optimal setup: separate instances for sessions, cache and FPC with correct eviction policies:

'session' => [
    'save' => 'redis',
    'redis' => [
        'host' => 'redis-session.internal',
        'port' => '6379',
        'timeout' => '2.5',
        'persistent_identifier' => 'sess',
        'database' => '0',
        'compression_threshold' => '2048',
        'compression_library' => 'lz4',
        'max_concurrency' => '20',
        'break_after_frontend' => '5',
        'break_after_adminhtml' => '30',
        'first_lifetime' => '600',
        'bot_first_lifetime' => '60',
        'bot_lifetime' => '7200',
        'disable_locking' => '1',
        'min_lifetime' => '60',
        'max_lifetime' => '2592000',
        'sentinel_master' => 'mymaster',
        'sentinel_servers' => 'sentinel1:26379,sentinel2:26379,sentinel3:26379',
    ]
],
'cache' => [
    'frontend' => [
        'default' => [
            'backend' => 'Magento\Framework\Cache\Backend\Redis',
            'backend_options' => [
                'server' => 'redis-cache.internal',
                'port' => '6379',
                'database' => '0',
                'compress_data' => '1',
                'compression_lib' => 'lz4',
                'preload_keys' => [
                    'EAV_ENTITY_TYPES',
                    'GLOBAL_PLUGIN_LIST',
                    'DB_IS_UP_TO_DATE',
                    'SYSTEM_DEFAULT'
                ]
            ]
        ],
        'page_cache' => [
            'backend' => 'Magento\Framework\Cache\Backend\Redis',
            'backend_options' => [
                'server' => 'redis-fpc.internal',
                'port' => '6379',
                'database' => '0',
                'compress_data' => '0'
            ]
        ]
    ]
]

Critical insight: disable_locking on sessions prevents lock contention that causes timeouts at high concurrency. The preload_keys option warms the most frequently used cache entries immediately after flush.

MySQL query profiling and index optimization

Magento's EAV model generates complex queries. Without proper indexing, performance degrades exponentially with catalog size. Systematic profiling identifies bottlenecks:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';
 
SELECT
    ea.attribute_code,
    ea.backend_type,
    COUNT(*) as usage_count
FROM catalog_product_entity_varchar cpev
JOIN eav_attribute ea ON cpev.attribute_id = ea.attribute_id
WHERE ea.entity_type_id = 4
GROUP BY ea.attribute_id
HAVING usage_count > 1000
ORDER BY usage_count DESC;
 
ALTER TABLE catalog_product_index_price
    ADD INDEX idx_website_customer_price (website_id, customer_group_id, min_price);
 
ALTER TABLE catalog_product_entity
    ADD INDEX idx_listing_cover (type_id, attribute_set_id, entity_id, sku, created_at);

Real-world tip: the catalog_product_flat table doesn't scale past 100k+ products. Disable flat tables and optimize EAV queries with proper covering indexes.

Async operations and queue processing

Synchronous operations during checkout are conversion killers. Move everything not immediately needed to async queues:

namespace Ten50\Module\Model\Queue;
 
use Magento\Framework\MessageQueue\PublisherInterface;
use Magento\Sales\Api\Data\OrderInterface;
 
class OrderPostProcessor
{
    private const TOPIC_INVENTORY = 'inventory.reservations.update';
    private const TOPIC_INDEX = 'product.index.update';
 
    public function __construct(
        private PublisherInterface $publisher
    ) {}
 
    public function afterPlace(OrderInterface $order): void
    {
        $this->publisher->publish(
            self::TOPIC_INVENTORY,
            json_encode([
                'order_id' => $order->getEntityId(),
                'items' => $this->extractSkuQty($order)
            ])
        );
 
        $productIds = array_column($order->getAllItems(), 'product_id');
        $this->publisher->publish(
            self::TOPIC_INDEX,
            json_encode(['product_ids' => array_unique($productIds)])
        );
    }
}

Run consumers via supervisor with auto-restart. Benchmark: checkout time reduced from 3.2s to 0.8s by moving inventory updates and email sending to async.

Need a performance audit?

We analyze your Magento stack from Varnish to MySQL and implement targeted optimizations with measurable results.

Get in touch