Tabellen für viele kleine Datensätze optimieren

[Medium]

Hallo,

ich bin gerade dabei, mir Vorüberlegungen zu einem Energie-Monitoring System zu machen. Bei unserem Kunden sollen diverse Strom/Gas/Wasser-Messgeräte verbaut werden, die via Modbus an Gateways übers Ethernet an unseren Server gehen. Die Messwerte sollen auch über Jahre gespeichet werden, um langfristige Betriebsoptimierungen darauf stützen zu können. Überschlagen fallen dabei etwa 150000 Messwerte pro Tag an, verteilt auf knapp 650 Messstellen.

Diese Werte sollen später sowohl als Liste gedruckt, aber auch grafisch betrachtet werden. Der Art "male mir bitte auf, wie sich die Spannung an Messstellen a, b und c zwischen dem 15.01.2014 und dem 15.02.2014 verhielt".

Meine Frage ist jetzt, wie ich mein Archiv am besten anlege. Bisher war ich von einer einzigen großen Tabelle ausgegangen, mit folgender Struktur:

SQL-Code:

CREATE TABLE `history` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `vdate` timestamp NOT NULL default '0000-00-00 00:00:00',
  `valueID` smallint(5) unsigned NOT NULL default '0',
  `maxValue` float NOT NULL default '0',
  `meanValue` float NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `Index_2` (`valueID`),
  KEY `Index_3` (`vdate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 6144 kB; InnoDB free: 53248 kB; InnoDB free: 92'

(Es werden häufiger als gespeichert Werte abgerufen, und dann nur minütlich bis stündlich Maxima und Durchschnitte in die Tabelle geschrieben. Sonst wäre das beim tatsächlichen Leseintervall ein Datenaufkommen von grob gerechneten 4,5GB/Tag, was etwas üppig würde.)

"valueID" ist ein Fremdschlüssel auf eine Liste der rund 650 Messstellen mit ihren Klartextnamen und anderen Infos.

Ich hatte jetzt die Idee, dieses eine Monster-Archiv in mehrere Tabellen zu splitten. Dabei wäre eine Aufteilung nach Messstellen interessant, da ich mir dann das Feld "valueID" sparen kann, was der Datenmenge ganz gut tut. Da sicherlich aber auch hier und da Betrachtungen von Werten mehrerer Messstellen in einer Liste bzw. in einem Diagramm gefragt sind, würden dann ggf. größere UNION-Konstrukte nötig. Und es entstünden halt auch 650 Tabellen.
Alternativ wäre auch eine Teilung nach Datum denkbar, so dass man vielleicht pro Monat ein neues Archiv beginnt, das dann aber alle Messstellen beinhaltet. Dann gibt es die UNIONs bei Monatsgrenzüberschreitenden Auswertungen.

Da ich mit derart üppigen Satz-Mengen nicht die größte Erfahrung habe, würde ich gerne euch fragen, ob sich abschätzen lässt, welche Strategie die bessere wäre. Da nur maximal pro Minute neue Eintragungen sind (dann aber potenziell für alle 650 Messstellen am Stück), ist denke ich vor allem die Performance bei der Auswertung der Knackpunkt. Ich will die Leute nicht dazu verdammen, eine Grafik die Mittags gebraucht wird, vor der Frühstückspause abzufragen.

Ein paar Erfahrungswerte würden mir hier viel helfen. Vielen Dank schon mal!

[mkinzler]

Imho machst Du Dir durch die Aufteilung nur mehr Probleme als Du damit behebst. Die Anzahl der Datensätze in einer ist eigentlich egal, wenn die Indizes stimmen.

[himitsu]

Beim MySQL gibt es verschiedene Speicher-Engines, welche für bestimmte Zwecke optimaler sind.

So weit ich mich noch erinnere, gibt es Eine die speziell für's Logging gedacht ist und welche da auch weniger Speicherplatz benötigt.
Aber man sollte dann auch beachten, daß dort einige Features nicht vorhanden oder nur Teilweise unterstützt sind.
- eventuell keine Indize und daher keine/langsame Sortierung/Filterung/...
- eventuell keine (geprüften) Fremdschlüssel
- (möglicht) kein verändern vorhandener Daten
- selten/garnicht löschen (wenn dann eher alles nur nicht nur Teile)


Nja, bei den Datenmengen könnte man sich schon überrlegen, ob man Diese aufteilt.
- entweder die Tabelle von der DB partitionieren lassen
- oder vielleicht doch mehrere "kleine" Tabellen, für jeden Sensor, wo dann die spalten mit der immer gleichen Sensor-ID entfällt.

Auf jeden Fall sollte in die Tabellen keine sinnlose Record-ID-Spalte mit enthalten sein. (automatischer PrimaryKey)
Und die Datumswerte sollten durch eine fortlaufende ID ersetzt werden.
- entweder das Datum als Integer darstellen, so wie z.B. GetTickCount, wo dann je 5 Minuten ab einem bestimmten Datum um 1 hochgezählt wird
- oder es gibt eine extra Zeittabelle, wo eine Integer-ID für jedenen Datums/Zeit-Wert generiert wurde.



PS: Klingt das nicht nach einem Fall für ein Data-Warehouse?
Für die, welche auf den letzten Delphi-Tagen der Daniela zugehört haben.

[jobo]

Die Aufteilung in Tabellen je Meßstelle halte ich für schlecht.
Dafür gibt es im zweifel Partitioning (weiß nicht ob mySQL das kann)

Physikalisch kann man auch ein paar Dinge regulieren, wenn z.B. nie ein Update erfolgt, kann man den Füllgrad von daten und index pages auf 100 % setzen (auch hier weiß ich nicht, wie es bei mySQL genau ist)

ggF könnte man sich den PK Schlüssel sparen. Hier werden sowieso kumulierte Werte abgelegt, ein eigenständiger PK macht da wohl nicht viel Sinn. Dann eher einen kombinierten PK Schlüssel plus Index auf MessStation plus Datum, sollte ja bei 1 Minuten Intervallen reichen.
Diesen Index sollte man dann manuelle anlegen nicht automatisch per Konstraint.
Damit kann man etwas spielen/ausprobieren, ggF 2 separate Indizes mit Unique Constraint und verfügbare Indexvarianten testen.

[himitsu]

Wenn du schon Indize verwendest, dann eventuell so, daß sie sich schneller nutzen lassen?
Du greifst ja bestimmt entweder nur über die ValueID auf (alle) Werte eines Sensors zu, oder auf alle Sensorwerte in einem bestimmten Zeitraum und dementsprechen sollten dann doch die Indize gewählt werden.

Das ID-Feld in der Wertetabelle ist jedenfalls nutzlose Platzverschwendung.

SQL-Code:

CREATE TABLE Zeittabelle (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `time` TIMESTAMP NOT NULL UNIQUE
)

CREATE TABLE Quelltabelle (
  `id` SMALLINT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE,
  ...
)

CREATE TABLE WertTabelle (
  `valueID` SMALLINT NOT NULL REFERENCES Quelltabelle,  -- vielleicht doch INT, das wären dann schöne 4*4 = 16 Byte pro Datensatz plus den Index
  `timeID` INT NOT NULL REFERENCES Zeittabelle,
  `maxValue` FLOAT NOT NULL, --DEFAULT 0,
  `meanValue` FLOAT NOT NULL, --DEFAULT 0,
  PRIMARY KEY (`valueID`, `timeID`),
  KEY Index_2 (`timeID`)
)

Um die Größe der WertTabelle aufzuteilen:
http://dev.mysql.com/doc/refman/5.7/...titioning.html

Und noch etwas zu den Engines:
http://dev.mysql.com/doc/refman/5.1/...e-engines.html



Man könnte auch Werte zusammenfassen, was zwar Speicherplatz sparen würde, aber beim Auslesen und Auswerten gibt das erhöhten aufwand, da man dann die Spalten wieder auf mehrere Zeilen auftrennen/drehen müsste.
Also ich glaub nicht, daß es den Aufwand wert wäre.

z.B. die Werte für je 10 Sensoren pro Datensatz.
0 = valueID=0 bis valueID=9
1 = valueID=10 bis valueID=19
...

SQL-Code:

CREATE TABLE WertTabelle (
  `valueIndex` SMALLINT NOT NULL REFERENCES Quelltabelle,
  `timeID` INT NOT NULL REFERENCES Zeittabelle,
  `maxValue0` FLOAT NOT NULL DEFAULT 0,
  `meanValue0` FLOAT NOT NULL DEFAULT 0,
  `maxValue1` FLOAT NOT NULL DEFAULT 0,
  `meanValue1` FLOAT NOT NULL DEFAULT 0,
  ...
  `maxValue9` FLOAT NOT NULL DEFAULT 0,
  `meanValue9` FLOAT NOT NULL DEFAULT 0,
  PRIMARY KEY (`valueIndex`, `timeID`),
  KEY Index_2 (`timeID`)
)

[Blup]

Aufteilung in mehrere Datentabellen und Dateien:

1.Tabelle nimmt nur fortlaufend die aktuellen Daten entgegen.
Ein Prozess verarbeitet stündlich diese Daten für die Agregattabellen.
Damit entfallen unnötige Lesezugriffe, jeder Datensatz wird in dieser Tabelle genau einmal erzeugt, gelesen und gelöscht.

2.Tabelle mit stündlich zusammengefassten Werten
Ausreichend für grobe Auswertungen.

3.Tabelle mit minutengenau zusammengefassten Werten
Für Detailauswertungen.

Eventuell jedes Jahr eine neue Datenbankdatei, um Platz bei der Datensicherung zu sparen.

[Medium]

Uiui, viel Lesestoff, sehr fein! Danke euch schon mal! Morgen werde ich mich mit dem Thema hoffentlich wieder konzentriert befassen können, heute kam leider etwas dazwischen. (Daher bis jetzt keine Rückmeldung.)

Nur schon mal schnell, weil's mir beim Überfliegen auffiel: Die 4,5GB (ja, giga) waren bezogen auf den ganz ursprünglichen Plan vom Kunden, wirklich alle Messstellen sekündlich aufzunehmen. Zwar ist ein Datensatz nur 16 Byte groß, ich habe aber mal zum Test ein paar 100000 erzeugt, und geschaut was der alte MySQL Administrator als Tabellen- und Indexgröße ausgespuckt hat. Das war in Summe pro Datensatz dann ca. 82 Byte - warum auch immer. (Ich bin mir auch fast sicher, dass die Messmethode Käse ist, ich wollte es nur ein Mal schnell ganz grob über'n Daumen peilen.)
Da hieß es dann eben für sekündlich: 650*82*24*60*60 = 4392 GB, aber das ist nicht mehr relevant! Mit den revidierten Intervallen komme ich jetzt auf die o.g. 150000 Sätze pro Tag, was nach der genannten "Messmethode" rund 12MB am Tag füllt. Das ist denke ich völlig okay.

Mehr morgen!

[p80286]

Zitat von Medium:

Überschlagen fallen dabei etwa 150000 Messwerte pro Tag an, verteilt auf knapp 650 Messstellen.

Gehen wir mal von 100 Byte pro Datensatz aus (ich halte aber nicht viel von dieser Byte-Klauberei) dann sind das 150 MByte / Tag. wie Du auf 4,5 GByte kommst ist mir da schleierhaft.
Die Struktur Deiner Tabelle ist meiner Meinung nach durchaus in Ordnung, nur würde ich der ValueID den gleichen Typ verpassen wie der ID.

Gruß
K-H

[himitsu]

Bei den genannten 16 Byte sind es nur 2,2 MB ... vielleicht meinte er ja 4,5 MB und nicht GB?

[Dejan Vu]

Eins vorneweg: Nachdenken und sich einen abbrechen ist teurer als jedes TB.

Bei der Frage nach der Auswertung wirst Du wohl oder übel auf (Zeitraum in Tagen)x150k Daten kommen. Die Frage ist, ob das irgend eine Sau in der Auflösung benötigt....

Hier ist der Einsatz eines Datastores (also ein Teil eines DWH) imho die bessere Wahl. In einem Datastore sind die Daten (redundant) in der für Auswertungen notwendigen minimalen Granularität abgelegt. Braucht ihr die Daten auf die Sekunde genau?

Angenommen, ihr benötigt Auswertungen über mehrere Monate: Da wäre eine Granularität von 5 Minuten sicherlich vollkommen ausreichend, wenn Du mit Livedaten spielen willst, vielleicht noch gröber, also so alle 10-15 Minuten. Dann saugst Du dir pro Query ein paar 1000 Werte und kannst die in Memory in Echtzeit verarbeiten, auch mit nem iPad

Wenn Du Peaks innerhalb der Minute trotzdem sehen willst, dann enthält ein Datensatz der verdichteten Auswertungstabelle nicht nur die Anzahl der Messdaten, Mittelwert, Min,Max, etc., sondern auch Informationen über cpk, mean etc., vielleicht ein paar Splinekoeefizienten, eben alles was man so braucht, um Daten bewerten zu können.

Diese Tabelle fütterst Du in Echtzeit mit deinen Messdaten z.B. per Trigger oder Zeitgesteuert alle 5 Minuten...

Was Du also machst, ist (1) auf Speicherplatz zu pfeifen und lieber ne Platte mehr kaufen und (2) verdichtete und optimal gepimpte Tabellen für die jeweilige Fragestellung bez. den Auswertungskomplex zu erstellen. Der Trick dabei ist, eigentlich gar nichts mehr zur Queryzeit zu suchen oder zu aggregieren, sondern gleich 1:1 aus den Tabellen zu laden (wohlgemerkt: Die Archivdaten fässt Du gar nicht an)

Deine Archivdaten kannst Du dann ruhig so ablegen, wie Du Dir das vorgenommen hast. Das ist ja zweitrangig, wie genau das geht und ob das 100% optimal ist, denn deine Auswertetabellen sind um ein vielfaches schneller.

Wenn man bei der Übersicht in die Verläufe reinzoomen will, kannst Du dann einfach ab einer bestimmten Granularität einfach auf die Einzeldatensätze gehen. Da ziehst Du dir dann nur ein paar 1000 und bist wieder ratzfatzschnell.

Bei den verdichteten Auswertetabellen kommt das Star-Design zum Einsatz (im Gegensatz zum Snowflake-Design bei 3NF Produktionsdatenbanken). Alles redundant abgelegt, verbrät viel Speicher, ist aber performancetechnisch optimal.