Управление объектами таблиц в Greenplum: хранилище, ориентированное на строки и столбцы
Колоночно-ориентированные базы данных становятся все более и более популярными в области систем онлайн аналитической обработки запросов (OLAP) в противовес традиционным базам данных, ориентированным на хранение строк.
Многие современные БД, предназначенные для хранения данных и аналитики, являются исключительно колоночно-ориентированными. Вместо того, чтобы слепо следовать современным тенденциям, Greenplum предоагает своим пользователям выбор модели хранения данных: строки, столбцы или даже их комбинация.
В этой статье мы поговорим о том, как правильно выбрать модель хранения данных, наиболее подходящую именно для Ваших потребностей.
Колоночно-ориентированная БД – это БД, которая хранит данные по столбцам, а не по строкам.
Как выбрать модель хранения данных в Greenplum?
В большинстве случаев хранение, ориентированное на строки, гарантирует привлекательное сочетание гибкости и производительности. Колоночно-ориентированное хранение предлагает более эффективные операции ввода/вывода, а также оптимальное хранение данных.
При выборе модели хранения в Greenplum рекомендую руководствоваться следующими соображениями:
Хранение, ориентированное на строки
- Обновление данных таблицы: если Вы часто обновляете данные, хранящиеся в таблице, выбирайте модель хранения, ориентированную на строки.
- LOAD/частые операции INSERT: для обеспечения эффективности операций LOAD и при частых вставках выбирайте строки, а не столбцы. Колоночно-ориентированные таблицы не оптимизированы для операций записи, поскольку значения столбцов должны быть зафиксированы в разных местах на диске.
Колоночно-ориентированное хранение
- Компрессия. Для данных, ориентированных на столбцы, доступны оптимизации размера хранилища, которые недоступны для данных, ориентированных на строки. Многие схемы сжатия используют сходство смежных данных для сжатия и достижения значительной экономии дискового пространства.
- Количество столбцов в запросе(ах): колоночно-ориентированные таблицы лучше всего подходят для запросов, агрегирующих несколько значений столбца, где присутствуют предикаты WHERE или HAVING. Например:
SELECT SUM(salary)…
или
SELECT AVG(salary)… WHERE salary > 10000
Или где предикат WHERE возвращает сравнительно небольшое количество строк. Например:
SELECT salary, dept … WHERE state=’CA’
- Количество столбцов в таблице: Колоночно-ориентированные таблицы могут обеспечить лучшую производительность, когда в запросах Вы обращаетесь к небольшому подмножеству столбцов. Хранилище, ориентированное на строки, более эффективно, когда одновременно требуется много столбцов или когда размер строки таблицы относительно мал.
Пример из реальной жизни: ссылка
На 4-узловой системе Greenplum (v6.6.0) на AWS мы протестировали две модели хранения:
- Таблицы, ориентированные на строки (без компрессии) (amzn-reviews-ro),
- Колоночно-ориентированная таблица (ZLib компрессия) (amzn-reviews-co).
Каждая модель хранения сравнивалась с другой на примере 6 различных сценариев использования
Массовая загрузка (~151 миллион строк)
- Загрузка amzn-reviews-ro завершилась за 544,98 секунды (или примерно 9 минут и 5 секунд), а
- Загрузка amzn-reviews-co завершилась за 552,29 секунды (или примерно 9 минут и 12 секунд).
Загрузка из другой таблицы (~151 миллион строк)
- Загрузка amzn-reviews-ro завершилась за ~216 секунд (или 3 минуты и 36 секунд), а
- Загрузка amzn-reviews-co завершилась за ~98 секунд (или 1 минуту и 38 секунд).
Размер таблицы и использование дискового пространства
- amzn-reviews-ro = ~80 Гб (без компрессии),
- amzn-reviews-co = ~74 Гб/32 Гб (до/после компрессии), или компрессия составила ~56% .
SELECT — 1x “короткое” поле данных
SELECT product_id
FROM demo.amzn_reviews_*** WHERE DATE_PART('year', review_date) BETWEEN 2000 AND 2005;- выполнен за 5845.564 мс для amzn-reviews-ro, и
- выполнен за 3350.621 мс для amzn-reviews-co.
SELECT — 1x “длинное” поле данных
SELECT review_body
FROM demo.amzn_reviews_*** WHERE DATE_PART('year', review_date) BETWEEN 2000 AND 2005;
- выполнен за 9244.800 мс для amzn-reviews-ro, и
- выполнен за 23396.014 мс для amzn-reviews-co.
SELECT — несколько столбцов
SELECT product_id,
marketplace, product_category, star_rating FROM demo.amzn_reviews_*** WHERE DATE_PART('year', review_date) BETWEEN 2000 AND 2005;- выполнен за 5717.630 мс для amzn-reviews-ro, и
- выполнен за 4548.015 мс для amzn-reviews-co.
SELECT — много столбцов
SELECT marketplace
, customer_id , product_id , product_title , product_parent , product_category , star_rating , helpful_votes , total_votes , vine , verified_purchase , review_headline, review_body FROM demo.amzn_reviews_*** WHERE DATE_PART('year', review_date) BETWEEN 2000 AND 2005;
- выполнен за 10359.897 мс для amzn-reviews-ro, и
- выполнен за 29237.993 мс для amzn-reviews-co.
Агрегатная/оконная функция над ограниченным количеством столбцов
SELECT COUNT(*) , product_category , star_rating FROM demo.amzn_reviews_heap GROUP BY 2, 3, 4;
- выполнен за 3989.040 мс для amzn-reviews-ro, и
- выполнен за 1835.179 мс для amzn-reviews-co.
Агрегатная/оконная функция над множеством столбцов
SELECT COUNT(*) , ROUND(AVG(helpful_votes)) AS helpful_votes_avg , ROUND(AVG(total_votes)) AS total_votes_avg , marketplace , product_category , star_rating , verified_purchase , CASE WHEN LENGTH(review_headline)<= 20 THEN 'Short Headline' ELSE 'Long Headline' END AS review_headline_length_type , CASE WHEN LENGTH(review_body)<= 400 THEN 'Short Body' ELSE 'Long Body' END AS review_body_length_type FROM demo.amzn_reviews_ro GROUP BY 4, 5, 6, 7, 8, 9;
- выполнен за 2780.567 мс для amzn-reviews-ro, и
- выполнен за 1890.663 мс для amzn-reviews-co.
Заключение
Для пользователей, которым нужен не просто универсальный вариант, а действительно эффективное решение, Greenplum предлагает несколько вариантов моделей хранения в соответствии с рабочей нагрузкой и случаем использования.
Модель хранения, ориентированная на столбцы, обеспечивает наиболее эффективный ввод-вывод и хранение данных и действительно хороша для небольших запросов SELECT. Модель хранения данных, ориентированную на строки, часто называют устаревшей, но она все еще актуальна для рабочих нагрузок общего назначения или смешанных нагрузок и предлагает отличное сочетание гибкости и производительности.