7
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!
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!
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 213046
Url: https://administrator.de/contentid/213046
Printed on: April 25, 2024 at 10:04 o'clock
7 Comments
Latest comment
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ß
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ß
Gruß
Die Wahl der Engine hängt stark vom Einsatzzweck ab.
-Sind mehr Inserts als Selects?
-Benötigst du Foreign Keys?
-usw.
Gruß
-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ß
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ß
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,
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
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. 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)
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
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.
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
1
Hallo Biber! Vielen Dank für deine umfangreichen Tipps und Erklärungen. Bin recht neu in der Materie MySQL, daher die etwas rhetorische Frage 
Mit den Varchar-Feldern hast du völlig recht. Ich probiere das ganze aus!
Gruß
Mit den Varchar-Feldern hast du völlig recht. Ich probiere das ganze aus!
Gruß
