4. MySQL: Überhang bei sehr großen Tabellen

MySQL: Überhang bei sehr großen Tabellen

  • R

    Hallo Leute,

    mir stellt sich folgendes Problem:
    Ich habe eine Tabelle die durchaus eine Milliarde Einträge enthalten kann.
    Struktur:

    CREATE TABLE IF NOT EXISTS `infos` (
    `ID` bigint(20) unsigned NOT NULL auto_increment,
    `FileID` bigint(20) unsigned NOT NULL,
    `Number` int(10) unsigned NOT NULL,
    `Bytes` int(10) unsigned NOT NULL,
    `MessageID` varchar(250) NOT NULL,
    PRIMARY KEY (`ID`),
    KEY `FileID` (`FileID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    Das Problem ist nun, dass aus dieser Tabelle ca einmal täglich u.U. mehrere Millionen Einträge gelöscht werden und das dadurch ein recht großer Überhang (mehrere GB) entsteht.

    Ein Aufruf von "OPTIMIZE TABLE 'infos'" kommt allerdings nicht in Frage, da dieser eine viel zu hohe Laufzeit verursacht.

    Die Dokumentation zu "OPTIMIZE TABLE" (http://mysql2.mirrors-r-us.net/doc/refman/5.0/en/optimize-table.html) lässt etwas zu wünschen übrig, ich wüsste gerne, ob im Falle meiner Tabelle, wo nur etwas gelöscht wurde, auch die Indexes geupdatet werden müssen?

    Ich vermute, dass es für die Fragementierung wesentlich besser wäre, wenn das Feld 'MessageID' eine feste Länge hätte, oder? Wäre in diesem Fall überhaupt eine Optimierung nötig, oder würden neu hinzugefügte Zeilen automatisch die Fragemente GENAU ersetzen?

    Vielen Dank schon einmal, Rodney

    0
  • U

    Ob Optimize hilft kannst du nur testen.
    MySQL mit 1 Milliarde Einträge ist aber heftig.
    Hatte ich mal mit ca. 30 Millionen und habe dann auf MSSQL gewechselt. MSSQL war dabei ca 50 Mal schneller.
    Optimize hat anfangs sehr geholfen war aber auch nicht die Lösung. Insbesondere bei JOIN etc.
    Du kannst aber einen Task in der Nacht laufen lassen. Düfte bei den Daten eigentlich nur max. 10 Min dauern.
    Kannst auch versuchen in einen neue Tabelle zu schreiben, alte löschen und neue Umbenennen. Ging bei mir nicht da wir Replikation hatten.

    0
  • S

    Servus,

    eigentlich dürfte man keine Optimierung benötigen.

    Du arbeitest mit festen Satzlängen, der als "gelöscht" markierte Bereich kann
    von der Datenbank problemlos wieder verwendet werden. Ich würde das mal ein
    Paar Tage laufen lassen und die Dateigröße protokollieren.

    0
  • R

    Unix-Tom schrieb:

    Ob Optimize hilft kannst du nur testen.
    MySQL mit 1 Milliarde Einträge ist aber heftig.
    Hatte ich mal mit ca. 30 Millionen und habe dann auf MSSQL gewechselt. MSSQL war dabei ca 50 Mal schneller.
    Optimize hat anfangs sehr geholfen war aber auch nicht die Lösung. Insbesondere bei JOIN etc.
    Du kannst aber einen Task in der Nacht laufen lassen. Düfte bei den Daten eigentlich nur max. 10 Min dauern.
    Kannst auch versuchen in einen neue Tabelle zu schreiben, alte löschen und neue Umbenennen. Ging bei mir nicht da wir Replikation hatten.

    Die Datenbankgröße sollte für MySQL laut einigen Benchmarks kein Problem darstellen. Der Optimize-Durchlauf dauert allerdings nicht 10 Minuten sondern wesentlich länger, deshalb kommt er nicht mehr in Frage.
    Ein Umkopieren habe ich schon in Betracht gezogen, aber ich würde es gerne vermeiden.

    Scheppertreiber schrieb:

    Servus,

    eigentlich dürfte man keine Optimierung benötigen.

    Du arbeitest mit festen Satzlängen, der als "gelöscht" markierte Bereich kann
    von der Datenbank problemlos wieder verwendet werden. Ich würde das mal ein
    Paar Tage laufen lassen und die Dateigröße protokollieren.

    Wie oben erwähnt, habe ich noch keine festen Satzlängen, da ich VARCHAR verwende. Ich werde dan Datentyp mal auf Char ändern, obwohl das natürlich einen wesentlich erhöhten Platzverbrauch nach sich zieht.

    Mir ging es auch im wesentlichen darum, zu wissen, ob die Indizes mit der Zeit "schlechter" werden, wenn kein Optimize erfolgt, oder ob MySQL die selbst konsistent hält.

    0
  • S

    Größere Datenmengen (> 100 Mio Records) hatte ich mal mit Btrieve probiert.

    Eigentlich müßte jedes Datenbanksystem seine Indices mitziehen.

    Bei Deiner Größenordnung ist das etwas "nachteilig". Nach jedem Ändern/Löschen
    baut die DB die Indices um, das geht elend in die Laufzeit. Bei solchen
    Datenmengen sollte man andere Wege gehen (Bestandsdatei ändern und dann erst
    inidizieren).

    0
  • R

    Scheppertreiber schrieb:

    Größere Datenmengen (> 100 Mio Records) hatte ich mal mit Btrieve probiert.

    Eigentlich müßte jedes Datenbanksystem seine Indices mitziehen.

    Bei Deiner Größenordnung ist das etwas "nachteilig". Nach jedem Ändern/Löschen
    baut die DB die Indices um, das geht elend in die Laufzeit. Bei solchen
    Datenmengen sollte man andere Wege gehen (Bestandsdatei ändern und dann erst
    inidizieren).

    Das Löschen der Datensätze geht in einem absolut akzeptablem Tempo, soviel dazu. Eine Million Zeilen werden in unter einer Minute gelöscht, laut der Argumentation, müsste da ja schon die Zeit für das Umbauen der Indizes drin sein.

    BTW: Wie heißt die korrekte technische Übersetzung für "Überhang"? Ich finde dazu nichts, würde aber gerne ein paar Recherchen anstellen. Unter anderem würde ich gerne die Größe des Überhangs auslesen.

    0
  • S

    Löschen geht immer schnell *fg*

    Gelöschte Records werden nur als "gelöscht" markiert. Der Platz den sie belegt
    hatten bleibt erhalten. Beim Neu-Anfügen von Records versucht die Datenbank, diese
    Stellen wiederzuverwenden.

    Mit der "optimierung" ist uU gemeint, diesen Platz auszumerzen und damit die
    Dateigröße runterzubringen.

    Probier' doch einfach mal.

    Das einzige Problem dabei können (theoretisch) die internen Satznummern (auto-increment key)
    bzw. physical index) sein - da gehen irgendwann die Nummern aus ...

    0
  • R

    Scheppertreiber schrieb:

    Löschen geht immer schnell *fg*

    Gelöschte Records werden nur als "gelöscht" markiert. Der Platz den sie belegt
    hatten bleibt erhalten. Beim Neu-Anfügen von Records versucht die Datenbank, diese
    Stellen wiederzuverwenden.

    Mit der "optimierung" ist uU gemeint, diesen Platz auszumerzen und damit die
    Dateigröße runterzubringen.

    Probier' doch einfach mal.

    Das einzige Problem dabei können (theoretisch) die internen Satznummern (auto-increment key)
    bzw. physical index) sein - da gehen irgendwann die Nummern aus ...

    hm, ja das war mir bereits alles klar, dann hatte ich mich wohl undeutlich ausgedrückt 😉

    Es wurde hier ja geschrieben, dass MySQL automatisch beim (nach dem?) Löschen die Indexes anpasst, bzw diese immer konsistent bleiben.

    Bei mir kommt eben ein Optimieren durch "optimize table" nicht infrage, da diese Abfrage eine viel zu hohe Laufzeit hat.

    Deswegen wollte ich noch ein paar Recherchen zum Überhang anstellen, wozu ich allerdings nicht die korrekte englische Übersetzung finde 😉

    0
  • S

    Überhang = Overhead ?

    Erklär mal genau was Du meinst.

    0
  • R

    Scheppertreiber schrieb:

    Überhang = Overhead ?

    Erklär mal genau was Du meinst.

    Ich meine den ungenutzen Speicher, der eben genau dann entsteht, wenn man Zeilen aus einer Tabelle löscht. Die Datenbank wird dann ja nicht kleiner, sondern hält diesen freigewordenen Datenbereich für zukünftige Zeilen zurück. Die Größe dieses "freien" Datenbereichs möchte ich auslesen. Overhead scheint eine mögliche Übersetzung zu sein, die auch phpmyadmin nutzt. Damit bin ich auf folgendes gestoßen:

    "when you do a SHOW TABLE STATUS, "overhead" is data_free. phpmyadmin is the only application that uses the term "overhead"."

    Problem gelöst 😉

    0
  • S

    Gratuliere *grins* Das hatte ich auch gemeint !

    0
Anmelden zum Antworten