Fröling: [ANNOUNCE] p4d - Visualisierung und Einstellung der S-3200 via COM1

Moin,

ja ist nicht gerade wenig ;), hier sieht es so aus:

mysql> select table_name as 'Table', table_rows as 'Row Count', round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" from information_schema.tables where table_schema = 'p4' order by (data_length + index_length) desc;
+------------+-----------+------------+
| Table      | Row Count | Size in MB |
+------------+-----------+------------+
| samples    |  16839104 |    1806.00 |
| menu       |      1334 |       0.16 |
| jobs       |       435 |       0.09 |
| valuefacts |       273 |       0.06 |
| schemaconf |        34 |       0.02 |
| errors     |        49 |       0.02 |
| config     |        21 |       0.02 |
+------------+-----------+------------+
7 rows in set (0.04 sec)

Ich habe vor die Daten welche älter als X Monate (Konfigurierbar) sind zu aggregieren, entweder in der selben Tabelle oder in einer weiteren. Sodass im Anschluss für alles was vor X Monaten Aufgezeichnet wurde nur noch die Mittelwerte über N Messwerte aufgehoben werden. Aktuell zeichne ich im Minuten Intervall auf, alles was älter als 1 Jahr ist auf 10 oder 15 Minuten zu aggregieren bringt schon eine Menge ....
Wichtig ist mir weiterhin alles in den Charts auswählen zu können. Finde die Vergleiche mit dem Vorjahr recht interessant ....

Bessere Idee(en)?

Viele Grüße
Jörg

BTW: Hab noch ein anderes Projekt an dem derzeit mit zwei anderen entwickle, da geht es um die Verwaltung von EPG Daten (also das TV Programm) mit allem möglichen Zusatzinformationen (Bildern, etc.) nebst WEB Interface welche aus dem Internet zusammen gesucht werden. Das ganze dient dann als EPG Basis für den VDR (genialer Open-Source Linux DVB Reicver von Klaus Schmidinger ) ...... wie auch immer, in der Datenbank habe ich knapp 7 GB Daten für 14 Tage EPG :o - ist mir gerade aufgefallen da wir bei dem Thema waren.

mysql> select table_name as 'Table', table_rows as 'Row Count', round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" from information_schema.tables where table_schema = 'epg2vdr' order by (data_length + index_length) desc;     
+-------------------+-----------+------------+
| Table             | Row Count | Size in MB |
+-------------------+-----------+------------+
| series_media      |     47420 |    3360.97 |
| movie_media       |     27555 |    2582.02 |
| images            |     26645 |     280.39 |
| events            |     84570 |     225.11 |
| useevents         |     64494 |     133.48 |
| series_episode    |     99174 |      91.20 |
| episodes          |     78216 |      19.98 |
| imagerefs         |     19994 |      14.05 |
| recordings        |      5315 |      10.47 |
| components        |     31794 |       9.03 |
| snapshot          |     10725 |       4.36 |
| movie             |      1757 |       3.25 |
| series            |      1086 |       2.16 |
| movie_actor       |     14137 |       1.66 |
| movie_actors      |     11155 |       1.48 |
| series_actor      |      8421 |       1.11 |
| timers            |       667 |       0.27 |
| channelmap        |       207 |       0.14 |
| timersdone        |        31 |       0.06 |
| fileref           |       114 |       0.05 |
| analyse           |        32 |       0.03 |
| vdrs              |         5 |       0.03 |
| channelsconf      |         0 |       0.02 |
| searchtimers      |         2 |       0.02 |
| recordingdirs     |       151 |       0.02 |
| parameters        |        24 |       0.02 |
| timerdistribution |        26 |       0.02 |
| thetvdbview       |      NULL |       NULL |
| eventsviewplain   |      NULL |       NULL |
| eventsview        |      NULL |       NULL |
+-------------------+-----------+------------+
30 rows in set (0.11 sec)


mysql> select sum(round(((data_length + index_length) / 1024 / 1024), 2)) "Size in MB" from information_schema.tables where table_schema = 'epg2vdr' order by (data_length + index_length) desc;
+------------+
| Size in MB |
+------------+
|    6741.40 |
+------------+
1 row in set (0.09 sec)

Und das nur weil die von den Sendern mitgelieferten EPG Daten so bescheiden sind

ja da gehören die nun hin, das 'aufräumen' ist eines der neuen Features. Wobei du aber die mitgelieferten Schemas wählen können solltest, auch wenn dein eigenes noch nicht da liegt. Simmen die Rechte?

Grüße Jörg

Habe für die Aggregation mal was vorbereitet.

Zur Installation.

- git pull
- nun p4d anhalten
- make clean all install

Tabelle samples altern (am mysql prompt):

alter table samples drop column sensorid;
alter table samples add column aggregate varchar(1) after type;
alter table samples add column samples integer(3) after text;
alter table samples drop primary key, add primary key(address, type, aggregate, time);

Die oberste Zeile könnte sofern ihr die spalte sensorid bereits nicht mehr habt einen Fehler werfen, den könnt ihr ignorieren (die Spalte stammt von einer sehr alten Version).

Das ganze kann sehr lange dauern da die Tabelle recht groß ist. Auf meinem nicht ganz so kleinen Server lief es 20 Minuten!

Nun könnt Ihr den p4d Starten, damit sollte alles wieder wie gewohnt laufen

Zur Aggregation schreibe ich einen einzelnen Post

Grüße
Jörg

Die Aggregation ... erst mal manuell:

Ich würde, sofern euch die Messwerte wichtig sind vorher einen Backup (mysqldump) der Tabelle machen!!!!

Mit diesem Statement aggregiert ihr alles vo dem 1.1.2014 auf einen Messwert pro Sensor je 15 Minuten:

insert into samples select address, type, 'A' as aggregate, 
      CONCAT(DATE(time), ' ', SEC_TO_TIME((TIME_TO_SEC(time) DIV 900) * 900)) + INTERVAL 15 MINUTE time, 
      unix_timestamp(sysdate()) as inssp, unix_timestamp(sysdate()) as updsp, 
      round(sum(value)/count(*), 2) as value, text, count(*) samples 
    from 
      samples 
    where 
      aggregate != 'A' and 
      time <= '2013-12-31 23:59:59' group by CONCAT(DATE(time), ' ', SEC_TO_TIME((TIME_TO_SEC(time) DIV 900) * 900) )+ INTERVAL 15 MINUTE, address, type;

Datum und Intervall könnt Ihr nach belieben anpassen, Feldreihenfolge muss beim insert into ... select beibehalten werden!

nun könnt ihr euch die aggregation = 'A' Zeilen mit selects ansehen und prüfen ob es wunschgemäß gelaufen ist.

Danach können die einzel Messwerte vor dem 1.1.2014 gelöscht werden:

delete from samples where time <= '2013-12-31 23:59:59' and aggregate != 'A';

Danach zum bereinigen der Altlast am besten noch ein

OPTIMIZE TABLE samples;

laut:

select table_name as 'Table', table_rows as 'Row Count', round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" from information_schema.tables where table_schema = 'p4' order by (data_length + index_length) desc;

ist meine Tabelle nun um ca 40% kleiner! Auf dem Filesystem merkt ihr jedoch keinen Unterschied da MySQL die Table-Spaces dort reserviert behält, um diese zu verkleinern müsste man MySQL im Single File Mode betreiben und ggf. sogar die Tabelle einmal exportieren. Schadet aber weiter nicht da der freigewordenen Platz nun wieder verwendet wird (so verstehe ich es wenigstens).

Im nächsten Schritt baue ich das aggregieren in dem p4d ein sodass es jede Nacht automatisch den ältesten Tag aggregiert ....

Grüße
Jörg

in das Statement für die Aggregation sind mir 2 'where' reingerutscht, das eine einfach löschen

Habe es oben geändert, Gruß Reichl.

Hat es schon jemand ausprobiert?

BTW: die Status Mails gibt es bald (optional) im HTML Format

@Reichl, Danke!

Erweiterung für die Aggregation ist fertig und im git (Version 0.1.13).

Voraussetzung ist das die für die Version 0.1.12 beschriebenen Schritte (altern , ...) 4-5 Post von hier aus zurück, durchgeführt wurden. Auch das dort beschriebene einmalige manuelle aggregieren ist sinnvoll damit es Nachts nicht ewig läuft (solange würden keine Daten Aufgezeichnet.
Das nächtliche aggregieren (betrifft dann ja nur jeweils einen Tag) läuft hier in unter einer Sekunde.

Zum aktivieren der nächtlichen Aggregation (läuft immer um 01:00) muss sie in der p4d.conf eingetragen werden:

# aggregation


# history for aggregation in days (default 0 days -> aggegation turned OFF)
# aggregateHistory = 365


# aggregation interval in minutes - 'one sample per interval will be build' (default 15 minutes)
# aggregateInterval = 15

Als weiteres Feature kann man nun einstellen ob die Mails in HTML formatiert oder wie bisher plain ASCII versendet werden sollen. Beispiel für die config liegt bei. Dafür musste auch das Mail Skript erweitert werden, sofern ihr kein eigenes habt ist weiter nichts zu beachten da es mit 'make install' automatisch installiert wird

# ----------------------------------------
# send mail in html format (default 1)


# htmlMail = 0

@Reichl, das mit den Textfile hab ich nicht vergessen steht noch auf meinem Plan.

Viel Spaß damit, Grüße Jörg

philipphunger, diene Erweiterung des README im git habe ich übernommen. Danke!

er will dir schonen beibringen das du den compiler (g++) installieren musst bzw. das er diesen nicht findet. Geht den g++ --version an der Kommandozeile?