Время обработки данных в одном знакомом мне проекте энтерпрайз-хранилища данных с реляционной моделью составляет почти 2,5 часа. Много это или мало?
Заметка описывает эксперимент по созданию маленькой копии энтерпрайз-хранилища данных с сильно ограниченными техническими условиями. А именно, на базе одноплатного компьютера Raspberry Pi.
Модель и архитектура будут упрощёнными, но похожими на энтерпрайз-хранилище. Результатом является оценка возможности использования Raspberry Pi в области обработки и анализа данных.
Роль опытного и сильного игрока будет выполнять машина Exadata Х5 (один юнит) корпорации Оракл.
Процесс обработки данных включает в себя следующие шаги:
Итого, интеграция 350 миллионов записей за 2,5 часа, что эквивалентно 2,3 миллионам записей в минуту или примерно 39 тысячам записей исходных данных в секунду.
В роли экспериментального оппонента будет выступать Raspberry Pi 3 Model B+ с 4х-ядерным процессором 1.4 ГГц.
В качестве хранилища используется sqlite3, чтение файлов происходит с помощью PHP. Файлы и база данных находятся на SD карте размером 32 ГБ класса 10 во строенном ридере. Резервная копия создаётся на флэш диске 64 ГБ, подключённом к USB.
Модель данных в реляционной базе sqlite3 описана в статье о маленьком хранилище.
Исходный файл access.log — 37 МБ с 200 тысячами записей.
Итого, интеграция 200 тысяч записей заняла почти 7 минут, что эквивалентно 28 тысячам записей в минуту или 470 записям исходных данных в секунду. База данных занимает 7,5 МБ; всего 8 SQL запросов для обработки данных.
Файл более активного сайта. Исходный файл access.log — 67 МБ с 290 тысячами записей.
Итого, интеграция 290 тысяч записей заняла чуть больше 12 минут, что эквивалентно 23 тысячам записей в минуту или 380 записям исходных данных в секунду. База данных занимает 22,9 МБ
Для получения данных в виде модели, которая позволит проводить эффективный анализ, необходимы значительные вычислительные и материальные ресурсы, и время в любом случае.
Например, один юнит Экзадаты обходится более чем в 100К. Один Raspberry Pi стоит 60 единиц.
Линейно их нельзя сравнивать, т.к. с увеличением объёмов данных и требований надёжности возникают сложности.
Однако, если представить себе случай, когда тысяча Raspberry Pi работают параллельно, то, исходя из эксперимента, они обработают около 400 тысяч записей исходных данных в секунду.
И если решение для Экзадаты оптимировать до 60 тысяч записей в секунду, то это ощутимо меньше, чем 400 тысяч. Это подтверждает внутреннее ощущение того, что цены энтерпрайз-решений завышены.
В любом случае, Raspberry Pi отлично справлятся с обработкой данных и реляционными моделями соответствующего масштаба.
Домашний Raspberry Pi был настроен как веб сервер. Эксперимент с его производительностью можно провести самостоятельно по адресу. Модель базы данных (DDL), процедуры загрузки (ETL) и саму базу данных можно там же скачать.
Изменения
Благодаря комментариям, ошибка в загрузке файла Exadata поправлена и цифры в заметке исправлены. Для чтения используется sqlloader, какой-то жук удалил параметры BINDSIZE и ROWS. По причине неустойчивой загрузки с удалённого накопителя выбран способ conventional вместо direct path, который мог бы увеличить скорость ещё на 30-50%.
Заметка описывает эксперимент по созданию маленькой копии энтерпрайз-хранилища данных с сильно ограниченными техническими условиями. А именно, на базе одноплатного компьютера Raspberry Pi.
Модель и архитектура будут упрощёнными, но похожими на энтерпрайз-хранилище. Результатом является оценка возможности использования Raspberry Pi в области обработки и анализа данных.
№1
Роль опытного и сильного игрока будет выполнять машина Exadata Х5 (один юнит) корпорации Оракл.
Процесс обработки данных включает в себя следующие шаги:
- Чтение из файла 10,3 ГБ — 350 миллионов записей за 90 минут.
- Обработка и очистка данных — 2 SQL запроса и 15 минут (с шифрованием персональных данных 180 минут).
- Загрузка измерений — 10 минут.
- Загрузка таблиц фактов 20 миллионами новых записей — 5 SQL запросов и 35 минут.
Итого, интеграция 350 миллионов записей за 2,5 часа, что эквивалентно 2,3 миллионам записей в минуту или примерно 39 тысячам записей исходных данных в секунду.
№2
В роли экспериментального оппонента будет выступать Raspberry Pi 3 Model B+ с 4х-ядерным процессором 1.4 ГГц.
В качестве хранилища используется sqlite3, чтение файлов происходит с помощью PHP. Файлы и база данных находятся на SD карте размером 32 ГБ класса 10 во строенном ридере. Резервная копия создаётся на флэш диске 64 ГБ, подключённом к USB.
Модель данных в реляционной базе sqlite3 описана в статье о маленьком хранилище.
Тест первый
Исходный файл access.log — 37 МБ с 200 тысячами записей.
- Прочитать лог и записать в базу данных заняло 340 секунд.
- Загрузка измерений с 5 тысячами записей длилась 5 секунд.
- Загрузка таблиц фактов 90 тысячами новых записей — 32 секунды.
Итого, интеграция 200 тысяч записей заняла почти 7 минут, что эквивалентно 28 тысячам записей в минуту или 470 записям исходных данных в секунду. База данных занимает 7,5 МБ; всего 8 SQL запросов для обработки данных.
Тест второй
Файл более активного сайта. Исходный файл access.log — 67 МБ с 290 тысячами записей.
- Прочитать лог и записать в базу данных заняло 670 секунд.
- Загрузка измерений с 25 тысячами записей длилась 8 секунд.
- Загрузка таблиц фактов 240 тысячами новых записей — 80 секунд.
Итого, интеграция 290 тысяч записей заняла чуть больше 12 минут, что эквивалентно 23 тысячам записей в минуту или 380 записям исходных данных в секунду. База данных занимает 22,9 МБ
Вывод
Для получения данных в виде модели, которая позволит проводить эффективный анализ, необходимы значительные вычислительные и материальные ресурсы, и время в любом случае.
Например, один юнит Экзадаты обходится более чем в 100К. Один Raspberry Pi стоит 60 единиц.
Линейно их нельзя сравнивать, т.к. с увеличением объёмов данных и требований надёжности возникают сложности.
Однако, если представить себе случай, когда тысяча Raspberry Pi работают параллельно, то, исходя из эксперимента, они обработают около 400 тысяч записей исходных данных в секунду.
И если решение для Экзадаты оптимировать до 60 тысяч записей в секунду, то это ощутимо меньше, чем 400 тысяч. Это подтверждает внутреннее ощущение того, что цены энтерпрайз-решений завышены.
В любом случае, Raspberry Pi отлично справлятся с обработкой данных и реляционными моделями соответствующего масштаба.
Ссылка
Домашний Raspberry Pi был настроен как веб сервер. Эксперимент с его производительностью можно провести самостоятельно по адресу. Модель базы данных (DDL), процедуры загрузки (ETL) и саму базу данных можно там же скачать.
Изменения
Благодаря комментариям, ошибка в загрузке файла Exadata поправлена и цифры в заметке исправлены. Для чтения используется sqlloader, какой-то жук удалил параметры BINDSIZE и ROWS. По причине неустойчивой загрузки с удалённого накопителя выбран способ conventional вместо direct path, который мог бы увеличить скорость ещё на 30-50%.
asmm
это крайне медленно, должно быть на порядок быстрее, возможно код у Вас где-то не так написан
asmm
Сейчас попробовал на коленке загрузить. На моём стареньком компе на HDD, файл 0.3Гб, 7млн строк, залетают в MySQL за ~137сек, т.е. около 50к строк в секунду
am-habr Автор
При написании SQL ориентируюсь на скорость 100K в секунду для одного запроса вставки в таблицу. Эксперимент описывает интеграцию данных. Из исходных данных загружаются измерения и агрегаты.
Общее время, необходимое для интеграции делится на количество исходных записей.
Метод загрузки файла выбран не самый быстрый, его можно оптимировать, но в 2 раза, а не на порядок.
Но и метод с PHP не самый быстрый, поэтому этот шаг можно считать эквивалентным.
asmm
загрузку файла можно оптимизировать на порядок, а то и на 2, PHP вообще не должен трогать и читать файл, только отправлять SQL запросы. Далее второй пункт «Обработка и очистка данных — 2 SQL запроса и 130 минут.» тож больно долго, какие-то видимо сильно не оптимальные запросы, от этого пункта можно вообще избавиться и всё реализовать одной загрузкой. А какая БД у вас на Экзадате? Оракл?
am-habr Автор
Oracle Database 18c. Ваши замечания и вопросы верные и логичные.
Для описания причин нужно писать статью, но такие плохо читаются и сильно минусуются энтерпрайзом, пот. быстро уходят в сторону «эффективных менеджеров» (уже поробовал и удалил про конфликт интересов).
Один аспект. Все операции тем эффективные, чем ближе к железу.
Например, метод внешних таблиц в Оракле будет очень быстрым.
Но нужно иметь расширенные права к диску и окружению. Часто даже к ОС, тут сложно становится с гарантийным обслуживанием.
Администратор БД, чел. по безопасности и аналитик будут испытывать конфликт интересов (одни не занимаются содержимым, другие могут что-нибудь сломать).
Но есть другой способ — sqlloader, гибкий и не сильно быстрый. Можно быстро, но должно лежать рядом с базой и тут снова конфликт.
Оптимизация и распределение знаний в проекте тоже являются причиной конфликта интересов. Тот, кто с паролем и всё может, будет концентрировать знания вокруг себя, что приведёт к проблеме в случае его отсутствия.
Быстро загрузить — не всегда эффективно.
Поэтому во втором случае PHP для файла, чтобы сохранить баланс :)
asmm
уж простите, но похоже с компетентностью туго в вашей организации, за те 6 часов которые обрабатываются 10Гб, можно заново написать загрузчик, для любой БД, загрузить эти данные и отказаться от Экзадаты, и недальновидного Администратора этой Экзадаты.
Если у вас организационно-бюрократические проблемы в организации, то в технической статье глупо писать что Exadata Х5 обрабатывает информацию со скоростью 500кб/сек
В целом для Оракла тут вообще изи и сверх быстро всё должно работать:
— CREATE TABLE access_log… ORGANIZATION EXTERNAL (… LOCATION ('access.log')) PARALLEL 12…
— MERGE INTO agregate_data SELECT… FROM access_log
притом если ещё лог файл подточить напильником для фикс формата и создать внешнюю таблицу как ORGANIZATION EXTERNAL (… RECORDS FIXED… ), то можно получить мгновенный доступ к любой записи используя
ORGANIZATION EXTERNAL (… ACCESS PARAMETERS (… RECORDS FIXED… SKIP N
даже если файл будет хоть 10Тб
am-habr Автор
Хотел бы спросить.
Почему все хотят видеть техническую статью, не привязанную к реальности?
Вы встречали организацию без организационно-бюрократических проблем?
Или где доступ к параметрам ОС, в которой стоит база, раздают всем желающим.
Есть ли информация о том, сколько у Вас в среднем времени проходит от external table до готового отчёта?
asmm
Странная у Вас реальность…
Всем желающим не надо, надо дать только разработчикам. Можно хоть докер развернуть, пусть контейнер как надо соберут и установят какие надо для работы параметры и ПО, я не понимаю какие тут вообще могут быть проблемы…
Типа как в анекдоте:
Какой отчёт? Из каких данных? Вопрос не совсем корректный. Я в своё время (~год назад) через EXTERNAL TABLE + INSERT APPEND делал загрузку телеметрических данных с GPS датчиков, около 15к в онлайне, данные обновлялись каждую секунду со сложной бизнес логикой с
денормализованными данными о скоростных режимах, расходах топлива, превышений и т.д.
Ваша задача:
— access.log загрузка 350млн записей 10Гб данных
— ELT преобразование
— агрегированный отчёт
мои предварительные оценки:
— пара-тройка дней на разработку (чисто SQL запросы)
— день на простенький вывод отчёта html
— ориентировочная финальная производительность на более менее нормальном железе ~ 15-60 мин на генерацию
FlyingDutchman
Не буду спорить, но я
бысделал так:1. sqlloader с опцией direct в оракловскую таблицу без всяких индексов, всё в одну транзакцию — это будет очень быстро, мы максимально быстро загнали данные из файла в БД с огромными кэшами и улучшенной возможностью применения бизнес логики.
2. insert /*+ APPEND */ select * из временной оракловской таблицы в другую оракловскую таблицу (пустую или не очень), в которой уже есть индексы и всё прочее. Можно даже в таблицу с партициями, тогда можно будет доступ к данным еще подтюнить.
3. Если фильтрация не очень сложная — то на правильно индексированной таблице вы довольно быстро почистите данные от шелухи.
Везде должно быть быстро. У меня в одном проекте еще был шаг 0 — perl'овый скрипт делал первоначальную очистку файлов от шелухи. Вот уж кого нельзя обвинить в медленности дисковых операций.