Optymalizacja bazy danych Opcje

[wachcio]

Witam

Mam bazę danych którą opisałem wcześniej Temat: Wyszukiwanie danych w kilku tabelach
Na razie składa się ona z 11 tabel gdzie każda przechowuje w kolumnach numer porządkowy, datę odczytu i wartość odczytu z różnych czujników np temperatury. Dane gromadzone są od maja tego roku a odczyty co minutę. Daje to na tę chwilę ok 250 000 rekordów na każdą tabelę czyli niecałe 3 000 000 rekordów w bazie. Zrobiłem sobie prostą stronę aby wyświetlać jak na razie:
- ostatni odczyt np.

[PHP] pobierz, plaintext 
$temp_raspberry_pi = mysql_query ("SELECT `value`,`time` FROM `Raspberry_Pi` ORDER BY  `Raspberry_Pi`.`time` DESC LIMIT 2");
$temp_raspberry_pi = mysql_fetch_assoc($temp_raspberry_pi);
[PHP] pobierz, plaintext 

- najwyższy odczyt z dziś

[PHP] pobierz, plaintext 
$hi_temp_raspberry_pi = mysql_query ("SELECT `value`,`time` FROM  `Raspberry_Pi` WHERE DATE(  `time` ) = CURDATE( ) ORDER BY  `Raspberry_Pi`.`value` DESC  LIMIT 2");
$hi_temp_raspberry_pi = mysql_fetch_assoc($hi_temp_raspberry_pi);
[PHP] pobierz, plaintext 

- najniższy odczyt dziś

[PHP] pobierz, plaintext 
$low_temp_raspberry_pi = mysql_query ("SELECT `value`,`time` FROM  `Raspberry_Pi` WHERE DATE(  `time` ) = CURDATE( ) ORDER BY  `Raspberry_Pi`.`value` ASC LIMIT 2 ");
$low_temp_raspberry_pi = mysql_fetch_assoc($low_temp_raspberry_pi);
[PHP] pobierz, plaintext 

Udało mi się uporać z problemami przedstawionymi w poprzednim poście ale wyświetlanie tych wyników dla wszystkich 11 tabel trwało ok 14 sek Poczytałem jak mogę zoptymalizować bazę. Dodałem indeksy na kolumny z datą i wartością odczytu. W zapytaniu SELECT uściśliłem potrzebne mi kolumny oraz dodałem klauzulę LIMIT. Strona wczytuje się teraz ok 6,5 sek czyli sporo mniej ale i tak dość długo. Zauważyłem, że najwięcej czasu zajmują zapytania wybierające rekordy z dziś. Wiadomo, że z czasem problem będzie narastał bo i danych będzie przybywało. Poza tym planuję dodawać nowe czujniki...
Co jeszcze mogę zrobić aby poprawić wydajność?

[trueblue]

A jak często wybierasz rekordy z "dziś" w ciągu jednego dnia?

[wachcio]

Przy każdorazowym uruchomieniu strony. W celach testowych mam ustawione odświeżanie strony co minutę. Później ciężko powiedzieć jak często będą wejścia

Ten post edytował wachcio 14.12.2016, 21:14:31

[trueblue]

Usuń CURDATE(), a podstaw tam datę z php (date('Y-m-d')).

[wachcio]

Niestety czas wykonania skryptu bez zmian

[trueblue]

1. Możesz zapisywać wszystkie w jednej tabeli? Pewnie nie.
2. Partycjonowanie tabeli w zakresie dat.
3. Przy pierwszej próbie sprawdzenie odrębnej tabeli gdzie są dane tylko z dnia dzisiejszego, jeśli są, to są odczytywane. Jeśli brak, to są pobierane z dużej tabeli i zapisywane w mniejszej. Przy okazji usuwane są dane z dnia wczorajszego.

[wachcio]

Ad. 1
Niestety nie gdyż jest to kopia danych oprogramowania nettemp na Raspberry Pi i to ono narzuca sposób zapisu.

Ad2 oraz Ad3
Niestety nie wiem jak to zrobić Dopiero raczkuję w MySQL...

[trueblue]

ad.2 Partycjonowanie jest opisane w dokumentacji, ale przy takiej ilości rekordów na tabelę, to chyba zbyt duże narzędzie.
ad.3 Nie robisz tego tylko w MySQL. Robisz to w PHP+MySQL. a) sprawdzasz czy istnieją pobrane rekordy, tak jak obecnie, ale w małej tabeli. jeśli brak, to pobierasz rekordy z dużej tabeli (tak jak teraz) i zapisujesz w małej (insert). c) jeśli są to wyświetlasz d) usuwasz rekordy wczorajsze (delete+where na datę).
Zamiast małej tabeli, możesz zapisywać wynik w pliku txt. To coś na miarę cache.

Ten post edytował trueblue 14.12.2016, 22:26:01

[wachcio]

Czyli dużo kombinacji a efektu może nie być za wiele

Ten post edytował wachcio 14.12.2016, 23:03:02

[trueblue]

Dlaczego nie za wiele?
Odczyt z małej tabeli czy z pliku txt będzie wielokrotnie szybszy. Odczyt z dużej będzie tylko raz na dzień, kiedy brak danych w małej tabeli lub pliku.

[wachcio]

Dane są dopisywane do tabel co minuta więc jeśli dobrze rozumiem to przy każdym odświerzeniu strony musiałbym przepisać część tabel gdzie są odczyty dziś do innych tabel (tabeli) a to chyba też zajmie czas?

[viking]

W jaki sposób założyłeś indeksy? Mają być dokładnie takie same jak w zapytaniu. Jeśli wykonujesz zapytanie bezpośrednio na bazie , z pominięciem PHP b jaki jest czas?

[wachcio]

Indeksy zakładałem klikając w phpMyAdmin. Pojedyncze zapytanie o ostatni odczyt to kilka kilkanaście tysięcznych sek natomiast przy uzyskiwaniu maksymalnych odczytów z dnia to od 0,25 do 0,7 sek

[viking]

Nie chodzi mi w jakim narzędziu tylko czy faktycznie są używane. Uruchom zapytanie z http://dev.mysql.com/doc/refman/5.7/en/explain.html i zobacz co się dzieje. Czyli dobrze rozumiem że 0,7s trwa z poziomu mysql a w php robi się 6.5s?

[trueblue]

Dane są dopisywane do tabel co minuta więc jeśli dobrze rozumiem to przy każdym odświerzeniu strony musiałbym przepisać część tabel gdzie są odczyty dziś do innych tabel (tabeli) a to chyba też zajmie czas?

Skoro z jednego dnia masz tak dużą liczbę rekordów, to nie ma sensu. Byłem przekonany, że 2 rekordy na 1 dzień.

Ale wydaje mi się, że warunek WHERE jest niepotrzebny. Przecież nie ma w tabeli nowszych danych niż z dnia dzisiejszego. Wystarczy więc ORDER BY i LIMIT.

Ten post edytował trueblue 15.12.2016, 09:01:29

[pmir13]

Z tego co autor napisał wynika, że tabele są dla różnych czujników, a każda ma dane od początku, więc WHERE jest niezbędny by mieć dane z konkretnego dnia.
Co do optymalizacji pierwszym problemem jest tutaj fakt, że w tym warunku WHERE wykonujemy funkcję na kolumnie, co nie kwalifikuje indeksu na tej kolumnie do wykorzystania. Można próbować przepisać to w ogólnej postaci jako na przykład

[SQL] pobierz, plaintext 
 ... WHERE `time` BETWEEN '2016-08-01 00:00:00' AND '2016-08-01 23:59:59'
[SQL] pobierz, plaintext 

albo prościej dla dzisiejszego dnia o ile nie ma możliwości by były jakieś późniejsze wpisy:

[SQL] pobierz, plaintext 
 ... WHERE `time` > CURDATE()
[SQL] pobierz, plaintext 

jednak w najlepszym przypadku dostaniemy w explain type=range, a do tego nie da się potem podczepić następnej części indeksu do sortowania wartości. W zależności od ilości danych może to być szybsze niż pełen skan tabeli (type=all) czy bardziej prawdopodobny skan indeksu (type=index), jednak wciąż w obrębie dnia będziemy mieć sort by znaleźć maksymalną czy minimalną wartość.
Dopiero denormalizacja i wprowadzenie dodatkowej kolumny zawierającej wyłącznie datę bez czasu oraz wprowadzenie podwójnego indeksu na (data,wartość) umożliwi nam dla zapytania typu

[SQL] pobierz, plaintext 
... WHERE `date` = CURDATE() ORDER BY `value` DESC LIMIT 1
[SQL] pobierz, plaintext 

pełne wykorzystanie tego indeksu (w explain będzie type=ref oraz ref=const) i możemy mieć w tabeli pierdyliard rekordów a wynik dostaniemy bardzo szybko - mysql znajdzie konkretne miejsce z danymi po indeksie btree w logarytmicznym czasie.

[trueblue]

Z tego co autor napisał wynika, że tabele są dla różnych czujników, a każda ma dane od początku, więc WHERE jest niezbędny by mieć dane z konkretnego dnia.

Zgadza się, że są dla różnych czujników, ale nie wiem na jakiej podstawie wnioskujesz, że każdy czujnik ma dane z innych dni.
Z tego co napisał autor, każdy czujnik ma najświeższe dane (zbiera co minutę).
Tak więc WHERE jest niepotrzebny.
Ale może się zdarzyć sytuacja, że dany czujnik nie działa i jeśli chcemy wyświetlić dane tylko z dzisiejszego dnia. Wtedy rozwiązaniem może być sprawdzenie danych już na poziomie skryptu. Podobnie gdyby się jednak okazało, że dane są z różnych dni.

[pmir13]

Zgadza się, że są dla różnych czujników, ale nie wiem na jakiej podstawie wnioskujesz, że każdy czujnik ma dane z innych dni.
Z tego co napisał autor, każdy czujnik ma najświeższe dane (zbiera co minutę).

Na podstawie pierwszego postu i szacowanych ilości rekordów. Mamy 11 tabel, osobno dla każdego czujnika, w każdej co minutę insert od maja (jak inaczej wytłumaczyć 250k rekordów na tabelę?), szukamy dla każdego czujnika najwyższej i najniższej wartości dla danego dnia. O ile znalezienie tych wartości dla jednej tabeli daje autorowi w tej chwili akceptowalny czas wykonania, to gdy trzeba to powtórzyć dla wszystkich tabel spowolnienie jest już wyraźne. Myślę, że problem przedstawiony jest wystarczająco precyzyjnie.

To że czujnik ma najświeższe dane nic nie zmienia, jeśli chcemy najwyższą temperaturę dzisiaj, nie interesuje nas wpis z letnich upałów.

Dodanie dodatkowej kolumny na datę jest tutaj najbardziej oczywistym krokiem, jednorazowy update na dotychczasowe dane, trigger after insert na wyciągnięcie i zapisanie samej daty (być może również after update o ile robimy update oprócz insertów) plus dwukolumnowy indeks na (date,value) i mamy problem z głowy. Rozwiązanie bez grzebania w aplikacji zbierającej odczyty, opierające się wyłącznie na samej bazie danych (skoro autor może dodawać indeksy to ma do niej dostęp) i niezależne od wielkości bazy, bo dodatkowe czynności przy insertach co minutę nie wpłyną w zauważalny sposób na pracę bazy, a potrzebne do wyświetlenia na stronie dane dostajemy natychmiast z indeksu.

[trueblue]

Przeoczyłem fakt, że autor potrzebuje również najwyższą i najniższą wartość z danego dnia. I tu masz rację.
Natomiast przy najświeższym wyniku warunek nie jest potrzebny. Kiedy posortuje rekordy i wyciągnie jeden lub dwa, to będą rekordy z dnia dzisiejszego, nie lata. I tak też uczynił autor w zapytaniu z pierwszego postu.

Co do drugiej kwestii, nie wypowiadam się, bo nie zahaczyłem o nią.

[wachcio]

Kolega pmir13 bardzo dobrze zrozumiał co miałem na myśli i dobrze to opisał. Na razie 11 tabel z 3 kolumnami każda. Kiedy w skrypcie odczytuję dane robi się już 33 zapytania do bazy przez co czas jest dość długi. Mam dostęp do bazy danych gdyż jest to tylko kopia do której system odbioru danych z czujników (nettemp) zapisuje co minuta zduplikowane wartości ze swojej własnej bazy. W przyszłości będzie dodatkowe urządzenie (urządzenia) które będą zbierały dane z kolejnych czujników i zapisywały je do tej właśnie bazy. Dlatego też pomysł był taki aby mieć wszystkie odczyty w jednym miejscu z możliwością dość łatwej rozbudowej o dalsze funkcje czyli np. raport ze średniej temperatury z danego okresy z danego czujnika itd.
Widzę, że koledzy proponują różne rozwiązania i bardzo dziękuję Wam za to ale muszę mocno podszkolić się w MySQL aby niektóre sprawdzić w praktyce...
Do tej pory sprawdziłem najłatwiejszy do zrobienia sposób czyli zamiast CURDATE() na sztywno wpisać datę i godzinę z BETWEEN niestety bez jakiejś zauważalnej różnicy w czasie wykonania.
Zainteresował mnie szczególnie pomysł kolegi pmir13 ale nie bardzo wiem jak go zrealizować niestety... Muszę przeczytać post kilka razy i może wtedy uda mi się lub poprosić o jakieś dalsze wskazówki


EDIT:

Udało mi się zmodyfikować pliki systemu Nettemp i teraz kopię danych w mojej bazie danych zapisuje w postaci:

Kolumna Typ Null Ustawienia domyślne
id int(6) Nie
time timestamp Nie CURRENT_TIMESTAMP
value float Tak NULL
data date Nie IndeksyDokumentacja


Nazwa klucza Typ Jednoznaczny Spakowany Kolumna Moc Metoda porównywania napisów Null Komentarz
PRIMARY BTREE Tak Nie id 261521 A Nie
time BTREE Nie Nie time 261521 A Nie
value BTREE Nie Nie value 318 A Tak


Czyli dodałem kolejną kolumnę o nazwie "data" typu "date" w której on zapisuje na bieżąco aktualną datę. Reasumując teraz w każdej tabeli mam dwa razy datę zapisu. Raz jako "CURRENT_TIMESTAMP" i raz jako samą datę.
Teraz gdzie powinienem założyć a gdzie (i jak) ewentualnie usunąć indeksy w tabelach? Jak dodać samą datę w brakujących rekordach? Czy wystarczy w zapytaniu o ekstremalne wartości z danego dnia zmienić wyszukiwanie z kolumny "time" na "data"?

Ten post edytował wachcio 16.12.2016, 21:53:37