MySQL und häufige Daten-Änderungen

>über den einfluss von löchern zwischend en datensätzen musst du dir
>keine gedanken machen wenn deine selects indices benutzen, weil die nach
>1-3 zugriffen (gerne aus dem index-cache) wissen auf welche stelle im

Wenn eine Abfrage aber trotz Indexscans etliche Werte innerhalb eines 
Bereiches ergibt, so daß dann auch ein gewisser Bereich der 
Tabellendaten gelesen werden muß, dann machen sich Löcher und Fragmente 
in den Tabellendaten durchaus wieder negativ bemerkbar.

Tom schrieb:
> ich wollte mal fragen, wie MySQL mit z.B. InnoDB sowie andere
> Datenbank-Systeme aus wartungstechnischer Sicht mit häufigen
> Daten-Bewegungen klar kommen

Was nennst du häufig? Mehr als 1 Million UPDATEs der gleichen Row pro 
Sekunde oder was? Aus dem Blickwinkel einer Datenbank haben Menschen oft 
ein komisches Verständnis von "häufig".

> bzw. welche Strategien existieren, um
> eventuelle Probleme zu vermeiden?

Was für Probleme hast du da im Sinn?

> Es geht mir nicht um die Schreib-Performance, sondern eher um so Dinge
> wie Fragmentierung der Daten oder Indexe, physischem Platzbedarf auf dem
> Datenträger, etc.

Darauf hat die Häufigkeit der Änderungen der Daten nahezu keinen 
Einfluß. OK, es kommt auf die Details an. InnoDB schreibt eine neue 
Version der Row in-place. Zu Komplikationen kommt es dabei nur, wenn

a) die neue Version der Row länger ist und
b) kein Platz mehr auf der aktuellen Seite ist

erst wenn beides erfüllt ist, muß InnoDB eine neue Seite aufmachen und 
die Hälfte Rows der alten auf die neue kopieren. Und genau dann besteht 
überhaupt nur die Chance einer Fragmentierung-

Die Praxis zeigt, daß InnoDB für 99% aller Anwendungen überhaupt kein 
Problem dieser Art hat. Fragmentierung existiert schlicht nicht.

> Nehmen wir mal an, in einer Datenbank ist relativ konstant viel
> Bewegung, welche die Datenbank von der Performance her mühelos
> verarbeitet. Neben dem obligatorischen Aktualisieren und Hinzufügen
> werden häufig auch nicht mehr benötigte Datensätze entfernt oder
> verschoben (sei es nun durch Partitionierung oder durch entsprechende
> Wartungs-Abfragen, die bestimmte Records z.B. löschen oder in
> Archiv-Tabellen auslagern)...ist das problemlos und folgenlos über einen
> langen Zeitraum möglich?

Das ist Wischi-Waschi. Das Hinzufügen oder Löschen von Rows ist ganz 
normales Datenbankgeschäft. "Verschieben" gibt es gar nicht erst als 
eigenständige Operation.

Nur wie gesagt: eine Workload, die InnoDB hier in Bedrängnis bringt, 
müßte man erst mühselig konstruieren. Mit dem, was im Alltag anfällt, 
kommt InnoDB für gewöhnlich gut zurecht.

Bekannte, real vorkommende Pattern mit Problemen wären kurzlebige 
Zeitreihendaten. Also so etwas, wie Monitoring-Daten, die nach Timestamp 
sortiert eingehen und nach einigen Tausend bis Millionen Iterationen vom 
Anfang her wieder gelöscht werden. Hier ist das Problem, daß das 
Housekeeping (alte Daten abräumen) vergleichsweise teuer kommt. Und man 
handelt sich gern mal nennenswert "Verschnitt" ein - Speicherplatz, der 
zwar fast keine Daten enthält, aber nicht freigegeben wird. Für 
Zeitreihendaten gibt es bessere Datenbanken. In die gleiche Kerbe 
schlagen Queueing-Systeme. Da ist das Problem aber mehr auf der 
Locking-Seite. Es skaliert einfach nicht für viele Reader/Writer (und: 
"viele" meint hier nicht 10 oder 100, sondern einige 10-tausend).

c.m. schrieb:
> - die indices nach einer bestimmten zeit rebuilded werden. b-trees
> werden nach häufigen insert/update/deletes unbalanciert.

Err. Falsch.

Indizes degraden nicht. Ein Index-Rebuild ist nur dann notwendig, wenn 
sich die Indizierungsregeln geändert haben, etwa wenn die Collation 
einer (indizierten) Spalte geändert wurde oder wenn eine (indizierte) 
Spalte einen anderen Typ bekommen hat. Beim INSERT/UPDATE/DELETE werden 
Indizes automatisch neu balanciert. Genau deswegen sind Änderungen an 
indizierten Spalten auch teurer als solche an nichtindizierten.

>Indizes degraden nicht. Ein Index-Rebuild ist nur dann notwendig, wenn
>sich die Indizierungsregeln geändert haben, etwa wenn die Collation
>einer (indizierten) Spalte geändert wurde oder wenn eine (indizierte)
>Spalte einen anderen Typ bekommen hat. Beim INSERT/UPDATE/DELETE werden
>Indizes automatisch neu balanciert. Genau deswegen sind Änderungen an
>indizierten Spalten auch teurer als solche an nichtindizierten.

Doch - die degraden. Zwar nicht logisch, aber physisch. Wenn Indexpages 
komplett voll sind, lückenlos und in Reih' und Glied auf der Platte 
liegt, dann kann man einen neuen Key nicht mehr einfach so physisch in 
diese Reihe zwischenschieben, sondern es wird eine neue Page aufgemacht 
(bzw. mehrere - root + leaf pages, je nach aktuellem Zustand), die dann 
irgendwo anders liegt - also der Beginn einer Fragmentation.

Jens G. schrieb:
>>Indizes degraden nicht. Ein Index-Rebuild ist nur dann notwendig, wenn
>>sich die Indizierungsregeln geändert haben, etwa wenn die Collation
>>einer (indizierten) Spalte geändert wurde oder wenn eine (indizierte)
>>Spalte einen anderen Typ bekommen hat. Beim INSERT/UPDATE/DELETE werden
>>Indizes automatisch neu balanciert. Genau deswegen sind Änderungen an
>>indizierten Spalten auch teurer als solche an nichtindizierten.
>
> Doch - die degraden. Zwar nicht logisch, aber physisch. Wenn Indexpages
> komplett voll sind, lückenlos und in Reih' und Glied auf der Platte
> liegt, dann kann man einen neuen Key nicht mehr einfach so physisch in
> diese Reihe zwischenschieben, sondern es wird eine neue Page aufgemacht
> (bzw. mehrere - root + leaf pages, je nach aktuellem Zustand), die dann
> irgendwo anders liegt - also der Beginn einer Fragmentation.

Wenn du Fragmentation auf diese Weise definierst, dann gibt es keinen 
nichtfragmentierten Index in InnoDB. Oder irgendeiner Datenbank mit 
B-Tree Indizes. Denn es gibt keine Abbildung eines B-Trees auf eine 
lineare Folge von Pages, die man ohne Sprünge traversieren könnte. Die 
Sprünge sind aber gerade die Fragmentationen.

Praktisch entschärft InnoDB das Problem auf drei Wegen:

1. Pages werden in Extents von 1MB (64 Pages) alloziert. Fragmentation 
findet oberhalb der 1MB Extent-Größe immer statt, innerhalb der 
Extents aber praktisch nicht (zum großen Teil dank des Buffer-Pools und 
Prefetch-Mechanismus)

2. der Adaptive Hash Index (AHI) wird parallel zur primären PK->Leaf 
Page Abbildung im RAM gehalten. Bei einer gut eingestellten 
MySQL-Instanz werden dann 99+ % aller PK-Zugriffe über den AHI 
aufgelöst, müssen den Index-Tree also gar nicht mehr traversieren.

3. generell werden Index Pages bevorzugt im Buffer Pool (RAM) gehalten. 
Wildes Umherspringen während der Traversierung hat dann keine extra 
Kosten, so lange die Seiten im RAM liegen. Auch hier hilft Prefetching.

>Wenn du Fragmentation auf diese Weise definierst, dann gibt es keinen
>nichtfragmentierten Index in InnoDB. Oder irgendeiner Datenbank mit

Eben.

>Praktisch entschärft InnoDB das Problem auf drei Wegen:

>1. Pages werden in Extents von 1MB (64 Pages) alloziert. Fragmentation
>findet oberhalb der 1MB Extent-Größe immer statt, innerhalb der
>Extents aber praktisch nicht (zum großen Teil dank des Buffer-Pools und
>Prefetch-Mechanismus)

Und wenn eine Page bzw Extend voll ist, und InnoDB will ausgerechnet 
dort einen neuen Key einfügen? Dann wird wohl auch InnoDB für diesen 
neuen Unterbereich eine neue Seite/Extend irgendwo anfangen müssen 
(alles nach dem neuen Key nach hinten verschieben wird es aus 
Perf.-Gründen wohl nicht).

>3. generell werden Index Pages bevorzugt im Buffer Pool (RAM) gehalten.
>Wildes Umherspringen während der Traversierung hat dann keine extra
>Kosten, so lange die Seiten im RAM liegen. Auch hier hilft Prefetching.

Index und Prefetching - paßt irgendwie nicht zusammen. Es sei denn, 
InnoDB will wirklich einen Indexscan über einen größeren Bereich machen.
Für das gezielte Suchen eines/weniger Keys aber eher hinterlich.

Jens G. schrieb:
>>Wenn du Fragmentation auf diese Weise definierst, dann gibt es keinen
>>nichtfragmentierten Index in InnoDB. Oder irgendeiner Datenbank mit
>
> Eben.
>
>>Praktisch entschärft InnoDB das Problem auf drei Wegen:
>
>>1. Pages werden in Extents von 1MB (64 Pages) alloziert. Fragmentation
>>findet oberhalb der 1MB Extent-Größe immer statt, innerhalb der
>>Extents aber praktisch nicht (zum großen Teil dank des Buffer-Pools und
>>Prefetch-Mechanismus)
>
> Und wenn eine Page bzw Extend voll ist, und InnoDB will ausgerechnet
> dort einen neuen Key einfügen? Dann wird wohl auch InnoDB für diesen
> neuen Unterbereich eine neue Seite/Extend irgendwo anfangen müssen

Es wird ein neuer Extent alloziert und so viele Pages wie nötig dahin 
verschoben. Wie ich bereits sagte: Fragmentation findet oberhalb der 1MB 
Extent-Größe immer statt.

Und das ist in der Praxis auch überhaupt kein Problem. Wer Fragmentation 
auf diesem Level für ein Problem hält, hat sie schlicht nicht 
verstanden. Filesysteme wie ext4 oder xfs (sogar reiserfs schon) haben 
das Konzept eines langen, durchgehenden Datenbereichs verworfen, obwohl 
man genau das zur unfragmentierten Speicherung einer großen Datei ja 
braucht. Sie machen statt dessen Node Groups bzw. Extents (verschiedene 
Namen für im Prinzip die gleiche Idee). Sie führen also Fragmentation 
ein und sind trotzdem schneller als ihre Vorgänger.

Dazu kommt noch der gerade stattfindende Übergang von magnetischen 
Festplatten hin zu SSD. 99% aller IO-Optimierungen in Datenbanken (und 
Filesystemen) sind damit obsolet.

>>3. generell werden Index Pages bevorzugt im Buffer Pool (RAM) gehalten.
>>Wildes Umherspringen während der Traversierung hat dann keine extra
>>Kosten, so lange die Seiten im RAM liegen. Auch hier hilft Prefetching.
>
> Index und Prefetching - paßt irgendwie nicht zusammen.

Durchaus.

> Es sei denn,
> InnoDB will wirklich einen Indexscan über einen größeren Bereich machen.
> Für das gezielte Suchen eines/weniger Keys aber eher hinterlich.

Für Punktzugriffe ist in erster Linie der AHI zuständig. Aber auch eine 
Suche in einem Index wird i.d.R. mehrere Pages lesen müssen, wenn man 
den Baum Ebene für Ebene herunter steigt. Und sobald einige dieser Pages 
im gleichen Extent liegen, erkennt das der Prefetcher und saugt den 
kompletten Extent in den Bufferpool. Bei einem Scan natürlich noch mehr.

Unterhalb der Datenbank und des Filesystems des Servers fragmentiert 
möglicherweise auch noch das Filesystem, auf dem das Disk-Image einer 
virtueller Maschine aufsitzt. So dass Daten, die in einer VM sequentiell 
erscheinen, es im Storage-Layer nicht sein müssen.

Bei Datenbanken neigen COW-Filesysteme wie ZFS, btrfs und WAFL 
prinzipbedingt zu Fragmentierung, da keine bestehenden Daten 
überschrieben werden, sondern jeder Schreibvorgang an einer neuen Stelle 
stattfindet. Bei HDDs kann sich das recht markant auf die Performance 
von Backups/Exports auswirken und regelmässige Defragmentierung nötig 
machen. Mindestens bei btrfs kann deshalb für solche Daten das Verfahren 
auf eine konventionelle direkt überschreibende Methode umgestellt 
werden.