[mysql] waarden binnen 1 minuut selecteren. - Softwareontwikkeling

dinsdag 27 mei 2014 22:47

Acties:

Verwijderd

Topicstarter

Ik heb een database waarin ik mijn verbruik per W/h bijhoud om deze weer te geven in Highchart.
Dit alles draait op een Raspberry PI.
Nu loop ik tegen het probleem aan dat ik teveel data heb en daardoor de boel vastloopt(server timeout).
Nu heb ik als eerste oplossing de resultaten beperkt tot de laatste 60.000 records.(duurt nog 12 seconden)

Nu zou ik graag een 2e tabel maken met daarin het gemiddelde verbruik per minuut en 1 met het gemiddelde verbruik per uur, per dag, per week.

Ik heb een Id,Watt,tijd,count.
Watt is de waarde in watt, tijd is een TIMESTAMP.
Ik heb gemiddeld 6-12 watt waarden per minuut.
Hoe selecteer ik nu alle watt waarden die binnen (timestamp) '24-05-2014 8:40' vallen, dus de seconden niet bekijken?

Ik heb erg weinig(geen) ervaring in programmeren en was al heel blij dat ik een werkend Python scriptje had samengesteld uit allerlei voorbeeldjes.

Kan iemand mij een setje geven in de juiste richting?

dinsdag 27 mei 2014 22:49

Acties:

Pinobigbird

doesn't share food!

SQL:

1	WHERE tijd BETWEEN '2014-05-24 8:40' AND '2014-05-24 8:41'

?

[ Voor 5% gewijzigd door Pinobigbird op 27-05-2014 22:50 . Reden: datumnotatie aangepast ]

Joey: Nice try. See the Netherlands is this make believe place where Peter Pan and Tinkerbell come from.
https://kattenoppasleiderdorp.nl
PV: 3080Wp ZO + 3465Wp NW = 6545Wp totaal 13°tilt

dinsdag 27 mei 2014 23:07

Acties:

Verwijderd

Topicstarter

Geweldig, bedankt daar kan ik verder mee.

woensdag 28 mei 2014 01:14

Acties:

Gomez12

Ik zeg google eens naar indexen/query plans/explain, 60.000 records is peanuts en ik vermoed dat je probleem simpelweg is dat je geen database optimalisaties gebruikt.

woensdag 28 mei 2014 11:46

Acties:

NMe

Quia Ego Sic Dico.

Wat Gomez12 zegt inderdaad. Databases met goeie indexen gaan met miljoenen records normaal gesproken nog niet op hun knieën, dus 60.000 records is niks.

'E's fighting in there!' he stuttered, grabbing the captain's arm.
'All by himself?' said the captain.
'No, with everyone!' shouted Nobby, hopping from one foot to the other.

woensdag 28 mei 2014 12:20

Acties:

Barryvdh

NMe schreef op woensdag 28 mei 2014 @ 11:46:
Wat Gomez12 zegt inderdaad. Databases met goeie indexen gaan met miljoenen records normaal gesproken nog niet op hun knieën, dus 60.000 records is niks.

Het is wel op een RaspberryPi he, die zijn wel een stukje langzamer/minder geheugen als een normale database server.
En het gaat dus niet om 60.000 records in totaal, maar om het ophalen van 60.000 records in 1 request en dat in een grafiekje zetten. (Zoals ik het lees)

woensdag 28 mei 2014 12:28

Acties:

NMe

Quia Ego Sic Dico.

Dan is het alsnog een kwestie van meten.

'E's fighting in there!' he stuttered, grabbing the captain's arm.
'All by himself?' said the captain.
'No, with everyone!' shouted Nobby, hopping from one foot to the other.

woensdag 28 mei 2014 12:32

Acties:

CurlyMo

Ook ik log mijn energieverbruik per 100w. Sinds 22 juli 2013 heb ik nu 156,425 records in mijn database staan. Via wat tussen tabellen en caching mechanismen krijg ik nog steeds mijn grafiek in 1 seconden op het scherm. De belangrijkste winst zit hem in een json bestand dat elke keer als ik mijn pagina opvraag bijgewerkt wordt met de laatste waarden uit de database. Dat bestand is nu 1.41MB groot. Geen probleem voor Highcharts

Mijn scripts zijn hier te vinden:

https://github.com/CurlyMoo/RaspberryPi-P1-port/

Sinds de 2 dagen regel reageer ik hier niet meer

woensdag 28 mei 2014 12:54

Acties:

Jeroen

uǝʌ ǝp uɐʌ

Bij zoiets zou ik overwegen om data gewoon dubbel op te slaan, elke minuut een row in een tabel zetten met het gemiddelde van die afgelopen minuut bijvoorbeeld. Dat is op zich een vrij snelle operatie waardoor het ophalen van je data veel sneller kan.

"I don't always test my code, but when I do, I test on production."

woensdag 28 mei 2014 22:16

Acties:

Verwijderd

Topicstarter

@gomez12 ik ga je tip opvolgen , maar indexen gaan mij niet helpen denk ik omdat ik alle row's nodig heb en niet specifiek een bepaalde row.

@barryvdh idd het gaat om alle records en om daar een grafiek van te maken bij meer dan 60.000 rows werkt het niet meer.Ik zit inmiddels op 177500 records maar dat krijg ik dus niet in 1 grafiek.

@CurlyMo ik ga je scrips eens bekijken , ik had ze al eens gezien in het topic over de KWH logger.Ik begreep de code niet voldoende om deze toe te passen.Het zou idd een heel stuk schelen als de data alleen aangevuld word met de nieuwe waarden ipv elke keer de hele boel opnieuw op te halen.

@jeroen dat was idd het plan, ik log nu elke w/h dat wil ik in een 2e tabel omzetten naar een minuut gemiddelde, en naar een tabel gemiddelde per uur, gemiddelde dag, gemiddelde week.

Ik heb mij errorlog nog eens bekeken, ik krijg een php fatal error max time of 30 seconden exeeded in regel 28.

PHP:

<?php

echo '<html>';
echo '<head>';
echo '<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>';
echo '<script src="http://code.highcharts.com/stock/highstock.js"></script>';
echo '<script src="js/highstock.js"></script>';
echo '</head>';
$link = mysql_connect('localhost', 'user', 'pass');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
 //echo 'Connected successfully';

mysql_select_db("energie");



$result = mysql_query("SELECT watt, tijd FROM elektra");


mysql_close($link);

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

        $date = date_create($row[0]);
        $format = $date->format('Y, m-1, d, H, i, s');

        $data[] = "[Date.UTC($format), $row[1]]";
}



?>
<script>
$(function () {
   $('#container').highcharts('StockChart', {
        chart: {
        },
    xAxis: {
        type: 'datetime', //ensures that xAxis is treated as datetime values

        },


        series: [{
                name : 'Data',
                data: [<?php echo join($data, ',') ?>],
        }]
    });
});



</script>

<div id="container" style="width:100%; height:500px;"></div>

</html>

Mogelijk dat 1 van jullie hier iets raars in zien?
In PHPmyAdmin duurt die request 0,0039S voor 118500 records, dus vermoed ik dat het dan ergens anders fout gaat.

[ Voor 47% gewijzigd door Verwijderd op 28-05-2014 22:56 ]

woensdag 28 mei 2014 23:01

Acties:

Gomez12

Verwijderd schreef op woensdag 28 mei 2014 @ 22:16:
@gomez12 ik ga je tip opvolgen , maar indexen gaan mij niet helpen denk ik omdat ik alle row's nodig heb en niet specifiek een bepaalde row.

Super simpele vraag : Wat zit je te doen met mysql?

Mysql heeft simpelweg allerlei overhead bij het ophalen van alle records, waarom zou je het dan niet bijv naar een flat-file json opslaan die highcharts direct kan inlezen? Op zich kan mysql het fluitend aan (mits je mysql een beetje ingericht hebt) maar waarom iets gebruiken wat overhead introduceert als je er niets van gebruikt?

Maar een veel belangrijkere vraag : Wat moet je met 60.000 rows in highcharts? Even ervanuit gaande dat je geen 5 schermen naast elkaar hebt staan om je grafiek op te tonen (met een gemiddelde resolutie van 3000 pixels) en je geen 60 lijntjes tekent zit je gewoon aan een max mogelijk aantal punten op een grafiek, meer kan er gewoon niet getoond worden.
Een 300 pixels breed grafiekje heeft max 300 data punten nodig (even uitgaande van 1 lijntje) aangezien een pixel de kleinste eenheid is waarop een punt getoond kan worden.
En ik ken highcharts even niet diepgaand, maar ik vermoed dat die ook gewoon methodes kent voor lazy-loading etc zodat je in 0,1 seconde je basis grafiek met 300 punten neergezet heeft en daarna direct kan beginnen met lazy-loading voor de volgende 600 punten (links en rechts zodat je kan schuiven) en daarna kan je vast met lazy-loading de volgende 900 punten binnenhalen om te kunnen zoomen etc.

Op die manier heb je binnen 0,1 sec een grafiek staan die direct uit te lezen is. En wellicht (want lazy-loading heeft ook weer een performance penalty) heb je dan pas na 13 seconden ipv 12 de hele complete dataset ingeladen, maar na 2 seconden kan je al links-rechts scrollen, na 3 seconden kan je al inzoomen etc
Tegen de tijd dat je daadwerkelijk die 60.000 punten nodig hebt ben je al 10 muisklikken verder binnen highcharts (en elke muisklik kost ook weer seconden dus grote kans dat je normaliter nog niet eens binnen 12 seconden op dat nivo zit dat je ze nodig hebt).

Let wel dit is de moeilijke weg (maar wel de meest efficiente), je kan ook simpelweg een 2 of 3-traps raket maken dat je eerst 300 records inlaad, die toon je dan lazy-load je bijv 9000 records en daarna lazy-load je de complete dataset, dan duurt het wellicht 20 seconden voordat je alles ingeladen hebt (want je voert geen optimalisaties uit en je stapelt dus simpelweg alle query-tijden op elkaar) maar je hebt wel binnen 1 sec een beeld en kan inzoomen na 6 seconde en na 20 seconden is alles er.

woensdag 28 mei 2014 23:04

Acties:

CurlyMo

Highchart kan in en uitzoomen en daarmee kan je de resolutie van de grafiek vergroten of verkleinen. I.p.v. per 15min kan je dan een overzicht opvragen van bijv. per uur of per dag. Allemaal dynamisch. Dat maakt het makkelijk om lekker veel punten in te laden.

Sinds de 2 dagen regel reageer ik hier niet meer

donderdag 29 mei 2014 00:01

Acties:

Verwijderd

Topicstarter

Ik hoor het al, nog heel veel te leren.

Waarom 60.000 rows? Ik was in de veronderstelling dat ik alle data direct in Highcharst moet laden om een totaal overzicht te maken.(die 60.000 is de grens omdat erboven de boel crashed) in totaal was ik van plan 4100000 rows te laden. En het ging de eerste dagen ook geweldig tot de magische grens van 60.000.

Ik snap dat idd mij scherm nooit alle tegelijk punten kan weergeven.Waarschijnlijk gaat het daar ook deels fout.

Eerst wil ik mijn ruwe data omzetten naar gemiddelden per minuut, dat maakt de boel al 6x kleiner.

Ik ga eens kijken wat ik nog meer met Highchart kan mbt het aantal datapunten.

Ik dank jullie voor jullie bijdrages , ik ben hier echt mee geholpen

donderdag 29 mei 2014 00:21

Acties:

CurlyMo

Het json bestand dat ik op dit moment inlaad bevat 29683 datapunten en dat trekt hij prima.

Sinds de 2 dagen regel reageer ik hier niet meer

donderdag 29 mei 2014 13:22

Acties:

Gomez12

CurlyMo schreef op woensdag 28 mei 2014 @ 23:04:
Highchart kan in en uitzoomen en daarmee kan je de resolutie van de grafiek vergroten of verkleinen. I.p.v. per 15min kan je dan een overzicht opvragen van bijv. per uur of per dag. Allemaal dynamisch. Dat maakt het makkelijk om lekker veel punten in te laden.

Dat snap ik, maar je hoeft geen 60.000 punten in 1x te voeden neem ik aan.

Ik neem aan dat je met highcharts eerst 300 punten kan inladen, dan terwijl je de grafiek ziet kunnen er op de achtergrond extra punten ingeladen worden in meerdere fases zodat je er niets van merkt. En je snel iets kan tonen terwijl er aan de achterkant data bijgeladen wordt.

De truc is om simpelweg niet alles in te laden, maar enkel in te laden wat je nodig hebt om nu te tonen en op de achtergrond wat er zometeen nodig is, op die manier kan je "oneindige" data inladen.

Blind alles inladen betekent dat je heel lang moet wachten en dat je tegen grenzen aanloopt.

donderdag 29 mei 2014 13:28

Acties:

CurlyMo

Ik neem aan dat je met highcharts eerst 300 punten kan inladen, dan terwijl je de grafiek ziet kunnen er op de achtergrond extra punten ingeladen worden in meerdere fases zodat je er niets van merkt. En je snel iets kan tonen terwijl er aan de achterkant data bijgeladen wordt.

In de ideale situatie doe je dat, maar ik neem aan dat dit gewoon in het lokale netwerk draait. Snelheid van overdracht is dus het probleem niet.

Blind alles inladen betekent dat je heel lang moet wachten en dat je tegen grenzen aanloopt.

Dat is een onterechte aanname. Mijn 30.000 datapunten worden gewoon binnen 1 seconde gerenderd.

Sinds de 2 dagen regel reageer ik hier niet meer

donderdag 29 mei 2014 13:40

Acties:

Barryvdh

CurlyMo schreef op donderdag 29 mei 2014 @ 13:28:
Dat is een onterechte aanname. Mijn 30.000 datapunten worden gewoon binnen 1 seconde gerenderd.

Bij dat JSON bestand hoef je dan server-side ook niks te doen, dan ligt het meer aan je browser/pc hoeveel moeite hij met zoveel punten heeft.

donderdag 29 mei 2014 13:48

Acties:

CurlyMo

Dat is precies mijn punt

Sinds de 2 dagen regel reageer ik hier niet meer

donderdag 29 mei 2014 21:34

Acties:

Verwijderd

Topicstarter

Ik heb nu een hele vieze workaround gemaakt, maar dat moet niet zo blijven.(set_time_limit())
Ik heb gekeken naar het Json script van CurlyMo De eerste paar regeltjes begrijp ik , maar vervolgens ga je met waarden goochelen waarvan ik al niet begrijp waar je die uit afgeleid hebt.Waarschijnlijk komt dat doordat jij een P1 meter uitleest en ik puur pulsjes tel.

In eerste instantie was het mijn bedoeling om mijn pulseteller(s) dezelfde data te laten genereren als het P1 script van CurlyMo zodat dezelde database gebruikt kom worden en dezelfde scrips voor de charts. Echter die code isnog veel te geavanceerd voor mij, dsu dat heb ik eerst laten varen.

Ik begrijp nu dat omdat PHP serverside is dat de server de datatabel moet maken, vervolgens word het hele script naar de browser gestuurt en vervolgens voert de browser het script uit dat zorgt voor de weergave.
Het probleem waar ik nu tegenaan loop is niet dat MySql te traag zou zijn maar het PHP script heeft gewoon teveel werk om die tabel gereed te maken voor verzending.
Door gebruik van Json te maken kan ik de tabel in takt laten, Json vult de ontbrekende waarden aan in de Json file en omdat de tabel eigenlijk al volledig gereed staat is de PHP parser veel sneller klaar voor verzending.

Ik schrijf mijn code normaal in pseudocode en ga dan opzoek naar de juiste code om dat te doen.Ik ga op zoek naar Json tutorials/voorbeelden en scripjes om te ontleden, Ik wil nl. wel begrijpen wat elk deel doet voordat ik het zelf gebruik.Volgens mij is het niet zo heel ingewikkeld:

Lees de waarden uit Mysql en markeer de verwerkte waarden en schrijf die weg in het juiste formaat in een file.
Bij de volgende actie selecteer je alleen de ongemarkeerde records en vult dat bij achter in de file.

Dan nog iets met een cronjob doen om de watt/tijd tabellen om te zetten naar w/minuut gemiddelden

Dat word dus weer een paar weken knoeien en klooien.

donderdag 29 mei 2014 21:49

Acties:

HuHu

Wat is nu precies je doel?

Je hebt een tabel (met Id,Watt,tijd,count), maar wat wil je als uitvoer? In je OP zeg je "waarden binnen één minuut", maar het lijkt er meer op dat je alle waarden wilt, samengevoegd naar hele minuten. Klopt dat?

Wellicht zou zo'n query werken (uit m'n hoofd, niet getest):

SQL:

SELECT 
  tijd,
  SUM(watt),
  SUM(`count`)
FROM
  tabel
GROUP BY
  UNIX_TIMESTAMP(tijd) DIV 60

edit: het lijkt te werken: http://sqlfiddle.com/#!2/48143/5/0

[ Voor 7% gewijzigd door HuHu op 29-05-2014 22:02 ]

donderdag 29 mei 2014 22:43

Acties:

Verwijderd

Topicstarter

Idd ik wil alle waarden middellen binnen dezelfde minuut. dus alle waarden van 8:10 tot 8:11 van 8:11 tot 8:12 enz. terug rekenen naar( SUM 'watt ' divide by rows) zo krijg ik dan een gemiddeld momentaan verbruik per minuut.
Vervolgens wil ik een nieuwe tabel maken met daarin het gemiddelde verbruik en de tijd(begin van de minuut)
Count doe ik nog niets mee.

donderdag 29 mei 2014 23:03

Acties:

HuHu

Waarom wil je een nieuwe tabel maken? Is de query die ik gaf te traag? Je kunt ook een VIEW maken van deze query (en 'm aanpassen voor uur/dag/week/enz...).

donderdag 29 mei 2014 23:20

Acties:

Verwijderd

Topicstarter

Waarom ik een tabel wil? Waarschijnlijk omdat ik dat begrijp, de querry duurt nu ca 12 seconden.En helaas werkt het niet helemaal zoals gehoopt. ik krijg toch nog dubbele tijden

code:

2014-05-17 08:57:06     938
2014-05-17 08:57:21     2261
2014-05-17 08:58:02     2134
2014-05-17 08:58:45     839
2014-05-17 08:59:02     3561
2014-05-17 09:00:06     2321
2014-05-17 09:00:46     764

code:

ID       WATT         TIMESTAMP            COUNT
137     430     2014-05-17 08:58:02     37
138     429     2014-05-17 08:58:11     38
139     428     2014-05-17 08:58:19     39
140     424     2014-05-17 08:58:27     40
141     423     2014-05-17 08:58:36     41
142     419     2014-05-17 08:58:45     42
143     420     2014-05-17 08:58:53     43

Ik wil hier dus als uitkomst:

code:

1	424 2014-05-17 08:58:00

donderdag 29 mei 2014 23:34

Acties:

HuHu

Laat je query ook eens zien.

edit: hebt je überhaupt de query die ik gaf wel geprobeerd? Zie hier: http://sqlfiddle.com/#!2/868280/3/0 , wat je vraagt komt er exact uit (met een kleine aanpassing voor de deling).

[ Voor 76% gewijzigd door HuHu op 29-05-2014 23:42 ]

donderdag 29 mei 2014 23:46

Acties:

Verwijderd

Topicstarter

code:

SELECT tijd, SUM( watt )
FROM elektra
GROUP BY TIMESTAMP( tijd )
DIV 60

Ik denk dat ik het al zie TIMESTAMP moet zijn UNIX_TIMESTAMP.

donderdag 29 mei 2014 23:54

Acties:

Martindo

.

[ Voor 97% gewijzigd door Martindo op 29-05-2014 23:55 ]