PostgreSQL к Хранилищу данных: Лучший подход в течение псевдореального времени ETL / извлечение данных
Фон:
У меня есть PostgreSQL (v8.3) база данных, которая в большой степени оптимизирована для OLTP.
Я должен извлечь данные из него на полу основе в реальном времени (кто-то обязан спросить, чем полу средства в реальном времени и ответ состоят так часто в том, как я обоснованно могу, но я буду прагматически настроен, поскольку сравнительный тест позволяет, говорят, что мы надеемся в течение каждых 15 минут), и подайте его в хранилище данных.
Сколько данных? В пиковое время мы говорим приблизительно 80-100k строки в минуту, поражая сторону OLTP, непиковую, это значительно отбросит к 15-20k. Наиболее часто обновляемые строки составляют ~64 байта каждый, но существуют различные таблицы и т.д., таким образом, данные довольно разнообразны и могут расположиться до 4 000 байтов за строку. OLTP активен 24x5.5.
Лучшее решение?
Из того, что я могу соединить наиболее практическое решение, следующие:
- Создайте ТРИГГЕР для записи всего действия DML во вращающийся файл журнала CSV
- Выполните любые преобразования требуются
- Используйте собственный инструмент объекта преобразования данных DW для эффективного нагнетания преобразованного CSV в DW
Почему этот подход?
- ТРИГГЕРЫ позволяют выборочным таблицам быть предназначенными вместо того, чтобы быть в масштабе всей системы +, вывод настраивается (т.е. в CSV) и относительно легок записать и развернуться. SLONY использует аналогичный подход и наверху приемлема
- CSV, легкий и быстрый для преобразования
- Легкий накачать CSV в DW
Alternatives рассматривают....
- Используя собственный вход (http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html). Проблема с этим, это выглядело очень подробным относительно того, в чем я нуждался и был немного более хитрым, чтобы проанализировать и преобразовать. Однако это могло быть быстрее, поскольку я предполагаю, что существует меньше служебное по сравнению с ТРИГГЕРОМ. Конечно, это сделало бы администратора легче, поскольку это в масштабе всей системы, но снова, мне не нужны некоторые таблицы (некоторые используются для персистентного устройства хранения данных сообщений JMS, которые я не хочу регистрировать),
- При запросах данных непосредственно через инструмент ETL, такой как Talend и нагнетании его в DW... проблемой является схема OLTP, нуждался бы, настроил для поддержки этого, и это имеет много отрицательных побочных эффектов
- Используя настраивание/взламывание SLONY - SLONY делает хорошее задание входа и миграции изменений в ведомом устройстве, таким образом, концептуальная основа там, но предлагаемое решение просто кажется легче и более чистым
- Использование WAL
Кто-либо сделал это прежде? Хотите совместно использовать свои мысли?
2 ответа
Предполагая, что интересующие вас таблицы имеют (или могут быть дополнены) уникальный, индексированный, последовательный ключ, тогда вы получите гораздо лучшую ценность просто введите SELECT ... FROM table ...WHERE key>: last_max_key с выводом в файл, где last_max_key - это последнее значение ключа из последнего извлечения (0 в случае первого извлечения). Этот инкрементный, развязанный подход позволяет избежать вводится задержка запуска в пути данных вставки (будь то настраиваемые триггеры или модифицированный Slony), и, в зависимости от вашей настройки, может лучше масштабироваться с количеством процессоров и т. д. (Однако, если вам также необходимо отслеживать UPDATE s , и последовательный ключ был добавлен вами, тогда ваши операторы UPDATE должны SET ключевой столбец на NULL , поэтому он получает новое значение и выбирается при следующем извлечении. Вы не сможете отслеживать DELETE s без триггера.) Это то, что вы имели в виду, когда упомянули Talend ?
Я бы не использовал средство ведения журнала, если вы не можете реализовать решение, описанное выше ; ведение журнала, скорее всего, включает в себя накладные расходы на блокировку , чтобы гарантировать, что строки журнала записываются последовательно и не перекрывают / не перезаписывают друг друга, когда несколько бэкэндов записывают в журнал (проверьте исходный код Postgres). Накладные расходы на блокировку могут быть не катастрофическими, но вы можете обойтись без него, если можете использовать инкрементную альтернативу SELECT . Более того, запись в журнал операторов заглушит любые полезные сообщения WARNING или ERROR, а сам синтаксический анализ не будет мгновенным .
Если вы не хотите анализировать WAL (включая отслеживание состояния транзакции и быть готовым переписывать код каждый раз, когда вы обновляете Postgres), я не обязательно буду использовать WAL - то есть, если у вас нет дополнительного оборудования доступно , и в этом случае вы можете отправить WAL на другую машину для извлечения (на второй машине вы можете беззастенчиво использовать триггеры - или даже регистрацию операторов - поскольку что бы ни случилось это не влияет на производительность INSERT / UPDATE / DELETE на основной машине.) Обратите внимание, что с точки зрения производительности (на основной машине), если вы не можете писать журналов в SAN, вы получите сопоставимое снижение производительности (в основном с точки зрения перегрузки кеша файловой системы) от отправки WAL на другую машину, как от выполнения инкрементного SELECT .
если вы можете представить себе «таблицу контрольных сумм», которая содержит только идентификаторы и «контрольную сумму», вы можете не только быстро выбрать новые записи но также измененные и удаленные записи.
контрольной суммой может быть функция контрольной суммы crc32, которая вам нравится.