Shop erzeugt eine enorme Last auf MySQL

[XXXXXX]

Hallo, lt. Aussage unseres Providers erzeugt der Shop eine enorme Last auf MySQL so dass ab und zu sogar der Server mal abschmiert. Kann man da irgendwie gegensteuern? Die Daten des Servers sind unten im Footer des Threads angegeben. Gibt es eine spezielle Konfiguration, wie der Server für Shopware konfiguriert werden muss/sollte? Falls ja, woher kann ich diese beziehen damit ich meinen Provider mit der Konfig beauftrage. Haben andere auch teilweise eine enorme Auslstung des Systems aufgrund der Version SW4.0.x? Bei Shopware 3.5 lief alles bestens. Viele Grüße Rainer

[datema]

Ja, bei uns auch.

[icebreaker]

Das Problem hatte ich auch. SW4 braucht eine schnelle Datenbank, das erreichst Du nur durch viel Ram-Zuweisung an die Datenbank. Wenn Du zuviel Ram vergibst (das problem hatte ich) schmiert der Server gerne mal ab. Du solltest die Konfiguration der DB überarbeiten um das Speichermanagement zu verbessern, dann läuft das ganze stabiler. Empfehlen kann ich auch den MySQL-Tuner: allgemein-f25/performance-verbessern-bei-4-0-4-t9742-10.html?hilit=mysqltuner#p47676 (Mit bestem Dank an Benjamin Cremer), das Tool gibt dir nette Tipps zum optimieren. Gruß Micha PS: Ich hab 2.000 Kategorien und 20.000 Artikel und die Kiste läuft

[api]

Hi, wenn deinem Provider die Datenbank mit Shopware abraucht, kann es einfach sein, das er zuwenig Ressourcen bereitstellt. Das passiert meistens bei günstigen Webspace Paketen, wo du dir den Server mit 100 anderen Kunden teilst. Du kannst dir auch einen Slowquery Log besorgen, dann siehst du die Queries wo zu langsam sind und kannst im Shop optimieren, wie bereits vom Vorposter angesprochen mit MySQL Tuner.

[XXXXXX]

@ api Es ist ein dedizierter Server. Ein Techniker ist damit beauftrag die Konfiguration zu verbessern. Mal abwarten.

[api]

Ich sehe du verwendest Plesk, wenn das dein Shopsystem ist Plesk greift relativ tief in dein System ein, wesentlich performanter fährst du da mit LiveConfig. Bei MySQL kannst du neben dem SlowQuery Log, noch prüfen ob evtl. Percona als MySQL Server eine Alternative ist. Wenn dir die Performance dann immernoch nicht ausreicht, lass dir eine SSD mit hoher IOPS Zahl einbauen. Da reicht eine Consumer MLC, mit RAID 1 fährst du dann ganz gut. Edit: Du kannst in PHPMyAdmin auf der Status Seite sehen wo es etwas hängt, sprich wo MySQL zu langsam ist, dann gezielt die Parameter optimieren. Bei deinem Fall geh ich daher von einer nicht „so gut“ angepassten Konfiguration aus.

[XXXXXX]

ass dir eine SSD mit hoher IOPS Zahl einbauen[/quote] Da muss ich wirklich überlegen, ob ein anderes Shopsystem nicht besser wäre. Hier ein Auszug vom Provider , ganz aktuell. Übrigens, der Shop läuft nach hochfahren des Server nicht lang (keine Stunde), dann ist er Down. Provider -Zitat: [quote]Hier laufen Queries die offenbar niemals fertig werden. Im Beispiel oben schon über 800 Sekunden[/quote] Auszug aus dem Protokoll: [quote] Key Efficiency: 80.0% Bps in/out: 0.1/ 7.3 Id User Host/IP DB Time Cmd Query or State – ---- ------- – ---- — -------------- 989 admin localhost 0 Query show full processlist 962 Ausr localhost A_sw 6 Query SELECT a.id as articleID, d.id AS articleDetailsID, d.ordernumber, datum, sales, topseller as highlight, a.description, a. description_long, s.name AS supplierName, s.img AS 965 Ausr localhost A_sw 6 Query SELECT a.id as articleID, d.id AS articleDetailsID, d.ordernumber, datum, sales, topseller as highlight, a.description, a. description_long, s.name AS supplierName, s.img AS 967 Ausr localhost A_sw 6 Query SELECT a.id as articleID, d.id AS articleDetailsID, d.ordernumber, datum, sales, topseller as highlight, a.description, a. description_long, s.name AS supplierName, s.img AS 968 Ausr localhost A_sw 6 Query SELECT a.id as articleID, d.id AS articleDetailsID, d.ordernumber, datum, sales, topseller as highlight, a.description, a. description_long, s.name AS supplierName, s.img AS 969 Ausr localhost A_sw 6 Query SELECT COUNT(*) FROM s_order_notes n, s_articles a WHERE (sUniqueID=‚f8d2b1cdb51e267bd58de5dc2e55c23b005824c6‘ OR (userID!=0 AND userID = ‚0‘)) AND a.id = n.articleID AND a. 972 Ausr localhost A_sw 6 Query SELECT s.id AS id, COUNT(DISTINCT a.id) AS countSuppliers, s.name AS name, s.img AS image FROM s_categories c JOIN s_categories c2 ON c2.active=1 AND c2.left >= c.left AND c 975 Ausr localhost A_sw 6 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 976 Ausr localhost A_sw 6 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 977 Ausr localhost A_sw 6 Query SELECT s.id AS id, COUNT(DISTINCT a.id) AS countSuppliers, s.name AS name, s.img AS image FROM s_categories c JOIN s_categories c2 ON c2.active=1 AND c2.left >= c.left AND c 978 Ausr localhost A_sw 6 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 979 Ausr localhost A_sw 6 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 982 Ausr localhost A_sw 6 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 983 Ausr localhost A_sw 6 Query SELECT s.id AS id, COUNT(DISTINCT a.id) AS countSuppliers, s.name AS name, s.img AS image FROM s_categories c JOIN s_categories c2 ON c2.active=1 AND c2.left >= c.left AND c 984 Ausr localhost A_sw 6 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 985 Ausr localhost A_sw 6 Query SELECT s.id AS id, COUNT(DISTINCT a.id) AS countSuppliers, s.name AS name, s.img AS image FROM s_categories c JOIN s_categories c2 ON c2.active=1 AND c2.left >= c.left AND c 986 Ausr localhost A_sw 6 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 966 Ausr localhost A_sw 73 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 959 Ausr localhost A_sw 76 Query SELECT a.id as articleID, a.name as articleName, COUNT(r.articleID) as relevance FROM s_categories c, s_categories c2, s_articles_categories ac, s_articles a LEFT JOIN s_ema 938 Ausr localhost A_sw 181 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 907 Ausr localhost A_sw 317 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 885 Ausr localhost A_sw 418 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 852 Ausr localhost A_sw 625 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 842 Ausr localhost A_sw 701 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va 830 Ausr localhost A_sw 762 Query SELECT fv.optionID AS id, COUNT(DISTINCT a.id) AS count, fo.id AS optionID, fo.name AS optionName, f.id AS groupID, f.name AS groupName, fv.value AS optionValue, fv.id AS va [/quote] my.cfn [quote]# # The MySQL database server configuration file. # # You can copy this to one of: # - „/etc/mysql/my.cnf“ to set global options, # - „~/.my.cnf“ to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/serve … ables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain „#“ chars… # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] local-infile=0 # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. # bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP max_connections = 500 table_cache = 512 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI „tinyca“. # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 128M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with ‚.cnf‘, otherwise they’ll be ignored. # !includedir /etc/mysql/conf.d/

[XXXXXX]

sieh auch im Bugtracker …

[Litronics2000]

Da muß Shopware ran und das Backend mal komplett überarbeiten! Die SQL-Querries, die da abgegeben werden, sind absolut suboptimal. Warum muß der SQL-Server innerhalb einer Abfrage ständig einen TRIMM-Befehl ausfürhen? Wäre es da nicht sinnvoller den Wert zu trimmen, wenn er in die DB geschrieben wird und nicht jedes Mal, wenn er ausgelesen wird? Von solchen Dingen gibt es extrem viele im Code und da muß dringend was dagegen gemacht werden. Nur mal so zum schmunzeln. Wenn wir einen Artikel in den Warenkorb legen, dann dauert die komplette Abhandlung des SSL-Teils nur etwa 0,2 Sekunden. Dann braucht der Shop weitere 4 Sekunden um zu antworten. In der Zeit feuert er Höllenabfragen auf den SQL-Server ab. Aber warum - ein einfaches “Insert artikel Into Warenkorb” würde doch reichen und daurert normalerweise nur 0,002 Sekunden. Hier also der DRINGENDE Aufruf an die Programmierer bei Shopware - überarbeitet die SQL-Querries, damit die Shops performanter werden!

[SebastianKloepper]

Hallo litronics2000, so wie das aussieht kommt das bei dir nicht von Shopware direkt bzw. aus dem Standard. Da muss eine Erweiterung oder Plugin aktiv sein, welches dort eingreift. Standardmäßig kann das überhaupt nicht diese Ladezeiten haben. Hast du bei uns dazu ein Ticket im Support aufgemacht? Dann prüfen wir das morgen und geben dir dann Feedback.

[Litronics2000]

Habe ich vorher schon gemacht. Bin dann schon mal gespannt was dabei rauskommt.

[XXXXXX]

Hallo, schaut mal das Plugin Register Spam Protection für SW4 an. Seit dem ich es deaktiviert habe, läuft der Shop wieder flüssig. In der my.cnf habe ich auch Werte geändert (erhöht). Edit: Shop schon wieder nicht erreichbar! Gruß Rainer

[api]

Wenn das an einem Plugin liegt, musst du prüfen was du als letztes installiert hast, bzw. seit wann die Probleme auftreten, ggf. hier Rollback. Dann kannst du die von mir vorgeschlagenen Lösungen eh vergessen, bin davon ausgegangen das du entsprechend Traffic auf der Seite hast. Lass dir mal den SlowQuery Log einrichten und poste was da drinnen steht, setzte den Wert vom Query auf 3 Sekunden.

[Heiner_Lohaus]

Hi rascob, wir haben uns jetzt die problematischen MySQL-Queries bei dir einmal angeschaut. An der reinen Performance der Queries kann es nicht liegen, da diese bei dir schon relativ schnell ausgeführt werden. Die werden bei dir zwar von MySQL nicht richtig optimiert, dass liegt aber nur an der geringen Artikel/Kategorie-Anzahl. Das Problem ist das die Queries manchmal bei dir nicht richtig abgeschlossen werden. Siehe Anhang. Und das kommt wahrscheinlich durch eine falsche MySQL-Server Konfiguration zustande. Dazu solltest du daher einmal deinen Hoster befragen. Heiner

[XXXXXX]

Vielen Dank für die Rückmeldung. Gruß Rainer

[Litronics2000]

[quote=“Heiner Lohaus”]Hi rascob, wir haben uns jetzt die problematischen MySQL-Queries bei dir einmal angeschaut. An der reinen Performance der Queries kann es nicht liegen, da diese bei dir schon relativ schnell ausgeführt werden. Die werden bei dir zwar von MySQL nicht richtig optimiert, dass liegt aber nur aus der geringen Artikel/Kategorie-Anzahl. Das Problem ist das die Queries manchmal bei dir nicht richtig abgeschlossen werden. Siehe Anhang. Und das kommt wahrscheinlich durch eine falsche MySQL-Server Konfiguration zustande. Dazu solltest du daher einmal deinen Hoster befragen. Heiner[/quote] Hallo Heiner, danke für das Reinschauen - wir haben auch das Plugin von Sofortüberweisung als einen Leistungsfresser identifiziert! Das ist absolut nicht verwendbar und nachdem wir es deaktiviert hatten, ging es deutlich schneller. Das mit den Wenigen Produkten und Kategorien kann ich so nicht stehen lassen. Wir haben gut knapp 25.000 Artikel mit etwa 1500 Kategorien im Shop - das ist keine kleine Anzahl. Auch verstehe ich nicht, wie ein SQL-Server eine Abfrage optimieren soll? Der fürht lediglich die Abfragen der Software aus und nimmt dafür die Indizes und Daten her, die zur Verfügung stehen. Dann wäre auch noch zu klären, warum Abfragen nicht zu ende geführt werden. Da kann doch die Konfituration (außer vielleicht ein Timeout, wenn eine Anfrage zu lange läuft) des Servers nicht’s helfen. Wenn die Anfrage keine Ergebnisse findet oder den Server in eine Schleife jagt, was soll da der Server dagegen machen? Schöne Grüße Stefan

[Heiner_Lohaus]

Hi Stefan, die Antwort bezog sich auch nur auf Rainers-Problem. Soll ich mir das bei dir auch einmal anschauen? Bei dir sollten die Anfragen natürlich richtig optimiert werden. Der MySQL-Optimizer ist übrigens dafür das der Server erst die großen Tabellen und dann die kleinen / Filter-Tabellen “JOINT”. Daher hat das bei Rainer auch nicht funktioniert. Optimal wäre übrigens wenn MySQL die Tabellen so “JOINEN” würde, wie das in den Shopware-Quries vorgeben ist. Das macht aber MySQL nicht immer automatisch richtig. Heiner

[Litronics2000]

Hallo Hainer, oh sorry - da hab ich mich wohl dazwischengedrängelt. Aber ja - wenn Du Dir das mal anschauen könntest, wäre super! Schöne Grüße Stefan

[XXXXXX]

@ Heiner [quote] falsche MySQL-Server Konfiguration[/quote] Habt Ihr eine MySQL Konfiguration für den Shop? Rainer

[Heiner_Lohaus]

Hi, @Rainer: Nein, leider nicht. Wir haben damit auch nicht viel Erfahrung (Also Server-Konfiguration). Aber ich sehe du hast eine sehr alte MySQL-Server-Version am Laufen. Vielleicht hilft hier auch einfach ein Update auf eine neuere Version? @Stefan: Ich sehe gerade dein Shop läuft noch auf Shopware 3.5. Auch hier sollte mal ein Update auf Shopware 4 überprüft werden. Die Kategorie/Filter-Queries wurden da nämlich erheblich angepasst / verbessert und ohne Update können die auch nicht einfach eingebaut werden. Heiner