Cea mai rapida metoda de a extrage din baza de date un numar exact de row-uri, din ultimele 24 ore, cand intervalul de adaugare difera

sibianul · septembrie 10, 2017, 1:15pm

Titlul e cam lung, nu cred ca ati inteles ce vreau, incerc sa explic in urmatoarele randuri.

Am o baza de date unde salvez valoare mai multor senzori, majoritatea senzorilor trimit date noi odata la 5 minute, am altii care trimit odata la 2 minute, la 1 minut, si senzorul de energie (Wati) l-am setat sa trimita la fiecare 10 secunde.

Acum am facut o pagina noua in care am o lista cu toti senzorii activi in ultimele 24 de ore, dar din cauza ca unii din senzori au salvat foarte multe randuri (mult mai des decat alti senzori), astept destul de mult pana se afiseaza pagina in browser. As vrea ca toate graficele sa contina informatii din ultimele 24 de ore, dar as vrea ca fiecare grafic sa fie format din maxim 100 de puncte

Am mai avut problema asta si inainte cand am inceput lucrul la dashboard, cand am utilizat AND id MOD $every_n_th_row = 0 , dar aceasta varianta este foarte foarte inceata, query-ul se executa mult mai rapid daca efectiv afisez sute de rows-uri, in loc sa le limitez folosind id MOD $every_n_th_row = 0 in query.

Mai jos va pun functia care am facuto si pe care o folosesc acum, merge mult mai rapid decat id MOD $every_n_th_row = 0, dar acum in pagina in care am lista cu toti senzorii, se intarzie cam mult, si as vrea sa gasesc o varianta si mai rapida.

Mai demult ma gandeam sa imi fac si alte tabele “arhiva pe termen lung”, odata pe zi sa rulez un script care copiaza informatiile de la toti senzorii, inregistrate in ziua anterioara, dar la un interval mai rar, tabelul cu arhiva zilnica sa contina informatii la fiecare 15 minute, tabelul cu arhiva lunara sa contina datele trimise odata la 30 minute, iar tabelul cu arhiva anuala odata la 1 ora … - asta era una dintre ideile mele pt a face extragerea datelor mai rapida, pt afisarea unor grafice - nu foarte detaliate.

Acum va rog pe voi sa imi spuneti cum ati face voi extragerea datelor din baza de date, tinand cont ca unii senzori salveaza 21600 randuri / zi, altii 1440 randuri / zi si altii 288 randuri / zi . iar toate graficele ar trebui sa contina informatii din ultimele 24 ore, dar avand maxim 100 - 200 randuri fiecare ?

Asta e una dintre functiile pe care o folosesc pt extragerea datelor, functia asta calculeaza automat $every_n_th_record, variabila pe care o trimit cand apelez functia nu se mai foloseste, dar am alta functie care tine cont de acea variabila. Oricum ar fi … ambele au cate o problema, sunt oarecum incete, cand queryul returneaza foarte multe randuri

function get_last_24hours_readings($sensor_id,$sensor_value,$readings=10,$every_n_th_record=1, $hours=1)
{
	global $db;
	
	$hours_to_time = $hours * 3600;
	$x_hours_ago = time() - $hours_to_time;	
		
	$n =0;																										// ".(($every_n_th_record!=1) ? 'AND id mod '.$every_n_th_record.' = 0' : '')."
	
	$q_sensor = mysqli_query($db, "SELECT `value_integer` FROM `home_devices_status` WHERE `value_name` = '".$sensor_value."' AND device_id = '".$sensor_id."'  AND added_date > ".$x_hours_ago."  ORDER BY `id` DESC");
	$values = '';
	$r=0;
	$total_rows = mysqli_num_rows($q_sensor);
	
	if($total_rows > $readings)
	{
		$dinamic_every_nth_row = round($total_rows / $readings);
	}
	else
	{
		$dinamic_every_nth_row = 1;
	}
	
	if($total_rows != 0)
	{
		
		while($sensor = mysqli_fetch_assoc($q_sensor))
		{
			if($n==$dinamic_every_nth_row)
			{
				$values = $sensor['value_integer']. (($r==0)?'':','). $values;
				$n=0;
				$r++;
			}
			
			$n++;
		}
	}

	return $values;

}

In JSfiddle de jos am pus codul HTML dar si timpul necesar pt generarea fiecarei sectiuni de cod (in comment html), o sa vedeti ca numarul valorilor din fiecare grafic este similar, dar timpul pt generarea HTML-ului difera foarte mult, la unii senzori a durat 0.05secunde, la altii 6 secunde

https://jsfiddle.net/j2n24g7c/1/

Multumesc. Va doresc o duminica frumoasa tuturor

Ivascu_Madalin · septembrie 10, 2017, 4:15pm

Deci trebuie sa grupezi randurile pe device_id si apoi sa folosesti ceva gen limit pentru a limita numarul de inregistrari,
Cred ca o sa-ti trebuiasca si un selfjoin

o idee ar fi raspunsul asta pe so

sibianul · septembrie 10, 2017, 4:26pm

Nu, datele fiecarui sensor le scot pe rand din DB, am si unele grafice care contin valoarea a doi senzori, dar nu vreau sa complic acum probleme pt o exceptie bathroom

Nu vreau cu limit, adica din cate stiu eu nu cu limit poti face ce vreau eu, pt ca nu vreau ultimele 100 de inregistrari, sau 100 de inregistrari consecutive, chiar daca nu sunt ultimele, ci vreau ultimele 24 de ore, ultimele 24 de ore in 100 de puncte, adica cam 4 puncte pe ora

Ivascu_Madalin · septembrie 10, 2017, 4:38pm

atunci ar trebui sa calculezi minutele sa fie un multiple al raportului nr de puncte/total puncte pe h

Luxian · septembrie 10, 2017, 4:51pm

E greu de zis ce metodă te-ar ajuta cel mai mult fără să putem testa noi înșine, însă uite câteva idei:

Primul lucru care l-aș verifica eu ar fi să am indecși setați pentru tabela respectivă, astfel SQL-ul poate să filtreze mai rapid înregistrările.

Apoi, în loc să extragi absolut toate datele din baza de date și să le filtrezi după aceea cu N-th row mai bine folosești GROUP BY să îți grupezi valorile în intervale de timp și funcții gen AVG, MAX sau MIN să calculezi valorile pentru intervalul respectiv. Așa lași toată procesarea pe seama bazei de date și o să fie mult mai puține date de transferat între PHP și baza de date. Momentan dacă păstrezi doar o înregistrare din 5 înseamnă că 80% din transferul de data DB - PHP e inutil.

Ca să faci asta trebuie doar schimbi query-ul în ceva de genul:

$interval = 15; // în secunde
$sql = "
-- AVG() o să să-ți returneze average-ul pentru fiecare grup/interval
SELECT AVG(`value_integer`) as `value_integer`
FROM `home_devices_status` 
WHERE `value_name` = '{$sensor_value}' 
  AND device_id = '{$sensor_id}'  
  AND added_date > {$x_hours_ago}  
-- asta ar trebuie să grupeze rezultatele in intervale de X secunde
GROUP BY added_date MOD {$interval}
ORDER BY `id` DESC"

Dacă și după asta tot e încet, atunci aș încerca să fac cache la rapoarte și să le procesez cât mai rar. Intervalele noi le poți adăuga la valorile deja calculate, și nu mai trebuie să re-calculezi ultimele 24 de ore la fiecare refresh.

Ideea cu a face un tabel separat unde să ții doar rapoartele nu e rea, dar de obicei asta se implementează mai târziu. Dacă aplicația e la început ar trebui să meargă și fără o vreme.

O altă chestie pe care o observ e că apelezi funcția aia pentru fiecare senzor și SQL-ul trebuie să parcurgă toată tabela de fiecare dată. Poate ar fi mai rapid să faci o singură interegorare pentru toți senzorii și apoi să separi valorile returnate în PHP.

sibianul · septembrie 10, 2017, 5:31pm

Dashboardul respectiv ruleaza pe un BananaPi, baza de date e pe HDD, nu pe card. si Deocamdata ruleaza doar local, nu am deschis portul 80 pt a putea vi vizualizata interfata dinafara retelei.

Partea interesanta e ca tocmai din cauza ca am folosit MOD in query, mi s-a incetinit aplicatia, efectiv dupa ce am scos partea cu MOD afara din query si am procesat toate randurile folosind PHP, rezultatul aparea mult mult mai rapid. Poate devina e si BananaPi-ul, ca “aplicatia” nu ruleaza pe un server dedicat, as fi putut pune de test pe unu din serverele care le am online, dar nu avea nici un rost pe ca oricum eu vreau neaparat datele astea sa le am doar local, deci tot pe un Pi .

O sa incerc acum queryul tau, sa vad cum ruleaza, dar daca crezi ca te ajuta, am pus tabelul cu datelel pe wetransfer https://we.tl/jSyxaxGUkl

Mersi si pt tip-ul cu un singur query pt toti senzorii, in loc de cate un query pt fiecare, . Din cate observ la timpii de executie pt fiecare, probleme sunt doar la senzorii care raporteaza foarte des (odata pe minut, azi am si modifcat crontab-ul ala la odata la 5 minute pt ca nu era nimic critic la valorile alea, nu sunt deloc importante, as putea trai si fara … singurul senzor care chiar vreau sa trimita foarte des este cel de energie, dar deocamdata l-am deconectat pt ca vreau sa fac un cablaj pt tot sistemul de masurare a tensiunii si a energiei, pt toate cele 3 faze de 220v)

< ! – 0.0049948692321777s (+0.0050477981567383s) -->Pana aici sunt cateva queryuri pt setari, chestii simple
< ! – 6.5342409610748s (+6.5292110443115s) --> timpul necesar pt a scoate valorile primului senzor care scria date noi odata la 60 secunde
< ! – 10.370232820511s (+3.8359620571136s) --> acelasi device_id, dar alt senzor tot la 1 minut
< ! – 13.863029003143s (+3.4927668571472s) --> acelasi device_id, dar alt senzor tot la 1 minut
< ! – 17.158525943756s (+3.2954709529877s) --> acelasi device_id, dar alt senzor tot la 1 minut
< ! – 17.65100479126s (+0.49245190620422s) --> alt device id, senzorul salveaza informatii odata la 5 minute
< ! – 17.660460948944s (+0.0094320774078369s) --> acelasi device id, alt senzor, tot la 5 minute
< ! – 18.401435852051s (+0.74094605445862s) --> alt device_id, senzorul salveaza informatii odata la 5 minute
< ! – 18.466317892075s (+0.064864158630371s) --> acelasi device id, alt senzor, tot la 5 minute
< ! – 19.986643791199s (+1.5202460289001s) --> alt device_id, senzorul salveaza informatii odata la 5 minute
< ! – 20.036381959915s (+0.049710988998413s) --> acelasi device id, alt senzor, tot la 5 minute

Din cate vedeti, foarte mult dureaza doar la device-ul care salveaza foarte des, odata la 1 minut, apoi la urmatoarele deveice-uri, primul query (cu tot cu procesarea in php) dureaza mai mult, adica 0.7 secunde - este suficient de rapid pt mine, iar al doilea query cu acelasi device_id, dar alt senzor, dureaza mult mult mai putin, adica 0.06 secunde … cu tot cu procesarea in PHP

Revin dupa ce fac teste si cu queriul lui Luxian

Multumesc

sibianul · septembrie 10, 2017, 7:53pm

@Luxian Am incercat varianta ta si intr-adevar queriurile se executa mai rapid, dar am alta problema, in sensul ca la senzorii care adauga informatii in baza de date odata / minut , nu imi scoate decat o singura valoare din baza de date, acum vad ca numarul valorilor extrase din baza de date difera destul de mult, in functie frecventa de adaugare a informatiilor, la fel am si rezultatelor, adaug des, am multe rezultate, adaug rar, am putine puncte in grafic. Eu as fi vrut sa fie un numar constant, dar nu neaparat exact identic.

Am folosit $interval = 300; … 200 si 100 dar tot o singura valoare se extragea pt acel senzor, desi pierde aproape la fel de mult timp ca si varianta in care am scos toate rows-urile si le-am filtrat din PHP.

Mai jos este un screenshot cu comparatia intre codul vechi si cel nou, cu gri in fata numelui senzorului am pus timpul necesar pt executia codului, jos de tot in stanga e timpul total.

Tabelele dedicate unei arhive pe termen lung cred ca sunt o solutie buna, mai ales avand in vedere ca deja baza de date a ajuns la o dimensiune destul de mare, in doar 2 luni, are 123Mb si ~1.5m randuri, cred ca cel mai bine ar fi sa fac si alte tabele in care salvez datele pe termen lung, la un interval mult mai rar, decat in tabelul curent, iar pe acesta in care scriu foarte des , sa sterg constant toate informatiile mai vechi de 30 de zile, pt a evita cresterea numarului de inregistrari salvate in acel tabel.

sibianul · septembrie 10, 2017, 8:33pm

@Luxian , oare se poate optimiza query-ul tau sa returneze informatiile si mai rapid, si corect in acelasi timp, in poza de mai jos se vede diferenta dintre datele returnate de fiecare varianta de cod, la cea furnizata de tine, pe langa ca la primii senzori am doar o singura inregistrare, imi apar difernete destul de mari la numarul de valori returnate, la un senzor sunt 600 de valori, la altul 33 de valori. Diferenta de timp nu e foarte mare, sau mai bine zis, tot nu sunt multumit de cat de rapid mi se afiseaza informatia, tot mai mult ma gandesc sa imi fac acele tabele in care salvez informatia mult mai rar, si pt graficele astea care nu sunt detaliate deloc, sa iau direct de acolo totul, cu siguranta nu voi mai avea probleme.

Doar acum cand mi-am modificat functia care returneaza valorile unui senzor, am vazut cat de multe rows-uri se proceseaza … degeaba. Acum functia nu mai returneaza exact valorile senzorului, ci un array in care am si numarul maxim, numarul lor dupa filtrare, astea doua le-am adaugat pt debugging, dar vroiam neaparat sa am si valoarea minima si cea maxima.

Legat de valorile astea minime si maxime, oare exista vreo varianta sa returneze in ACELASI query atat toate informatiile din ultimele 24 de ore, ale unui senzor cat si minimul si maximul folosind SELECT MAX(value_integer) as max_value, MIN(value_integer) as min_value ? Daca adaug sintaxa asta vad ca ramane un singur rezultat , nu se mai scot toate .

O sa ma gandesc si la varianta de cache, un tabel dedicat in care sa salvez toate valorile din ultimele 24 ore ca si un singur string, iar de fiecare data cand senzorul trimite o noua informatie, sa se verifice data ultimei actualizari a cache-ului, daca e mai mai veche de 10 - 15 minute, sa adauge la sfarsitul string-ului noua valoare, iar prima valoare din stringul respectiv sa o stearga. In felul asta actualizarea cacheului nu mai implica citirea tabelul mare, ceea ce se pare ca va trebui sa evit.

Mersi pt idei. Daca mai aveti si altele sunt binevenite

O seara faina tuturor.

Catalin_Maftei · septembrie 10, 2017, 8:49pm

salut Marius.

ce face: GROUP BY added_date MOD {$interval}
???

am rulat de curiozotate in mysql:
select ‘2017-01-01’ mod 1
, ‘2017-01-01’ mod 2
, ‘2017-01-01’ mod 3
, ‘2017-01-01’ mod 4
, ‘2017-01-01’ mod 5
, ‘2017-01-01’ mod 6
, ‘2017-01-01’ mod 7
, ‘2017-01-01’ mod 8
, ‘2017-01-01’ mod 9
, ‘2017-01-01’ mod 10
, ‘2017-01-01’ mod 11
, ‘2017-01-01’ mod 12
, ‘2017-01-01’ mod 13

nu inteleg nimic din rezultate …

Catalin_Maftei · septembrie 10, 2017, 9:07pm

trebuie sa te joci un pic cu index sa vezi cum “apreciaza” mysql interogarea
daca este acceptabil average per hour, ca sa ruleze si mai repede poti sa adaugi un camp “ora” si faci update si index pe el

SELECT 	AVG(`value_integer`) as `value_integer`
		, count(`id`) as `cate_valori`
        , ora
FROM `home_devices_status` 
WHERE `value_name` = '{$sensor_value}' 
  AND device_id = '{$sensor_id}'  
  AND added_date > {$x_hours_ago}
GROUP BY ora
ORDER BY `ora` DESC;

sau incerci direct un query de genul dar se poate sa dureze mult … pt. ca treb sa determine ora pt. fiecare row din where

SELECT 	AVG(`value_integer`) as `value_integer`
		, count(`id`) as `cate_valori`
        , date_part('hour', stamp_created) as `ora`
FROM `home_devices_status` 
WHERE `value_name` = '{$sensor_value}' 
  AND device_id = '{$sensor_id}'  
  AND added_date > {$x_hours_ago}
GROUP BY date_part('hour', stamp_created)
ORDER BY 3 DESC

sibianul · septembrie 11, 2017, 5:48am

@Catalin_Maftei eu salvez added_date cu time(), nu in formatul afisat de tine, poate si din cauza aia query-ul cu MOD x nu afisa rezultate corecte. Acum multi ani de zile cand am inceput sa invat PHP/MySQL mi s-a parut cea mai simpla varianta de salvare a unui timestamp, si de atunci nu am mai folosit altceva, decat in cateva cazuri cand aveam nevoie sa salvez un timestamp mai vechi de 1970. Daca salvare timestamp-ului intr-o coloana de tip DATETIME ajuta la extragerea datelor mai rapid, sunt dispus sa modific tabelul, nu tin neaparat sa am data in format unixtime, dar dupa cum v-am spus , asa a fost cel mai usor pt mine, si inca mi se pare cea mai simpla varianta , si pt filtrare, si pt afisarea

Alex_Chisca · septembrie 11, 2017, 5:50am

Salut

Poti incerca sa scoti dintr-un singur query informatiile pentru toti senzorii ( group by device_id mai intai si dupa restul )
Pune ORDER BY id ASC, posibil ca DESC sa afecteze timpul de executie. ( poti inversa mai departe rezultatul )
Rezultate pentru fiecare ora ar fi GROUP BY HOUR(added_date) - daca exista doar o zi. Iar pentru ca sa aduci toti senzorii cu un singur query : GROUP BY device_id, HOUR(added_date).
Fii sigur ca ai index pe id, value_name, device_id, added_date. Posibil ca ajuta si value_integer ca index.

Cred ca ai putea sa pui structura la tabela/e, in felul asta te poate ajuta sigur cineva.

sibianul · septembrie 11, 2017, 6:08am

Am incercat ORDER ASC in loc de DESC, diferenta este fff mica 20.851s vs 21.082s

Legat de GROUP BY HOUR(added_date) , functioneaza daca in coloana added_date am unixtime? Coloana respectiva este de tip int, nu este DATETIME.

mersi .

--
-- Table structure for table `home_devices_status`
--

CREATE TABLE `home_devices_status` (
  `id` int(11) NOT NULL,
  `device_id` int(11) NOT NULL,
  `value_name` varchar(100) NOT NULL,
  `value_integer` double(7,2) NOT NULL,
  `value_text` varchar(50) NOT NULL,
  `added_date` int(13) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `home_devices_status`
--
ALTER TABLE `home_devices_status`
  ADD PRIMARY KEY (`id`),
  ADD KEY `thishelps` (`device_id`,`value_name`,`added_date`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `home_devices_status`
--
ALTER TABLE `home_devices_status`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

Luxian · septembrie 11, 2017, 7:29am

Acum îmi dau seama că am greșit lamentabil cu query-ul. Ar trebui să fie CEIL(date / $interval) și nu MOD. Dacă ai un GROUP BY valid atunci o să returneze o valoare per grup. În cazul de față (cu query-ul greșit) dacă toți senzorii sunt citiți în aceiași secundă nu o să primești decât o valoare. Încearcă cu CEIL(date/interval) și ar trebui să meargă.

Am făcut un query de test cu baza de date postată de tine și phpmyadmin zice că a durat 3.6 secunde pentru toți senzori și toate valorile de la 1 septembrie grupate pe intervale de 5 secunde:

SELECT 
  h.*,
  COUNT(h.id) as `group_count`,
  AVG(value_integer) as `value_integer_group_average`,
  MAX(value_integer) as `value_integer_group_max`,
  MIN(value_integer) as `value_integer_group_min`
FROM `home_devices_status` h
WHERE h.added_date > 1504224000
GROUP BY h.device_id, h.value_name, CEIL(h.added_date / 5)

Văd că tabela are deja indecși

Am încercat și ce a sugerat @Alex_Chisca și am adăugat o coloană cu valoare după care se face grouparea și a ieșit cam așa:

-- Adăugare coloană (cred că poți face câte una pentru fiecare interval care-ți trebuie)
ALTER TABLE `home_devices_status` ADD `interval_5s` INT NOT NULL , ADD INDEX (`interval_5s`)

-- actualizare valori deja existente  ~ 36 secunde
UPDATE `home_devices_status` SET `interval_5s` = CEIL(`added_date` / 5) WHERE interval_5s = 0; 

SELECT 
  h.*,
  COUNT(h.id) as `group_count`,
  AVG(value_integer) as `value_integer_group_average`,
  MAX(value_integer) as `value_integer_group_max`,
  MIN(value_integer) as `value_integer_group_min`
FROM `home_devices_status` h
WHERE h.added_date > 1504224000
GROUP BY h.device_id, h.value_name, h.interval_5s

Dar nu am văzut nici o diferență majoră de performanță. Însă s-ar putea să țină și de CPU, eu am încercat asta pe un PC cu i5, poate pe Raspberry diferența o să fie mult mai mare.

Am uitat să scriu, dar am presupus că data e salvată ca UNIX TIMESTAMP (numărul de secunde față de 01.01.1970). Cu funcția aia (vezi și corecția de mai sus cu CEIL() în loc de MOD) convertești numărul de secunde în al câtelea interval de $interval secunde a trecut de atunci. În cazul în care data e salvată ca Date atunci trebuie să folosești UNIX_TIMESTAMP(date).

sibianul · septembrie 11, 2017, 9:57am

Mersi mult @Luxian O sa incerc deseara sa vad daca se returneaza un numar constant de rows-uri, indiferent de intervalul de adaugare in baza de date. Zilele astea mi-am tot “pierdut timpul” cu dezvoltarea dashboard-ului dar azi ar trebui sa incep lucrul, ca deja imi sta mintea tot la altea chestii ce as mai putea face Hobby-urile barbatilor … :))

Initial mi-am facut acest dashboard doar pt mine, si mi-am cam facut cam toate modulele afisate in index, hardcoded … dar acum vrea si un prieten sa isi monteze cativa senzori de monitorizare, dar nu il intereseaza chiar toate modulele care le am eu, si incerc incet incet sa fac interfata cat mai customizabila, zilele astea am reusit sa fac paginile cu senzori, graficele, totul e dinamic, oricat de multi senzori o sa raporteze catre Pi, automat o sa apara valorile lor in acele pagini fara interventia mea

Urmeaza sa fac partea cu notificarile, la fel totul dinamic, sa poti crea dinamic niste reguli, iar daca valorile sunt inafara valorilor definite in acele reguli, sa se trimita notificare, deocamdata am doar o notificare, la orice device nou care apare in retea - dar e hardcoded in scriptul care scaneaza reteaua.

O zi faina tuturor

mirceaciu · septembrie 11, 2017, 1:02pm

off-topic alert! chiar folosești un BananaPi?

sibianul · septembrie 11, 2017, 1:12pm

Da, pt ca are SATA. Sincer nu stiu cat va tine un cardSD daca in permanenta fac write in baza de date. Am pus partitia de sistem pe SATA, pe card a ramas doar cea de boot. Deocamdata merge bine, vom vedea cat tine

horia141 · septembrie 11, 2017, 5:01pm

Daca rulezi aplicatia asta intr-un mediu embedded s-ar putea sa ai mai mult noroc cu Sqlite. Mysql/Postgres/SQLServer etc sunt destul de optimizate pentru un mediu server, unde ai multe procesoare, multa memorie, disk mare etc. Faptul ca Sqlite e “single-threaded”[1] nu cred ca e o constrangere, deoarece nu cred ca te astepti la sute de utilizatori concomitent pe un dashboard.

Alta sugestie ar fi sa restructurezi tabelul home_devices_status. B-Trees ce stau la baza tabelelor din bazele de date relationale nu sunt chiar ideale pentru date time-series. Dar poti sa optimizezi putin structura lor astfel incat scrierile sa fie decente si citirile de range-uri rapide.

Din cate vad, (device_id, value_name) ar fi un indicator pentru un time-series. Iar in cadrul asteia ai added_date si value_integer+value_text ca si valoarea asociata. Ai putea sa restructurezi tabelul asa in pseudo-SQL

CREATE TABLE home_devices_status (
    device_id int not null,
    value_name varchar(100) not null,
    added_date int not null, -- sau datetime, sau timestamp etc.
    value_integer int not null,
    value_text varchar(50) not null,
    constraint primary key (device_id, value_name, added_date)
)

Nu mai ai alti indexi decat indexul clustered pentru primary key compus din (device_id, value_name, added_date). In esenta tratezi tabelul ca un key-value store de la (device_id, value_name, added_date) -> (value_integer, value_text).

Scrierea o sa fie more-or-less la fel de complexa ca inainte. Inainte aveai un append frumos la B-Tree datorita folosirii lui id ca primary key, dar trebuia sa updatezi si indexul thishelps care implica mai multe posibile rebalance-uri ale copacului, care sunt relativ costisitoare. Acum o sa ai rebalance ca insert-ul principal, cu ceva mai multe date atasate, dar o sa fie un update al unei singuri structuri pe disk, in loc de doua. Care e un win.

Citirea o sa fie in schimb mult mai OK, pentru ca more-or-less o sa folosesti (device_id, value_name) ca sa selectezi un set de pagini consecutive care contin doar date legate de dispozitivul si metrica ce te intereseaza, de la data care te intereseaza in sus. Citirea de pagini consecutive din orice tip de memorie (de la RAM pana la banda magnetica) e operatia cu throughputul cel mai mare, asa ca eu zic ca o sa vezi o diferenta importanta in viteza de citire. Inainte aveai comportamentul asta oarecum din cauza indexului, dar indexul continea doar pointeri la datele din tabel. Asa ca odata ce identificai astea, aveai de citit pagini random de pe stocare, care e cam cea mai inceata operatiune (again, indiferent de tipul de memorie).

Un ultim sfat, ar trebui sa ai un alt tabel in care tii informatii despre metrici, iar value_name ar trebui sa fie inlocuit cu un value_id care e foreign key catre tabelul ala. Mult din bloat-ul tabelului cred ca vine de la valori de value_name care sunt repetate de multe ori. IDK ce suport de compresie are InnoDB, asa ca poate stie sa se ocupe el cand vede ca are cardinalitate mica. Dar merita incercat dupa ce ai facut alte operatiuni.

[1] Single connection mai bine zis.

sibianul · septembrie 11, 2017, 7:49pm

@horia141 trebuie sa mai citesc odata postul tau nivelul tau este mult peste nivelul meu de cunostinte in MySQL, m-ai cam pierdut, dar vreau sa fiu sigur ca o chestie ai inteles corect din ce vroiam eu, mai exact, eu NU vreau sa citesc consecutiv din tabel, tocmai asta e chestie, ca vreau sa iau tot al x-lea row, iar numarul x difera de la un senzor la altul, pt ca fiecare senzor adauga un numar diferit de inregistrari in 24 de ore. Mai sus unde am pus screenshot-urile cu comparatia de rezultate dintre modul meu de filtrare a rezultatelor, si SQL-ul propus de Luxian, am vrut sa vedeti ca la varianta in care in PHP filtrez rezultatele, fiecare grafic era construit dintr-un numar similar de puncte ~100, dar la varianta in care rezultatele se filtreaza folosind un query mai complex, numarul de rezultate extrase difera destul de mult … dar acum va trebui sa incerc noul cod propus de Luxian, sa vad cat de rapid ruleaza pe BananaPi … in comparatie cu i5-ul pe care a incercat el

Doar ce m-am intors de la ziua unui prieten si parca nu mai imi prea sta capul la programare

Revin cu rezultate… mai tarziu sau maine.

Multumesc!

horia141 · septembrie 12, 2017, 6:47am

Ar trebui sa fie un boost indiferent de ce tip de query folosesti. Sa citesti mai putine pagini si paginile sa fie consecutive o sa te ajute chit ca transmiti toate datele la PHP ptr. procesare, chit ca faci ceva procesare in baza de date direct.