Czyszczenie bazy danych: dlaczego Twój sklep PrestaShop z czasem działa coraz wolniej

To problem, o którym słyszymy bardzo często: Twój sklep PrestaShop działał szybko w dniu uruchomienia, a rok później te same strony ładują się mozolnie. Ten sam hosting, ten sam motyw, te same moduły, nic oczywistego się nie zmieniło. Co więc się stało? W dużej części takich przypadków winne jest właśnie to, co zmieniało się po cichu, każdego dnia działania sklepu: baza danych. PrestaShop domyślnie zapisuje zaskakująco dużo aktywności, a tabele, które przy starcie miały kilkaset wierszy, po czasie mają ich miliony. Zapytania natychmiastowe na małej tabeli zaczynają wyraźnie obciążać dużą, a ponieważ strony produktów, strony kategorii i panel administracyjny korzystają z tych tabel, spowolnienie rozlewa się po całym sklepie.

Ten poradnik dotyczy konkretnie rozrostu bazy danych, który narasta z czasem. Tego, które tabele rosną, dlaczego rosną, co dokładnie można bezpiecznie wyczyścić i jak zrobić to bez uszkodzenia sklepu. To nie jest ogólny tekst o tym, "dlaczego PrestaShop działa wolno" (moduły, hosting i ciężar front-endu to osobne przyczyny, omawiamy je w artykule co naprawdę spowalnia PrestaShop) ani tekst o obchodzeniu wolnych zapytań cache'em (Redis i pełnostronicowy cache mają własne wpisy). Tutaj zajmujemy się osadem, który narasta wewnątrz samych tabel.

Opisane niżej tabele podatne na rozrost i zasady bezpiecznego czyszczenia obowiązują w PrestaShop 1.7, 8.x i 9.x. Dokładny zestaw tabel statystyk zależy od wersji i zainstalowanych modułów, dlatego najpierw wykonaj zapytanie audytowe, zawsze zmierz własny schemat, zanim wskażesz cokolwiek do usunięcia.

Dlaczego bazy danych PrestaShop rosną po cichu

PrestaShop zawiera system statystyk i logowania, który zapisuje znacznie więcej danych, niż większość sprzedawców sobie uświadamia. Połączenia, sesje gości, wyszukiwania wewnętrzne, błędy 404, logi wysłanych wiadomości, porzucone koszyki, gdy odpowiednie funkcje statystyk i logowania są włączone, wszystko trafia do bazy danych, a zasady retencji niemal w całości pozostają po Twojej stronie. PrestaShop nie ma wbudowanej polityki typu "zachowuj 90 dni", a przycinanie zależy od używanych modułów i konfiguracji, więc w większości sklepów dane po prostu się kumulują.

Dla umiarkowanie dużego sklepu z ruchem 500 odwiedzających dziennie matematyka jest bezlitosna:

• ps_connections, jeden wiersz na połączenie odwiedzającego. 500 dziennie to ok. 15 000 miesięcznie, ok. 182 000 rocznie, i ten licznik nigdy się nie zatrzymuje.
• ps_connections_source, śledzenie źródeł/referrerów HTTP dla każdego połączenia; często tabela większa niż samo ps_connections.
• ps_guest, jeden wiersz na unikalnego gościa (odcisk przeglądarki/systemu/ekranu), narastający dla każdego niezalogowanego odwiedzającego.
• ps_log, log aplikacji. Jeden gadatliwy moduł generujący notice'y potrafi zapisywać kilka wierszy przy każdym wczytaniu strony i wypchnąć tę tabelę do milionów rekordów.
• ps_pagenotfound, każdy błąd 404, w tym nieustanny ruch botów sprawdzających ścieżki typu /wp-admin, /phpmyadmin i podobne.
• ps_statssearch, każde zapytanie wpisane przez odwiedzającego w wyszukiwarce sklepu.
• ps_cart i ps_cart_product, każdy kiedykolwiek utworzony koszyk, z których przytłaczająca większość nigdy nie stała się zamówieniem.
• ps_mail, log każdej wiadomości e-mail wysłanej przez PrestaShop.
• natywne tabele statystyk (ps_statssearch, ps_searchengine, a w niektórych instalacjach ps_referrer/ps_referrer_cache), warstwa analityczna, z której prawie nikt realnie nie korzysta w PrestaShop, bo zamiast niej używa Google Analytics. Dokładna obecność tych tabel zależy od wersji PrestaShop, zainstalowanych modułów statystyk i historii aktualizacji bazy danych, dlatego najpierw przeaudytuj faktyczny schemat, zamiast zakładać stałą listę.

Znaczenie jest proste: po dwóch lub trzech latach te tabele rutynowo mają miliony wierszy, zestaw roboczy MySQL przestaje wygodnie mieścić się w pamięci, pula buforów InnoDB zaczyna się dławić, a nawet dobrze zaindeksowane zapytania zwalniają, bo silnik czyta więcej stron z dysku. Twój sklep nie stał się cięższy w sposób widoczny dla klienta, stał się ciężki tam, gdzie czuje to tylko baza danych.

Najpierw sprawdź, co naprawdę urosło w Twoim sklepie

SELECT table_name, table_rows, ROUND((data_length + index_length) / 1024 / 1024, 1) AS mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name LIKE 'ps\_%'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Nie czyść na ślepo. Każdy sklep rozrasta się inaczej, w zależności od struktury ruchu i zainstalowanych modułów, dlatego pierwszym krokiem jest zmierzenie rozmiarów tabel. Uruchom to w phpMyAdminie (albo w Adminerze lub w MySQL CLI), podstawiając rzeczywistą nazwę bazy danych, zapytanie pokaże największe tabele według liczby wierszy i rozmiaru na dysku:

• SELECT table_name, table_rows, ROUND((data_length + index_length)/1024/1024, 1) AS size_mb FROM information_schema.tables WHERE table_schema = 'your_db_name' ORDER BY (data_length + index_length) DESC LIMIT 25;

Przeczytaj wynik, zanim czegokolwiek dotkniesz. Jeśli na szczycie listy są ps_orders, ps_order_detail i ps_product_lang, to jest zdrowy rozrost, prawdziwe dane biznesowe, które musisz zachować. Jeśli na górze widzisz ps_connections, ps_guest, ps_log, ps_statssearch albo ps_pagenotfound, znalazłeś śmieci warte gigabajtów. To jedno zapytanie mówi Ci, czy reszta artykułu w ogóle dotyczy Twojego sklepu i które tabele brać na cel.

Które tabele czyścić, a których nigdy nie ruszać

To moment, w którym poradniki stają się niebezpieczne, więc trzeba być precyzyjnym. Tabele dzielą się na trzy grupy.

Tabela koszyków wymaga skalpela, nie młotka

Najczęstszy błąd, jaki widzimy, to całkowite czyszczenie ps_cart przez TRUNCATE, żeby odzyskać miejsce. Nie rób tego. Wiersz koszyka bez odpowiadającego mu id_cart w ps_orders jest porzuconym koszykiem i można go bezpiecznie usunąć, gdy jest już stary; koszyk, który ma powiązanie z zamówieniem, stanowi część historii tego zamówienia. Bezpieczny wzorzec wskazuje wyłącznie osierocone koszyki starsze niż wybrany okres, następnie usuwa najpierw ich rekordy podrzędne w ps_cart_product, a dopiero potem same koszyki, aby nie zostawić osieroconych odwołań. Najpewniejszy sposób to zbudować z góry listę kandydatów id_cart, a potem usuwać po tej liście. Dzięki temu można też czyścić partiami. Pamiętaj, że MySQL nie pozwala użyć LIMIT przy wielotabelowym DELETE (JOIN), więc przy samym usuwaniu całkowicie unikamy formy z JOIN:

• CREATE TEMPORARY TABLE tmp_orphan_carts AS SELECT c.id_cart FROM ps_cart c LEFT JOIN ps_orders o ON o.id_cart = c.id_cart WHERE o.id_order IS NULL AND c.date_add < DATE_SUB(NOW(), INTERVAL 60 DAY);
• DELETE FROM ps_cart_product WHERE id_cart IN (SELECT id_cart FROM tmp_orphan_carts) LIMIT 20000;, powtarzaj, aż zgłosi 0 zmienionych wierszy.
• DELETE FROM ps_cart WHERE id_cart IN (SELECT id_cart FROM tmp_orphan_carts) LIMIT 20000;, ponownie, powtarzaj aż do 0 wierszy.
• DROP TEMPORARY TABLE tmp_orphan_carts;

Każde DELETE celuje teraz w jedną tabelę, bez joinów, więc LIMIT 20000 jest poprawny i możesz uruchamiać każdą instrukcję ponownie, aż oczyści dane. Zawsze zakończ usuwanie rekordów podrzędnych w ps_cart_product, zanim usuniesz rekordy nadrzędne w ps_cart, a samo porcjowanie nie jest przypadkowe. Następna sekcja wyjaśnia, dlaczego ma większe znaczenie, niż mogłoby się wydawać.

Jak czyścić bezpiecznie, nie wyłączając sklepu

Krok 1, wykonaj kopię zapasową bazy danych, bez wyjątków

Przed każdym DELETE albo TRUNCATE wykonaj pełną kopię danych (mysqldump albo snapshot od hostingu). W PrestaShop istnieje narzędzie Parametry zaawansowane → Baza danych → Kopia zapasowa DB, ale przy dużych tabelach bardziej niezawodny jest mysqldump po stronie serwera. Jeśli czyszczenie pójdzie źle, przywrócenie kopii będzie jedyną siatką bezpieczeństwa, traktuj je jako obowiązkowe, nie opcjonalne.

Krok 2, usuwaj partiami, nie jednym gigantycznym zapytaniem

DELETE FROM ps_connections
WHERE date_add < DATE_SUB(NOW(), INTERVAL 180 DAY)
LIMIT 5000;

Pojedyncze DELETE usuwające dwa miliony wierszy zakłada długą blokadę i może unieruchomić front sklepu na czas działania. Pracuj partiami po 10 000–50 000 wierszy (LIMIT w zapytaniach koszykowych powyżej), z krótką przerwą między uruchomieniami, i powtarzaj, aż tabela będzie czysta. Gdy chcesz zachować określony okres retencji zamiast opróżniać tabelę, na przykład zostawić ostatnie 90 dni, a usunąć resztę, użyj DELETE z filtrem daty, takiego jak DELETE FROM ps_connections WHERE date_add < DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 50000;, powtarzanego do momentu, aż zgłosi 0 wierszy. Po TRUNCATE TABLE ps_connections; sięgaj tylko wtedy, gdy naprawdę chcesz usunąć wszystko z jednorazowej tabeli logów. Jest dużo szybsze, bo usuwa i odtwarza tabelę zamiast kasować wiersze jeden po drugim, ale nie da się go filtrować po dacie i resetuje autoinkrementację, więc rezerwuj je do opróżniania całych tabel, wykonując operacje w kolejności bezpiecznej dla zależności, jeśli powiązane tabele odwołują się do siebie.

Krok 3, odzyskaj miejsce na dysku przez OPTIMIZE TABLE

To część, którą sprzedawcy często pomijają. PrestaShop używa InnoDB, a po dużym DELETE liczba wierszy spada, ale plik na dysku się nie zmniejsza, zwolnione miejsce pozostaje zarezerwowane jako wewnętrzna fragmentacja, a zapytania nadal mogą czytać te puste strony. Trzeba jawnie przebudować tabelę:

• OPTIMIZE TABLE ps_connections, ps_connections_source, ps_guest, ps_log, ps_pagenotfound, ps_statssearch;

W InnoDB OPTIMIZE TABLE przebudowuje tabelę i ponownie analizuje jej indeksy, defragmentując dane. To, czy faktycznie zwróci miejsce systemowi operacyjnemu, zależy od konfiguracji: przy włączonym innodb_file_per_table, domyślnym w nowoczesnym MySQL/MariaDB, każda tabela ma własny tablespace i zwolnione miejsce wraca do systemu plików; we współdzielonym systemowym tablespace tak się nie stanie. Przebudowa może też blokować albo kopiować tabelę w zależności od wersji i konfiguracji MySQL/MariaDB, dlatego uruchamiaj ją po usuwaniu, w oknie serwisowym albo przy niskim ruchu, i sprawdź zachowanie na konkretnej wersji serwera, zanim założysz, że miejsce zostanie odzyskane.

Krok 4, czyść cache PrestaShop tylko wtedy, gdy musisz

Usuwanie wierszy z tabel logów i statystyk to porządkowanie danych, a nie zmiana kodu ani schematu, więc zwykle nie wymaga czyszczenia cache'u, PrestaShop nie cache'uje tych surowych wierszy logów. Wyczyść cache tylko wtedy, gdy porządki dotknęły danych, które coś aktualnie cache'uje (na przykład statystyk panelu zasilających cache'owany widget), albo po zmianach kodu, modułu lub schematu, które rzeczywiście unieważniają skompilowany wynik. Gdy jest to potrzebne, użyj Parametry zaawansowane → Wydajność → Wyczyść cache w panelu administracyjnym (albo usuń zawartość var/cache/); w wersjach utrzymujących skompilowany indeks klas jego przebudowa odbywa się w ramach tego samego czyszczenia.

Czyszczenie z panelu administracyjnego zamiast surowego SQL

Lista stanu sprawdza wersję PHP, moduły, tabele bazy danych, fragmentację, wiersze do wyczyszczenia i rozmiar pamięci podręcznej, każdy oznaczony jako jeszcze niesprawdzony.

Konfiguracja powinna znajdować się w module, a nie w doraźnym SQL trzymanym na czyimś laptopie.

Surowy SQL jest precyzyjny, ale bezlitosny, jeden brakujący JOIN i osierocisz zamówienie. Jeśli wolisz nie pisać instrukcji DELETE przeciwko działającemu sklepowi, masz dwie bezpieczniejsze drogi wewnątrz PrestaShop:

• SQL Manager (wbudowany): Parametry zaawansowane → Baza danych → Menedżer SQL pozwala zapisać i ponownie uruchamiać zapytanie audytujące rozmiary oraz wszelkie kontrole tylko do odczytu. To przede wszystkim narzędzie do SELECT, ale jest właściwym miejscem do mierzenia przed i po oraz do trzymania pod ręką zapytania audytowego z miesiąca na miesiąc.
• Moduł konserwacyjny: dedykowany moduł do czyszczenia obsługuje logikę osieroconych koszyków, usuwanie partiami i krok OPTIMIZE z ekranu panelu administracyjnego z checkboxami, bez SQL, z operacjami destrukcyjnymi ukrytymi za potwierdzeniem. To ścieżka, którą wskazalibyśmy nietechnicznemu właścicielowi sklepu, właśnie dlatego, że usuwa ryzyko omyłkowego DELETE na ps_orders.

Niezależnie od wyboru polityka jest taka sama jak w SQL powyżej, narzędzie zmienia tylko to, jak bezpiecznie ją wykonujesz.

Zrób z tego rutynę, nie coroczną panikę

Rozrost trwa cały czas, więc czyszczenie też powinno być ciągłe. Sklep wyczyszczony raz, a potem pozostawiony bez opieki, w ciągu kilku miesięcy wróci do podobnych liczników wierszy. Ustal stałą miesięczną rutynę:

• Przejrzyj pobieżnie ps_log i ps_pagenotfound pod kątem realnych problemów (powtarzające się błędy, faktycznie uszkodzone linki), a potem usuń wpisy starsze niż 30 dni.
• Usuń osierocone koszyki starsze niż 60 dni (dwa zapytania koszykowe powyżej), najpierw rekordy podrzędne w ps_cart_product, potem same koszyki.
• Usuń wiersze ps_connections, ps_connections_source i ps_guest starsze niż 90 dni za pomocą DELETE z filtrem daty (uważaj na FK ps_guest → ps_customer), albo wyczyść je całkowicie przez TRUNCATE, jeśli w ogóle nie zachowujesz okna retencji.
• Przytnij ps_statssearch i ps_mail do ok. 90 dni tym samym wzorcem DELETE z filtrem daty.
• Uruchom OPTIMIZE TABLE na wszystkim, co czyściłeś.

Cała sekwencja może mieszkać w skrypcie SQL uruchamianym co miesiąc przez zadanie cron na serwerze albo być wykonywana według harmonogramu przez moduł konserwacyjny. Automatyzacja jest różnicą między sklepem, który pozostaje szybki, a takim, który po cichu wraca do 4-sekundowych czasów ładowania stron, gdy Ty zajmujesz się prowadzeniem biznesu.

Poziom głębiej: indeksy i same zapytania

Przycinanie wierszy daje największy efekt, bo zmniejsza każdy skan tabeli naraz, ale warto wspomnieć o dwóch powiązanych kwestiach, żeby jasno widzieć granicę:

• Brakujące indeksy w modułach zewnętrznych. Wiele modułów tworzy własne tabele bez indeksowania kolumn, po których później filtruje. Włącz slow query log MySQL, obserwuj zapytania wykonujące pełny skan tabeli modułu i dodaj indeks na kolumnie z WHERE/JOIN. Czysta tabela z właściwym indeksem potrafi skrócić 2-sekundowe zapytanie do kilku milisekund.
• Strojenie MySQL na poziomie serwera (innodb_buffer_pool_size i podobne) oraz szersza optymalizacja zapytań to osobna dziedzina, obszar hostingu i konfiguracji, opisany w artykułach optymalizacja wydajności od zapytań bazodanowych po pełnostronicowy cache oraz wybór hostingu dla PrestaShop. Najpierw wyczyść dane, dzięki czemu będziesz stroić szczupłą bazę danych zamiast przerośniętej, zrób ten krok, zanim kupisz większy serwer.

Zmierz stan przed i po, porządnie

Nie polegaj na wrażeniu, że "jest szybciej". Przed czyszczeniem zapisz TTFB (Time to First Byte) dla stałego zestawu stron, strony głównej, jednej strony produktu, jednej strony kategorii i pulpitu panelu administracyjnego (admin często cierpi przez rozrost najbardziej, bo tabele statystyk zasilają widgety pulpitu). Zanotuj też największe rozmiary tabel z zapytania audytowego. Po czyszczeniu i OPTIMIZE zmierz ponownie te same strony i te same tabele.

Nie podamy Ci gwarantowanego procentu. Zysk zależy całkowicie od tego, jak duży był rozrost na starcie. Sklep, w którym ps_connections przechowywało trzy lata logów, zobaczy znacznie większy skok niż sklep, który już wcześniej był uporządkowany. Jeśli diagnostyka nie wskazuje jasno na bazę danych, zacznij od uporządkowanego sprawdzenia wolnego działania zamiast zgadywać: czy Twój sklep działa wolno, jak to sprawdzić i co z tym zrobić. A jeśli chcesz zobaczyć, jak wygląda w pełni zoptymalizowany PrestaShop od początku do końca, opisaliśmy taki przypadek w artykule PageSpeed 99/100 w prawdziwym sklepie ze 130 modułami.

Zasada pozostaje taka sama niezależnie od wybranej drogi: PrestaShop bez problemu zaloguje się do ziemi, jeśli mu na to pozwolisz, bo nic nie przycina tych tabel za Ciebie. Znajdź realny rozrost, usuń wyłącznie to, co rzeczywiście jest jednorazowe, odzyskaj miejsce przez OPTIMIZE i wpisz proces w harmonogram. Zrób to, a sklep zachowa szybkość z dnia uruchomienia, przez lata działania, bez kupowania czegokolwiek.

Najczęściej zadawane pytania

Czy bezpiecznie jest wyczyścić przez TRUNCATE ps_connections i ps_guest?

Czyszczenie przez TRUNCATE ps_connections i tabel towarzyszących (ps_connections_source, ps_connections_page) jest bezpieczne, jeśli nie używasz natywnych statystyk PrestaShop, te tabele zasilają analitykę, którą większość sprzedawców i tak zastępuje Google Analytics. ps_guest wymaga nieco więcej ostrożności ze względu na powiązanie kluczem obcym z ps_customer; lepiej użyć DELETE z filtrem daty, który zachowuje najnowsze wiersze, niż ślepego TRUNCATE. Czego nigdy nie wolno czyścić przez TRUNCATE, to ps_cart. Porzucone koszyki są wymieszane z koszykami należącymi do prawdziwych zamówień, więc tu potrzebny jest pokazany wyżej skalpel ograniczony do osieroconych rekordów, nie młotek.

Usunąłem miliony wierszy, ale dysk się nie zmniejszył. Dlaczego?

InnoDB nie zwraca zwolnionego miejsca systemowi operacyjnemu po DELETE, liczba wierszy spada, ale plik na dysku zostaje w tym samym rozmiarze, a zwolnione strony są trzymane jako wewnętrzna fragmentacja. Trzeba przebudować tabelę przez OPTIMIZE TABLE, aby je odzyskać, i nawet wtedy miejsce wróci do systemu plików tylko wtedy, gdy włączone jest innodb_file_per_table (domyślnie w nowoczesnym MySQL/MariaDB). Uruchamiaj OPTIMIZE po usuwaniu, w oknie niskiego ruchu, ponieważ w zależności od wersji może blokować albo kopiować tabelę.

Czy po czyszczeniu bazy danych muszę wyczyścić cache PrestaShop?

Zwykle nie. Usuwanie wierszy z tabel logów i statystyk to porządkowanie danych, a nie zmiana kodu ani schematu, i PrestaShop nie cache'uje tych surowych wierszy. Wyczyść cache tylko wtedy, gdy porządki dotknęły danych, które coś aktualnie cache'uje, powiedzmy statystyk pulpitu zasilających cache'owany widget, albo po rzeczywistych zmianach kodu/modułu/schematu. Rutynowe przycinanie logów i koszyków tego nie wymaga.

Dlaczego usuwać partiami zamiast jednym zapytaniem?

Pojedyncze DELETE usuwające dwa miliony wierszy zakłada długą blokadę i może zamrozić front sklepu na czas działania. Praca partiami po 10 000–50 000 wierszy, z krótką przerwą między nimi, utrzymuje każdą blokadę krótko i pozwala sklepowi odpowiadać. Używaj DELETE z filtrem daty i LIMIT, powtarzanego do momentu, aż zgłosi zero wierszy. TRUNCATE jest wyjątkiem. Działa prawie natychmiast, bo usuwa i odtwarza tabelę, ale nie da się go filtrować po dacie, więc zostaw je do opróżniania całej jednorazowej tabeli logów.

Czy to wszystko znowu nie urośnie?

Tak, i właśnie dlatego trzeba robić to cyklicznie. PrestaShop nie ma wbudowanej polityki retencji, więc sklep wyczyszczony raz w ciągu kilku miesięcy wróci do tych samych liczników wierszy. Wpisz sekwencję (przycinanie tabel logów/statystyk/koszyków, potem OPTIMIZE) w miesięczny cron albo pozwól modułowi konserwacyjnemu uruchamiać ją według harmonogramu, żeby tabele nie miały okazji urosnąć z powrotem. Automatyzacja jest różnicą między utrzymaniem szybkości a cichym powrotem do 4-sekundowego ładowania stron.

Powiązane artykuły

• Database Cleanup, uruchamia logikę osieroconych koszyków, usuwanie partiami i OPTIMIZE z panelu administracyjnego według harmonogramu, pokazując liczniki wierszy przed usunięciem czegokolwiek
• Performance Revolution, gdy wyczyszczona baza danych nadal ogranicza TTFB, dodaje na wierzchu pełnostronicowy cache i warstwę critical CSS
• Optymalizacja wydajności: od zapytań bazodanowych po pełnostronicowy cache, indeksy, EXPLAIN i strojenie InnoDB dla szczupłej bazy danych, którą właśnie utworzyłeś
• Co naprawdę spowalnia PrestaShop. Potwierdź, że przyczyną jest rozrost bazy, zanim zaczniesz usuwać dane