Diskussion betreffend Wetterdaten und Datenbanken

[Fredy]

Hallo 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?

Kannte RoundRobin bisher nur vom DNS. Setzt du ein solches System ein? Kannst du ein paar Worte dazu schreiben?


Hallo TheWeather

Vielen Dank für deine interessanten Erklärungen zu den Datentypen.
Es würde auch erklären warum DECIMAL, zumindest in Performance, am schlechtesten abgeschlossen hatte. Überrascht hat mich der grössere Speicherbedarf meiner FLOAT Test-Tabelle gegenüber der DECIMAL Tabelle. (Vielleicht habe ich aber auch einen Fehler im Testablauf. Geh da noch mal über die Bücher.)

Da mir Speicherplatz in der Grössenordnung noch nicht wirklich relevant scheint, werde ich vermutlich zugunsten der doch erheblich höherern Performance, testweise eine FLOAT Tabelle mitführen. So kann ich zwischen den Tabellen wechseln und weitere Erfahrungen Sammeln.

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.

Anbedracht dessen ist es wohl am sinnvollsten, einfach mehrgleisig zu fahren (zumindest was die lokale Sicherung betrifft:
Format optimiert auf maximale Kompatibilität (.csv ?)
Format optimiert auf Auswertung und Performance (Datenbank? welche? typen?)
Format optimiert auf simple (lokale) Auswertbarkeit ( excel u.ä. ?)
(und evtl. zusätzlich eines auf geringsten Platzbedarf)

Grüsse Fredy
Beiträge zusammengeführt, weil der Autor sich selbst geantwortet hat statt seinen letzten Beitrag zu ändern: 23.01.2015, 00:23:19
Ich denke es ist Grundlegend zu unterscheiden, zwischen lokaler Sicherung und Datenbereitstellung für eine höhere Anzahl an Abfragen bei vernünftiger Ausführungszeit (z.B. Website).

Für meine persönlichen Auswertung ist mir die Dateigrösse und Abfragegeschwindigkeit nicht so wichtig. Hier möchte ich vor allem alle Daten verfügbar haben. (z.b auch Werte wie Innentemperatur etc.)
Das Lokal verfügbare Datenmaterial, sollte sich primär ohne Umwege mit dem Mittel der Wahl auswerten lassen. (SQL, Excel, WsWin etc.)

Auf der Website steht für mich vorallem die Performance im Vordergrund. Hier gilt es abzuwägen, welche Daten ich wie speichern und abfragen möchte.
Ich habe für mich den Anspruch, 5 Minutenwerte über den gesamten Zeitraum ausgeben zu können. Es macht aber selbstverständlich keinen Sinn, z.B. 5 Minuten Werte in einem Jahresdiagramm darstellen zu wollen. Ich grenze hier die vom Anwender möglichen Abfragen ein. (5min Werte max. 30 Tage, Stundenwerte max. 3 Monate, etc. (Ohne Zoom im Diagramm, würden natürlich auch 5min für 30 Tage keinen Sinn machen.)

Am schönsten wäre da natürlich eine komplett Dynamische Lösung, welche je nach Zoomstufe, die benötigten Daten nachladen kann.

Grüsse Fredy
Beiträge zusammengeführt, weil der Autor sich selbst geantwortet hat statt seinen letzten Beitrag zu ändern: 23.01.2015, 00:39:42

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.

Hallo Joachim, wie sieht den deine Tabellenstruktur derzeit aus? (welche Felder, welcher Typ und Länge)

Gruss Fredy

[schappenberg]

Da ich absoluter Neuling bin was Wetterbeobachtung angeht, habe ich mir erst mal bestehende Anleitungen aus dem Netz besorgt. U.A. war da eine Anleitung die ich mit meinem Raspi und einer BMP085 Platine 1:1 nachgebaut habe, inkl. Software mit einer RoundRobin Datenbank.

Die RoudRobin Datenbank ist eine Datenbank, die bei der Definition schon eine fixe Anzahl an Datensätzen zugewiesen bekommt. Sobald die Datensätze belegt sind werden die alten wieder überschrieben. Außerdem können Daten nach einer definierten Zeit auf einen Min., Max. und Durchschnittswert komprimiert werden. Das Schöne an RRD sind mMn aber die eingebauten Grafikfunktionen. Genaueres siehe Wikipedia, folgende hervorragende Seite und natürlich die offizielle Seite.

Ich habe einige Wochen lang in meinem Büro die Temperatur und den Luftdruck aufgezeichnet und von den gewonnenen Daten Grafiken generieren lassen. Das Ganze ist auf dem Raspi ohne Probleme gelaufen, jetzt folgt natürlich die Überlegung das Setting mit zusätzlichen Sensoren (ev. dem ELV KS300) auszubauen.

Da ich mich aber mit anderen Datenbanken nicht auskenne und ich auch selber noch nicht genau weiß was ich eigentlich will, verfolge ich solche Diskussionen wie diese sehr gern und hoffe noch viel für mich zu lernen.

Regnerische Grüße
schappenberg

[Jürgen]

Hallo,

- Wie sind eure Konzepte der langjährigen Sicherung von Wetterdaten?
- Welche Datenbanksysteme nutzt ihr (offline/online)?
- Wie sehen eure Tabellenstrukturen aus?
- Wieviele Tabellen sind sinnvoll?

Ich habe ein 'zweistufiges' System. In einer ersten Datenbank (mysql) werden die Daten im Minutenintervall gespeichert, wie sie von der Wetterstation kommen. Da ich eine VP als Wetterstation betreibe, gips zusätzlich zu der Minutenwert-Tabelle eine MinMax-Tabelle, welche die MinMax-Werte direkt von der Wetterstation speichert.

Für die Homepage werden die Daten auf meinem lokalen Wetterrechner vorverarbeitet und in einem 2. Satz Tabellen gespeichert. Hier gibt es
- laufende Daten im 15-Minuten-Raster
- insgesamt 4 Minmax-Tabellen (Tag,Monat, Jahr, Alltime)
In diesen Tabellen sind die Daten schon woweit vorgekaut, daß auf der Homepage nur noch selektiert und angezeigt werden muß.

Bzgl. der Datentypen hatte ich mich damals auch schon länger beschäftigt, vorallem vor dem Hintergund, daß mein Provider max. 100Mb/DB erlaubte. Da war ich ziemlich am Grübeln, die ich die Tabellen vorallem platzsparend aufbaue. Inzwischen kann ich aber 1Gb/DB speichern, weshalb ich das erst einmal in den Hintergund gestellt habe. Momentan verwende ich Floats.

Zusätzlich werden die Daten noch in reine Textdateien geschrieben, pro Tag eine. Diese werden dann auch gesichtert (auf USB-Platten, Sticks, CD's).

Meine Software ist komplett selbst geschrieben und läuft unter Linux.

So sieht dann eine Tabestabelle aus:   klick

und so eine Monatsübersicht:  klack

Achtung: das ist noch eine Testseite

warum nehmt ihr alle MySQL, Oracle, ...?
Weil ihr damit schon Erfahrung habt, oder hat das andere Gründe?

Ich verwende Mysql (bzw. inzwischen MariaDB, weil MySQL von Oracle aufgekauft wurde ....  Klick.Wikipedia), wel es bei meiner Linux-Distribution kostenlos dabei ist und auch später auf meinem Webspace zur Verfügung steht. Weiterhin komme ich mit PHP sehr einfach an die Datenbanken heran.

[Fredy]

Hallo

Ich habe mal einige Tests in Bezug auf Tabellengrösse und Abfragegeschwindigkeit gemacht.

Testtabelle:
- Datenbanksystem ist MySQL 5.5.35
- Jeweils 1'000'000 Datensätze, gefüllt mit zufälligen Werten zwischen -999.9 - 999.9(4,1) oder -99.9 - 99.9 (3,1).
- Die Testtabellen haben ein "datetime" sowie 20 "decimal", 20 "float" oder 20 "SMALLINT" Spalten. Die Spalte "datetime" hat einen Index.
- (1'000'000 Datensätze entsprechen ca. 10 Jahre Aufzeichnung bei 5 Minuten Intervallen oder ca. 2 Jahre bei 1 Min. Intervallen)

Tabellengrösse:


 
   
   
   
   
   
   
   

Table Type	Field Size	Rows	Table Size	Index Size	Full Size
DECIMAL	3,1	1000000	46.7	13.8	60.5
DECIMAL	4,1	1000000	65.8	13.8	79.6
FLOAT	3,1	1000000	84.9	13.8	98.7
FLOAT	4,1	1000000	84.9	13.8	98.7
FLOAT (innoDB)	4,1	1000000	106.6	0	106.6
SMALLINT	4	1000000	46.7	13.8	60.5

Abfragegeschwindigkeit:


 
   
   
   
   
   
   
   
   

Table Type	Field Size	Query 1	Query 2	Query 3	Query 4	Query 5
DECIMAL	3,1	0.33	0.39	7.25	18.7	18.7
DECIMAL	4,1	0.33	0.39	7.35	18.7	18.7
FLOAT	3,1	0.35	0.25	1.23	3.9	3.9
FLOAT	4,1	0.35	0.25	1.25	3.9	3.9
FLOAT (innoDB)	4,1	1.59	0.78	2.61	5.3	5.3
SMALL INT	4	0.32	0.23	5.87	8.9	9.08
SMALL INT /10	4	0.33	0.25	5.94	9.15	9.21

Query 1:

Code Auswählen Erweitern


SELECT *
FROM wettertabelle
WHERE DATE(datetime) = "2006-10-11"
ORDER BY DATE(datetime)


Query 2:

Code Auswählen Erweitern


SELECT MAX(temp)
FROM wettertabelle
ORDER BY MAX(temp)


Query 3:

Code Auswählen Erweitern


SELECT DATE(datetime), SUM(temp),SUM(temp2), SUM(temp3), SUM(temp4), SUM(temp5), SUM(temp6), SUM(temp7), SUM(temp8), SUM(temp9), SUM(temp10), SUM(temp11), SUM(temp12), SUM(temp13), SUM(temp14), SUM(temp15), SUM(temp16), SUM(temp17), SUM(temp18), SUM(temp19), SUM(temp20)
FROM wettertabelle
GROUP BY DATE(datetime)
ORDER BY DATE(datetime)


Query 4:

Code Auswählen Erweitern


SELECT DATE(datetime), MAX(temp), MIN(temp), AVG(temp), MAX(temp2), MIN(temp2), AVG(temp2), MAX(temp3), MIN(temp3), AVG(temp3), MAX(temp4), MIN(temp4), AVG(temp4), MAX(temp5), MIN(temp5), AVG(temp5), MAX(temp6), MIN(temp6), AVG(temp6), MAX(temp7), MIN(temp7), AVG(temp7), MAX(temp8), MIN(temp8), AVG(temp8), MAX(temp9), MIN(temp9), AVG(temp9), MAX(temp10), MIN(temp10), AVG(temp10), MAX(temp11), MIN(temp11), AVG(temp11), MAX(temp12), MIN(temp12), AVG(temp12), MAX(temp13), MIN(temp13), AVG(temp13), MAX(temp14), MIN(temp14), AVG(temp14), MAX(temp15), MIN(temp15), AVG(temp15), MAX(temp16), MIN(temp16), AVG(temp16), MAX(temp17), MIN(temp17), AVG(temp17), MAX(temp18), MIN(temp18), AVG(temp18), MAX(temp19), MIN(temp19), AVG(temp19), MAX(temp20), MIN(temp20), AVG(temp20)
FROM wettertabelle
GROUP BY DATE(datetime)
ORDER BY DATE(datetime)


Query 5:

Code Auswählen Erweitern


SELECT DATE(datetime), ROUND(MAX(temp),0), ROUND(MIN(temp),0), ROUND(AVG(temp),0), ROUND(MAX(temp2),0), ROUND(MIN(temp2),0), ROUND(AVG(temp2),0), ROUND(MAX(temp3),0), ROUND(MIN(temp3),0), ROUND(AVG(temp3),0), ROUND(MAX(temp4),0), ROUND(MIN(temp4),0), ROUND(AVG(temp4),0), ROUND(MAX(temp5),0), ROUND(MIN(temp5),0), ROUND(AVG(temp5),0), ROUND(MAX(temp6),0), ROUND(MIN(temp6),0), ROUND(AVG(temp6),0), ROUND(MAX(temp7),0), ROUND(MIN(temp7),0), ROUND(AVG(temp7),0), ROUND(MAX(temp8),0), ROUND(MIN(temp8),0), ROUND(AVG(temp8),0), ROUND(MAX(temp9),0), ROUND(MIN(temp9),0), ROUND(AVG(temp9),0), ROUND(MAX(temp10),0), ROUND(MIN(temp10),0), ROUND(AVG(temp10),0), ROUND(MAX(temp11),0), ROUND(MIN(temp11),0), ROUND(AVG(temp11),0), ROUND(MAX(temp12),0), ROUND(MIN(temp12),0), ROUND(AVG(temp12),0), ROUND(MAX(temp13),0), ROUND(MIN(temp13),0), ROUND(AVG(temp13),0), ROUND(MAX(temp14),0), ROUND(MIN(temp14),0), ROUND(AVG(temp14),0), ROUND(MAX(temp15),0), ROUND(MIN(temp15),0), ROUND(AVG(temp15),0), ROUND(MAX(temp16),0), ROUND(MIN(temp16),0), ROUND(AVG(temp16),0), ROUND(MAX(temp17),0), ROUND(MIN(temp17),0), ROUND(AVG(temp17),0), ROUND(MAX(temp18),0), ROUND(MIN(temp18),0), ROUND(AVG(temp18),0), ROUND(MAX(temp19),0), ROUND(MIN(temp19),0), ROUND(AVG(temp19),0), ROUND(MAX(temp20),0), ROUND(MIN(temp20),0), ROUND(AVG(temp20),0)
FROM wettertabelle
GROUP BY DATE(datetime)
ORDER BY DATE(datetime)



Auswertung:

innoDB vs MyISAM
- MyISAM kleinere Tabelle bei schnellerer Geschwindigkeit

FLOAT vs DECIMAL
- FLOAT grössere Tabelle bei schnellerer (Berechnungs-)Geschwindigkeit

FLOAT vs SMALLINT
- FLOAT grössere Tabelle bei schnellerer (Berechnungs-)Geschwindigkeit


Ich würde daher Datentyp FLOAT in MyISAM Tabellen empfehlen (MySQL 5.5.35).

Grüsse Fredy

Edit:
Testsystem:
Win7 64Bit
Intel Core2Duo E8200 @2.66 GHz
4GB RAM DDR2-800
HD Samsung SSD 850 PRO

[TheWeather]

Hallo Fredy, vielen Dank für Deine aufschlussreichen Tests.

Ich würde daher Datentyp FLOAT in MyISAM Tabellen empfehlen (MySQL 5.5.35)

Das Ergebnis war aber durchaus schon vorhersehbar, wobei es bei Float 4,1 oder Float 3,1 wohl keine Unterschiede gibt.

Float ist Float und die Darstellung eines Floatwertes als 4,1 oder 3,1 ist nahezu unbedeutend, Float sind immer 4 Bytes, weswegen auch die Verarbeitung die gleiche Zeit benötigt. Schön aber, dass Du's in diversen Versuchen bestätigen konntest.

Warum die "decimal"-Tabellen bei Dir weniger Speicher brauchen als "Float" kann ich mir dennoch nicht erklären. Ist "Float" bei Deinen Definitionen irgendwie gleichbedeutend mit "Double"? Dann wären es nämlich 8 Bytes anstatt 4 Bytes, was den zusätzlichen Speicherbedarf vielleicht erklären könnte ...

Gruß Hans

[Fredy]

Hallo Hans

Das Ergebnis war aber durchaus schon vorhersehbar, wobei es bei Float 4,1 oder Float 3,1 wohl keine Unterschiede gibt.

Float ist Float und die Darstellung eines Floatwertes als 4,1 oder 3,1 ist nahezu unbedeutend, Float sind immer 4 Bytes, weswegen auch die Verarbeitung die gleiche Zeit benötigt. Schön aber, dass Du's in diversen Versuchen bestätigen konntest.

Hatte ich auch vermutet, jetzt ists bestätigt 

Warum die "decimal"-Tabellen bei Dir weniger Speicher brauchen als "Float" kann ich mir dennoch nicht erklären. Ist "Float" bei Deinen Definitionen irgendwie gleichbedeutend mit "Double"? Dann wären es nämlich 8 Bytes anstatt 4 Bytes, was den zusätzlichen Speicherbedarf vielleicht erklären könnte ...

Kanns mir auch noch nicht ganz erklären. An DOUBLE hab ich auch als erstes gedacht. Eine DOUBLE Tabelle ist aber mit Total 169,2 MiB nochmals grösser. Interessant ist, dass SMALLINT (2Byte) exakt gleich gross wie DEC(3,1) ist. Demnach hätte DECIMAL(3,1) auch 2 Byte und wäre kleiner als FLOAT mit 4 Byte.

Grüsse Fredy 

[TheWeather]

Hallo Fredy,

bei "decimal" weiß ich es nicht, wie ich schon früher mal geschrieben habe. Ich kenne nur "Currency", das in C++ verarbeitungstechnisch gleich oder ähnlich mit "decimal" sein sollte. "Decimal" ist offenbar eine Vereinbarung aus einem SQL-Derivat (MySQL), welches Fähigkeiten aufweist, die ich nicht kenne.

Möglich also, dass man decimal(2,1) definieren kann mit einer Vor und einer Nachkommastelle, was ja dann nur zwei Bytes (256 Bits vorne, 256 Bits als NK) bedeuten würde. Da kann ich leider aber nichts mehr beitragen, weil ich dieses Format nicht kenne. Denkbar, je nachdem wie die Deklaration decimal (2,1) den Wert abspeichert, wär's auch - weiß ich aber nicht ...

Gruß Hans

[wettermapCH]

Da ich mich aber mit anderen Datenbanken nicht auskenne und ich auch selber noch nicht genau weiß was ich eigentlich will, verfolge ich solche Diskussionen wie diese sehr gern und hoffe noch viel für mich zu lernen.

Tip, Sehr hilfreich: http://www.pscl.ch/

[tazza]

Ich trage die Wetterdaten gewöhnlich in eine Exeltabelle. Das finde ich bequem.