September 5th, 2006 by depesz | | 16 comments »
Did it help? If yes - maybe you can help me?

praktycznie od zawsze na listach dyskusyjnych bazo danowych, forach, grupach i kanałach ircowych pojawia się jeden temat: “czy i jak przechowywać w bazie obrazki?". oczywiście nie chodzi tylko o obrazki. chodzi o wszelkiego typu pliki binarne – obrazki, filmy, muzykę, pliki z danymi (excel, word itd.).

osobiście jestem przeciwnikiem trzymania tego typu rzeczy w bazie. ale zrobiłem ostatnio mały research aby wypisać często pojawiające się argumeny za i przeciwko takiemu rozwiązaniu.

te argumenty postaram się skomentować, choćby po to by samemu mieć pewność, że mój wybór jest lepszy.

podstawowymi argumentami “za" trzymaniem plików w bazie danych są:

  1. trzymanie ich w bazie upraszcza zarządzanie. praca z np. 100 obrazkami nie jest problemem tak czy inaczej, ale jak poradzić sobie jak jest ich kilka milionów?
  2. wystarcza jeden prosty backup. w tym backupie jest wszystko co konieczne do wznowienia pracy.
  3. bezpieczeństwo – posiadanie danych w bazie umożliwia proste filtrowanie dostępu.
  4. pliki poza bazą danych są narażone na rozsynchronizowanie z bazą – skasujemy rekord z bazy, a pliku nie, albo odwrotnie i katastrofa gotowa.
  5. zapisanie pliku do bazy jest prostsze niż wydzielanie oddzielnego api do zapisywania na filesystemie

podstawowymi (dla mnie) argumentami przeciwko trzymaniu ich w bazie są:

  1. obrazki zajmują zazwyczaj sporo więcej miejsca niż pozostałe dane. to powoduje pewne utrudnienia przy korzystaniu z baz danych które wymagają prealokacji przestrzeni dyskowej.
  2. przesyłanie obrazków wykrzystuje bardzo nieetektywnie połączenia do bazy – połączenie trwa długo i praktycznie nic nie robi
  3. przechowywanie obrazków w bazie praktycznie nic nam nie daje od strony bazodanowej. inne typy danych są użyteczne przy budowaniu warunków wyszukania, łączenia czy sortowania – i w ten sposób “zarabiają" na to by umieścić je w bazie. obrazków (ich plików) nie da się do tego wykorzystać.
  4. systemy plików w nowych systemach operacyjnych są mocno zoptymalizowane w celu szybkiego dostarczania i cache'owania plików. dodatkowo – nowoczesne serwery http potrafią korzystać z mechanizmów systemu operacyjnego do dalszego przyspieszenia wysłania obrazka.

dodatkowo czasem pojawia się jeszcze jeden argument, który jest używany przez zwolenników trzymania plików w bazie, mimo, że jest argumentem “negatywnym" – wykazującym jedynie brak sensu jednego z argumentów przeciwników.
chodzi o to, że zwolennicy twierdzą, że co prawda trzymanie plików w bazie zwalnia bazę, ale obecnie stosowane serwery są i tak bardzo szybkie, pamięć jest tania, dyski też, więc to nie problem.

argument ten traktuję bardziej jako “wyznanie wiary" niż stwierdzenie faktu. pamięć może i jest tania gdy mówimy o pamięcy do pecetów czy do serwerów tak do 8 giga ramu. każda osoba która korzysta z większych maszyn wie, że ceny pamięci rosną lawinowo powyżej tej granicy – w szczególności 16 giga ramu kosztuje dużo więcej niż 2x koszt 8 giga ramu. a co do dysków – fakt robią się coraz tańsze, ale też wymagamy od nich więcej. macierz którą ostatnio się bawiłem kosztowała około $55000. to nie są “grosze".wróćmy więc do bardziej sensownych argumentów.

argumenty “za":
1. to jest istotny problem. bazy danych świetnie sobie radzą z tabelami mającymi kilka milionów rekordów. natomiast katalog z kilkoma milionami plików to porażka.ale wydaje mi się, że problem jest rozdmuchany. wystarczy wymyśleć sensowną konwencję nazewnictwa katalogów/plików i problem znika. przykładowo – w systemie nad którym ostatnio sporo siedzę, nazwy plików są stworzone przez taką transformatę:

  • metadane obrazka są wpisywane do bazy
  • obrazek dostaje swoje id – integera
  • integera konwertujemy na wartość hexadecymalną
  • dopełniamy opcjonalnym zerem do parzystej ilości cyfr
  • wstawiamy “slashe" między pary cyfr
  • i doklejamy rozszerzenie.

brzmi skomplikowanie. ale jest proste. przykład:

  • obrazek o id: 1533672
  • po konwersji na hex uzyskujemy: 1766e8
  • ilośc cyfr jest parzysta więc nie muszę dopełniać (dostawiać z przodu 0)
  • wstawiam slashe i uzyskuję: 17/66/e8
  • dodaję rozszerzenie i mam: 17/66/e8.jpg
  • i koniec.

dzięki temu na każdym poziomie katalogów z obrazkami mam maksymalnie 512 pozycji (256 plików i 256 katalogów). wyszukiwanie i serwowanie tego jest szybkie i proste.

2. jeden prosty backup może i tak. ale ten argument się w/g mnie mocno kłóci z milionami plików.

dużo prościej jest zbackupować bazę o wielkości x giga i do tego katalog z milionem plików niż bazę danych o wielkości x giga + ten milion plików.

czyli – prosty backup tak, ale dla relatywnie małych ilości plików. potem się okazuje, że zwykły dump staje się nierealny i trzeba używać rzeczy typu hot-backup.

3. argument celny o ile pliki są wystawione “po prostu" w documumentroot'cie apache'a.

ograniczanie dostępu na poziomie bazy jest oczywiście miłe i proste, ale zrobienie tego samego po stronie apache'a też nie jest trudne – są .htaccess'y, mod_perl, mod_rewrite, zarządzanie uprawnieniami “w locie". wymaga to trochę innego zestawu umiejętności niż zaprogramowania aplikacji, ale w końcu nie jest to jakiaś wiedza tajemna. są manuale, są przykłady.

dodatkowo – dla większości aplikacji kontrola dostępu do plików binarnych nie jest niezbędna. zazwyczaj (nie zawsze!) są one tylko dodatkiem do danych tekstowych.

sam znam kilka miejsc gdzie obrazki są w ogóle nie chronione – chroni się teksty. jak ktoś się domyśli jaki jest url do obrazka – fajnie. da się przeżyć. a jak się nie da – są metody naprawdę prostej ochrony takich danych.

4. to faktycznie jest problem. w sensie – nie da się zapewnić braku możliwości skasowania pliku z filesystemu. a i robienie triggera kasującego plik z filesystemu przy skasowaniu rekordu byłoby problematyczne (choćby kwestia konieczności użycia poleceń systemowych, co większość baz danych utrudnia, czy rollbacków).

natomiast realnie (mówiąc realnie mam na myśli serwisy którymi się zajmuję) – problem tak naprawdę nie występuje. pliki są wgrywane na filesystem. nikt ich ręcznie stamtąd nie kasuje. dostęp na roota jest limitowany do adminów, innych kont praktycznie na webserwerach nie ma.

jak plik zostanie skasowany z bazy (w sensie, jego metadane) – to przecież istnienie go na dysku nam nie przeszkadza – tzn. zajmuje trochę miejsca, ale to wszystko.

aby to zużycie miejsca przez skasowane pliki zminimalizować wystarcza naprawdę prosty automat który robi listę plików w filesystemie, listę plików z bazy, porównuje i kasuje te których nie powinno być. mały cron który np. raz dziennie robi porządki. nam ten cron zajął jakieś 20 minut pracy. to chyba nie jest za dużo?

5. ostatni argument nt. prostoty api muszę przyznać, że mnie zaskoczył.

spotkałem się z nim relatywnie najrzadziej, ale i tak pojawił się kilka razy. musiałem się nad nim chwilę zastanowić. efekt?

pomyślmy. api wpisu obrazka do bazy dla fajnych baz wygląda:

INSERT INTO obrazki (file) VALUES (‘?');

i wstawienie tak danych binarnych. to jest faktycznie proste. do czasu.

po przejści na inną (większą) bazę pojawiają się bloby których obsługa jest już zdecydowania inna. trzeba otworzyć, pisać, zamknąć – zupełnie jak przy pliku.

co do obsługi pliku, wystarcza trójca: open(); write(); close();. niezależnie od języka programowania wygląda to zasadniczo podobnie. dodatkowo część języków ma wbudowane mechanizmy do robienia tego jeszcze prościej – np. w perlu, można użyć modułu io::all, i potem takiej konstrukcji:

$dane > io(‘/tmp/some.file');

co zajmie się otwarciem, zapisaniem i zamknięciem. bezpiecznie. z obsługą sytuacji krytycznych.

i zadziała tak samo dla site'u mającego 400 wizyt tygodniowo i takiego co ma 400 wizyt na sekundę.

tak więc prostota api wydaje mi się być mocno dyskusyjna. ale ponieważ się pojawiła, trzeba było skomentować 🙂

zostały argumenty przeciwników zapisywania obrazków w bazie – z nimi pójdzie mi łatwiej, bo są to też moje argumenty:

1. na temat tego argumentu mam relatywnie najmniej do powiedzenia. do tej pory używałem dwóch baz wymuszającym prealokację przestrzeni (adabas d i oracle). w obu przypadkach uznałem pomysł za chybiony i maksymalnie upierdliwy. nie jestem specem od oracle'a czy adabasa. może się da to jakoś rozwiązać ładniej. ale jedna rzecz mnie “boli":

mając plik na dysku który ma 1 megabajt. zajmuje on na dysku fizycznie ten 1 megabajt plus wielkosć inode'a. łącznie powiedzmy 1 megabajt i 1 kilobajt.

plik w bazie podlega innym regułom. np. w postgresie – pole dłuższe niż 2 kilobajty jest dzielone i przechowywane w tabelach typu “toast". realnie oznacza to, że na każde 2 kilobajty jest zapisywane dodatkowo około 26 bajtów (plus 4 jeśli używamy oid'ów).

oznacza to, że nasz 1 megabajtowy obrazek w bazie zajmuje około 1megabajt i 13 kilobajtów.
12 dodatkowych kilobajtów to niedużo. a co jak obrazków jest milion?

2. z mojej dotychczasowej praktyki z bazami wynika, że jednego zasobu nigdy nie ma za dużo – są to połączenia do bazy. wystarczy jedno źle napisane zapytanie i potrafi zapchać serwer tak, że nawet najprostsze i najszybsze selecty się nie wykonają. z tego punktu widzenia obrazki to koszmar.

mały, prosty select, indeksowany – powinien być błyskawiczny. i faktycznie jest. dopóki czytamy dane lokalnie.

jeśli jednak request jest zdalny, to połączenie będzie wykorzystywane przez cały czas transmisji do klienta. czyli w skrajnym przypadku kilka minut. oops? czy widzicie ten problem?

nie ma znaczenia co zrobicie – wystarczy, że więcej ludzi zechce obejrzeć wasze obrazki korzystając z kiepskich łącz i system siada. a chyba nie o to chodziło?

3. to wydaje się być oczywiste. wrzucenie do bazy danych np. wielkości obrazka pozwala na proste filtrowanie. daty daje możliwość wyszukania najnowszych obrazków. a co nam daje wrzucenie samego obrazka? nic.

baza nie będzie filtrowała używając danych obrazka. nie założymy na tym indeksu. nie zrobimy order by czy nic takiego. po prostu dane do jednokrotnego wstawienia i selectowania. zero zysku przy dostępie.
4. ten argument wydaje mi się najistotniejszy.rozważmy co musi zrobić aplikacja aby zaserwować obrazek.

załóżmy, że nasza aplikacja jest w php, pracuje pod apache'em. baza danych – postgres.

  • php otwiera połączenie do postgresa (albo korzysta z gotowego)
  • wysyła zapytanie
  • postgres szybko znajduje plik w tabeli
  • odczytuje rekord
  • zwraca do php
  • znajduje nastepny blok
  • odczytuje
  • przesyła do php
  • itd. aż do końca pliku.
  • wtedy postgres informuje: to już koniec
  • a php przesyła to do klienta.

skomplikowane. w dodatku – postgres nie ma i nie może mieć za bardzo zoptymalizowanego dostępu do tego pliku – w końcu – to dane jak inne.
co się dzieje gdy request o plik trafia do apache'a który serwuje pliki statyczne z filesystemu?

  • mając otwarte połączenie do klienta
  • apache otwiera plik który ma serwować
  • a następnie wykonuje jednego syscalla: sendfile

ten syscall realizuje przesłanie zawartości jednego uchwytu pliku do drugiego. w tym przypadku całej zawartości pliku do klienta. całośc odbywa się w kernelu i jest niesamowicie szybka.
zgadza się, że nowe maszyny są szybsze niż kiedyś. ale wymagamy od nich więcej. i jeśli mam obsłużyć 2 miliony page-views dziennie, to nie będę poświęcał czasu maszyn na “durne" przerzucanie danych między procesami (baza, php, klient) skoro można po prostu przepompować dane na poziomie kernela. bez żadnych pętli, warunków itd.

do tego wszystkiego dochodzi jeszcze jeden argument – dla mnie istotny. w życiu każdego systemu pojawia się sytuacja, że kończy się wydajność sprzętu. trzymając wszystko w bazie musimy modyfikować serwer bazodanowy. a mając dane “rozrzucone" mamy większe marginesy bezpieczeństwa i więcej możlwości modyfikacji.

cały czas sensowne klastrowanie baz danych jest marzeniem. są produkty które to robią, ale kosztują horrendalne pieniądze.

natomiast klastrowanie apache'y czy innych fileserwerów jest proste, łatwe i praktycznie bezkosztowe (poza sprzętem oczywiście).

kończąc ten wpis – na pewno są sytuacje gdy względy różne (prawne czy biznesowe) wymuszają stosowanie przechowywania obrazków w bazie. wierzę, że są też takie sytuacje i takie powody o których ja nie wiem.
wiem jednak, że dla praktycznie wszystkich trzymanie obrazków w bazie jest dobrowolną rezygnacją z wydajności i skalowalności w imię mitycznych i łatwo obalalnych idei.

  1. 16 comments

  2. Sep 6, 2006

    Nie napisałeś o najważniejszym argumencie za trzymaniem obrazków w bazie danych. Otóż są nimi transakcje i dumpy.

    Wyobraź sobie sytuację że modyfikujesz obrazek w bazie danych. Obrazek o id =100 wcześniej był żabą a teraz jest krową. (Zakładam że tak nie robisz, ale ktoś może wpaść na taki pomysł). W tym samym czasie wykonywany jest dump bazy. Gdy dump bazy został rozpoczęty, obrazek o id=100 był żabą. Gdy dump bazy został zakończony i rozpoczął się nietransakcyjny dump systemu plików obrazek o id=100 jest już krową. W konsekwencji nasz dump nie jest spójny. W dumpie mamy żabę, która wygląda jak krowa.

    Małym argumentem za trzymaniem obrazków w filesystemie, jest to, że tani hosting ma ograniczenia na rozmiar bazy danych. Ale my nie szukamy klientów, którzy szukają taniego hostingu.

  3. # D-
    Sep 6, 2006

    Wszyscy szukają. Są tylko różne definicje “tani” 😉

  4. Sep 6, 2006

    antek: hmm .. oczywiscie jest to pewien problem. natomiast ja uwazam, ze nie powinno byc update’ow danych obrazkow. po prostu – dodajesz nowy i kasujesz stary. nowy ma inne id. i po problemie.
    nie mowie, ze jest to idealne rozwiazanie. ale w/g mnie jest to wystarczajaco prosty “work-around” by nie bylo z nim problemow.

  5. # kura
    Sep 6, 2006

    W przypadku obrazków – ok. Ale jeśli robisz system, w którym są bardzo ważne, poufne, dane binarne (np. skanowany dokument jest wprowadzany do systemu w postaci zOCRowanej i oryginalnej – jako dowód rzeczowy z podpisem itp), to jednak względy bezpieczeństwa moim zdaniem w zupełności uzasadniają trzymanie tego w bazie. Nie powodują także specjalnego obciążenia, bo służą tylko do weryfikacji od czasu do czasu (bo pracuje się na zOCRowanych danych).

    Także w przypadku serwisów internetowych z obrazkami – baza faktycznie niewiele wnosi, ale w przypadku systemów, w których dane binarne są ważne – wnosi dużo.

    PS A prealokacja przestrzeni w Oracle nie jest konieczna przecież – ustawiasz AUTOEXTEND i już.

  6. Sep 6, 2006

    powiedzmy sobie szczerze: przy dużych serwisach (np. allegro) trzymanie obrazków w bazie jest nierealne bo a) każdy insert do bazy to jest straszny koszt b) serwowanie obrazków z php to jest koszt znacznie większy niż lighttpd z epoll skonfigurowany do serwowania plików statycznych c) backup jest nierealny (ok, można robić po prostu snapshot wolumena dyskowego licząc na to że dane będą spójne)

    Jedyną zaletę jaką widzę to łatwiejsza replikacja.

  7. Sep 7, 2006

    Zgoda, ale pod warunkiem, ze zaladowany do systemu obrazek, ma byc dostepny publicznie. Nie zawsze jest to prawda. W wielu systemach dokumenty wpisane do “magazynu trwalego” maja chroniony dostep. Istnieja zlozone instrukcje warunkowe odnosnie prawd dostepu, czyli serwowanie ich jako kontent statyczny jest nie mozliwe.

    Warto zwrócić też uwagę na takie narzędzia jak Hibernate. W opcjach konfiguracji możemy ustawić sposób zapisu obrazków w bazie danych: poprzez system plików lub bloby. Dzieki temu będziemy łatwo mogli zmienić działanie systemu po jego uruchomieniu.

  8. Jun 16, 2008

    jak dla mnie jest tylko jedne argument za trzymaniem obrazków w bazie. Limitowana ilosc plików fizycznych na koncie. Niestety coraz powszechniej jest to stosowane,a być może akurat ja trafiam na takie hostingi. W kazdym razie – przekroczenie magicznej granicy naprz 100 000 plików (kei.pl) jest dla częsci moich serwisów kwestią kilku tygodni. A ponieważ nie ładuje dużych obrazków – tylko takie na poziomie max 40kB , pozwolilem sobie trzymac obrazki w bazie, poniewaz nie musze ciągle pisać do adminów o powiększenie limitu. Jak dla mnie był to jedyny powód trzymania obrazków w sql na publicznym serwerze. Czasami proza życia zmusza do rozwiązań które by normalnie się nie stosowało.

  9. # Artur
    Feb 21, 2009

    Ja w sprawie tajności danych. Choć jestem gorliwym zwolennikiem przechowywania plików binarnych poza bazą, to muszę się uczciwie zgodzić z argumentem bezpieczeństwa. Wyobraźmy sobie na ten przykład taką sytuację: w poniedziałek ktoś pracuje w zespole d/s czegoś i ma dostęp do danych binarnych (dokumentów zeskanowanych), a we czwartek go przenoszą i pracuje nad czymś innym. Admin odbiera mu uprawnienia dostępu do tej części bazy. Ale on, spryciula zna linki do plików – i może robić “przecieki” do prasy. Na pewno nie będzie na niego, przecież on do nich “dostępu nie ma”.

  10. Feb 22, 2009

    @Artur:
    pliki na dysku to nie to samo co brak bezpieczeństwa.

    dostęp do plików może być też chroniony przez chociażby acle systemowe. nie mówiąc już o plikach dostępnych via http.

  11. # Andrzej
    Feb 22, 2009

    @depesz:
    Niestety ale w dużym systemie z dynamicznie nadawanymi uprawnieniami do pobrania pliku rozwiązania na poziomie ACL (szczególnie w przypadku gdzie oprogramowanie udostępniające plik chodzi na tym samym użytkowniku) lub http się nie sprawdzi. Co prawda możesz zrobić mechanizm pośredniczący, który sprawdzi uprawnienia w bazie i jeśli są wystarczające to na ich podstawie odczyta z dysku plik i przekaże użytkownikowi ale tutaj już tracisz argument związany z wykorzystaniem prostych operacji w rodzaju sendfile. Dodatkowo, przy wrażliwych danych (skan umowy, dokumentów itp) trzymanie plików w DMZ na serwerze WWW to proszenie się o duże kłopoty.

  12. Feb 22, 2009

    @Andrzej:
    niby czemu użycie mechanizmu pośredniczącego sprawdzającego uprawnienia powoduje utratę możliwości użycia sendfile’a?

  13. # Andrzej
    Feb 23, 2009

    @depesz:
    gdyż serwer WWW nie będzie miał dostępu do pliku, nie będzie więc miał na czym wykonać sendfile, pośrednik po potwierdzeniu uprawnień wystawiałby dostępny (wyłącznie jemu) plik poprzez określenie nagłówka i dołączenie danych. Gdyby założyć, że pośrednik jedynie sprawdza, że użytkownik posiada wymagane uprawnienia i przekazuje URL do obrazka to miałbyś racje, pytanie tylko gdzie tu bezpieczeństwo? URL można wyciągnąć z historii przeglądarki (jak tu już Artur wspomniał), przekazać przez komunikator itd… Zakładam oczywiście, że system ma spełniać wymogi prawne dotyczące dostępu do danych “wrażliwych”, a nie jest zwykłym “allegro” ze zdjęciami produktów.

  14. Feb 23, 2009

    @Andrzej:
    daje się to bezstresowo zrobić. zobacz np. serwer PerlBal, i jego funkcję “internal redirect”.

  15. # Andrzej
    Feb 24, 2009

    @depesz:
    oczywiście, tak samo jak można zastosować inne mechanizmy, które są m.in. standardowo w apache, wprowadzasz tu jednak dodatkową warstwę, frontend nie sięga już bezpośrednio po plik, odpada argument z wydajnością bezpośredniego dostępu do pliku. Oczywiście wszystko zależy od wymagań – inne są przy serwowaniu obrazków w sklepie, w dodatku w miejscu gdzie jest bardzo duży ruch, inne są w miejscu gdzie nie każdy może mieć dostęp do pliku, udostępnianie musi podlegać ścisłej kontroli, ruch zazwyczaj jest mniejszy więc dodatkowy narzut nie jest aż tak odczuwalny.

  16. # Darek
    Dec 3, 2010

    Artykuł na pewno pomógł mi w podjęciu decyzji… 🙂 W prawdzie mam około 4k obrazków, rośnie – ale podoba mi się sposób w jaki segregujesz obrazki, podkradnę Ci ten pomysł i trochę zmodyfikuję 😉

    Takie pytanie mi się nasuwa – jak sprawdza się obiektowa baza danych z przechowywaniem obrazków? Chodzi mi o np. gemstone sql – gdzieś czytałem, że obiektowe bazy są stworzone do przechowywania obrazów, filmów, muzyki?

  17. Dec 3, 2010

    @Darek:
    nie wiem – musiałbyś zapytać kogoś kto zna takie bazy.

Leave a comment