Tabellen für viele kleine Datensätze optimieren

[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!

[Sir Rufo]

Wofür benötigst du denn `id` int(10) unsigned NOT NULL auto_increment, ?

Eigentlich ist die doch für nix gut ...

• Ein PK würde sich anbieten auf valueID und vdate.
• Statt InnoDB eher MyISAM nehmen.
• Partitions auf die Jahre legen (eine monatliche Unterteilung geht auch mit Extract( YEAR_MONTH FROM vdate ) )

SQL-Code:

CREATE TABLE `history` (
  `valueID` smallint(5) unsigned NOT NULL,
  `vdate` datetime NOT NULL,
  `maxValue` float NOT NULL,
  `meanValue` float NOT NULL,
  PRIMARY KEY (`valueID`,`vdate`)
) 
PARTITION BY RANGE(YEAR(valueDate)) 
PARTITIONS 3 (
  PARTITION `p2012` VALUES LESS THAN (2013) ENGINE=MyISAM , 
  PARTITION `p2013` VALUES LESS THAN (2014) ENGINE=MyISAM , 
  PARTITION `pCurrent` VALUES LESS THAN (MAXVALUE) ENGINE=MyISAM ) 
ENGINE=MyISAM

Um für das nächste Jahr eine neue Partition anzufügen einfach diesen Befehl ausführen

SQL-Code:

ALTER TABLE `values` REORGANIZE PARTITION pCurrent INTO
( PARTITION p2014 VALUES LESS THAN (2015),
   PARTITION pCurrent VALUES LESS THAN MAXVALUE )

Mit den Partitions hat sich dann das Problem mit der Datensicherung auch erledigt, denn nun gibt es pro Partition separate Dateien und wenn nur aktuelle Datensätze angefügt werden, dann bleiben die alten Dateien unberührt und müssen eben nicht mehr gesichert werden.

[Dejan Vu]

Zitat von Sir Rufo:

Wofür benötigst du denn `id` int(10) unsigned NOT NULL auto_increment, ?

Weil es allgemein gesehen besser ist, für einen Datensatz einen eindeutigen anonymen Schlüssel zu haben. Ob es hier besser ist? Wenn man -wie vermutlich hier- die Daten nie ändert, sondern nur anfügt, braucht man das tatsächlich nicht. Da wird dann aber auch überhaupt kein PK benötigt.

Da man nur über vDate und ValueID abfragt, sollte ein Index reichen: ValueID+vDate (das meint Sir Rufo vermutlich mit 'PK über ValueID und vDate'. Hier steht jedoch nicht der Primary Key im Vordergrund, sondern ein nonunique index. Denn: Falls vDate in der Granularität nicht pikosekundengenau ist, wäre es denkbar, zwei Messwerte zum gleichen Zeitpunkt zu haben. Zumindest ausschließen kann man das nicht.

MySQL kennt vermutlich keinen Clustered Index, so wie MS SQL-Server. Gäbe es jedoch ein ähnliches Konzept, wäre ein Clustered Index über vDate+ValueID noch schneller, da bei den Abfragen nur ein Clustered Index-Seek/Scan ausgeführt wird.

Wenn man beim Auswerten über einen Zeitraum auch 'nur Morgens zwischen 8-10' abfragen will, dann Datum und Uhrzeit getrennt, sonst als Timestamp.

Grundsätzlich würde ich trotzdem das Konzept mit den redundanten Aggregationstabellen verfolgen, es macht einfach Spaß, in Echtzeit in die Daten zu zoomen und zu scrollen, ohne irgendwelche Last zu produzieren. Man sollte ja auch bedenken, das man entweder eine separate Auswerte-DB erstellt, oder -wenn man das nicht macht- den Server mit Auswertungen nicht zu sehr belastet. Das wäre dann ziemlich blöd, wenn er gerade wie ein blöder Daten fressen soll, aber nicht kann, weil eine Tabelle wegen einer Auswertung gelocked ist.

Zitat:

Mit den Partitions hat sich dann das Problem mit der Datensicherung auch erledigt,...

Top.

[Medium]

Södale, ihr habt mich ganz gut davon überzeugen können, dass eine Spaltung in mehrere Tabellen nicht das Gelbe vom Ei ist. Den aktuellen PK, also das autoinc Feld, kann ich hier tatsächlich weg lassen. Hab mir das angewöhnt, weil ich es für unsere Hauptanwendung zwingend brauche, das hier ist aber ausser der Reihe. Der fliegt also schon mal.

Partitioning klingt sehr praktisch, danke auch für das Beispiel als Code. Gelesen habe ich ein wenig drüber, genutzt bisher nicht. Das einzige was mich an der gezeigten Variante stört ist, dass ich quasi jedes Jahr ein "Pflege-Statement" ausführen muss, was mit Sicherheit spätestens nach dem 2. Jahr im Tagesgeschäft unter geht. Kann man es so deichseln, dass mit Beginn eines neuen Jahres automatisch eine neue Partition begonnen wird?

Bezüglich Index: Hier fehlt mir auch noch ein wenig Erfahrung um genau sagen zu können, welche Indizes ich hier wirklich brauche. Einen PK braucht es glaube ich wirklich nicht, aber wenn, dass wäre vdate+valueID ein guter Kandidat. Die kürzesten Intervalle von Einträgen mit derselben valueID sind 1 Minute, also garantiert verschiedene Timestamps.
Bisher habe ich recht naiv immer über die einzelnen Felder einen Index angelegt, die ich nachher in meinen WHERE-Klauseln benutze. Das wäre in diesem Fall vdate und valueID, auch in Kombination. Es wird nachher sicherlich ein Statement der folgenden Struktur genutzt um die Daten zu holen:

SQL-Code:

SELECT
  h.vdate, h.valueID, h.maxValue, h.meanValue, v.valueName 
FROM 
  history h JOIN valueList v 
    ON h.valueID = v.id 
WHERE 
  (h.vdate BETWEEN :sDate AND :eDate) AND 
  (v.valueID IN ({idListe}))

Edit: Der Join macht eigentlich kaum Sinn, da ohnehin bei der Ausgabe nach valueID, vdate sortiert werden sollte. Die Messstellennamen kann ich mir also auch billiger in einem separaten Statement holen und als Überschrift/Legende einsetzen. Die müssen nicht bei jedem Datensatz einzeln bei stehen.

Ist es da besser einen kombinierten Index auf vdate und valueID zu legen, oder jedes Feld seinen eigenen? Oder gar beides? Welche Art von Index eigenet sich hier? (Beide Felder sind für sich genommen non-unique, in Kombi aber unique.) Mein MySQL Tabellen-Tool bietet mir hier die Optionen:
Index Kind: Index, Primary, Unique, Fulltext, Spatial
Index Type: Default, BTree, Hash, RTree
Ich habe mich bisher schämlich wenig damit befasst, welche Index-Arten für welche Fälle geeignet sind

Die angesprochene Trennung von Datum und Uhrzeit wird nicht nötig sein. Wenn, dann werden immer komplette Zeiträume am Stück abgefragt.

Eine separate Tabelle mit Daten zur Anzeige, und Umschaltung bei Zoom klingt zunächst sehr nett, aber ich weiss nicht, ob ich es mir leisten kann auf Dauer mehrere Granularitäten vollständig vorzuhalten. Klar ist, dass ich bei einem Zoom, der pro Pixel im Graphen z.B. 30min entspricht nicht alle minütlichen Werte übers Netzwerk jagen lassen wollen würde. Das Problem habe ich mir bisher nicht genauer angesehen, und hatte gehofft eine Möglichkeit zu finden vielleicht angeben zu können, dass ich nur jeden 2. oder 3. Datensatz einer Ergebnismenge vom Server bekomme. Gibt es sowas?
Wenn nicht, werden redundante Tabellen leider doch wieder interesant, weil vermutlich die letzte Möglichkeit dann.

Danke zwischendurch schon mal für die rege und produktive Teilnahme!

[Sir Rufo]

Du kannst beim MySQL Events definieren, die zu einem bestimmten Zeitpunkt laufen, also erstellst du dir einen Event, der einmal im Jahr losläuft und die entsprechende Partition neu erstellt.

Hier auch ein entsprechender SO-Beitrag

Der Pflegeaufwand geht also auch gegen Null

[Dejan Vu]

Zitat von Medium:

Ist es da besser einen

Es wird nur ein Index verwendet, ergo dürfte der kombinierte Index der richtige sein. In welcher Reihenfolge? ich würde ValueID+vDate nehmen, wenn Du nur von wenigen der 650 Stationen Werte über einen Zeitraum haben möchtest. Aber *wetten* würde ich nicht.

Zitat:

... Gibt es sowas? Wenn nicht, werden redundante Tabellen leider doch wieder interesant, weil vermutlich die letzte Möglichkeit dann.

Wieso eigentlich 'leider'? Das ist mit ein paar Triggern oder cron-Jobs gemacht (also alle paar minuten die Daten der letzten paar Minuten aggregieren und in die Reporttabellen schubsen)

[jobo]

Zitat von Medium:

Ist es da besser einen kombinierten Index auf vdate und valueID zu legen, oder jedes Feld seinen eigenen? Oder gar beides? Welche Art von Index eigenet sich hier? (Beide Felder sind für sich genommen non-unique, in Kombi aber unique.) Mein MySQL Tabellen-Tool bietet mir hier die Optionen:
Index Kind: Index, Primary, Unique, Fulltext, Spatial
Index Type: Default, BTree, Hash, RTree
Ich habe mich bisher schämlich wenig damit befasst, welche Index-Arten für welche Fälle geeignet sind

Ich würde es mit getrennten Indizes machen. Das ist flexibler. Ein kombinierte Index greift u.U. nicht, wenn weitere Bedingungen ins Spiel kommen.
Die uniqueness kannst Du separat per constraint definieren. NOrmalerweise tuen RDBMS einem den "Gefallen" abhängig vom Constraint direkt auch einen Index zu bauen, aber das kann man unterbinden, löschen, ändern.

Zitat von himitsu:

Aber bei vielen Daten ist ein View doch auch nicht so gut, wenn er erst stundenlang rumrechnen würde, bei jeder Abfrage.

Das kann man pauschal nicht sagen. Ein View ändert per se nichts an der Abfragegeschwindigkeit.
Ausnahme wäre bspw. wenn ein Abfrage Paramter besser "innerhalb eines Views" aufgehoben ist, weil er dort eine größere Selektivität entfaltet (stärkere Datenreduktion bringt, Folgekosten spart, ..)

Zitat von Medium:

Jetzt machst du mich aber spontan unglücklich,
..
Bei Views wüsste ich jetzt nicht so genau, wie ich die Anforderung erfüllen sollte, die in dem längeren Kommentar beschrieben ist. Und Views werden doch auch dynamisch ausgeführt oder? Die Abfrage auf die originalen "feinen" Daten würde damit ja trotzdem nötig werden. Zwar dann verdichtet über's Netzwerk gehen, aber der Server hätte dennoch die volle Arbeit zu tragen. Bin da skeptisch.

Ein Materialized View, der für kumulierte Daten verwendet werden könnte ist m.E. auch nicht unbedingt effizient. Kommt drauf an, wie das Refresh arbeitet. In Deinem Fall ist klar, es müssen immer nur Daten nachgetragen werden, nicht eine ganzer Mat.View refreshed werden.
Das kann man auch gezielt je Tag oder so manuell in eine eigene Tabelle kippen. Falls dort Indizes definiert sind, dann eben die Indizes disablen und nachher wieder aktualisieren, ist etwas schneller.