Ich arbeite gerade an einer Webapp die täglich mehrere DBase Dateien mit MySQL synchronisieren muss. Es gibt 2-3 kleine mit bis zu 2.000 bis 3.000 Datensätze und eine große mit 200.000 Datensätze. Jede DBase Datei hat ca. 10-20 Spalten wobei ich nur manche (5-10) wirklich brauche, die anderen sind unwichtig.
Bisher habe ich ein Yii Console Command erstellt das die Dateien via PHP einliest und dann prüfe ich ob es die Daten schon gibt und falls nicht, füge ich sie ein. Bisher suche ich über findByAttributes und füge sie dann über
$x = new Model Y;
$x->attribut = "bla"
ein. Bei 2.000 bis 3.000 Datensätzen dauert das ca. 5 Minuten, finde ich akzeptabel. Aber bei den 200.000 Datensätzen hat es zuletzt ca. 20 Stunden gedauert…glaub ich. Test das nicht so oft weil es so lange dauert;) Habe InnoDB Tabellen und die Config schon aus verschiedenen Quellen heraus optimiert. Aber dauert trotzdem noch ziemlich lange. Das Problem ist das viele Datensätze voneinander abhängig sind, sonst würde ich einfach größere Queries erstellen. So muss ich wirklich bei allen 200.000 Zeilen ca. 5-15 Queries durchführen.
Achso am Anfang setze ich ein "delete" Attribut bei allen Datensätzen auf true und in der afterFind bzw afterSave Methode setze ich es auf false. Am Schluss lösche ich alle bei denen delete auf true steht. So das alte Daten aus der SQL Datenbank rausfliegen.
Und bei einigen Modellen ist in der afterSave bzw. beforeDelete Methode eine Funktion die eine Log Nachricht erstellt und auch in der Datenbank speichert. Später soll genau ersichtlich sein was sich wann geändert hat. Ich denke das bremst auch nochmal ziemlich, da es die SQL Anweisungen ja verdoppelt…
Was für Möglichkeiten gibt es das ganze zu Optimieren? Ich fände es schön wenn der ganze Import nur noch ca. 2-4 Stunden dauert.
Also 5 Minuten für 2000-3000 Zeilen find ich arg langsam. Das sollte auch in ein paar Sekunden gehen.
Hier ein paar Ideen:
1.) Entweder sind zu viele Indizes gesetzt was INSERTS verlangsamt (müssen ja bei INSERTS up to date gehalten werden) oder zu wenige gesetzt (Beispiel "delete" flag => WHERE flag="delete" ohne Index führt zu einem table scan)
2.) Wenn es wirklich um Performance geht sollte man eventuell von AR auf DAO umsteigen. Damit umgehst du den Overhead den AR aufgrund der ganzen Logik mitbringt. AR ist für einzelne CRUD Anweisungen sinnvoll (etc. für direkte Usereingaben) aber eher schlecht wenns um "bulk" Dinge geht
4.) Ad Speicher: Im Normalfall werden Dateien die per PHP eingelesen werden komplett in den Speicher geladen was ja bei 200.000 Zeilen schon heftig sein kann. Auch hier könnte man überlegen nur einen Teil einzulesen (zweiter parameter bei fopen($handle,$size))
Es gibt nur einen Primary Key (Auto Increment) pro Tabelle, meinst du das?
Ja das hab ich mir auch gedacht und werde das mal umstellen!
Ich teste das auf einem Laptop, der hat aber 4GB Ram, die MySQL Config hab ich jedenfalls versucht zu optimieren, vorher war die Prozessorauslastung bei dem Import bei 80% und ein paar 100MB Speicher und jetzt ist der Import bei 20% Prozessorauslastung und knapp 2GB RAM, ich weiß aber nicht was besser ist…
Also ich lese die Datei ja über die PHP extension und der liest jedenfalls in der Methode jede Zeile einzeln, ob der intern vorher die ganze Tabelle in den Speicher liest weiß ich nicht.
Ich stelle mal von AR auf DAO um und gucke wie es dann mit der Performance aussieht und schreib dann nochmal;)
Wenn du bei Punkt 1 schon so fragst bin ich mir sicher dass es zu einem gewissen Teil an fehlenden Indizes liegt Generell liegen ca. 80% der MySQL Performance Probleme an Indizes oder einem zu kleinen buffer cache.
Da kommt immer das Telefonbuchbeispiel ins Spiel: Wenn man dort nach einem Namen sucht wird man schnell fündig, es ist nämlich nach dem Namen indiziert. Wenn ich “Zebra” suche fang ich nicht bei “Apfel” an Umgekehrt, also mittels einer Telefonnummer nach dem Namen zu suchen ist aber irr langsam, man muss eigentlich ALLE Namen durchgehen und die Nummer gegenchecken. Genau das passiert bei einer WHERE Klausel die sich auf ein Attribut bezieht das nicht indiziert ist. PRIMARY KEYS sind immer ein Index, du kannst aber mehrere setzen. Ich kenn deine Tabelle nicht aber wenn für jede WHERE Abfrage eventuell Millionen Zeilen gelesen werden müssen nur um eine zu finden ist das ein Bottleneck und würde erklären warums so - auf wienerisch - “zaach rennt”
Ja stimmt, dass wusste ich eigentlich auch, hab aber irgendwie nicht dran gedacht, bei meinen bisherigen Programmierarbeiten waren die Datenbestände nie so groß
Ok, als Beispiel:
Habe eine Tabelle
id, integer, primary key, auto increment
spalte 1, varchar 20
spalte 2, unwichtig
spalte 3, unwichtig
spalte 4, unwichtig
Ich brauche die ID unbedingt weil wir nachher Data Matrizen (quasi QR Codes) drucken und die müssen möglichst klein sein, und der Integer Wert nimmt nicht so viel ein wie die 20 alphanumerischen Zeichen. Reicht es wenn ich dann einfach einen Primary Key bei Spalte 1 hinzufüge? Bzw. in den den anderen Tabellen einfach bei den Spalten die nachher von dem Import durchsucht werden?
Richtig einfach die Spalten nach denen am meisten gesucht wird indexen.
Was AR angeht. Wenn du den ganzen Kram wie Validation etc nicht brauchst, dann nutz den query Builder. Der verbraucht wesentlich weniger Speicher / Ressourcen als der ganze AR overhead. Würde ich mir solchen Datenmengen sowieso angewöhnen wenn AR nicht gebraucht wird.
Ja natürlich. Am liebsten hätte ich einfach eine Schleife gemacht in dem der zu dem Query einfach ein Insert Statement hinzufügt. Nach 100-1000 Datensätzen führt er das aus und macht direkt weiter. Das Problem ist bei der Verschachtelung, also das ich die ID von dem ersten Datensatz brauche bevor ich den zweiten Einfügen kann etc.
Aber werde die Tabellen mal weiter indexieren und AR weg lassen, mal gucken wie schnell es dann ist. (Ich kann leider erst Mittwoch damit weiter machen, aber melde mich auf jeden Fall! Ich hasse das wenn Themen so und Lösung im Raum stehen bleiben…)
Also die id wird dann für den QR Code verwendet? Da diese Spalte ja ein PK ist ist sowieso ein Index drauf. Wenn du also nach einer id suchst geht das zackig. Jetzt hast du noch weitere Möglichkeiten Indizes zu setzen:
1.) Du erzeugst den PK als mehrspaltigen Schlüssel (id,spalte1). Weiß nicht obs sinnvoll ist, bei auto increment der id wohl eher nicht
Aber das mit dem Index werde ich jetzt auch direkt auf ein paar ältere Projekte anwenden, auch wenn es da quasi keine Rolle spielt, find ich sollte man mit Performanceoptimierung auch schon im kleinen Anfangen:)
Habe ich jetzt umgestellt ($this->_db wird vorher einmalig Yii::app()->db zugewiesen):
$command = $this->_db->createCommand(
'SELECT id FROM {{prozessbereich}} WHERE name = :name'
):
$command->bindParam(':name', $name, PDO::PARAM_STR);
$model = $command->queryRow();
if ($model === false) {
$command = $this->_db->createCommand(
'INSERT INTO {{prozessbereich}} (name) VALUES (:name)'
);
$command->bindParam(':name', $name, PDO::PARAM_STR);
$command->execute();
return $this->_db->getLastInsertId();
}
return $model['id'];
Vor der Rückgabe prüfe ich natürlich noch ob ein Fehler aufgetreten ist, habe ich jetzt aber mal weg gelassen. So Funktionen werden 4 mal pro Datensatz ausgeführt, bei 6500 Datensätzen und das dauert 530 Sekunden. Außerdem loggt der jetzt keine Meldungen mehr in der DB beim Import, ist aber trotzdem nicht schneller geworden.
Die Prozessorauslastung bei dem Prozess (mysqld) ist auch nur bei ca. 5%, der Virtuelle Speicher bei 2,3GB und der Reservierte Speicher bei 180MB. Muss ich vielleicht noch was an der Config anpassen?
Edit: Bei manchen Inserts brauche ich dann auch die ID vom vorherigen eingefügen Model. Bei den Spalten nach den gesucht wurd, also beispielsweise hier in der Tabelle prozessbereich die Spalte ‘name’, habe ich ein Index hinzugefügt.
Generell liegen ca. 80% der MySQL Performance Probleme an Indizes oder einem zu kleinen buffer cache.
Umgekehrt, also mittels einer Telefonnummer nach dem Namen zu suchen ist aber irr langsam, man muss eigentlich ALLE Namen durchgehen und die Nummer gegenchecken. Genau das passiert bei einer WHERE Klausel die sich auf ein Attribut bezieht das nicht indiziert ist. PRIMARY KEYS sind immer ein Index, du kannst aber mehrere setzen. Ich kenn deine Tabelle nicht aber wenn für jede WHERE Abfrage eventuell Millionen Zeilen gelesen werden müssen nur um eine zu finden ist das ein Bottleneck und würde erklären warums so - auf wienerisch - “zaach rennt”