Русский
Русский
English
Статистика
Реклама

Администрирование баз данных

Как мы внедрили свою модель хранения данных highly Normalized hybrid Model. Доклад Яндекса

26.05.2021 12:10:08 | Автор: admin
Общепринятый и проверенный временем подход к построению Data Warehouse (DWH) это схема Звезда или Снежинка. Такой подход каноничен, фундаментален, вотрфоллен и совсем не отвечает той гибкости, к которой призывает Agile. Чтобы сделать структуру DWH гибкой, существуют современные подходы к проектированию: Data Vault и Anchor modeling похожие и разные одновременно. Задавшись вопросом, какую из двух методологий выбрать, мы в Яндекс Go пришли к неожиданному ответу: выбирать надо не между подходами, а лучшее из двух подходов.

Темы доклада, который вместе со мной прочитал Николай Гребенщиков:
DV и AM: в чем разница и где точки соприкосновения
Гибридный подход к построению хранилища
Сильные и слабые стороны этого подхода
Примеры кода
Дальнейший вектор развития hNhM

Меня зовут Евгений Ермаков, я руководитель Data Warehouse в Яндекс Go.

Я расскажу историю о том, как два руководителя объединились и сделали нечто крутое как минимум, по мнению этих двух руководителей. Расскажу про наш подход к хранению данных в детальном слое. Мы его называем highly Normalized hybrid Model. Надеюсь, что корректно произнес по-английски, я тренировался.

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



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

Также я расскажу про архитектуру хранилища Яндекс Go в целом, вместе с детальным слоем, где как раз эта модель и применяется. Потом сравню Data Vault и якорное моделирование так, как мы у себя их сравнивали, и объясню, почему мы из этого сравнения сделали вывод, что нужно создавать нечто свое. И расскажу базисные основы про hNhM.



А во второй главе я передам слово Коле.

Николай Гребенщиков:
Я расскажу о нашем фреймворке, который позволяет нам описывать сущности и загружать данные. Покажу, как мы с ним работаем, как загружаем используем и строим витрины над нашим детальным слоем.

В конце мы подведем итоги, самое главное, что можно из этого доклада почерпнуть. посмотрим, куда мы пришли, что сделали и как это можно переиспользовать.



Итак, архитектура Data Warehouse (DWH) в Яндекс Go. Расскажу об архитектуре слоев данных, какая она у нас, какие инструменты хранения и обработки информации есть, и покажу место детального слоя во всей этой архитектуре.



По моему мнению, архитектура слоев данных в нашем хранилище максимально классическая. Мы шли от глаголов действий над данными, которые происходят с хранилищами. Что мы делаем с данными?

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

Потом мы предоставляем эти данные для анализа, непосредственно анализируем и все. Классика.

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



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

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

Дальше детальный слой. Ядро хранилища. Здесь мы храним детальную историю изменений и консолидируем данные между всеми источниками.



На базе этого детального слоя есть слой витрин Common Data Marts.



Здесь мы уже формируем плоские, удобные для аналитиков витрины и оптимизируем доступ на чтение.



В слое отчетов мы храним отчетные срезы в контексте без потребителя, агрегаты, нечто более понятное для потребителя.



В итоге классика, все по слоям, от RAW до REP. Данные протекают в хранилище, все как завещали Кимбалл и Инмон в своих подходах.

Если это рассматривать прямо по системам, то для того, чтобы захватывать данные, у нас есть сервис репликации данных. Он забирает инкременты и снепшоты из источников самых разных типов, которые есть в Яндексе. И преобразовывает эти данные к устойчивому формату.



RAW, ODS это такой Data Lake. Здесь у нас полуструктурированные данные, каркас MapReduce и всевозможные внутренние аналоги экосистемы Hadoop.

Центр хранилища это непосредственно Data Warehouse. Здесь у нас слои ODS, DDS, CDM.



Основные цели давать ответ на всевозможные ad-hoc-запросы наших аналитиков, выдерживать большое количество Join и достаточно малое время отклика на всевозможные вопросы.

И витрины. Помимо того, что они служат Data Warehouse, мы еще отгружаем их содержимое в системы анализа и визуализации данных. Это кубы данных, отчеты, дашборды, Tableau.



Если мы эти слои разложим по системам, то получится приблизительно такая картина.

То есть RAW, ODS и части CDM-слоя на супербольших данных это у нас Data Lake.



Маленькая ремарка: в Яндексе много внутренних инструментов, которые мы делаем сами. У нас есть своя собственная платформа, есть такие внутренние инструменты, как YT. Можно проводить аналоги: YT это как Hadoop. И в принципе, есть всевозможные аналоги Hadoop-стека.

На Greenplum у нас находятся часть слоя ODS, детальный слой и витрины. Построенные в Greenplum витрины мы затем отгружаем в MS SSAS или ClickHouse для ряда пользователей. Некоторым удобно пользоваться кубами данных, некоторым широкимb плоскими таблицами, и ClickHouse здесь прямо идеален.

Часть витрин доступно для биосистем, или мы делаем из них агрегаты, доступные для нашего BI. BI у нас это Tableau.

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

Смотреть доклад Владимира

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

Если посмотреть на детальный слой, он не зря находится в самом центре хранилища, потому что это ядро, сердце, ключевое для построения всей доменной модели хранилища.



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

Два важных пункта. Детальный слой должен быть устойчив к изменению в бизнесе. Это достаточно сложно: допустим, мы изменили кардинальные связи или добавили новые сущности. Или у нас полностью изменился подход к бизнесу.

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

Когда мы начали проектировать детальный слой, то провели анализ а какие подходы к построении детального слоя вообще есть? Первое, что приходит на ум, это когда нет никакого детального слоя или подхода.

Отсутствие детального слоя, когда витрина строится прямо по ODS, это нормально по Кимбаллу. Это нормально, если у вас немного источников, если вы небольшой стартап. Но при росте количества источников взаимосвязи между ними и построением витрин становятся настолько большими, что приходится выделять детальный слой, чтобы унифицировать процесс построения витрин.

А подход, который называется никакого подхода, это когда мы просто сваливаем данные в кучку и потом пытаемся их них что-то извлечь. Если огрублять, это что-то вроде болота данных.

К сожалению, такой подход тоже имеет право на жизнь только потому, что он часто встречается. Так-то он, конечно, не имеет права на жизнь. Тем не менее, тут у нас денормализация. Можно использовать без подготовки в том плане, что легко селектить. Однако здесь нет устойчивости к изменениям, при любых изменениях в источнике придется все это перестраивать.



Пойдем дальше. Следующий подход классическая звезда и снежинка. Нормализация до третьей нормальной формы, то, что описывал у себя Билл Инмон. Это можно использовать с минимальной подготовкой нужно понимать, что такое первичный ключ, внешний ключ, join-таблички, SCD2, если мы говорим про измерения. И понимать, какие SCD вообще существуют.

Это может быть неудобно перестраивать при изменении кардинальности. Например, есть клиент и заказ. У одного клиента может быть много заказов, но у одного заказа только один клиент. Бизнес поменялся, у одного заказа теперь может быть несколько клиентов, и клиенты могут делить между собой заказ, как это, например, реализовано в такси. Перестраивать все это крайне больно.

При этом минимальное дублирование информации, если мы используем SCD. И какое-то приемлемое количество join. Если аналитики работают с SQL, они должны уметь работать и с этим.

Следующие походы Data Vault и Anchor modeling. Почему я их вывел одновременно? Потому что они предлагают, на самом деле, нечто очень похожее. Это достаточно строгая нормализация, их сложно использовать без подготовки, без понимания, какие таблицы и какие правила они накладывают.



Обе методологии обещают, что их не надо перестраивать. Для Data Vault это работает с ограничениями, я дальше проговорю, какими. Здесь ультрабольшое количество join. При этом обе методологии относительно современны и обещают гибкость.

Посмотрим на эксплуатацию. Все, что справа, Data Vault и якорное моделирование достаточно сложно эксплуатировать из-за большого количества join. Чем больше join, тем сложнее писать SQL-запросы и в целом получать отсюда информацию. При этом проще вносить изменения в модель. Во всяком случае, обе методологии это обещают.

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

Когда мы начали смотреть на Data Vault и якорное моделирование, они показались нам крайне близкими и мы решили сравнить их между собой.



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

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

Сперва стоит спрогнозировать требования, а потом подумать, согласовать, подумать еще. Я уверен, что все, кто работал с классическими DWH, которые построены не по Data Vault или по якорю, понимают, что DWH это не быстро.

Но здесь классический дэвэхашник сталкивается с проблемой: заказчик не знает, чего хочет. Готов смотреть на результат, а потом уточнять, что ему хочется. Его желания постоянно меняются.

И вообще, у нас Agile, мы гибкие, давайте все переделывать максимально быстро. Желательно, завтра. Примерно так выглядит классический дэвэхашник, когда попадает в Agile-среду, в которой невозможно использовать Waterfall.

Логичные вопросы: откуда родились методологии Data Vault и якорь; может ли DWH быть agile; можно ли подходить к разработке хранилища гибко?

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



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



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

Data Vault вводит и регламентирует несколько основных тип таблиц. Ключевые это Hub, Link и Satellite, дальше я буду о них говорить. Hub хранит сущности, Link обеспечивает связь между хабами, Satellite предоставляет атрибуты и описания Data Vault.

Data Vault 2.0 я не буду касаться. Есть еще специальные таблицы типа bridge и point-in-time. Они упрощают или соединение данных через несколько связей, или получение информации из сателлитов с разной частотой обновления. Это скорее расширяющие, упрощающие модель сущности. Ключевые таблицы это все-таки Hub, Link и Satellite.

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



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

Satellite или сателлит это описательная информация хаба, обычно с историзмом по SCD2. Здесь есть информация о ключе связи или хаба, в зависимости от описательных характеристик того, что находится в этом сателлите, сама информация и технические записи: SCD2, временная отметка даты загрузки, код источника данных.

Если на это посмотреть с точки зрения третьей нормальной формы, то есть вот такая неплохая картинка из презентации самого автора Data Vault:


Ссылка со слайда

Посмотрим на третью нормальную форму. Видно, что бизнес-ключи мигрируют в хабы. Все внешние ключи мигрируют и фактически являются связями. А все описательное, все наши поля, которые несут какой-то смысл, уходят в сателлиты. Сателлитов может быть несколько. Нет явных правил, как правильно разбить атрибуты на сателлит. Есть разные подходы: можно бить по частоте изменений, по источнику, по чтению это такой творческий момент в Data Vault.



Если мы посмотрим на якорное моделирование, то я бы его сформулировал как такую крайнюю форму Data Vault, когда у нас правила еще строже и нормализация еще выше.

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

Якорь фактический аналог хаба с важным отличием: здесь нет бизнес-ключей, есть только суррогатный ключ и временная отметка даты загрузки, техническая информация. Все бизнес-ключи фактически являются атрибутами и хранятся в таблицах атрибутов.



Tie связь многих ко многим, один в один как в Data Vault. Внешние таблицы это принципиальный момент, даже если кардинальность связи другая. Но в якорном моделировании есть отличие у связи принципиально не может быть атрибутов.

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

Но расплачиваться за это приходится дорого. Даже очень простая схема, которая загружена на слайде, превращается в гигантское, просто неимоверное количество таблиц.

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



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



Внешние ключи находятся в наших связях.


Ссылка со слайдов

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

Возникает вопрос: нельзя ли хоть как-нибудь прохачить систему и навесить атрибут на связь? Можно через словарь. Но это именно что словарик, некий типизатор связи. Ничего более сложного на связь в якорной модели навесить нельзя.

Посмотрим на стандартный тест TPC-H. Я уверен, что многие из вас знают, что это такое, но кратко напомню: это стандартный тест для проверки аналитических хранилищ, внизу на слайде есть ссылка на одну из его версий.


Ссылка со слайда

Сейчас на слайде схема в третьей нормальной форме. Здесь есть таблица фактов, таблица измерений, все это связано между собой. Ключи не помечены, но их можно прочитать.

Если мы эту схему преобразуем в Data Vault, то видно, насколько больше таблиц становится, появляются отдельные хабы, отдельные Link. Причем Link сделаны в виде таблиц многие-ко-многим.


Ссылка со слайда

У нас вешаются сателлиты как на Link, так и на отдельные хабы. И в целом, таблица становится больше.

Если мы это конвертируем в якорную модель, получится нечто подобное.



Честно говоря, на этом слайде есть небольшое лукавство в том смысле, что я не нашел якорной модели в TPC-H, хотя пытался найти. Взял просто абстрактную схему с сайта якорного моделирования. Но, тем не менее, таблиц должно быть примерно столько, если не больше.

В чем же схожести и различия, если их в лоб сравнивать?



И в Data Vault, и в якоре создается специальная таблица на сущность. В Data Vault это Hub, в якоре это якорь. Разница в том, что в хабе есть бизнес-ключ, а в якоре бизнес-ключ это атрибут.

В Data Vault атрибуты группируются в таблицы-сателлиты. В якоре все строже: один атрибут одна таблица, шестая нормальная форма, все раскладываем на отдельные кубики-элементы.

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

И есть специальные таблицы, в Data Vault point in time и bridge, в якоре knot.

Когда мы вот так, в лоб, их сравнили, ощущение было что выбрать? В чем-то лучше якорь, в чем-то Data Vault. У каждого достаточно цельная методология. Якорь построже, но, с другой стороны, возникает меньше вопросов и больше пространства для автоматизации.


В общем, как-то так. Яндекс славится тем, что создает свои инструменты. Почему бы нам не создать свою модель?

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



Из этой идеи и родилась наша гибридная модель highly Normalized hybrid Model, hNhM. Здесь я кратко расскажу ключевые идеи модели. (...)



Ключевая идея выбирать оптимальный формат хранения. Мы не ограничиваем себя ни Data Vault, ни строгостью якоря. Но при этом позволяем выбрать либо одно, либо другое. А если мы позволяем это выбрать, то с точки зрения методологии классно было бы разделить логическое и физическое моделирование.

Еще есть требование, которое мы сами себе ставили: параллельная загрузка из разных источников, идемпотентность при повторной загрузке тех же данных. Стандартная для Data Vault и якоря устойчивость к изменению в бизнесе, модульность и масштабируемость.

И удобство построения витрин. Потому что да, таблиц очень много, есть большой риск написать не оптимально. Это даже не то чтобы риск. Просто вокруг тебя раскиданы мины. Очень хочется дать инструментарий, чтобы со всем этим было удобно работать.

Если пойти по этим идеям, посмотреть очень верхнеуровнево, то мы это представляли так.



У нас есть слои, про которые я рассказывал, RAW. ODS это наш Data Lake. В RAW мы захватили данные, они лежат как есть. В ODS мы их чуть почистили, но это операционные данные, без истории. В детальном слое мы фактически разложили это все на маленькие кубики-сущности. С точки зрения логического проектирования это сущности-связи между ними. С точки зрения физического хранения на нашем Greenplum это скрыто с точки зрения использования.

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

То есть мы сначала разбираем все на кубики, а потом из этих кубиков собираем что-то красивое. Во всяком случае, мы себе так это рисовали на старте. К концу доклада вы сможете понять, получилось у нас сделать это так, как мы это изначально планировали, или нет.

Про разделение логического и физического уровня. Мы хотели на старте их разделить явным образом.

Логический уровень это всем известные ER-диаграммы. Уверен, что все, кто работает с данными, про ER-диаграммы знает. Здесь есть сущности и их ключи, связи между сущности и атрибутивный состав сущностей. Мы еще добавили необходимость их историзации, но это как дополнительная информация к атрибутам.

Логический уровень когнитивно сложная часть проектирования. Он не зависит от СУБД в общем виде и достаточно сложен. Трудно выделить сущности и домены в бизнесе, особенно если ты его не знаешь так, как основатели этого бизнеса.



Физический уровень это скрипты DDL. Здесь выполняется партицирование сущностей, объединение атрибутов в группы, дистрибьюция в системах MPP. И индексы для ускорения запросов. Все перечисленное мы хотели скрыть. Во-первых, оно зависит от СУБД и технических ограничений, которые у нас есть. Во-вторых, нам хотелось сделать так, чтобы этот физический уровень был невидим, чтобы мы могли переключаться между Data Vault и якорем, если захотим.



Разделяя так логический и физический уровень, мы фактически делим задачи между нашими ролями. У нас есть две роли:

  • Партнер по данным, Data Partner. Мы так переименовали Data steward. Да, слово steward имеет здравое значение в переводе: распорядитель чего-то чужого, а данные в этом смысле чужие. Но все равно партнер по данным звучит гораздо лучше.

    Партнер по данным на логическом уровне должен ответить на вопрос концептуальной модели: какие вообще направления бизнеса и взаимосвязи между ними у нас есть, как часто будут меняться атрибуты. И из этого построить логическую модель, прямо по классике.
  • И инженер данных, здесь все классически. Он отвечает на вопросы физической модели: как хранить атрибуты, нужны ли партиции, нужно ли закрытие SCD2. И обеспечивает сам расчет данных по инкременту, а также пересчет истории, то есть фактически ETL-процесс.

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



В итоге, базируясь на ER-диаграммах, мы используем сущность и связь. Сущность базовый кубик любого описания домена. Здесь, на логическом уровне, мы хотели сделать так, чтобы сущности описывались наименованием и комментарием; именем сущности и его описанием; бизнес-ключом, который обязателен для любой сущности, чтобы понимать, что его определяет; и каким-то набором атрибутов.

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

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

Атрибут это таблица. Она содержит информацию об одном атрибуте, может содержать историю, а может не содержать. Group это группа атрибутов, как сателлит в Data Vault. Она может содержать информацию о нескольких атрибутах. Важное ограничение на уровне модели: все атрибуты должны приходить в эту группу сателлита из нового источника и иметь один тип историзма.



Связь. На уровне ER-диаграммы понятно, что это такое. С точки зрения логического уровня это список связанных сущностей и кардинальность связи. Здесь мы пошли по жесткой модели якоря и не позволили хранить у связи никаких атрибутов. Если хочется повесить на связь атрибут, то для этого должна быть отдельная сущность.

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

Итого мы получаем примерно такую картинку: На логическом уровне рисуем ER-диаграмму или описываем нашу сущность.



А на физическом уровне получаем вот такой набор таблиц. Видно, что тут есть и группы атрибутов, и просто атрибуты, и связи. Хотелось бы, чтобы все это было скрыто. Причем на обоих уровнях работы с DDS: и на загрузке данных, и на построении витрин.



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

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

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

Всего тут есть три концептуальные идеи:

  • Закрепить разделение логического и физического проектирования в методологии, полностью закрыть физическое проектирование.
  • Сокрытие физического проектирования провести как при загрузке данных, так и при использовании модели, чтобы было удобно загружать и строить витрины.
  • И так как мы все сокрыли с физической точки зрения, то нужно еще выбирать оптимальный формат хранения для каждого конкретного случая: группировать и догруппировать атрибуты. Все это скрыто внутри.

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

Из якоря мы взяли к ебе сам якорь. То есть мы не храним бизнес-ключи в ключевой таблице, а смотрим на них как на еще одни атрибуты. Связи идут через отдельную таблицу, как в Data Vault или якоре, но мы запретили вешать на них атрибуты. В перспективе хотим разрешить.



Но с точки зрения простоты внедрения проще не думать, вешать ли атрибут на связь, а сразу знать: если на связь потребуется выделить атрибуты создай новую сущность. Или подумай, что за сущность с этими атрибутами должна существовать.

Из Data Vault мы взяли специальные таблицы point in time и bridge для упрощения своей собственной внутренней работы с hNhM.

И если подводить итог, этим всем категорически невозможно управлять без фреймворка. Если вы попробуете все это воссоздать полуавтоматически или DDL-скриптами, то можно буквально убиться об количество таблиц и весь объем данных, метаданных, которыми надо управлять.



Поэтому мы разработали так называемый hNhM-фреймворк. Здесь я передаю слово Коле. Он подробно расскажет, что мы конкретно разработали и как мы этим пользуемся у себя внутри.

Да, я расскажу о нашем фреймворке что сделали, как храним и как с этим работаем.

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

Смотреть доклад Владимира

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



В центральной части находится наш Data Lake, который хранится на YT. Это аналог экосистемы Hadoop, в нем мы храним слои RAW и ODS. Сейчас у нас объем данных около двух петабайт.

И само хранилище на Greenplum, в котором находится частично ODS за очень небольшое количество времени и слой DDS. Сейчас Greenplum у нас весит, по-моему, около пол-петабайта. На каждом слое мы умеем вычислять инкремент, что позволяет ускорить загрузку, избавиться от лишней работы по чтению и обработке данных.

Детальный слой основной элемент хранилища на Greenplum. На его основе мы строим все наши витрины.



Отличительная особенность платформы: все сущности на всех слоях описываются в питонячем коде, но это видно и на слайде. Особенности описания сущностей в DDS мы рассмотрим на основе сущностей Person сотрудник.



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

Для чего нам нужна документация? На ее основе во время релиза платформа собирает документацию по всем нашим объектам и автоматически выкладывает на внутренний ресурс, это удобно для пользователей. Потому что им не надо следить за анонсами по изменению структуры. Они могут пойти в документацию, попробовать найти то, что им нужно, и начать пользоваться данными.



Далее необходимо описать технические параметры layout. С помощью трех полей Layer, Group и Name мы определяем путь до места хранения объекта в нашем хранилище. Неважно, будет ли это YT, Greenplum или что-то еще в будущем.



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

Для каждого атрибута указываем тип историчности.



Сейчас у нас есть три типа. Ignore это когда пришедшее значение записывается, а мнение с большей бизнес-датой игнорируется. Update когда при изменении значение перезаписывается. New исторический атрибут.

Атрибуты с Ignore и Update не хранят историю изменений и отличаются тем, что в Ignore предпочтение отдается значению с меньшей бизнес-датой, а в Update с большей.



Также для каждой сущности мы указываем логический ключ.

На слайде видно, во что физически превращается каждая сущность.





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

Также в каждой таблице есть поля, в которых хранятся даты действия атрибута.





Если у данного атрибута историчность New, то это два столбца utc_valid_from_dttm и utc_valid_to_dttm. То есть это метка во времени, с которой определяется действие конкретной записи.

Для атрибутов типа историчности Update и Ignore действует только один столбец: utc_valid_from. Это бизнес-дата, с которой мы узнали, что атрибут имеет текущее значение.





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

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



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

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

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

Структура после объединения части атрибутов в группы выглядит уже гораздо более компактно.





Например, все флаги мы объединили в группу flg, общую информацию в группу info, ключевые атрибуты в группу key.

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



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

Каждый Link по умолчанию является историческим. Таким образом, в нем всегда есть два поля: utc_valid_from и utc_valid_to.

В этом примере мы делаем Link между департаментом и сотрудником. В данном случае ключом Link является сотрудник.



На слайде мы видим, как это физически реализовано.



Видно, что у Link нет суррогатного ключа, потому что его суррогатным ключом являются все те сущности, которые входят в ключ Link.

Теперь мы обсудим, как загружаем сущности.

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



В самом простом случае этот кубик выглядит так.



В самом начале мы описываем источник. Это stage-таблица, для которой есть точно такой же класс, написанный отдельно в платформе.

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

Далее мы делаем маппинг между атрибутами. Описание таргета состоит из двух частей указание сущности, куда мы загружаем атрибуты, и маппинг самих атрибутов.



Как мы видим, в качестве таргета указана таблица Person. Дальше следует маппинг атрибутов. Это самый простой вариант описания. В реальной жизни все выглядит гораздо сложнее. Например, так.



Мы видим, что из одной stage-таблицы данные грузятся в несколько сущностей. Одна сущность может грузиться несколько раз. Это в данном случае e-mail, он может быть в stage-таблице и персональным, и рабочим.

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

А если нет, то, скорее всего, это ошибка и мы об этом сообщаем пользователю.



Но в ряде случаев мы не можем автоматически этого сделать. Например, в stage-таблицу приходит и персональный почтовый ящик, и рабочий. Но и в обоих Link используется одна и та же сущность e-mail. Мы их специально не разделяем, потому что в реальном мире e-mail единая сущность. В то же время нам надо понять, какое здесь поле, какой Link грузить. Мы добавили специальный параметр, который позволяет определить, какое поле куда идет.

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



Все задачи внутри графа выполняются параллельно, в зависимости от типа изменений. Это либо Insert\Update по SCD2, либо Insert. Данные из разных источников также могут загружаться параллельно.



Дальше я расскажу, как мы используем наш hNhM.

На основе DDS мы строим витрины. Пользователи напрямую могут обращаться к нашим сущностям и просматривать данные.



У нас есть два основных типа потребления данных DDS: ad-hoc-запросы от бизнес-пользователей и построение витрин. ad-hoc-запрос реализован двумя способами либо view, либо через функции. И то и то скрывает реализацию от пользователей.

Витрины мы строим с помощью нашего фреймворка и тоже полностью скрываем реализацию.

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

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

В конце концов, это просто очень сложно написать что-то с select, где будет 20-30 join.

Как у нас происходит доступ к сущностям из Python?



Было разработано несколько классов, которые позволяют сформировать SQL-запрос к определенной сущности и затем использовать его в построении витрины. Как это выглядит?



Мы описываем CTE. Оно может быть либо историческое, либо актуальное.



В первую очередь мы указываем сущность, которую хотим использовать.



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



Прямо здесь мы можем сделать переименования, чтобы лишний не делать их в коде.

А так выглядит SQL-запрос для построения витрины. Есть переменные постановки, которые полностью скрывают реализацию. Потом идет сам запрос, который их использует. При этом можно использовать переменные как в визе, так и во временных таблицах. Это уже зависит от объема данных.



Как происходит доступ к сущностям из СУБД?



У нас есть вьюхи и есть две специальные процедуры. Первая get_entity. Она получает в качестве параметров сущность, столбцы и создает временную таблицу, в которой будут, собственно, все необходимые данные процедуры.

И вторая процедура это когда мы от entity к существующей таблице через Link добавляем дополнительную сущность и расширяем нашу табличку. Теоретически ключ у нее даже может поменяться.

А сейчас чуть более подробно покажу, как это работает.



Мы вызываем специальную функцию.



Указываем сущность.



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



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

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



Примерно так же выглядит добавление сущности к уже созданной таблице.



Мы указываем таблицу, в которой уже есть сущность.



Указываем Link, через который мы хотим подключить дополнительную сущность.



Указываем саму сущность, которую мы хотим добавить.



И столбцы этой новой сущности. При этом мы можем как положить данные в уже существующую таблицу, так и создать новую.



Дальше мы расскажем, какие задачи оптимизации мы решали. Тут я передам слово Жене.

Спасибо. Расскажу про оптимизацию и атрибуты против групп. Задача, на мой взгляд, очень интересная.

Посмотрим на объявление класса. Например, на объявление сущности Person.



В физическом мире мы можем представить ее в виде один атрибут одна табличка.





Это будет вполне нормально с точки зрения якорной модели. А можем ли мы сгруппировать атрибут? Здесь часть сгруппирована: флаги отдельно, информация о персоне отдельно, ключи отдельно, атрибуты отдельно.





Какая схема лучше? С точки зрения загрузки данных все то же самое. С точки зрения чтения данных все это скрыто, Коля про это рассказал. И с точки зрения использования будет одинаково.Не можем ли мы сделать так, чтобы эта физическая модель выбиралась наилучшим, оптимальным образом?



Мы постарались это свести к оптимизационной задаче. У нас буквально стояла проблема с объемом данных на Greenplum. Сейчас она не стоит. У нас действительно полупетабайтовый кластер на Greenplum, это достаточно много. Но еще буквально год-полтора назад даже такого не было.

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

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

Будем минимизировать занимаемое место на диске. Вот такую задачу мы себе поставили. Как мы ее решали?



Прежде всего мы формализовали и стандартизовали операции, меняющие схему, но не меняющие логику. Они совсем простые и очевидные. Можем объединять группы атрибутов в какую-нибудь еще группу, еще более крупную. Можем разъединять набор групп или вообще разъединить до атрибутов.

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

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



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

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

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

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

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

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



К чему мы пришли, зачем мы сюда пришли и стоит ли вообще повторять наш путь?

Первый, на наш взгляд, инсайт от доклада сравнение Data Vault и якоря. Мы поняли, что нам нужен детальный слой, что есть современные методологии Data Vault и якорь, и провели между ними сравнение. Они очень похожи. Я воспринимаю якорь как более жесткую форму Data Vault: в последнем есть правила, а в якоре эти правила еще более сложные.



Мы провели сравнение и сделали вывод: можно взять лучшее из каждой методологии. Не стоит слепо идти по одной из них и разрезать, например, clickstream на отдельные атрибуты. Стоит взять лучшее из Data Vault и из якоря.


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

Раз мы все это скрыли, то можем для физического мира выбирать нечто наиболее оптимальное. Оптимизировали именно хранение информации, но можно сделать нечто более интересное, например чтение.

На слайде показано, что мы взяли из Data Vault, а что из якоря.

Стоит ли за этим повторять? Пожалуйста, можете взять и полностью повторить, использовать точно так же. Кажется, что это вполне рабочая вещь, потому что мы ее используем уже года полтора.



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

Этот путь мы не советуем повторять. Если у вас нет свободной горы разработчиков, которые настолько круто прокачаны в Python, что готовы все это повторить и аккуратно сделать так же, как у нас, лучше не вступать на этот скользкий путь. Вы просто убьете очень много времени и не получите того бизнес-результата, который можно получить, используя Data Vault, якорь или гибридную модель, как мы.



Что же делать? У нас есть большой roadmap развития. Можно добавить больше гибкости например, я говорил, что на связь мы не вешаем ни атрибуты, ни группы. Но здесь можно это реализовать и воссоздать все типы таблиц Data Vault и якоря.

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

DSL у нас хоть и есть, но хотелось бы полноценный язык, который позволит строить витрины с учетом инкремента так, чтобы была полная кодогенерация. Пока она частичная.

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

Что делать, если у вас нет разработчиков? Дождаться, когда мы это заопенсорсим. У нас есть такие мечты. Вполне возможно, что мы свои фантазии осуществим и заопенсорсим этот продукт.

Следите за обновлениями или, если не хотите следить, приходите к нам и создавайте гибридную модель вместе с нами. На этом все. Спасибо за внимание.
Подробнее..

Опыт хранения IP-адресов в PostgreSQL

16.06.2021 14:10:53 | Автор: admin

Описание проблемы

Не раз наша команда в Каруне сталкивались с задачей, связанной с хранением и использованием IP-адресов в базе данных. Предположим, что есть типичная задача: необходимо парсить огромное количество диапазонов адресов (~300k) сизвестного ресурса, а далее определять страну по IP-адресу клиента. Кажется, ничего особенного. Это довольно просто решается любым ниже описанным способом при малых нагрузках. Но если у нас тысячи пользователей, или наш сервис является прокси перед всеми остальными? В этом случае не хочется быть бутылочным горлышком и приходится бороться за каждую долю секунды.

Немного про адресацию

Существует 2 типа адресации в сети

INET (Классовая адресация IP-сетей) архитектура сетевой адресации, которая использовалась в Интернете в период с 1981 по 1993 годы. Была вытеснена бесклассовой адресацией ввиду плохой гибкости и неэкономичного использования адресного пространства.

CIDR (Classless Inter-Domain Routing,Бесклассовая адресация) современный метод IP-адресации, при которой количество адресов в сети определяется маской подсети.

Диапазон адресов записывается в видеaddress/y, гдеy число бит маски подсети. Например, /28 означает, что 28 разряда IP-адреса отводятся под номер сети, а остальные 4 разряда полного адреса под адреса хостов этой сети, адрес этой сети и широковещательный адрес сети.

Например, запись192.168.5.0/24означает диапазон адресов от192.168.5.1до192.168.5.254, а также192.168.5.0 адрес сети и192.168.5.255 широковещательный адрес сети.

Типы inet и cidr по умолчанию

PostgreSQL предоставляет 2 типа по умолчанию для хранения IP-адресов и диапазонов:inetиcidr. Существует путаница между официальными названиями классовой и бесклассовой адресации и типамиinet/cidr.

Типinetсодержит адрес узла, а также может содержать подсеть. Вводимое значение должно иметь форматaddress/y. Если компонентyотсутствует, то маска сети считается равной 32 (для IPv4), так что это значение будет представлять один узел.

Типcidrсодержит определение сети IPv4 (или IPv6). Вводимое значение также имеет форматaddress/y. Но еслиyкомпонент отсутствует, то сеть вычисляется по старой классовой схеме нумерации сетей (INET).

Существенным отличием этих двух типов является в том, чтоinetпринимает значения с ненулевыми битами справа от маски сети, аcidrнет. Если у вас сетевая маска /8, то типcidrтребует, чтобы все 24 крайних правых бита были равны нулю,inetне имеет этого требования. Например,255.0.0.2/8будет ошибочным дляcidrт.к. справа от маски255.0.0.0имеются ненулевые значения (цифра 2 в последнем разряде адреса).255.128.128.7/24, 255.255.255.255/31 тоже ошибочны, а вот для типаinetявляются валидными.

А может уже померим что-нибудь?

Выполним несколько предварительных настроек на локальной машине (MacBook 16, 2019 2,6 GHz 6-Core Intel Core i7). Создадим таблицу и добавим индекс для поля с IP-адресом:

CREATE INDEX ON ip_ranges USING GIST (ip_range inet_ops);

Попробуем выполнить большое количество запросов (1.000.000) определения вхождения в диапазон IP-адреса клиента с помощью цикла:

DO$$DECLARE  i RECORD;BEGIN FOR i IN 1..1000000 LOOP  PERFORM country_id FROM ip_ranges WHERE ip_range >>= {random_ip}; end loop;END;$$;

и посчитаем среднее время определения адреса.

inet

cidr

749 мкс

891 мкс

Волшебный ip4r

Существует способ и более быстродействующий использование расширенияip4r, позволяющее значительно сократить время определения страны пользователя.

Расширение гарантирует, что умеет в индексы лучше, чем встроенные типы PostgreSQL. И указывает на низкую производительность дефолтных типов даже в новых версиях СУБД. Кроме того, говорит о перегруженности дефолтных типов.

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

Серебряная пуля (или нет?)

Если вдруг вы используетеnginx, то для него естьgeo модуль, позволяющий определять по IP-адресу нужный параметр. Создадим сервис черезdocker-compose.yml:

version: '3.7'services:  web:    image: nginx:latest    volumes:      - ./nginx.conf:/etc/nginx/nginx.conf      - ./GeoIP.dat:/var/geo/GeoIP.dat      - ./geo.conf:/var/geo/geo.conf    ports:      - "8080:80"    environment:      - NGINX_PORT=80

Конфигnginx:

http {        ...    geo $geo {        default        NONE;        include        /var/geo/geo.conf;    }    geoip_country /var/geo/GeoIP.dat;        ...    server {        ...        location / {            ...            add_header Geo-By-File $geo;            add_header Geo-By-Binary $geoip_country_code;        }    }}

Мы можем получать гео клиента, через переменную$geo, предварительно сгенерировав файлgeo.confтипа:

128.0.0.0/1 US;...

Или скачать бинарный файлGeoIP.datи использовать его без генерации, получая гео через переменные ($geoip_country_code).

Измерить скорость определения в данном случае не получилось, но на боевом сервере проблем с быстродействием этого способа не возникало. Несмотря на простоту и удобство данного способа, не всегда возможно его использование (требование частого обновления и проблемы с этим, политики безопасности компании, и т.д.).

Выводы

Стоит отметить, что измерения производились в клиенте PostgreSQL и свели к минимуму оверхед языка программирования. Если вы боретесь за милисекунды, то стоит учесть и этот факт.

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

В случае если вам не нужно хорошее быстродействие, мало клиентов, вы не боретесь за доли секунды, то вам подойдут типы по умолчаниюinetилиcidr, различие между которыми находятся в рамках статистической погрешности. Расширениеip4rпозволит сократить время в ~20 раз.

Подробнее..

Анализируем слона вместе с коллегами

15.06.2021 14:10:21 | Автор: admin

Если ваша жизнь DBA, сопровождающего PostgreSQL, наполнена вопросами "а почему так медленно?" и "как сделать, чтобы запрос не тормозил?", наш сервис анализа и визуализации планов запросовexplain.tensor.ru сделает ее немного легче за счет привлечения коллег и обновленных подсказок.

м/ф "Следствие ведут Колобки"м/ф "Следствие ведут Колобки"

"Ландон из зе кепитал оф Грейт Британ"

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

Обсуждайте проблемный план там, где вам удобноОбсуждайте проблемный план там, где вам удобно

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

Подсказки к плану

Про базовый набор подсказок и способов ими воспользоваться я уже рассказывал в статье "Рецепты для хворающих SQL-запросов" - теперь мы сделали их еще больше и удобнее!

Рекомендательные подсказки узлов планаРекомендательные подсказки узлов плана
  • цветовая и текстовая кодировка разных видов

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

  • несколько подсказок у одного узла

    Неудачно использованный индекс с большим количеством отбрасываемых записей может легко соседствовать с неактуальной статистикой на таблице и быть ее следствием.

  • на самом видном месте

    Больше не нужно листать огромный план, чтобы увидеть, где там возникла проблема - все подсказки уже сдублированы в заголовке, и достаточно всего одного клика, чтобы перейти к нужному узлу.

Все подсказки - вместе, клик - и вы на местеВсе подсказки - вместе, клик - и вы на месте

Точно так же клик на заголовке столбца сразу перенесет фокус на узел с максимальным значением, так что выяснить "кто тут отфильтровал миллион записей" можно за полсекунды.

Масштабируемая диаграмма

Если прежде увидеть "горячие точки" на потоковой диаграмме выполнения большого плана было сложно за счет возникавшей прокрутки, то теперь сразу запускается просмотр в режиме масштабирования. Но вернуться к режиму прокрутки можно в один клик.

Карта будущего сражения за производительностьКарта будущего сражения за производительность

Пользуйтесь! Возникнут идеи или замечания - прошу в комментарии.

Подробнее..

Перевод Как объяснить детям, что такое Apache Kafka за 15 минут с картинками и выдрами

19.06.2021 04:20:24 | Автор: admin


Я учусь иллюстрировать сложные процессы с помощью комиксов. Нашла себе в копилку крутой кейс: как с помощью комиксов про милых выдр можно ребенку объяснить такую сложную штуку как Apache Kafka, и сделать мир немного добрее.

Легко по течению легкое введение в потоковую обработку и Apache Kafka. Группа выдр обнаруживает, что они могут использовать гигантскую реку для общения друг с другом. По мере того, как все больше выдр перемещается в лес, они должны научиться адаптировать свою систему, чтобы справиться с возросшей активностью леса.

Под катом 25 слайдов, объясняющие основы Kafka для детей и гуманитариев. И много милых выдр.



Легко по течению



image

Поначалу, в лесу было тихо, и только две семьи выдр жили среди деревьев и рек.

image

Всякий раз, когда им нужно было поделиться новостями, они просто разговаривали друг с другом напрямую.

Они делились новостями о вечеринка в честь дня рождения, новых гостях и других событиях, происходящих в лесу.

image

Со временем, все больше и больше выдр переселялось в лес.

И точно так же росло количество происходящих в лесу событий.

image

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

image

Это не только занимало много времени, но было чревато ошибками.

Что если какая-то выдрячья семья была на пикнике, и не могла получить уведомление о предстоящем событии?

image

Ну, тогда выдре, которая хотела оповестить соседей, придется вернуться позже или сдаться.

image

Поскольку остальные выдры общались друг с другом лично, говорили, что они Тесно Связаны.

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

image
Именно тогда у одной маленькой выдры по имени Никси, появилась идея, которая навсегда изменила лес.

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

Вы можете установить Kafka в своем лесу:

# clone the repositorygit clone \https://github.com/round-robin-books/gently-kafka.git# start kafkadocker-compose up


image

Она даже сочинила песню, чтобы объяснить, как это работает:

События свои
В реку ты опусти,
Река их отнести
Сможет выдрам другим.

Что о событиях,
Плывущих в потоке,
Этот путь удивителен,
Мы все тут в шоке.

image

Не отстанешь ли ты? вмешался дельфин
Нельзя оставлять все на милость глубин!

Не будет в доставке такого.
Мы командой реку разделим,
На множество мелких потоков
И вместе достигнем цели.

Давайте посмотрим, как это рабоnает



image

Во первых, выдра наблюдает Событие, что-то, что произошло в определенный момент времени.

К примеру, Сегодня вернулись пчелы это событие.

image

Дальше, выдра создает Запись о событии.
Записи (которые иногда называют сообщения) включают время события (отметка о времени) и дополнительную информацию о событии.

image

Тогда выдры смогут решать в какую часть реки направить это сообщение.

Река делится на потоки, которые называют Топики, которые делают организацию сообщений проще.

image

В итоге, выдры бросали свои сообщения в потоки (топики), чтобы другие выдры могли их найти.

По началу они использовали стеклянные бутылки, но бутылки просто уплывали.

image

Тогда, выдры переключились на стеклянные поплавки. Поскольку поплавки оставались на месте и никуда не уплывали, это было названо Устойчивостью.

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

2 типа выдр



image

Выдры, которые помещают сообщения в реку, называются Продюсеры.

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

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

image

Выдры, которые читают события в потоке называются Консьюмеры.

Консьюмеры следят за любым топиком, который их интересует.

Например, выдры Первой помощи следят за топиком с лесными алертами, так что они могут реагировать в чрезвычайных ситуациях (как в ситуации с пчелами).

image

Эта система работает хорошо, но спустя какое-то время река становится слишком загруженной огромным количеством событий. Как же маленьким выдрам все успеть?

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

image

Тогда, один член семьи, так называемый Лидер Группы, назначает подмножество мелких потоков (разделов) каждому члену семьи.

Это подразумевает, что каждая выдра ответственна только за ту часть потока, которую она мониторит.

image

Работая совместно в этом случае, каждая выдрячья семья называется Группой Консьюмеров, которая успевает следить за своей частью потока сообщений, сколько бы их не было всего в реке.

image

Более того, если выдра заболела или у нее есть дела, ее работу можно поручить другой выдре в ее группе.

Поскольку кто-то всегда был рядом, выдры были Высоко доступными.

И поскольку они могли справляться с Незапланированными Ситуациями, они были Отказоустойчивыми.

image

Еще существовала магическая часть леса, Земля Потоковой Обработки, где выдры могли делать реально классные вещи с событиями в реке.

image

Целая книга написана об этом магическом месте: Mastering Kafka Streams and ksqlDB

image

Выдры также построили дороги, которые называются Кафка Коннекторы, чтобы донести сообщения других сообществ в реку Кафка. Все присоединились к веселью.

image

Кафка продолжала помогать многим другим во всем мире. И возвращалась обратно в лес.

Жизнь шла своим чередом многие годы, а выдры жили долго и счастливо.



Я не удержалась, вот вам несколько фактов про выдр:



Выдрята, или щенки выдр, спят у мам-выдр на груди.



Взрослые выдры держат друг друга за лапки во сне, чтобы их не разделило течение.



А одинокие выдры, чтобы не дрейфовать, заворачиваются в водоросли.

Строго говоря, автор использует термин otter, что переводится как выдровые, и включает себя 13 видов животных с распространением по всему миру, кроме Австралии.

И если эти слайды вы действительно использовали этот материал на детях в образовательных целях, то завершить можно речевым упражнением:

В недрах тундры выдры в гетрах тырят в ведра ядра кедров. Выдрав с выдры в тундре гетры, вытру выдрой ядра кедра, вытру гетрой выдре морду ядра в вёдра, выдру в тундру.
Подробнее..

Как из одной базы данных сделать 10 разных, храня только инкременты обзор решения

18.05.2021 10:20:20 | Автор: admin
История очень простая: есть большая продуктовая база данных. Она нужна пяти-шести командам разработки, тестировщикам и другим командам. Можно сделать штук 10 разных инстансов + БД, но обычно это дорого и долго. Гораздо лучше взять одну мастер-базу и хранить её инкременты для тех команд, которые с ней работают. Для этого есть специальные утилиты. Если лет пять назад они только начинали распространяться в России, то теперь их использование абсолютно нормальная практика.

Давайте посмотрим, как это работает, на примере Actifio:

image
Слева Shapshots, на их основе можно создавать виртуальные БД (VDB).

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

Куда нужна такая БД?


Да почти везде:

image

Обычный процесс выглядит так:

image

На самом деле это процесс курильщика, потому что вот процесс здорового человека, подумавшего когда-то про правильную инфраструктуру:

image

Для этого мы делаем не вот так:

image

То есть каждый инстанс БД взаимодействует в режиме read-write (да-да, именно чтение и запись, это не оЧепятка ;) !!!) через Actifio с основной и единственной на всех БД и её инкрементами. Или, если вы не хотите нагружать её чтением, с единственным зеркалом для разработки/препрода/тестов и иных полезных задач.

image

То есть, допустим, разработчики говорят: нам нужна тестовая БД для быстрой выкатки нового функционала. ОК. Заходим в GUI Actifio, нажимаем раз пять-шесть мышкой и делаем провижн виртуальной БД (VDB), которая является клоном продуктивной БД. Таких клонов (VDB) может быть сколь угодно много. VDB готовится из одной-единственной копии продуктивной БД. И эта копия БД постоянно обновляется (догоняет) информацией из продуктивной БД (по графику, который можно установить произвольным образом).

У нас среднее время от тикета до предоставления базы 30 минут.

Actifio ещё можно использовать для задач Disaster Recovery:

image

Ну и бекапа и восстановления БД соответственно примерно так же.

Как выглядят интерфейсы?


Вот так. Процесс создания виртуальной БД (VDB):

image

Слева выбираем необходимый снэпшот для создания виртуальной БД (VDB) и нажимаем кнопку Mount (внизу справа):

image

Заполняем необходимую информацию о создаваемой VDB и нажимаем кнопку Submit. Процесс создания VDB начался, он займёт минут 1520:

image

Сам процесс создания VDB можно контролировать:

image

Собственно, всё.

Практика


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

Мы разворачиваем Актифио обычно в виртуальной среде заказчика, к которой подсоединён сторадж, где будет храниться основной клон. Обычно это железо из тестового, то есть либо доживающее свой пятый-шестой год, либо из недорогих. Хоть там и будет копия продуктовой базы, продуктовой нагрузки не будет, поэтому можно брать самые дешёвые хранилки. Дальше делается первая большая репликация, то есть фактически пишется этот самый клон в сторадж. Потом из основной базы делаются инкрементальные а-ля бекапчики по ночам.

От клона в разные стороны торчат тестовые инстансы, где команды могут полноценно работать с базой. Каждая запись хранится как инкремент для этого инстанса, то есть каждый работает со своей версией базы и может там менять что хочет, но база типа 15 ТБ занимает с 22 нашими инкрементами в районе 20 ТБ (потому что там ещё включён дедуп, который не используется на проде из-за хайлоада).

Разворачивается на Linux, UNIX, Windows. Нужно проверить версию операционки и версию СУБД, но всё популярное подходит. С легаси может не срастись.

Это всё в эксплуатации уже больше пяти лет.

С какого момента это окупается?


Обычно с баз от 3 ТБ и четырёх команд. Мы начали использовать на базе 6 ТБ и шести команд у себя лет пять назад.

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

DBA прибираем мертвые души

12.05.2021 18:04:50 | Автор: admin

Иногда при выполнении длительных или плохо написанных запросов в PostgreSQL происходят разные неприятные вещи типа внезапного сбоя процесса или краша всего сервера.

В таких случаях на носителе могут остаться "мертвые души" - файлы (иногда совсем немаленькие, а вполне сравнимые по объему со всей остальной базой), которые были созданы во время работы процесса в качестве временного хранилища промежуточных данных.

Эти данные уже никому не нужны, никем не могут быть использованы, но сервер все равно не торопится избавиться от них как Плюшкин.

Сегодня посмотрим, как их можно найти и безболезненно "зачистить".

Разыскиваем temp buffers

Первая категория возникающих проблем - временное использование дискового пространства при выполнении узла плана, если необходимый объем памяти не уместился в work_mem.

Получить такой эффект достаточно просто - забыть поставить или выбрать слишком большой предел рекурсии:

explain (analyze, buffers)WITH RECURSIVE T AS (  SELECT    0 i  , '' sUNION ALL  SELECT    i + 1  , repeat('a', i + 1)  FROM    T  WHERE    i < 1e4 -- 10k итераций)TABLE T ORDER BY s DESC LIMIT 1;

[просмотреть на explain.tensor.ru]

Корень беды заключается в том, что для сортировки рекурсивной выборки T необходимо вычислить и куда-то записать ее полностью, что и показывает атрибут temp written:

->  CTE Scan on t  (actual time=0.008..374.157 rows=10001 loops=1)      Buffers: temp written=6126

Давайте теперь сэмулируем неприятность, случившуюся во время выполнения запроса - увеличим для этого ограничение рекурсии на порядок:

SELECT pg_backend_pid();-- 15004 - это PID процесса, обслуживающего наше клиентское соединениеexplain (analyze, buffers)WITH RECURSIVE T AS (  SELECT    0 i  , '' sUNION ALL  SELECT    i + 1  , repeat('a', i + 1)  FROM    T  WHERE    i < 1e5 -- 100k итераций)TABLE T ORDER BY s DESC LIMIT 1;
kill -9 15004
Плохо "убитый" клиентский процесс тянет за собой postmaster и весь PostgreSQL-серверПлохо "убитый" клиентский процесс тянет за собой postmaster и весь PostgreSQL-сервер

Сервер быстро упал - быстро поднялся. Но место на диске у нас убыло почти на 4GB - где же они?

Найти их нам поможет функция получения списка временных файлов pg_ls_tmpdir:

SELECT * FROM pg_ls_tmpdir();
 name            |  size      |  modificationpgsql_tmp15004.0 | 1073741824 | 2021-05-12 10:46:10+03pgsql_tmp15004.1 | 1073741824 | 2021-05-12 10:46:38+03pgsql_tmp15004.2 | 1073741824 | 2021-05-12 10:47:06+03pgsql_tmp15004.3 |  958078976 | 2021-05-12 10:47:31+03

Данная функция появилась только в PostgreSQL 12, поэтому если версия вашего сервера младше, придется воспользоваться pg_ls_dir по <data>/base/pgsql_tmp - это как раз то место, где сохраняются временные файлы, которые мы ищем.

Эти файлы в имени содержат PID процесса, который их породил. Поэтому, чтобы не зачистить лишнего, сразу проверим, что среди активных запросов такого идентификатора нет:

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/pgsql_tmp' dir), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir), tmp AS (  SELECT    *  , regexp_replace(fn, '^pgsql_tmp(\d+).*$', '\1')::integer pid  , (pg_stat_file(dir || '/' || fn)).*  FROM    ls)SELECT  dir || '/' || fnFROM  tmpLEFT JOIN  pg_stat_activity sa    USING(pid)WHERE  sa IS NOT DISTINCT FROM NULL;

Теперь осталось пройти по полученному списку и поудалять. Замечу, что если "прибивать" запрос через pg_terminate_backend(pid), то и сервер не "падает", и подобного "мусора" в каталоге не остается.

Ничейные TEMPORARY TABLE

CREATE TEMPORARY TABLE x ASSELECT  i, repeat('a', i::integer) sFROM  generate_series(1, 1e5) i;

Теперь в списке схем нашего соединения появилась pg_temp_5:

SELECT current_schemas(true);-- {pg_temp_5,pg_catalog,public}

Именно на эту схему проецируется обращение к псевдосхеме pg_temp - то есть в этом соединении запросы TABLE x, TABLE pg_temp.x и TABLE pg_temp_5.x будут эквивалентны, пока эта временная таблица существует.

Но раз эта таблица полноценная, а не "полуфабрикат", как в случае temp buffers, то мы должны бы увидеть ее и в pg_class:

SELECT  oid, relnamespace::regnamespace, relname, relfilenodeFROM  pg_classWHERE  relnamespace::regnamespace::text ~ '^pg(_toast)?_temp';
 oid  |  relnamespace   |  relname             |  relfilenode66112 | pg_toast_temp_5 | pg_toast_66109       | 6611266114 | pg_toast_temp_5 | pg_toast_66109_index | 6611466109 | pg_temp_5       | x                    | 66109

Выяснение такой странной нумерации схем приводит к письму Tom Lane аж от февраля 2003:

> What is the origin of these schemas? local temporary tables? sorts?

Right, they're made to hold temporary tables. The first time a givenbackend does CREATE TEMP TABLE, it looks for a pg_temp_n schema, and makes it if it's not there. On shutdown, it removes the temp tables, but it seemed like a waste of cycles to remove the pg_temp_n schema itself.

(ObTrivialFact: the 'n' is the backend's pgproc slot number, so it's known not to be in use by any concurrently running backend. But it will certainly be used again in future.)

Итак, при штатном гашении сервера сами файлы временных таблиц должны быть вычищены. Собственно, а где они?

В отличие от temp buffers, относящихся ко всему серверу, файлы временных таблиц и индексов относятся к конкретной базе, но имеют несколько другой формат имени:

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/' || oid dir  FROM    pg_database  WHERE    datname = current_database()), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir)SELECT  *FROM  lsWHERE  fn ~ '^t';
 dir                |  fn.../data/base/16393 | t5_66109.../data/base/16393 | t5_66112.../data/base/16393 | t5_66114

То есть имя файла временного объекта выглядит как t<temp schema N>_<temp object OID>. Если сейчас мы "уроним" сервер снова, эти файлы останутся, как и записи в pg_class.

Чтобы избавиться от них, можно прогнать VACUUM FULL по всей базе, но это практически невозможно, если она достаточно велика. Или просто подождать когда то же самое доберется сделать autovacuum:

LOG: autovacuum: found orphan temp table "tst.pg_temp_5.x"

Но если таблиц в базе немало, это может наступить ой как нескоро, а дисковое пространство по-прежнему будет занято.

Поэтому, чтобы выловить заведомо "ничейные" временные таблицы, сравним время последней модификации их файлов со временем перезагрузки сервера, которое в обычных условиях можно определить как время сброса статистики по "нулевой" базе:

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/' || oid dir  FROM    pg_database  WHERE    datname = current_database()), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir), lsid AS (  SELECT    *  , (pg_stat_file(dir || '/' || fn)).*  FROM    ls  WHERE    fn ~ '^t'), sch AS (  SELECT DISTINCT    regexp_replace(fn, '^t(\d+)_.*$', '\1') sch  FROM    lsid  WHERE    modification < (      SELECT        stats_reset      FROM        pg_stat_database      WHERE        datid = 0    ))SELECT  string_agg('DROP SCHEMA ' || nspname || ' CASCADE;', E'\n') sFROM  schJOIN  pg_namespace nsp    ON nsp.nspname IN ('pg_temp_' || sch, 'pg_toast_temp_' || sch);

Получаем готовый текст запроса, который останется только выполнить:

DROP SCHEMA pg_temp_5 CASCADE;DROP SCHEMA pg_toast_temp_5 CASCADE;
Подробнее..

Что нового в плане мониторинга в PostgreSQL 14

22.05.2021 22:20:06 | Автор: admin
Всем привет, на этой неделе вышел бета-релиз PostgreSQL 14. В этом небольшом посте я сделаю краткий обзор того что есть нового и полезного в плане мониторинга и наблюдения.

По идее пост должен быть интересен тем кому небезразлична тема мониторинга и поиска проблем в PostgreSQL системные администраторы, DBA, SRE, DBRE.



  1. В pg_stat_activity добавлено новое поле query_id. Поле содержит идентификатор запроса аналогичный тому что есть в pg_stat_statements. Таким образом с помощью трио полей datid/userid/query_id можно присоединить pg_stat_statements и посмотреть накопленную статистику по конкретному типу запросов. Забавный нюанс имена полей для объединения между pg_stat_activity и pg_stat_statements отличаются.
    Осторожно, текст
    select a.*, s.* from pg_stat_activity a inner join pg_stat_statements s on (a.datid = s.dbid AND a.usesysid = s.userid AND a.query_id = s.queryid) where a.pid = 1001291;-[ RECORD 1 ]-------+-----------------------------------------------------------datid               | 16413datname             | pgbenchpid                 | 1001291leader_pid          | usesysid            | 10usename             | postgresapplication_name    | pgbenchclient_addr         | client_hostname     | client_port         | -1backend_start       | 2021-05-22 10:15:57.299468+05xact_start          | 2021-05-22 10:16:25.566151+05query_start         | 2021-05-22 10:16:25.566623+05state_change        | 2021-05-22 10:16:25.566763+05wait_event_type     | wait_event          | state               | idle in transactionbackend_xid         | 237577backend_xmin        | query_id            | 2517686606037258902query               | SELECT abalance FROM pgbench_accounts WHERE aid = 1715456;backend_type        | client backenduserid              | 10dbid                | 16413toplevel            | tqueryid             | 2517686606037258902query               | SELECT abalance FROM pgbench_accounts WHERE aid = $1plans               | 0total_plan_time     | 0min_plan_time       | 0max_plan_time       | 0mean_plan_time      | 0stddev_plan_time    | 0calls               | 209439total_exec_time     | 4251.98569499987min_exec_time       | 0.005414max_exec_time       | 0.435581mean_exec_time      | 0.020301785698938563stddev_exec_time    | 0.005889254053319066rows                | 209439shared_blks_hit     | 884097shared_blks_read    | 0shared_blks_dirtied | 0shared_blks_written | 0local_blks_hit      | 0local_blks_read     | 0local_blks_dirtied  | 0local_blks_written  | 0temp_blks_read      | 0temp_blks_written   | 0blk_read_time       | 0blk_write_time      | 0wal_records         | 149wal_fpi             | 2wal_bytes           | 9870
    


  2. В pg_stat_activity в списке процессов теперь также отображается WAL archiver. Пока информации не так много, так что не особо информативно и есть куда развиваться далее.
  3. В pg_stat_activity для wal sender процессов (участвуют в репликации), в поле query отображается команда протокола репликации. Это небольшое улучшение позволяет отслеживать команды репликации между мастером и репликами, раньше это возможно было только через логи с включенным параметром log_replication_commands.
  4. В pg_locks добавлено поле waitstart время с которого началось ожидание. Поле позволяет получить время ожидания и при этом не присоединять pg_stat_activity. С одной стороны вроде и удобно, но чтобы взять текст запроса, все равно понадобится присоединить pg_stat_activity. А вот для использования в качестве метрики, вполне подходит т.к. в таком случае текст запроса может быть неинтересен.
    Выглядит так
    # select pid,mode,now()-waitstart as wait_time from pg_locks where not granted;-[ RECORD 1 ]--------------pid       | 1068094mode      | ShareLockwait_time | 00:00:12.669753-[ RECORD 2 ]--------------pid       | 1068093mode      | ShareLockwait_time | 00:00:14.789208
    


  5. Пара групп полей в pg_stat_database. Первая группа это session_time, active_time, idle_in_transaction_time коротко, в этих полях посчитано сколько времени проведено сессиями. Есть нюанс счетчики обновляются в момент смены состояния (поле state), поэтому если сессия долгое время находится в одном состоянии, то это время засчитается только тогда когда оно (состояние) сменится на другое или сессия завершится. Вторая группа полей sessions, sessions_abandoned, sessions_fatal, sessions_killed определяет статистику по сессиям и причинам их завершения. Обе группы счетчиков так и просятся в графики на мониторинг.
  6. Новое представление pg_stat_progress_copy. По названию очевидно что представление показывает ход выполнения команд COPY. Полезно за наблюдением того как 1) выполняется выгрузка и загрузка дампов (pg_dump), 2) непосредственное выполнение команд COPY, 3) выполнение начальной загрузки таблиц при использовании логической репликации через публикации/подписки.
    Пример копирования из файла в таблицу
    -[ RECORD 1 ]----+----------pid              | 1068106datid            | 16413datname          | pgbenchrelid            | 17612command          | COPY FROMtype             | FILEbytes_processed  | 30998528bytes_total      | 195764221tuples_processed | 313263tuples_excluded  | 0
    


  7. Новое представление pg_stat_wal с помощью него можно получить подробное представление об объемах генерируемого WAL. До версии 13 ничего подобного не было. Затем в 13-й версии подобная статистика была добавлена в pg_stat_statements и если ее просуммировать можно получить нечто похожее. Теперь эта статистика расширена и есть в более обобщенном виде (не привязана к запросам).
    Пример
    -[ RECORD 1 ]----+------------------------------wal_records      | 40811237wal_fpi          | 1551923wal_bytes        | 13744020096wal_buffers_full | 509935wal_write        | 1177449wal_sync         | 666045wal_write_time   | 26449.751wal_sync_time    | 10956905.427stats_reset      | 2021-05-21 10:33:39.009804+05
    


  8. Новое представление для наблюдения за логическими слотами репликации pg_stat_replication_slots. Если используется логическая репликация (PUBLICATIONS/SUBSCRIPTIONS, или например Debezium), то это будет полезно в добавок к уже имеющейся pg_replication_slots.
  9. Новое поле toplevel в pg_stat_statements. Известно что pg_stat_statements можно настроить так, чтобы хранить статистику о запросах вложенных в процедуры и функции. Однако если считать агрегаты, то можно получить неверные результаты статистика по вложенным запросам посчитается дважды. Все из-за того, что непонятно как отличить вложенный запрос от нормального. Новое поле позволяет отличать запросы и исключать их.
  10. Новое представление pg_stat_statements_info. Пока там всего два значения время сброса статистики и количество выброшенных значений из pg_stat_statements. Второе значение представляет пользу, поскольку pg_stat_statements имеет лимит на количество записей и если лимит достигнут, то старые записи будут удалены и этого никто не узнает. Теперь же это можно отследить и при необходимости пересмотреть значение pg_stat_statements.max.
  11. Новое представление pg_backend_memory_contexts одно из нововедений которое вызвало у меня противоречивые эмоции. Показывает распределение используемой памяти текущим процессом. Представление как мне кажется предназначено для разработчиков и для отладки на предмет утечек памяти при долгой работе сессий.
  12. Здесь как бы продолжение предыдущего пункта, в котором будет понятен тезис про эмоции.
    Новая функция pg_log_backend_memory_contexts() это развитие предыдущего улучшения, функция принимает в качестве аргумента идентификатор процесса и сохраняет в журнал, статистику утилизации подобно той что в представлении pg_backend_memory_contexts.

    И вот тут как мне кажется, разработчики перемудрили достаточно было бы сделать только одну эту функцию (без вьюхи) которая бы принимала идентификатор процесса, но при этом вместо логирования, выводила бы статистику в виде строк. В таком случае можно взять статистику и по текущей сессии и по любой соседней. При этом возможность залогировать вывод тоже никуда не девается (хоть и не в журнал, но тем не менее возможность есть). А в текущем виде есть представление и функция с логированием и еще в журнал надо сходить, чтобы достать сброшенную статистику. Короче, мне реализация показалось слегка странной.
  13. Новые поля в pg_prepared_statements generic_plans, custom_plans для подсчета количества планов для каждого запроса. Честно, я практически этим представлением никогда не пользовался и у меня нет практических примеров его применения.
  14. Новая функция pg_get_wal_replay_pause_state(). Чуть более улучшенная версия функции pg_is_wal_replay_paused(). Показывает состояние постановки репликации на паузу. Доступно три варианта not paused, pause requested, paused.
  15. Новый параметр log_recovery_conflict_waits включает журналирование случаев когда воспроизведение WAL журнала на реплике вступает в конфликт и ждет разрешения конфликта. Штука полезная, я бы рекомендовал включать по-умолчанию.
  16. Новые операторы для pg_lsn типа. Тип pg_lsn используется для работы с позицией в WAL журнале например с помощью этого типа удобно считать лаг репликации в байтах. С помощью двух новых операторов арифметика стала проще и значения pg_lsn можно складывать или вычитать с целыми числами.
    Пока не придумал где использовать, но выглядит клево
    pgbench=# select '1/8000000'::pg_lsn + 16777216;-[ RECORD 1 ]-------?column? | 1/9000000pgbench=# select '1/8000000'::pg_lsn - 16777216;-[ RECORD 1 ]-------?column? | 1/7000000
    


  17. Добавлена информация о таймингах ввода-вывода при журналировании операций autovacuum и autoanalyze. Должен быть включен log_autovacuum_min_duration.
    А ну-ка попробуйте отыскать
    2021-05-22 10:50:48.000 +05 1005664 @ from  [vxid:4/309623 txid:0] [] LOG:  automatic vacuum of table "pgbench.public.pgbench_accounts": index scans: 1        pages: 0 removed, 65600 remain, 0 skipped due to pins, 0 skipped frozen        tuples: 1936414 removed, 2000605 remain, 566 are dead but not yet removable, oldest xmin: 253998        buffer usage: 92201 hits, 108672 misses, 129131 dirtied        index scan needed: 58623 pages from table (89.36% of total) had 1961508 dead item identifiers removed        index "pgbench_accounts_pkey": pages: 10970 in total, 0 newly deleted, 0 currently deleted, 0 reusable        avg read rate: 3.522 MB/s, avg write rate: 4.185 MB/s        I/O Timings: read=392.361 write=1964.360        system usage: CPU: user: 2.92 s, system: 1.79 s, elapsed: 241.07 s        WAL usage: 195815 records, 72916 full page images, 308792606 bytes
    



Вот и всё.

В завершение хочу сделать анонс мероприятия 8 и 9 июля в онлайне состоится PG Day21 Russia. Это будет двух-дневная тусовка фанатов PostgreSQL, 12 выступлений от отечественных и зарубежных докладчиков. Участие бесплатное. Прием докладов также открыт до 7 июня

На этом все, спасибо за внимание!
Подробнее..

Решение проблемы безопасности данных интегрированными средствами Oracle

14.05.2021 10:12:48 | Автор: admin

С началом работы программ лояльности, появилась возможность накапливать скидки, предоставляемых продавцами в виде бонусов. и оплачивать ими покупки. Сотрудники, обрабатывающие данные программ лояльности, оказываются перед соблазном использовать свои права доступа для неправомерных действий. Дмитрий Юдин, директор по развитию бизнеса Oracle СНГ, и Сергей Петраков, начальник службы информационной безопасности АО ТПК, специализированного оператора по процессингу пластиковых карт на корпоративном и розничном топливном рынке, рассказывают, почему тем, перед кем стоит задача защиты данных, стоит сосредоточиться не на поиске мошенников в своей команде и устранении последствий мошенничества, а на превентивных мерах защиты данных в информационных системах. О том, как с помощью встроенных в базы данных инструментов, можно исключить возможность мошенничества до того, как оно произошло.

Почему данные программ лояльности стали объектом интереса мошенников

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

Цифровизация топливного ритейла охватывает, в числе прочего, программы лояльности для физических лиц - комментирует Сергей Петраков, - а также корпоративные топливные программы, участниками которых являются юридические лица, заключившие контракты на поставку, продажу нефтепродуктов. В обеих программах конечный пользователь проводит расчеты на АЗС, используя соответствующие пластиковые карты или иные, замещающие карты цифровые сущности (QR-код, штрих-код, токен и др.) Объем средств, проходящий через корпоративную топливную программу существенно больше по отдельной покупке, но число участников программ лояльности при этом значительно выше. Случаи мошенничества встречаются во всех программах, но программы лояльности становятся особенно привлекательными для злоумышленников за счет растущего объема передвигающихся средств, высоким уровнем использования современных цифровых технологий, возможностью применения, так называемых, технологий социальной инженерии на участниках программы. Для корпоративных программ возможности злоумышленника ограничены контрактной сущностью взаимодействия юридических лиц и реальной целью мошенника может стать задача по обналичиванию средств на АЗС или воздействие на данные в информационной системе, которое позволит, например, подставному юридическому лицу незаконно получить по картам топливо.

Борьбу с мошенничеством можно разделить на два больших направления борьба с мошенничеством на уровне конечного пользователя и противодействие мошенничеству на уровне информационных систем. Если первое направление это, как правило, совместная работа служб безопасности всех участников программ, включая и конечного пользователя, то второе направление это забота, в первую очередь, оператора, владельца информационной системы. Обеспечить безопасность данных в информационной системе - именно так представляют себе основную задачу безопасности в Топливной Процессинговой Компании, осуществляющей услуги процессинга по топливным программам и программам лояльности своих партнеров операторов сетей АЗС.

На каких этапах обработки данных по программам возможны неправомерные действия

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

Новые технологии на стороне клиента появляются постоянно, - объясняет Сергей Петраков, - К примеру, это мобильные приложения, виртуальные карты и другие инструменты, которые используются при покупке в качестве инструментов программы лояльности. С интеграцией каждого нового инструмента появляются новые уязвимости. Мошенники стремятся их обнаружить и создать механизмы, с помощью которых можно эти уязвимости использовать. Однако все эти способы мошенничества находятся на стороне клиента, за границей периметра наших систем, в которых хранятся данные. Поэтому мы можем бороться с потенциальными злоупотреблениями на стороне клиента только в определенных пределах. Используются для этого системы антифрода, специальных ограничений в правилах программы, обеспечивающих безопасность со стороны клиента. Это важно, но это не поможет, если мошенником окажется инсайдер, имеющий доступ к данным в базах на нашей стороне.

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

Выработка принципиального решения

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

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

Выбор средств, ограничивающих действия инсайдеров

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

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

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

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

Выбор между интегрированными средствами и внешними решениями

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

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

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

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

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

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

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

Какие интегрированные решения Oracle используют в ТПК

В качестве средства для шифрования данных клиентов программ лояльности в ТПК используют Oracle Advanced Security Transparent Data Encryption (TDE). Это решение обеспечивает возможность управления ключами шифрования и прозрачность шифрования конфиденциальных данных. В TDE задействован механизм шифрования на основе команд DDL. Он полностью исключает необходимость в изменении приложений и создании дополнительных программных средств для управления ключами шифрования, триггеров и представлений в базах данных. Шифрование дает возможность реализовать многоуровневую глубокую защиту. Данные защищены как при передаче, так и при хранении.

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

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

Результаты

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

Подробнее..

Этюд копированию баз данных PostgreSQL без использования pg_dump

14.05.2021 16:12:54 | Автор: admin

Постановка задачи

От бизнеса поступила задача необходимо регулярно сохранять копии отдельных баз данных. Упрощенно говоря бекапить отдельные базы данных, на случай сверки и потери данных в исходных базах.

Первое и самое очевидное решение pg_dump
Достоинства простота решения. Штатные методы. Все отработано, документации и материалов великое множество.
Но, достоинства есть продолжения недостатков.
Во первых-объемы дампов.
Во вторых-и это самое неприятное, были случаи несовпадения исходной и целевой БД при восстановлении из дампа.
В третьих-время, сначала на создание дампа, потом на восстановление БД из дампа.

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

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

Термины и исходные данные

Исходный кластер -кластер PostgreSQL содержащий БД которую нужно скопировать.
Исходная БД объект копирования, БД на исходном кластере
Клон БД копия исходной БД на исходном кластере
Кластер хранения копий БД отдельный кластер PostgreSQL.
Копия БД-целевая копия БД на кластере хранения копий БД

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

Шаг 1.

Создается клон БД, используя
CREATE DATABASE ... TEMPLATE = Исходная БД
фрагмент скрипта
CLONE_DB=$source_db_name'_'$timestamp_labelpsql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$source_db_name'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$source_db_name'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "CREATE DATABASE $CLONE_DB TEMPLATE=$source_db_name " >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = '$source_db_name'" >>$LOG_FILE 2>&1

Шаг 2.

Загружается схема клона БД, используя
pg_dump --shema_only --file=$DUMP_FILE ... 
фрагмент скрипта
pg_dump -h $source_host_name -U postgres --schema-only --verbose --file=$DUMP_FILE $source_db_name 2>$SCHEMA_DUMP_LOG_FILE

Шаг 3.

Создается пустая БД копия БД, используя
createdb ... 
фрагмент скрипта
createdb $CLONE_DB

Шаг 4.

Создается схема клона БД в копии БД, используя
psql ... < $DUMP_FILE
фрагмент скрипта
TARGET_SCHEMA_DUMP_LOG_FILE=$FILE_LABEL'target.log'psql -U postgres -d $CLONE_DB  < $DUMP_FILE > $TARGET_SCHEMA_DUMP_LOG_FILE 2>&1

Шаг 5.

Создание логической репликации.
Создание публикации в клоне БД, используя
CREATE PUBLICATION ... FOR ALL TABLES 
фрагмент скрипта
PUBLICATION_NAME=$CLONE_DB'_pub'psql -h $source_host_name -U postgres -d $CLONE_DB  -c "CREATE PUBLICATION $PUBLICATION_NAME FOR ALL TABLES " >>$LOG_FILE 2>&1

Создание подписки в копии БД, используя
CREATE SUBSCRIPTION ...
фрагмент скрипта
SUBSCRIPTION_NAME=$CLONE_DB'_sub'CONNECTION_STR="CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION 'host=$source_host_name port=5432 user=postgres dbname=$CLONE_DB' PUBLICATION $PUBLICATION_NAME"psql -U postgres -d $CLONE_DB  -c "$CONNECTION_STR" >>$LOG_FILE 2>&1

Шаг 6.Синхронизация клона БД и копии БД, используя
SELECT count(*) FROM pg_stat_subscription WHERE subname =... AND relid IS NOT NULL
БД считаются синхронизированными, если нет процесса синхронизации между таблицами. Клон БД для работы приложения не используется, что гарантирует идентичность баз.
фрагмент скрипта
flag=0while [[ $flag = '0' ]];do    COUNT_STR="SELECT count(*) FROM pg_stat_subscription WHERE subname ='$SUBSCRIPTION_NAME' AND relid IS NOT NULL "    subscription_process_count=`psql -At -U postgres -d $CLONE_DB -c "$COUNT_STR"`    if [[ $subscription_process_count = '0' ]];    then        break    fi    sleep 60done

Шаг 6.

Удалить логическую репликацию, используя
DROP SUBSCRIPTION...
фрагмент скрипта
psql -d $CLONE_DB -Aqt -c "DROP SUBSCRIPTION $SUBSCRIPTION_NAME" >> $LOG_FILE 2>&1
DROP PUBLICATION ...
фрагмент скрипта
psql -h $source_host_name  -d $CLONE_DB -Aqt -c "DROP PUBLICATION $PUBLICATION_NAME" >> $LOG_FILE 2>&1
в копии БД и клоне БД соответственно.

Шаг 7.

Удалить клон БД, используя
DROP DATABASE ...
фрагмент скрипта
psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$CLONE_DB'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "DROP DATABASE $CLONE_DB " >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1

Результат


Проведено копирование БД между серверами с минимальными издержками на передачу и хранение данных.
Подробнее..

Перевод Измеряем расходы на память у Postgres процессов

10.06.2021 18:12:02 | Автор: admin

Это вольный перевод поста одного из сильных разработчиков Postgres - Andres Freund. Кроме того что разработчик сильный, так еще и статья довольно интересная и раскрывает детали того как работает ОС Linux.

Довольно часто можно слышать заявления что постгресовые соединения используют слишком много памяти. Об этом часто упоминают сравнивая процессную модель обработки клиентских соединений с другой моделью, где каждое соединение обслуживается в отдельном потоке (thread).

Как по мне, здесь есть что обсудить. Кроме того можно сделать несколько улучшений и уменьшить использование памяти.

Я думаю что эта обеспокоенность оверхэдом на память вызвана одной общей причиной, это то что самый простой способ измерения потребления памяти через утилиты типа top и ps является довольно обманчивым.

И действительно, особенно довольно сложно измерить то как увеличивается используемая память при каждом новом соединении.

В этом посте я буду говорить о Postgres работающем на Linux, т.к. именно в этом направлении у меня больше всего опыта.

И перед тем как продолжить я хочу акцентировать внимание, что при точном и аккуратном измерении, одно соединение имеет накладные расходы на уровне меньше 2MiB (см. выводы в конце поста).

первый взгляд

Если использовать стандартные утилиты операционной системы, то можно сделать вывод, что накладные расходы существенно больше (чем есть на самом деле). Особенно если не использовать большие страницы (huge pages), то использование памяти каждый процессом действительно выглядит слишком большим. Отмечу что настоятельно рекомендуется использовать большие страницы. Давайте взглянем на только что установленное соединение, в только что запущенном Postgres:

andres@awork3:~$ psqlpostgres[2003213][1]=# SELECT pg_backend_pid(); pg_backend_pid         2003213 (1 row)andres@awork3:~/src/postgresql$ ps -q 2003213 -eo pid,rss    PID   RSS2003213 16944

Около 16MiB.

Утечки памяти!?! К счастью, нет.

При этом со временем памяти используется все больше и больше. Чтобы продемонстрировать это, я воспользуюсь расширением pgprewarm, чтобы загрузить таблицу в буфер (shared buffers):

postgres[2003213][1]=# SHOW shared_buffers ; shared_buffers  16GB           (1 row)postgres[2003213][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;  sum    383341 andres@awork3:~$ ps -q 2003213 -eo pid,rss    PID   RSS2003213 3169144

Теперь использование памяти достигло уровня 3GB. При том что, на самом деле, в этой сессии не потребовалось выделять дополнительную память. Объем используемой памяти, увеличился пропорционально объему используемого буфера:

postgres[2003213][1]=# SELECT pg_size_pretty(SUM(pg_relation_size(oid))) FROM pg_class WHERE relfilenode <> 0; pg_size_pretty  2995 MB        (1 row)

Что еще хуже, даже если эти страницы будут использовать в других сессиях, это также будет отображаться как использование большого объема памяти:

postgres[3244960][1]=# SELECT sum(abalance) FROM pgbench_accounts ; sum    0 (1 row)andres@awork3:~/src/postgresql$ ps -q 3244960 -eo pid,rss    PID   RSS3244960 2700372

Конечно, Postgres на самом деле не использует 3GB и 2.7GB памяти в данном случае. На самом деле, в случае huge_pages=off, утилита ps отображает объем разделяемой (shаred - память совместно используемая с другими процессами) памяти, включая и страницы в буфере которые используются в каждой сессии. Очевидно это приводит к значительной переоценке величины используемой памяти.

На помощь внезапно приходят большие страницы

Множество процессорных микро-архитектур обычно используют страницы размером 4KiB, но также могут использовать и страницы большего размера, например широко распространенный вариант это 2MiB.

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

Если повторить вышеописанный эксперимент с huge_pages=on, можно увидеть гораздо более приятные глазу результаты. Для начала, взглянем на "новый процесс":

andres@awork3:~$ ps -q 3245907 -eo pid,rss    PID   RSS3245907  7612

Теперь, новый процесс использует всего около 7MiB. Такое уменьшение вызвано тем что таблицы управления страницами (page table) теперь требуют меньше места, из-за того что используются большие страницы, для управления тем же объемом памяти нужно в 512 раз меньше элементов чем раньше (4KiB * 512 = 2MiB).

Теперь давайте посмотрим что произойдет при доступе к большим объемам данных в памяти:

postgres[3245843][1]=# ;SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;postgres[3245851][1]=# SELECT sum(abalance) FROM pgbench_accounts ;andres@awork3:~$ ps -q 3245907,3245974 -eo pid,rss    PID   RSS3245907 122603245974  8936

В отличие от самого первого эксперимента, эти процессы используют всего 12MiB и 9MiB соответственно, в то время как в прошлый раз использовалось 3GiB и 2.7GiB.

Разница довольно очевидна ;)

Это следствие того, как в Linux реализован учёт использования больших страниц, а не потому, что мы использовали на порядки меньше памяти: используемые большие страницы не отображаются как часть значения RSS в выводе ps и top.

Чудес не бывает

Начиная с версии ядра 4.5, появился файл /proc/$pid/status в котором отображается более подробная статистики об использование памяти процессом:

  • VmRSS общий размер используемой памяти. Значение является суммой трех других значений (VmRSS = RssAnon + RssFile + RssShmem)

  • RssAnon размер используемой анонимной памяти.

  • RssFile размер используемой памяти ассоциированной с файлами.

  • RssShmem размер используемой разделяемой памяти (включая SysV shm, сегменты в tmpfs и анонимные разделяемые сегменты)

andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3247901/statusRssAnon:    2476 kBRssFile:    5072 kBRssShmem:    8520 kBHugetlbPages:       0 kBpostgres[3247901][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;andres@awork3:~$ ps -q 3247901 -eo pid,rss    PID   RSS3247901 3167164andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3247901/statusRssAnon:    3148 kBRssFile:    9212 kBRssShmem: 3154804 kBHugetlbPages:       0 kB

RssAnon отображает объем "анонимной" памяти, т.е. участки рабочей памяти которые не являются отображанием файлов на диске. RssFile это как раз отображение в памяти конкретных файлов на диске, включая даже исполняемый файл postgres. И последнее RssShmem отображает доступную разделяемую память без учета больших страниц.

Это хорошо показывает причину того почему ps и подобные утилиты показывают большие значения используемой памяти - из-за того что учитывается разделяемая память.

И теперь взглянем на эту же статистику, но с huge_pages=on:

andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3248101/statusRssAnon:    2476 kBRssFile:    4664 kBRssShmem:       0 kBHugetlbPages:  778240 kBpostgres[3248101][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3248101/statusRssAnon:    3136 kBRssFile:    8756 kBRssShmem:       0 kBHugetlbPages:    3846144 kB

Увеличиваем точность

Однако даже если считать использование памяти без учета разделяемой памяти, это всё еще является переоценкой реального использования памяти. Тут есть две причины:

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

Второе, RssAnon также переоценивает использование памяти. Смысл тут в том что ps показывает всю память процесса целиком, при том что большая часть этой памяти в случае создания нового процесса делится между пользовательским соединением и памятью родительского процесса postgres (так же известен как postmaster). Это следует из того что Linux не копирует всю память целиком когда создает новый процесс (при выполнении операции fork()), вместо этого используется механизм Copy-on-Write для копирования в новый процесс, набора только измененных страниц.

Таким образом, пока нет хорошего способа аккуратно и точно измерить использование памяти отдельно взятого нового процесса. Но все не так плохо, начиная с версии 4.14 ядро предоставляет еще одну статистику (коммит с описанием) процесса в /proc/$pid/smaps_rollup файле. Pss показывает "принадлежащую процессу пропорциональную долю отображения" среди всех отображений этого процесса (детали можно найти в документации поиском по smaps_rollups и Pss которые сами по себе не имеют прямых ссылок). Для сегмента памяти используемого совместно между несколькими процессами, доля будет представлять собой отношение размера этого сегмента на количество процессов которые используют этот сегмент.

postgres[2004042][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;  sum    383341 (1 row)postgres[2004042][1]=# SHOW huge_pages ; huge_pages  off        (1 row)andres@awork3:~$ grep ^Pss /proc/2004042/smaps_rollupPss:             3113967 kBPss_Anon:           2153 kBPss_File:           3128 kBPss_Shmem:       3108684 kB

Pss_Anon включает в себя анонимную память используемую процессом, Pss_File включает память используемую под разделяемые библиотеки задействование процессом, и Pss_Shmem (если не используются большие страницы) показывает использование общей памяти разделенное на все процессы которые обращались к соответствующим страницам.

Но у пропорциональных значений есть небольшой недостаток, это использование делителя который зависит от числа подключений к серверу. Здесь я использовал pgbench (scale 1000, -S -M prepared -c 1024) чтобы создать большое число подключений:

postgres[2004042][1]=# SELECT count(*) FROM pg_stat_activity ; count   1030 (1 row)postgres[2004042][1]=# SELECT pid FROM pg_stat_activity WHERE application_name = 'pgbench' ORDER BY random() LIMIT 1;   pid    3249913 (1 row)andres@awork3:~$ grep ^Pss /proc/3249913/smaps_rollupPss:                4055 kBPss_Anon:           1185 kBPss_File:              6 kBPss_Shmem:          2863 kB

И с использованием huge_pages=on:

andres@awork3:~$ grep ^Pss /proc/2007379/smaps_rollupPss:                1179 kBPss_Anon:           1173 kBPss_File:              6 kBPss_Shmem:             0 kB

Как я понимаю Андрес намекает здесь на то что из-за большого количества процессов и соответственно большого делителя, значение Pss_Shmem становится маленьким, отчего может складываться впечатление что процесс использует мало разделяемой памяти хотя по факту мог использовать ее гораздо больше (Прим. переводчика).

К сожалению Pss значения учитывают только те ресурсы, что видны приложению. Например, размер таблицы страниц не учитывается. Размер таблицы страниц можно увидеть в уже упоминавшемся `/proc/$pid/status`.

Я не уверен, но насколько я знаю, VmPTE (размер таблицы страниц) полностью приватный для каждого процесса, но остальное большинство Vm* значений, включая стек VmStk являются общими через copy-on-write.

Учитывая всё это, накладные расходы с учетом таблицы страниц и с huge_pages=off:

andres@awork3:~$ grep ^VmPTE /proc/2004042/statusVmPTE:      6480 kB

и с huge_pages=on:

VmPTE:     132 kB

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

Вывод

На основе проделанных измерений, мы можем представить что процесс выполняющий достаточно простую read-only OLTP нагрузку имеет накладные расходы около 7.6MiB с huge_pages=off и около 1.3MiB с huge_pages=on включая Pss_Anon в VmPTE.

Даже если представить что есть некий "невидимый" оверхэд, и большой объем данных в буфере и т.д., я думаю мы вернемся к моему раннему утверждению что накладные расходы на соединение меньше чем 2MiB.

Дополнение от переводчика. В версии Postgres 14 появилось новое представление pg_backend_memory_contexts которое показывает подробную утилизацию памяти текущим процессом с точки зрения самого Postgres.

Подробнее..

Перевод Как автоматизировать развертывание баз данных с помощью Liquibase?

12.05.2021 20:07:31 | Автор: admin

Перевод материала подготовлен в рамках курса Экспресс-курс по управлению миграциями (DBVC).


Liquibase это инструмент управления изменениями в базе данных. С его помощью вы можете отслеживать изменения в базе данных, сделанные с помощью SQL (или XML) скриптов. Эти скрипты могут быть добавлены в системы контроля версий, такие как git.

В данной статье мы рассмотрим, как автоматизировать развертывание баз данных с помощью Liquibase. Автоматизация может быть выполнена с помощью

1. Пайплайн Jenkins

2. Shell-скриптов

Я не буду говорить ни об одном из этих двух способов, поскольку это не является темой данной статьи только для того, чтобы все было просто и понятно.

Существует два способа записи наборов изменений в Liquibase, т.е. XML и SQL. Я выбрал SQL, чтобы избежать записи соответствующих изменений XML для каждого выполняемого нами обновления SQL. Таким образом, тот же SQL-файл, который мы пишем для разработки, используется для развертывания Liquibase.

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

Выполните приведенные ниже шаги:

Создать файл changelog (журнал изменений)

Создать XML-файл с именем liquibase-changelog.xml (имя может быть любым!) со следующим содержимым:

<?xml version="1.0" encoding="UTF-8"?><databaseChangeLog xmlns="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangelog"xmlns:xsi="http://personeltest.ru/away/www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangeloghttp://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"><include file="<path to changeset SQL file>/<changeset file name>.sql>" relativeToChangelogFile="true"/>...more <include> tags goes here...</databaseChangeLog>

Обратите внимание на тег include в приведенном выше XML. Каждый файл SQL changeset (набор изменений), который должен отслеживаться Liquibase, должен быть зарегистрирован в этом файле changelog (журнал изменений).

Создание наборов изменений (changeset) SQL

Добавьте файлы SQL changeset в выбранное вами место. Синтаксис SQL, который работает с Liquibase, следующий:

--liquibase formatted sql--changeset <author name>:<a unique identifier for the SQL changeset><SQL statements go here><SQL statements go here>--rollback <rollback SQL statements>--rollback <rollback SQL statements>

Рассмотрим пример:

--liquibase formatted sql--changeset xameeramir:create-test-tableCREATE TABLE IF NOT EXISTS testTable(columnName1 VARCHAR (355));--rollback DROP TABLE--rollback testTable

Обратите внимание, что файл SQL changeset отличается от файла XML changelog.

Регистрация SQL changeset в XML-файле changelog

Включите файл SQL changeset в файл changelog, который мы создали ранее, со следующими тегами XML:

<include file=<path to SQL changeset file>/<changeset file name>.sql relativeToChangelogFile="true" />

Добавьте столько SQL changesets и зарегистрируйте их в файле changelog, сколько вам нужно.

Триггер в Liquibase для обновления базы данных

Просто выполните приведенную ниже команду:

liquibase --changeLogFile=<path to changelog file>/<liquibase changelog file name>.xml --username=<database username> --password=<database password> --classpath=<path to the liquibase installation>/postgresql-42.2.5.jar --url=jdbc:postgresql://<database url>/<database name> update

Classpath (путь к классам) - это драйвер JDBC, который мы настроили в предыдущей публикации. Postgresql-42.2.5.jar - это JDBC-драйвер, предназначенный для Postgres, и его можно будет заменить на базу данных по вашему выбору без каких-либо специальных преобразований на этих этапах.

Приведенная выше команда может быть использована в shell-скриптах или в пайплайне CI/CD для запуска обновлений базы данных.

Автоматизация CI/CD

После того, как вышеуказанная конфигурация установлена - автоматизация может быть выполнена либо на клиенте с помощью shell-скриптов, либо на сервере с помощью shell-скриптов или имплементации CI/CD.

Предположим, что имплементация CI/CD, которая запускает развертывание Liquibase, означает выполнение команды триггер (trigger) Liquibase, приведенной выше, при каждом git push в ветку DEVELOP (или любую другую).

Первым предварительным условием будет наличие файла liquibase-changelog.xml. Допустим, мы сохраним его на уровне ~/ с операторами include, указывающими на папку, в которой находятся changeset SQL. Следующий рабочий процесс позволит автоматизировать развертывание базы данных с помощью пайплайна CI/CD:

  • Поместите файл SQL changeset в репозиторий функций.

  • Отправьте запрос на исправление для ветки DEVELOP

  • После достоверной проверки и согласования объедините ветку feature с веткой DEVELOP.

  • Имплементация CI/CD, настроенная на сервере DEVELOP, запустит Liquibase для обновления базы данных.

  • Liquibase автоматически будет выполнять только новые файлы (любые уже выполненные файлы не будут запущены повторно).

Автоматизация с помощью shell-скриптов

В shell-скриптах будет записана одна и та же команда триггер Liquibase. Как только shell-скрипты будут выполнены, содержащие их changeset (наборы изменений) Liquibase будут выполнены автоматически.

Вы можете задаться вопросом, как shell-скрипты узнают, когда выполнять команду? Ответ прост:

  • Shell-скрипты могут выполняться на триггерах cron.

  • Shell-скрипты могут быть выполнены при некоторых системных событиях.

Выбор за вами!


Узнать подробнее об экспресс-курсе по управлению миграциями (DBVC)

Подробнее..

Управление и оптимизация баз данных в ApexSQL анонс вебинара

18.05.2021 22:22:31 | Автор: admin
ApexSQL это комплексный набор инструментов, который оптимизирует и автоматизирует процессы управления базами данных SQL Server и разработки, а также обеспечивает безопасность и соответствие требованиям. В одной из прошлых статей мы описывали бесплатные и платные инструменты ApexSQL (там и правда есть из чего выбрать).

image

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

Под катом список решений ApexSQL с кратким описанием и ссылками на соответствующие страницы на сайте вендора.


  1. ApexSQL Compare инструмент для сравнения SQL-кода, файлов и папок. Работает также в качестве расширения для SSMS или Visual Studio.
  2. ApexSQL Decrypt потоковая дешифрация объектов в SQL Server: процедур, функций, триггеров и представление (view). Работает также в качестве расширения для SSMS или Visual Studio.
  3. ApexSQL Discover обнаружение экземпляров SQL Server и сопутствующих сервисов SSRS, SSAS и SSIS.
  4. ApexSQL Refactor инструмент для рефакторинга и форматирования SQL-кода. Работает в качестве расширения для SSMS или Visual Studio.
  5. ApexSQL Model создание диаграмм объектов SQL Server. Работает также в качестве расширения для SSMS или Visual Studio.
  6. ApexSQL Plan инструмент для оптимизации Execution plans. Работает также в качестве расширения для SSMS.
  7. ApexSQL Complete инструмент автоматически завершает операторы SQL и позволяет добавлять собственные сниппеты (сочетания клавиш для автозаполнения). Работает также в качестве расширения для SSMS или Visual Studio.
  8. ApexSQL Propagate инструмент для исполнения SQL-кода на нескольких БД за один раз.
  9. ApexSQL Search утилита для поиска данных и объектов в недрах SQL Server. Работает в качестве расширения для SSMS или Visual Studio.
  10. ApexSQL DevOps Toolkit инструмент для создания CI/CD пайплайнов. Единственный из всех перечисленных тут продуктов имеет веб-консоль.
  11. ApexSQL Audit инструмент для аудита БД на соответствие требованиям безопасности, в т.ч. поддерживаются HIPAA, GDPR, PCI. Поддерживаются отчёты и просмотр истории изменений.
  12. ApexSQL Backup автоматизация создания инкрементального бэкапа, лога транзакций и полного бэкапа. Поддерживается восстановление на определённый момент во времени, можно создавать шаблоны для создания бэкапа и гибко настраивать планы бэкапов.
  13. ApexSQL Defrag утилита для мониторинга и управления дефрагментацией.
  14. ApexSQL Job инструмент для управления заданиями, включая историю, расписание и уведомления.
  15. ApexSQL Log инструмент для чтения лога транзакция для аудита, репликации или отката изменений.
  16. ApexSQL Recover восстановление повреждённых, удалённых или потерянных данных.
  17. ApexSQL Analyze инструмент для анализа связей в БД.
  18. ApexSQL Build инструмент для автоматизации создания БД. Может подключаться к системам контроля версий.
  19. ApexSQL Enforce улучшатель SQL-кода.
  20. ApexSQL Generate инструмент для генерации миллионов строк данных за один клик. Поддерживается экспорт тестовых данных в SQL, XML, CSV, JSON и Excel.
  21. ApexSQL Mask инструмент для поиска, классификации и маскирования чувствительных данных в БД. Имеет 220+ предопределённых масок и 55+ встроенных фильтров для классификации.
  22. ApexSQL Script инструмент для создания DDL и DML скриптов и исполняемых инсталляционных пакетов.
  23. ApexSQL Source Control инструмент для интеграции систем контроля версий с SSMS.
  24. ApexSQL Trigger аудит данных в БД и трансляция в DML.
  25. ApexSQL Unit Test инструмент для выполнения юнит-тестов напрямую из консоли SSMS.


Чтобы задать вопросы по продуктам ApexSQL, получить на тестирование коммерческие продукты или уточнить цену, оставьте запрос в форме обратной связи у нас на сайте или другим способом.



А ещё у нас есть:

Интерфейсы для мониторинга производительности популярных БД в Foglight for Databases

Быстрая локализация проблем производительности Microsoft SQL Server в Quest Foglight

10 бесплатных утилит ApexSQL для управления базами данных Microsoft SQL Server

Как не превратиться в стрекозу, если у вас много разных баз данных

Подписывайтесь на нашу группу в Facebook и канал в Youtube.
Подробнее..

Управление и оптимизация баз данных SQL Server в ApexSQL анонс вебинара

19.05.2021 00:17:20 | Автор: admin
ApexSQL это комплексный набор инструментов, который оптимизирует и автоматизирует процессы управления базами данных SQL Server и разработки, а также обеспечивает безопасность и соответствие требованиям. В одной из прошлых статей мы описывали бесплатные и платные инструменты ApexSQL (там и правда есть из чего выбрать).

image

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

Под катом список решений ApexSQL с кратким описанием и ссылками на соответствующие страницы на сайте вендора.


  1. ApexSQL Compare инструмент для сравнения SQL-кода, файлов и папок. Работает также в качестве расширения для SSMS или Visual Studio.
  2. ApexSQL Decrypt потоковая дешифрация объектов в SQL Server: процедур, функций, триггеров и представление (view). Работает также в качестве расширения для SSMS или Visual Studio.
  3. ApexSQL Discover обнаружение экземпляров SQL Server и сопутствующих сервисов SSRS, SSAS и SSIS.
  4. ApexSQL Refactor инструмент для рефакторинга и форматирования SQL-кода. Работает в качестве расширения для SSMS или Visual Studio.
  5. ApexSQL Model создание диаграмм объектов SQL Server. Работает также в качестве расширения для SSMS или Visual Studio.
  6. ApexSQL Plan инструмент для оптимизации Execution plans. Работает также в качестве расширения для SSMS.
  7. ApexSQL Complete инструмент автоматически завершает операторы SQL и позволяет добавлять собственные сниппеты (сочетания клавиш для автозаполнения). Работает также в качестве расширения для SSMS или Visual Studio.
  8. ApexSQL Propagate инструмент для исполнения SQL-кода на нескольких БД за один раз.
  9. ApexSQL Search утилита для поиска данных и объектов в недрах SQL Server. Работает в качестве расширения для SSMS или Visual Studio.
  10. ApexSQL DevOps Toolkit инструмент для создания CI/CD пайплайнов. Единственный из всех перечисленных тут продуктов имеет веб-консоль.
  11. ApexSQL Audit инструмент для аудита БД на соответствие требованиям безопасности, в т.ч. поддерживаются HIPAA, GDPR, PCI. Поддерживаются отчёты и просмотр истории изменений.
  12. ApexSQL Backup автоматизация создания инкрементального бэкапа, лога транзакций и полного бэкапа. Поддерживается восстановление на определённый момент во времени, можно создавать шаблоны для создания бэкапа и гибко настраивать планы бэкапов.
  13. ApexSQL Defrag утилита для мониторинга и управления дефрагментацией.
  14. ApexSQL Job инструмент для управления заданиями, включая историю, расписание и уведомления.
  15. ApexSQL Log инструмент для чтения лога транзакция для аудита, репликации или отката изменений.
  16. ApexSQL Recover восстановление повреждённых, удалённых или потерянных данных.
  17. ApexSQL Analyze инструмент для анализа связей в БД.
  18. ApexSQL Build инструмент для автоматизации создания БД. Может подключаться к системам контроля версий.
  19. ApexSQL Enforce улучшатель SQL-кода.
  20. ApexSQL Generate инструмент для генерации миллионов строк данных за один клик. Поддерживается экспорт тестовых данных в SQL, XML, CSV, JSON и Excel.
  21. ApexSQL Mask инструмент для поиска, классификации и маскирования чувствительных данных в БД. Имеет 220+ предопределённых масок и 55+ встроенных фильтров для классификации.
  22. ApexSQL Script инструмент для создания DDL и DML скриптов и исполняемых инсталляционных пакетов.
  23. ApexSQL Source Control инструмент для интеграции систем контроля версий с SSMS.
  24. ApexSQL Trigger аудит данных в БД и трансляция в DML.
  25. ApexSQL Unit Test инструмент для выполнения юнит-тестов напрямую из консоли SSMS.


Чтобы задать вопросы по продуктам ApexSQL, получить на тестирование коммерческие продукты или уточнить цену, оставьте запрос в форме обратной связи у нас на сайте или другим способом.



А ещё у нас есть:

Интерфейсы для мониторинга производительности популярных БД в Foglight for Databases

Быстрая локализация проблем производительности Microsoft SQL Server в Quest Foglight

10 бесплатных утилит ApexSQL для управления базами данных Microsoft SQL Server

Как не превратиться в стрекозу, если у вас много разных баз данных

Подписывайтесь на нашу группу в Facebook и канал в Youtube.
Подробнее..

Oracle diagnostic events Cheat sheet

20.05.2021 02:20:23 | Автор: admin

Oracle diagnostic events - это очень мощное средство, но, к сожалению, слабо документированное, поэтому я решил перечислить и свести воедино несколько неизвестных или малоизвестных способов его использования.

Единственная его документация - это очень краткая встроенная документация, доступная по команде oradebug doc в SQL*Plus, и она отсутствует в официальной документации. Вы можете встретить только отрывки из нее в разных блогах, что не очень удобно, поэтому я скомпилировал ее полностью: http://orasql.org/files/events/

Современный синтаксис и несколько простых примеров приведены в oradebug doc event. Я их здесь приводить не буду и начну сразу с примеров.

alter system set events    'kg_event[1476]        {occurence: start_after 1, end_after 3}            trace("stack is: %\n", shortstack())            errorstack(2)    ';
  1. kg_event[errno] - это Kernel Generic event из библиотеки Generic, инструктирующее сработать на ошибку с номером errno;

  2. {occurence: start_after X, end_after Y} - это один из фильтров , инструктирующий пропустить X срабатываний данного event и выполниться Y раз;

  3. trace(format, str1, str2, ..., str15) - это функция из ACTIONS для вывода в трейс-файл своих сообщений;

  4. shortstack() - это функция из ACTIONS , возвращающая call stack в кратком виде;

  5. errorstack(level) - это функция из ACTIONS, выводящая в трейс-файл расширенную информацию (level: 0 - только errorstack, 1 - errorstack + call stack, 2 - как level=1 + processtate, 3 - как level=2 + context area). Еще более расширенную информацию можно получить с помощью PROCESSSTATE или SYSTEMSTATE. Если нужен только call stack можно воспользоваться CALLSTACK(level) - при level>1 запишет и аргументы.

alter system set events     'trace[SQL_Compiler.* | SQL_Execution.*]        [SQL: ...]            {process: ospid = ...}            {occurence:end_after 3}                controlc_signal()';
  1. trace[component] - это основной диагностический event, позволяющий указать компоненты, внутри которых надо срабатывать. В данном случае, я указал срабатывать внутри всех дочерних функций в SQL_Compiler и SQL_Execution. Например, RDBMS.SQL_Compiler.SQL_Optimizer.SQL_Transform.* указало бы срабатывать только в функциях трансформации запросов.

  2. SQL[SQL: sqlid ] - это единственный SCOPE в библиотеке RDBMS, позволяющий отфильтровать все события, связанные с указанными запросами, включая события его рекурсивных запросов(например, если это sql_id PL/SQL вызова, то будут оттрассированы все запросы внутри него, или для запроса - все его внутренние запросы во время парсинга и оптимизации, внутренних запросов PL/SQL функций и тд.);

  3. {process: ...} - это фильтр, позволяющий указать процессы для которых данный event будет включен.

  4. controlc_signal - это ACTION, вызывающий ошибку "ORA-01013: user requested cancel of current operation", т.е. сессия запустившая этот запрос получит эту ошибку, как будто она сама прервала выполнение запроса.

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

alter system set events     'sql_trace {process: ospid = ...}      trace("sqlid(%): %\n", sqlid(), evfunc())     ';

sql_trace - это старый добрый event 10046, а целиком команда предписывает при каждом событии инструментированным этим event 10046, вывести функцию, его вызвавшую(evfunc) и sqlid запроса (ACTION sqlid).

Пример:

включаем eventвключаем eventсначала выполняем запрос с настройками по умолчанию, а затем с _rowsource_statistics_sampfreq=1сначала выполняем запрос с настройками по умолчанию, а затем с _rowsource_statistics_sampfreq=1Разница в трейсе заметнаРазница в трейсе заметна

Как видите, при "_rowsource_statistics_sampfreq" =1 инструментировано намного больше событий: 26 против 12! Подробнее тут.

alter system set events     'wait_event["enq: TM - contention"]        {wait: minwait=1000}             errorstack(1)            trace("event=[%] sqlid=%, ela=% p1=% p2=% p3=%\n",                    evargs(5), sqlid(), evargn(1), evargn(2), evargn(3), evargn(4))    ';
  1. wait_event[name] - event, срабатывающий по имени событий ожидания (wait events), имена и их параметры вы можете посмотреть в v$event_name:
    select wait_class,name,parameter1,parameter2,parameter3 ,display_name from v$event_name

  2. {wait: ... } - фильтр позволяющий отфильтровать как по долготе ожидания(в мс), так и по параметрам P1, P2, P3. Например, для указанного ожидания "TM-contention", P2 - это object #, и можно указать {wait: minwait=1000; p2=12345}, т.е. только ожидания табличной блокировки на таблице с object_id=12345 и длившиеся дольше 1 сек.

  3. evargX() - это функции из ACTION, возвращающие аргументы event-check события, где 1-й аргумент это elapsed time(ms), 2-4 - p1-p3, 5-й - имя ожидания. Соответствующие функции имеет и kg_event: errargX.

Или еще пример, когда вам надо узнать какие сессионные переменные были изменены. Допустим, кто-то забыл указать nls-параметры в to_number, on conversion error не указан, и какие-то сессии периодически получают ORA-01722: invalid number:

-- допустим, изначально должны стоять следующие параметры:SQL> alter session set nls_numeric_characters='.,';Session altered.-- и все работает нормальноSQL> select to_number('1,200.3','999g999d999') + 10 from dual;TO_NUMBER('1,200.3','999G999D999')+10-------------------------------------                               1210.3-- до тех пор, пока в какой-то момент они не меняются:SQL> alter session set nls_numeric_characters=q'[.']';'Session altered.-- соответственно вылетает ошибка:SQL> select to_number('1,200.3','999g999d999') + 10 from dual;select to_number('1,200.3','999g999d999') + 10 from dual                 *ERROR at line 1:ORA-01722: invalid number

Хотя нет никакой вьюхи для получения параметров чужой сессии, не входящих в v$ses_optimizer_env, мы можем легко их получить с помощью MODIFIED_PARAMETERS():

alter system set events     'kg_event[1722]         {process: ospid=27556}        {occurence:end_after 1}            MODIFIED_PARAMETERS()';

И благодаря тому, что сейчас есть удобные v$diag_alert_ext - для доступа к alert.log, v$diag_trace_file_contents - для доступа к трейс-файлам, мы можем все получить простым запросом:

select c.payloadfrom v$diag_trace_file_contents cwhere 1=1  and c.session_id   = ... -- sid сессии  and c.serial#      = ... -- serial#  and c.section_name = 'Error Stack' -- параметры будут в секции Error Stack--  and c.payload like '%nls_numeric%' -- можно отфильтровать именно  and c.timestamp>systimestamp-interval'15'minute;-- результат:DYNAMICALLY MODIFIED PARAMETERS:  nls_language             = "AMERICAN"  nls_territory            = "AMERICA"  nls_sort                 = "BINARY"  nls_date_language        = "AMERICAN"  nls_date_format          = "yyyy-mm-dd hh24:mi:ss"  nls_currency             = "$"  nls_numeric_characters   = ".'"  nls_iso_currency         = "AMERICA"  nls_calendar             = "GREGORIAN"  nls_time_format          = "hh24:mi:ssxff"  nls_timestamp_format     = "yyyy-mm-dd hh24:mi:ssxff"  nls_time_tz_format       = "hh24:mi:ssxff TZR"  nls_timestamp_tz_format  = "yyyy-mm-dd hh24:mi:ssxff TZR"  nls_dual_currency        = "$"  nls_comp                 = "BINARY"  local_listener           = ""

PS. Дополнительные детали вы можете узнать в статьях Tanel Poder:
1. The full power of Oracles diagnostic events, part 1: Syntax for KSD debug event handling
2. The full power of Oracles diagnostic events, part 2: ORADEBUG DOC and 11g improvements
3. What Caused This Wait Event: Using Oracle's wait_event[] tracing
4. http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc

О внутренних функциях у Frits Hoogland: http://orafun.info/

Полная внутренняя документация по oradebug/diagnostic events: http://orasql.org/files/events

Подробнее..

Автоматизация в центрах обработки данных

17.05.2021 12:14:39 | Автор: admin

В большинстве серверов HPE имеется встроенный контроллер управления Integrated Lights Out (iLO). Его первоначальное назначение удаленное управление сервером:
включение/выключение, перехват графической консоли, подключение медиа-устройств что и иллюстрирует название Lights-Out Свет выключен в ЦОД, где трудятся серверы HPE, администратору нет необходимости быть рядом. Все действия с серверами можно выполнить из любой точки мира. Функционал iLO постоянно расширялся и сейчас его можно назвать центром управления полетом сервера, фактически это мини-компьютер внутри большого компьютера. У iLO есть процессор, оперативная и флэш-память, Ethernet порт и, естественно, интерфейс управления: веб-браузер, командная строка, скрипты и программируемые интерфейсы REST API. Через REST API и осуществляется автоматизированное управление серверами HPE в соответствии со стандартом Redfish, пришедшим на смену устаревшему IPMI.

Следующий уровень управления инфраструктурой HPE программный продукт HPE OneView. Для стоечных и блейд-серверов c-Class это виртуальная машина (поддерживаются все основные гипервизоры), для платформы HPE Synergy это аппаратный модуль Composer. Управляется HPE OneView аналогично iLO: веб-интерфейс, скрипты и команды RESTful API / Redfish.

HPE OneView обеспечивает полное низкоуровневое управление серверной инфраструктурой: настройка BIOS, конфигурирование сетевых интерфейсов, подключение к SAN, создание томов на внешних системах хранения (HPE Primera, HPE Nimble, HPE 3PAR), контроль и обновление драйверов и микропрограмм. Все эти действия можно выполнять для одного или нескольких серверов, как используя консоль браузера, так и с помощью скриптов PowerShell или Python. Но самых интересных результатов можно достичь путем интеграции OneView со средствами развертывания операционных систем и приложений. В этом случае администратору вообще не нужно обращаться к OneView, все необходимые действия выполняются в вышестоящей консоли управления. Например, для среды VMware единой консолью служит vCenter, для Microsoft Windows Admin Center.

REST и Redfish

Если говорить о REST (Representational State Transfer), то он представляет собой обычный HTTP(s) запрос, передавая необходимые данные в качестве параметров запроса. В отличие от Redfish (более современной версии REST), REST никак не стандартизирован, он лишь является архитектурным стилем, позволяющим придерживаться определенных последовательностей, таких как запрос, тело запроса и параметры запроса. При этом какое дерево запроса, по какому стандарту передается тело запроса (JSON, XML или другой), каждый производитель решает на свое усмотрение. В итоге это привело к тому, что если пользователь работал с REST-интерфейсами от нескольких вендоров, то к ним требовался разный подход, а часто и разный набор кода, что не позволяло масштабировать решения основанные на REST-запросах.

В отличие от REST, Redfish является стандартизированным интерфейсом и позволяет работать пользователю с разными производителями с одинаковым подходом и тем самым, обеспечивать масштабируемость решения. В решениях HPE стандарт Redfish появился в ILO4 (v2.30) и более поздних продуктах.

Решение HPE по автоматизации (Deployment Automation Solution)

При преодолении определенной численности информационных систем и нарастании запросов со стороны бизнеса, сотрудники отдела сопровождения часто приходят к целесообразности автоматизации ежедневных, рутинных операций. Это помогает вводить и обслуживать информационные системы быстрее, а сотрудникам сконцентрироваться на более важных задачах. Обычно администраторы пытаются автоматизировать задачи собственными силами и привычными им средствами. В этом им помогает широкий набор инструментов (https://github.com/hewlettpackard/), таких как Ansible, Python, Puppet и других. При этом, как правило самостоятельно написанные скрипты приходится часто править, особенно при масштабировании решения в условиях продуктивной среды.

Deployment Automation Solution представляет собой услугу по настройке решения автоматизации ежедневных операций, построенного на открытом исходном коде, либо их коммерческих аналогов, которые уже используются на предприятии. Таким образом потребители решения смогут вносить правки, как в само решение, делая его более заточенным под конкретную организацию, так и добавлять в решение собственные наработки, тем самым расширяя функционал базового решения. Deployment Automation Solution основан на стандартных программных компонентах, таких как Ansible, для автоматизации и оркестрации, Nginx для предоставления библиотеки образов микрокодов, операционных систем и файлов конфигураций и GitLab как способ централизованной доставки скриптов и Ansible playbooks. Таким образом, использование стандартных отраслевых инструментов с помощью сценариев Ansible, связанных с базовым репозиторием кода и инфраструктурным конвейером DevOps с помощью GitLab делает подход системным, а масштабирование удобным. Большинство компонент работает в контейнерах, что дает возможность быстро развертывать и обновлять само решение.

Весь процесс взаимодействия построен на программно-определяемой инфраструктуре посредством использования существующих богатых функций OneView Ansible collection / REST API, предназначенных для среды управления HPE OneView, или Redfish / iLO для управляемых сред, без OneView, а также API-интерфейсов хранилищ данных. Также используются API конкретного программного производителя, например:

  • Ansible Playbooks от RHEL могут связываться с серверами Linux через SSH;

  • Подключение к VMware может осуществляться через REST API или SSH;

  • Связь с Windows может быть через WinRM.

Для удобства оркестрации решения и построения сложных рабочих процессов автоматизации используется AWX (https://github.com/ansible/awx, upstream project для Ansible Tower) или Ansible Automation Platform (https://www.ansible.com/integrations/infrastructure/hpe), для объединения и инкапсуляции атомарной инфраструктуры в виде базовых операций кода в рабочие процессы автоматизации для реализации задач выделения ресурсов и управления жизненным циклом. Этот модульный подход позволяет гибко настраивать интеграцию аппаратных и программных элементов решения. При этом AWX, или Ansible Automation Platform не является обязательным атрибутом. Все рабочие процессы доступны через REST API и могут быть вызваны из любого существующего решения, такого как HPE ServiceNow или Morpheus. решение достаточно гибкое, чтобы интегрироваться с порталом управления, выбранным каждым клиентом, и обеспечивает интеграцию с любым сторонним решением.

В базовом варианте решения HPE предоставляет два основных сценария использования:

  • Установка операционных систем, включая настройку аппаратных компонент: презентация томов СХД, настройка зонинга для SAN и т.д, а также программных компонент, включая настройки безопасности и манипуляции с ПО;

  • Обновление микрокодов, в том числе и без прерывания работы серверов.

При этом заказчик получит доступ к репозиторию с обновлениями playbooks для поддержки новых сценариев и компонент. Из коробки решение заточено под аппаратную платформу HPE, включая новейшие аппаратные ресурсы: DL Gen10, Synergy, Apollo и 3PAR / Primera / Nimble, однако открытый исходный код и поддержка разнообразных API позволит пользователям интегрировать решения сторонних производителей.

Заключение

Инфраструктура Hewlett Packard Enterprise предоставляет широкие возможности по автоматизации ежедневных ИТ-операций: подготовка и развертывание новых серверов, подключение к сетям передачи данных и системам хранения, установка операционных систем, контроль и обновление драйверов и firmware, реконфигурирование систем под изменяющиеся требования приложений. Заказчики сами выбирают уровень автоматизации: от простых сценариев групповых операций с iLO до решений Инфраструктура как код с HPE OneView и Ansible AWX / Automation Platform.

Подробнее..

Перевод Самые популярные базы данных 20062021гг

31.05.2021 18:12:13 | Автор: admin

(статья обновлена в мае 2021г.)

Какие системы управления базами данных (СУБД) распространены в мире больше всего? Как они изменились с 2006года и какие входят в десятку самых популярных? В этой статье мы проанализируем базы данных, которые были на пике популярности с 2006 по 2021год. Данные обновляются каждый месяц. Подробнее в индексе ведущих баз данных TOPDB. Итак, рассмотрим самые популярные базы данных с 2006 по 2021год.

15 самых популярных баз данных с 2006 по 2021год

Какая база данных стала самой популярной в 2021году? Согласно рейтингу БД, это Oracle. Этой базой данных пользуются 30,2% респондентов. В два раза меньше респондентов используют MySQL (16,65%) и SQL Server (13,21%) второе и третье места соответственно. В совокупности на долю этих трех СУБД приходится более 62% общего числа пользователей. На четвертой строчке расположилась СУБД Microsoft Access 9%. На долю баз данных, занявших пятое и последующие места, приходится менее 5%.

При этом Oracle занимает то же положение, что и 15лет назад. В мае 2006года этой СУБД пользовались 31,8% респондентов. На втором месте была MySQL 24,5%. В совокупности этими двумя базами данных в 2006году пользовались более 55% респондентов. Третью строчку в 2006году занимала СУБД Microsoft Access. Тогда ее использовали 17,6% респондентов, но в 2021году их количество сократилось почти вдвое и составило 9,07%. СУБД SQL Server с тех пор поднялась на одну позицию, и хотя ее показатель по-прежнему составляет около 13%, ей удалось обойти Access.

Рейтинг баз данных DB-Engines май 2021года

В мае 2021года лидером рейтинга DB-Engines остается Oracle. За ней следует MySQL, которая набрала 1236баллов, и Microsoft SQL Server 992,66балла.

Рейтинг DB-Engines март 2021года: Визуализация данных через платформу Flourish

Мы рассмотрели самые популярные базы данных в рейтинге TOPDB. TOPBD рассчитывает показатель так: Индекс ведущих баз данных TOPDB основывается на анализе частоты поисковых запросов в Google, содержащих названия баз данных. Но какие базы данных наиболее популярны в мире по версии DB-Engines?

На первых трех строчках размещаются все те же СУБД. Лидирует Oracle (1321,73балла), на втором месте MySQL (1254,83балла), далее Microsoft SQL Server (1015баллов). Но начиная с четвертой строки рейтинг меняется: по версии DB-Engines четвертой самой популярной в мире СУБД стала PostgreSQL, которая набрала 549,29балла.

Рейтинг DB-Engines Топ 10 наиболее популярных баз данных март 2021года: Визуализация данных через платформу Flourish

Еще один интересный пример: в TOPDB Microsoft Access занимает четвертое место, но в рейтинге DB-Engines Access набирает 118,14балла. В десять раз меньше, чем Oracle. (Подробнее о том, как рассчитываются показатели БД в этом рейтинге, можно прочитать по ссылкеhttps://db-engines.com/en/ranking_definition.)

Самые быстрорастущие базы данных в прошлом году

Какие из 50 баз данных проявили себя лучше других в прошлом году, а какие не продемонстрировали блестящих результатов? Начнем с хорошего. Microsoft Azure SQL Database, PostgreSQL, Mongo DB и Snowflake показали высокий рост. Из них наибольший рост продемонстрировала СУБД Microsoft Azure (35,44%), а наименьший Snowflake (+20,77%). Показатели неплохо поднялись у Google BigQuery, Redis и Amazon DynamoDB. Среди них самый высокий рост наблюдался у BigQuery (+8,51%), а наименьший у Amazon DynamoDB (+6,38%).

Рейтинг DB-Engines Топ 50 наиболее популярных баз данных март 2021года: Визуализация данных через платформу Flourish

Наибольшую отрицательную динамику показали три базы данных: Microsoft SQL Server (82,55%), Oracle (18,91%) и Hive (9,34%). Однако некоторые из баз данных, показатели которых ухудшились по сравнению с показателями марта, по-прежнему занимают лидирующие позиции в общем рейтинге. Oracle, MySQL и Microsoft SQL самые популярные в мире базы данных в среднем потеряли по 35,55%.

Выше представлена интерактивная таблица рейтинга DB-Engines (ссылки на официальные данные можно найти здесь: https://db-engines.com/en/ranking). Вы можете посмотреть данные для разных столбцов.

Источники и полезные ссылки

Работая над этой статьей, я использовал несколько источников, в том числе рейтинги TOPDB и DB-Engines. Ссылки на источники указаны в статье.

Видео о самых популярных базах данных с 2006 по 2021год: https://youtu.be/thuG2PXVbBU

Статья о самых популярных игровых консолях: https://statisticsanddata.org/data/best-selling-consoles-in-history-1972-2021/


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

Подробнее..

Recovery mode Как ускорить сайт в 4 раза, просто перенастроив сервер

02.06.2021 12:04:43 | Автор: admin

Если вы работаете с сайтом, который постепенно растет, - увеличивается количество товаров, трафик с рекламы - то рано или поздно придется перейти в режим работы highload, высоких нагрузок на сервер. Но что делать, если ваш сайт не растет, а сервер все чаще не выдерживает, и происходит блокировка данных? Именно с этой проблемой мы столкнулись, дорабатывая сайт для интернет-магазина светового оборудования с ассортиментом более чем 100 000 товаров.

Исходная ситуация

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

Поиск проблемы

Мы провели аудит настроек сервера и сайта, разделив работы на два этапа: анализ back-end и front-end, и обнаружили низкую скорость загрузки страниц на back-ende - порядка 80 секунд на самых посещаемых страницах, что в итоге приводило к существенному снижению конверсии.

Мы выяснили, что основная проблема заключалась в неправильно настроенном кэше и настройке базы данных.

В итоге был составлен план действий из четырех шагов, который помог нам добиться неплохих результатов. Что мы сделали?

Решение

Шаг 1. Настройка баз данных

На первом этапе мы настроили базу данных MySQL без изменения систем хранения, исходя из доступных ресурсов и нагрузки проекта. Эти действия, в первую очередь, были направлены на оптимизацию потребления ресурсов оперативной памяти, что позволило избежать ухода сервера в SWAP, когда, исчерпав ресурсы оперативной памяти, сервер начинал работать из файла подкачки и замедлял работу сайта.

Шаг 2. Смена типа хранения на InnoDB

Почему мы выбрали InnoDB?

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

Главное преимущество InnoDB заключается в скорости работы при выполнении запроса к базе InnoDB происходит блокировка только строки, при выполнении же запроса к базе MyISAM блокируется вся таблица. Дело в том, что пока запрос не будет выполнен, никакие другие обращения к таблице/строке будут невозможны. А поскольку строки значительно меньше целых таблиц, InnoDB обрабатывает запросы быстрее.

Также была произведена оптимизация работы самой базы данных InnoDB. Например, были оптимизированы параметры:

# InnoDB parameters

innodb_file_per_table

innodb_flush_log_at_trx_commit

innodb_flush_method

innodb_buffer_pool_size

innodb_log_file_size

innodb_buffer_pool_instances

innodb_file_format

innodb_locks_unsafe_for_binlog

innodb_autoinc_lock_mode

transaction-isolation

innodb-data-file-path

innodb_log_buffer_size

innodb_io_capacity

innodb_io_capacity_max

innodb_checksum_algorithm

innodb_read_io_threads

innodb_write_io_threads

Промежуточные результаты

После выполнения шагов 1 и 2 количество одновременных соединений с веб-сервером уменьшилось, так как запросы к базе данных и подключение к ней стали обрабатываться быстрее.

Это в свою очередь привело к уменьшению потребляемой оперативной памяти.

Шаг 3. Перенастройка Nginx и установка модулей кэширования brotli, pagespeed, proxy_buffering

Nginx позиционируется как простой, быстрый и надежный сервер, неперегруженный функциями. Уже длительное время Nginx обслуживает серверы многих высоконагруженных российских сайтов, например, Яндекс, Mail.Ru, ВКонтакте и Рамблер. Для улучшения производительности при использовании дополнительных серверов, Nginx поддерживает буферизацию (proxy_buffering) и кеширование (proxy_cache), чем мы и воспользовались.

Не обошлось и без курьезов настроек Nginx. У клиента был обычный интернет-магазин с товарами, тогда как настройки буферизации, которые мы обнаружили во время аудита, позволяли ему быть чуть ли ни стриминговым сервисом. Мы существенно уменьшили значения в параметре client_max_body_size, что в совокупности с перенастройкой Nginx еще больше снизило потребление памяти.

Шаг 4. Оптимизация настроек PHP-FPM и Memcache и отключение Apache

PHP-FPM нередко используется в паре с веб-сервером Nginx. Последний обрабатывает статические данные, а обработку скриптов отдает PHP-FPM. Такая реализация работает быстрее, чем распространенная модель Nginx + Apache.

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

Необходимым шагом стал перевод работы PHP-FPM на unix socket. Зачем это понадобилось? Nginx сам по себе довольно быстрый веб-сервер, однако самостоятельно он не может обрабатывать скрипты. Для этого необходим бэкенд в виде PHP-FPM. Чтобы вся эта связка работала без потери скорости, мы использовали unix socket способ подключения к PHP-FPM, позволяющий избегать сетевые запросы и дающий значительный прирост в скорости работы сайта.

Результаты работ

1. Время отклика главной страницы уменьшилось с 24 секунд до чуть более 3 секунд, внутренних до 5-8 сек.

2. Уменьшилось потребление серверных ресурсов.

3. Стабилизировалось поведение сервера - он перестал зависать.

4. Глубина просмотров увеличилась на 30%, и как следствие, это дало улучшение в SЕО, а также последующих продаж: растут поведенческие показатели => растут позиции сайта в выдаче => растет трафик => растут продажи.

5. Клиенту были даны рекомендации по оптимизации front-end части сайта для ускорения работы сайта. Например:

  • оптимизировать графики и настройку выдачи изображений в формате webp;

  • настроить lazyload-загрузки данных;

  • вынести все некритические для отображения страницы скрипты в конец страницы.

Вывод

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

Подробнее..

Перевод Ваш скрипт, обслуживающий индексы, измеряет не то, что надо

03.06.2021 18:21:18 | Автор: admin

Мой Дорогой Друг Шон недавно написал пост, рассказывающий о том, как люди неправильно обслуживают индексы. Я собираюсь пойти немного дальше и поговорить о том, что сам метод, которым ваш скрипт, обслуживающий индексы, оценивает фрагментацию индексов - неправильный.

Если вы посмотрите на то, как ваш скрипт решает нужно или нет перестраивать индексы, и тоже самое касается планов обслуживания (я запускал ПРОФАЙЛЕР ДА ЗДРАВСТВУЕТ ПРОФАЙЛЕР ВПЕРЁД ПРОФАЙЛЕР чтобы проверить), вы увидите, что они выполняют запрос к sys.dm_db_index_physical_stats.

Все эти запросы используют столбец avg_fragmentation_in_percent, чтобы понять - нужно ли перестроить индекс. Документация (по ссылке выше) про этот столбец имеет сказать следующее:

хныкхнык

Это мера логической фрагментации. Логическая фрагментация - это когда страницы "перемешаны" на диске.

Если вы используете приличные диски, даже на SAN, или у вас нормальный объём ОЗУ, вы можете понять из Великого Поста Шона, что фрагментация - это не самая худшая участь, которая может выпасть на долю ваших индексов. Если вы обслуживаете статистику, всё будет в порядке.

Кэши рулят

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

Вы могли бы сделать это с помощью столбца avg_page_space_used_in_percent.

НО...

упсупс

Ваше любимое решение по обслуживанию индексов позаботится о вас и запустит, по умолчанию, dm_db_index_physical_stats в режиме LIMITED. Это всё потому что более подробные измерения могут быть очень тяжёлыми на сервере, где хранится множество данных, и, блин, даже LIMITED может выполняться очень долго.

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

Штука в том, что между avg_fragmentation_in_percent и avg_page_space_used_in_percent, нет особой корреляции.

Локальная БД

Посмотрим на фрагментацию в моей локальной БД Stack Overflow 2013:

отстойотстой

Обе таблицы достаточно фрагментированы, чтобы привлечь внимание обслуживающего скрипта, но перестройка индекса, на самом деле, помогает только таблице Posts, несмотря на то, что мы перестроили оба.

В таблице Comments, avg_page_space_used_in_percent слегка уменьшается, а в Posts становится лучше примерно на 10%.

Количество страниц для Comments не изменяется, но уменьшается примерно на 500 тысяч для Posts.

Вот это то, что мне нравится. Я был бы рад читать на 500 тысяч меньше страниц при сканировании таблицы целиком.

Но при этом, я вообще-то не хочу сканировать целиком таблицу, если говорить не об отчётах или хранилищах данных.

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

Правильно?
Правильно

Подумайте о настройках обслуживания индексов

Вероятно они на стандартных 5% и 30% для реорганизации и перестроения. Дело не только в том, что они абсурдно низкие, но и в том, что они даже измеряют не тот тип фрагментации. Даже при 84% "фрагментации" мы видели страницы, заполненные на 75%.

Это не идеально, но едва ли это катастрофа.

Да вы возможно размышляли о том, чтобы установить fill factor ещё меньше, чтобы избежать фрагментации.

Что ещё хуже, вы, вероятно, смотрите все таблицы > 1000 страниц, т.е. примерно 8МБ. Но если у вас проблемы с тем, чтобы прочитать и удержать в памяти 8 мегабайт - может пора сгонять в магазин?

Спасибо, что прочитали!

Примечание переводчика

Тема достаточно холиварная. Erik Darling и Brent Ozar достаточно давно относятся к той группе, которая топит за то, что, в общем случае, индексам не нужно обслуживание. В противовес им можно поискать посты Paul S. Randal и Paul White, которые наоборот считают, что индексы нужно регулярно обслуживать.

На Хабре не нашёл постов/переводов, представляющих такую точку зрения, поэтому решил сделать сам. Ну и интересно, как обслуживают индексы dba на Хабре - принимайте участие в опросе.

Подробнее..

Перевод Путеводитель по базам данных в 2021г

04.06.2021 20:14:20 | Автор: admin

Данные это один из наиболее важных компонентов геопространственных технологий и, пожалуй, любой другой отрасли. К управлению данными сейчас относятся серьезно во всех отраслях, поэтому знания по этой дисциплине имеют важное значение для карьеры ИТ-специалистов. Этот цикл статей задуман как универсальное руководство, в котором мы рассмотрим тему от и до, начиная с вопроса Что такое данные? и заканчивая изучением и применением геопространственных запросов.

Основные понятия баз данных

Что такое данные?

Данные могут представлять собой любую информацию, которая сохраняется с целью обращения к ней в будущем. Эта информация может включать числа, текст, аудио- и видеоматериалы, местонахождение, даты и т.д. Она может быть записана на бумаге либо сохранена на жестком диске компьютера или даже в облаке.

Что такое база данных?

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

Зачем нужна база данных?

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

Система управления базами данных (СУБД)

Итак, мы уже знаем, что данные и базы данных важны, но как осуществляется работа с базами данных в компьютерных системах? Вот тут на сцену и выходит СУБД. СУБД это программное обеспечение, предоставляющее нам способ взаимодействия с базами данных на компьютере для выполнения различных операций, таких как создание, редактирование, вставка данных и т.д. Для этого СУБД предоставляет нам соответствующие API. Редко какие программы не используют СУБД для работы с данными, хранящимися на диске.Помимо операций с данными СУБД также берет на себя резервное копирование, проверку допуска, проверку состояния базы данных и т.д. Поэтому рекомендуется всегда использовать СУБД при работе с базами данных.

Пространственные данные и база данных

Особое внимание мы уделим обработке пространственных данных, поэтому я хотел бы обсудить здесь этот тип данных. Пространственные данные несколько отличаются от остальных. Координаты необходимо сохранять в особом формате, который обычно указан в документации на веб-сайте о базе данных. Этот формат позволяет базе считывать и правильно воспринимать координаты. Если обычно для поиска данных мы используем запросы типа Получить все результаты, где возраст>15, то пространственный запрос выглядит как-то так: Получить все результаты в радиусе 10км от определенной точки.Поэтому пространственные данные необходимо хранить в надлежащем формате.

Типы баз данных

Базы данных обычно делятся на два типа: реляционные и нереляционные. Оба типа имеют свои плюсы и минусы. Было бы глупо утверждать, что один лучше другого, поскольку это будет зависеть от варианта использования. Конкретно для пространственных данных я в 99% случаев использую реляционные базы данных, и вы скоро поймете почему.

Реляционные базы данных и РСУБД

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

Образец таблицы с информациейОбразец таблицы с информацией

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

Связь между двумя столбцамиСвязь между двумя столбцами

Взаимосвязи в реляционных базах данных мы подробно рассмотрим позже.

По сравнению с базами данных NoSQL, недостатком реляционных баз данных является относительно медленное получение результатов, когда количество данных стремительно увеличивается (по мнению автора статьи прим. пер.). Еще один недостаток заключается в том, что при добавлении каждой записи нужно следовать определенным правилам (типы столбцов, количество столбцов и т.д.), мы не можем просто добавить отдельный столбец только для одной записи.В реляционных базах данных используется SQL(Structured Query Language язык структурированных запросов), с помощью которого пользователи могут взаимодействовать с данными, хранящимися в таблицах. SQL стал одним из наиболее широко используемых языков для этой цели. Мы подробнее поговорим об SQL чуть позже.Вот примеры некоторых известных и часто используемых реляционных баз данных: PostgreSQL, MySQL, MSSQL и т.д. У каждой крупной компании, занимающейся реляционными базами данных, есть собственная версия SQL. В большинстве аспектов они выглядят одинаково, но иногда требуется немного изменить какой-нибудь запрос, чтобы получить те же результаты в другой базе данных (например, при переходе из PostgreSQL в MySQL).

Нереляционные базы данных (NoSQL)

Все базы данных, не являющиеся реляционными, относятся к категории нереляционных баз данных. Обычно данные хранятся в нетабличном формате, например:

  1. Пара ключ-значение

  2. Формат JSON, XML

  3. Графовый формат

Основное преимущество баз данных NoSQL состоит в том, что все строки независимы и могут иметь разные столбцы. Как показано на изображении ниже, оба пользователя относятся к одной и той же таблице Core_user, но их записи содержат разную информацию.

База данных NoSQL реального времени в Google FirebaseБаза данных NoSQL реального времени в Google Firebase

База данных NoSQL реального времени в Google Firebase

При использовании баз данных NoSQL пользователям иногда приходится прописывать собственную логику, чтобы добавить уникальный ключ к каждой записи и тем самым обеспечить доступ к записям. В большинстве стандартных баз данных NoSQL, таких как Firebase и MongoDB, для хранения данных используется формат JSON. Благодаря этому очень легко и удобно выполнять операции с данными из веб-приложений, используя JavaScript, Python, Ruby и т.д.

Рекомендации по выбору типа базы для хранения пространственных данных

Очевидно, что нам хотелось бы сохранить точку, линию, многоугольник, растры и т.д. так, чтобы это имело смысл, вместо того чтобы сохранять просто координаты. Нам нужна СУБД, которая позволяет не только сохранять данные, но и запрашивать их пространственными методами (буфер, пересечение, вычисление расстояния и т.д.). На сегодняшний день для этого лучше всего подходят реляционные базы данных, поскольку в SQL есть функции, помогающие выполнять подобные операции. Использование таких дополнительных средств, как PostGIS для PostgreSQL, открывает разработчикам возможности для написания сложных пространственных запросов. С другой стороны, NoSQL тоже работает в области геопространственных технологий: например, MongoDB предоставляет кое-какие функции для выполнения геопространственных операций. Однако реляционные базы данных все же лидируют на рынке с большим отрывом.

Работа с РСУБД

Основное внимание мы уделим РСУБД, так как именно эти системы в большинстве случаев мы будем использовать для хранения пространственных данных и работы с ними. В качестве примера мы будем использовать PostgreSQL, поскольку это самая перспективная реляционная база данных с открытым исходным кодом, а ее расширение PostGIS позволяет работать и с пространственными данными. Вы можете установить PostgreSQL, следуя инструкциям из документации. Помимо PostgreSQL рекомендуется также загрузить и установить pgAdmin. Платформа pgAdmin предоставляет веб-интерфейс для взаимодействия с базой данных. Также для этого можно загрузить и установить какое-либо другое совместимое ПО или использовать командную строку.

pgAdmin4 на MacpgAdmin4 на Mac

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

Создание базы данных. Нам нужно создать базу данных (в идеале должно быть по одной базе данных для каждого проекта).

Создание новой базы данных для проектаСоздание новой базы данных для проекта

В инструменте запросов (Query Tool) база данных создается следующим образом:

CREATE DATABASE <database_name>

Создание таблиц. Создание таблицы требует некоторых дополнительных соображений, поскольку именно здесь нам нужно определить все столбцы и типы данных в них. Все типы данных, которые можно использовать в PostgreSQL, вы найдете здесь.

pgAdmin позволяет нам выбрать в таблице различные ключи и ограничения, например Not Null (запрет на отсутствующие значения), Primary Key (первичный ключ) и т.д. Обсудим это подробнее чуть позже.

Создание таблицы пользователейСоздание таблицы пользователей

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

CREATE TABLE <table_name> (<column_1> <datatype>,<column_2> <datatype>,.....<column_n> <datatype>PRIMARY KEY (<column>));

CRUD-операции с данными в таблицах

CRUD-операции (создание, чтение, обновление и удаление Create, Retrieve, Update, Delete) это своего рода hello world в мире СУБД. Поскольку эти операции используются наиболее часто, команды для их выполнения одинаковы во всех РСУБД. Мы будем писать и выполнять запросы в инструменте запросов в pgAdmin, который вызывается следующим образом:

Инструмент запросов (Query Tool) в pgAdminИнструмент запросов (Query Tool) в pgAdmin

1. Создание новой записи

Для добавления новой записи в таблицу используйте следующую команду:

INSERT INTO <tablename> (column1, column2, column3,...) VALUES (value1, value2, value3,...);

INSERT, INTO, VALUE являются ключевыми словами в SQL, поэтому их нельзя использовать в качестве переменных, значений и т.д. Чтобы добавить новую запись в нашу таблицу пользователей, мы напишем в инструменте запросов следующий запрос:

INSERT INTO users(name, employed, address) VALUES ('Sheldon Cooper', true, 'Pasadena');

Обратите внимание: строки всегда следует заключать в'' (одинарные кавычки), а не в"" (двойные кавычки).

2. Получение записей (всех или нескольких)

Данные, хранящиеся в базе данных, можно извлечь и отобразить на экране. При этом мы можем получить все данные или ограниченное количество записей. Код для получения данных:

select <column1, column2 ,...> from <tablename> 

Этот код извлекает весь набор данных. Если вы хотите получить только 20записей, напишите:

select <column1, column2 ,...> from <tablename> limit 20

Если вы хотите получить данные из всех столбцов, то вместо перечисления названий всех столбцов можно написать:

select * from <tablename>

Если вы хотите получить результат с определенным условием, используйте ключевое слово WHERE, как показано ниже:

select * from <tablename> where <key> = <value>

Вы можете создавать даже сложные запросы, о которых мы поговорим позже.В нашем примере мы можем получить нужные нам данные:

--Retrieving Specific columns for all usersselect name,employed from users--Retrieving all columns for all usersselect * from users--Retrieving all columns for first 3 usersselect * from users limit 3--Retrieving all columns for all users where employed = trueselect * from users where employed = true

3. Обновление записей (всех или нескольких)РСУБД позволяет нам обновить все или только некоторые записи данных, указав новые значения для столбцов.

UPDATE <tablename> SET <column1> = <value1>, <column2> = <value2> 

Если вы хотите обновить определенные строки, добавьте условия с использованием ключевого слова WHERE:

UPDATE <tablename> SET <column1> = <value1>, <column2> = <value2>WHERE <column> = <value> 

В нашем случае мы обновим таблицы с помощью следующих запросов:

-- Make all rows as  employed = trueupdate users set employed = true-- change employed = false for entries with address = 'nebraska'update users set employed = false where address = 'nebraska'
Обновление записейОбновление записей

4. Удаление записей (всех или нескольких)Удалять записи в SQL легко. Пользователь может удалить либо все строки, либо только определенные строки, добавив условие WHERE.

-- Deleting all entries Delete from <tablename> -- Deleting entries based on conditionsDelete from <tablename> where <column> = <value> 
-- Deleting all entries Delete from users-- Deleting entries based on conditionsDelete from users where employed = false
Удаление записей из таблицыУдаление записей из таблицы

CRUD-операции используются очень часто, поскольку выполняют основные функции в базе данных.


Перевод подготовлен в рамках курса Базы данных. Все желающих приглашаем на бесплатный двухдневный онлайн-интенсив Бэкапы и репликация PostgreSQL. Практика применения. Цели занятия: настроить бэкапы; восстановить информацию после сбоя. Регистрация здесь.

Подробнее..

SAP HANA. Таблицы с типом хранения Row

12.06.2021 12:08:35 | Автор: admin

Добрый день, коллеги. В этой статье я бы хотел затронуть тему таблиц с типом Row. Этот тип таблиц для многих администраторов баз данных, долгое время оставался наиболее естественным типом, так сказать типом по умолчанию. Таблицы типа COLUMN в основнов встречались в хранилищах данных (Data Warehouse), то есть базах данных с преобладающей нагрузкой типа OLAP.

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

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

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

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

Пример таблицы с классическим хранениемПример таблицы с классическим хранением

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

Представление Row-store таблицы в памятиПредставление Row-store таблицы в памяти

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

Пример сканирования строк в таблицах с типом Row и Column storeПример сканирования строк в таблицах с типом Row и Column store

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

В базе данных HANA, таблицы с типом Row-store имеют целый ряд ограничений:

  • таблицы не могут быть секционированы

  • отсутствует алгоритм для сжатия таблиц

  • к колонке в такой таблице не может быть предоставлен отдельный доступ, а также параллельный доступ.

  • таблица не может быть выгружена из памяти

  • если таблица с строковым типом не может быть полностью загружена в память, сервер базы данных не сможет запуститься

  • для построения большинства информационных моделей, таблицы с этим типом не могут быть использована напрямую.

Для row-store таблиц, доступно два типа индекса: классический b-tree индекс и cpb+-tree (сжатый префикс b-tree) индекс который оптимизирован для обработки символьных индексных ключей в памяти. Несмотря на то, что есть возможность выбрать тип используемого индекса, обычно в этом нет необходимости. SAP HANA будет использовать cpb+-tree тип определенный по полю или комбинации полей типа string, binary string, или decimal. Для всех остальных типов, будет создан классический b-tree индекс. Индексы по row-store таблицам не сохраняются в БД на постоянной основе, вместо этого они пересоздаются каждый раз при загрузке таблицы в память (запуске базы данных).

Управление многоверсионной конкуренцией (Multiversion Concurrency Control)

Multiversion Concurrency Control (MVCC) это концепция, которая гарантирует консистентность транзакционных данных путем изоляции транзакций которые получают доступ к одним и тем же данным в одно и то же время. Для того чтобы это было реализовано параллельно, сохраняется несколько версий записи. Проблемы с MVCC обычно связаны с большим количеством активных версий записи. Старые версии время от времени должны удаляться для того, чтобы освободить память.

Колоночные и строковые таблицы реализуют это функцию совершенно по-разному.

Для row store таблиц, каждая измененная страница сначала копируется и размещается в цепочке версий страниц, где каждая версия имеет состояние данных на определенную операцию commit. Эти цепочки страниц хранятся в виртуальном контейнере в памяти, который называется undo. Представление для мониторинга M_UNDO_CLEANUP_FILES показывает детальную информацию по этим внутренним виртуальным файлам-контейнерам.

За удаление старых версий записи отвечает Garbage Collector. Сборка мусора происходит после операции commit, а также периодически (по умолчанию каждый час). Сборщик мусора может удалить лишь те старые версии записей, для которых транзакции обновления были завершены (была выполнена операция commit или rollback). В случае, если транзакция изменяет десятки или тысячи строк без фиксации изменений (операции commit), вы можете попасть в ситуацию, когда большой объем избыточных данных необходимо хранить в основной памяти (main memory), так как в базе данных будут храниться десятки тысяч блокированных версий записей и новых активных версий записи. Я уже наблюдал картину, когда по большим таблицам в памяти хранилось до 8 млн. версий записей.

Если обраться к представлению с потоками (например M_SERVICE_THREADS), то в поле Thread Type периодическая сборка мусора будет обозначаться как MVCCGarbageCollector. Для того, чтобы найти транзакции, которые зафиксировали свои изменения, необходимо в представлении потоков, сделать выборку по условиям THREAD_TYPE=SqlExecutor и THREAD_METHOD=CommitTrans.

Реорганизация Row-store области

Реорганизация Row-store области представляет собой процесс дефрагментации сегментов памяти с целью более компактного хранения. Несмотря на значительные улучшения в самом процессе реорганизации, который претерпел существенные изменения начиная еще с HANA 1.0, сама по себе процедура всё еще остается проблемной. Об этом я расскажу ниже, а сейчас немного общей информации.

Пространство Row store увеличивается путем выделения сегментов памяти размером 64 Мб. Сегмент внутренне разделен на страницы фиксированного размера. Когда row-store таблице требуется больше памяти для хранения записей, для неё выделяются свободные страницы из существующего сегмента. Если свободных страниц в сегменте больше нет, выделяется новый сегмент.

Удаление большого числа записей может привести к появлению разрозненных сегментов. В этом случае может помочь реорганизация области row store с целью более компактного хранения в памяти. Страницы из разрозненных сегментов переносятся в другие сегменты, в результате освобождается свободное пространство. SAP рекомендует выполнять реорганизацию Row Store пространства при условии, что его размер превышает 10Gb при этом свободных блоков более 30%. В базе данных HANA доступно два режима реорганизации пространства ONLINE и OFFLINE.

ONLINE реорганизация стала доступна с версии HANA 1.0 SPS8. С тех пор этот режим постоянно улучшается с целью снижения влияния на бизнес процессы и повышения эффективности хранения в области Row store. До версии SAP HANA 2.0 SPS3 включительно, при проведении онлайн реорганизации, на таблицы участвующие в реорганизации устанавливалась эксклюзивная блокировка, в результате чего, изменения записей по этим таблицам были невозможны. Начиная с версии SAP HANA 2.0 SPS4 в процессе реорганизации произошли изменения, теперь при онлайн реорганизации блокировка устанавливается на уровне строки, а не таблицы целиком. В соответствии с рекомендациями SAP online реорганизация необходимо запускать в период минимальной нагрузки для того, чтобы добиться лучшего результата. Именно в этом и кроется основная претензия к этому режиму реорганизации, он не позволяет добиться такого же результата, как в случае OFFLINE реорганизации.

До версии HANA 2.0 SPS4, компания SAP рекомендовала использовать именно OFFLINE режим, для получения лучшего результата. При этом OFFLINE режим несёт в себе один существенный недостаток, логически вытекающий из его названия. Систему работающую в режиме 24х7 для реорганизации row-store пространства никто останавливать не будет. По этому, обычно, OFFLINE реорганизацию совмещают с другими работами, требующими останова системы.

Внимание! OFFLINE реорганизация может существенно увеличить время запуска базы данных HANA.

Начина я с версии SAP HANA 2.0 SPS4 стал доступен режим автоматической ONLINE реорганизации. По умолчанию раз в час запускается проверка на фрагментированность области row store. Если показатель полезного использования памяти менее 60% от размера области row-store, запускается автоматическая реорганизация. Пороговое значение фрагментированности, а также частота запуска проверки может быть изменена. Подробнее о автоматическом режиме можно прочитать в ноте 2789255 - Automatic Online Row Store Reorganization.

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

Подробнее..

Категории

Последние комментарии

  • Имя: Билал
    04.12.2024 | 19:28
  • Имя: Murshin
    13.06.2024 | 14:01
    Нейросеть-это мозг вселенной.Если к ней подключиться,то можно получить все знания,накопленные Вселенной,но этому препятствуют аннуннаки.Аннуннаки нас от неё отгородили,установив в головах барьер. Подр Подробнее..
  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru