Magento 2 performance – OPcache, Redis, N+1 queries, Blackfire, priority table

Magento 2 performance is a broad topic, but the high-impact improvements follow a clear priority order. OPcache configuration is free and immediate. Redis correctly configured reduces PHP cycles. N+1 queries are the single biggest source of slowness in custom code. Blackfire makes the invisible visible. I show a prioritised approach with concrete configurations and code examples.

Priority table – effort vs impact

OPcache – free performance in php.ini

[opcache]
opcache.enable=1
opcache.enable_cli=1
opcache.memory_consumption=512
opcache.interned_strings_buffer=64
opcache.max_accelerated_files=65407
opcache.validate_timestamps=0  ; 0 on production - no stat() calls per request
opcache.save_comments=1        ; required by Magento for annotations
opcache.consistency_checks=0

; Magento requires preloading for best performance
; opcache.preload=/var/www/html/preload.php
; opcache.preload_user=www-data

# Check OPcache status
php -r "print_r(opcache_get_status());" | grep -E "hit_rate|memory_usage"

# Hit rate should be > 99% on production
# Low memory: increase opcache.memory_consumption
# Low hit rate: increase opcache.max_accelerated_files

N+1 queries – how to find and fix

<?php

// Classic N+1 pattern - one query for the collection, N queries inside the loop
class BadOrderExport
{
    public function export(array $orderIds): array
    {
        $orders = $this->orderRepository->getList(
            $this->searchCriteriaBuilder->addFilter('entity_id', $orderIds, 'in')->create()
        )->getItems(); // 1 query

        $result = [];
        foreach ($orders as $order) {
            // getShippingAddress() triggers a separate DB query for EACH order - N queries!
            $address = $order->getShippingAddress();
            $result[] = [
                'id'      => $order->getId(),
                'total'   => $order->getGrandTotal(),
                'city'    => $address?->getCity(),
            ];
        }
        return $result; // Total: 1 + N queries
    }
}

// Fixed - load addresses in one batch query
class GoodOrderExport
{
    public function export(array $orderIds): array
    {
        $orders = $this->orderRepository->getList(
            $this->searchCriteriaBuilder->addFilter('entity_id', $orderIds, 'in')->create()
        )->getItems();

        // Batch load all addresses in one query
        $orderEntityIds = array_map(fn($o) => (int) $o->getEntityId(), $orders);
        $addresses = $this->loadAddressesForOrders($orderEntityIds); // 1 query

        $result = [];
        foreach ($orders as $order) {
            $address = $addresses[$order->getEntityId()] ?? null;
            $result[] = [
                'id'    => $order->getId(),
                'total' => $order->getGrandTotal(),
                'city'  => $address?->getCity(),
            ];
        }
        return $result; // Total: 2 queries regardless of collection size
    }

    private function loadAddressesForOrders(array $orderIds): array
    {
        $connection = $this->resourceConnection->getConnection();
        $select = $connection->select()
            ->from(['a' => $this->resourceConnection->getTableName('sales_order_address')])
            ->where('a.parent_id IN (?)', $orderIds)
            ->where('a.address_type = ?', 'shipping');

        $rows = $connection->fetchAll($select);
        $indexed = [];
        foreach ($rows as $row) {
            $indexed[$row['parent_id']] = $row;
        }
        return $indexed;
    }
}

Blackfire – profiling in practice

# Install Blackfire agent and PHP probe in DDEV
ddev get ddev/ddev-blackfire

# Profile a specific URL
blackfire curl https://magento2-dev.ddev.site/catalog/product/view/id/42

# Profile a CLI command
blackfire run php bin/magento catalog:reindex

# Profile in PHPStorm via Blackfire plugin
# Set breakpoints -> Run with Blackfire -> See call graph

# Most common findings:
# 1. N+1 queries - visible as many identical DB calls in the call graph
# 2. Missing cache - same data fetched multiple times
# 3. Heavy serialization - large objects being serialized/deserialized
# 4. Missing indexes - slow DB queries with many rows examined

Database query log for N+1 detection

<?php

// Quick N+1 detection during development - count queries before and after a block
class QueryCounter
{
    private int $queryCount = 0;

    public function __construct(
        private \Magento\Framework\App\ResourceConnection $resource
    ) {
        // Enable query logging
        $this->resource->getConnection()->setProfiler(
            new \Zend_Db_Profiler(true)
        );
    }

    public function getCount(): int
    {
        return $this->resource->getConnection()
            ->getProfiler()
            ->getTotalNumQueries();
    }

    public function getQueries(): array
    {
        $profiler = $this->resource->getConnection()->getProfiler();
        $queries = [];
        foreach ($profiler->getQueryProfiles() as $profile) {
            $queries[] = $profile->getQuery();
        }
        return $queries;
    }
}

// Usage in a test or development helper
$before = $counter->getCount();
$result = $service->process($data);
$after  = $counter->getCount();
echo "Queries executed: " . ($after - $before) . "\n";

Summary

Magento 2 performance optimisation is most effective when approached in priority order. OPcache and Redis are low-effort, high-impact changes that should always be in place on production. N+1 queries in custom modules are the most common source of unexplained slowness – Blackfire makes them immediately visible. Fix the database layer first; infrastructure changes only help if the application is not generating unnecessary load.