Хабр Курсы для всех
РЕКЛАМА
Большая витрина: от крупнейших школ до частных авторов. Сравнивайте по цене, длительности, формату и выбирайте самый подходящий курс!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 99
Решить перечисленные проблемы с файлами гораздо проще, чем решить, например, проблемы с производительностью, когда файлы в бд.
1-пункт из вашего «мозгового штурма» + скрипт сборки мусора.
1-пункт из вашего «мозгового штурма» + скрипт сборки мусора.
Именно так сейчас и сделано в нашем проекте, но хотелось бы сделать более прозрачный и удобный механизм хранения файлов, так как задача очень распространенная, и для каждого хранилища писать сборщики мусора как то не очень хочется.
Это от проекта зависит.
Для не очень посещаемого ресурса, для сайта-визитки (если речь идёт о ЦМС), для ресурса, работающего где-нибудь в пределах локальной сети общежития, кафедры или школы например, такой подход вполне приемлим.
Для не очень посещаемого ресурса, для сайта-визитки (если речь идёт о ЦМС), для ресурса, работающего где-нибудь в пределах локальной сети общежития, кафедры или школы например, такой подход вполне приемлим.
вел проект соц.сеть, хранили все фотки в базе, как написано выше. никаких проблем с производительностью не чувствовали.
> файлы не удаляются при удалении соответствующей записи БД
Это почему? Все спокойно удаляется, или может мы вас не поняли?
>проблемы при одновременной попытке обновления файла
Разъясните, что вы имеете ввиду «обновление файла»?
>нарушение синхронизации между БД и файловой системой при откате транзакции
Кто вам мешает удаленный файл «ложить» в корзину?
>при резервном копировании и восстановлении информации в БД может возникнуть рассинхронизация с файловой системой
Спорный вопрос
>файлы не подчиняются ограничениям доступа, наложенным с помощью БД
Ну это другое дело :)
Ну а хранить файлы в БД — это извините имхо бред. Пару фильмов и ваша БД будет необъятных размеров.
Плюс разные «преобразования» до добра не доводят — это еще гораздо худшее зло.
Это почему? Все спокойно удаляется, или может мы вас не поняли?
>проблемы при одновременной попытке обновления файла
Разъясните, что вы имеете ввиду «обновление файла»?
>нарушение синхронизации между БД и файловой системой при откате транзакции
Кто вам мешает удаленный файл «ложить» в корзину?
>при резервном копировании и восстановлении информации в БД может возникнуть рассинхронизация с файловой системой
Спорный вопрос
>файлы не подчиняются ограничениям доступа, наложенным с помощью БД
Ну это другое дело :)
Ну а хранить файлы в БД — это извините имхо бред. Пару фильмов и ваша БД будет необъятных размеров.
Плюс разные «преобразования» до добра не доводят — это еще гораздо худшее зло.
Почитайте презентацию SQL Antipatterns, там более подробно расписано и даже нарисовано про проблемы храниения файлов отдельно от БД.
А по поводу хранения файлов в БД — абсолютно согласен с вами, это огромный удар по производительности. И цель этого поста была — именно рассмотреть способы устранения недостатоков хранения файлов «отдельнно» и проблем с производительностью при хранении «в БД».
А по поводу хранения файлов в БД — абсолютно согласен с вами, это огромный удар по производительности. И цель этого поста была — именно рассмотреть способы устранения недостатоков хранения файлов «отдельнно» и проблем с производительностью при хранении «в БД».
Такой ли уж «огромный удар по производительности»? Интересуюсь серьёзно — есть ли какие-нибудь замеры?
Вот для обсуждения такая архитектура: выделенный MySQL/ngnix сервер для хранения файлов. В MySQL на каждый проект одна база, в базе одна таблица Files с теми полями, которые у Вас в статье описаны. Эта таблица связывается с таблицами Вашего проекта, которые находятся на другом сервере. Поддержка целостности ложится на плечи SQL-сервера.
На ngnix запущен простой скрипт, который по path находит запись (если не найдена, возвращает 404), открывает BLOB для потокового чтения и тупо всё отдаёт. Единственную сложность вижу здесь: надо протестировать заголовки HTTP Location и Length, чтобы докачка поддерживалась, для больших файлов это надо.
При таком подходе данные практически прямо с диска попадают в TCP stream. Теоретически просадка по производительности составит, ну, 2-3-5%, никак ни 20, ни 30%. Вы проверяли, как будет на практике? Может оказаться работоспособным вариантом.
Вот для обсуждения такая архитектура: выделенный MySQL/ngnix сервер для хранения файлов. В MySQL на каждый проект одна база, в базе одна таблица Files с теми полями, которые у Вас в статье описаны. Эта таблица связывается с таблицами Вашего проекта, которые находятся на другом сервере. Поддержка целостности ложится на плечи SQL-сервера.
На ngnix запущен простой скрипт, который по path находит запись (если не найдена, возвращает 404), открывает BLOB для потокового чтения и тупо всё отдаёт. Единственную сложность вижу здесь: надо протестировать заголовки HTTP Location и Length, чтобы докачка поддерживалась, для больших файлов это надо.
При таком подходе данные практически прямо с диска попадают в TCP stream. Теоретически просадка по производительности составит, ну, 2-3-5%, никак ни 20, ни 30%. Вы проверяли, как будет на практике? Может оказаться работоспособным вариантом.
Для MySQL требуется скрипт, который будет выбирать файл из базы и отдавать http-серверу.
Пока файл качается — скрипт занимает память. Файлы могут качаться долго и в несколько потоков. В итоге, память тратится нерационально.
Пока файл качается — скрипт занимает память. Файлы могут качаться долго и в несколько потоков. В итоге, память тратится нерационально.
потому что nginx ипользует sendfile, а при запросе его из бд будет несколько дополнительных копирований данных
Полез проверять. С удивлением обнаружил, что MySQL не поддерживает работу с блобами, как с потоками, даже в C API.
Зато оказалось, что в сети есть несколько проектов BLOB streaming engine extention, например, вот: blobstreaming.org/
Причём у них даже HTTP-сервер простой реализован, так что даже скрипт писать не надо.
Зато оказалось, что в сети есть несколько проектов BLOB streaming engine extention, например, вот: blobstreaming.org/
Причём у них даже HTTP-сервер простой реализован, так что даже скрипт писать не надо.
Есть замеры. Производительность начинает заметно сильнее просидать после того как фаил переваливает около 5 мб.
Замеры были на оракле и mssql про mysql мне кажется тоже можно нарыть. Исследование проводилось каким-то институтом американским. Резюме было такое, что хранить до 5 мб файлы можно со свистом в БД, после 5 лучше в файловой системе, именно по этому в оракле и mssql есть на этот счет специальный функционал (filestreaming и т.д.) это когда база сама следит за целостностью файлов, которые хранятся в файловой системе.
Замеры были на оракле и mssql про mysql мне кажется тоже можно нарыть. Исследование проводилось каким-то институтом американским. Резюме было такое, что хранить до 5 мб файлы можно со свистом в БД, после 5 лучше в файловой системе, именно по этому в оракле и mssql есть на этот счет специальный функционал (filestreaming и т.д.) это когда база сама следит за целостностью файлов, которые хранятся в файловой системе.
Спасибо. Буду иметь в виду.
В тех проектах, в которых я участвовал, верхняя граница была где-то в районе 7-8 метров, наверное, потому и не было заметно больших просадок. Для отправки контента использовались ADO.NET, где есть операции поблочного чтения блобов.
В тех проектах, в которых я участвовал, верхняя граница была где-то в районе 7-8 метров, наверное, потому и не было заметно больших просадок. Для отправки контента использовались ADO.NET, где есть операции поблочного чтения блобов.
Вы, наверное, про Oracle больше имели в виду.
В MS SQL я такой возможности не помню, полез искать — так и есть, нововведение в 2008 версии. :)
В MS SQL я такой возможности не помню, полез искать — так и есть, нововведение в 2008 версии. :)
и будет к mysql вместо 10-ти, как обычно, соединений — 1000 (тысяча), ибо тпштч или нет — а большой файл будет долго тянуться именно с mysql
Тут уж надо определяться: если речь о большом проекте, где возможна одновременное скачивание 1000 файлов, надо выносить эту базу на отдельный сервер, как у меня и предложено.
Вообще же, накладных расходов там должно быть немного: индекс по имени файла в оперативке, а это вообще копейки (скажем, миллион файлов при средней длине пути 60 символов вполне уложится где-то в 80 метров). Сколько то килобайт на каждое подключение, при 1000 подключений это тоже мегабайты. Всю остальную оперативку можно выделить под дисковый кеш — те файлы, которые чаще скачиваются, будут лежать в оперативке и отдаваться ещё быстрее.
Одна беда — без blob streaming есть только один способ прочитать файл — весь его скачать в оперативку. Вот это уже засада.
Вообще же, накладных расходов там должно быть немного: индекс по имени файла в оперативке, а это вообще копейки (скажем, миллион файлов при средней длине пути 60 символов вполне уложится где-то в 80 метров). Сколько то килобайт на каждое подключение, при 1000 подключений это тоже мегабайты. Всю остальную оперативку можно выделить под дисковый кеш — те файлы, которые чаще скачиваются, будут лежать в оперативке и отдаваться ещё быстрее.
Одна беда — без blob streaming есть только один способ прочитать файл — весь его скачать в оперативку. Вот это уже засада.
одновременных скачиваний много, если медленные клиенты. Причём либо постоянно выкачиваем файлы из базы в дисковый кэш, либо как Вам и хочется, читаем blob'ы как поток — и получаем эти 1000 соединений с mysql.
Да и с масштабированием есть проблемы — файлов на терабайты придумать легко, а вот сервер mysql на терабайты мне как-то не представляется.
Да и с масштабированием есть проблемы — файлов на терабайты придумать легко, а вот сервер mysql на терабайты мне как-то не представляется.
Ну вот я и интересуюсь — в чём проблема в 1000 соединений с MySQL? Ну, пусть 20 килобайт занимает каждое соединение, 1000 соединений это 20 мегабайт ОЗУ. Какие ещё накладные расходы?
Кстати, я подозреваю, что решение, ссылку на которое я выше кидал (blobstreaming.com) не требует отдельных подключений.
По последнему пункту: пока не понял, в чём проблема с масштабированием. Поставили два сервера, три, четыре — разве не будет работать? Почему?
Кстати, я подозреваю, что решение, ссылку на которое я выше кидал (blobstreaming.com) не требует отдельных подключений.
По последнему пункту: пока не понял, в чём проблема с масштабированием. Поставили два сервера, три, четыре — разве не будет работать? Почему?
а если сервера в разных датацентрах, что при раздаче файлов и получается? файлы перегнать через rsync — легко, а реплицировать базу?
И мы получаем ещё одну возможную проблему. Длительность транзакций резко увеличивается при хранении файлов, наверняка будут проблемы с целостностью базы.
И мы получаем ещё одну возможную проблему. Длительность транзакций резко увеличивается при хранении файлов, наверняка будут проблемы с целостностью базы.
> а если сервера в разных датацентрах, что при раздаче файлов и получается?
У меня подобного опыта нет. Думаю, что при таких объёмах и решение будет уникальным, и одним rsync здесь не обойтись. Более того, это отдельная задача и в рамках текущей дискуссии. Одно дело хранить файлы в дополнение к какому-то основному функционалу, где требуется активно задействовать SQL. Совсем другое — специализироваться на терабайтах и датацентрах. Подозреваю, что для такой задачи SQL не сильно нужен.
> файлы перегнать через rsync — легко, а реплицировать базу?
Если в SQL всё равно хранить часть информации, то придётся реплицировать и файлы, и базу. Вот в этом случае вероятность рассинхронизации возрастает многократно. Представьте: база уже новая, а файлов ещё нет. Вот если всё лежит в базе, или всё в файлах, тогда проблем меньше.
> И мы получаем ещё одну возможную проблему. Длительность транзакций резко
> увеличивается при хранении файлов, наверняка будут проблемы с целостностью базы.
Э-э-э-э… Мы про что сейчас говорим? Теряю нить рассуждения. Про blobstreaming для mysql из ссылки выше?
У меня подобного опыта нет. Думаю, что при таких объёмах и решение будет уникальным, и одним rsync здесь не обойтись. Более того, это отдельная задача и в рамках текущей дискуссии. Одно дело хранить файлы в дополнение к какому-то основному функционалу, где требуется активно задействовать SQL. Совсем другое — специализироваться на терабайтах и датацентрах. Подозреваю, что для такой задачи SQL не сильно нужен.
> файлы перегнать через rsync — легко, а реплицировать базу?
Если в SQL всё равно хранить часть информации, то придётся реплицировать и файлы, и базу. Вот в этом случае вероятность рассинхронизации возрастает многократно. Представьте: база уже новая, а файлов ещё нет. Вот если всё лежит в базе, или всё в файлах, тогда проблем меньше.
> И мы получаем ещё одну возможную проблему. Длительность транзакций резко
> увеличивается при хранении файлов, наверняка будут проблемы с целостностью базы.
Э-э-э-э… Мы про что сейчас говорим? Теряю нить рассуждения. Про blobstreaming для mysql из ссылки выше?
>Э-э-э-э… Мы про что сейчас говорим? Теряю нить рассуждения. Про
>blobstreaming для mysql из ссылки выше?
Нет, про вообще хранение в базе при любом алгоритме. Обычно скрипт отрабатывает за доли секунды, и на вероятность, что что-то случится за время работы скрипта (перезагрузка компьютера) и не все требуемые операции с базой завершатся мы как-бы не обращаем внимания — mysql транзакций не имеет и на риск приходится идти. А в случае хранения файла размером 30 мегабайт время выполнения скрипта будет уже секунды. Вероятность аварийного завершения, соответственно, на порядок или на порядки выше.
>blobstreaming для mysql из ссылки выше?
Нет, про вообще хранение в базе при любом алгоритме. Обычно скрипт отрабатывает за доли секунды, и на вероятность, что что-то случится за время работы скрипта (перезагрузка компьютера) и не все требуемые операции с базой завершатся мы как-бы не обращаем внимания — mysql транзакций не имеет и на риск приходится идти. А в случае хранения файла размером 30 мегабайт время выполнения скрипта будет уже секунды. Вероятность аварийного завершения, соответственно, на порядок или на порядки выше.
>Ну вот я и интересуюсь — в чём проблема в 1000 соединений с MySQL? Ну, пусть 20 килобайт занимает каждое соединение
У Вас очень скромные настройки mysql :)
memory = key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
У меня сейчас довольно скромные настройки среднего сервера (едва за сотню sql-запросов в секунду). Так при 1000 соединений оно сожрёт в моём случае:
memory = 384M + (3M+3M)*1000 = 6384M. А на сервере всего 2,5Гб :)
У Вас очень скромные настройки mysql :)
memory = key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
У меня сейчас довольно скромные настройки среднего сервера (едва за сотню sql-запросов в секунду). Так при 1000 соединений оно сожрёт в моём случае:
memory = 384M + (3M+3M)*1000 = 6384M. А на сервере всего 2,5Гб :)
>Такой ли уж «огромный удар по производительности»?
1. Разница в скорости отдачи статики и динамики — уже порядок или даже более. Независимо от бэкенда динамики.
2. Проблемы при параллельной раздаче с модификациями — mysql будет периодически лочить всю базу.
3. Наконец, засовывать 30-50-100Гб в одну базу — это страшно :D
1. Разница в скорости отдачи статики и динамики — уже порядок или даже более. Независимо от бэкенда динамики.
2. Проблемы при параллельной раздаче с модификациями — mysql будет периодически лочить всю базу.
3. Наконец, засовывать 30-50-100Гб в одну базу — это страшно :D
Если я правильно понял автора, то последний пункт реализует целостность данных на уровне БД, а не на программном, как во всех других случаях.
Идея вобщем-то хорошая, по-моему.
Идея вобщем-то хорошая, по-моему.
Вобщем-то давно пришли к мнению, что зло рассинхронизации БД с ФС сервера намного меньше, чем зло хранения файлов в БД.
Меньшее зло, но почему бы не объеденить преимущества обоих подходов? Файлы и как бы в БД и при этом лежат просто на ФС. Осталось самая малость — написать storage engine для MySQL :-)
Наверное потому, что это неизбежно вызовет оверхед использования ФС.
Зачем городить надстройку над MySQL, которая бы повторяла функционал ФС? Если есть просто ФС?
Зачем городить надстройку над MySQL, которая бы повторяла функционал ФС? Если есть просто ФС?
Абсолютно согласен. Связь между ФС и БД должен реализовывать класс Модели файлов, который и будет обеспечивать целостность записи, изменения и удаления.
Обычно Мускул подключается через сокеты в PHP скрипт.
И что же мы имеем если файлы у нас в БД: дополнительную нагрузку на сокетах (а чтение с диска как было так и останется, за исключение закешированных файлов самим мускулом(хотя мускул по-моему блобы не кеширует))
Обычно Мускул подключается через сокеты в PHP скрипт.
И что же мы имеем если файлы у нас в БД: дополнительную нагрузку на сокетах (а чтение с диска как было так и останется, за исключение закешированных файлов самим мускулом(хотя мускул по-моему блобы не кеширует))
Вы не совсем поняли идею — файлами управляет MySQL — создает, удаляет, переименовывует и т.д., а пишем и читаем мы их просто через файловую систему, без всяких MySQL, сокетов и блобов…
Эм. Я конечно задам дурацкий вопрос, но зачем тут СУБД?
Для поддержания целостности…
Для этого достаточно использовать транзакции.
Т.е. например, при чистке мусора запросить из базы файлы которые необходимо удалить, затем средствами php удалить их, сообщить базе по завершении, что всё удалено и удалить соответствующие записи. Я правильно понял?
Да. Для ускорения процесса, лучше делать двухфазное удаление. В первой транзакции помечаем, что файлы удалены (ставим флажок), потом переодически запускаем сервис уборщика мусора, который выбирает такие записи, откывает транзакцию, удаляет файлы, затем удаляет записи и закрывает транзакцию. В случае если транзакция откатилась, повторить транзакцию.
И того по два запроса на каждый цикл уборки + необходимость писать уборщик. Вместо того чтобы просто удаляя строку из бд автоматически удалить все связанные с ней файлы средствами mysql.
а если в файловом хранилище более одного узла?
Обычно в БД хранят не картинки интерфейса, которые дергаются один раз и остаются в кеше, а скажем фотография к новости (пользователь посмотрел новость и ушел дальше к следующей), архивы, фильмы (скачал один раз и все). Тоесть кешированием тут не решишь проблему производительности.
Я бы предложил не новый Engine, а новый тип поля — FILE
Типа такого:
CREATE TABLE `data_storage`.`files` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`path` VARCHAR( 255 ),
`data` FILE
)
или
SELECT * FROM `data_storage`.`files`WHERE FILE_SIZE(`data`) > 10000
При этом mySQL сам создает отдельный файл для каждой записи таблицы. И есть дополнительные функции для работы с полем типа FILE.
Основной недостаток такого подхода (как в Вашей, так и в моей модификации), особенно при больших файлах — это пожирание оперативки и дикий траффик между клиентом (например, php-скриптом) и mySQL-сервером, который в общем случае может быть сетевым.
Типа такого:
CREATE TABLE `data_storage`.`files` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`path` VARCHAR( 255 ),
`data` FILE
)
или
SELECT * FROM `data_storage`.`files`WHERE FILE_SIZE(`data`) > 10000
При этом mySQL сам создает отдельный файл для каждой записи таблицы. И есть дополнительные функции для работы с полем типа FILE.
Основной недостаток такого подхода (как в Вашей, так и в моей модификации), особенно при больших файлах — это пожирание оперативки и дикий траффик между клиентом (например, php-скриптом) и mySQL-сервером, который в общем случае может быть сетевым.
Лично мое мнение — оставьте файлы файловым системам.
Вышеупомянутые проблемы решаются настолько элементарно, что выглядят притянутыми за уши.
Вышеупомянутые проблемы решаются настолько элементарно, что выглядят притянутыми за уши.
Однажды мой знакомый сказал: «Храни файлы в БД лишь тогда, когда иначе ну совсем нельзя»
Дело в том что на самом деле такой подход нарушает суть самой базы данных! Ведь та же аватарка — это данные о пользователе. И как бы там нибыло файлы ничем не отличаются от описания пола пользователя, просто они больше!
Суть БД в том, что она хранит и централизованно обрабатывает данные. И то что существующие БД не поддерживают полноценную работу с файлами — признак того, что на самом деле эти БД неполноценны, как таковые, т.к. не соответствуют своей сути. Вам никогда не приходило в голову: почему каждый раз как дело касается работы с файлами программисту приходится самостоятельно создавать систему синхронизации и поддержания целостности данных?
Суть БД в том, что она хранит и централизованно обрабатывает данные. И то что существующие БД не поддерживают полноценную работу с файлами — признак того, что на самом деле эти БД неполноценны, как таковые, т.к. не соответствуют своей сути. Вам никогда не приходило в голову: почему каждый раз как дело касается работы с файлами программисту приходится самостоятельно создавать систему синхронизации и поддержания целостности данных?
Данные в БД не только для хранения, но и для последующего поиска. Например — фильтр по тому или иному параметру.
Кстати, другой мой знакомый говорил (это было во времена, когда форумы и гостевые использовали в качестве БД текстовые файлы): использование текстовых файлов в качестве БД на порядок лучше в плане производительности, нежели реляционные БД. Но лишь до тех пор, пока нам не потребовалось что-то искать.
Искать что-либо в BLOB будет несколько странно, а значит одно из самых серьёзных преимуществ БД теряет смысл.
Кроме того лично очень осторожно отношусь к действиям, которые увеличивают размер БД. Больше размер — меньше производительность. Аватарки в БД для форума на 100 человек — ерунда. Коллекция фотографий каждого пользователя многомиллионной соцсети — неразумное раздутие архитектуры проекта.
Кстати, другой мой знакомый говорил (это было во времена, когда форумы и гостевые использовали в качестве БД текстовые файлы): использование текстовых файлов в качестве БД на порядок лучше в плане производительности, нежели реляционные БД. Но лишь до тех пор, пока нам не потребовалось что-то искать.
Искать что-либо в BLOB будет несколько странно, а значит одно из самых серьёзных преимуществ БД теряет смысл.
Кроме того лично очень осторожно отношусь к действиям, которые увеличивают размер БД. Больше размер — меньше производительность. Аватарки в БД для форума на 100 человек — ерунда. Коллекция фотографий каждого пользователя многомиллионной соцсети — неразумное раздутие архитектуры проекта.
БД должна управлять файлами и содержать на них ссылки (не будете же вы индексировать jpg или mp3 файлы по содержимому) а хранить в ФС и в результате запроса указывать откуда эти файлы взять вот и всё. А когда удаляется запись из БД, то удалять файл. Т.о. БД не раздувается, время программистов экономится, нагрузка на сервер снижается!
Можно использовать UDF (user defined function), вызываемую из триггера, удаляющую или обновляющую файл (локально или на удалённом сервере) при изменении записей в таблице. Это возможно.
Спасибо! Не знал о наличии такой функцинальности у триггеров, это сильно упрощает все!
Надеюсь вы отдаете отчет в том насколько это небезопасно… пропущенная SQL-инъекция сможет прибить не только вашу БД, но и файлы.
Если допускать возможность SQL инъекции, то хранить данные в БД тоже очень небезопасно выходит… Инъекция может прибить данные в таблице (связь имен файлов с статьями например) и тогда хоть файлы и будут нетронутыми, они будут бесполезные, так как удалены данные, с которыми они связаны.
Ну БД можно и нужно бэкапить → можно восстановить, как правило на больших ресурсах эта задача гораздо легче выполнима, чем бэкап файлов (которых могут быть терабайты, при базе в десятки-сотни мегабайт)
Да вы правы, это может быть дополнительной дырой в системе, но ее ведь можно закрыть как на уровне ОС (MySQL сервер выполянется под непривелигированым пользователем) так и средствами MySQL — забрать у пользователя привилегию Execute.
Правильнее дыр все таки не создавать.
Может я не уловил, но вроде как высказана идея, привязать триггер с UDF к удалению/обновлению записи, после чего UDF сделает exec и прибьет соответствующий записи файл? Если я таки понял правильно, то причем тут ОС, пользователи и права?
Приходит нехороший парень, инджектит вам запрос типа DELETE * FROM… после чего SQL-сервер, совершенно законно, от своего имени, с данными ему вами правами — прибивает все ассоциированные с записями ресурсы.
Понятно, что кое-какие меры можно предусмотреть, но моя консервативная точка зрения подсказывает мне, что лучше не создавать себе дополнительных проблем без надобности, а надобности я если честно пока не увидел.
Может я не уловил, но вроде как высказана идея, привязать триггер с UDF к удалению/обновлению записи, после чего UDF сделает exec и прибьет соответствующий записи файл? Если я таки понял правильно, то причем тут ОС, пользователи и права?
Приходит нехороший парень, инджектит вам запрос типа DELETE * FROM… после чего SQL-сервер, совершенно законно, от своего имени, с данными ему вами правами — прибивает все ассоциированные с записями ресурсы.
Понятно, что кое-какие меры можно предусмотреть, но моя консервативная точка зрения подсказывает мне, что лучше не создавать себе дополнительных проблем без надобности, а надобности я если честно пока не увидел.
Эм. Вы точно знаете как работают триггеры и хранимые процедуры? :) Туда можно запередаваться SQL-иньекцию в параметрах. Она не будет работать. Просто потому что в отличии от обычных запросов, что триггеры, что хранимые процедуры компилируются один раз. При создании. Далее никаких изменений не происходит не взирая на то, что вы там передали.
Если нехороший человек найдет способ сделать delete from, то дальше за него все сделает сам сервер, никаких параметров никуда передавать не надо будет. Опять же если я правильно понял то, для чего предлагается использовать UDF.
Как работают триггеры я знаю :)
Как работают триггеры я знаю :)
Ну давайте расскажите мне как из хранимки или триггера вызывать delete from если их там нет. Тоже самое можно сказать про UDF. Ни то ни то не является динамически компилируемым в отличии от обычных запросов.
Блин, может я торможу конечно… или вы, не знаю :)
злоумышленник делает простой зпрос DELETE FROM users WHERE 1=1
на удаление записи висит триггер, в котором происходит вызов функции с передачей ей OLD.id или там OLD.file_path или по какой то другой логике заложенной в триггере (выборка из соседней таблицы, обработка ее результатов в цикле). Как раз с UDF я дела не имел, каким образом там происходит exex(), последствием которого будет удаление файла я не знаю — я лишь верю исходному комментарию в этой ветке, что это возможно. Значит результатом будет удаление всех файлов ассоциированных с каждой записью в таблице users. Штатным, заложенным в систему способом
не? если я не правильно понял что-то — прошу мне объяснить тогда, что написано в первом каменте в этой ветке.
злоумышленник делает простой зпрос DELETE FROM users WHERE 1=1
на удаление записи висит триггер, в котором происходит вызов функции с передачей ей OLD.id или там OLD.file_path или по какой то другой логике заложенной в триггере (выборка из соседней таблицы, обработка ее результатов в цикле). Как раз с UDF я дела не имел, каким образом там происходит exex(), последствием которого будет удаление файла я не знаю — я лишь верю исходному комментарию в этой ветке, что это возможно. Значит результатом будет удаление всех файлов ассоциированных с каждой записью в таблице users. Штатным, заложенным в систему способом
не? если я не правильно понял что-то — прошу мне объяснить тогда, что написано в первом каменте в этой ветке.
А теперь давайте представим, что злоумышленник передает SQL иньекцию в строке, которая не вклеивается в обычный SQL запрос, а передается в хранимую процедуру. Т.е запрос будет иметь вид UPDATE_USERS(«DELETE FROM users WHERE 1=1;»,2); к примеру. Как видим такой запрос не воспримется как SQL-запрос. Как результат, она не будет выполнена. Я про это. А про то что вы рассказываете никакого отношения к собственно опасности триггеров, как сами понимаете не имеет и является возможным дополнением к механизмам целостности СУБД.
Ммм… а я где то писал про опасность использования триггеров?
Я лишь сказал, что небезопасно вешать удаление файлов на удаление записи в БД ибо в случае ошибки в скриптах, злоумышленник сможет грохнуть не только записи в таблицах БД (которую можно бэкапить), но и всё файло с ней связанное. По моему вы изначально меня неправильно поняли.
Я лишь сказал, что небезопасно вешать удаление файлов на удаление записи в БД ибо в случае ошибки в скриптах, злоумышленник сможет грохнуть не только записи в таблицах БД (которую можно бэкапить), но и всё файло с ней связанное. По моему вы изначально меня неправильно поняли.
Первоначальный диалог
viperet:
Спасибо! Не знал о наличии такой функцинальности у триггеров, это сильно упрощает все!
maxshopen:
Надеюсь вы отдаете отчет в том насколько это небезопасно… пропущенная SQL-инъекция сможет прибить не только вашу БД, но и файлы.
Первый автор говорит про триггеры вы говорите что это не безопасно. Вот и все.
viperet:
Спасибо! Не знал о наличии такой функцинальности у триггеров, это сильно упрощает все!
maxshopen:
Надеюсь вы отдаете отчет в том насколько это небезопасно… пропущенная SQL-инъекция сможет прибить не только вашу БД, но и файлы.
Первый автор говорит про триггеры вы говорите что это не безопасно. Вот и все.
Первый автор говорит не это, а вот это:
вся ветка — обсуждение данной возможности, с учетом собственно темы топика. Вы же зацепились не понятно зачем, конкретно за триггеры, и продолжаете упорствовать :)
Можно использовать UDF (user defined function), вызываемую из триггера, удаляющую или обновляющую файл (локально или на удалённом сервере) при изменении записей в таблице. Это возможно.
вся ветка — обсуждение данной возможности, с учетом собственно темы топика. Вы же зацепились не понятно зачем, конкретно за триггеры, и продолжаете упорствовать :)
ну если у тебя параметр на входе процедуры строка, и ты ее подставляешь в свой запрос внутри, то вот такой вот запрос тебя может повалить:
процедура(param1,param2){
select * from masterdb where id like '%param1%'
}
а значение параметра сделаем вот таким param1= 'union drop table abvgd; '
сорри мог где нить с синтаксисом напутать, но смысл должен быть понятен.
процедура(param1,param2){
select * from masterdb where id like '%param1%'
}
а значение параметра сделаем вот таким param1= 'union drop table abvgd; '
сорри мог где нить с синтаксисом напутать, но смысл должен быть понятен.
Угроз безопасности, исходящих от SQL-инъекций, при использовании UDF ровно столько же, сколько и без них. Если грамотно организовано поддержание целостности данных, и строки таблицы, и файлы (в каком бы виде они ни хранились) должны удалиться, если выполняется соответствующий DELETE FROM. Борьба с SQL-инъекциями должна выполняться до передачи SQL-запроса СУБД.
Не совсем так. Отправка SQL-иньекции в UDF или хранимую процедуру ни к чему летальному не приведет.
Есть некий демон. Он раз в n делает SELECT из базы на на получение списка «файлов на удаление». Далее удаляя файл делает UPDATE базы, добавляя флаг, что файл был удален. По окончаню просто удаляются записи с этим лагом, или переносяться в историю…
так делал я… кажется логичным )
так делал я… кажется логичным )
Как мне представляется, в пятом пункте вы решили излишне усложнить задачу. При определенных ограничениях и допущениях, например, файл в БД не может быть изменен ранее некоторого времени. Файл из БД можно отдать скриптом, а Nginx фронтенд кэширует его на необходимое время.
Думаю можно придумать механизм форсированного обновления кэша Nginx при изменении записи в БД, но в этом моменте нужно разобраться…
Думаю можно придумать механизм форсированного обновления кэша Nginx при изменении записи в БД, но в этом моменте нужно разобраться…
Вот какое решение в своих проектах используем мы (правда не MySQL а Oracle у нас, но это значения не имеет в данной ситуации).
Хранить файлы в ФС действительно неудобно. У нас есть специальная папочка temp. При обращении к файлу приложение пишет его в temp с именем {имя_таблицы}_{id_файла}.{расширение} или {имя_таблицы}_{имя_файла_из_таблицы} (для второго случая на поле с именем файла необходимо наложить unique). После этого, при каждом обращении к файлу, функция проверяет, существует ли данный файл? Если существует — просто отдает его имя, если нет — записывает по приведенному выше алгоритму.
Сама папочка temp чистится раз в сутки, причем не удаляются файлы созданные в последний час. На всякий случай, в друг их кто то еще использует. Файлы которые созданны раньше наврятли кто-то будет использовать.
Вот собственно и все. Одновременно мы боремся и с висящими файлами (я думаю если файл проживет несколько лишних часов это не особо принципиально), и с повышенной нагрузкой связанной с регулярной перезаписью файлов.
Безусловно, подходит не для всего, но нам такого хватает. Если есть более повышенные требования к файлам думаю данный метод не подойдет. Но проблемы заявленные в топике решает.
Хранить файлы в ФС действительно неудобно. У нас есть специальная папочка temp. При обращении к файлу приложение пишет его в temp с именем {имя_таблицы}_{id_файла}.{расширение} или {имя_таблицы}_{имя_файла_из_таблицы} (для второго случая на поле с именем файла необходимо наложить unique). После этого, при каждом обращении к файлу, функция проверяет, существует ли данный файл? Если существует — просто отдает его имя, если нет — записывает по приведенному выше алгоритму.
Сама папочка temp чистится раз в сутки, причем не удаляются файлы созданные в последний час. На всякий случай, в друг их кто то еще использует. Файлы которые созданны раньше наврятли кто-то будет использовать.
Вот собственно и все. Одновременно мы боремся и с висящими файлами (я думаю если файл проживет несколько лишних часов это не особо принципиально), и с повышенной нагрузкой связанной с регулярной перезаписью файлов.
Безусловно, подходит не для всего, но нам такого хватает. Если есть более повышенные требования к файлам думаю данный метод не подойдет. Но проблемы заявленные в топике решает.
файл в бд — это как минимум запрос к backend, в случае когда можно обойтись статикой.
ps. Интересно было бы посмотреть на сравнение быстродействия
ps. Интересно было бы посмотреть на сравнение быстродействия
микроскопом гвозди забиваете.
какие именно файлы там хранить?
вот у меня допустим 300 дистрибутивов по 30-80 мегабайт.
обновляются они раз в неделю. Сейчас они заливаются по FTP. потоком, все сразу.
как это через MySQL реализовать? через php механизмы?
зачем усложнять если эффективности от этого нет.
какие именно файлы там хранить?
вот у меня допустим 300 дистрибутивов по 30-80 мегабайт.
обновляются они раз в неделю. Сейчас они заливаются по FTP. потоком, все сразу.
как это через MySQL реализовать? через php механизмы?
зачем усложнять если эффективности от этого нет.
Считаю, что однозначно файл не должен быть в БД. Файл в БД — это лишний запрос к бекенду, потом лиший коннект к БД-серверу, который сам по себе не бесплатен, а если БД-сервер находится на отдельном сервере, то коннект будет еще более дорогим. Причем сам запрос файла будет подразумеват парсинг запроса мускулем и передача результата через сокеты. Далее, бекапить БД с гигобайтными файлами будет, наверное, сложнее.
Мое мнение — если можно обойтись без обращения к БД (использовать expires, memcached и т.п.), то нужно обходиться.
Мое мнение — если можно обойтись без обращения к БД (использовать expires, memcached и т.п.), то нужно обходиться.
на вот этой www.blobstreaming.org/ странице был найден вот этот www.blobstreaming.org/ storage-engine для mysql, который как раз и можно использовать для хранения и раздачи файлов.
кстати, там же есть RitmarkFS — позволяет использовать MySQL для манипуляций с файловой системой
извините, первая ссылка неправильная, должно быть dev.mysql.com/doc/refman/5.1/en/storage-engines-other.html
Оужас, этоже BLOB!
Как страшно жить…
Как страшно жить…
Где-то в мануале mysql натыкался на совет: не используйте BLOB для хранения файлов :)
Мне кажется, вы решаете разные задачи: «правильно» хранить файлы(картинки) и быстро отдавать.
Правильно хранить в вашем случае, чтобы не было проблем с целостностью, это в БД.
Быстро отдавать — прокси в режиме кеширования, например, Squid, ну и есть много других вариантов. Это конечно, если у вас большая нагрузка.(Иначе тут вообще нет проблемы)
Научите сквид кешировать на диск, если памяти в серверах мало :)
Да тут, конечно, есть проблема со временем обновления кеша. Но тут можно эксперементировать с заголовками, например отдавать из базы(приложения) не картинку, а только признак менялась она или нет, на основании заголовка прокси либо перезапросит ее, либо отдаст из кеша.
P.S. Могу соврать, но вроде бы фликр большинство популярных картинок держит в кеширующих проксях причем в памяти.
Правильно хранить в вашем случае, чтобы не было проблем с целостностью, это в БД.
Быстро отдавать — прокси в режиме кеширования, например, Squid, ну и есть много других вариантов. Это конечно, если у вас большая нагрузка.(Иначе тут вообще нет проблемы)
Научите сквид кешировать на диск, если памяти в серверах мало :)
Да тут, конечно, есть проблема со временем обновления кеша. Но тут можно эксперементировать с заголовками, например отдавать из базы(приложения) не картинку, а только признак менялась она или нет, на основании заголовка прокси либо перезапросит ее, либо отдаст из кеша.
P.S. Могу соврать, но вроде бы фликр большинство популярных картинок держит в кеширующих проксях причем в памяти.
не совсем в тему, а в качестве факультативного материала ;)
в SQL Server 2008 реализована технология Filestream как раз для решения подобных задача.
msdn.microsoft.com/en-us/library/cc949109.aspx
думаю, что скоро и в mysql сделают нечто подобное.
в SQL Server 2008 реализована технология Filestream как раз для решения подобных задача.
msdn.microsoft.com/en-us/library/cc949109.aspx
думаю, что скоро и в mysql сделают нечто подобное.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Хранение файлов в MySQL и их быстрая раздача