Kategorie lässt sich nur einmal speichern, dann hängt es (Lock Wait Timeout)

Shopware 6 (German) Programmierung
1

Hallo,

wir haben das Problem, dass beim manuellen Ändern von Kategorien im SW6-Backend, das nur einmal funktioniert. Aber wenn ich dann nochmal irgendwo ein Zeichen ranhänge und speichere, passiert nichts mehr. Es lädt ewig und bricht dann irgendwann ab.

Ich muss dazu sagen, dass wir über 100.000 Kategorien haben. Die meisten sind über die API importiert, aber manche auch manuell erstellt, was am Anfang mit wenigen Kategorien gut geklappt hat.

Ich habe das slow_query_log in mysql aktiviert. Da steht jetzt sowas drin:

# Query_time: 50.005573  Lock_time: 50.004758 Rows_sent: 0  Rows_examined: 0
use shopware;
SET timestamp=1728482674;
UPDATE `category` SET `version_id` = '^O©^\ãéjK¾KÙÎu,4%', `parent_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `after_category_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `cms_page_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `updated_at` = '2024-10-09 14:04:34.029' WHERE id = '^A<8e>\r£Â<92>r\" é<8c>xð<96>\nè' AND version_id = '^O©^\ãéjK¾KÙÎu,4%';

Es lädt 50 Sek mit Lock und bricht dann ab.
Mit den binary Daten kann ich wenig anfangen, sonst hätte ich mal geschaut, ob das wirklich die betreffende Kategorie ist, die ich gerade geändert habe. (Nebenfrage: Kann man diesen SQL-Befehl irgendwie verwertbar konvertieren?)

Auch wenn ich einen ähnlichen, simplen Befehl direkt auf der DB absetze, kommt der Lock Wait timeout Fehler:

update category
set updated_at = '2024-10-09 14:04:00.000'
where id = unhex('018e0da3c2927222a0e98c78f0960ae8');

>> Error Code: 1205. Lock wait timeout exceeded; try restarting transaction

Ich hab schon alle möglichen Performance-Optionen in der mysql.cnf aktiviert. Aber nichts hat einen Unterschied gemacht.

Ich habe die Befürchtung, dass hier bei Änderunge einer Kategorie irgendeine Art von Indizierung angestoßen wird, die mit den ganzen Abhängigkeiten der Tabellen zu lange läuft.

Komischerweise läuft die regelmäßige Indizierung via RabbitMQ problemlos durch:

bin/console dal:refresh:index --only=category.indexer --use-queue

Der Admin-Worker ist disabled.

Gibt es irgendwo noch eine Option, dass Änderungen auch im Backend dann über die queue und nicht direkt über die DB laufen? Oder hat jemand noch eine andere Idee, woran es liegen könnte?

2

Beu welchem Provider bist du denn, hast du da mal mit dem Support Kontakt aufgenommen?

Eventuell mal versuchen die Tabelle per SQL zu optimieren - vielleicht passt die Indizierung durch den Import nicht mehr richtig und die Querys laufen daher zu lange:

ANALYZE TABLE `category`
OPTIMIZE TABLE `category`
3

Wir hosten selbst.

Analyze und Optimize hab ich schon gemacht …

Bringt alles nix :frowning:

Hab jetzt schon den innodb_lock_wait_timeout hochgesetzt.
innodb_lock_wait_timeout = 300

Aber dieser eine category-update Befehl läuft dann die vollen 300 Sek und bricht dann ab.

Im RabbitMQ bleibt auch diese eine Message stehen, bis ich die queue manuell lösche.

Hab den consumer auch mal auf 300 gesetzt, aber das macht auch keinen Unterschied

bin/console messenger:consume failed async low_priority --time-limit=300

Mir fällt langsam nix mehr ein … :frowning:

4

Habt ihr das Problem nur bei den Kategorien und nicht bei Artikeln?

Welche Shopware-Version nutzt ihr? Ich glaube ab 6.5 werden v7 UUIDs unterstützt, die sind für die Indizierung etwas performanter als v4. Ggf. alle Kategorien nochmal komplett neu mit UUID v7 anlegen.

Was sind denn so die Eckdaten des MySql-Servers? Wieviel freeable memory hat der im laufenden Betrieb? Mysqltuner schon gecheckt?

Die Abfragen über die Queue laufen zu lassen, verschiebt ja das Problem zeitlich nur. Eine einfache UPDATE-Abfrage sollte immer möglich sein. Für mich sieht das erstmal so aus, als ob der Mysql-Server einfach überlastet ist.

5

Die Abfrage müsste auf einem gescheiten Server eigentlich im tausendstel-Sekunden Bereich liegen. Du kannst über die URL im Admin ja die Kategorie-ID sehen (besser als der binäre Quark) und damit mal eine manuelle Update-Abfrage versuchen, ob die auch so extrem langsam ist.

6

Ganz ehrlich, wenn ihr selber hostet und das Problem nicht selber gelöst bekommt, dann solltet ihr das „selber hosten“ überdenken. Wechselt zu einem entsprechend spezialisierten Shopware-Hoster, da gibt es diese Probleme nicht - oder der Support hilft euch, das Problem in relativ kurzer Zeit zu lösen.

1 „Gefällt mir“
7

Nur bei Kategorien. Artikel (ca. 400.000) lassen sich beliebig oft speichern, ohne die DB zu belasten.

SW 6.6.6.1

Da wir sowohl die meisten Kategorien als auch die Artikel über die API importieren, verwenden wir md5 für die ids, damit sie reproduzierbar sind, wie hier beschrieben:

Wenn man sich mal anguggt, was auf der DB passiert, wenn man eine Kategorie ändert, ist das nicht nur ein Befehl, sondern über die constraints sind das gefühlt hunderte Befehle.

SHOW FULL PROCESSLIST;

Ich muss dazu sagen, dass wir über 100.000 Kategorien in 5 Sprachen haben, d.h. 5x translations-Tabellen und 5x seo_urls.

Das System ist noch nicht live, daher hab ich noch keine Zahlen. Tests hab ich nicht gemacht. mysqltuner ist auch nicht so aussagekräftig, weil ich ja immer wieder neu starte und umkonfiguriere.

Aktuell haben wir 8 CPU und 64GB RAM. Das werden wir im Live-Betrieb noch aufstocken. Aber es irritiert halt, dass bei so einem vermeintlich einfachen Update ein Deadlock auftritt.

Sobald ich die Kategorie im Backend änder und eine 1 hinzufüge, rödelt sich das System mit haufenweise update-Befehlen auf die category-Tabelle ab. Wenn ich dann in dieser Zeit in der Workbench einen einfachen Update-Befehl auf die category-Tabelle ausführe, dauert der auch ewig und bricht irgendwann ab, weil die Tabelle gelockt ist. Wenn ich denselben Update-Befehl nach einem Neustart ausführe, dauert er ein paar Millisekunden.

8

Ne, is klar. Mit einem spezialisierten Shopware-Hoster sind ALLE Shopware-Probleme behoben… hätte ich ja auch früher drauf kommen können.

Vielen Dank für den hilfreichen Beitrag.

9

Ja sowas ist mir auch schon unter gekommen, kann es sein dass das Problem schlimmer wird umso tiefer die Kategorie verschachtelt ist? Hört sich ein bisschen so an als wenn er da rekursiv den halben Kategoriebaum anfässt.

Edit:

er greift rekursiv auf alle children in dem indexer zu, zwar asynchron aber die messages müssen ja auch geschrieben werden vorab:

also klingt das so dass es schlimmer wird umso weiter oben du im baum bist - ist das so?

10

Ja, sowas in der Art habe ich mir auch gedacht.

Aber komischerweise war das jetzt eine Kategorie, die auf oberster Ebene war und keine Children hatte. Es war auch eine Kategorie, die ich manuell im Backend angelegt habe.
Ich wüsste gar nicht, was da alles geupdated werden müsste. Aber die update-Befehle rauschen da nur so durch …

Die Kategorien, die über die API angelegt wurden, haben schon 6 Ebenen.

Wobei ich bei der API das Indexieren deaktiviert habe (indexing-behavior:disable-indexing) und das dann über einen cron antriggere. Das läuft komischerweise durch über die queue.

Ob es für das manuelle Ändern im Backend auch sowas gibt wie indexing-behavior:disable-indexing?

11

Was wird denn da alles (noch) aktualisiert, ist das alles category mit derselben ID oder sind das andere Tabellen/IDs? Eigentlich sollte er doch primär category und category_translation aktualisieren. MySQL kann schon klemmen, wenn es mit Abfragen zugeschüttet und überfordert wird.

12

Also ich hab jetzt mal die Zeit gefunden, die general.log zu aktivieren und zu schauen, was da so abgeht.

Ich habe das System neu gestartet, habe nur eine Kategorie geändert und eine Zahl in die Beschreibung angefügt.

Dann hab ich gewartet, bis wieder Ruhe auf der DB herrscht und mir dann das general.log angeschaut.

grep -o "UPDATE \`category\` SET" general.log | wc -l

liefert 131.643 Zeilen. :astonished:
Also ein Update auf eine Kategorie erzeugt bei uns 131.643 update Befehle auf die Kategorie-Tabelle.

Die sehen dann so aus:

2024-10-24T14:09:08.382219Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|',`level` = '2' WHERE `id` = '^A<8e>\r£Â<92>r\" é<8c>xð<96>\nè'
2024-10-24T14:09:08.383013Z        95 Query     UPDATE `category` SET `path` = NULL,`level` = '1' WHERE `id` = '^A<8e>\r£Â}pè°FPÙ^U5ZÄ'
2024-10-24T14:09:08.383767Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|ee700af3da33af4efc8103e1891b0914|',`level` = '4' WHERE `id` = '\0\0,¯hÅ^C^Bä^E©}Såa^O'
2024-10-24T14:09:08.384469Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|faff732ffbf00f67c52b38dd49127216|ba98fb456d8a49077f483d34c6acae5c|857daad3ba2e5b2cc94fbe38e599dd11|',`level` = '6' WHERE `id` = '\0\0à\\3tËE^_»dßÖ¬\'á'
2024-10-24T14:09:08.385122Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|ee700af3da33af4efc8103e1891b0914|a028d9d789f549082abdfb7b5630d58b|33f74b7ef540e85ba1d28b450503fbb3|',`level` = '6' WHERE `id` = '\0\0å7\'uëY^Dû¸2<9a>\ZðC'
2024-10-24T14:09:08.385786Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|ee700af3da33af4efc8103e1891b0914|e6d2b6a08cd8bb285c07c5263fd32a50|3e23cf5d9e80865cd09db55ca902295f|',`level` = '6' WHERE `id` = '\0\0ç\'ô¡îÅ^XÇOÏ^O+ê?'
2024-10-24T14:09:08.386419Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|5eb91da6b0269b5073a86f7598c180b7|0bd0c6591c7824a27729e357eabdbf99|74b70e2ab7457b903ad2bf94cad1df0d|',`level` = '6' WHERE `id` = '\0^A<85>>þPÿ^A<8f>+Ç`<88>EL®'
2024-10-24T14:09:08.387151Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|ee700af3da33af4efc8103e1891b0914|eacb095ec3879ff9e030acb13ba329b8|825e7e8c0553bd694090a888b74fde95|',`level` = '6' WHERE `id` = '\0^A¹²ó<85>f^Y,&<9e>D<8f>u\\<9c>'
2024-10-24T14:09:08.387809Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|ee700af3da33af4efc8103e1891b0914|3c3cf394dff3909d3e6f8a3c2584ac7a|67ff0988e07f9ef5e0f35c9bd3c783eb|',`level` = '6' WHERE `id` = '\0^B5Ü^<99>±á¤è<8b>ètï[ø'
2024-10-24T14:09:08.388442Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|0d48d86e82a6faad8ef1b3d3d345919d|a437f323900214cfc9ab619f94a5bc78|ca09fcf1b33b23865c935042b4580b70|',`level` = '6' WHERE `id` = '\0^BLÍÌ¢^B\n:jWo´,·<96>'
2024-10-24T14:09:08.389069Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|78997e99cf10568a7bb0866e5f77162c|e6465133f4d95dd8ba99b9a28db5f89b|97788426a608b3c150282dfbe29513c0|',`level` = '6' WHERE `id` = '\0^BªØBÝÊÞ^D¹ø4^Yé;/'
2024-10-24T14:09:08.389700Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|88b98f26a6f20515d5e3a13f420fc273|8f7af6d73061ea7122910164cd8387ba|60d341075c590be5049d68ca70a65a92|',`level` = '6' WHERE `id` = '\0^Bé8C{ò<87><91>ÞãÉ^BJqÖ'
2024-10-24T14:09:08.390320Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|faff732ffbf00f67c52b38dd49127216|cc04be69fb4a950547151350081848fb|4371f1da8880c5152b6c6e84f3d0202b|',`level` = '6' WHERE `id` = '\0^C-V^_á¼^_¿^AÇ^CÀt¸.'
2024-10-24T14:09:08.391108Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|faff732ffbf00f67c52b38dd49127216|92019cc4e925e3eb133385fbd08a3111|',`level` = '5' WHERE `id` = '\0^C¾<83><8e>µ§s5Ð^Hª<83>Í<99><88>'
2024-10-24T14:09:08.391737Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|faff732ffbf00f67c52b38dd49127216|40018c04db3d8cf6aa329aca99331e47|eebe0e9b3c07961f1f82efa8b93bec7e|',`level` = '6' WHERE `id` = '\0^Cë©f^D EyÎ<8a>/ºC^EÜ'
2024-10-24T14:09:08.392485Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|ee700af3da33af4efc8103e1891b0914|a028d9d789f549082abdfb7b5630d58b|dd24528e87290398e19b689a83b2cdaa|',`level` = '6' WHERE `id` = '\0^C÷y<93>kà|jl·«JùeO'
2024-10-24T14:09:08.393123Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|78997e99cf10568a7bb0866e5f77162c|c91248841c135ff93da5c72da987a9ed|05cce106a60dd42e216fa87a4445830d|',`level` = '6' WHERE `id` = '\0^D^G^HQZsò¶Ü¶Z^N<9e>Ô<9f>'
2024-10-24T14:09:08.393749Z        95 Query     UPDATE `category` SET `path` = '|018e0da3c27d70e8b04650d915355ac4|018e7f86357478a585a16424315b2db5|07f0e0498e7d7de2fd0972dbf879c226|0cac48aa68362b17575d9719a84c7c12|82c1e078a85cbd45d135bb1b0475f119|',`level` = '6' WHERE `id` = '\0^D/ ªv³ãäoÝr^V:Ê9'

Scheinbar wird dann bei ALLEN Kategorien der Pfad aktualisiert. Warum?

Anschließend wird noch überall die Übersetzung aktualisiert:

grep -o "INSERT INTO \`category_translation\`" general.log | wc -l

Das liefert freundliche 657.240 Zeilen. Das sind offensichtlich alle Kategorien in 5 Sprachen.

2024-10-24T14:12:04.963948Z        95 Query     INSERT INTO `category_translation` (`category_id`, `category_version_id`, `language_id`, `breadcrumb`, `created_at`)
            VALUES ('^A<8e>\r£Â<92>r\" é<8c>xð<96>\nè', '^O©^\ãéjK¾KÙÎu,4%', '^A<8e>\n^V+<92>q6·ô<8a><84>ÿé^FR', '...', DATE(NOW()))
            ON DUPLICATE KEY UPDATE `breadcrumb` = '...'

Das Ganze erzeugt ca. 4.000 insert-update-Befehle pro Sekunde auf eine Tabelle, die 657.365 Zeilen hat.

Dann gibt es noch eine Palette solcher Befehle:

2024-10-24T14:18:30.308045Z       456 Query     UPDATE `category` SET `version_id` = '^O©^\ãéjK¾KÙÎu,4%', `parent_id` = '^¹^]¦°&<9b>Ps¨ou<98>Á<80>·', `parent_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `after_category_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `media_id` = '<80>^UpJ^í%òVÝq<91>ëñ<8e>^Y', `cms_page_id` = '^A<8e>`|©(x^R<89><81>áäì^VÅ¿', `cms_page_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `display_nested_products` = '0', `type` = 'page', `product_assignment_type` = 'product', `visible` = '1', `updated_at` = '2024-10-24 14:15:30.522' WHERE id = '^KÐÆY^\x$¢w)ãW꽿<99>' AND version_id = '^O©^\ãéjK¾KÙÎu,4%'
2024-10-24T14:18:30.309095Z       456 Query     UPDATE `category` SET `version_id` = '^O©^\ãéjK¾KÙÎu,4%', `parent_id` = '^KÐÆY^\x$¢w)ãW꽿<99>', `parent_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `after_category_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `media_id` = '<80>^UpJ^í%òVÝq<91>ëñ<8e>^Y', `cms_page_id` = '^A<8e>`|©(x^R<89><81>áäì^VÅ¿', `cms_page_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `display_nested_products` = '0', `type` = 'page', `product_assignment_type` = 'product', `visible` = '1', `updated_at` = '2024-10-24 14:15:30.531' WHERE id = 'NS<90>Ë<8e>^[Pü^]å5^FÛi¬+' AND version_id = '^O©^\ãéjK¾KÙÎu,4%'
2024-10-24T14:18:30.310067Z       456 Query     UPDATE `category` SET `version_id` = '^O©^\ãéjK¾KÙÎu,4%', `parent_id` = 'NS<90>Ë<8e>^[Pü^]å5^FÛi¬+', `parent_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `after_category_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `cms_page_id` = '^A<8e>ðZôÜr2 ^^^G<91>¨õgp', `cms_page_version_id` = '^O©^\ãéjK¾KÙÎu,4%', `display_nested_products` = '1', `type` = 'page', `product_assignment_type` = 'product', `visible` = '1', `updated_at` = '2024-10-24 14:15:30.540' WHERE id = '<9f>^D¯^X<89><83>xH§u©&^Bhæ+' AND version_id = '^O©^\ãéjK¾KÙÎu,4%'

Da wird dann scheinbar ALLES nochmal aktualisiert.

Dann gehts weiter mit tausenden Updates auf die custom_fields

2024-10-24T14:18:30.320325Z       456 Query     UPDATE `category_translation` SET custom_fields = JSON_SET(IFNULL(`custom_fields`, "{}"), '....) WHERE (`category_id` = '^¹^]¦°&<9b>Ps¨ou<98>Á<80>·') AND (`language_id` = '/»_ââ<9a>MpªXTÎ|ãâ^K') AND (`category_version_id` = '^O©^\ãéjK¾KÙÎu,4%')

Also brauch ich mich eigentlich nicht wundern, warum die DB in die Knie geht. :face_with_raised_eyebrow:

Aber ich frag mich echt, warum man das nicht auf die queue auslagert? Wenn ich über die API Kategorien update, kann ich es ja auch einstellen, dass es über die queue indexiert wird.

Weiß jemand, ob man das bei manuellen Updates auf Kategorien auch irgendwie einstellen kann? In der shopware.yaml vielleicht?