Witam!

Na potrzeby wykresu Highcharts napisałem skrypt PHP, który pobiera dane z bazy MySQL i koduje je do formatu JSON (3 serie danych). Skrypt działa poprawnie, ale czas jego wykonania i pobrania wyniku do przeglądarki jest bardzo długi ok. 10 sekund co stwarza problemy przy wywoływaniu go co 1 minutę wraz z innymi skryptami - zawieszenie przeglądarki po ok. 1 godzinie. W przykładzie poniżej ograniczyłem wynik do 5 minut, ale w rzeczywistości pobierane są dane z miesiąca (43 tys. rekordów). Proszę o radę w jaki sposób zoptymalizować ten skrypt?

Oczekiwany format kodowania:
Wynik działania skrypu

Kod:

[PHP] pobierz, plaintext 
$sth = mysql_query("SELECT czas_unix, wind_speed_average, windgust, wind_direction_average FROM tab_czujniki_2 WHERE czas_datetime >= '$minus_data' ORDER BY czas_datetime ASC") or die('Błąd zapytania');
 
	$rows = array();
	$rows['name'] = 'wind_speed_average';
	$rows1 = array();
	$rows1['name'] = 'windgust';
	$rows2 = array();
	$rows2['name'] = 'wind_direction_average';
 
	while($r = mysql_fetch_array($sth)){
				$rows['data'][] = array(1000*($r['czas_unix']),$r['wind_speed_average']);
				$rows1['data'][] = array(1000*($r['czas_unix']), $r['windgust']);
				$rows2['data'][] = array(1000*($r['czas_unix']), $r['wind_direction_average']);
	}
 
	$result = array();
	array_push($result,$rows);
	array_push($result,$rows1);
	array_push($result,$rows2);
	print json_encode($result, JSON_NUMERIC_CHECK)
[PHP] pobierz, plaintext 

Pozdrawiam

Masz założony indeks na pole czas_datetime?

Tak wszystkie rekordy są indeksowane.

Problemem chyba nie jest zapytanie, ale duża ilość danych, może trzeba ograniczyć ilość rysowanych punktów na wykresie i pobierać np. co dziesiąty punkt?

Ilość pobieranych danych na pewno ma znaczenie, ale wydaje mi się że nie tylko. Ograniczyłem zakres danych na wykresie do 1 dnia (1440 rekordów), a czas wykonania skryptu nadal jest długi ok. 5 sekund.

Sprawdź w jakimś narzędziu typu heidisql ile samo zapytanie się wykonuje, zobacz explain, dołóż indeks ewentualnie. Skoro i tak wszędzie jest mnożenie to dlaczego nie robisz tego raz od razu na bazie? array_push też można pominąć i od razu tworzyć tablicę właściwą.

Dziękuję. Jak pominąć array_push?

Też obstawiałbym, że problemem jest ilość zwracanych danych. Nawet jeśli samo zapytanie korzysta z indeksów to zwrócenie 43k danych musi zająć trochę czasu. Dla pewności pokaż explain zapytania. Możesz rownież stawiać sobie proste benchmarki typu die('już') w newralgicznych miejscach (wykonanie zapytania, zbudowanie tablicy, kodowanie do JSON) i zobaczysz co tak naprawdę spowalnia.

Dziękuję. W PHP nie poruszam się jeszcze biegle. Rozumiem, że skrypt napisany jest prawidłowo. Nie wiem jak pokazać explain zapytania i postawić benchmarki. Czy mógłbyś na przykładzie mojego skryptu to objaśnić?

1. Explain select czas_unix... i wklej tutaj wynik.
2. Co do tych pseudo benchmarków to zatrzymuj skrypt w określonych miejscach, np. po linii z mysql_query i zobacz, który fragment kodu zabiera najwiecej czasu.

Czas wykonania zapytania w odniesieniu do pobrania wszystkich rekordów z bazy danych jest zależny od obciążenia serwera, ale nie dłuższy niż 2 sekundy (EXPLAIN).

czas zapytania 1
czas zapytania 2

Nie wiem co począć z EXPLAIN:
EXPLAIN
Analizator EXPLAIN

Zwolnienie może być na etapie selekcji rekordów do wyświetlenia. Sekcja kodu przygotowująca przedział czasowy do pobrania rekordów może być nieoptymalnie napisana (kod poniżej) Nie mam pomysłu jak to napisać wydajniej:

[PHP] pobierz, plaintext 
$wynik_data = mysql_query("SELECT czas_datetime FROM tab_czujniki_2 ORDER BY czas_datetime DESC LIMIT 1");
 
		if(mysql_num_rows($wynik_data) > 0) {
			$r = mysql_fetch_assoc($wynik_data);
			$datan = $r['czas_datetime'];
			$daten = new DateTime($data);
			$daten->modify('-49 day');
			$minus_data = $daten->format("Y-m-d H:i:00");
		}
 
 
	$sth = mysql_query("SELECT czas_unix, wind_speed_average, windgust, wind_direction_average FROM tab_czujniki_2 WHERE czas_datetime >= '$minus_data' ORDER BY czas_datetime ASC") or die('Błąd zapytania');
[PHP] pobierz, plaintext 

Dziękuję za pochylenie się nad tematem i pomoc.
Pozdrawiam

Jaki to jest problem napisać sobie prostą klasę profilera i użyć jej w celu dokonania pomiarów, w określonych miejscach? Zresztą wyjdą ciekawe wyniki, bo nie tylko samo zapytanie zajmie czas, to jeszcze operacje na tablicach na tych 40 - 50k pobranych danych, json_encode też się wykona w jakimś czasie. A potem można sobie łatwo jeszcze sprawdzić ile czasu zajmie wyświetlenie tego w oknie przeglądarki w postacji JSON a ile przy uzyciu var_dump albo print_r.

Tutaj widać, że zapytanie nie korzysta z żadnego indeksu. W kolumnie key masz NULL.

Bo z czego miałby brać indeks skoro nie ma warunku?

@zbysiusp explain musisz dokleić do zapytania z warunkami. A najlepiej zobacz czy masz indeks na `czas_datetime`

Bardzo dziękuję za wszystkie rady, ale proszę o wyrozumiałość z racji braku biegłości w php (początkujący). Jak powinno wyglądać przyporządkowanie indeksu do 'czas_datetime' lub innego pola i zapytanie EXPLAIN w moim przypadku. W zapytaniu jest warunek WHERE określający zakres pobieranych rekordów.

Tabela w mojej bazie danych jest uzupełniana co 1 minutę o jeden rekord i odczytywana również co jedną minutę. Doczytałem trochę o indeksach. Po pracy spróbuję go przyporządkować do kolumny 'czas_datetime' i przetestować. Tylko nie wiem jak powinno wyglądać zapytanie EXPLAIN z tym indeksem.

Jeśli masz już indeks na kolumnie z czasem, a zapytanie nadal działa powoli to może warto zastanowić się nad partycjonowaniem tabeli po miesiącach.

Założyłem indeks na kolumnie 'czas_datetime'. W jaki sposób ma to usprawnić działanie mojego skryptu? Pomóżcie proszę.
Indeks założony
Zapytanie wykonane z powodzeniem:
EXPLAIN
Analiza EXPLAIN:
Explain Analyzer
Profil szczegółowy:
Profil szczegółowy
Czas wykonania skryptu praktycznie bez zmian:
Czas wykonania skryptu

A zdajesz sobie sprawę z tego co w takim przypadku, kiedy np. pobierasz te 40k rekordów wypluwa profiler?

https://s16.postimg.org/nl90e5alh/profiler_1.png
https://s1.postimg.org/892sgpz7z/profiler_2.png

Test na 800k rekordów z losowymi danymi w bazie, profiler frameworka Kohana 3.3. Oprócz zapytań z bazy dałem benchmark na tych operacjach na array w PHP oraz na konwesji do JSON. W tym przypadku ponad 1s zajmuje pobranie danych z bazy i ponad 1s same operacje w PHP i konwersja do JSON. A i jeszcze zanim te dane zostaną wyplute na ekran to też trochę czasu minie.

Daje to wiele do myślenia. To dlatego przy listowaniu danych stosuje się paginację albo wczytuje tylko pewną ilość danych a przy scrollowaniu strony w dół AJAX-em wczytuje kolejne partie danych. Albo w ogóle pewne dane wczytuje AJAX-em z jakimś tam loaderem który każe czekać a nie coś takiego, że zanim się w ogóle strona wyświetli to trzeba czekać nie wiadomo ile.

I nic tu nie dało założenie tego indeksu.

Witam.
Bardzo dziękuję Wszystkim za rady, sugestie i testy. Wykorzystam je na pewno w rozwiązywaniu problemu.
Pozdrawiam


Witam.

Przerzucenie mnożenia na poziom bazy danych (przy zapisie rekordu) było przełomowe dla czasu wykonywania się skryptu, który skrócił się z ok. 7-8 sek. do 2 sek. Bardzo dziękuję. Klikam pomógł bardzo!
Jakbyś mógł jeszcze napisać jak pominąć array_push i od razu tworzyć tablicę wyjściową czas skróciłby się jeszcze najpewniej jeszcze bardziej

Pozdrawiam

Robisz tutaj 3 klucze stałe więc możesz od razu do tablicy

[PHP] pobierz, plaintext 
$result = array();
$result[]['name'] = 'wind_speed_average';
$result[]['name'] = 'windgust';
$result[]['name'] = 'wind_direction_average';
 
// w pętli
$result[0]['data'][] = [1,2];
$result[1]['data'][] = [3,4];
$result[2]['data'][] = [5,6];
[PHP] pobierz, plaintext 

@viking

Dziękuję bardzo. Działa dobrze.
Pozdrawiam