vmalyutin Oct 21 2019 at 15:21

Вокруг Света за 4 Секунды на Columnstore (Часть 1)

18 min

7.8K

В этой статье я собираюсь рассмотреть вопрос повышения скорости отчетов. Под отчетом я понимаю любой запрос в базу данных, который использует агрегирующие функции. Также, я собираюсь затронуть вопросы, касающиеся затрачиваемых ресурсов на производство и поддержку отчетов, как людские, так и машинные.

В примерах я буду использовать набор данных, содержащий 52 608 000 записей.

На примере не сложных аналитических запасов я продемонстрирую, что даже слабый компьютер можно превратить в неплохое средство анализа «приличного» объема данных без особых усилий.

Поставив не сложные эксперименты, мы увидим, что обычная таблица не является подходящим источником для аналитических запросов.

Если читатель без труда может расшифровать аббревиатуры OLTP и OLAP, возможно есть смысл перейти сразу к разделу Columnstore

Два подхода к работе с данными

Здесь я буду краток, т.к. информации по этой теме в интернете более, чем достаточно.

Итак, на самом верхнем уровне существует всего два подхода к работе с данными: OLTP и OLAP.

OLTP — можно перевести, как моментальная обработка транзакций. На самом деле, речь идёт об онлайн обработке коротких транзакций, работающих с небольшим объёмом данных. Например, запись, обновление или удаление заказа. В подавляющем большинстве случаев заказ — это крайне малый объём данных, при обработке которого можно не бояться длительных блокировок, накладываемых современными РСУБД.

OLAP — можно перевести как аналитическая обработка большого количества транзакций за один раз. Любой отчет использует именно этот подход, ведь в подавляющем большинстве случаев отчет выдаёт сводные, агрегированные цифры по определённым разрезам.

Для каждого из подходов существуют свои технологии. Например, для OLTP — это PostgreSQL, а для OLAP — это Microsoft SQL Server Analysis Services. В то время как PostgresSQL использует широко известный формат хранения данных в таблицах, для OLAP придумали несколько разных форматов. Это многомерные таблицы, бакеты наполненные парами ключ-значение и любимый мною columnstore. О последнем более подробно ниже.

Зачем нужны именно два подхода?

Было замечено, что любое хранилище данных рано или поздно сталкивается с двумя типами нагрузки: частое чтение (запись и обновление, конечно, тоже) крайне малых объёмов данных и редкое чтение, но очень больших объёмов данных. На деле это активность, например, кассы и руководителя. Касса, работая целый день, наполняет хранилище мелкими порциями данных, при этом в конце дня объём накопленного, если бизнес идёт хорошо, достигает внушительных размеров. В свою очередь руководитель в конце дня желает знать, сколько денег заработала касса за день.

Итак, в OLTP мы имеем таблицы и индексы. Эти два инструмента прекрасно подходят для записи активности кассы со всеми деталями. Индексы обеспечивают быстрый поиск записанного ранее заказа, поэтому изменить заказ не составляет труда. Но для, того, чтобы удовлетворить потребности руководителя, нам необходимо считать весь объём накопленных за день данных. К тому же, как правило, руководителю не нужны все детали всех заказов. Что ему действительно нужно знать это то, сколько денег заработала касса в общем. При этом не важно, где касса стояла, когда был перерыв на обед, кто за ней работал, и т.д. OLAP существует затем, чтобы за короткий временной отрезок система могла ответить на вопрос — сколько в целом заработало предприятие без последовательного чтения каждого заказа и всех его деталей. Может ли OLAP использовать те же таблицы и индексы, что и OLTP? Ответ нет, по крайней мере, не должна. Во-первых, потому что OLAP просто не нужны все детали, записанные в таблицах. Эта проблема решается путем хранения данных в иных форматах, отличных от двумерных таблиц. Во-вторых, анализируемая информация, часто, рассредоточена по разным таблицам, что влечет за собой множественные их объединения включая объединения типа self-join. Для решения этой проблемы, как правило, разрабатывают специальную схему БД. Эта схема получается оптимизированной для OLAP нагрузки, так же хорошо, как обычная нормализованная схема для OLTP нагрузки.

Что происходит, когда OLAP использует OLTP схему

На самом деле, я ввёл этот раздел, чтобы эта статья четко удовлетворяла моим собственным требованиям к формату подобного материала, т.е. проблема, решение, заключение.

Перечислим ряд недостатков использования OLTP схемы в целях анализа данных.

Слишком много индексов.

Нередко для поддержки отчетов приходится делать специальные индексы. Такие индексы реализуют схему хранения данных OLAP. Они не используются OLTP частью приложения, при этом оказывают нагрузку именно на неё, требуя постоянной поддержки и занимая место на диске.
Объём считываемых данных превышает необходимый.
Отсутствие четкой схемы данных.

Дело в том, что часто информация, подаваемая отчетами в едином виде разнесена по разным таблицам. Такая информация требует постоянной трансформации на лету. Простейший пример — это сумма выручки, которая складывается из наличных и безналичных денег. Еще один яркий пример — это иерархии данных. Т.к. развитие приложения происходит поступательно и не всегда известно, что потребуется в будущем, одна и та же по смыслу иерархия может храниться в разных таблицах. И хотя получение схемы налету активно применяется в OLAP это немного разные вещи.
Чрезмерная сложность запросов.

Т.к. OLTP схема отличается от OLAP необходим сильно связанный программный слой, который приводит схему данных OLTP к нужному виду.
Сложность поддержки, отладки и развития.

В целом можно сказать, что чем сложнее кодовая база, тем сложнее её поддерживать в работоспособном состоянии. Это аксиома.
Сложность покрытия тестами.

Много копий сломано на рассуждениях на тему, как получить базу данных, наполненную всеми тестовыми сценариями, но лучше сказать, что имея более простую схему данных задача покрытия тестами упрощается многократно.
Бесконечная отладка производительности.

Велика вероятность того, что пользователь закажет «неподъёмный» для сервера БД отчет. Эта вероятность растет с течением времени. Необходимо заметить, что и OLAP подвержена этой проблеме, но в отличии от OLTP, ресурс OLAP в этом вопросе гораздо выше.

Columnstore

В этой статье речь пойдет о формате хранения columnstore, но без низкоуровневых деталей. Другие форматы, упоминавшиеся выше, тоже заслуживают внимания, но это тема для другой статьи.

На самом деле формат columnstore известен уже лет 30. Но в РСУБД он не был реализован до недавнего времени. Суть columnstore, в том, что хранение данных осуществляется не в строках, но в колонках. Т.е. на одну страницу (всем известные 8 Кб) сервер записывает данные только одного поля. И так с каждым полем в таблице по очереди. Необходимо это для того, чтобы не приходилось считывать лишнюю информацию. Давайте представим таблицу с 10 полями и запрос, у которого в операторе SELECT указано только одно поле. Если бы это была обычная таблица сохранённая в row-based формате, сервер был бы вынужден считывать все 10 полей, но при этом возвращать только одно. Получилось бы, что сервер считал в 9 раз больше информации, чем было необходимо. Columnstore полностью решает эту проблему, т.к. формат хранения позволяет считывать только одно заказанное поле. Все это происходит, потому что единица хранения в РСУБД — это страница. Т.е. сервер всегда записывает и считывает, как минимум одну страницу. Вопрос только в том, сколько полей присутствует на ней.

Чем реально Columnstore может помочь

Для ответа на этот необходимо иметь точные цифры. Давайте же получим их. Но какие цифры могут дать точную картину?

Объём дискового пространства.
Производительность запросов.
Отказоустойчивость.
Простота внедрения.
Какие новые навыки должны быть у разработчика для работы с новыми структурами.

Объём дискового пространства

Давайте создадим простую таблицу, наполним её данными и проверим сколько места она займет.

create foreign table cstore_table 
( 
  trd date, 
  org int, 
  op int, 
  it int, 
  wh int, 
  m1 numeric(32, 2), 
  m2 numeric(32, 2), 
  m3 numeric(32, 2), 
  m4 numeric(32, 2), 
  m5 numeric(32, 2) 
) 
server cstore_server 
options(compression 'pglz');

Как вы заметили, я создал внешнюю таблицу. Дело в том, что PostgreSQL не имеет встроенной поддержки columnstore. Зато PostgreSQL имеет мощную систему для расширений. Одно из них как раз и даёт возможность создавать columnstore таблицы. Ссылки в конце статьи.

pglz — сообщает расширению, что данные должны быть сжаты встроенным в PostgreSQL алгоритмом;
trd — время транзакции;
op, it, wh — аналитические разрезы или измерения;
m1, m2, m3, m4, m5 — числовые показатели или меры;

Давайте вставим «приличный» объём данных и посмотрим, сколько места он займёт на диске. Заодно проверим производительность вставки. Т.к. свои эксперименты я ставлю на домашнем ноутбуке, я слегка органичен в объёмах данных. К тому же, что даже хорошо, я буду использовать HDD под управлением гостевой OS Fedora 30. Хост OS — Windows 10 Home Edition. Процессор Intel Core 7. Гостевая OS получила 4 Гб RAM. Версия PostgreSQL — PostgreSQL 10.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64-bit. Экспериментировать буду с набором данных с количеством записей 52 608 000.

explain (analyze) 
insert into cstore_table 
select  
  '2010-01-01'::date + make_interval(days => d) as trd 
  , op 
  , org 
  , wh 
  , it 
  , 100 as m1 
  , 100 as m2 
  , 100 as m3 
  , 100 as m4 
  , 100 as m5 
from generate_series(0, 1) as op 
cross join generate_series(1, 2) as org 
cross join generate_series(1, 3) as wh 
cross join generate_series(1, 4000) as it 
cross join generate_series(0, 1095) as d;