Maschinendatenerfassung in 15 minuten Schritten

S

Sinumeriker

Neuer Benutzer
Beiträge
3
Hallo Zusammen...


Für eine Maschinendatenerfassung wird bei der Fertigstellung eines Teiles ein neuer Datensatz in einer Datenbank geschrieben (Id, Maschine, Datum + Zeit)

Die Auswertung erfolgt dann in 15 Minuten Schritten.

Eigentlich funktioniert das alles gut.


Problematisch wird die Größe der Datenbank (ca. 20 Millionen Datensätze / Jahr).

Grade die Auswertung dauert viel zu Lange. Ich lasse eine Schleife laufen die für jede 15 Minuten eines Tages eine SQL Abfrage startet was mein PHP / SQL in die Knie zwingt…



Daher war meine Überlegung die Datensätze mittels Update SET count = count + 1
in 15 Minuten Pakete aufzusplitten…
Hierbei stoße ich aber an meine Wissensgrenzen…

Ist mein Ansatz richtig oder gibt es da bessere / einfachere Lösungen?

Grüße aus dem Sauerland…
Peter
 
Werbung:
Ich rate mal, was ihr auswertet. Für einen bestimmten Tag wollt ihr wissen, wie viele Meldungen es je Maschine und 15-Minuten-Interval es gab.

Ich baue mal eine Testtabelle und fülle die mit 10 Millionen Datensätzen. Dabei habe ich 10 verschiedene Maschinen (von 0-9). Auf die Spalte mit dem Timestamp setze ich einen BRIN - Index:


Code: 
test=# create table sinumeriker(id serial primary key, maschine int, ts timestamp);
CREATE TABLE
test=*# insert into sinumeriker (maschine, ts) select (random()* 10)::int, '2015-01-01'::timestamp + s * '2minutes'::interval + random() * 100 * '1second'::interval from generate_series(1, 100000000) s;
INSERT 0 100000000
test=*# create index idx_ts on sinumeriker using brin (ts);
CREATE INDEX

Nun frage ich mal für ein Datum (3.3.2015) je Maschine und Virtelstunde ab, wie viele Meldungen es gab. Dazu das EXPLAIN ANALYSE:

Code: 
test=*# explain analyse select maschine, ts::date, extract(hour from (ts))::int * 4 + (extract(minute from (ts))::int / 15), count(1) from sinumeriker where ts between '2015-03-03 00:00:00' and '2015-03-04 00:00:00' group by 1,2,3 order by 1,2,3;
  QUERY PLAN   
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=583807.35..583907.35 rows=40000 width=20) (actual time=8.107..8.159 rows=530 loops=1)
  Sort Key: maschine, ((ts)::date), ((((date_part('hour'::text, ts))::integer * 4) + ((date_part('minute'::text, ts))::integer / 15)))
  Sort Method: quicksort  Memory: 66kB
  ->  HashAggregate  (cost=579549.81..580749.81 rows=40000 width=20) (actual time=7.682..7.876 rows=530 loops=1)
  Group Key: maschine, (ts)::date, (((date_part('hour'::text, ts))::integer * 4) + ((date_part('minute'::text, ts))::integer / 15))
  ->  Bitmap Heap Scan on sinumeriker  (cost=5205.00..574549.81 rows=500000 width=12) (actual time=5.954..7.293 rows=720 loops=1)
  Recheck Cond: ((ts >= '2015-03-03 00:00:00'::timestamp without time zone) AND (ts <= '2015-03-04 00:00:00'::timestamp without time zone))
  Rows Removed by Index Recheck: 22960
  Heap Blocks: lossy=128
  ->  Bitmap Index Scan on idx_ts  (cost=0.00..5080.00 rows=500000 width=0) (actual time=2.185..2.185 rows=1280 loops=1)
  Index Cond: ((ts >= '2015-03-03 00:00:00'::timestamp without time zone) AND (ts <= '2015-03-04 00:00:00'::timestamp without time zone))
 Planning time: 0.114 ms
 Execution time: 8.351 ms
(13 Zeilen)

test=*#

8 Millisekunden erscheinen mir jetzt nicht wirklich langsam. Allerdings ist das kein MySQL, sondern PostgreSQL.
 
Mit einem normalen btree-Index geht es sogar noch schneller:

Code: 
test=*# create index idx_ts_btree on sinumeriker using btree (ts);
CREATE INDEX
test=*# explain analyse select maschine, ts::date, extract(hour from (ts))::int * 4 + (extract(minute from (ts))::int / 15), count(1) from sinumeriker where ts between '2015-03-03 00:00:00' and '2015-03-04 00:00:00' group by 1,2,3 order by 1,2,3;
  QUERY PLAN   
-------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=75.99..105.44 rows=693 width=20) (actual time=3.134..3.536 rows=530 loops=1)
  Group Key: maschine, ((ts)::date), ((((date_part('hour'::text, ts))::integer * 4) + ((date_part('minute'::text, ts))::integer / 15)))
  ->  Sort  (cost=75.99..77.72 rows=693 width=12) (actual time=3.128..3.226 rows=720 loops=1)
  Sort Key: maschine, ((ts)::date), ((((date_part('hour'::text, ts))::integer * 4) + ((date_part('minute'::text, ts))::integer / 15)))
  Sort Method: quicksort  Memory: 58kB
  ->  Index Scan using idx_ts_btree on sinumeriker  (cost=0.57..43.29 rows=693 width=12) (actual time=0.039..2.450 rows=720 loops=1)
  Index Cond: ((ts >= '2015-03-03 00:00:00'::timestamp without time zone) AND (ts <= '2015-03-04 00:00:00'::timestamp without time zone))
 Planning time: 0.395 ms
 Execution time: 3.651 ms
(9 Zeilen)

Dafür ist der BRIN-Index halt deutlich kleiner:

Code: 
test=*# select pg_size_pretty(pg_relation_size('idx_ts_btree'));
 pg_size_pretty
----------------
 2142 MB
(1 Zeile)

test=*# select pg_size_pretty(pg_relation_size('idx_ts'));
 pg_size_pretty
----------------
 160 kB
(1 Zeile)

test=*#

Muß man halt abwägen ...
 
Hallo...

Danke für die Antworten, aber das übersteigt doch meinen Horizont...

Ich benutze folgende Abfrage:

SELECT count( `data`.zeit )
FROM produktion.`data`
WHERE `data`.zeit
BETWEEN '2012-02-20 10:02:40'
AND "2012-02-20 11:15:17"
AND id_maschine =1

Das "Sending Data" dauert gut 0,5 sekunden.
Bei 96 Abfragen (24 Stunden x 4) sind das schon 48 sekunden

Ich glaube ich muss nochmal Bücher welzen gehen...

Danke,
Peter
 

Anhänge

  • mypic.png
    mypic.png
    8 KB · Aufrufe: 2
Warum fragst Du ein Interval von über 1 Stunde ab, wenn Du es auf 15 Minuten genau haben willst?

Code: 
test=*# select '2012-02-20 11:15:17'::timestamp - '2012-02-20 10:02:40'::timestamp as diff;
  diff   
----------
 01:12:37
(1 Zeile)

Warum fragst Du es weiterhin in einer Schleife ab, warum verwendest Du mal ' und mal " für das Quoting von Timestamps, und warum schaust Du nicht in das Explain? (okay, die letztere Frage kann man bei MySQL mit 'wegen dem Brechreiz' beantworten ...)
 
Um es einfach zu halten, und erstmal von dieser beknackten Schleife weg zu kommen, solltest du erstmal für alle Intervalle eine Hilfstabelle erstellen. Es geht natürlich eleganter, nur nicht mit MySQL. Daher
Code: 
CREATE TABLE hilfe(
   von TIME NOT NULL,
   bis TIME NOT NULL
   );

[...]
INSERT INTO hilfe(von,bis) VALUES('10:00:00.000','10:14:59.999');
INSERT INTO hilfe(von,bis) VALUES('10:15:00.000','10:29:59.999');
INSERT INTO hilfe(von,bis) VALUES('10:30:00.000','10:44:59.999');
INSERT INTO hilfe(von,bis) VALUES('10:45:00.000','10:59:59.999');
[...]
Und jetzt puzzelst du: Aus der datumzeit Spalte nimmst du das Datum und joinst auf den Zeitanteil deinen Interval:
Code: 
SELECT   cast(t.datumzeit AS DATE) AS datum,h.von,h.bis,count(*) AS anzahl
FROM   tabelle t
INNER JOIN hilfe h
ON     cast(t.datumzeit AS TIME) BETWEEN h.von AND h.bis
GROUP BY cast(t.datumzeit AS DATE),h.von,h.bis
ORDER BY cast(t.datumzeit AS DATE),h.von,h.bis
Und wenn du zu jedem vorhandenen Datum alle Zeiträume willst (also auch Anzahl = 0):
Code: 
SELECT   x.datum,
     h.von,
     h.bis,
     sum(CASE WHEN t.datumzeit IS NULL THEN 0 ELSE 1 END) AS anzahl
FROM   hilfe h
CROSS JOIN ( SELECT DISTINCT cast(datumzeit AS DATE) AS datum FROM tabelle ) x
LEFT JOIN tabelle t
ON     x.datum = cast(t.datumzeit AS DATE)
AND     cast(t.datumzeit AS TIME) BETWEEN h.von AND h.bis
GROUP BY x.datum,h.von,h.bis
ORDER BY x.datum,h.von,h.bis
Damit müssten erstmal ohne Schleife sinnvolle Daten zu ermitteln sein. Performance kann man sich dann immernoch ansehen.

PS: MSSQL, daher ist cast() eventuell anders zu nutzen.
 
Code: 
SELECT zeit, minute( zeit ) AS `Minute` , id_maschine, count( id ) AS wert
FROM produktion.`data`
WHERE `data`.zeit
BETWEEN '2012-02-29 00:00:00'
AND '2012-02-20 23:59:59'
GROUP BY (
( 60 /15 ) * HOUR( zeit ) + FLOOR( MINUTE( zeit ) /15 )
)

fertig....
 
Werbung:

Ähnliche Themen

D
Indizien erstellen | Performance Probleme
Antworten
5
Aufrufe
2K
MDDaniel
MDDaniel
Zurück
Oben