Insert in Tabelle mit vielen Indizes

Shade Of Mine

Hallo.

Ich habe eine MySQL MyISAM Tabelle auf der ich 99% der Zeit nur Lookups fahre. Deshalb hat sie sehr viele Indizes. Die Indizes zusammen sind dabei fast so groß wie die Daten in der Tabelle.

Das ganze ist nämlich ein Lookup für Logdateien. Die Dateien werden einmal reingespielt und danach sind Auswertungen sehr einfach möglich.

Mein Problem ist nun, dass das reinladen der Logdaten sehr sehr lange dauert. Wenn ich die Indizes entferne geht es natürlich deutlich schneller - nur leider bringt mir das nichts.

Gibt es eine Möglichkeit die Inserts dennoch zu beschleunigen? Die Indizes müssen während dem befüllen der Tabelle ja noch nicht verfügbar sein. uU ist auch eine andere Storage Engine für diese Situationen besser (so lange die Select Zeiten nicht nach oben gehen)?

Danke schonmal.

hustbaer

Bulk-Inserts wirst du ja schon verwenden schätze ich. Wenn nicht: das bringt irre viel.

Und manchmal ist es schneller, wenn man die Indexe droppt, dann die Daten hochlädt, und dann die Indexe neu anlegt.

Shade Of Mine

hustbaer schrieb:

Bulk-Inserts wirst du ja schon verwenden schätze ich. Wenn nicht: das bringt irre viel.

Es gibt einige Techniken die man unter Bulk Insert versteht.
Welche meinst du?

Und manchmal ist es schneller, wenn man die Indexe droppt, dann die Daten hochlädt, und dann die Indexe neu anlegt.

Leider hier nicht, da die Datenbank mit jedem einladen größer wird (die alten Logdaten bleiben erhalten) - so dass die Zeit fürs Index erstellen absolut krass wird (mehrere Stunden).

hustbaer

Ich kenne mich mit MySQL ehrlich gesagt kaum aus (dafür mit Datenbanken allgemein relativ gut :)).
Wichtig ist, dass du nicht Zeile für Zeile einfügst, sondern mehrere Zeilen auf einmal (z.B. ein paar tausend Zeilen mit einem einzigen INSERT).

Oder du verwendest "Bulk-Copy" Utilities, die Daten aus CSV Files o.ä. hochladen - die sind üblicherweise auf Geschwindigkeit optimiert.

Shade Of Mine

hustbaer schrieb:

Wichtig ist, dass du nicht Zeile für Zeile einfügst, sondern mehrere Zeilen auf einmal (z.B. ein paar tausend Zeilen mit einem einzigen INSERT).

Momentan sind es 1000 Zeilen mit einem Insert.
Irgendwo ist die maximal größe - die könnte ich noch ausloten und optimal ausnutzen...

hustbaer

Du musst ja nicht ans Limit gehen. Guck einfach ob es mit 2000 Zeilen schneller wird als mit 1000. Wenn nicht wäre es ziemlich sinnlos grössere Blöcke zu verwenden.

Gibt es für MySQL auch Bulk-Copy Utilities, und falls ja, hast du die schon probiert? z.T. verwenden solche Utilities spezielle APIs, mit denen es nochmal schneller geht.
Sonst könntest du auch noch in der C API nachgucken ob du entsprechende Funktionen findest.

Nochwas was du probieren könntest: Upload in einen TEMP Table ohne Indexe (natürlich auch mit Bulk-Insert), und dann den gesamten TEMP Table mit einem einzigen INSERT-SELECT in den Log-Table reintun.

Was die MySQL Storage Engines angeht kann ich dir leider nicht viel sinnvolles sagen.
Evtl. mal InnoDB ausprobieren.
Und dann gibts noch was was sich MERGE Tables nennt. Die INSERTs müssten damit schneller werden (da die Indexe der Teil-Tabellen kleiner sind). Bloss kann sein dass es die SELECTs dann zu sehr ausbremst.

Shade Of Mine

hustbaer schrieb:

Du musst ja nicht ans Limit gehen. Guck einfach ob es mit 2000 Zeilen schneller wird als mit 1000. Wenn nicht wäre es ziemlich sinnlos grössere Blöcke zu verwenden.

Habe jetzt was mit max_allowed_packet gebaut - sprich ich schicke immer die maximal mögliche anzahl an inserts (ohne reordering der inserts). Hat das ganze ein paar Prozent schneller gemacht.

Der wahnsinn ist aber wieschnell die Indizes die Performance killen. Die erste Logdatei wird zB mit 690 Zeilen pro Sekunde in die Datenbank geknallt, die 2. nur noch mit 538 und die dritte mit 466.

Gibt es für MySQL auch Bulk-Copy Utilities, und falls ja, hast du die schon probiert? z.T. verwenden solche Utilities spezielle APIs, mit denen es nochmal schneller geht.
Sonst könntest du auch noch in der C API nachgucken ob du entsprechende Funktionen findest.

Werde ich mal schauen gehen. Externe Tools würde ich ungern einbauen...

Nochwas was du probieren könntest: Upload in einen TEMP Table ohne Indexe (natürlich auch mit Bulk-Insert), und dann den gesamten TEMP Table mit einem einzigen INSERT-SELECT in den Log-Table reintun.

klingt spannend. werde das am Montag mal testen.

Und dann gibts noch was was sich MERGE Tables nennt. Die INSERTs müssten damit schneller werden (da die Indexe der Teil-Tabellen kleiner sind). Bloss kann sein dass es die SELECTs dann zu sehr ausbremst.

Ja, selects sind das hauptproblem. Wie gesagt: die meisten queries sind selects. Die neuen logfiles werden nur sehr selten reingeladen. notfalls übers wochenende. wichtig ist aber dass die reports schnell sind. die sind nämlich so schon sau lahm (weil zuviele Daten mit querverweisen existieren).

PS:
argh ich und rechtschreibung

hustbaer

wichtig ist aber dass die reports schnell sind. die sind nämlich so schon sau lahm (weil zuviele Daten mit querverweisen existieren).

Hm.
Kannst du die Daten nicht beim Importieren flachwalzen?

BTW: vielleicht wäre eine andere Storage-Engine oder gar ein anderes DMBS angesagt. Clustered-Indexe und Indexed-Views können einiges an Speed bringen bei Abfragen. Oder vielleicht wäre überhaupt ein OLAP System besser (wobei ich leider kein gutes & preiswertes empfehlen kann)?

Shade Of Mine

hustbaer schrieb:

Kannst du die Daten nicht beim Importieren flachwalzen?

Nicht so gut.
zB ein Logeintrag hat meistens 0-2 User auf die er sich bezieht. Ein User gehört zu N Gruppen.

Das ist nicht so leicht "flachzuwälzen" ohne dass die Datenbank alles doppelt und dreifach drinnen stehen hat.

BTW: vielleicht wäre eine andere Storage-Engine oder gar ein anderes DMBS angesagt. Clustered-Indexe und Indexed-Views können einiges an Speed bringen bei Abfragen. Oder vielleicht wäre überhaupt ein OLAP System besser (wobei ich leider kein gutes & preiswertes empfehlen kann)?

Budget sind 0 Euro :p

Aber durch die temporäre Tabelle und dem einzelnen "insert into select" bin ich bei meinen Testdaten von 1h53min auf knapp über 3 Minuten gefallen. Nice

Vielen Danke, das war der Tipp den ich gesucht habe.

hustbaer

Krass...

Aber da muss ich jetzt nochmal nachfragen, weil mit der Unterschied einfach zu krass vorkommt.

Wie sehen denn deine INSERTs aus? So?

INSERT INTO tab (a, b)
VALUES
	(1, 1),
	(1, 2),
	(1, 3),
...
	(1, 999);

Oder so?

INSERT INTO tab (a, b) VALUES (1, 1);
INSERT INTO tab (a, b) VALUES (1, 2);
INSERT INTO tab (a, b) VALUES (1, 3);
...
INSERT INTO tab (a, b) VALUES (1, 999);

volkard

Shade Of Mine schrieb:

Aber durch die temporäre Tabelle und dem einzelnen "insert into select" bin ich bei meinen Testdaten von 1h53min auf knapp über 3 Minuten gefallen. Nice

Krass.
Warum Faktor 38? 2 oder 3 oder gar 10 (bei vielen Indizes) würde ich mir ja noch gefallen lassen, ohne besonders neugierig zu werden. Aber 38 ist krank. Man kann doch nur DEN EINEN optimal mergen. Die anderen haben gelitten. Oder?
Was ist da los?

zwutz

hast du es schon mit ALTER TABLE xxx DISABLE KEYS versucht? Gerade bei vielen Indizes macht das gewaltig viel aus

hustbaer

volkard schrieb:

Warum Faktor 38? 2 oder 3 oder gar 10 (bei vielen Indizes) würde ich mir ja noch gefallen lassen, ohne besonders neugierig zu werden. Aber 38 ist krank. Man kann doch nur DEN EINEN optimal mergen. Die anderen haben gelitten. Oder?
Was ist da los?

Das verstehe ich jetzt nicht. Jeder Index ist eigenständig. Und jedes INSERT in die Tabelle führt dazu dass alle Indexe der Tabelle upgedated werden müssen. Jeder für sich. Dann kann auch jeder für sich optimal gemerged werden... nicht? Oder hast du das jetzt nicht auf die Index-Struktur (vermutlich irgend ein B-Baum Derivat) bezogen?

----

@zwutz:
Kann man bei MySQL/MyISAM denn einfach so Indexe deaktivieren, dann haufenweise Daten einfügen, und dann die Indexe einfach so wieder reaktivieren? Merkt sich das System welche Datensätze neu sind, und passt dann beim Reaktivieren die Indexe entsprechend an?
Ich kenne das De- und Reaktivieren nur von diversen Constraints. Und die werden dann IIRC beim Reaktivieren komplett neu gecheckt. Was eine Zeit lang dauern kann
Bzw. wenn es ein UNIQUE Constraint ist, müsste der dafür verwendete Index IMO auch komplett neu aufgebaut werden.
Kann mich da aber auch täuschen, ich hab' das ehrlich gesagt noch nie probiert.

elpego

hallo an Alle,
bin frisch angemeldet im Forum und suche verzweifelt den Button um ein neues Thema zu schreiben, kann ihn nicht finden. habe in FAQ nachgesehen da steht was von einem Button innerhalb der Beiträge. Wo finde ich den?

Shade Of Mine

@volkard:
Das Problem ist dass bei den inserts immer alle Keys neu berechnet werden müssen. Bei dem einmal rüber schieben muss nur einmal alle Keys neu berechnet werden.

@zwutz:
Ja, ist natürlich auch eine Möglichkeit - aber wenn die Tabelle nicht leer ist, sondern schon x millionen Zeilen hat, dann ist das komplett neu aufbauen auch nicht wirklich schnell.

@hustbaer:
Die Doku sagt zwar, dass er nur die neuen Zeilen indiziert - meinen Tests nach zu urteilen macht er aber ein komplett neues Indexing aller Zeilen.

hustbaer

Naja... vermutlich muss er sowieso nen kompletten Table-Scan machen, und zu wissen welche Zeilen wirklich neu sind. Kann auch sein dass es deswegen so lange dauert. (Oder er müsste dafür wieder eine Indexartige Struktur pflegen - geht natürlich auch, ist irgendwie doof, wenn durch das Deaktivieren eines Index ein Index nötig wird :D)

Aber nochmal zurück zu deinen Bulk-Inserts: wie sehen die aus (Syntax)?
Oder machst du die über ne non-SQL API?

Shade Of Mine

hustbaer schrieb:

Naja... vermutlich muss er sowieso nen kompletten Table-Scan machen, und zu wissen welche Zeilen wirklich neu sind. Kann auch sein dass es deswegen so lange dauert. (Oder er müsste dafür wieder eine Indexartige Struktur pflegen - geht natürlich auch, ist irgendwie doof, wenn durch das Deaktivieren eines Index ein Index nötig wird :D)

Ja er läuft im "Repair by Sort" Modus drüber. Hatte erst "Repair by Keycache", das war vielleicht lahm...

Aber nochmal zurück zu deinen Bulk-Inserts: wie sehen die aus (Syntax)?
Oder machst du die über ne non-SQL API?

Normale SQL Queries.

insert into t (a,b,c) values (a,b,c),(a,b,c),(a,b,c),...

und jedes insert ist maximal lang. ich nehme die maximale länge eines queries und hänge solange (a,b,c) an das query an bis diese erreicht ist.