[MySQL] query of database optimalisatie met veel records

[Anoniem: 336502]

Mijn vraag
In een applicatie waarin ik process variablen log in een MySQL database begin ik last te krijgen mijn raspberry performance. Er draait hier nu een machine ongeveer een halfjaar en heeft ongeveer 3 miljoen records verzameld. Dat wil zeggen ca 500.000 records per maand. Het zal mooi zijn als de applicatie het over een paar jaar ook nog functioneel is

Ik heb de volgende vragen:
- Is er nog wat te optimaliseren aan mijn queries/wijze van afhandelen (zie ^{"Wat ik al gevonden of geprobeerd heb"})
- Zou Partitionieren van de tabel een optie zijn? Zo ja, welke methode zal voor mijn toepassing het beste zijn en levert het in mijn geval ook performance winst op?
- Gezien een raspberry met 1GB geheugen geen query beest is wat zou geschiktere hardware zijn? Ik zou graag over 5 jaar (met 30 miljoen records) de query in pak em beet 1 sec willen kunnen uit voeren.

Relevante software en hardware die ik gebruik
HW: Raspberry Pi 3b+ of eigenlijk een RevPi+ 32GB (deze: https://revolution.kunbus.de/shop/en/revpi-connect-plus)
SW: PHP 7.3, Maria 10.3.22, Apache 2.4.38

Wat ik al gevonden of geprobeerd heb
mijn Methods:

PHP:

    /**
     * getElementLogData_Bulk, haal de log data van een element uit de DB
     *
     * @param string $element het element nummer ("307.S20")
     * @param int $eenheid eenheid voor het filter (een sensor kan meerdere eenheden hebben)
     * @param int $start unix timestamp, start tijd van het filter
     * @param int $end unix timestamp, eind tijd van het filter
     * @param int $skip grote data sets kun je er voor kiezen om records over te slaan
     * @return array            associated array van de data punten binnen het filter
     * @throws Exception
     */
    public static function getElementLogData_Bulk($element, $eenheid, $start, $end, $skip=1) {
        $db = static::getLoggerDB();
        if ($skip > 1) {
            $sql = "SELECT * FROM ( SELECT @row := @row +1 AS rownum, `LogDataWaarde`, `LogDataTimeStamp` FROM ( SELECT @row :=0) r, plc_logdata WHERE LogDataElementNr='$element' AND LogDataEenheidNr=$eenheid AND (LogDataTimeStamp BETWEEN FROM_UNIXTIME('$start') AND FROM_UNIXTIME('$end'))) ranked WHERE rownum % $skip = 1 ";
        } else {
            $sql = "SELECT LogDataWaarde, LogDataTimeStamp FROM plc_logdata WHERE LogDataElementNr='$element' AND LogDataEenheidNr='$eenheid' AND (LogDataTimeStamp BETWEEN FROM_UNIXTIME('$start') AND FROM_UNIXTIME('$end'))";
        }
        echo "<pre>";
        print_r($sql);
        echo "</pre>";

        $stmt = $db->query($sql);

        $return = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (count($return) > 0) {
            return $return;
        } else {
            return [];
        }
    }

    public static function getElementLogData_BulkV2($element, $eenheid, $start, $end, $skip=1) {
        $db = static::getLoggerDB();
        $start = date("Y-m-d H:i:s", $start);
        $end = date("Y-m-d H:i:s", $end);
        $sql = "SELECT LogDataWaarde, LogDataTimeStamp FROM plc_logdata WHERE LogDataElementNr='$element' AND LogDataEenheidNr='$eenheid' AND (LogDataTimeStamp BETWEEN '$start' AND '$end')";
        echo "<pre>";
        print_r($sql);
        echo "</pre>";

        $stmt = $db->query($sql);

        $return = [];

        $i = 0;

        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $record) {
            if ($i==0 || ($i % $skip) == 0) {
                $return[] = $record;
            }
            $i++;
        }

        if (count($return) > 0) {
            return $return;
        } else {
            return [];
        }
    }

Mijn test code:

PHP:

        $start1 = hrtime (true);
        $result1 = Graph::getElementLogData_Bulk("307.S70",0, strtotime("2020-05-26 10:00:00"), time(), 500);
        $end1 = hrtime (true);
        echo 'Bulk orig' . round(($end1 - $start1)/1e+9,3)  . ' sec. <br><br> ';

        $start2 = hrtime (true);
        $result2 = Graph::getElementLogData_BulkV2("307.S70",0, strtotime("2020-05-26 10:00:00"), time(), 500);
        $end2 = hrtime (true);
        echo 'Bulk V2 ' . round(($end2 - $start2)/1e+9, 3). ' sec';

wat het volgende resultaat oplevert:

code:

SELECT * FROM 
 ( 
  SELECT @row := @row +1 
  AS rownum, `LogDataWaarde`, `LogDataTimeStamp` 
  FROM 
  ( 
   SELECT @row :=0
  ) r, plc_logdata 
  WHERE LogDataElementNr='307.S70' 
  AND LogDataEenheidNr=0 
  AND (LogDataTimeStamp 
    BETWEEN FROM_UNIXTIME('1590480000') AND FROM_UNIXTIME('1598437522'))
 ) 
ranked WHERE rownum % 500 = 1
Bulk orig 4.637 sec.

SELECT LogDataWaarde, LogDataTimeStamp FROM plc_logdata 
WHERE LogDataElementNr='307.S70' 
AND LogDataEenheidNr='0' 
AND (LogDataTimeStamp BETWEEN '2020-05-26 10:00:00' AND '2020-08-26 12:25:30')
Bulk test 4.692 sec

De "V2" method heb ik als blinde gok er tussen gegooid ik dacht misschien dat de "query in query" (de officiële term is mij niet bekend) veel performance koste van de mariadb server. Maar blijkbaar maakt het niet veel uit.

De "query in query" doe ik om voor de grafieken, gemaakt met ChartJS, van maximaal bij voorbeeld maximaal 400 data punten te geven. Zodat ik de "periode" van de grafiek flexibel kan gebruiken. zoals in het voor beeld pak ik 3 maanden, maar het kan ook van de afgelopen 2 uur zijn. Dat wil zeggen:
Ik doe eerst een "count" query van de geselecteerde "logwaarde" en "periode"
Vervolgens bepaal ik de skip met:

PHP:

if ($count > 400) {
     $skip = round($count / 400);
} else {
     $skip = 1;
}

[Kalentum]

code:

BETWEEN FROM_UNIXTIME('$start') AND FROM_UNIXTIME('$end')

Dit krijg je nooit snel want FROM_UNIXTIME is een functie en die kan geen index gebruiken (misschien wel met een generated column en dan daar een index op)

Verder: kun je de CREATE TABLE geven van deze tabel zodat we kunnen zien welke indexen erop zitten?

[ Voor 20% gewijzigd door Kalentum op 26-08-2020 13:15 ]

[Bolukan]

Geen antwoord op je vraag, maar mogelijk wel op je probleem: Is het een optie voor je om data te comprimeren/ontdichten/schonen na een tijd, zoals in lijn van de gedachte van influxdb en retention policies?

[borft]

Zonder tabel definitie kunnen we niet veel zeggen denk ik. Heb je al es een EXPLAIN gedaan op je query, gebruikt de query de indeces die je hebt? kan je restricties toevoegen dat de potentiele resultset sneller klein maakt?

Daarnaast denk ik dat je subquery niet heel veel helpt qua performance. Als je het in PHP doet is het nagenoeg even snel, maar je haalt wel 500x zoveel data op! Hoeveel rows levert het uiteindelijk op? anders zou je eventueel je eerste query op kunnen splitsen, eerst de subquery doen, die naar een indexed temporary table laten schrijven, en daar dan de outer query op doen. (de % 500 query is in je huidige opzet redelijk duur, want geen index, en je gooit dus 499/500 rows weg, dus inefficient)

Wat betreft geschikte hardware, waar zit je bottleneck? is het i/o? of is het CPU limited?

[ Voor 35% gewijzigd door borft op 26-08-2020 13:28 ]

[Anoniem: 336502]

Kalentum schreef op woensdag 26 augustus 2020 @ 13:13:

code:

BETWEEN FROM_UNIXTIME('$start') AND FROM_UNIXTIME('$end')

Dit krijg je nooit snel want FROM_UNIXTIME is een functie en die kan geen index gebruiken (misschien wel met een generated column en dan daar een index op)

Verder: kun je de CREATE TABLE geven van deze tabel zodat we kunnen zien welke indexen erop zitten?

FROM_UNIXTIME gebruik ik eerlijk gezegd uit gemakzucht... Stel ik doe het net zoals de "V2" met een date("Y-m-d H:i:s", $start); heeft dit op deze manier dan wel een index? (ik ben geen "SQL guru")
Voor de vorm heb ik het even getest...

SQL:

Showing rows 0 - 24 (264 total, Query took 8.4050 seconds.)
SELECT * FROM ( SELECT @row := @row +1 AS rownum, `LogDataWaarde`, `LogDataTimeStamp` FROM ( SELECT @row :=0 ) r, plc_logdata WHERE LogDataElementNr='307.S70' AND LogDataEenheidNr=0 AND (LogDataTimeStamp BETWEEN '2020-05-26 10:00:00' AND '2020-08-26 12:25:30') ) ranked WHERE rownum % 500 = 1
Showing rows 0 - 24 (264 total, Query took 8.2640 seconds.)
SELECT * FROM ( SELECT @row := @row +1 AS rownum, `LogDataWaarde`, `LogDataTimeStamp` FROM ( SELECT @row :=0 ) r, plc_logdata WHERE LogDataElementNr='307.S70' AND LogDataEenheidNr=0 AND (LogDataTimeStamp BETWEEN FROM_UNIXTIME('1590480000') AND FROM_UNIXTIME('1598437522')) ) ranked WHERE rownum % 500 = 1

de CREATE TABLE (export van phpmyadmin):

SQL:

CREATE TABLE `plc_logdata` (
  `LogDataID` int(11) NOT NULL,
  `LogDataElementNr` varchar(9) COLLATE utf8_bin NOT NULL,
  `LogDataEenheidNr` int(2) NOT NULL,
  `LogDataTimeStamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `LogDataWaarde` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

borft schreef op woensdag 26 augustus 2020 @ 13:25:
Zonder tabel definitie kunnen we niet veel zeggen denk ik. Heb je al es een EXPLAIN gedaan op je query, gebruikt de query de indeces die je hebt? kan je restricties toevoegen dat de potentiele resultset sneller klein maakt?

Daarnaast denk ik dat je subquery niet heel veel helpt qua performance. Als je het in PHP doet is het nagenoeg even snel, maar je haalt wel 500x zoveel data op! Hoeveel rows levert het uiteindelijk op? anders zou je eventueel je eerste query op kunnen splitsen, eerst de subquery doen, die naar een indexed temporary table laten schrijven, en daar dan de outer query op doen. (de % 500 query is in je huidige opzet redelijk duur, want geen index, en je gooit dus 499/500 rows weg, dus inefficient)

Wat betreft geschikte hardware, waar zit je bottleneck? is het i/o? of is het CPU limited?

Tabel definite zie hier boven?

"en je gooit dus 499/500 rows weg"
Hier ben ik me van bewust, ik zou alleen niet weten hoe ik het anders moet doen... het enige wat me te binnen schiet is een loop i.c.m. OFFSET maar dan heb ik een schop in de juiste richting nodig...

"waar zit je bottleneck?"
Het intern geheugen zit redelijk vol... in iddle heb ik 100mb vrij draai ik de query blijft er niks over...
de CPU doet in idle praktisch niks. draai ik de query gaat hij richting de 100%
beide denk ik dus of zou het een het gevolg van het ander zijn?

[ Voor 29% gewijzigd door Anoniem: 336502 op 26-08-2020 13:47 ]

[edeboeck]

Anoniem: 336502 schreef op woensdag 26 augustus 2020 @ 13:34:
[...]
FROM_UNIXTIME gebruik ik eerlijk gezegd uit gemakzucht... Stel ik doe het net zoals de "V2" met een date("Y-m-d H:i:s", $start); heeft dit op deze manier dan wel een index? (ik ben geen "SQL guru")

Neen, want je hebt geen index op het veld LogDataTimeStamp gelegd:

de CREATE TABLE (export van phpmyadmin):

SQL:

CREATE TABLE `plc_logdata` (
  `LogDataID` int(11) NOT NULL,
  `LogDataElementNr` varchar(9) COLLATE utf8_bin NOT NULL,
  `LogDataEenheidNr` int(2) NOT NULL,
  [b]`LogDataTimeStamp` timestamp NOT NULL DEFAULT current_timestamp(),[/b]
  `LogDataWaarde` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Houd er rekening mee dat een index het opvragen van de gegevens versnelt, maar het aanmaken/aanpassen/verwijderen trager (omdat die dan telkens de volledige index op dat veld moet updaten).
Stel dat je veel schrijfoperaties hebt: is het een optie om dagelijks de nieuwe records binnen te trekken in een andere DB (waar je dan wel de nodige indexen plaatst) die je gebruikt voor je opvragingen?
Die andere DB zou je dan eventueel op een ander toestel kunnen zetten als de Raspberry het niet trekt.

[ Voor 4% gewijzigd door edeboeck op 26-08-2020 13:45 ]

[Xiphalon]

Kalentum schreef op woensdag 26 augustus 2020 @ 13:13:

code:

BETWEEN FROM_UNIXTIME('$start') AND FROM_UNIXTIME('$end')

Dit krijg je nooit snel want FROM_UNIXTIME is een functie en die kan geen index gebruiken (misschien wel met een generated column en dan daar een index op)

Verder: kun je de CREATE TABLE geven van deze tabel zodat we kunnen zien welke indexen erop zitten?

Lijkt me dat de waardes die uit die functies komen (die ook nog eens een constante als input hebben en in principe deterministisch zijn) prima in een index opgezocht moeten kunnen worden.

zou je het andersom doen, dus een

code:

SELECT * FROM tabel WHERE FROM_UNIXTIME(kolom) BETWEEN iets1 AND iets2

dan had je een punt.

Voor de TS:
Als je query dit is

code:

SELECT LogDataWaarde, LogDataTimeStamp FROM plc_logdata 
WHERE LogDataElementNr='307.S70' 
AND LogDataEenheidNr='0' 
AND (LogDataTimeStamp BETWEEN '2020-05-26 10:00:00' AND '2020-08-26 12:25:30')

dan heb je dus een index nodig op plc_logdata(LogDataElementNr, LogDataEenheidNr, LogDataTimeStamp)

Zie het als een telefoonboek, een index is de sorteervolgorde.
dus als je in het telefoonboek het telefoonnummer van 'janssen' in 'Rotterdam' wil zoeken met postcode tussen '3000' en '3100' dan gaat dat dus het makkelijkste als de boel gestorteerd is op
achternaam, stad, postcode
of
stad, achternaam, postcode

[ Voor 30% gewijzigd door Xiphalon op 26-08-2020 13:51 ]

[edeboeck]

Xiphalon schreef op woensdag 26 augustus 2020 @ 13:46:
[...]

Lijkt me dat de waardes die uit die functies komen (die ook nog eens een constante als input hebben en in principe deterministisch zijn) prima in een index opgezocht moeten kunnen worden.

zou je het andersom doen, dus een

code:

SELECT * FROM tabel WHERE FROM_UNIXTIME(kolom) BETWEEN iets1 AND iets2

dan had je een punt.

Ben je hier zeker van? Voor zover ik weet wordt een index opgebouwd uit (vaste) waarden die in een binaire (of N-aire) boom worden opgeslagen voor snelle retrieval. Als jij daar een functie op loslaat, creëer je dus een expressie waarvan de value niet in de index zit... is de query-optimalisatie in MySQL al zo ver gevorderd dat die jouw redenering kan maken?

[borft]

Anoniem: 336502 schreef op woensdag 26 augustus 2020 @ 13:34:
[...]
de CREATE TABLE (export van phpmyadmin):

SQL:

CREATE TABLE `plc_logdata` (
  `LogDataID` int(11) NOT NULL,
  `LogDataElementNr` varchar(9) COLLATE utf8_bin NOT NULL,
  `LogDataEenheidNr` int(2) NOT NULL,
  `LogDataTimeStamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `LogDataWaarde` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Ik zie geen enkele index, dus daar kan je denk ik al wat winnen Waar dient eigenlijk de LogDataID voor, wie genereert dat? als je dat bv een auto_increment veld maakt, dan hoef je je row nummer niet meer uit te rekenen, dan die zit dan al in de db (weliswaar met een offset, maar dat boeit niet voor je integer deling).

Ik zou es beginnen met een index op je timestamp, en daarnaast es kijken naar dingen als een primary key. Eigenlijk wil je alle kolommen waar je op filtert in een index hebben. Mogelijk dat een composite index hier ook uitkomst biedt.

Daarnaast zou je ook nog naar de datatypes kunnen kijken. bv LogDataEenheidNR, heeft die echt een 32 bit int nodig? of zou dat ook een tiny int (of bv een enum) kunnen zijn? En weet je echt zeker dat je de LogDataWaarde als float op wilt slaan? MySQL heeft bv een hele mooie decimal daarvoor.

[ Voor 18% gewijzigd door borft op 26-08-2020 13:54 ]

[Xiphalon]

edeboeck schreef op woensdag 26 augustus 2020 @ 13:50:
[...]

Ben je hier zeker van? Voor zover ik weet wordt een index opgebouwd uit (vaste) waarden die in een binaire (of N-aire) boom worden opgeslagen voor snelle retrieval. Als jij daar een functie op loslaat, creëer je dus een expressie waarvan de value niet in de index zit... is de query-optimalisatie in MySQL al zo ver gevorderd dat die jouw redenering kan maken?

Andersom de poster gaf aan dat een query als

SELECT * FROM tabel WHERE kolom BETWEEN functie(a) AND functie(b)

niet snel te krijgen is. Dat klopt (meestal) niet, omdat de waardes uit de functies prima in de index zijn op te zoeken

Zoekwoord hierbij is SARGable

[Xiphalon]

borft schreef op woensdag 26 augustus 2020 @ 13:52:
[...]


Ik zie geen enkele index, dus daar kan je denk ik al wat winnen Waar dient eigenlijk de LogDataID voor, wie genereert dat? als je dat bv een auto_increment veld maakt, dan hoef je je row nummer niet meer uit te rekenen, dan die zit dan al in de db (weliswaar met een offset, maar dat boeit niet voor je integer deling).

Ik zou es beginnen met een index op je timestamp, en daarnaast es kijken naar dingen als een primary key

Juist niet op de timestamp (zie mijn edit) maar juist eerst op kolommen die constant zijn tijdens uitvoeren van de query

[borft]

Xiphalon schreef op woensdag 26 augustus 2020 @ 13:54:
[...]

Juist niet op de timestamp (zie mijn edit) maar juist eerst op kolommen die constant zijn tijdens uitvoeren van de query

nja, je wilt eigenlijk dat alles wat je in de WHERE hebt staan een index matched. Het doel is, eigenlijk, om het aantal potentiele records in je resultset zo snel mogelijk zo klein mogelijk te maken. Ik verwoord het misschien een beetje ongelukkig. Ik had beter kunnen zeggen: begin eerst maar eens met uberhaupt wat indeces toe te voegen


Oh jah, en gebruik aub geen string concatinaties om je queries op te bouwen. Beter gebruik je prepared statements (of op zijn minst fatsoenlijke escaping).

[ Voor 12% gewijzigd door borft op 26-08-2020 13:58 ]

[Kalentum]

Xiphalon schreef op woensdag 26 augustus 2020 @ 13:46:
[...]

Lijkt me dat de waardes die uit die functies komen (die ook nog eens een constante als input hebben en in principe deterministisch zijn) prima in een index opgezocht moeten kunnen worden.

zou je het andersom doen, dus een

code:

SELECT * FROM tabel WHERE FROM_UNIXTIME(kolom) BETWEEN iets1 AND iets2

dan had je een punt.

Voor de TS:
Als je query dit is

code:

SELECT LogDataWaarde, LogDataTimeStamp FROM plc_logdata 
WHERE LogDataElementNr='307.S70' 
AND LogDataEenheidNr='0' 
AND (LogDataTimeStamp BETWEEN '2020-05-26 10:00:00' AND '2020-08-26 12:25:30')

dan heb je dus een index nodig op plc_logdata(LogDataElementNr, LogDataEenheidNr, LogDataTimeStamp)

Zie het als een telefoonboek, een index is de sorteervolgorde.
dus als je in het telefoonboek het telefoonnummer van 'janssen' in 'Rotterdam' wil zoeken met postcode tussen '3000' en '3100' dan gaat dat dus het makkelijkste als de boel gestorteerd is op
achternaam, stad, postcode
of
stad, achternaam, postcode

Ja, je hebt gelijk, te snel gelezen. MySQL hoeft het in dit geval maar 1 keer te berekenen en niet voor elke rij

[borft]

Kalentum schreef op woensdag 26 augustus 2020 @ 13:57:
[...]


Ja, je hebt gelijk, te snel gelezen. MySQL hoeft het in dit geval maar 1 keer te berekenen en niet voor elke rij

Hoe bedoel je? je vergelijkt de output van een functie, die als input de waarde uit een row heeft met 2 constantes. maw, je moet het juist wel voor elke row in de tabel berekenen. Het lijkt me handiger om de datum, buiten de query, naar het goeie formaat om te zetten, kan in PHP of in MySQL. Dan heb je helemaal geen functie calls nodig, en kan MySQL direct op de index matchen (als die er zou zijn)

[Xiphalon]

borft schreef op woensdag 26 augustus 2020 @ 14:01:
[...]


Hoe bedoel je? je vergelijkt de output van een functie, die als input de waarde uit een row heeft met 2 constantes. maw, je moet het juist wel voor elke row in de tabel berekenen. Het lijkt me handiger om de datum, buiten de query, naar het goeie formaat om te zetten, kan in PHP of in MySQL. Dan heb je helemaal geen functie calls nodig, en kan MySQL direct op de index matchen (als die er zou zijn)

In geval van de TS zijn $start en $end letterlijk 2 constantes die in de querytekst worden gezet dus
functie($start) en functie($end) zijn constant, en daarom is de

code:

SELECT * FROM tabel WHERE veld BETWEEN functie($start) AND functie($end)

prima te versnellen met een index.

[Anoniem: 336502]

Ik heb nu een index op LogDataElementNr LogDataEenheidNr LogDataTimeStamp

SQL:

ALTER TABLE `plc_logdata` ADD INDEX( `LogDataElementNr`, `LogDataEenheidNr`, `LogDataTimeStamp`);

Showing rows 0 - 24 (264 total, Query took 4.4852 seconds.)

SQL:

SELECT * FROM ( SELECT @row := @row +1 AS rownum, `LogDataWaarde`, `LogDataTimeStamp` FROM ( SELECT @row :=0 ) r, plc_logdata WHERE LogDataElementNr='307.S70' AND LogDataEenheidNr=0 AND (LogDataTimeStamp BETWEEN '2020-05-26 10:00:00' AND '2020-08-26 12:25:30') ) ranked WHERE rownum % 500 = 1

De helft van de tijd bedankt tot zover...

(Requote)

borft schreef op woensdag 26 augustus 2020 @ 13:25:
....
Daarnaast denk ik dat je subquery niet heel veel helpt qua performance. Als je het in PHP doet is het nagenoeg even snel, maar je haalt wel 500x zoveel data op! Hoeveel rows levert het uiteindelijk op? anders zou je eventueel je eerste query op kunnen splitsen, eerst de subquery doen, die naar een indexed temporary table laten schrijven, en daar dan de outer query op doen. (de % 500 query is in je huidige opzet redelijk duur, want geen index, en je gooit dus 499/500 rows weg, dus inefficient)

Wat betreft geschikte hardware, waar zit je bottleneck? is het i/o? of is het CPU limited?

"en je gooit dus 499/500 rows weg"
Hier ben ik me van bewust, ik zou alleen niet weten hoe ik het anders moet doen... het enige wat me te binnen schiet is een loop i.c.m. OFFSET maar dan heb ik een schop in de juiste richting nodig...

"waar zit je bottleneck?"
Het intern geheugen zit redelijk vol... in iddle heb ik 100mb vrij draai ik de query blijft er niks over...
de CPU doet in idle praktisch niks. draai ik de query gaat hij richting de 100%
beide denk ik dus of zou het een het gevolg van het ander zijn?

[DataGhost]

@borft vroeg het al, ik heb hem nog niet gezien, wat zegt de EXPLAIN? Daarin zie je waarschijnlijk zelf al waar er (geen) index gebruikt wordt en kan je alvast gaan bedenken hoe je dat wilt fixen. Verder ziet je subquery er echt ronduit kut uit voor je performance en je haalt inderdaad alle rijen op (die passen waarschijnlijk niet allemaal in je cache) om het grootste deel weg te gooien, dus alles moet steeds van disk komen. Als die modulo 500 ongeveer samenvallen met bepaalde tijden (altijd de eerste van het uur ofzo) kan je met een index op de timestamp-kolom (eventueel/wss als datetime) groupen op uur en daar de eerste van pakken, dan kan je index daar gewoon voor gebruikt worden.

[borft]

Xiphalon schreef op woensdag 26 augustus 2020 @ 14:05:
[...]

In geval van de TS zijn $start en $end letterlijk 2 constantes die in de querytekst worden gezet dus
functie($start) en functie($end) zijn constant, en daarom is de

code:

SELECT * FROM tabel WHERE veld BETWEEN functie($start) AND functie($end)

prima te versnellen met een index.

helemaal mee eens, in dit geval zijn de uitkomsten van de functie constant, en kunnen opgezocht worden in de index.

in het andere geval (degene die ik quote):

WHERE functie(kolom) BETWEEN start AND end

gaat die vlieger niet op, dat is wat ik bedoelde.

[Josk79]

Ik mis een ORDER BY in de ranked subquery, dus de volgorde is mogelijk ook niet bepaald. Wellicht een ORDER BY LogDataTimeStamp toevoegen?

EXPLAIN is inderdaad handig om in te zien.

Ik weet niet of het nog uit kan maken om in de ranked-subquery alleen de id's op te halen en vervolgens pas bij de andere query (WHERE rownum % 500 = 1) de overige kolommen er bij te betrekken. Wellicht is er dan veel minder geheugen nodig omdat niet alle records dan hoeven te worden opgehaald, alleen de id's van de records. In dat geval is het natuurlijk ook weer van belang dat LogDataID een index heeft en uniek is.

[Anoniem: 336502]

@DataGhost Ik had hem even gemist..

code:

EXPLAIN SELECT * FROM ( SELECT @row := @row +1 AS rownum, `LogDataWaarde`, `LogDataTimeStamp` FROM ( SELECT @row :=0 ) r, plc_logdata WHERE LogDataElementNr='307.S70' AND LogDataEenheidNr=0 AND (LogDataTimeStamp BETWEEN '2020-05-26 10:00:00' AND '2020-08-26 12:25:30') ) ranked WHERE rownum % 500 = 1;
+------+-------------+-------------+--------+------------------+------------------+---------+-------------+--------+-----------------------+
| id   | select_type | table       | type   | possible_keys    | key              | key_len | ref         | rows   | Extra                 |
+------+-------------+-------------+--------+------------------+------------------+---------+-------------+--------+-----------------------+
|    1 | PRIMARY     | <derived2>  | ALL    | NULL             | NULL             | NULL    | NULL        | 265534 | Using where           |
|    2 | DERIVED     | <derived3>  | system | NULL             | NULL             | NULL    | NULL        |      1 |                       |
|    2 | DERIVED     | plc_logdata | ref    | LogDataElementNr | LogDataElementNr | 33      | const,const | 265534 | Using index condition |
|    3 | DERIVED     | NULL        | NULL   | NULL             | NULL             | NULL    | NULL        |   NULL | No tables used        |
+------+-------------+-------------+--------+------------------+------------------+---------+-------------+--------+-----------------------+

Ik moet eerlijk zeggen dat ik hier niet wijzer van word...

Wat betreft de Group by suggestie: heb ik na wat zoek werk nu dit:
Showing rows 0 - 24 (400 total, Query took 4.6322 seconds.)

SQL:

SELECT LogDataWaarde, LogDataTimeStamp FROM plc_logdata WHERE LogDataElementNr='307.S70' AND LogDataEenheidNr='0' AND (LogDataTimeStamp BETWEEN '2020-05-26 10:00:00' AND '2020-08-26 12:25:30') GROUP BY UNIX_TIMESTAMP(`LogDataTimeStamp`) DIV 20000

[DataGhost]

Ik kan me niet voorstellen dat dat een goeie group is, performance-technisch. Ik denk niet dat het een index kan gebruiken waardoor je dus niks optimaliseert en dat zie je ook in je querytijd (onveranderd). Je wilt iets als GROUP BY YEAR(ldts), MONTH(ldts), DAY(ldts), HOUR(ldts).
De suggestie van @Josk79 is ook een heel goede, zorg dat je in je subqueries alleen de IDs ophaalt (met een logische query, liefst zonder rownum%500 maar waarschijnlijk is die nog redelijk performant in dat geval) en dan in je hoofdquery WHERE id IN (subquery). Wellicht kan je bovenstaande dingen combineren.

In je explain zie je in ieder geval dat drie van de vier delen van je query geen indexen (kunnen) gebruiken, dat eentje daarvan een scan van ongeveer 260k rijen zal opleveren zonder index, en een andere met.
Probeer je subqueries ook eens van binnenuit, dan zal je zien dat de tijd(stoename) bij het stuk query met index een stuk minder zal zijn dan bij de subqueries zonder.

[ Voor 24% gewijzigd door DataGhost op 26-08-2020 15:33 ]

[Josk79]

Ik dacht aan zoiets:

code:

SET @row := 0;
SELECT plc_logdata.* FROM
(
  SELECT 
    @row := @row + 1 AS rownum,
    `LogDataId`
  FROM plc_logdata
  WHERE LogDataElementNr='307.S70' 
  AND `LogDataEenheidNr`=0 
  AND `LogDataTimeStamp` BETWEEN FROM_UNIXTIME('1590480000') AND FROM_UNIXTIME('1598437522')
  ORDER BY `LogDataTimeStamp`
) ranked
INNER JOIN plc_logdata ON plc_logdata.LogDataId=ranked.LogDataId
WHERE rownum % 500 = 1

*EDIT: Nog wat dingetjes gefixt. Lastig een query bouwen zonder de tabel erbij

[ Voor 21% gewijzigd door Josk79 op 26-08-2020 15:40 . Reden: Typos ]

[Reptile209]

Anoniem: 336502 schreef op woensdag 26 augustus 2020 @ 12:53:
Mijn vraag
In een applicatie waarin ik process variablen log in een MySQL database begin ik last te krijgen mijn raspberry performance. Er draait hier nu een machine ongeveer een halfjaar en heeft ongeveer 3 miljoen records verzameld. Dat wil zeggen ca 500.000 records per maand. Het zal mooi zijn als de applicatie het over een paar jaar ook nog functioneel is

[...]

Even voorbijstappen aan het query-gedeelte: heb je al die data tot in groot detail nodig? Zo te zien heb je nu ongeveer elke 5 seconden een meetpunt (500.000 / 30 / 24 / 60 = 11 per minuut). Kan/wil je het je veroorloven om bijvoorbeeld de data ouder dan een week te gaan aggregeren tot bijvoorbeeld 1x per minuut, of 1x per kwartier of uur zelfs? Daarmee groeit je database in ieder geval een heel stuk minder hard.

Of maak een tweede tabel met de geaggregeerde data, want in je voorbeeld pak je nu ook steeds alleen het 500e punt, en gebruik die voor de queries die anders te traag worden. Wil je dan toch een keer in de details duiken, dan is een trage query misschien wel acceptabel (of kan je beter afbakenen welk blok data je nodig hebt).

[Josk79]

Wellicht helpt het als de drie velden niet in één index zitten. Ik weet niet wat de relatie is tussen LogDataElementNr en LogDataEenheidNr, maar misschien die samen in 1 index en de timestamp in een andere index. Of gewoon drie losse indices...

[mcDavid]

Gaat niemand het hebben over de "elephant in the room"?

Je gebruikt een relationele database, terwijl je geen relaties legt maar een timeserie opslaat. Waarom gebruik je niet een timeseries database? Zoals bijv. InfluxDB? Dat is echt 1000x performanter en gebruiksvriendelijker voor dit soort data.

Edit: verder voel ik me ook nog verplicht even te wijzen op de gevaren van SQL-injectie en adviseren deze applicatie in de huidige vorm op geen enkele manier publiek toegankelijk te maken.

[ Voor 28% gewijzigd door mcDavid op 26-08-2020 16:12 ]

[GlowMouse]

mcDavid schreef op woensdag 26 augustus 2020 @ 16:05:
Je gebruikt een relationele database, terwijl je geen relaties legt maar een timeserie opslaat. Waarom gebruik je niet een timeseries database? Zoals bijv. InfluxDB? Dat is echt 1000x performanter en gebruiksvriendelijker voor dit soort data.

Ik geloof die '1000x performanter' niet. Van wat ik zo zie gebruikt InfluxDB een LSM tree waarmee je efficiënt data kunt toevoegen, maar gaat dat juist ten koste van de leessnelheid. Het enige voordeel zou er dus uit kunnen bestaan dat InfluxDB niet ACID-compliant is, maar als je niet om ACID geeft kun je MySQL ook zo instellen dat de leessnelheid hoger wordt. De overhead die zou worden veroorzaakt doordat MySQL relaties ondersteunt, is er niet als je geen relaties legt.

Josk79 schreef op woensdag 26 augustus 2020 @ 15:55:
Wellicht helpt het als de drie velden niet in één index zitten. Ik weet niet wat de relatie is tussen LogDataElementNr en LogDataEenheidNr, maar misschien die samen in 1 index en de timestamp in een andere index. Of gewoon drie losse indices...

Xiphalon heeft mooi uitgelegd waarom de index goed is zoals hij is.


Wat opvalt in de EXPLAIN is de key_len van 33 voor LogDataElementNr. De velden van de index zijn varchar(9) in utf8, int, en timestamp, dus dan kom je op 2829+4+4 bytes _{varstring and blob keys are ALWAYS stored with a 2 byte length prefix}. De index wordt dus niet gebruikt voor LogDataTimeStamp. Kun je eens ANALYZE TABLE draaien en kijken of de EXPLAIN-output daarna wijzigt?

[ Voor 21% gewijzigd door GlowMouse op 26-08-2020 17:03 ]

[mcDavid]

GlowMouse schreef op woensdag 26 augustus 2020 @ 16:48:
[...]

Ik geloof die '1000x performanter' niet. Van wat ik zo zie gebruikt InfluxDB een LSM tree waarmee je efficiënt data kunt toevoegen, maar gaat dat juist ten koste van de leessnelheid. Het enige voordeel zou er dus uit kunnen bestaan dat InfluxDB niet ACID-compliant is, maar als je niet om ACID geeft kun je MySQL ook zo instellen dat de leessnelheid hoger wordt. De overhead die zou worden veroorzaakt doordat MySQL relaties ondersteunt, is er niet als je geen relaties legt.

Het leek me evident dat die "1000x" metaforisch was. Mijn persoonlijke ervaring is dat logdata-agregaties queryen in InfluxDB aanzienlijk sneller gaat, en ook makkelijker is met InfluxQL.
Overigens wil ik niet zeggen dat het "fout" is om MySQL te gebruiken, er zijn genoeg redenen te verzinnen waarom dat in de praktijk toch handiger kan zijn. Maar je doet jezelf wel tekort als je niet ook naar alternatieven kijkt.

[Gomez12]

GlowMouse schreef op woensdag 26 augustus 2020 @ 16:48:
[...]
Ik geloof die '1000x performanter' niet. Van wat ik zo zie gebruikt InfluxDB een LSM tree waarmee je efficiënt data kunt toevoegen, maar gaat dat juist ten koste van de leessnelheid. Het enige voordeel zou er dus uit kunnen bestaan dat InfluxDB niet ACID-compliant is, maar als je niet om ACID geeft kun je MySQL ook zo instellen dat de leessnelheid hoger wordt. De overhead die zou worden veroorzaakt doordat MySQL relaties ondersteunt, is er niet als je geen relaties legt.

Het x keer performanter van influxdb / timeseries zit hem voornamelijk in het feit dat ze gewoon anders werken.
Erg gesimplificeerd slaan ze enkel een timestamp met 1 value op in een tabel per measurement, je kan wel iets van tags / andere velden toevoegen, maar daar kan je er expliciet niet te veel van hebben over tijd(of je trekt je hele performance naar beneden)

En in principe kan je de data ook alleen maar via timestamps per measurement opvragen. Waardoor je simpelweg een ander model krijgt, want je kan het dan bijv ook opslaan via imbalanced b-tree's die je op tijd laat splitsen ipv op waardes, sla een gemiddelde waarde van de onderliggende waarde op per b-tree splitsing en je kan zeer goedkoop waardes met een resolutie van 5 sec of 1 minuut of nog hoger krijgen, doordat je gewoon de b-tree's maar gedeeltelijk afdaalt.

Dat soort dingen is erg moeilijk/onmogelijk in mysql te realiseren. Alleen time-series is wel even een complete andere tak van sport...

Wat je wel zou kunnen doen / overpakken uit time-series is simpelweg een 2e / 3e / 4e tabel aanmaken met aggregates per vastgestelde tijdseenheid. Dus maak een 2e tabel aan : plc_logdata_per_minute of een 3e tabel : plc_logdata_per_500_units waarin je gewoon 1 op de 500 resultaten opslaat (die pak je nu ook al alleen haal je eerst 499 irrelevante results op).
En bereken met een cron_process die aggregated tables elke nacht oid.

kijk aan het einde van de rit is het gewoon heel simpel : Als jij 500 records ophaalt om 1 record te lezen, dan moet MySQL dus ook 500x zoveel data verstouwen, dus ook 500x zoveel memory reserveren etc. etc.
En zeker met grafieken etc wil je niet 500.000 waardes ophalen om 1 dag te tonen, maar wil je bijv slechts 1 waarde per minuut hebben (/1440 per dag) bereken dat eenmalig in een cron_job vooraf en je haalt nog maar 1440 records/waardes op per keer dat je de grafiek moet tonen.

Indexen etc gaan je maar tot zover helpen als jij je data opslaat per seconde en het in een jaargrafiek wilt tonen met punten per dag, want dan moet hij gewoon elke keer on the fly die data lezen en functies erop uitvoeren. Terwijl in wezen je gegevens in het verleden onveranderbaar zijn en gewoon vast te aggregeren.

Dat is mede waarom een time-series-databases ook relatief klein kunnen zijn, ze aggregeren de hele tijd. Het is niet echt een standaard vraag dat je van een jaar geleden nog wilt weten wat er op welke seconde gebeurde. Veelal ben je max geinteresseerd (bij data van 1 jaar geleden) in wat er gemiddeld per uur gebeurde (=3600x reductie van data / supersimplistisch gedacht 3600x sneller te verwerken data)

Wat ik zou zeggen is (mits je alle data wilt bewaren) :
- Splits je hoofdtabel naar current_data en archive_data (waarbij archive_data bijv geen indexes kent).
- Maak aggegratie tabellen aan
- Maak een paar cron_jobs aan die de current_data aggregeren en overzetten naar archive_data.

En op die manier kan je een live systeem maken die bijv laatste 24 uurs data uit current_data trekt, laatste week_grafiek komt uit een aggregatie tabel etc. En mocht je het toch echt willen weten dan kan je je archive_data gaan indexeren / naar boven halen om daarop te gaan query'en.

Want alhoewel een db met honderden miljoenen records nog steeds prima te benaderen is op een raspberry pi, vereist dat wel het betere sql-werk en alsnog zitten daar grenzen aan.
Terwijl met een behoorlijk aggregatie systeem je veelal veel sneller en met veel minder kennis je doel bereikt

[GlowMouse]

Gomez12 schreef op woensdag 26 augustus 2020 @ 22:17:
[...]

Het x keer performanter van influxdb / timeseries zit hem voornamelijk in het feit dat ze gewoon anders werken.
Erg gesimplificeerd slaan ze enkel een timestamp met 1 value op in een tabel per measurement, je kan wel iets van tags / andere velden toevoegen, maar daar kan je er expliciet niet te veel van hebben over tijd(of je trekt je hele performance naar beneden)

Je reduceert de overhead per record waardoor je de memorybus efficiënter benut. De overhead per record in MySQL is vrij fors. TS kan op dit vlak al winst halen door de string LogDataElementNr te normaliseren en zijn huidige index als primary key in te stellen. Dan zou het gaan om 19 bytes overhead per record op 20 bytes aan data. Ongeveer een factor 2, geen 1000, en alleen op de memorybus.

want je kan het dan bijv ook opslaan via imbalanced b-tree's die je op tijd laat splitsen ipv op waardes, sla een gemiddelde waarde van de onderliggende waarde op per b-tree splitsing en je kan zeer goedkoop waardes met een resolutie van 5 sec of 1 minuut of nog hoger krijgen, doordat je gewoon de b-tree's maar gedeeltelijk afdaalt.

De index die TS gebruikt is ook een b-tree die op tijd is gesplitst. Het probleem is dat die splitsing in tijd nog niet wordt gebruikt waardoor alle records voor een halfjaar worden doorlopen ipv alleen de records van één dag. Als ANALYZE TABLE dat blijkt te verhelpen, heb je een factor 100 tijdswinst.

Het gemiddelde van de onderliggende nodes in een node opslaan is inderdaad niet mogelijk en vereist een aparte tabel zoals je voorstelt. Bij InfluxDB zie ik die mogelijkheid zo snel trouwens niet terug.

[Gomez12]

GlowMouse schreef op woensdag 26 augustus 2020 @ 22:45:
[...]
De index die TS gebruikt is ook een b-tree die op tijd is gesplitst. Het probleem is dat die splitsing in tijd nog niet wordt gebruikt waardoor alle records voor een halfjaar worden doorlopen ipv alleen de records van één dag. Als ANALYZE TABLE dat blijkt te verhelpen, heb je een factor 100 tijdswinst.

De truc bij een time-series db is dat de b-tree echt specifiek op vaste tijdseenheden is gesplitst, zelfs al worden de b-trees imbalanced doordat je bepaalde tijdsdelen mist etc.
Het lijkt me zeer onwaarschijnlijk dat mysql dit ook zou doen, omdat het gewoon een heel aparte use-case is voor een index, normaliter wil je die gewoon balanced over je waardes hebben.

Grofweg gezegd zegt een time-series gewoon dat als jij 10.000 waardes in 0:01 hebt en 10 waardes in 0:02, splits de b-tree alsnog op de seconde, want dat is gewoon de standaard use-case waarop het gebruikt wordt.
Terwijl ik bij mysql zou verwachten dat er grofweg 5005 in de ene index-leaf terechtkomen en 5005 in de andere index-leaf.

Wat ervoor zorgt dat je bij een tijdsperiode x je bij mysql een onbekend aantal leafs op moet halen, terwijl dit bij time-series normaliter een bekend aantal is.

Time-series zijn dan ook echt gebouwd op een relatief gelijk aantal waardes per tijdseenheid.

Het gemiddelde van de onderliggende nodes in een node opslaan is inderdaad niet mogelijk en vereist een aparte tabel zoals je voorstelt. Bij InfluxDB zie ik die mogelijkheid zo snel trouwens niet terug.

Ik ben bij InfluxDB de weg een beetje kwijtgeraakt toen ze hun hele storage engine gingen herbouwen.
Maar als ik het zo snel even nakijk benoemen ze het idd niet, alhoewel ze wel iets hebben qua level 1-4 compactions.
En aan de andere kant heb ik het ook niet echt over influxdb maar meer over time-series db's in het algemeen en dan nog een extreem high-level overview

Ik zie daarentegen wel weer dat influxdb je data wil sharden per dag of 7 dagen, maar ik kan me niet echt voorstellen dat dat genoeg is voor iets van een overview over 5 jaar opgeslagen per micro-seconde.

Maar qua snelheid vergeet ook niet dat time-series "zogezegd" 1 measurement per tabel opslaan, waar dat in sql standaard meerdere measurements per tabel zijn.

En uiteraard is die 1000x sneller deels marketingpraat en kan je in sql best een soortgelijke structuur opbouwen die soortgelijke snelheden kan halen.
Alleen in sql is het imho toch ietwat ongewoon om een clustered index op een timestamp te maken met 1 tabel per measurement.
In sql zou ik eerder zeggen meerdere measurements per tabel en dan clustered index op measurement / timestamp, zodat je per measurement het op timestamp eruit kan halen, alleen dit gaat wel ten koste van je write speed.
Oftewel qua hoe de gemiddelde persoon omgaat met databases (fake id's / pk's zie ik nog vaak genoeg voorbij komen) is een time-series database "1000x" sneller omdat het gewoon anders werkt.

---

Hetzelfde heb je met bijv document-dbases, je kan in sql ook gewoon een heel "document" als json/bson/xml in 1 record van het type nvarchar(max) opslaan en dan gaan klooien met kunstzinnige indexen etc die creatief allerlei data uit je "document" gaan trekken.
Waardoor het grootste verschil tussen sql en document-databases het verschil wordt tussen eventual consistency van je indexen en immediate consistency.

Alleen praktisch werk je met sql met een rdbms en moeten de extra kosten van joins etc redelijk teniet gedaan worden door bandwidth besparingen (je gooit niet met gedeserializede documenten van x KB de hele tijd over en weer) en flexibiliteit.

Het valt er redelijk in te bouwen en mogelijk kom je qua snelheid in de buurt, alleen het wordt een martelgang omdat de standaard tooling simpelweg niet ervoor gebouwd is.
Alhoewel MS SQL en Oracle een paar jaar terug wel bezig was om de standaard tooling uit te breiden om dit soort dingen wel te kunnen ondersteunen.