optymalizacja przechowywania danych w mysql

Ciężko jest skategoryzować ten wpis – czy to bardziej mysql czy też php. Celem jest przedstawienie formy przechowywania danych w określonym przypadku. Załóżmy sytuację, że musimy w bazie danych przechowywać informacje które na poziomie samej bazy nie będą przetwarzane, a jedynie dopiero po odczytaniu przez php. Chodzi o to, że zamiast budować relację możemy przechowywać zserializowaną tablicę jako kolejną kolumnę w tabeli. Najlepszym przykładem są wszelkie systemy do zbierania informacji, które później trzeba przeanalizować, np.: systemy do zbierania statystyk.

Opis problemu

Posiadamy system do zbierania i analizowania danych – przykładem jest system statystyk, ale nie zawężajmy się tylko do tego. Ilość danych jest bardzo duża i musimy wymyśleć optymalne rozwiązanie zapisu i analizowania danych. Najlepiej by było aby zapis i analizowanie danych odbywało się szybko i sprawnie. Dlatego proponuję użyć dwóch baz danych. B1 – baza zoptymalizowana do zapisu, B2 – baza zoptymalizowania do operowania na danych. Pomiędzy B1 i B2 musimy uruchamiać proces przejściowy. Takim sposobem uzyskujemy szybki, mało kosztowny zapis, oraz sprawną analizę danych.

Rozwiązanie

Dane wynikowe w takim systemie nie będą odświeżane/przeliczane za każdym razem, dlatego będą istniały dwie bazy, agregująca (B1) – czyli ta która będzie przechowywała spakowane dane, oraz ta która będzie przechowywała przetworzone informacje gotowe do prezentacji/przeliczeń itp. Proces w takim przypadku wyglądał by mniej więcej tak:

1. Zapis spakowanych danych do bazy agregującej – B1
2. Przetworzenie danych z bazy B1 do formatu pozwalającego operowanie na danych
3. Zapisane danych z pkt 2 do bazy B2
4. Operowanie na danych z bazy B2.

Dobrze przemyślane rozwiązanie pozwoli na

1. Szybsze zapisywanie - dzięki zastosowaniu B1
2. Zmniejszenie transferu – dzięki kompresji użytej podczas zapisu w bazie B1
3. Szybsze operowanie na danych – dzięki transformacji do formatu B2
4. Zmniejszenie liczby niepotrzebnych relacji w bazie

Wadą jest złożenie systemu, oraz konieczność transformacji z B1 do B2.

Przykład

Załóżmy, że mamy system statystyk i zliczamy odwołania na podstawie wywoływanego pliku z parametrami przykładowe wywołanie

http://example.com/zlicz.php?ip=555&resolution=1024/768(……)

Czyli posiadamy plik zlicz.php który otrzymuje ustaloną liczbę argumentów.Standardowo do tego celu powstałaby baza

1
2
3
4
5
6
Ip
Date
Cookie
url
Ref
Itp. Itd.

Taka struktura danych jest bardzo dobra jeżeli chodzi o obrabianie danych lecz zapis jest długi i kosztowny (biorąc pod uwagę dużą skalę).

Ja proponuję wykonać table

1
2
3
id
date
data

Pole „data” będzie zawierało skompresowane dane podane w zmiennej $_GET, dzięki temu nie będziemy wrażliwi na ilość parametrów a zapis będzie szybszy.

Metoda kompresji

W php do tego celu znalazłem dwie funkcje bzcompress(),gzcompress(). Oczywiście od razu sprawdziłem która jest szybsza i jak kompresują dane.

Jako dane początkowe użyłem tablicy $_SERVER – po zserializowaniu zajmowała 1721 znaków. Funkcje kompresujące posiadają parametry – w teście użyłem je z domyślnymi wartościami. Wartości czasowe określają czas wykonania 1000 operacji. Wykonałem kilkanaście testów i wszystkie wyniki oscylowały wokół tych danych.

Dane skompresowane Czas kompresji Czas dekompresji
bzcompress 944 0,75 0,17
gzcompress 885 0,20 0,03

Kod php:

1
2
3
4
5
$str = serialize($_SERVER);
$bstr = bzcompress($str);
$str = bzdecompress($bstr);
$gstr = gzcompress($str);
$str = gzuncompress($gstr);

Jak widać znacznie lepszą metodą kompresji jest gzcompress(), świadczą o tym liczby w tabelce.

Dane do bazy

Zapis do bazy wyglądałby mniej więcej tak

1
2
$data = mysql_real_escape_string(gzcompress(serialize($_GET)));
mysql_query(‘insert into TABELA (id, date, data) VALUES (‘’, NOW(), $data)’);

Podsumowanie

Tak wiem ameryki nie odkryłem :). Celem jest przedstawienie sytuacji gdzie wszędzie powinniśmy szukać optymalizacji aplikacji/procesów powtarzalnych i zajmujących dużo pamięci transferu.

 

Tagi: , , ,

Komentarze: 5 do “optymalizacja przechowywania danych w mysql”

  1. 1 Mateusz Żeromski     2009-07-04 18:34:46

    Powyższe rozwiązanie w dużej skali: http://blog.gemius.pl/?more=95

  2. 2 eRIZ     2009-07-04 22:50:13

    A czemu nie napisałeś o przechowywaniu wielu danych bool w postaci zapisu/odczytu poszczególnych bitów każdego bajta? Umożliwiłoby to nawet operowanie w ramach zapytania, w jednym bajcie można zapisać aż 8 różnych prawd/fałszów. [;

    Tak w ogóle, to wynalazłeś koło na nowo – odpowiednie rozwiązanie jest już od dawna zaimplementowane w MySQL – http://dev.mysql.com/tech-resources/articles/storage-engine.html

  3. 3 Mateusz Żeromski     2009-07-05 09:42:36

    A to już pisałem o tym
    http://blog.zeromski.com.pl/2008/11/mysql-myisam-vs-innodb-vs-memory-vs-archive/

    Moim celem było przedstawienie sytuacji kiedy komplikujemy system w celach optymalizacji, czyli dodatkowe bazy danych, procesy trasformacji ich, w celu poprawy wydajności.

    Czy mógłbyś rozwinąć myśl dot. tych bajtów, bo nie za bardzo rozumiem.

  4. 4 eRIZ     2009-07-05 15:50:05

    Czy mógłbyś rozwinąć myśl dot. tych bajtów, bo nie za bardzo rozumiem.

    Mam na myśli odwoływanie się do poszczególnych bitów każdego bajta, tzn. sprawdzanie każdego z nich. [;

    Z tego, co pamiętam, to się to chyba nazywało pole bitowe.

  5. 5 Tomasz     2010-05-02 20:43:46

    Witam :)
    czy ma Pan “bardziej-prościej” opisanie tej metody ?
    nie to abym po google nie poszukał ale… ale :)

Napisz komentarz