Hallo allerseits, ich wollte mal fragen, wie MySQL mit z.B. InnoDB sowie andere Datenbank-Systeme aus wartungstechnischer Sicht mit häufigen Daten-Bewegungen klar kommen bzw. welche Strategien existieren, um eventuelle Probleme zu vermeiden? 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. 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? Grüße...Tom
Inwiefern wuerden sich deine Anforderungen von Standardbenutzern unterscheiden ? Allenfalls ein paar Zahlen ? Was bedeutet oft ? viel ? lange ?
Tom schrieb: > 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. die daten brauchen so viel platz auf dem datenträger wie sie halt brauchen, plus verwaltungsinformationen und löchern im datenbestand die zu klein sind als das sie mit neuen daten gefüllt werden können. die performance wird nicht beeinflusst wenn: - du keine full table scans machst (d.h. indices benutzen und explain plans beachten) - die indices nach einer bestimmten zeit rebuilded werden. b-trees werden nach häufigen insert/update/deletes unbalanciert. ü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 datafile sie für den angeforderten datensatz zugreifen müssen. es finden also keine unnöötigen reads statt, kein full table scan. datenbanken sind schließlich keine windows registry ^^
>ü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.
Jens G. schrieb: >>ü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. wenn man vorraussetzt, dass mehrere passende rows in einem read gelesen werden könnten, ja. bei einer schmalen tabelle mit wenigen datensätzen wird das zutreffen, je breiter und/oder länger eine tabelle wird, desto weniger trifft das zu.
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.
Für die pgsql hab ich 2 2 * vacuumdb --all in der crontab stehen..
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.
PG schrieb: > Für die pgsql hab ich > 2 2 * vacuumdb --all > in der crontab stehen.. Warum benutzt du nicht den Autovacuum Dienst?
>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.
:
Bearbeitet durch User
Thread beobachten
Seitenaufteilung abschaltenBitte melde dich an um einen Beitrag zu schreiben. Anmeldung ist kostenlos und dauert nur eine Minute.
Bestehender Account
Schon ein Account bei Google/GoogleMail? Keine Anmeldung erforderlich!
Mit Google-Account einloggen
Mit Google-Account einloggen
Noch kein Account? Hier anmelden.