Neu Shop4 / MySQL 5.7 / Apache2

[DITH-Shop]

Moin,
ich bin auf der Suche nach weiteren Optimierungsmöglichkeiten, da das ganze Gebilde doch recht schleppend läuft (Insbesondere Shop-Synchro mit der Wawi)
Bei einem Shop-Abgleich werden bei mir gerade einmal 1,7Artikel/Sek. geupdatet.
Disk-IO Probleme kann ich inzwischen ausschließen.

Durch viel Testen habe ich festgestellt, das MySQL augenscheinlich bei der Synchro nur 2 der 16 Cores benutzt. Die CPU-Last ist daher statt bei max 1600% nur bei max 180 %
(Per MySQL Admin ist zu erkennen das in der Tat vom SHop aus nur maximal 2 Verbindungen aufgebaut werden)

Das scheint daran zu liegen das zu wenige Connections zwischen Shop4 (Apache2 mit PHP7 MPM Worker mit PHP-FPM) / MySQL 5.7 aufgebaut werden
(laut INet nutzt MySQL je Connection nur 1 Core)

Wo muss/kann ich "schrauben" damit mehr Verbindungen aufgebaut werden?

Jemand eine Idee?

 

[css-umsetzung]

Verstehe ich jetzt nicht.
Der shop wird aufgerufen, macht einen connect zur DB, abeitet sein script bis ende durch, fertig.

Eventuell ist auch einfach nur dein Server lahm?

 

[DITH-Shop]

Moin,
ich bin auf der Suche nach weiteren Optimierungsmöglichkeiten, da das ganze Gebilde doch recht schleppend läuft (Insbesondere Shop-Synchro mit der Wawi)
Bei einem Shop-Abgleich werden bei mir gerade einmal 1,7Artikel/Sek. geupdatet.
Disk-IO Probleme kann ich inzwischen ausschließen.

Durch viel Testen habe ich festgestellt, das MySQL augenscheinlich bei der Synchro nur 2 der 16 Cores benutzt. Die CPU-Last ist daher statt bei max 1600% nur bei max 180 %
(Per MySQL Admin ist zu erkennen das in der Tat vom SHop aus nur maximal 2 Verbindungen aufgebaut werden)

Das scheint daran zu liegen das zu wenige Connections zwischen Shop4 (Apache2 mit PHP7 MPM Worker mit PHP-FPM) / MySQL 5.7 aufgebaut werden
(laut INet nutzt MySQL je Connection nur 1 Core)

Wo muss/kann ich "schrauben" damit mehr Verbindungen aufgebaut werden?

Jemand eine Idee?

Echt jetzt? Niemand eine (sinnvolle) Idee?

Durch etliche Optimierungen am IO und MySQL habe ich es jetzt so weit das die CPU Last bei ~250% liegt, IO gerade mal bei 1-3MB/sec. (Kapazität laut Benchmark knapp read 300 / write 260MB/sec - IO_DIRECT), es werden jedoch weiterhin maximal 3 Verbindungen zur DB aufgebaut.
Es muss doch irgendwo einstellbar sein, das mehr Verbindungen aufgebaut werden, damit mehr parallele SQL ausgeführt werden können?

Zu der Aussage "Server lahm" :
HP ProLiant DL380 G7, 2x Xeon X5650 3,2 GHz, RAID5 (3x SSD) 512GB, 64GB RAM

Da auf dem Server nur der Apache2 und die DB läuft sollte DER ja wohl kaum Probleme machen.
Gesamt CPU Last liegt bei 3-15%

 

[fav-hosting.online]

Der Abgleich wird nicht mehr als eine Verbindung verwenden, kann er ja auch nicht.
JTL packt für den Transfer die Daten in eine ZIP-Datei und sendet diese an den Shop.
Der Shop entpackt diese Datei und arbeitet den Inhalt der Reihe nach ab es gibt keine Simultanen Zugriffe in diesem Fall.
Anders wäre es z.B. wenn beim Shopabgleich die Wawi statt immer z.B. 20 Artikel nacheinander 5 x 20 Artikel versenden würde.
Dann würden 5 Prozesse gleichzeitig laufen und auch Simultan bearbeitet werden. Das wird aber wahrscheinlich nicht so leicht umzusetzen sein.
Einen Kindartikel vor dem Vaterartikel zu senden kann z.B. zu eventuell Abhängigkeitsproblemen führen.

Wenn du mir sagst wie ein normaler Artikel bei die aufgebaut ist kann ich gerne mal testweise einen ähnlichen Abgleich ausführen und messen wie lange dieser dauert.

 

[css-umsetzung]

Genau so ist es es startet wie ich schon sagte, ein Script, das macht einen connect und arbeitet über diesen, den ganzen geforderten Prozess ab.
Ich hab meine Datenbank noch nicht angeschaut bei einem Abgleich aber wenn die auf 180% geht, dann würde ich mir sorgen machen?

Da ich einen Windowserver im Netz habe sehe ich ja direkt wie schnell sogar ein komplett Abgleich laufen kann, ich habe da nicht das Gefühl das mein Linuxserver wo dann alles landet, Stress hat. Der Abgleich flutscht schön schnell durch wenn alle Komponenten vernünftig eingestellt sind.

Ich hab das mal kurz durchgespielt, windows Server im Netz: 270 Artikel mit Bilder 70 Kategorien, dann hab ich den Abgleich zum Shop gestartet der auf einem Linux Server liegt

der ganze Abgleich hat keine 5 Minuten gedauert.

der Linuxserver hatte einen entspannten Severload von 0.03
MySQL hatte eine prozentuale Auslastung von ganzen 13%

der Linux Server hat kein SSD und 32G RAM und einen Intel(R) Xeon(R) CPU E3-1270 v3 @ 3.50GHz
der Windows Server hat SSD und 16G RAM und einen Intel(R) Xeon(R) CPU E3-1230 v3 @ 3.30GHz

Beide Rechner haben sich dabei gelangweilt.

 

[DITH-Shop]

Der Abgleich wird nicht mehr als eine Verbindung verwenden, kann er ja auch nicht.
JTL packt für den Transfer die Daten in eine ZIP-Datei und sendet diese an den Shop.
Der Shop entpackt diese Datei und arbeitet den Inhalt der Reihe nach ab es gibt keine Simultanen Zugriffe in diesem Fall.
Anders wäre es z.B. wenn beim Shopabgleich die Wawi statt immer z.B. 20 Artikel nacheinander 5 x 20 Artikel versenden würde.
Dann würden 5 Prozesse gleichzeitig laufen und auch Simultan bearbeitet werden. Das wird aber wahrscheinlich nicht so leicht umzusetzen sein.
Einen Kindartikel vor dem Vaterartikel zu senden kann z.B. zu eventuell Abhängigkeitsproblemen führen.

Wenn du mir sagst wie ein normaler Artikel bei die aufgebaut ist kann ich gerne mal testweise einen ähnlichen Abgleich ausführen und messen wie lange dieser dauert.

Dann kann ich die Angaben von maydo nicht nachvollziehen. 2.000 Artikel (NICHT QuickSync) in 10 Sekunden...

Kind/Vaterartikel gibt es bei mir nicht.
Wie ein Artikel aufgebaut ist? Äh... Hä?
Beschreibung; Bild, Preis usw.. Oder was meinst Du?
Ich kann drehen wo und was ich will, ich komme nicht über 1,8 Artikel/Sek. beim Synch

 

[x86]

Mich würde interessieren wie du auf 1,7 Sec / Artikel kommst, da mysqladmin nicht zum Tracing von SQL Anweisungen geeignet ist.
Wenn du dennoch der Annahme bist, dass der Webserver o. deine MySQL DB für einen Artikel so exorbitant viel Zeit benötigt dann empfehle ich dir deine my.cnf
bei Diensten wie Percona für deine Zwecke zuschneiden zu lassen.

Zu den Prozessoren:
Da hast du etwas falsch verstanden, MySQL ist bereits eine Anwendung (ob Unix / Windows spielt keine Rolle) die multi-threading fähig ist und somit
kümmert sich der jeweilige Linux Kernel selbst um die Verteilung der Threads auf die verschiedenen Cores. Du kannst dieses Verhalten nur beschränkt für die Datenbank
Engine InnoDB beeinflussen durch das maximale setzen oder nicht setzen von thread_limits. Da aber der JTL Shop 4 (ebenso Version 3) überwiegend auf MyISAM setzt,
kann ich nicht sagen inwiefern die Einstellung hier greift.

Zu den Verbindungen:
Es wird bei einem Web-Request über die NiceDB Klasse eine statische variable mit einer Connection geöffnet, diese wird beim __deconstruct der Klasse wieder freigegeben.
Da immer auf diese bereits laufende Instanz zurückgegriffen wird, öffnet der Shop pro Aufruf genau einen Datenbankhandle / eine Datenbankverbindung.
Die Anzahl der maximalen Connections kannst du mit max_connections hochsetzen.

Zur MySQL Datenbank:
Mit welchen Workbench-Tools arbeitest du den (MySQLTuner, der Workbench direkt, Performance Schema)?
Welche Optimierungen hast du durchgeführt? Ohne solchen Informationen kann man hier schlecht helfen.
Spontan würde ich vorschlagen auf MariaDB zu wechseln, Migration zu MySQL 5.7 dürfte zu 10.1 keine Probleme darstellen.

 

[DITH-Shop]

Mich würde interessieren wie du auf 1,7 Sec / Artikel kommst,...

Jeder beliebige Taschenrechner kann das.

Ich lasse Pakete zu 600 Artikel von der Wawi erzeugen und an den Shop zum Abgleich senden.
Das zusammenpacken dauert ~2-3 Sekunden, das übertragen <1Sek.
a) kann ich im Dateisystem sehen das sich die Anzahl im Verzeichnis je Sekunde um die genannte Zahl verringert
b) kann ich aus (durchschnittlich) 6,5 Minuten / 600 Artikel selbst errechnen wie viele da in der Sekunde bearbeitet wurden.

Mein Shop läuft übrigens zum Großteil NICHT auf MyISAM sondern auf InnoDB

...Zu den Verbindungen:
Es wird bei einem Web-Request über die NiceDB Klasse eine statische variable mit einer Connection geöffnet, diese wird beim __deconstruct der Klasse wieder freigegeben.
Da immer auf diese bereits laufende Instanz zurückgegriffen wird,...

und genau da scheint das Problem zu liegen.

Ich kann übrigens sehr wohl zwischen Connections und Threads unterscheiden.
Wenn dem nicht so wäre, wären meine 9 Trimester Informatik in Heidelberg (Hardwarenahe Programmierung und Netzwerktechnik als Hauptbereiche) dann doch umsonst gewesen. Gut, ist schon ein paar Jahre her, aber so viel hat sich nun nicht verändert.

 

[DITH-Shop]

Aber:
Wir können das Thema auch gerne schließen. Für die zwei Monate macht es keinen Sinn sich noch groß Gedanken zu machen.

 

[css-umsetzung]

Warum läuft das auf InnoDB, es gibt eigentlich nur drei oder 4 Tabellen die per default auf InnoDB stehen.

Theoretisch heißt es, dass InnoDB schneller ist, aber wenn ich einen Import von größeren Daten über die Shell mache, muss ich vorher richtig Action machen damit die Daten nicht 4 tage brauchen um importiert zu werden, weil hier das InnoDB mit seinen Möglichkeiten den Import extrem verlangsamt.

für mich wäre MariaDB keine Lösung, da ich bereits erfahren durfte, dass MariaDB "noch" nicht alles kann was MySQL kann, speziell wenn es um groups subselects und Sortierungen geht.

Wenn dem nicht so wäre, wären meine 9 Trimester Informatik in Heidelberg (Hardwarenahe Programmierung und Netzwerktechnik als Hauptbereiche) dann doch umsonst gewesen. Gut, ist schon ein paar Jahre her, aber so viel hat sich nun nicht verändert.

scheint ja so zu sein?

 

[x86]

Die Standard Engine ist MyISAM, wie du der initial_schema.sql entnehmen kannst was auch
durchaus Sinn macht:
https://gitlabci.jtl-software.de/jtlshop/shop4/blob/master/install/initial_schema.sql

Web-Requests:
Es ist kein Problem immer auf die gleiche Instanz und damit Connection zurückzugreifen, da der JTL- Shop
ohnehin keine asynchrone Abarbeitung über Threads ermöglicht. Selbst mit mehreren Connections kann ich mir
nicht vorstellen dass du von einem immensen Geschwindigkeitsvorteil profitieren wirst...

Anstatt über deine Fakultät und deine Graduierung - die sicherlich noch folgt - zu philosophieren wäre ein Auszug
aus der my.cnf mehr von Nutzen gewesen...

 

[DITH-Shop]

[mysqld]
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
lc-messages-dir   = /usr/share/mysql
skip-external-locking
skip-name-resolve
lower_case_table_names = 1
max_connections = 100
bind-address       = 0.0.0.0
key_buffer_size       = 180M
max_allowed_packet   = 32M
thread_stack       = 192K
thread_cache_size   = 90
join_buffer_size   = 24M
table_open_cache_instances = 8
table_open_cache   = 7200
table_definition_cache  = 7200
open_files_limit   = 7200
sort_buffer_size   = 4M
read_buffer_size   = 1M
low_priority_updates = 0
concurrent_insert = 2
query_cache_limit   = 36M
query_cache_size        = 0
slow_query_log = 0
slow_query_log_file = "/var/log/mysql/log-slow-queries.log"
long_query_time = 4
log_queries_not_using_indexes = False
expire_logs_days   = 5
max_binlog_size   = 100M
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_buffer_pool_instances = 4
innodb_log_buffer_size = 16M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_read_io_threads = 64
innodb_write_io_threads = 64

 

[ag-websolutions.de]

Aber:
Wir können das Thema auch gerne schließen. Für die zwei Monate macht es keinen Sinn sich noch groß Gedanken zu machen.

Soll jetzt moderativ geschlossen werden oder nicht ... du bist der TO ?!

 

[DITH-Shop]

Mir wurscht - wenn es jemanden gibt der glaubt herauszufinden wo das Problem liegt lass offen - es gibt ja laut Forum auch andere User die exakt denselben Wert beim Abgleich haben.
Vielleicht hilft es ja wenigstens anderen wenn ich mich hier beleidigen lasse

 

[maydo]

@DITH-Shop

bist du mit dem Thema weitergekommen ? wie ist deine aktuell Rate ?

wir sind kürzlich auf einen neuen Server umgezogen, der alte lief nun knapp 4 Jahre.
auf dem alten Server lief der Abgleich mit 2,7 bis 3 Artikel/sec. damit kamen wir gerade noch so klar, (was noch zu langsam für unseren Bedarf ist)

Auf dem neuen komme ich aktuell nicht über 1,5/sec, was definitiv zu langsam ist.

Alle Optimierungen die ich auf dem alten durchgeführt habe, habe ich soweit auch auf dem neuen durchgeführt.

alt
Intel i5 4590 4 Cores 32GB RAM SSD Raid1
ubu14.04 mysql 5.5 php 7.1
load average 3-5 (15min)

neu
AMD Ryzen 7 1700X 16 Cores 64GB RAM SSD Raid1
ubu16.04. mysql 5.7 php 7.2
load average 2 (15min)

Der Shop selbst läuft schneller auf dem neuen, nur der Abgleich nicht.

Wie sieht es bei anderen aus, gibt es Erfahrungswerte ? Abgleichspeed Artikel pro Sekunde.

 

[DITH-Shop]

@DITH-Shop

bist du mit dem Thema weitergekommen ? wie ist deine aktuell Rate ?.

Bin bei 750 Artikel / Minute

Wichtig ist das bei den Festplatten die Protokollierung abgeschaltet wird (hdparm anschauen)
Und dann in den MySQL-DB Parametern entsprechend IO_DIRECT wählen

 

[FPrüfer]

Hallo,

auf dem alten Server lief der Abgleich mit 2,7 bis 3 Artikel/sec. ...

Auf dem neuen komme ich aktuell nicht über 1,5/sec,...

Gerade beim Abgleich haben einige Querys extrem vom MySQL-QueryCache profitiert. Dieser wurde jedoch von MySQL in 5.7 standardmäßig deaktiviert und wird in MySQL 8.0 ganz entfernt sein.

 

[DITH-Shop]

Hallo,

Gerade beim Abgleich haben einige Querys extrem vom MySQL-QueryCache profitiert. Dieser wurde jedoch von MySQL in 5.7 standardmäßig deaktiviert und wird in MySQL 8.0 ganz entfernt sein.

Query Cache habe ich schon mit der 5.6 deaktiviert was den Abgleich beschleunigte.

 

[maydo]

query cache habe ich getestet, aktiv inaktiv, keine unterschiede bemerkt, ist wieder default inaktiv.

@DITH-Shop
mit IO_DIRECT meinst du bestimmt O_DIRECT in innodb flush method
bin gerade dabei es zu testen.
PS: hast du alle Tabellen auf innodb umgestellt ? oder nur ausgewählte

- bei den Festplatten die Protokollierung abgeschaltet wird

was meinst du damit ? konnte keine Infos finden.

/dev/sda:
multcount = 0 (off)
IO_support = 1 (32-bit)
readonly = 0 (off)
readahead = 256 (on)
geometry = 62260/255/63, sectors = 1000215216, start = 0

hdparm ausgabe einer platte,
sind zwei ssds per mdadm im raid1 verbund.

 

[DITH-Shop]

query cache habe ich getestet, aktiv inaktiv, keine unterschiede bemerkt, ist wieder default inaktiv.

@DITH-Shop
mit IO_DIRECT meinst du bestimmt O_DIRECT in innodb flush method

Ja, sorry, war alles aus dem Gedächtnis heraus.

- bei den Festplatten die Protokollierung abgeschaltet wird

UNBEDINGT für backups sorgen !!!!

mit hdparm
hdparm -t --direct /dev/xxx

mit tune2fs
tune2fs -O ^has_journal /dev/xxx


1) schaltet den direkten Schreibzugriff ein
2) schaltet das Log (Journal) der Platte ab.
Brachte bei mir eine Steigerung von ~ 500% im Zusammenhangmit der oben benannten MySQL Einstellung.

ABER VORSICHT !!!!! Es kann schnell das GESAMTE System zerschossen werden wenn man nicht aufpasst.
Hat man es aber geschafft freut man sich über ein enorm performantes System