Czy przechowywanie dużych plików (10 MB) w bazie danych jest złą praktyką?

Obecnie tworzę aplikację internetową, która pozwala użytkownikom przechowywać i udostępniać pliki o wielkości 1 MB - 10 MB.

Wydaje mi się, że przechowywanie plików w bazie danych znacznie spowolni dostęp do bazy danych.

Czy to ważna sprawa? Czy lepiej jest przechowywać pliki w systemie plików i zapisać nazwę pliku i ścieżkę w bazie danych? Czy są jakieś najlepsze praktyki związane z przechowywaniem plików podczas pracy z bazą danych?

Pracuję w PHP i MySQL dla tego projektu, ale problem jest taki sam dla większości środowisk ( Ruby on Rails , PHP , .NET ) i baz danych (MySQL, PostgreSQL ).

Powody przemawiające za przechowywaniem plików w bazie danych:

1. Spójność ACID, w tym wycofanie aktualizacji, które jest skomplikowane, gdy pliki są przechowywane poza bazą danych. Nie można tego lekko przesadzić. Bardzo przydatna może być synchronizacja plików i bazy danych oraz możliwość uczestniczenia w transakcjach.
2. Pliki idą do bazy danych i nie można z niej osierocić.
3. Kopie zapasowe automatycznie uwzględniają pliki binarne.

Przyczyna przechowywania plików w bazie danych:

1. Rozmiar pliku binarnego różni się w zależności od bazy danych. Na przykład na serwerze SQL, gdy nie używa się obiektu FILESTREAM, ma on 2 GB. Jeśli użytkownicy muszą przechowywać pliki większe (jak powiedzmy film), musisz skakać przez obręcze, aby magia się wydarzyła.
2. Zwiększa rozmiar bazy danych. Jedna ogólna koncepcja, którą należy wziąć sobie do serca: Poziom wiedzy wymagany do prowadzenia bazy danych rośnie proporcjonalnie do wielkości bazy danych.Tzn. Duże bazy danych są bardziej skomplikowane w utrzymaniu niż małe bazy danych. Przechowywanie plików w bazie danych może znacznie zwiększyć bazę danych. Nawet jeśli powiedziano by, że wystarczyłaby codzienna pełna kopia zapasowa, przy większym rozmiarze bazy danych, możesz już nie być w stanie tego zrobić. Być może będziesz musiał rozważyć umieszczenie plików w innej grupie plików (jeśli baza danych to obsługuje), zmodyfikuj kopie zapasowe, aby oddzielić kopię zapasową danych od kopii plików itp. Żadnej z tych rzeczy nie można się nauczyć, ale nie zwiększyć złożoność konserwacji, co oznacza koszt dla firmy. Większe bazy danych również zużywają więcej pamięci, ponieważ próbują upchnąć jak najwięcej danych do pamięci.
3. Przenośność może być problemem, jeśli korzystasz z funkcji specyficznych dla systemu, takich jak obiekt SQL Server FILESTREAMi musisz przeprowadzić migrację do innego systemu bazy danych.
4. Problemem może być kod zapisujący pliki do bazy danych. Jedna firma, z którą konsultowałem się nie tak wiele księżyców temu, w pewnym momencie podłączyła frontend Microsoft Access do swojego serwera bazy danych i wykorzystała zdolność Accessa do przesyłania „czegokolwiek” za pomocą kontroli Ole Object. Później zmienili się, aby użyć innej kontroli, która nadal polegała na Ole. Znacznie później ktoś zmienił interfejs do przechowywania surowego pliku binarnego. Wydobycie tych Ole Object było nowym poziomem piekła. Podczas przechowywania plików w systemie plików nie jest wymagana dodatkowa warstwa do zawijania / poprawiania / modyfikowania pliku źródłowego.
5. Udostępnianie plików na stronie internetowej jest bardziej skomplikowane. Aby to zrobić za pomocą kolumn binarnych, musisz napisać moduł obsługi, aby przesyłać strumieniowo plik binarny z bazy danych. Możesz to zrobić, nawet jeśli przechowujesz ścieżki plików, ale nie musisz tego robić. Ponownie dodanie modułu obsługi nie jest niemożliwe, ale zwiększa złożoność i jest kolejnym punktem niepowodzenia.
6. Nie możesz korzystać z pamięci w chmurze. Załóżmy, że pewnego dnia chcesz przechowywać swoje pliki w wiadrze Amazon S3. Jeśli to, co przechowujesz w bazie danych, to ścieżki do plików, masz możliwość zmiany ich na ścieżki w S3. O ile mi wiadomo, nie jest to możliwe w żadnym scenariuszu z żadnym DBMS.

IMO, uznając przechowywanie plików w bazie danych lub nie za „złe”, wymaga więcej informacji na temat okoliczności i wymagań. Czy rozmiar i / lub liczba plików zawsze będzie mała? Czy nie ma planów korzystania z przestrzeni dyskowej w chmurze? Czy pliki będą udostępniane na stronie internetowej lub w pliku binarnym, takim jak aplikacja Windows?

Ogólnie rzecz biorąc, z mojego doświadczenia wynika, że ​​przechowywanie ścieżek jest tańsze dla firmy, nawet biorąc pod uwagę brak ACID i możliwość sierot. Nie oznacza to jednak, że internet nie jest legionem z historiami o braku kontroli ACID w przypadku przechowywania plików, ale ogólnie oznacza, że ​​rozwiązanie to jest łatwiejsze do zbudowania, zrozumienia i utrzymania.

W wielu przypadkach jest to zły pomysł. Nadęje pliki bazy danych i spowoduje szereg problemów z wydajnością. Jeśli umieścisz obiekty BLOB w tabeli z dużą liczbą kolumn, będzie jeszcze gorzej.

Jednak! Niektóre bazy danych, takie jak SQL Server, mają typ kolumny FILESTREAM. W takim przypadku dane są faktycznie przechowywane w osobnym pliku na serwerze bazy danych, a tylko identyfikator pliku jest zapisywany w tabeli. W tym przypadku nie widzę powodu, aby nie przechowywać danych na serwerze SQL. Pliki są automatycznie dołączane jako część kopii zapasowej serwera, a baza danych i pliki nigdy nie są zsynchronizowane. Problem z sugestią Tony'ego dotyczącą przechowywania nazw plików polega na tym, że baza danych i system plików mogą się nie zsynchronizować. Baza danych twierdzi, że plik istnieje, gdy zostanie usunięty na dysku. Jeśli proces modyfikuje bazę danych, a następnie ulega awarii, pliki i baza danych nie będą zgodne (tzn. Brak ACID z plikami poza bazą danych).

Tak, to zła praktyka.

Wpływ na wydajność DB:

• jeśli zrobisz to SELECTz dowolną kolumną BLOB, zawsze uzyskasz dostęp do dysku, podczas gdy bez BLOB masz szansę na uzyskanie danych bezpośrednio z pamięci RAM (DB o wysokiej przepustowości zostanie zoptymalizowany, aby pasował do tabel w pamięci RAM);
• replikacja będzie powolna, opóźnienie replikacji wysokie, ponieważ będzie musiała zepchnąć BLOB do slave'ów. Duże opóźnienie replikacji spowoduje różnego rodzaju warunki wyścigu i inne problemy z synchronizacją, chyba że wyraźnie weźmiesz to pod uwagę;
• Tworzenie kopii zapasowych / przywracanie bazy danych potrwa znacznie dłużej;

Przewaga prędkości - brak ! Podczas gdy niektóre starsze systemy plików nie radziłyby sobie dobrze z katalogami zawierającymi miliony plików, większość współczesnych nie ma w ogóle problemu i faktycznie używa takich samych struktur danych jak BD (zazwyczaj B-drzewa). Na przykład ext4 (domyślny system plików Linux) używa Htree .

Wniosek: obniży wydajność bazy danych i nie poprawi wydajności pobierania plików.

Ponadto, ponieważ mówisz o aplikacji internetowej - serwowanie plików statycznych bezpośrednio z systemu plików za pomocą nowoczesnego serwera WWW, który może wykonywać połączenia sendfile()systemowe, to ogromna poprawa wydajności. Nie jest to oczywiście możliwe, jeśli pobierasz pliki z bazy danych. Rozważmy na przykład ten test porównawczy , pokazujący, że Ngnix wykonuje 25 000 wymagań / s przy 1000 równoczesnych połączeniach na niskim laptopie. Tego rodzaju obciążenie usmażyłoby dowolny rodzaj DB.

Byłbym do tego pragmatyczny i kierowałbym się zasadą „nie optymalizuj jeszcze”. Stwórz rozwiązanie, które ma sens w tej chwili, i takie, które masz zasoby programistyczne do prawidłowego wdrożenia. Istnieje wiele potencjalnych problemów . Ale niekoniecznie stają się prawdziwymi problemami. Np. Prawdopodobnie nie będzie problemu, jeśli masz 100 użytkowników. Może to stanowić problem, jeśli masz 100 000 lub 10 000 000 użytkowników. Ale w tym drugim przypadku powinna istnieć podstawa dla większych zasobów programistycznych do rozwiązania wszystkich problemów.

Ale przechowywanie danych w bazie danych odciąża cię od radzenia sobie z innymi problemami, np. Gdzie powinny być przechowywane pliki, jak powinny być tworzone kopie zapasowe itp. Ponieważ piszesz aplikację internetową, ze względów bezpieczeństwa byłby to bardzo dobry pomysł aby upewnić się, że proces obsługujący aplikację nie ma dostępu do zapisu w systemie plików, należy więc skonfigurować serwer, aby proces miał dostęp do odczytu / zapisu w folderze, w którym przechowywane są dane.

Osobiście wybrałbym przechowywanie danych w bazie danych, ale upewnij się, że BLOBY nie są odczytywane, dopóki nie są naprawdę potrzebne, tj. Nie wykonuje się polecenia „WYBIERZ * OD ...” na tabelach zawierających blogi. I upewniłbym się, że projekt ułatwia przenoszenie danych z bazy danych do systemu plików, jeśli pojawią się problemy z wydajnością. Na przykład przechowuj informacje o pliku w osobnej tabeli plików , dzięki czemu informacje o pliku będą przechowywane z dala od innych podmiotów biznesowych.

Zakładając, że masz klasę File do reprezentowania pliku odczytanego w bazie danych, wpływ kodowania późniejszego przeniesienia go będzie minimalny.

Microsoft opublikował białą księgę na ten temat kilka lat temu. Koncentruje się na SqlServer, ale możesz tam znaleźć kilka interesujących informacji:

Do BLOBA czy nie do BLOBA? Przechowywanie dużych obiektów w bazie danych lub systemie plików?

Bardzo zwięzłą wersją ich wniosków jest:

Podczas porównywania systemu plików NTFS i SQL Server 2005, BLOBS mniejsze niż 256 KB są bardziej wydajnie obsługiwane przez SQL Server, podczas gdy NTFS jest bardziej wydajny dla BLOBS większych niż 1 MB.

Polecam napisać kilka małych testów dla konkretnego przypadku użycia. Pamiętaj, że musisz uważać na efekty buforowania. (Byłem zaskoczony, gdy po raz pierwszy uzyskałem prędkości zapisu na dysk, które wydawały się mieć większą przepustowość niż było to fizycznie możliwe!)

Stara konwencjonalna mądrość przechowywania plików poza bazą danych może już się nie utrzymywać. Zasadniczo wolę uczciwość niż szybkość, a dzięki nowoczesnemu systemowi DBMS możesz mieć jedno i drugie.

Tom Kyte wydaje się zgadzać :

Nie znam zalet przechowywania danych, które chcę przechowywać przez długi czas poza bazą danych.

Jeśli jest w bazie danych, mogę

upewnij się, że jest profesjonalnie zarządzany

kopii zapasowej

do odzyskania (z resztą danych)

zabezpieczone

skalowalny (spróbuj umieścić 100 000 dokumentów w jednym katalogu, teraz umieść je w tabeli - która to „skaluje” - to nie jest katalog)

Mogę łatwo cofnąć usunięcie (flashback)

Mam blokadę

Przeczytałem spójność ...

Tak.

Jeśli podajesz plik ze swojego systemu plików, twój serwer WWW może użyć kodu jądra, takiego jak sendfile () na BSD lub Linux, aby skopiować plik bezpośrednio do gniazda. Jest bardzo szybki i bardzo wydajny.

Udostępnianie plików poza bazą danych oznacza, że ​​musisz skopiować dane z dysku serwera bazy danych do pamięci serwera bazy danych, następnie z pamięci serwera db na port sieciowy serwera db, następnie z sieci do procesu serwera WWW, a następnie ponownie na serwer wychodzące połączenie sieciowe.

O ile nie masz naprawdę dobrego powodu, aby zawsze tego nie robić, zawsze lepiej jest podawać pliki statyczne z systemu plików.

Słynny Tom Kyte napisał, że oni (Oracle) używają bazy danych Oracle jako serwera plików i działa ona doskonale, nawet szybciej niż normalny system plików, z pełną transakcyjnością, bez utraty wydajności i z jedną kopią zapasową.

Tak, ale pamiętaj, że są producentem bazy danych Oracle, a dla każdego innego użytkownika występują problemy z kosztami. Korzystanie z komercyjnej bazy danych, takiej jak Oracle, do przechowywania plików jest po prostu nieefektywne kosztowo.

Jednak na przykład w PostgreSQL można po prostu uruchomić inną instancję DB tylko do przechowywania obiektów blob. Masz wtedy pełne wsparcie transakcyjne. Ale transakcyjność kosztuje przestrzeń DB. Istnieje potrzeba przechowywania w bazie danych wielu instancji obiektów blob dla wielu jednoczesnych transakcji. Na PostgreSQL jest to najbardziej bolesne, ponieważ ta baza danych przechowuje duplikaty obiektów blob wykonane dla transakcji są przechowywane, nawet jeśli nie są już potrzebne, dopóki proces VACUUM nie zostanie zakończony.

Z drugiej strony, w przypadku przechowywania systemu plików, należy zachować szczególną ostrożność, gdy ktoś modyfikuje plik, ponieważ transakcję można wycofać, a kopię pliku należy zachować, dopóki stara wersja nie będzie już widoczna.

W systemie, w którym pliki są tylko dodawane i usuwane, a transakcyjny dostęp do plików nie stanowi problemu, pamięcią systemu plików będzie IMHO najlepszym wyborem.

Zwykle najlepiej jest przechowywać duże BLOBY w osobnej tabeli i po prostu przechowywać odniesienie klucza obcego do BLOBa w głównej tabeli. W ten sposób nadal możesz odzyskać plik z bazy danych (więc nie potrzebujesz specjalnego kodu) i uniknąć problemów związanych z zewnętrznymi zależnościami DB (utrzymywanie synchronizacji DB i systemu plików itp.), Ale ponosisz tylko ten narzut jeśli wyraźnie dołączysz do tej tabeli (lub wykonasz osobne połączenie). 10 MB nie jest strasznie duże, większość nowoczesnych komercyjnych baz danych nie będzie miała problemu. Jedynym powodem, dla którego przechowuję plik w systemie plików, jest ograniczenie przepustowości bazy danych. Jeśli baza danych będzie tasować wiele z tych plików, może być konieczne podzielenie obciążenia i zapisanie tylko pewnego rodzaju deskryptora pliku. Następnie możesz mieć osobne wywołanie, aby załadować plik z innego serwera,

Możesz napotkać niektóre z tych problemów:

• Wykonanie SELECT *operacji, która obejmuje wiersz z dużym obiektem blob, zajmuje bardzo dużo czasu, nawet jeśli nie potrzebujesz obiektu blob (Oczywiście powinieneś dokonać określonego wyboru, ale czasami aplikacje są napisane w ten sposób)
• Tworzenie kopii zapasowej może potrwać znacznie dłużej. W zależności od potrzeb może być konieczne zablokowanie tabel na czas tworzenia kopii zapasowej, więc możesz chcieć utrzymać niski czas tworzenia kopii zapasowej
• Przywracanie zajmie również znacznie więcej czasu.
• Jeśli zabraknie Ci miejsca, musisz wymyślić jakiś sposób (może przenieść całą bazę danych na nowy serwer), aby rozwiązać ten problem. Przechowując pliki w systemie plików, zawsze możesz zamontować inny dysk twardy i ustawić miękkie linki.
• Po prostu wyszukiwanie pliku do debugowania lub innych informacji nie jest tak łatwe. Dotyczy to również skryptów, które mogą nie mieć dostępu do bazy danych, ale potrzebują informacji z różnych plików.

Oczywiście zyskujesz także pewne korzyści:

• Kopie zapasowe danych i plików menu są zsynchronizowane
• Usunięcie pliku bez wiedzy bazy danych nie jest możliwe
• Nie musisz czytać pliku z dysku, ale możesz to zrobić w jednej instrukcji SQL
• Możesz pobrać bazę danych, dołączyć zrzut do środowiska programistycznego i mieć wszystkie zależności w tym miejscu

Osobiście nie robię tego, ponieważ uważam, że wady są znacznie cięższe niż zalety. Ale jak wspomniano powyżej, zależy to całkowicie od przypadku użycia i tym podobnych.

Niektóre systemy zarządzania treścią Enterpirse, takie jak SiteCore, używają jednej bazy danych do przechowywania danych strony i innej bazy danych do przechowywania plików. Używają MS SQL Server.

W celu praktycznego wdrożenia, możesz się martwić:

Korzyści:

1. Cała zawartość pliku jest zdecydowanie zsynchronizowana z twoim stołem. Jak wspomniano powyżej, tworzenie kopii zapasowych danych jest całkowicie wygodne, ponieważ nie trzeba synchronizować danych z systemem plików.
2. Z kodowania można uzyskać zawartość pliku bezpośrednio z wyboru SQL.
3. Z zapytania możesz nawet filtrować zawartość pliku lub jego rozmiar wprost z instrukcji SQL.

Wady:

1. W porównaniu do bazy danych, której struktura jest semantycznie taka sama, ale nie przechowuje zawartości pliku, baza danych ma tendencję do zużywania radykalnie większej ilości pamięci podczas wykonywania zapytań.
2. Automatyczna kopia zapasowa może powodować problemy z wydajnością, ale niewiele. Wyobraźmy sobie, że twój serwer bazy danych tworzy kopie zapasowe co 6 godzin, a te bazy danych przechowują 10 MB pliku na rekord. Ten scenariusz nie jest tym, czego chcesz.