Hallo, schon seit eingier Zeit haben wir Probleme mit unserem Shop dass dieser manchmal sehr langsam ist und auch Fehlermeldungen erscheinen. Unser Provider hat uns nun mitgeteilt dass einige SQL-Queries um 5000 sekunden und mehr laufen. Das beeinträchtigt die Serverleistung deutlich. Es wurde auch eine Liste der Prozesse mitgeschickt: \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5337 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 5385 State: Copying to tmp table Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '129' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5352 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 5224 State: Copying to tmp table Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '129' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5359 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 5137 State: Copying to tmp table Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '129' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5378 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 4888 State: Copying to tmp table Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '129' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 5. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5381 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 4864 State: Copying to tmp table Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '129' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 6. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5390 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 4814 State: storing result in query cache Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '258' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 7. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5392 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 4786 State: storing result in query cache Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '258' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 8. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5393 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 4783 State: storing result in query cache Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '258' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 9. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5403 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 4737 State: storing result in query cache Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '258' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 10. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5445 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 4598 State: storing result in query cache Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '387' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 11. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5558 User: web1db1 Host: localhost db: db\_web1\_1 Command: Query Time: 2910 State: storing result in query cache Info: SELECT a.id AS id, REPLACE(ROUND(ap.price \* (100+t.tax) / 100, 2), '.', ',') AS grossFee, ROUND(ap.price, 2) AS netFee, aa.attr19 AS protectedAmountDecimal, aa.attr20 AS protectionDurationInt, ad.ordernumber AS tsProductID FROM s\_articles AS a INNER JOIN s\_articles\_attributes AS aa ON a.id = aa.articleID INNER JOIN s\_articles\_details AS ad ON a.id = ad.articleID INNER JOIN s\_core\_tax AS t ON t.id = a.taxID INNER JOIN s\_articles\_prices AS ap ON a.id = ap.articleID AND ap.pricegroup = 'EK' AND ap.from = 1 WHERE CAST(aa.attr19 AS SIGNED) \>= '375' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 12. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Id: 5852 User: debian-sys-maint Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: SHOW FULL PROCESSLIST Sind diese Prozesse wirklich nötig oder liegt wirklich ein Fehler vor? Wie kann ich diese Prozesse bei Bedarf unterbinden? Meine Suche bei Google ergab nur einen Hinweis auf das Plugin von Trusted Shops, den angegebenen Code habe ich in unserer Version des Plugins allerdings nicht gefunden. Verwendet wird Shopware 4.3.6. Vielen Dank. Thomas
Diese queries werden definitiv von einem Plugin erzeugt. Hier müsstest du mal schauen, welches Plugin das verursacht. WHERE CAST(aa.attr19 AS SIGNED) \>= '129' AND ad.ordernumber LIKE '%EUR' ORDER BY CAST(aa.attr19 AS SIGNED) ASC LIMIT 1 Die Abfragen brauchen übrigens so lange, weil du in deiner ORDER einen CAST() ausführst und mysql daher nichts cachen kann sondern -jeden- Datensatz abarbeiten muss. Hinzu kommt, dass auf deinen Attributen wahrscheinlich kein index sitzt. Du kannst ja spaßeshalber mal diesen Teil weglassen und die Abfrage via phpmyadmin ausführen. Viele Grüße
Sieht nach demselben Problem aus, welches ich letztens bei einem Kunden hatte: Bei mir war es das Trusted Shops Plugin, welches seitdem auch nicht mehr in Gebrauch ist.
Hallo zusammen, das Ticket: PT-3389 Trusted Shops Plugin is blocking cart ist schon in Bearbeitung: http://jira.shopware.de/?ticket=PT-3389 Gruß Heiner
Also hat es aller Voraussicht nach doch etwas mit Trusted Shops zu tun? Eine Deaktivierung dieses Plugins ist für uns aber leider keine Option. Kann man denn schon sagen wann es eine Lösung geben wird? Bestellungen gehen aber wunderbar durch, nur manchmal braucht der Shop länger zum Laden von Kategorien oder Artikeln.
Nein, in dem Ticket geht’s um den Query im Checkout. Auf der Kategorie/Detailseite macht das TS-Plugin nichts. Gruß Heiner
Dann muss unser Problem an was anderem liegen. [quote] Hinzu kommt, dass auf deinen Attributen wahrscheinlich kein index sitzt. [/quote] Wie kann ich das verstehen?
Hi, das bedeutet, dass der Fix ein MySQL-Index auf die Attr.-Spalte ist: CREATE INDEX attr19 ON s_articles_attributes (attr19); Diesen Query könntest du z.B. im PhpMyAdmin ausführen und der obige SQL-Query ist wieder schnell. Außerdem muss dann noch das “CAST(… AS SIGNED)” weg, damit der Index überhaupt funktioniert. Gruß Heiner
Und wie kriege ich dieses „CAST(… AS SIGNED)“ weg? Sorry für die vielleicht doofe Frage, ich arbeite noch nicht so lange mit Shopware. Ich bilde mir auch ein dass die ganzen Probleme erst seit dem Update auf Shopware 4.3.6 vermehrt auftreten, zumindest sind mir in unserer alten Version nicht solche Fehler aufgefallen.