Wydajność bazy MySQL/MariaDB w sklepie B2B - tuning i indeksy

Top 10 wzorców slow query

Z analizy slow logów z ponad trzydziestu wdrożeń Magento wyłaniają się powtarzające się wzorce. Spisuję je, bo każdy nowy audyt zaczynam od ich szukania - są jak listy kontrolne dla mechanika samochodowego.

Pełen scan tabeli catalog_product_entity przy zapytaniach typu WHERE updated_at > '2026-01-01' to klasyk. Brak indeksu na updated_at przy 100 tys. produktów to 5-10 sekund per request. Indeks na updated_at rozwiązuje problem w pięć minut roboty.

Joiny EAV bez ograniczenia store_id. SELECT e.entity_id, v_name.value FROM catalog_product_entity e JOIN catalog_product_entity_varchar v_name ON ... WHERE v_name.attribute_id = X bez WHERE v_name.store_id = Y skanuje wszystkie store views, trzykrotnie wolniej niż powinien. Dorobienie warunku store_id daje natychmiastowy 3x wzrost wydajności.

Wyszukiwanie w nazwie produktu z LIKE '%laptop%'. Wildcard na początku oznacza pełen skan tabeli. Dla 50 tys. produktów 5-15 sekund. Lekarstwo to przeniesienie wyszukiwarki do Elasticsearch/OpenSearch - MySQL to nie jest silnik wyszukiwania pełnotekstowego, choćby udawał.

ORDER BY na nieindeksowanej kolumnie: WHERE customer_id = 123 ORDER BY created_at DESC LIMIT 10 bez kompozytowego indeksu (customer_id, created_at) robi filesort, dla klienta z tysiącem zamówień to sekundy. Kompozytowy indeks rozwiązuje sprawę.

COUNT(*) ze złożonym WHERE: SELECT COUNT(*) FROM sales_order WHERE status = 'pending' AND store_id = 1 bez indeksu (status, store_id) to skanowanie milionów rekordów dla licznika w panelu admina, który nikt nie ogląda częściej niż raz na minutę.

Subquery w SELECT typu SELECT e.*, (SELECT COUNT(*) FROM sales_order WHERE customer_id = e.entity_id) FROM customer_entity e to N+1 w przebraniu. Dla 10 tys. klientów lecę 10 tys. zapytań do bazy. JOIN z GROUP BY rozwiązuje to w jeden query.

JOIN bez warunku - cartesian product. Klasyczny błąd, najczęściej z custom modułów Magento dorabianych w pośpiechu. Wystarczy zapomnieć ON i tabela 100 tys. razy 100 tys. zabija bazę natychmiast.

UPDATE z dużą zmianą indeksu: UPDATE catalog_product_index_price SET price = price * 1.05 WHERE store_id = 1. Update milionów rekordów blokuje tabelę, generuje replication lag, klient czeka. Lepiej w batchach po 10 tys. z committami między.

Pełen scan na url_rewrite. Tabela rośnie do 500 tys. rekordów w średnim sklepie, bez indeksu (entity_type, store_id) każdy lookup boli.

Sesje w bazie. Jeśli sesje siedzą w core_session zamiast w Redisie, każdy request to read plus write do bazy. Top 1 powód lagu w starszych instalacjach Magento 2.x, których nikt nie skonfigurował od początku porządnie.

Diagnostyka tego wszystkiego idzie przez slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

mysqldumpslow -t 20 /var/log/mysql/slow.log
pt-query-digest /var/log/mysql/slow.log

pt-query-digest z Percona Toolkit daje znacznie lepszą agregację niż mysqldumpslow - dla audytu większego sklepu używam wyłącznie jego.

Indeksy w Magento EAV

EAV (Entity-Attribute-Value) to model bazodanowy Magento. Bardzo elastyczny - dodajesz atrybuty bez ALTER TABLE - ale wolny bez właściwych indeksów. Co gorsza, standardowe indeksy z core'a Magento często nie wystarczają dla custom queries z modułów.

Standardowa tabela catalog_product_entity_varchar ma PRIMARY KEY na value_id, plus indeksy na entity_id, attribute_id i store_id osobno. Wystarcza dla domyślnych operacji Magento, ale dla niestandardowych queries widzę często, że trzeba dorzucić kompozyt.

Pierwszy kandydat to indeks pokrywający typowy lookup pattern:

ALTER TABLE catalog_product_entity_varchar
ADD INDEX idx_attr_store_entity (attribute_id, store_id, entity_id);

Używany przez lookup wartości atrybutu dla konkretnego store plus listy encji. To indeks, który dorabiam w 80% audytów.

Drugi - kompozyt do wyszukiwania po wartości:

ALTER TABLE catalog_product_entity_varchar
ADD INDEX idx_attr_value (attribute_id, value(50));

Prefix 50 znaków, bo pełen indeks varchar w tabeli z milionami rekordów to za dużo dysku.

Dla zamówień dodaję dwa kompozyty:

ALTER TABLE sales_order ADD INDEX idx_customer_created (customer_id, created_at);
ALTER TABLE sales_order ADD INDEX idx_status_store_created (status, store_id, created_at);

Pierwszy do "ostatnie zamówienia klienta", drugi do raportów admina.

Dla URL Rewrites:

ALTER TABLE url_rewrite ADD INDEX idx_entity_type_store (entity_type, store_id);

Dla klientów:

ALTER TABLE customer_entity ADD INDEX idx_email_website (email, website_id);

Każdy nowy indeks weryfikuję EXPLAIN-em na konkretnym slow query:

EXPLAIN SELECT * FROM catalog_product_entity_varchar
WHERE attribute_id = 73 AND store_id = 1;

Wynik type=ref plus key=idx_attr_store_entity to dobrze. type=ALL to pełen skan i znak, że indeks nie został wybrany przez optymalizator albo nie istnieje.

Ostrzeżenie, które powtarzam każdemu zespołowi. Każdy nowy indeks spowalnia INSERT i UPDATE - bo trzeba go aktualizować. Magento wykonuje setki tysięcy update'ów dziennie (reindexer, price update, stock update). Nie dodawaj indeksów bez konkretnego uzasadnienia, jakie konkretne zapytanie chcesz przyspieszyć. Po dodaniu profilowanie przez SHOW INDEX FROM table plus EXPLAIN ANALYZE na realnym query.

Buffer pool, query cache, InnoDB tuning

Konfiguracja MySQL/MariaDB dla typowego sklepu Magento z 50 tys. SKU i około 500 zamówień dziennie na serwerze 32 GB RAM. To setup, który polecam jako punkt wyjścia, nie magiczny skrypt.

[mysqld]
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

query_cache_type = 0
query_cache_size = 0

max_connections = 500
thread_cache_size = 50
table_open_cache = 4000

sort_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 256M
max_heap_table_size = 256M

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1

Najważniejszy parametr to innodb_buffer_pool_size. To cache stron z dysku - im więcej, tym mniej fizycznych odczytów. Dla serwera dedykowanego pod MySQL ustawiam na 70-75% pamięci. Cel: hit ratio powyżej 99% (sprawdzasz przez SHOW ENGINE INNODB STATUS\G i szukasz "Buffer pool hit rate"). Jeśli widać poniżej 95%, dorzuć RAM-u.

Drugi ważny parametr - innodb_flush_log_at_trx_commit. To kompromis między trwałością a wydajnością. Wartość 1 oznacza pełen ACID, każdy commit synchronizowany do dysku. Wartość 2 oznacza, że log buffer trafia na dysk raz na sekundę - w razie crashu ryzykujesz utratę około sekundy transakcji, ale wydajność rośnie znacząco. Dla sklepu B2B akceptuję 2, bo zamówienie w gorszym wypadku retryje się z koszyka, a wydajność daje 20-30% więcej throughputu.

Query cache wyłączam zawsze (query_cache_type = 0). W MySQL 5.7 generuje contention i czasem spowalnia, w 8.0 jest deprecated. Cache'owanie wyników query do warstwy aplikacji (Redis) działa znacznie lepiej.

Skalowanie bazy danych w sklepie B2B - szersza strategia skalowania bazy.

Read replicas

Magento ma natywne wsparcie podziału read/write na różne bazy. To jest funkcja, którą warto włączyć, zanim w ogóle myślisz o sharding albo migracji na inną bazę.

Konfiguracja w env.php:

'db' => [
    'connection' => [
        'default' => [
            'host' => 'master.db.local',
        ],
        'indexer' => [
            'host' => 'master.db.local',
        ],
        'sales' => [
            'host' => 'master.db.local',
        ],
        'read' => [
            'host' => 'replica.db.local',
            'username' => 'magento',
            'password' => '...',
            'dbname' => 'magento',
        ],
    ],
],

Co przenoszę na replikę: catalog listing (PLP), wyniki wyszukiwarki, karta produktu (PDP), dashboard klienta tylko do odczytu, raporty. Wszystko, co nie musi widzieć ostatniego mikrokroku.

Co zostaje na masterze: zapisy zamówień, modyfikacje klienta, koszyk, reindexer (modyfikuje dane indeksów), operacje admina i wszystko, co musi widzieć świeże dane natychmiast po zapisie.

Lag replikacji w MySQL jest asynchroniczny, typowo 50-500 ms w zdrowym setupie. Magento ma świadomość lagu - po zapisie kolejne odczyty od tego samego klienta przez X sekund idą na master, eliminując problem "zapisałem produkt, nie widzę go w panelu". To rozwiązanie warte wdrożenia, bo eliminuje najczęstszą reklamację po włączeniu read replicas.

Pułapka: lag powyżej 5 sekund powoduje, że klient na PLP widzi nieaktualne stany magazynowe ("dostępne 10 sztuk", a faktycznie jest 0). Monitoring lagu z alertem do zespołu jest obowiązkowy, nie opcjonalny.

Partycjonowanie tabel

Tabele rosnące liniowo - sales_order, customer_log, sales_invoice - prędzej czy później mają miliony rekordów i wszystkie zapytania zaczynają zwalniać. Partycjonowanie pozwala MySQL operować tylko na relevantnej partycji, nie skanować całości.

Przykład dla sales_order partycjonowanego po roku:

ALTER TABLE sales_order
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

Zysk: zapytanie z WHERE created_at >= '2026-01-01' używa tylko partycji p2026, MySQL pomija resztę. DROP starszej partycji to operacja sekundowa, w przeciwieństwie do DELETE WHERE, które potrafi trwać godziny i blokować tabelę. Backupy per partycja są szybsze do wykonania i odzyskania.

Wady, których nie pomijam. Każde zapytanie bez warunku na klucz partycjonowania skanuje wszystkie partycje, czyli jest wolniejsze niż na nieparcjonowanej tabeli. Operacje DDL (zmiana schematu) komplikują się - alterowanie partycjonowanej tabeli to inny ból niż zwykłej. Magento bez modyfikacji nie wykorzystuje partition pruning we wszystkich query - czasem trzeba dodać WHERE created_at ręcznie tam, gdzie core go nie generuje.

Próg, na którym sięgam po partycjonowanie: tabela powyżej 10 mln rekordów, dominujące zapytania typu "ostatnie X miesięcy", wymóg archiwizacji starych danych. Poniżej tego progu próbuję najpierw indeksy i tuning, dopiero potem partycjonowanie.

Alternatywami są archive table (przeniesienie starych danych do osobnej tabeli sales_order_archive) albo manualne "partycje" - osobne tabele per rok z view nakładającą się na nie. Mniej eleganckie, ale czasem prostsze w utrzymaniu dla zespołu, który nie operuje na partycjach codziennie.

Migracja na PostgreSQL

Pytanie pojawia się regularnie: jak MySQL już nie wystarcza, czy nie czas na Postgres. Krótka odpowiedź: dla 95% sklepów na Magento ten ruch nie ma sensu, a koszt jest większy niż się wydaje na pierwszy rzut oka.

Postgres ma realne plusy. Skaluje się lepiej przy bazach powyżej 500 GB. Ma bardziej rozbudowane SQL - CTE, window functions, jsonb_path_query. Wspiera PostGIS dla geo-danych (zasięg kurierów, mapowanie magazynów). Operatory na JSON (@>, ?, jsonb_path_query) są lata świetlne lepsze niż w MySQL, jeśli przechowujesz atrybuty produktu jako JSON.

Wady, które przeważają w decyzji dla Magento. Po pierwsze - Magento natywnie wspiera tylko MySQL i MariaDB. Migracja na Postgres wymaga 6-12 miesięcy pracy i utrzymania własnego forka core'a, co jest pułapką długoterminową. Po drugie - większość problemów wydajnościowych, które ludzie chcą rozwiązać przez Postgres, to brak indeksów albo zły tuning MySQL. Postgres bez audytu też tego nie uleczy. Po trzecie - ekosystem narzędzi dla MySQL w e-commerce jest dojrzalszy (monitoring, backup, replication, Percona Toolkit). Po czwarte - DBA MySQL znajdziesz szybciej niż Postgres, zwłaszcza w polskim rynku.

W praktyce dla 95% sklepów B2B kombinacja tuningu MySQL/MariaDB, read replicas i porządnych indeksów wystarcza do dowolnej rozsądnej skali. Migracja na Postgres ma sens dla custom platform pisanych od zera albo wyboru platformy natywnie Postgresowej (Sylius, OroCommerce) - ale to wybór architektoniczny przy zakładaniu sklepu, nie migracja istniejącego Magento.

Audyt wydajności sklepu - kontekst całego audytu wydajności sklepu B2B.

FAQ

Jak włączyć i czytać slow query log w produkcji? Włączenie idzie przez SET GLOBAL slow_query_log = 'ON', SET GLOBAL long_query_time = 1 i SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'. Analiza najszybciej przez pt-query-digest z Percona Toolkit, który agreguje per zapytanie i sortuje po sumarycznym koszcie. Włącz na 24-48 godzin podczas typowego ruchu, potem wyłącz - sam slow log generuje koszt IO.

Czy lepiej skalować bazę pionowo czy poziomo? Pionowo do pewnego punktu (większy serwer) jest prostsze i tańsze w utrzymaniu - 16 GB, 64 GB, 256 GB RAM dokłada się bez zmian w kodzie. Gdy serwer 256 GB i tak siada, czas na poziome skalowanie przez read replicas. Sharding rozważam dopiero dla naprawdę dużych projektów, bo wprowadza złożoność na każdym poziomie aplikacji.

Czy warto migrować z MySQL na MariaDB? Niewielki zysk, niewielkie ryzyko. MariaDB ma kilka optymalizacji (storage engines, JSON, ColumnStore), ale Magento jest oficjalnie testowane na MySQL. Migracja jest prosta (drop-in replacement), ale ROI raczej mały. Sensowne dla nowych instalacji, raczej nie warte ryzyka na działającej produkcji.

Co z MySQL 8.0 vs MariaDB 11.x? MySQL 8.0 ma window functions, lepsze JSON, lepsze CTE. MariaDB 11.x ma kilka optymalizacji wydajności i lepszą kompatybilność wsteczną. Dla Magento 2.4.7+ MySQL 8.0 jest oficjalnie wspierany - jego polecam jako default. MariaDB tylko jeśli zespół ma dobre powody, żeby na niej zostać.

Czy Percona Server jest lepszy niż standard MySQL? Marginalnie. Percona ma rozszerzone Performance Schema, kilka optymalizacji wydajności (Thread Pool, lepsza obsługa concurrency). Dla nowej instalacji - sensowny wybór, bo daje dodatkowe narzędzia bez wad. Migracja z standard MySQL na Percona - rzadko warta ryzyka.

Co dalej

• Wydajność sklepu B2B: Wydajność e-commerce
• Audyt wydajności: Audyt wydajności sklepu
• Skalowanie bazy: Skalowanie bazy danych