MySQL (& PHP) - Daten komprimieren

[Ben Carter]

Hallo!

Ich bin gerade dabei, eine Anwendung zu entwickeln, die mit sehr vielen Datensätzen um sich wirft. Das Ganze ist ein Hobbyprojekt. Ich arbeite mit PHP 5 und MySQL 5.6. Grundsätzlich muss und werde ich bei PHP5 und MySQL bleiben, ein anderes Datenbanksystem steht mir nicht zur Verfügung.

Bei diesem Projekt geht es um die Auswertung von Log-Dateien, wobei jede Datei an die 10.000 Datensätze produziert, plus ein paar hundert in anderen Tabellen, aber die kann man unter dem Tisch kehren, da diese endlich sind und irgendwann bei ~20.000 keine neuen Datensätze mehr erzeugen werden.

In der großen Tabelle, die also recht rasch um tausende Datensätze anwachsen kann (je nachdem, wie sehr das Tool ankommt, können wir von einer Million Datensätze pro Tag reden, vielleicht sogar 5 Millionen), die geschrieben werden. Die Tabelle ist im innoDB Format, darum sollte es zumindest mal eine Weile problemlos klappen, rein von der Datensatzanzahl. Nach einem Jahr sind die Daten sowieso hinfällig, das heißt, im allerschlimmsten Fall gibt's an die zwei Milliarden Datensätze.

Die Abfragen selbst sind sehr simpel und auf diese Unmengen an Datensätzen wird nur selten Zugriff genommen, da aus diesen schon beim Einlesen die Statistiken erzeugt werden, um die es dann geht. Die große Tabelle soll einfach die Daten zur Kontrolle behalten bzw. damit sie nicht gleich verloren gehen.

Sieht bis hierhin jemand ein Problem?

Kommen wir aber nun zu dem Problem, das ich sehe. 10.000 Datensätze benötigen etwa 2,5 MB, ergo hätte die Datenbank nach einem Jahr etwa ein halbes Terabyte. Das ist leider inakzeptabel. Mit ein paar Optimierungen hoffe ich das ganze noch um 20-30% drücken zu können, aber mehr ist ohne einer Kompression nicht drin.
Komprimiert werden könnten 5 Spalten, die Text speichern, der nicht durchsucht werden muss.

Welche Möglichkeiten habe ich?
1. Bietet MySQL selbst etwas Sinnvolles, möglicherweise sogar Datensatzübergreifendes?
2a. Soll ich den Text zuerst mit PHP komprimieren, bevor ich ihn in die Datenbank speichere?
2b. Gibt es da schon etwas Vernünftiges? Die Textlänge ist ungefähr zwischen 5 und 50 Zeichen, es kann aber auch Ausreißer nach oben geben. Im Schnitt dürften es 20 Zeichen sein.

Ich habe die Option, die 5 Werte zusammenzulegen und gemeinsam in einem Feld abzuspeichern, wenn es Sinn ergibt.

Die Tabelle besteht aus:
1x BIGINT (Primary)
14x INT
1x VARCHAR(9)
5x TEXT
7x BOOLEAN

Ich denke, dass ich nach ein paar Testläufen 2 oder 3 INT zu SMALLINT ändern kann.

Danke!
lg Ben

[#44]

Man könnte die Logtexte in eine eigene Tabelle auslagern und lediglich über einen Fremdschlüssel referenzieren.

Das hilft, sofern sie sich relativ häufig wiederholen. Ansonsten wird das Anlegen neuer Texte natürlich irgendwann sehr imperformant...

[Ben Carter]

Auslagern würde vermutlich nicht so viel bringen, da es unzählig unterschiedliche Texte gibt (unterstützt dadurch, dass es mehrsprachig ist). Zumal der zusätzliche Schreibbefehl spürbar Performance kostet (von ~1.000 Datensätze pro Sekunde auf ~600 Datensätze pro Sekunde in der Testumgebung mit Debugging). 1.000-2.000 Datensätze pro Sekunde peile ich am Einsatzsystem an.

Sollte es am Ende noch immer schneller sein, als eine Komprimierung, werde ich es in Betracht ziehen, auch wenn der Nutzen nicht allzu hoch ist. Aber selbst 10% gespart, sind 10% gespart. :)

EDIT: Da fällt mir ein, Komprimierung bzw. das Auslagern in eine andere Tabelle könnte ggf. auch später gemacht werden, wenn die Serverlast gerade gering ist.

lg Ben

[Achill]

Hallo. Auch wenn es nichts mit deinem Problem direkt zu tun hat ... Logs auswertern kann man auch mit http://logstash.net bzw. die Systeme die dort miteinander kombiniert werden.

Falls du das Projekt aber zum Lernen nimmst, dann ignoriere mein Post ... :)

[Gast]

Hilft das schon?
http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html

Wenn du MariaDB benutzen kannst, gibt es noch TokuDB als Engine.
Nach Artikeln her soll sie eine höhere Kompression bieten, aber soweit ich weiß unterstützt sie noch keine FK.

[Gast]

Die 7 Booleans kannst du noch als Byte speichern :D

[#44]

Auslagern würde vermutlich nicht so viel bringen, da es unzählig unterschiedliche Texte gibt (unterstützt dadurch, dass es mehrsprachig ist).
Selbst wenn es zehntausende verschiedene Logmeldungen sind, wirst du damit mehr sparen als mit jeder Kompression, da sich jeder einzelne auf zwei Byte (eine ID) eindampfen ließe. Solange sie dutzend bis hundertfach vorkommen gewinnst du.

Das dürfte das beste Vorgehen sein, solange nicht viele Werte vorkommen die grundsätzlich nur wenige male zu erwarten sind.

Lieber mehr Aufwand betreiben, als direkt mit Tricksereien ala Zusammenfassen von Spalten und eigener Textkompression zu arbeiten - zumal die ja auch an der Performanz knabbern wird.
Das klingt doch schon nach Wartbarkeits- und Erweiterungsproblemen...

[Ben Carter]

Danke schonmal für die zahlreichen Antworten.

Hallo. Auch wenn es nichts mit deinem Problem direkt zu tun hat ... Logs auswertern kann man auch mit http://logstash.net bzw. die Systeme die dort miteinander kombiniert werden.

Falls du das Projekt aber zum Lernen nimmst, dann ignoriere mein Post ... :)
Danke. Das Projekt dient hauptsächlich als Hobby und es soll alleinstehend sein. Es gibt bereits andere Projekte, die etwas Ähnliches machen, aber weder den Komfort noch die Flexibilität haben, die ich mir wünsche und außerdem sehr langsam wurden, als die Datenmenge ordentlich anstieg (ein 20.000 Zeilen Log dauerte dann schon mal 5 Minuten).

Hilft das schon?
http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html

Wenn du MariaDB benutzen kannst, gibt es noch TokuDB als Engine.
Nach Artikeln her soll sie eine höhere Kompression bieten, aber soweit ich weiß unterstützt sie noch keine FK.
Danke, ich werde mir das ansehen, vor allem, ob der Server die auch Voraussetzungen mitbringt.
Wie eingangs schon erwähnt, ist es keine Option, das Datenbanksystem zu wechseln.

Die 7 Booleans kannst du noch als Byte speichern :D
Es können Abfragen dieser Werte entstehen. Die Frage ist: zahlt es sich aus, etwas mehr Rechenleistung für die Vergleichsoperation zu verwenden, um ca. 2% Speicher zu sparen. Bzw. werde ich die Booleans wohl noch auf BIT(1) ändern.


Selbst wenn es zehntausende verschiedene Logmeldungen sind, wirst du damit mehr sparen als mit jeder Kompression, da sich jeder einzelne auf zwei Byte (eine ID) eindampfen ließe. Solange sie dutzend bis hundertfach vorkommen gewinnst du.

Das dürfte das beste Vorgehen sein, solange nicht viele Werte vorkommen die grundsätzlich nur wenige male zu erwarten sind.
Der Großteil der Werte wird leider nur einmalig vorkommen.

Lieber mehr Aufwand betreiben, als direkt mit Tricksereien ala Zusammenfassen von Spalten und eigener Textkompression zu arbeiten - zumal die ja auch an der Performanz knabbern wird.
Das klingt doch schon nach Wartbarkeits- und Erweiterungsproblemen...
Das ist zumindest kein Problem. Das Projekt wird in sich abgeschlossen sein und ist an und für sich nicht mal sonderlich groß (voraussichtlich <20.000 LOC) und ich arbeite komplett alleine daran.
Performance ist und bleibt natürlich ein Faktor, der bedacht werden muss. Darum denke ich, dass es sinnvoll ist, die Komprimierung mittels Cronjob bei niedriger Last durchzuführen.

Sollte es keine bessere Möglichkeit geben, werde ich vermutlich alle umsetzbaren Tipps einsetzen, um das Maximum rauszuholen. Im schlimmsten Fall muss ich anfangen, die Daten früher als nach einem Jahr zu entsorgen.

lg Kinman

[Gast]

Es können Abfragen dieser Werte entstehen. Die Frage ist: zahlt es sich aus, etwas mehr Rechenleistung für die Vergleichsoperation zu verwenden, um ca. 2% Speicher zu sparen. Bzw. werde ich die Booleans wohl noch auf BIT(1) ändern.


BIT(1) ist ein Byte groß.

http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html

[RattuS]

Die große Tabelle soll einfach die Daten zur Kontrolle behalten bzw. damit sie nicht gleich verloren gehen.
Dann als Dateien auslagern, sofern sich die Felder dafür anbieten, sonst mehrere Tabellen pro Entität führen. Das garantiert deinem System auch eine bessere Wartbarkeit bzgl. Backup/Restore.

10.000 Datensätze benötigen etwa 2,5 MB, ergo hätte die Datenbank nach einem Jahr etwa ein halbes Terabyte. Das ist leider inakzeptabel.
250 Byte pro Datensatz ist doch noch sparsam. Warum sind denn 500 GB schon inakzeptabel? Wo liegt das Problem?

Wie viele Bytes hast du durchschnittlich in den TEXT-Feldern?

Es können Abfragen dieser Werte entstehen. Die Frage ist: zahlt es sich aus, etwas mehr Rechenleistung für die Vergleichsoperation zu verwenden, um ca. 2% Speicher zu sparen. Bzw. werde ich die Booleans wohl noch auf BIT(1) ändern.
Der Gast meinte wohl, dass du die 7 Werte in ein einzelnes Byte zusammenfasst (als Bit pro Feld) und ggf. wieder auseinanderziehst, wenn du sie auswertest. Dann hattest du nur einen Verbrauch von einem statt 7 Bytes pro Datensatz. Aber das ist grenzwertig. ;D

Es gibt übrigens keine Booleans in MySQL. Das ist lediglich ein Alias für TINYINT(1) == 1 Byte. Je nach Treiber hat der Wert im Recordset höchstens schon einen impliziten Cast.

[Ben Carter]

BIT(1) ist ein Byte groß.

http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html

Es gibt übrigens keine Booleans in MySQL. Das ist lediglich ein Alias für TINYINT(1) == 1 Byte. Je nach Treiber hat der Wert im Recordset höchstens schon einen impliziten Cast.

Der Gast meinte wohl, dass du die 7 Werte in ein einzelnes Byte zusammenfasst (als Bit pro Feld) und ggf. wieder auseinanderziehst, wenn du sie auswertest. Dann hattest du nur einen Verbrauch von einem statt 7 Bytes pro Datensatz. Aber das ist grenzwertig. ;D

Danke für den Hinweis. Darum wollte ich ja auf BIT wechseln, aber das ist nun sowieso hinfällig, da es genausoviel Speicher benötigt. Zusammenziehen ist etwas, das ich testen muss, da es durchaus zu Abfragen auf die Tabelle kommen kann, um z.B. tiefergreifende Statistiken zu haben. Ist die Performance trotzdem okay, passt es. Sollte es aus Platzgründen nicht klappen, diese Tabelle zu behalten, fallen natürlich diese Statistiken auch weg.

Dann als Dateien auslagern, sofern sich die Felder dafür anbieten, sonst mehrere Tabellen pro Entität führen. Das garantiert deinem System auch eine bessere Wartbarkeit bzgl. Backup/Restore.
Falls du meinst, dass ich Werte, die sich wiederholen können, auslagern sollte: es wiederholt sich kaum etwas. Falls du etwas anderes meinst, bitte ich um Aufklärung, da es mir nicht vollkommen klar ist.

250 Byte pro Datensatz ist doch noch sparsam. Warum sind denn 500 GB schon inakzeptabel? Wo liegt das Problem?
Die Anzahl der Datensätze macht die Größe. Und das Problem bei der finalen Größe ist, dass ich für den Speicher zahlen muss. Je mehr, umso teurer. Da es sich nur um ein Hobbyprojekt handelt, gebe ich da ein eher knappes Budgetlimit.

Wie viele Bytes hast du durchschnittlich in den TEXT-Feldern?
ca. 24 Bytes im Schnitt, kann aber auch mal auf >100 gehen.

lg Ben

[RattuS]

Wenn du wegen des Hostings nur wenig Speicher hast, wirst du nicht um regelmäßiges Archivieren + Komprimieren bzw. Exportieren herumkommen. Ansonsten schau halt, ob du schon an die InnoDB-Komprimierung (http://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html) gedacht hast.

[littlejam]

Welche Möglichkeiten habe ich?
1. Bietet MySQL selbst etwas Sinnvolles, möglicherweise sogar Datensatzübergreifendes?
2a. Soll ich den Text zuerst mit PHP komprimieren, bevor ich ihn in die Datenbank speichere?
2b. Gibt es da schon etwas Vernünftiges? Die Textlänge ist ungefähr zwischen 5 und 50 Zeichen, es kann aber auch Ausreißer nach oben geben. Im Schnitt dürften es 20 Zeichen sein.

1. http://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html
Versuch macht kluch, dein Anwendungsszenario passt eigentlich nicht so richtig.
Ergebnisse würden mich sehr interessieren, also bitte Feedback :-)

Ich habe die Option, die 5 Werte zusammenzulegen und gemeinsam in einem Feld abzuspeichern, wenn es Sinn ergibt.

Die Tabelle besteht aus:
1x BIGINT (Primary)
14x INT
1x VARCHAR(9)
5x TEXT
7x BOOLEAN

Ich denke, dass ich nach ein paar Testläufen 2 oder 3 INT zu SMALLINT ändern kann.

Danke!
lg Ben
Bigint, wird warscheinlich der Primärschlüssel sein.
Wenn du sicher stellen kannst, dass der nie über 4Mrd geht, nimm unsigned int.
Warum? Weil der primary in jedem Indizierten Feld auch steht und somit 4 Byte pro Feld ausmacht.
Bei 500Mio Zeilen sind das 2GB mehr fürs Bigint + 2GB pro Index.

Text in Tinytext oder besser varchar ändern.

Die 7 Bools in unsigned tinyint ändern und per Bitmaske nutzen.
Bei 500 Mio Zeilen ist ein Bool mit Index auch nicht wirklich praktisch außer sie sind nicht gleichverteilt, sprich bspw. ein Boolfeld hat nur 10000x true, der Rest false, dann kanns was bringen, wenn die selects nur auf die true gehen.
Allerdings ist auch dort wieder zu beachten, dass die 4-8Byte vom Primary in jedes indizierte Feld mit rein gehen.

Versuch die Ints in kleinere Formate zu quetschen.

Normalisiere Doppelungen, die Logeinträge selber zu normalisieren scheint mir sinnlos.

Zu guter Letzt schau dir die künftigen Selects an, nutze so wenig Indexe wie nötig und wenns geht covering Indexe.

Je nachdem wie Kompromissbereit/Ausfalltolerant du bist, gibts noch div. Mysql-Tunings, die die Schreibperformance nochmal erhöhen.

Gruß

[Dio Eraclea]

Die Abfragen selbst sind sehr simpel und auf diese Unmengen an Datensätzen wird nur selten Zugriff genommen, da aus diesen schon beim Einlesen die Statistiken erzeugt werden, um die es dann geht.

Der Knackpunkt für mich hier ist ob wirklich selten oder nie (sprich nur Sicherheitsbackup).

Sollte es sich nur um ein Sicherheitsbackup halten frage ich mich wozu du die Daten überhaupt in die Datenbank schreiben willst und diese nicht z.B. in (bitte nicht hauen dafür) eine CSV Datei schreibst die du dann rotierst (also regelmäßig komprimiert wegsicherst und mit einer neuen Datei anfängst).

Wenn diese Daten nur zur Anzeige später nochmals gebraucht werden (aber die Geschwindigkeit der Anzeige + verursachte Last dadurch nicht so große Rolle spielen) könnte man sich natürlich trotzdem etwas ähnliches überlegen => dabei müsste dann in der MySQL Datenbank eine Referenz auf die Datei (Dateiname) und eine eindeutige ID (Zeilennummer?, UUID?) gespeichert werden.

Sollen auch noch Abfragen (z.B. auf den Text) durchführbar sein würde ich es wagen zu behaupten dass MySQL (und andere relationale Datenbank) nicht dafür geeignet ist.

[Ben Carter]

1. http://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html
Versuch macht kluch, dein Anwendungsszenario passt eigentlich nicht so richtig.
Ergebnisse würden mich sehr interessieren, also bitte Feedback :-)
Werde ich definitiv testen und dann Rückmeldung geben. Es kann allerdings noch ein wenig dauern, da mein Fokus gerade wo anders liegt und ich das zuerst noch fertig machen muss/will.

Bigint, wird warscheinlich der Primärschlüssel sein.
Wenn du sicher stellen kannst, dass der nie über 4Mrd geht, nimm unsigned int.
Warum? Weil der primary in jedem Indizierten Feld auch steht und somit 4 Byte pro Feld ausmacht.
Bei 500Mio Zeilen sind das 2GB mehr fürs Bigint + 2GB pro Index.
Es sind vielleicht nie so viele Datensätze auf einmal in der Datenbank, aber der Index sollte auch über die Zeit eindeutig bleiben und da bin ich mir sehr sicher, dass es über die 4 Mrd. geht.

Text in Tinytext oder besser varchar ändern.
Tinytext sollte gehen, varchar aufgrund der nicht vorhersehbaren Länge, eher nicht.

Die 7 Bools in unsigned tinyint ändern und per Bitmaske nutzen.
Bei 500 Mio Zeilen ist ein Bool mit Index auch nicht wirklich praktisch außer sie sind nicht gleichverteilt, sprich bspw. ein Boolfeld hat nur 10000x true, der Rest false, dann kanns was bringen, wenn die selects nur auf die true gehen.
Allerdings ist auch dort wieder zu beachten, dass die 4-8Byte vom Primary in jedes indizierte Feld mit rein gehen.

Versuch die Ints in kleinere Formate zu quetschen.

Normalisiere Doppelungen, die Logeinträge selber zu normalisieren scheint mir sinnlos.
Was normalisieren geht, ist normalisiert. :)
Ich werde mal die Performance testen, wenn ich die Bools zusammenfasse.

Zu guter Letzt schau dir die künftigen Selects an, nutze so wenig Indexe wie nötig und wenns geht covering Indexe.
Covering Index wird auch benutzt, wo es sinnvoll ist.

Je nachdem wie Kompromissbereit/Ausfalltolerant du bist, gibts noch div. Mysql-Tunings, die die Schreibperformance nochmal erhöhen.

Gruß
Nope. Keine Toleranz in der ersten Zeit. :)



Der Knackpunkt für mich hier ist ob wirklich selten oder nie (sprich nur Sicherheitsbackup).

Sollte es sich nur um ein Sicherheitsbackup halten frage ich mich wozu du die Daten überhaupt in die Datenbank schreiben willst und diese nicht z.B. in (bitte nicht hauen dafür) eine CSV Datei schreibst die du dann rotierst (also regelmäßig komprimiert wegsicherst und mit einer neuen Datei anfängst).

Wenn diese Daten nur zur Anzeige später nochmals gebraucht werden (aber die Geschwindigkeit der Anzeige + verursachte Last dadurch nicht so große Rolle spielen) könnte man sich natürlich trotzdem etwas ähnliches überlegen => dabei müsste dann in der MySQL Datenbank eine Referenz auf die Datei (Dateiname) und eine eindeutige ID (Zeilennummer?, UUID?) gespeichert werden.

Sollen auch noch Abfragen (z.B. auf den Text) durchführbar sein würde ich es wagen zu behaupten dass MySQL (und andere relationale Datenbank) nicht dafür geeignet ist.
Abfragen sind selten in der Relation zu anderen Abfragen, es kann aber dennoch zu einer handvoll Abfragen pro Tag kommen.

Danke euch beiden für die Antworten. Ich melde mich, wenn ich Testergebnisse habe.

lg Ben

[littlejam]

Tinytext sollte gehen, varchar aufgrund der nicht vorhersehbaren Länge, eher nicht.
Das solltest du nochmal überdenken.

- Tinytext wird nicht inline in der row gespeichert - varchar ja.
- Ein "varchar(255) not null" gefüllt mit 20 Bytes verbraucht 21 Byte - genauso viel wie tinytext.
- varchar kannst du indizieren, text nur bedingt

Text solltest du nur nehmen, wenn die rowsize 64kb überschreiten könnte.

Grüße

[Ben Carter]

Hallo!

Hat leider etwas gedauert, bis ich alles testen konnte bzw. ich habe das System nun ein wenig überarbeitet. Die Textfelder komprimieren brachte kaum etwas, selbst wenn ich sie zusammenfasste und anschließend komprimierte, da ich durch das Zusammenfassen ein paar Zeichen an meine "Codierung" verloren habe. Alles im allem war Zusammenfassen aber immer noch einen ticken kleiner.

Einfache Kompression sparte etwa 15%
Zusammengefasste Kompression etwa 20%
Die Geschwindigkeit ging massiv runter (nur noch halb so schnell).

Das Zusammenfassen der Bits zu einem Tinyint brachte auch nochmals ca. 10%. Performance blieb mehr oder weniger unangetastet, da ich vieles von SQL Richtung PHP geschoben habe. Interessanterweise ist es massiv performanter größere Datenmengen (~10MB statt ~0,5MB) zwischen MySQL und PHP auszutauschen und den ganzen Logikkram im PHP zu machen, als direkt in MySQL. Vor allem wenn man sich JOINS sparen kann.

Ich war wirklich sehr überrascht, welchen Unterschied das macht. Teilweise konnte ich somit massiven Geschwindigkeitszuwachs verzeichnen (Ablauf in 0,2 Sekunden statt 6-8 Sekunden). Das sind in Bezug auf Responsibility Welten.

Mit der (Text-)Datenmenge habe ich es nun so gelöst, dass ich diese einfach nicht im Datenbanksystem sondern binär am Server speichere, mir ein Bisschen was einfallen habe lassen, um ohne viel zu suchen darauf zugreifen zu können und den Großteil wieder in PHP mache. Die Daten sind zusätzlich komprimiert.

Mit allem zusammengefasst konnte ich die Größe um ca. 70% drücken, ein paar kleine Stellschrauben sind noch offen, sodass ich auf die 80% am Ende anpeile und die Ausführungszeiten massiv beschleunigen. War zwar viel Arbeit, viel zu probieren und erzeugte auch ein paar Brainfucks, aber nun bin ich zufrieden damit.

lg Ben

[littlejam]

Danke für die Info.

Das Zusammenfassen der Bits zu einem Tinyint brachte auch nochmals ca. 10%. Performance blieb mehr oder weniger unangetastet, da ich vieles von SQL Richtung PHP geschoben habe. Interessanterweise ist es massiv performanter größere Datenmengen (~10MB statt ~0,5MB) zwischen MySQL und PHP auszutauschen und den ganzen Logikkram im PHP zu machen, als direkt in MySQL. Vor allem wenn man sich JOINS sparen kann.

Ich war wirklich sehr überrascht, welchen Unterschied das macht. Teilweise konnte ich somit massiven Geschwindigkeitszuwachs verzeichnen (Ablauf in 0,2 Sekunden statt 6-8 Sekunden). Das sind in Bezug auf Responsibility Welten.
Kannst du da mal bitte die Datenmengen beziffern?
Also wieviele Zeilen und Joins über wieviel Tabellen.
Und die Relation vom Buffer Pool zu den Daten.

Das klingt mir eher nach einer Query ohne Index oder mit schlechtem Index oder irgendwelchen Index-merges.

Gruß