Neu JTL Shop 5 - Datenbank optimieren

[Horus Sirius]

Hallo liebe JTL-Community,

Hab ein Upgrade von JTL Shop 4.0.6 Build 17 auf JTL Shop 5 durchgeführt.
Die Merkmalfilter sind jetzt viel präsenter und machen laut SQL-Query Log Probleme, sind also Slow Queries.
Es wird Redis als Caching System verwendet und PHP 7.4 mit fast-cgi.

Ein extrem langsamer query sieht so aus.

SELECT tseo.cSeo,
ssMerkmal.kHersteller,
ssMerkmal.cName,
ssMerkmal.nSortNr,
ssMerkmal.cBildPfad,
COUNT(*) AS nAnzahl
FROM (SELECT thersteller.kHersteller, thersteller.cName, thersteller.nSortNr, thersteller.cBildPfad, tartikel.kArtikel
FROM tartikel
#JOIN from JTL\Filter\States\BaseCharacteristic::getSQLJoin
JOIN (SELECT kArtikel
FROM tartikelmerkmal
WHERE kMerkmalWert = 208
GROUP BY tartikelmerkmal.kArtikel
) AS tmerkmaljoin ON tmerkmaljoin.kArtikel = tartikel.kArtikel

#JOIN from JTL\Filter\States\BaseManufacturer::getOptions
JOIN thersteller ON tartikel.kHersteller = thersteller.kHersteller

#join from SORT by bestseller
LEFT JOIN tbestseller ON tartikel.kArtikel = tbestseller.kArtikel

#product visiblity join from getBaseQuery
LEFT JOIN tartikelsichtbarkeit ON tartikel.kArtikel = tartikelsichtbarkeit.kArtikel
AND tartikelsichtbarkeit.kKundengruppe = 1
WHERE tartikelsichtbarkeit.kArtikel IS NULL AND tartikel.kVaterArtikel = 0 AND (tartikel.cLagerBeachten != 'Y'
OR tartikel.fLagerbestand > 0
OR (tartikel.cLagerVariation = 'Y'
AND (
SELECT MAX(teigenschaftwert.fLagerbestand)
FROM teigenschaft
INNER JOIN teigenschaftwert ON teigenschaftwert.kEigenschaft = teigenschaft.kEigenschaft
WHERE teigenschaft.kArtikel = tartikel.kArtikel
) > 0
))
#default group by
GROUP BY tartikel.kArtikel

#limit sql
ORDER BY tbestseller.fAnzahl DESC, tartikel.cName) AS ssMerkmal
LEFT JOIN tseo
ON tseo.kKey = ssMerkmal.kHersteller
AND tseo.cKey = 'kHersteller'
AND tseo.kSprache = 2
GROUP BY ssMerkmal.kHersteller
ORDER BY ssMerkmal.nSortNr, ssMerkmal.cName

Es handelt sich um über 46.000 Artikel mit tartikelmerkmal 479.000 Merkmaldaten. Der JTL Shop 4 lief meist mit 5% CPU jetzt sind es 100% CPU bei diesen slow-queries, welche schon eine Stunde bis zu mehreres Stunden laufen können je nach Anzahl dieser Slow-Queries. Ebenfalls als langsam habe ich folgende Anfrage identifiziert.

SELECT tartikel.kArtikel
FROM tartikel
#JOIN from JTL\Filter\States\BaseCharacteristic::getSQLJoin
JOIN (SELECT kArtikel
FROM tartikelmerkmal
WHERE kMerkmalWert = 207
GROUP BY tartikelmerkmal.kArtikel
) AS tmerkmaljoin ON tmerkmaljoin.kArtikel = tartikel.kArtikel

#join from JTL\Filter\Items\Category::getSQLJoin
JOIN tkategorieartikel ON tartikel.kArtikel = tkategorieartikel.kArtikel

#join from JTL\Filter\Items\Characteristic::getSQLJoin
JOIN tartikelmerkmal ON tartikel.kArtikel = tartikelmerkmal.kArtikel

#join from SORT by bestseller
LEFT JOIN tbestseller ON tartikel.kArtikel = tbestseller.kArtikel

#product visiblity join from getBaseQuery
LEFT JOIN tartikelsichtbarkeit ON tartikel.kArtikel = tartikelsichtbarkeit.kArtikel
AND tartikelsichtbarkeit.kKundengruppe = 1
WHERE
#condition from filter kf
tkategorieartikel.kKategorie IN (307) AND
tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (86)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition Schmuck in braun
AND
tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (210)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition Erwachsene
AND tartikelsichtbarkeit.kArtikel IS NULL AND tartikel.kVaterArtikel = 0 AND (tartikel.cLagerBeachten != 'Y'
OR tartikel.fLagerbestand > 0
OR (tartikel.cLagerVariation = 'Y'
AND (
SELECT MAX(teigenschaftwert.fLagerbestand)
FROM teigenschaft
INNER JOIN teigenschaftwert ON teigenschaftwert.kEigenschaft = teigenschaft.kEigenschaft
WHERE teigenschaft.kArtikel = tartikel.kArtikel
) > 0
))
#default group by
GROUP BY tartikel.kArtikel

#limit sql
ORDER BY tbestseller.fAnzahl DESC, tartikel.cName

Haben andere solche Probleme auch bemerkt? Der Komplette Server läuft mit 100% CPU und timed out teilweise komplett out mit HTTP-CODE 524 / 504.

Wie kriege ich diese Abfragen beschleunigt?
exmined_rows also untersuchte Zeilen sind teilweise 700.000.000 Stück für eine einzelne Abfrage, scheint an den subqueries im FROM zu liegen.
Eingesetzt wird MariaDB 10.5.8 (teilweise restartet der Datenbankserver alleine)


JTL Shop 4 zu JTL Shop 5 - der Unterschied.

 

[2275]

Hallo,

da wir das selbe Problem haben, möchte ich gerne wissen, ob es bei euch mittlerweile behoben wurde.

Beste Grüße

 

[FMoche]

Habt ihr mal im Backend unter /admin/dbcheck.php alle Tabellen zumindest analysiert?
Durch geänderte Key-Strukturen kann das beim Update von 4 auf 5 einen gewaltigen Unterschied machen.

 

[Horus Sirius]

Habt ihr mal im Backend unter /admin/dbcheck.php alle Tabellen zumindest analysiert?
Durch geänderte Key-Strukturen kann das beim Update von 4 auf 5 einen gewaltigen Unterschied machen.

Ihr solltet eventuell in euren JTL Shop Guide schreiben, das bei einem Serverumzug die Datenbank z.B. bei MariaDB 10.5 die Datenbank query -> "statistics" ausführt und dieser Prozess sehr CPU-hungrig ist selbst bei einem 16 Kern Rechner mit 32 Threads können ~ 48.000 (1 Sprache) * Anzahl der Sprachen zu einer Downtime von vielen Stunden führen.

 

[okh]

hatte das Problem ähnlich mit Prozessen die niemals enden bzgl. Merkmalwerten. Nach 2 unbeantworteten Tickets habe ich mich auf die Suche gemacht. Letztendlich waren es Merkmale die veraltet in der ShopDB dümpelten und select querys auslösten die mitunter stundenweise liefen und nur über kill beendet erden konnten. Haben bei mir den Server in die Knie gezwungen.

Ich empfehle tatsächlich Merkmale in der Shop DB händisch zu löschen und aus der Wawi neu zu übertragen.

Tipp: schaut euch die Processlist der DB an.

 

[Horus Sirius]

hatte das Problem ähnlich mit Prozessen die niemals enden bzgl. Merkmalwerten. Nach 2 unbeantworteten Tickets habe ich mich auf die Suche gemacht. Letztendlich waren es Merkmale die veraltet in der ShopDB dümpelten und select querys auslösten die mitunter stundenweise liefen und nur über kill beendet erden konnten. Haben bei mir den Server in die Knie gezwungen.

Ich empfehle tatsächlich Merkmale in der Shop DB händisch zu löschen und aus der Wawi neu zu übertragen.

Ich habe mehrere Serverumzüge gemacht, das Problem besteht wohl bei MariaDB für Version 10.5 das setze ich immer ein in Kombination von InnoDB auch die Migration von JTL Shop 4 zu JTL Shop 5 kann das zeitversetzt auslösen.

Die Prozesse nicht killen, die "respawnen" einfach erneut, zeitverzögert, würde eher sagen die Logik von MariaDB beim Umwandeln von myISAM auf InnoDB Datenbank Engine sollte das besser hinbekommen. Plesk Migrator Serverumzüge kopieren glaube ich die ibdata nicht mit, somit wird die komplette Datenbank Struktur für den Arbeitsspeicher erneut aufgebaut.

Empfehle die Datenbank in Ruhe zu lassen nix killen, das Problem kehrt zurück, ich weiß nicht genau ob die Querys nen Zwischenergebnis speichern.
Wenn das erstmal durchgelaufen ist, ist auch Ruhe und alles ist super schnell (mit z.B. mit Redis)