MySQL Tabellen bleiben fragmentiert

Hallo,
ich habe auf meinem Windows Server 2008 R2 einen MySQL-Server (5.6) laufen.
Um die Performance zu steigern und Probleme zu analysieren habe ich den Server mit "MySQL Tuner for Windows" überprüft.
Lt. diesem Tool sind einige Tabellen fragmentiert Es handelt sich ausschließlich um InnoDB Tabellen.
Ein "Optimize Table" und "Alter Table xyz Engine = InnoDB" hat jeweils nichts geholfen.
Habt Ihr noch einen Tipp für mich?
Danke Vorab!

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 213046

Url: https://administrator.de/contentid/213046

Ausgedruckt am: 25.11.2024 um 14:11 Uhr

7 Kommentare

Neuester Kommentar

Hallo,

Vl. gibt es noch andere Wege die Performance zu verbessern.
Und bringt ein anderes DB Design mehr als ein "OPTIMIZE TABLE xyz;"

Und wenn du die Features von InnoDB nicht benötigst und viele Inserts hast hilft dir vl. auch ein Umstieq auf MySAM.

Gruß

Ok, danke. Ich denke schon, dass InnoDB das richtige für die Tabellen ist. Ist aber schon komisch, dass sich offenbar die Tabellen nicht defragmentieren lassen. Vielleicht erstelle ich noch einen Dump und importiere diesen dann nochmal.
Gruß

Die Wahl der Engine hängt stark vom Einsatzzweck ab.
-Sind mehr Inserts als Selects?
-Benötigst du Foreign Keys?
-usw.

Gruß

Die Datenbank ist für eine Adressverwaltung mit Lieferungen usw. usw. Hier werden regelmäßig (z.T. alle 30 Min) alle Daten aus unserer anderen Warenwirtschaft via ODBC (mit Flowheater.de) in die jeweiligen Tabellen geschaufelt, damit die Mitarbeiter recht aktuelle Daten abrufen können. Ist hier InnoDB nicht so gut geeignet? Foreign Key benötige ich bislang nicht. Volltextsuche usw. benötige ich natürlich.
Bez. des Datenimports: ODBC ist relativ langsam. Macht es Sinn für größere Tabellen eine temporäre Tabelle anzulegen, welche per ODBC befüllt wird, und erst danach die Daten der temporären Tabelle in die eigentliche zu kopieren? (INSERT INTO xyz SELECT * FROM xyz_tmp)
Gruß

Hallo,

also ich habe nen Datenimport am laufen mit ca. 10.000.000 Datensätzen.
Der läuft mit InnoDB in ca. 8 Stunden durch und mit MySAM benötige ich nur ca. 45min.
Also siehst du selbst das der Unterschied gewaltig ist.

Kommt auf den Import an. Aber normalerweise mache ich sowas über Tmp Tabellen.

Gruß

Moin ScArAbAeuS,

Zitat von @RedBullmachtfit:

Bez. des Datenimports: ODBC ist relativ langsam. Macht es Sinn für größere Tabellen eine temporäre Tabelle anzulegen, welche per ODBC befüllt wird, und erst danach die Daten der temporären Tabelle in die eigentliche zu kopieren? (INSERT INTO xyz SELECT * FROM xyz_tmp)

Hier im Forum beantworten wir auch rhetorische Fragen, wenn es sein muss. Oder wenn wir sie nicht erkennen.

Natürlich geht es schneller, denn
a) in der temporären Tabelle wird nur angefügt, ohne jegliche Constraint/Kollisionsprüfung
b) es wird nur in die neue Tabelle geschrieben, aber nicht zusätzlich noch ganzen Indizes aktualisiert
c) ggf. kannst du noch jegliche Loggerei (und Lockerei ) für diese Tabelle abschalten - denn hier wirst du mit Sicherkeit kein Rollback machen

Zum Thema Fragmentierung

Eine Adressverwaltung hat mit Sicherheit überproprortional viele Varchar-Felder, die einen sehr unterschiedlichen Füllungsgrad haben.
Physikalisch bedeutet das natürlich, dass bei einer maximal definierten Satzlänge von meinetwegen 1000 Byte mal ein Satz 120 Zeichen lang ist, ein anderer 372 Zeichen der nächste 605 Zeichen. Bei einer Neuanlage/einem Import werden diese Sätze hintereinander weggeschrieben.
Wenn einer dieser Sätze jemals upgedatet wird (jemand zieht von "Leverkusen" nach "Leverkusen-Mitte"), ändert sich sich die (echte) Satzlänge um +6 Zeichen . der Beispielsatz hat jetzt statt 372 Zeichen nunmehr 378 und passt nicht mehr an die bisherige Position. Also wird der komplette Satz an eine freie Stelle in der Tabelle kopiert mit einer reservierten Länge von 378 Zeichen und die "alten" 372 Zeichen werden als "frei" markiert.
(Ja, richtig, ein UPDATE-Befehl auf einen Satz bedeutet in diesem Fall, ein Satz wird INSERTed, ein anderer DELETEd. Ergo: 100 UPDATEs=200 Datensätze werden geändert...).

-> Diese systemimmanente Fragmentierung kannst du nicht mit OPTIMIZE oer ALTER TABLE in den Griff bekommen, sondern wirksamer mit regelmäßigen Export-Drop-Reimportläufen über mysqldump oder ähnliches. vorzugsweise einmal die Woche in der Nacht von Sonntag auf Montag um 03.15h. (Anmerkung: ich würde es wahrscheinlich schedulen und nicht online machen).

Besser (oder zumindest zu prüfen) wäre für diese stark frequentierte und durchmischte Tabellenstruktur ein Ändern der Varchar-Felder auf CHAR, also feste Länge. Das kostet zwar mehr Plattenplatz, aber die Fragmentierung ist natürlich bei einer "festen" Satzlänge spürbar geringer.

Grüße
Biber