MySQL: Performance-Probleme mit mehreren Indizes

[Forumsgast]

Hallo Leute,

ich habe eine MySQL Tabelle mit 8 Spalten und 4 Indizes:

CREATE TABLE IF NOT EXISTS `test` (
`Subject` varchar(5000) NOT NULL,
`From` varchar(5000) NOT NULL,
`GUID` char(36) NOT NULL,
`ParentGUID` char(36) NOT NULL,
`Date` bigint(20) NOT NULL,
`Parts` int(11) NOT NULL,
`GroupID` int(11) NOT NULL,
`DataInfo` mediumblob,
PRIMARY KEY (`GUID`),
KEY `ParentGUID` (`ParentGUID`),
KEY `Date` (`Date`),
KEY `Subject` (`Subject`(20))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Bei wenigen Datensätzen in der Tabelle klappt das Einfügen sehr schnell, sobald die Anzahl Richtung mehrere Millionen geht, werden die Insert-Queries jedoch deutlich langsamer. Eine Zeile ist bei mir durchschnittlich ca 3kbyte groß.

Für das Erstellen der 4 Indizes wird der Aufwand natürlich mit steigender Zeilenanzahl höher, allerdings sollte er logarithmisch steigen.

The size of the table slows down the insertion of indexes by log N, assuming B-tree indexes.

Quelle: MySQL :: MySQL 5.0 Reference Manual :: 7.2.18 Speed of INSERT Statements

Dies erklärt also meiner Meinung nach nicht die Performance-Einbrüche.
Ich frage mich nun, wie MySQL den Index-Baum intern verwaltet, bzw wartet. Dieser muss ja eigentlich während des Einfügens immer wieder neu gewichtet werden, damit ein optimaler Index entsteht. Wann führt MySQL diese Operationen aus, oder muss man es manuell dazu anstoßen?

Wenn jemand dazu etwas weiß, oder schon ähnliche Szenarien hatte, wäre ich über eine Antwort sehr erfreut.

Viele Grüße,
Zender

[Forumsgast]

edit: sorry, malwieder nix vom eigentlichen thread gelesen...

[Forumsgast]

Was heißt deutlich lagsamer? Spürbar lagsamer oder werden aus 50ms 120ms?

Ich frage mich nun, wie MySQL den Index-Baum intern verwaltet, bzw wartet. Dieser muss ja eigentlich während des Einfügens immer wieder neu gewichtet werden, damit ein optimaler Index entsteht.

Was fügst du da ein? Ein Datensatz oder 1000ente? Bei 1000enten würd ich dir ALTER TABLE table DISABLE KEYS und ENABLE KEYS empfehlen. Das macht sich bei 1mio Datensätzen schon bemerkbar. Aber bei einem einzelnen Insert sollte das gar nix bringen. Bei einem Insert wird ja nicht der komplette Baum neu erstellt, sondner nur ein paar Blätter neu geordnet und das auch nur wenn nötig.

[Forumsgast]

Welche Punkte/Tipps aus MySQL :: MySQL 5.0 Reference Manual :: 7.2.18 Speed of INSERT Statements hast Du denn schon ausgetestet?

[Forumsgast]

Naja da die Indizes intern mit B Bäumen arbeiten und diese im Idealfall bei jedem Knoten nur halb so viele Elemente haben wie möglich ist ein einfügen meistens sehr schnell möglich. Da die Größe bei so Knoten meistens um die 100 ist sollte also etwa nur bei jedem 50ten Element eine Umsortierung nötig sein. Da das aber meist auch recht fix geht sollte das nicht so das Problem darstellen.

[Forumsgast]

Was heißt deutlich lagsamer? Spürbar lagsamer oder werden aus 50ms 120ms?

Was fügst du da ein? Ein Datensatz oder 1000ente? Bei 1000enten würd ich dir ALTER TABLE table DISABLE KEYS und ENABLE KEYS empfehlen. Das macht sich bei 1mio Datensätzen schon bemerkbar. Aber bei einem einzelnen Insert sollte das gar nix bringen. Bei einem Insert wird ja nicht der komplette Baum neu erstellt, sondner nur ein paar Blätter neu geordnet und das auch nur wenn nötig.

Ich füge in einem Batch-Prozess über mehrere Stunden 100.-tausende Einträge hinzu. Ich bemerke, dass sich die dafür benötigte Zeit (nicht in einem Durchgang, sondern eben auf die Stunden verteilt) so ca. verdreifacht...

Das Problem ist, dass ich nebenbei auch von der Tabelle lese, daher ist ein Deaktivieren der Keys nicht so einfach machbar. Das ist ja auch der Punkt, der im Manual vorgeschlagen wird.

[Forumsgast]

Ich füge in einem Batch-Prozess über mehrere Stunden 100.-tausende Einträge hinzu. Ich bemerke, dass sich die dafür benötigte Zeit (nicht in einem Durchgang, sondern eben auf die Stunden verteilt) so ca. verdreifacht...

Das Problem ist, dass ich nebenbei auch von der Tabelle lese, daher ist ein Deaktivieren der Keys nicht so einfach machbar. Das ist ja auch der Punkt, der im Manual vorgeschlagen wird.

Ich frage mich nun, wie MySQL den Index-Baum intern verwaltet, bzw wartet. Dieser muss ja eigentlich während des Einfügens immer wieder neu gewichtet werden, damit ein optimaler Index entsteht. Wann führt MySQL diese Operationen aus, oder muss man es manuell dazu anstoßen?

nach jedem einzelnen insert befehl. dagegen helfen entweder transaktionen oder write-tabellensperren (was übrigens auch gegen fsync wartezeit hilft).
da du nebenbei lesen musst, solltest du auf innoDB umsteigen da das dort, dank MVCC, kein problem darstellt.
kannst du nicht auf innoDB zurückgreifen bleibt dir nur das sperren der tabelle(n).

ansonsten kannst du noch ein bisschen mit den server variablen rumspielen (vor allem cache dinge), habe ich aber keine erfahrung mit.

[Forumsgast]

Wobei das einfügen eine Laufzeit von log n hat und dass kann sich bei so vielen Einträgen dann doch auswirken.

[Forumsgast]

Wobei das einfügen eine Laufzeit von log n hat und dass kann sich bei so vielen Einträgen dann doch auswirken.

Naja, klar wirkt sich das am Anfang aus. Ob man dann aber später 1 Million oder 10 Millionen Datensätze hat, wirkt sich nur noch sehr wenig aus (weniger als 25% mehr)

Meinem Logs nach steigt die Laufzeit aber um mehr als 100%. Ich werde mal noch ein wenig testen und mir die Laufzeiten in Relation zu den Einträgen mal genauer anschauen.