Diskussion betreffend Wetterdaten und Datenbanken

[Fredy]

Hallo

Bei mir werden die 5 Minuten Daten, jeweils sofort auf einen MySQL Server geladen. (via ws_newdata.csv). Derzeit alle weiteren Auswertungen dann auf Basis dieser Tabelle:

Code Auswählen Erweitern

datetime datetime (Primary Key, index)
temp, decimal(4,1)
hum, decimal(4,1)
pressure, decimal(5,1)
rain, decimal(8,3)
windspeed, decimal(4,1)
winddir, decimal(4,1)
windgust, decimal(4,1)
dewpoint, decimal(4,1)
windchill, decimal(4,1)
temp2, decimal(4,1)

Feld Anzahl:
Die Felder dewpoint und windchill könnte man ja auch berechnen. Hab aber noch nicht getestet, wie sich das in Bezug auf die Performance auswirken würde.

Feldtypen:
Ich habe bis jetzt ebenfalls auf Integer Typen verzichtet. Ist mir irgendwie Sympatischer (Möchte hierzu aber noch gerne einen Performance Test machen.)
Hier wäre noch die Frage an die Profis, ob man statt Decimal auch FLOAT verwenden könnte, und wie das mit deren "Ungenauigkeiten" ist. (So wie ich es bis jetzt verstehe, sollte dies bei unseren Daten wohl keine Probleme verursachen?)
Für Werte wie Windrichtungen, könnte evtl. auch ein ENUM Typ passen. Hab das aber noch nicht zuende gedacht.

Tabellengrösse:
Nach 4 Jahren habe ich rund 400'000 Datensätze (22Mbyte inkl. 1 Index) in dieser Tabelle, und die meisten Auswertungen laufen noch im akzeptablem Zeitrahmen (auf meinem Host < 0.5sec).
Kann mir aber vorstellen, dass man da mit der Zeit engpässe erzeugt, vorallem in Bezug auf die Aufbereitung für eine Website. (Bei 1 Minuten Intervallen natürlich noch extremer).
Für meine persönlichen Auswertungen, wären die Abfragezeiten aber wohl noch lange schnell genug.

Tabellen Anzahl:
Für die Website arbeite ich daher daran, noch eine zweite Tabelle mit berechneten Tagesdaten (min/max/avg/sum) anzulegen. Diese würde wohl schon ausreichen, die allermeisten Performance Engpässe für die nächsten 50 Jahre zu beseitigen.
Als Test werden jetzt mal die Tagesdaten, nach jedem neu ankommenden Datensatz, für den aktuellen Tag neu berechnet. (Die Ausführungszeit für diese Aktualisierung ist im Bereich von 0.01 Sek.)
Zusätzlich berechne ich sämtliche Tage, einmal pro Tag komplett Neu. (Falls ich mal einem Übertragungsausfall nicht bemerke). Dies ist vielleicht etwas übertrieben und liesse sich sicher auch  wesentlich eleganter lösen

Mir stellt sich noch die Frage, ob parallel zur "endlos 5 Minuten Tabelle", eine Art Ringspeicher Tabelle Sinn machen würde. Die meisten Anfragen auf meiner Website betreffen Daten der letzten 24h.

Lokale Sicherung:
Ich sichere mir den WsWin Programmordner komplett innerhalb des normalen Backup. Möchte mir aber die Haupttabelle auch noch lokal anlegen.

Grüsse Fredy

[WernerWetter]

Meine Wetterstation läuft erst set ca. 2 1/2 Jahren und es sind ca. 7Mbyte.
Meine Daten (auch die Wetterdatenbank) sichere ich jeden Morgen auf einen Backup-Server. 

float, decimal, integer -> keine Ahnung was "besser" ist. Ggf. wäre decimal besser gewesen...

[wneudeck]

Hallo Fredy,
ich würde dewpoint und windchill nicht berechnen, denn ich denke, eine etwaige Ersparnis steht in keinem Verhältnis zuim Aufwand.
Ich habe allerdings einzelne Felder geringfügig anders bei mir dimensioniert:
z.B. Temperatur mit decimal (3,1) - Feuchte mit decimal(3,0) usw.
Warum Du bei Regen decimal(8,3) genommen hast, kann ich nicht ganz nachvollziehen.
Die Gesamtgröße der Datenbank hält sich bei mir in Grenzen. Dort sind nun die Daten seit 2000 (immerhin 15 Jahre) drin und das macht 47 mByte aus. Ich muss allerdings dazusagen, dass von 2000 - 2006 die Daten nur im 30-Minuten-Raster gespeichert wurden und erst dann im 5-Minuten-Intervall.
Aber das ist ja insgesamt für eine mysql-Datenbank noch keine Größe. Das merkt man eigentlich nur dann, wenn man in PHP etwas dilletantisch ist (ich bin da nicht so der große Könner) und gewisse Abfragen dann etwas primitiv verfasst.
Aber wenn ich beispielsweise die Maximaltemperatur über den gesamten Zeitraum abfrage, dauert es 0,1320 Sekunden (sql-Abfrage), also kein Grund zur Beunruhigung.
Weil ich gearde den letzten Beitrag noch sehe:
Für INT braucht man 4 Byte, für Decimal ein Byte pro Stelle und 2 Byte für Komma und Dezimalpunkt (weil bei decimal die Werte als Zeichenkette gespeichert werden - trotzdem kann man damit rechnen). Damit wären dann für decimal(3,1) 5 Byte notwendig.

[Fredy]

Warum Du bei Regen decimal(8,3) genommen hast, kann ich nicht ganz nachvollziehen.

Ich auch nicht  Habe die Decimal längen mal an die Realität angepasst und hat mir immerhin 2.6MByte gebracht

Hier ein paar Testläufe auf MySQL und Testtabelle mit 1M Zeilen.

FLOAT

Code Auswählen Erweitern

Daten 50,5 MiB
Index 13,8 MiB
Insgesamt 64,3 MiB
QUERY SPEED:
0.4385
0.4624
0.4914
0.4374
0.4751

DECIMAL

Code Auswählen Erweitern


Daten 42,9 MiB
Index 13,8 MiB
Insgesamt 51,9 MiB
QUERY SPEED:
1.0167
1.1387
1.0859
1.0717
1.0522

INTERGER (SMALLINT)

Code Auswählen Erweitern


Daten 31,5 MiB
Index 13,8 MiB
Insgesamt 45,2 MiB
QUERY SPEED:
0.6062
0.5798
0.5843
0.5822
0.5926

Ausgeführte Abfrage:

Code Auswählen Erweitern


SELECT MAX(temp), MIN(temp), AVG(temp)
FROM wettertabelle
Group by DATE(datetime)
ORDER BY DATE(datetime)


Bleibt die Frage was die Zurückrechnung ( /10 ) von den SMALLINT kostet:

Code Auswählen Erweitern


Query:
SELECT MAX(temp)/10, MIN(temp)/10, AVG(temp)/10
FROM wettertabelle
Group by DATE(datetime)
ORDER BY DATE(datetime)

QUERY SPEED:
0.6019
0.6039
0.6208
0.6150
0.6385


Die Umstellung auf FLOAT Zahlen, hat mich gegenüber Decimal rund 24% mehr Speicher gekostet. Ist aber mit Abstand am schnellsten.
Ich dachte FLOAT hätte 4 Byte, dann müsste diese doch kleiner sein als DECIMAL ?
Laut manual wird DECIMAL seit v.5.0.3 nicht mehr als String gespeichert. Jetzt Binär, und so wie ich es verstanden habe, sind es jetzt 4Byte für ein (3,1). (Dann sollte doch aber FLOAT und DECIMAL etwa gleich gross sein?)
SMALLINT mit 2 Bytes ist wie erwartet am kleinsten, langsamer als FLOAT, aber schneller als DECIMAL.

Grüsse Fredy

[schappenberg]

Was mich zu diesem Thema interessieren würde:
warum nehmt ihr alle MySQL, Oracle, ...?
Weil ihr damit schon Erfahrung habt, oder hat das andere Gründe?
Ich habe mich mit dem Thema auch schon beschäftigt, und mir gefallen die RoundRobin Tabellen sehr gut, Einzelwerte und Komprimierung nach z. B. einen Monat auf Mittel-, Min.- und Maximalwerte. Ich lasse mich gerne eines besseren Belehren, aber braucht man die Einzelwerte wirklich nach Jahren noch?
Sonnige Grüße
schappenberg

[leknilk0815]

...aber braucht man die Einzelwerte wirklich nach Jahren noch?

Servus,
wenn Du nach Jahren mal einen Temperaturverlauf über einen bestimmten Zeitraum wissen willst, wie sollte das sonst machbar sein?
Was würde es z.B. nützen, wenn man beim Bankkonto wissen würde, wieviel max. drauf war und Minimum, wenn ich nach einer bestimmten Buchung suche?
Beim heutigen Speicherpreis und der Rechnerleistung ist das auch kein Problem mehr, sofern die DB vernünftig aufgebaut ist, und darum gehts ja wohl in dem Fred...

[TheWeather]

Der Datentyp float hat generell den Vorteil, dass man in einer geringen Anzahl von 4 Bytes (32 Bit) einen "riesigen" Zahlenbereich abbbilden kann und dass die Konvertierung hin und zurück auf Prozessorebene fast ausschließlich aus schnellen Schiebeoperationen (Mantisse für /2 nach rechts, Exponent für *2 nach links) und umgekehrt und binären Additionen besteht.

Ein kleiner Nachteil ist, dass viele Dezimalwerte (Beispiel oben in Wikipedia 18,4), nicht ohne Genauigkeitsverlust hin und zurück gewandelt werden können, wobei der (zwar geringe) Fehler im letzten Bit der Mantisse steckt. Man kommt also bei Darstellungen des Datentyps float in der Regel nicht um eine Rundung auf die gewünschten Nachkommastellen herum, um wieder den ursprünglichen Dezimalwert zu erhalten, bei bis zu 7 Nachkommastellen (NK) fällt der Fehler der Konvertierung beim späteren Runden auf die gleiche Stellenzahl wieder raus, so dass float für alle Dezimalwerte mit bis zu 7 NK ohne sichtbaren Fehler bleibt.

Beim Datentyp "decimal" werden bei MySQL 5.0.3 anscheinend (ich habe mich mit diesem Datentyp noch nie beschäftigt) wenigstens 4 Byte, die den Vorkommwert binär abbilden, benötigt (entspräche also int32). Wenn Nachkommastellen mitgeführt werden, benötigt jede NK zusätzlich (je nach Anzahl der NK gestaffelt) ein weiteres Byte, soweit ich es verstanden habe. Deswegen müssten wohl stets auch 4 Byte beim Datentyp "decimal" verbraten werden - heißt automatisch "decimal" verwendet bei vorhandenen NK immer mehr als 4 Bytes (so ganz durchgestiegen bin ich da in der Kürze noch nicht). Das Konvertieren hin und zurück, völlig gleich, wie das Format tatsächlich aufgebaut ist, wird aber wesentlich länger dauern, da binär abgelegte Ziffern zunächst wieder in Dezimalwerte konvertiert und nun dezimal um den Faktor 10 verschoben wieder addiert werden müssen, was den Rechenaufwand schon mal enorm in die Höhe treibt.

Ich würde daher für eine möglichst rein binäre Ablage plädieren, da sie den geringsten Speicherbedarf aufweist. Der Datentyp float hat gegenüber "decimal" keinen deutlichen Nachteil, solange es nicht um Währungsumrechnungen geht, wo es bei großen Transaktionen um Bruchteile von 1/1000 Cent geht (gehen könnte).

Der Datentyp float funktioniert bei allen meteorologisch interessanten Größen (Temperaturen, Feuchte, Luftdruck) im kompletten Wertebereich und auch bis zu 7 NK (was niemand wirklich braucht) bei Hin- und Rückwandlung von float in oder aus einer Datenbank. Bei der Anzeige von float-Werten muss man nur ans Runden denken, damit keine Werte wie z.B. 18,39999961853 °C angezeigt werden, sonder z.B. 18,4 °C, wie man ursprünglich abzuspeichern gedachte.

Soweit erst mal nur zur Performance bei der Verwendung verschiedener Datentypen.

Bei Windrichtung und einigen anderen Kenngrößen kann man überlegen, sich tatsächlich auf weniger Bytes an Speicherbedarf (char) einzulassen, wobei die Auswertung per SQL-Abfrage da wieder zusätzliche Zeit kosten kann, anstatt ein paar Byte gespart zu haben.

Wenn's also um die Dichte der Datenspeicherung geht (möglichst wenig Speicherplatz zum Abspeichern aller relevanten Informationen), ist binär, hier speziell float "eigentlich" immer von Vorteil. Wenn's um die schnelle Auswertung geht, können auch mal 1-Byte oder 2-Byte zur Speicherung (char) sinnvoll sein.

Nachteil binärer Datenbanken ist halt stets, dass man sie nicht mehr mit einem einfachen Editor auch nur ansatzweise (so wie bei .dbf) ansehen kann. Das ist der Pfand für deren Effektivität.

Gruß Hans
Beiträge zusammengeführt, weil der Autor sich selbst geantwortet hat statt seinen letzten Beitrag zu ändern: 22.01.2015, 14:50:15
Hi Schappenberg,

Was mich zu diesem Thema interessieren würde:
warum nehmt ihr alle MySQL, Oracle, ...?Weil ihr damit schon Erfahrung habt, oder hat das andere Gründe?

Mir persönlich sind filebasierte Datenbanken auch lieber als serverbasierte (sofern man die überhaupt benötigen sollte).

Filbebasierene Datenbanken (also Dateien oder eine Sammlung auf dem lokalen PC) sind mir generell lieber, als irgendeine Serverbasierende. Warum MySQL einen besonderen Standard erreicht haben sollte, dass ihn auch Einzelanwender bevorzugt verwenden, bleibt mir bislang selbst verborgen. Möglicherweise hängt es mit der Veröffentlichung von Daten im Internet zusammen, wo serverbasierende Lösungen quasi die einzige Möglichkeit sind, mittels geeigneter Tools und einem Script Daten interaktiv für Interessierte User verfügbar zu machen. Für "zuHause" reicht eine einfach Datenbank oder gar die von WsWin angelegten ".csv"-Dateien, um sich selbst alle möglichen Statistiken anzeigen zu lassen.

Schwieriger wird's, wenn eine Auswertung von WsWin selbst nicht mehr unterstützt wird, weil man dann auf Excel oder Derivate ausweichen muss und sich erstmal mit der Syntax zur Gewinnung der relevanten Daten aus einer .csv" beschäftigen muss, bevor eine "echte" Auswertung druckbar oder als Tabelle vorliegt.

OK - .csv ist kein Datenbankformat sondern im Wesentlichen nur für den generalisierten Datenaustausch gedacht. Weitläufig könnte man aber auch .csv-Dateien als eine gewisse Form einer Datenbank ansehen. Wer sich damit beschäftigt, erhält aus einer .csv die gleichen Informationen, die auch in einer Datenbank verfügbar wären.

Knackpunkt ist im Wesentlichen, dass man Informationen aus einer .csv-Datei nicht mit einem Zeiger auf das gewünschte Ziel sofort als Datenquelle übernehmen kann, weil .csv-Dateien für jeden Datensatz unterschiedliche Feldlängen und damit auch unterschiedlche Datensatzlängen für jeden Datensatz beinhalten.

Ich weiß nun nicht, wie tief Du einsteigen wolltest, aber mit filebasierten Datenbanken scheinst Du Dich ja generell auszukennen. Die von Dir zitierten "RoundRobin"-Tabellen sagen mir nun persönlich garnichts - ich wurde nie damit konfrontiert.

Falls auch diese filebasierend sind, dürften sie auch für Auswertungen auf dem eigenen PC geeignet sein - nur, wie gesagt, das sagt mir momentan nichts ...

Gruß Hans

[joachimF]

Zur Info "Tabellengröße"
Seit dem 6.12.2014 werden die WsWin-Wetterdaten ( 18 an der Zahl, csv ) minütlich in eine MySql-DB abgelegt (Webserver) und dort wöchentlich ein Backup angelegt. (Grundlage Fredy-Tut).
Bislang haben sich rund 70.000 Datensätze angesammelt und nehmen ca. 10 MB ein. Mir wird 'schwindelig' , wenn das Wachstum so weiter geht.

[leknilk0815]

Das sagt so überhaupt nichts aus, da die Anfangsgröße nicht bekannt ist.
Über 10MB durfte man sich zu Zeiten von WeatherProfessional und PostGres nicht aufregen, da konnte man locker 1-2 Nullen dranhängen...

[wneudeck]

Hallo,

werden die WsWin-Wetterdaten ( 18 an der Zahl, csv ) minütlich

Dazu nur kurz: es muss jeder selbst entscheiden, was für ihn wichtig ist. Das fängt beim Speicherintervall an. Ist ein Intervall von einer Minute wichtig oder reichen auch 5 Minuten? (mir reichen 5 Minuten).
Muss ich alle Werte, die die Vantage übermittelt oder die in WSWIN zur Verfügung stehen, in der Datenbank ablegen? In meiner csv-Datei stehen 22 Werte in einnm Datensatz, aber die brauche ich nicht alle und deshalb übertrage ich sie nicht alle.