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

Oracle database

Ещё один шаг в сторону open source как и почему мы внедрили Arenadata DB

22.04.2021 10:12:30 | Автор: admin

Привет, Хабр! Меня зовут Станислав Маскайкин, я архитектор аналитических систем ВТБ. Сегодня я расскажу о том, почему мы перевели нашу систему подготовки отчётности с Oracle SuperCluster на российскую Arenadata DB. Как мы выбирали решение, почему не взяли чистый опенсорс, а также о некоторых результатах такой миграции под катом.

Зачем нужен был переход?

Несколько лет назад банк ВТБ объединился с Банком Москвы и ВТБ 24. Каждый из банков имел собственную ИТ-инфраструктуру с отдельным аналитическим контуром. После объединения получилось, что в банке одновременно существуют три разных ИТ ландшафта.

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

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

Исторически ВТБ24 был ориентирован на работу с физическими лицами, ВТБ на работу с юридическими лицами, а Банк Москвы на работу и с первыми, и со вторыми.

На момент объединения этих банков обязательная отчётность формировалась в следующих системах:

  1. Единое хранилище данных (ЕХД) хранилище данных Банка Москвы, реализованное на SuperCluster M8 и ETL-инструменте Informatica Power Center.

  1. Система подготовки отчётности хранилище данных ВТБ24, реализованное на Oracle SuperCluster M8 с программным обеспечением Diasoft Flextera BI. Данные для этой системы готовились в другом хранилище корпоративном хранилище данных (КХД), реализованном на СУБД Teradata и ETL-инструменте SAS Data Integration. КХД, в свою очередь, получало данные из оперативного хранилища данных, реализованного на Oracle SuperCluster M8. А туда они реплицировались из автоматизированных банковских систем при помощи инструмента Oracle Golden Gate.

  1. Корпоративное информационное хранилище хранилище данных ВТБ, реализованное на Oracle Exadata X8-2 и ETL-инструменте Informatica Power Center.

Чтобы не формировать обязательную отчётность по объединённому ВТБ в ручном режиме, были созданы интеграции между хранилищами данных.

Это привело к ещё двум большим проблемам:

  1. Увеличилось время получения данных, что часто приводило к срыву сроков предоставления информации.

По правилам ЦБ РФ отчётность за очередной месяц сдаётся в течение первых четырех дней следующего месяца. В банке под это задействуются огромные вычислительные мощности. Если первые дни месяца попадают на рабочие дни (как в марте), мы даже не имеем возможности вернуться и что-то пересчитать это риск для банка не сдать отчётность вовремя. Повышение доступности данных на этом уровне играет огромную роль.

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

Ещё один момент: многие компоненты нашей инфраструктуры, такие как Oracle SuperCluster, на котором у нас реализована большая часть аналитического ландшафта, попали под End of life. Они были сняты с поддержки производителем и больше не развивались, т.е. обновление необходимо было в любом случае.

Проблема окончания поддержки коснулась не только системы подготовки отчётности, но и озера данных на платформе Oracle Big Data Appliance. К тому моменту а происходило все в 20182019 годах сотрудники ВТБ уже в полной мере оценили data-driven подход и потребляли достаточно много данных. Поэтому с точки зрения бизнеса банка система была критичной. Т.е. перед нами стояла более глобальная задача масштабов всей инфраструктуры.

Параллельно в объединённом ВТБ началась масштабная цифровая трансформация, охватившая все уровни IT, начиная от создания новых ЦОДов и объединения сетей, и заканчивая унификацией автоматизированных банковских систем и созданием омниканальной платформы для фронтальных решений. Всё это кардинально меняло внутренний IT-ландшафт банка.

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

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

Что такое платформа данных? Для себя мы определили её так: это набор сквозных интегрированных технологических решений (технологическое ядро), которые являются основой для разработки и функционирования сервисов по работе с данными банка ВТБ.

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

Мы выделили 6 компонентов технологического ядра:

  1. Управление данными.

  2. Управление качеством данных.

  3. Управление доступом.

  4. Аналитические справочники.

  5. Корректировки.

  6. ETL Framework.

Концептуальная архитектура платформы данных выглядит следующим образом:

Ядром платформы данных является СУБД, на которой реализовывается хранилище данных. Далее расскажу об этом подробнее.

Выбор новой платформы

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

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

Мы рассматривали всех крупнейших производителей подобных решений: Oracle Exadata, Teradata, Huawei. Оценили отечественные разработки практически все, что есть на рынке. Нам показался интересным опенсорс, тем более для банка это не первый заход в тему открытого исходного кода.

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

При выборе платформы мы учитывали следующие критерии:

  • функциональность

  • качество поддержки

  • отсутствие санкционных рисков

  • возможность гибкого масштабирования

  • надёжность

  • безопасность

  • наличие Road Map развития платформы и возможность на него влиять

  • стоимость владения совокупные затраты на программно-аппаратный комплекс на горизонте 10 лет (TCO5).

Если взять последний критерий, то даже с учётом стоимости всех контуров Arenadata DB и самого проекта миграции мы получали существенную экономию на фоне Oracle SuperCluster.

В итоге по совокупности факторов мы выбрали Arenadata DB.

Тестирование платформы

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

В итоге нами были проведены следующие проверки.

  • Функциональное тестирование:

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

  • Отказоустойчивость и отключение компонентов:

  • Отключение дисковых устройств на уровне БД для проверки стабильности работы кластера.

  • Отключение питания одного блока питания на серверах для проверки стабильности работы кластера.

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

  • Совместимость со средствами резервного копирования банка:

    • Проведение цикла резервного копирования и восстановления БД на СРК Veritas Netbackup:

      • Полное резервное копирование БД

      • Инкрементальное резервное копирование БД

      • Восстановление БД

  • Управление и качество работы системы:

    • Перезагрузка кластера: фиксация успешного выполнения процедуры перезагрузки.

    • Мониторинг и управление: субъективная балльная оценка от 0 до 5.

    • Генерация тестового отчёта: прогон запроса изсистемы подготовки отчётности в Arenadata DB для анализа качества результата генерируемого отчёта результаты выполнения отчёта должны быть идентичны.

  • Нагрузочное тестирование:

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

  • Интеграционное тестирование:

    • Интеграция с ПО Infomatica Power Center

    • Интеграция с Oracle BI

    • Интеграция с QlikView 12.

Результаты тестирования

В ходе тестирования кластера Arenadata DB показал высокую производительность как на синтетических тестах, так и на реальных нагрузках.

Ниже приведено сравнение скорости выполнения запросов по сравнению с текущим Oracle Super Cluster T5-8.

Тестирование проводил системный интегратор IBS Platformix.

Скорость выполнения синтетического запроса Jmeter (сек)

Arenadata DB (сек.)

Oracle (сек.)

160.3

1291

Кластер показал высокую скорость загрузки данных через ETL-инструмент Informatica Power Center: 200 Мбит/с.

В ходе тестирования была также осуществлена интеграция с основными BI-инструментами, используемыми в Банке ВТБ (Oracle BI и QlikView), и протестирован их функционал.

В QlikView на простейших SQL-запросах протестированы соединение с БД и выборка данных с последующей загрузкой в модель BI-инструмента.

Результаты выполнения представлены в таблице ниже.

Тест 1

Тест 2

Драйвер

ODBC PostgreSQL35W

ODBC PostgreSQL35W

Запрос

select * from user.test1

// 3 коротких поля

select e.* from dds.accounts e

where

e.entry_dt ='2019-02-03'

-and e.partition_source_system_cd ='00006'

and e.src_deleted is null

Строк

20480000

45 920

Затраченное время

0:58

2:59

Скорость загрузки в модель, строк в сек.

353103

257

При выполнении тестов была замечена особенность: получение первых строк данных из БД происходило с задержкой примерно в 23 секунды. После этого скорость выборки данных из БД и их доставки в QlikView становилась очень высокой.

Предположительно данная особенность связана c неоптимальной настройкой коннектора.

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

Цель теста

Предварительные условия

Процедура

Результат

Тестирование отказа диска с данными

Отказ диска эмулируется физическим извлечением диска или логическим отключения дискового уст-ва из работающего сервера.

Подключиться к серверу

Провести процедуру unmount для физического диска

Проверить доступность данных

Данные доступны

Тестирование отказа кэширующего диска

Отказ диска эмулируется физическим извлечением диска или логическим отключения дискового устройства из работающего сервера

Подключиться к серверу

Провести процедуру unmount для физического диска

Проверить доступность данных

Данные доступны

(Отказ кэширующего диска не приводит к потере данных)

Тестирование включения кластера после эмуляции аварии

Отказ сервера при выполнении SQL запроса к базе данных, эмулируется отключением электропитания работающего сервера

Подключиться к серверу

Выполнить SQL запрос

Выключить 1 ноду

Перезапустить выполнение SQL

Данные получены при повторном SQL запросе

Благодарю Дениса Степанова и Никиту Клименко, экспертов IBS Platformix, за предоставленные результаты тестирования.

Сбор отчётности как пилот

Наша цель это миграция на Arenadata DB всех существующих хранилищ банка ВТБ.

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

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

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

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

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

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

Поскольку мы уже видим результаты и детали взаимодействия, около полугода назад стартовал наш основной проект - миграция на продукты Arenadata центрального единого хранилища данных и озера данных. Помимо Arenadata DB, мы используем Arenadata Streaming на базе Apache Kafka и Arenadata Hadoop на базе Apache Hadoop. В ближайшее время первые результаты пойдут в продакшен.

Целевая архитектура платформы данных к концу 2022 годаЦелевая архитектура платформы данных к концу 2022 года

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

Подробнее..

Сводные таблицы в SQL

28.06.2020 10:17:00 | Автор: admin
Сводная таблица один из самых базовых видов аналитики. Многие считают, что создать её средствами SQL невозможно. Конечно же, это не так.


Предположим, у нас есть таблица с данными закупок нескольких видов товаров (Product 1, 2, 3, 4) у разных поставщиков (A, B, C):



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



В SQL это не так быстро, но большинство решений тривиальны.

Тестовые данные
-- таблица с полями: поставщик (supplier), товар (product), объем поставки (volume)create table test_supply (supplier varchar null, -- varchar2(10) в Oracle, и т.п.                          product varchar null,  -- varchar2(10) в Oracle, и т.п.                          volume int null                          ); -- тестовые данныеinsert into test_supply (supplier, product, volume) values ('A', 'Product 1', 928);insert into test_supply (supplier, product, volume) values ('A', 'Product 1', 422);insert into test_supply (supplier, product, volume) values ('A', 'Product 4', 164);insert into test_supply (supplier, product, volume) values ('A', 'Product 1', 403);insert into test_supply (supplier, product, volume) values ('A', 'Product 3', 26);insert into test_supply (supplier, product, volume) values ('B', 'Product 4', 594);insert into test_supply (supplier, product, volume) values ('B', 'Product 4', 989);insert into test_supply (supplier, product, volume) values ('B', 'Product 3', 844);insert into test_supply (supplier, product, volume) values ('B', 'Product 4', 870);insert into test_supply (supplier, product, volume) values ('B', 'Product 2', 644);insert into test_supply (supplier, product, volume) values ('C', 'Product 2', 733);insert into test_supply (supplier, product, volume) values ('C', 'Product 2', 502);insert into test_supply (supplier, product, volume) values ('C', 'Product 1', 97);insert into test_supply (supplier, product, volume) values ('C', 'Product 3', 620);insert into test_supply (supplier, product, volume) values ('C', 'Product 2', 776);-- проверкаselect * from test_supply;



1. Оператор CASE и аналоги



Самый простой и очевидный способ получения сводной таблицы это хардкод с использованием оператора CASE. Например, для поставщика А можно вычислить размер поставок как sum(case when t.supplier = 'A' then t.volume end). Чтобы получить объем поставок для разных товаров достаточно просто добавить группировку по полю product:

select t.product,        sum(case when t.supplier = 'A' then t.volume end) as Afrom test_supply tgroup by t.productorder by t.product;



Если добавить else 0, то для товаров, по которым не было поставок, вместо null будут выведены нули:

select coalesce(t.product, 'total_sum') as product,       sum(case when t.supplier = 'A' then t.volume end) as Afrom test_supply tgroup by t.product;



Если продублировать код для всех поставщиков (которых у нас три A, B, C), мы получим необходимую нам сводную таблицу:

select t.product,        sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as Cfrom test_supply tgroup by t.productorder by t.product;



В неё можно добавить итог по строкам (как обычную сумму, т.е. sum(t.volume)):
select t.product,       sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by t.product;



Не составит труда добавить и итог по столбцам. Для этого необходим использовать оператор ROLLUP, который позволит добавить суммирующую строку. В большинстве СУБД используется синтаксис rollup(t.product), хотя иногда доступен и альтернативный t.product with rollup (например, SQL Server).

select t.product,       sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



Результат можно сделать ещё красивее, заменив NULL на собственную подпись итога. Для этого можно использовать функцию coalesce(): coalesce(t.product, 'total_sum'), или же любой специфичный для конкретной СУБД аналог (например, nvl() в Oracle). Результат будет следующим:

select coalesce(t.product, 'total_sum') as product,       sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



Если СУБД не поддерживает ROLLUP ...
Если ваша СУБД настолько стара, что не поддерживает rollup, придётся использовать костыли. Например, так:

select t.product,       sum(case when t.supplier = 'A' then t.volume end) as A,       sum(case when t.supplier = 'B' then t.volume end) as B,       sum(case when t.supplier = 'C' then t.volume end) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by t.productunion allselect 'total_sum',       sum(case when t.supplier = 'A' then t.volume end),       sum(case when t.supplier = 'B' then t.volume end),       sum(case when t.supplier = 'C' then t.volume end),       sum(t.volume) as total_sumfrom test_supply t;




Можно (но вряд ли стоит) использовать какую-либо из вендоро-специфичных функций вместо стандартного CASE. Например, в PostgreSQL и SQLite доступен оператор FILTER:

select coalesce(t.product, 'total_sum') as product,       sum(t.volume) filter (where t.supplier = 'A') as A,       sum(t.volume) filter (where t.supplier = 'B') as B,       sum(t.volume) filter (where t.supplier = 'C') as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);


Особенность FILTER в том, что он является частью стандарта (SQL:2003), но фактически поддерживается только в PostgreSQL и SQLite.

В других СУБД есть ряд эквивалентов CASE, не предусмотренных стандартом: IF в MySQL, DECODE в Oracle, IIF в SQL Server 2012+, и т.д. В большинстве случаев их использование не несёт никаких преимуществ, лишь усложняя поддержку кода в будущем.

MySQL: IF
select coalesce(t.product, 'total_sum') as product,        sum(IF(t.supplier = 'A', t.volume, null)) as A,       sum(IF(t.supplier = 'B', t.volume, null)) as B,       sum(IF(t.supplier = 'C', t.volume, null)) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



Oracle: DECODE
select coalesce(t.product, 'total_sum') as product,        sum(decode(t.supplier, 'A', t.volume, null)) as A,       sum(decode(t.supplier, 'B', t.volume, null)) as B,       sum(decode(t.supplier, 'C', t.volume, null)) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



SQL Server 2012 или выше: IIF
select coalesce(t.product, 'total_sum') as product,        sum(iif(t.supplier = 'A', t.volume, null)) as A,       sum(iif(t.supplier = 'B', t.volume, null)) as B,       sum(iif(t.supplier = 'C', t.volume, null)) as C,       sum(t.volume) as total_sumfrom test_supply tgroup by rollup(t.product);



2. Использование PIVOT (SQL Server и Oracle)



Описанный выше подход трудно назвать красивым. Как минимум, хочется не дублировать код для каждого поставщика, а просто их перечислить. Сделать это позволяет разворот (PIVOT) таблицы, доступный в в SQL Server и Oracle. Хотя этот оператор не предусмотрен стандартом SQL, обе СУБД предлагают идентичный синтаксис.

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

select t.supplier,       t.product,       sum(t.volume) as aggfrom test_supply tgroup by t.product,         t.supplier;



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

select t.supplier, t.product, sum(t.volume) as aggfrom test_supply tgroup by t.supplier, t.productunion allselect null, t.product, sum(t.volume)from test_supply tgroup by t.productunion allselect t.supplier, null, sum(t.volume)from test_supply tgroup by t.supplierunion allselect null, null, sum(t.volume)from test_supply t;



Этот запрос можно существенно упростить, используя оператор CUBE:

select t.supplier,        t.product,        sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product);


Если мы хотим получить подпись итогов как 'total_sum' вместо NULL запрос необходимо немного откорректировать:

select coalesce(t.supplier, 'total_sum') as supplier,        coalesce(t.product, 'total_sum') as product,        sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product);



К такому результату уже можно применять PIVOT:

select *from ( select coalesce(t.supplier, 'total_sum') as supplier,       coalesce(t.product, 'total_sum') as product,       sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product)      ) tpivot (sum(agg)         -- NB: ниже в SQL Server - двойные кавычки, в Oracle DB - одинарные       for supplier in ("A", "B", "C", "total_sum")       ) pvt;


Здесь мы поворачиваем таблицу из прошлого запроса, используя агрегатную функцию суммы sum(agg). При этом заголовки столбцов мы берём из поля supplier, а с помощью in ("A", "B", "C", "total_sum") указываем какие конкретно поставщики должны быть выведены (total_sum отвечает за столбец с итогами по строкам).

3. Common table expression



В принципе, для поворота таблицы нам не нужен оператор PIVOT как таковой. Этот запрос можно легко переписать, используя стандартный синтаксис комбинацию CTE (common table expression) и соединений. Для этого будем использовать тот же запрос, что и для PIVOTа:

with cteas(select coalesce(t.supplier, 'total_sum') as supplier,          coalesce(t.product, 'total_sum') as product,          sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product))select * from cte;



Из результатов, полученных в cte нам необходимы только уникальные значения товаров:
select distinct t.product from cte t


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

left join cte aon t.product = a.productand a.supplier = 'A'


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

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

with cteas(select coalesce(t.supplier, 'total_sum') as supplier,          coalesce(t.product, 'total_sum') as product,          sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product))select distinct t.product,                     a.agg as A,                    b.agg as B,                    c.agg as C,                    ts.agg as total_sumfrom cte tleft join cte aon t.product = a.product and a.supplier = 'A'left join cte bon t.product = b.product and b.supplier = 'B'left join cte con t.product = c.product and c.supplier = 'C'left join cte tson t.product = ts.product and ts.supplier = 'total_sum'order by product;



Конечно, такой запрос это proof-of-concept, поэтому выглядит он довольно экзотично.

4. Функция CROSSTAB (PostgreSQL)



В PostgreSQL доступна функция CROSSTAB, которая примерно эквивалентна PIVOT в SQL Server или Oracle. Для работы с ней необходимо расширение tablefunc:
create extension tablefunc; -- для PostgreSQL 9.1+


CROSSTAB принимает в качестве основного аргумента запрос как text sql. Он будет практически тем же, что и для PIVOT, но с обязательным использованием сортировки:
select coalesce(t.product, 'total_sum') as product,         coalesce(t.supplier, 'total_sum') as supplier,                           sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product)order by product, supplier;


В отличие от PIVOT, для разворота таблицы нам необходимо указывать не только названия столбцов, но и типы данных. Например, так: "product" varchar, "A" int8, "B" int8, "C" int8, "total_sum" int8.
Ещё один нюанс состоит в том, что CROSSTAB заполняет строки слева направо, игнорируя NULL-овые значения. Например, такой запрос:

select *from crosstab     (    $$select coalesce(t.product, 'total_sum') as product,     coalesce(t.supplier, 'total_sum') as supplier,                       sum(t.volume) as agg      from test_supply t      group by cube(t.supplier, t.product)      order by product, supplier $$     )   as cst("product" varchar, "A" int8, "B" int8, "C" int8, "total_sum" int8);


вернёт совсем не то, что мы хотим:



Как можно заметить, там, где были NULL-овые значения, всё съехало влево. Например, в первой строке для Product1 итог по строке оказался в столбце для поставщика С, а поставки С в столбце поставщика В (для которого поставок не было). Корректно проставлены данные только для Product3 т.к. для этого товара у всех поставщиков были значения. Иными словами, если бы у нас не было NULL-овых значений, запрос был бы корректным и вернул нужный результат.

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

select distinct tt.supplier as supplierfrom test_supply ttunion allselect 'total_sum'order by supplier


а полный запрос будет выглядеть так:

select * from crosstab     (    $$select coalesce(t.product, 'total_sum') as product,     coalesce(t.supplier, 'total_sum') as supplier,                       sum(t.volume) as agg      from test_supply t      group by cube(t.supplier, t.product)      order by product, supplier $$,    $$select distinct tt.supplier as supplier      from test_supply tt      union all      select 'total_sum'      order by supplier $$     )   as cst("product" varchar, "A" int8, "B" int8, "C" int8, "total_sum" int8);



5. Динамический SQL (на примере SQL Server)



Запрос с PIVOT или CROSSTAB уже функциональнее, чем изначальный с CASE (или CTE), но названия поставщиков все ещё необходимо вносить вручную. Но что делать, если поставщиков много? Или если их список регулярно обновляется? Хотелось бы выбирать их автоматически как как select distinct supplier from test_supply (или же из словаря, если он есть).

Здесь чистого SQL недостаточно. Он подразумевает статическую типизацию: для создания плана запроса СУБД нужно заранее указать число столбцов. Поэтому, например, синтаксис PIVOT не позволяет использовать подзапрос. Но это ограничение легко обойти с помощью динамического SQL! Для этого названия столбцов необходимо преобразовать в строку формата "элемент_1", "элемент_2", , "элемент_n", и использовать их в запросе.

Например, в SQL Server мы можем использовать STUFF для получения такой строки

declare @colnames as nvarchar(max);select @colnames =            stuff((select distinct ', ' + '"' + t.supplier + '"'       from test_supply t       for xml path ('')       ), 1, 1, ''          ) + ', "total_sum"';


а затем включить её в окончательный запрос:

-- T-SQL (!)declare @colnames as nvarchar(max),        @query as nvarchar(max);select @colnames =            stuff((select distinct ', ' + '"' + t.supplier + '"'       from test_supply t       for xml path ('')       ), 1, 1, ''          ) + ', "total_sum"';set @query =   'select * from(select coalesce(t.supplier, ''total_sum'') as supplier, coalesce(t.product, ''total_sum'') as product, sum(t.volume) as aggfrom test_supply tgroup by cube(t.supplier, t.product)) as tpivot (sum(agg) for supplier in (' + @colnames + ')) as pvt';execute(@query);


Динамический SQL вполне можно применить и к самому первому решению с CASE. Например, так:

-- T-SQL (!)select distinct supplier into #colnames from test_supply;declare @colname as nvarchar(max),        @query as nvarchar(max);set @query = 'select coalesce(t.product, ''total_sum'') as product';while exists (select * from #colnames)begin    select top 1 @colname = supplier from #colnames;    delete from #colnames where supplier = @colname;    set @query = @query + ', sum(case when t.supplier = ''' + @colname + ''' then t.volume end) as ' + @colnameend;set @query = @query + ' , sum(t.volume) as total_sum                       from test_supply t                       group by rollup(t.product)'drop table #colnames;execute(@query);


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

  sum(case when t.supplier = '<Поставщик 1>' then t.volume end) as <Поставщик 1>, sum(case when t.supplier = '<Поставщик 2>' then t.volume end) as <Поставщик 2>..., sum(case when t.supplier = '<Поставщик n>' then t.volume end) as <Поставщик n>


Во многих СУБД доступно аналогичное решение. Тем не менее, мы уже слишком отдалились от чистого SQL. Любое использование динамического SQL подразумевает углубление в специфику конкретной СУБД (и соответствующего ей процедурного расширения SQL).

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

Из песочницы Метод научного тыка, или как подобрать конфигурацию субд с помощью бенчмарков и оптимизационного алгоритма

11.09.2020 16:15:38 | Автор: admin

Здравствуйте.


Решил поделится своей находкой плодом раздумий, проб и ошибок.


По большому счёту: это никакая не находка, конечно же всё это должно быть давно известно, тем кто занимается прикладной стат-обработкой данных и оптимизацией каких либо систем, не обязательно именно СУБД.


С другой стороны: навскидку не усматриваю широкого упоминания, распространения такого подхода, в интернете, среди ит-специалистов, ДБА.


Итак, к сути.


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


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


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


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


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


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


Ну. Так, почти всегда и бывает.


Что тут можно сделать.


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


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


Подавать на неё тестовую нагрузку, вот этой самой тулзой-утилитой, лоад-генератором.
И смотреть величину отклик, ну или метрику качества работы системы.


Второй мыслью может быть заключение такое что это же очень долго.


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


Ну и тут что можно понять и вспомнить.


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


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


Т.е.


Вот обозначим вектор конфигурации системы, как $X=x_{i}$, где $i=1,N$; Где $N$ кол-во параметров конфигурации системы, сколько их, этих параметров.


А значение метрики, соответствующей данному $X$ обозначим как
$M$, то, у нас получается функция: $M=f(X)$


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


Хорошо, но тут возникает организационно-прикладной вопрос: а какой именно алгоритм использовать.


  1. В смысле чтобы самому поменьше руками кодить.
  2. И чтобы работало, т.е. находило экстремум (если он есть), ну, по крайней мере быстрее координатного спуска.

Первый момент намекает что надо посмотреть в сторону каких то сред, в которых такие алгоритмы уже реализованы, и есть, в каком то виде, готовом к использованию в коде.
Ну, мне известны python и cran-r


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


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


Либо их можно получить на итогах работы алгоритма.


Тут многое зависит от входных условий.


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


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


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


Исходные:


  1. Пусть есть, в качестве сервисной системы: oracle xe 18c
  2. Пусть она обслуживает транзакционную активность и цель: получить возможно большую пропускную способность субд, по транзакциям/сек.
  3. Транзакции бывают сильно разные, по своему характеру работы с данными и контексту работы.
    Условимся так что это транзакции которые не обрабатывают большое кол-во табличных данных.
    В том смысле что не генерируют ундо-данных больше чем редо и не обрабатывают большие проценты строк, больших таблиц.

Это транзакции которые меняют одну строку в более-менее большой таблице, с небольшим кол-вом индексов над этой таблицей.


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


Оговорка если говорить именно про настройках субд.


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


Которые, конечно же, будут угнетающе сказываться на tps-метрике и это будет экзогенный, относительно субд, фактор: ну вот так задизайнили табличную модель и работу с данными в ней что возникают блокировки.


Поэтому, для чистоты эксперимента, исключим этот фактор, ниже уточню как именно.


  1. Предположим, для определённости, что 100% подаваемых в субд sql-команд: это dml-команды.
    Пусть характеристики пользовательской работы с субд: одни и те же, в тестах.
    А именно: кол-во скл-сессий, табличные данные, то как с ними работают скл-сессии.
  2. Субд работает в FORCE LOGGING, ARCHIVELOG модах. Флешбак-датабейс режим выключен, на уровне субд.
  3. Редо-логи: расположены в отдельной файловой системе, на отдельном "диске";
    Вся остальная часть физической компоненты бд: в другой, отдельной фс, на отдельном "диске":

Подробнее, про устройство физ. компоненты лабораторной бд
SQL> select status||' '||name from v$controlfile; /db/u14/oradata/XE/control01.ctlSQL> select GROUP#||' '||MEMBER from v$logfile;1 /db/u02/oradata/XE/redo01_01.log2 /db/u02/oradata/XE/redo02_01.logSQL> select FILE_ID||' '||TABLESPACE_NAME||' '||round(BYTES/1024/1024,2)||' '||FILE_NAME as col from dba_data_files;4 UNDOTBS1 2208 /db/u14/oradata/XE/undotbs1_01.dbf2 SLOB 128 /db/u14/oradata/XE/slob01.dbf7 USERS 5 /db/u14/oradata/XE/users01.dbf1 SYSTEM 860 /db/u14/oradata/XE/system01.dbf3 SYSAUX 550 /db/u14/oradata/XE/sysaux01.dbf5 MONITOR 128 /db/u14/oradata/XE/monitor.dbfSQL> !cat /proc/mounts | egrep "\/db\/u[0-2]"/dev/vda1 /db/u14 ext4 rw,noatime,nodiratime,data=ordered 0 0/dev/mapper/vgsys-ora_redo /db/u02 xfs rw,noatime,nodiratime,attr2,nobarrier,inode64,logbsize=256k,noquota 0 0

Изначально под эти условия нагрузки субд транзакциями хотел использовать SLOB-утиту
У неё есть такая замечательная особенность, процитирую автора:


At the heart of SLOB is the SLOB method. The SLOB Method aims to test platforms
without application contention. One cannot drive maximum hardware performance
using application code that is, for example, bound by application locking or even
sharing Oracle Database blocks. Thats rightthere is overhead when sharing data
in data blocks! But SLOBin its default deploymentis immune to such contention.

Эта декларация: соответствует, так и есть.


Удобно регулировать степень параллелизма скл-сессий, это ключ -t запуска утилиты runit.sh из состава SLOB-а


Регулируется процент дмл-команд, в том кол-ве скл-ей которые отправляет в субд, каждая скл-сессия, параметр UPDATE_PCT


Отдельно и очень удобно: SLOB сам, перед и после сессии нагрузки готовит статспак, или awr-снапшоты (что задано готовить).


Однако выяснилось что SLOB не поддерживает работу скл-сессий с продолжительностью менее 30 секунд.


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


Уточню по нагрузчику что и как он делает, для ясности.


По существу нагрузчик выглядит так:


Код воркера
function dotx(){local v_period="$2"[ -z "v_period" ] && v_period="0"source "/home/oracle/testingredotracе/config.conf"$ORACLE_HOME/bin/sqlplus -S system/${v_system_pwd} << __EOF__whenever sqlerror exit failureset verify offset echo offset feedback offdefine wnum="$1"define period="$v_period"set appinfo worker_&&wnumdeclare v_upto number; v_key  number; v_tots number; v_cts  number;begin select max(col1) into v_upto from system.testtab_&&wnum; SELECT (( SYSDATE - DATE '1970-01-01' ) * 86400 ) into v_cts FROM DUAL; v_tots := &&period + v_cts; while v_cts <= v_tots loop  v_key:=abs(mod(dbms_random.random,v_upto));  if v_key=0 then   v_key:=1;  end if;  update system.testtab_&&wnum t  set t.object_name=translate(dbms_random.string('a', 120), 'abcXYZ', '158249')  where t.col1=v_key  ;  commit;  SELECT (( SYSDATE - DATE '1970-01-01' ) * 86400 ) into v_cts FROM DUAL; end loop;end;/exit__EOF__}export -f dotx

Запускаются воркеры таким образом:


Запуск воркеров
echo "starting test, duration: ${TEST_DURATION}" >> "$v_logfile"for((i=1;i<="$SQLSESS_COUNT";i++))do echo "sql-session: ${i}" >> "$v_logfile" dotx "$i" "${TEST_DURATION}" &doneecho "waiting..." >> "$v_logfile"wait

А таблицы для воркеров готовятся так:


Создание таблиц
function createtable() {source "/home/oracle/testingredotracе/config.conf"$ORACLE_HOME/bin/sqlplus -S system/${v_system_pwd} << __EOF__whenever sqlerror continueset verify offset echo offset feedback offdefine wnum="$1"define ts_name="slob"begin execute immediate 'drop table system.testtab_&&wnum';exception when others then null;end;/create table system.testtab_&&wnum tablespace &&ts_name asselect rownum as col1, t.*from sys.dba_objects twhere rownum<1000;create index testtab_&&wnum._idx on system.testtab_&&wnum (col1);--alter table system.testtab_&&wnum nologging;--alter index system.testtab_&&wnum._idx nologging;exit__EOF__}export -f createtableseq 1 1 "$SQLSESS_COUNT" | xargs -n 1 -P 4 -I {} -t bash -c "createtable \"{}\"" | tee -a "$v_logfile"echo "createtable done" >> "$v_logfile"

Т.е. под каждый воркер (практически: отдельная скл-сессия в субд) создаётся отдельная таблица, с которой и работает воркер.


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


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


Причём достаточно долгого времени, чтобы, например, точно произошёл, и не один раз лог-свичинг.


Ну и соответственно возникали, с этим связанные, затраты и эффекты.


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


Кусок статспак-отчёта, с описанием работы субд под нагрузкой
Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---          2929910313 XE                  1 07-Sep-20 23:12 18.0.0.0.0  NOHost Name             Platform                CPUs Cores Sockets   Memory (G)~~~~ ---------------- ---------------------- ----- ----- ------- ------------     billing.izhevsk1 Linux x86 64-bit           2     2       1         15.6Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment~~~~~~~~    ---------- ------------------ -------- --------- ------------------Begin Snap:       1630 07-Sep-20 23:12:27       55        .7  End Snap:       1631 07-Sep-20 23:20:29       62        .6   Elapsed:       8.03 (mins) Av Act Sess:       8.4   DB time:      67.31 (mins)      DB CPU:      15.01 (mins)Cache Sizes            Begin        End~~~~~~~~~~~       ---------- ----------    Buffer Cache:     1,392M              Std Block Size:         8K     Shared Pool:       288M                  Log Buffer:   103,424KLoad Profile              Per Second    Per Transaction    Per Exec    Per Call~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------      DB time(s):                8.4                0.0        0.00        0.20       DB CPU(s):                1.9                0.0        0.00        0.04       Redo size:        7,685,765.6              978.4   Logical reads:           60,447.0                7.7   Block changes:           47,167.3                6.0  Physical reads:                8.3                0.0 Physical writes:              253.4                0.0      User calls:               42.6                0.0          Parses:               23.2                0.0     Hard parses:                1.2                0.0W/A MB processed:                1.0                0.0          Logons:                0.5                0.0        Executes:           15,756.5                2.0       Rollbacks:                0.0                0.0    Transactions:            7,855.1

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


  1. Размер журнальных групп бд. диапазон значений: [32, 1024] Мбайт;
  2. Кол-во журнальных групп бд. диапазон значений: [2,32];
  3. log_archive_max_processes диапазон значений: [1,8];
  4. commit_logging допускается два значения: batch|immediate;
  5. commit_wait допускается два значения: wait|nowait;
  6. log_buffer диапазаон значений: [2,128] Мбайт.
  7. log_checkpoint_timeout диапазаон значений: [60,1200] секунд
  8. db_writer_processes диапазаон значений: [1,4]
  9. undo_retention диапазаон значений: [30;300] секунд
  10. transactions_per_rollback_segment диапазаон значений: [1,8]
  11. disk_asynch_io допускается два значения: true|false;
  12. filesystemio_options допускаются такие значения: none|setall|directIO|asynch;
  13. db_block_checking допускаются такие значения: OFF|LOW|MEDIUM|FULL;
  14. db_block_checksum допускаются такие значения: OFF|TYPICAL|FULL;

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


Но.


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


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


Т.о., теперь о коде.


Выше говорил о cran-r, т.е.: все манипуляции, с настраиваемой системой оркестрируются в виде R-скрипта.


Собственно задание, анализ, подбор по значению метрики, векторов состояния системы: это пакет GA (документация)


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


А мой вектор, из значений настроечных параметров: это 14-ть величин целые числа и строковые значения.


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


Т.о., в итоге, основной кусок R-скрипта выглядит так:


Вызов GA::ga
cat( "", file=v_logfile, sep="\n", append=F)pSize = 10elitism_value=1pmutation_coef=0.8pcrossover_coef=0.1iterations=50gam=GA::ga(type="real-valued", fitness=evaluate,lower=c(32,2, 1,1,1,2,60,1,30,1,0,0, 0,0), upper=c(1024,32, 8,10,10,128,800,4,300,8,10,40, 40,30),popSize=pSize,pcrossover = pcrossover_coef,pmutation = pmutation_coef,maxiter=iterations,run=4,keepBest=T)cat( "GA-session is done" , file=v_logfile, sep="\n", append=T)gam@solution

Тут, с помощью lower и upper атрибутов подпрограммы ga задаётся, по сути, область поискового пространства, внутри которого будет выполнятся поиск такого вектора (или векторов) для которых будет получено максимальное значение фитнесс-функции.


ga-подпограмма выполняет поиск максимизируя фитнесс-функцию.


Ну, т.о., получается что, в данном случае, надо чтобы фитнесс-функция, понимая вектор как набор значений для определённых параметров субд, получала метрику, от субд.


Т.е.: сколько, при данной настройке субд и данной нагрузке на субд: субд обрабатывает транзакций в секунду.


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


  1. Обработка входного вектора чисел преобразование его в значения для параметров субд.
  2. Попытка создания заданного кол-ва редо-групп, заданного размера. Причём попытка: может быть неудачной.
    Уже существовавшие в субд журнальные группы, в каком то кол-ве и какого то размера, для чистоты эксперимента д.б. удалены.
  3. При успехе предыдущего пункта: задание базе значений конфигурационных параметров (опять же: может быть сбой)
  4. При успехе предыдущего пункта: остановка субд, запуск субд для того чтобы вновь заданные значения параметров вступили в силу. (опять же: может быть сбой)
  5. При успехе предыдущего пункта: выполнить нагрузочный тест. получить метрику от субд.
  6. Вернуть субд к исходному состоянию, т.е. удалить дополнительные журнальные группы, вернуть в работу исходную конфигурацию субд.

Код фитнесс-функции
evaluate=function(p_par) {v_module="evaluate"v_metric=0opn=NULLopn$rg_size=round(p_par[1],digit=0)opn$rg_count=round(p_par[2],digit=0)opn$log_archive_max_processes=round(p_par[3],digit=0)opn$commit_logging="BATCH"if ( round(p_par[4],digit=0) > 5 ) { opn$commit_logging="IMMEDIATE"}opn$commit_logging=paste("'", opn$commit_logging, "'",sep="")opn$commit_wait="WAIT"if ( round(p_par[5],digit=0) > 5 ) { opn$commit_wait="NOWAIT"}opn$commit_wait=paste("'", opn$commit_wait, "'",sep="")opn$log_buffer=paste(round(p_par[6],digit=0),"m",sep="")opn$log_checkpoint_timeout=round(p_par[7],digit=0)opn$db_writer_processes=round(p_par[8],digit=0)opn$undo_retention=round(p_par[9],digit=0)opn$transactions_per_rollback_segment=round(p_par[10],digit=0)opn$disk_asynch_io="true"if ( round(p_par[11],digit=0) > 5 ) { opn$disk_asynch_io="false"} opn$filesystemio_options="none"if ( round(p_par[12],digit=0) > 10 && round(p_par[12],digit=0) <= 20 ) { opn$filesystemio_options="setall"}if ( round(p_par[12],digit=0) > 20 && round(p_par[12],digit=0) <= 30 ) { opn$filesystemio_options="directIO"}if ( round(p_par[12],digit=0) > 30 ) { opn$filesystemio_options="asynch"}opn$db_block_checking="OFF"if ( round(p_par[13],digit=0) > 10 && round(p_par[13],digit=0) <= 20 ) { opn$db_block_checking="LOW"}if ( round(p_par[13],digit=0) > 20 && round(p_par[13],digit=0) <= 30 ) { opn$db_block_checking="MEDIUM"}if ( round(p_par[13],digit=0) > 30 ) { opn$db_block_checking="FULL"}opn$db_block_checksum="OFF"if ( round(p_par[14],digit=0) > 10 && round(p_par[14],digit=0) <= 20 ) { opn$db_block_checksum="TYPICAL"}if ( round(p_par[14],digit=0) > 20 ) { opn$db_block_checksum="FULL"}v_vector=paste(round(p_par[1],digit=0),round(p_par[2],digit=0),round(p_par[3],digit=0),round(p_par[4],digit=0),round(p_par[5],digit=0),round(p_par[6],digit=0),round(p_par[7],digit=0),round(p_par[8],digit=0),round(p_par[9],digit=0),round(p_par[10],digit=0),round(p_par[11],digit=0),round(p_par[12],digit=0),round(p_par[13],digit=0),round(p_par[14],digit=0),sep=";")cat( paste(v_module," try to evaluate vector: ", v_vector,sep="") , file=v_logfile, sep="\n", append=T)rc=make_additional_rgroups(opn)if ( rc!=0 ) { cat( paste(v_module,"make_additional_rgroups failed",sep="") , file=v_logfile, sep="\n", append=T) return (0)}v_rc=0rc=set_db_parameter("log_archive_max_processes", opn$log_archive_max_processes)if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("commit_logging", opn$commit_logging )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("commit_wait", opn$commit_wait )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("log_buffer", opn$log_buffer )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("log_checkpoint_timeout", opn$log_checkpoint_timeout )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("db_writer_processes", opn$db_writer_processes )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("undo_retention", opn$undo_retention )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("transactions_per_rollback_segment", opn$transactions_per_rollback_segment )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("disk_asynch_io", opn$disk_asynch_io )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("filesystemio_options", opn$filesystemio_options )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("db_block_checking", opn$db_block_checking )if ( rc != 0 ) {  v_rc=1 }rc=set_db_parameter("db_block_checksum", opn$db_block_checksum )if ( rc != 0 ) {  v_rc=1 }if ( rc!=0 ) { cat( paste(v_module," can not startup db with that vector of settings",sep="") , file=v_logfile, sep="\n", append=T) rc=stop_db("immediate") rc=create_spfile() rc=start_db("") rc=remove_additional_rgroups(opn) return (0)}rc=stop_db("immediate")rc=start_db("")if ( rc!=0 ) { cat( paste(v_module," can not startup db with that vector of settings",sep="") , file=v_logfile, sep="\n", append=T) rc=stop_db("abort") rc=create_spfile() rc=start_db("") rc=remove_additional_rgroups(opn) return (0)}rc=run_test()v_metric=getmetric()rc=stop_db("immediate")rc=create_spfile()rc=start_db("")rc=remove_additional_rgroups(opn)cat( paste("result: ",v_metric," ",v_vector,sep="") , file=v_logfile, sep="\n", append=T)return (v_metric)}

Т.о. вся работа: выполняется в фитнесс-функции.


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


Это, по сути и есть процесс поиска оптимального набора хромосом вектором, в N-мерном пространстве поиска.


Очень внятное, подробное объяснение, с примерами R-кода, работы генетического алгоритма.


Отдельно отмечу два технических момента.


Вспомогательные вызовы, из ф-ции evaluate, например остановка-запуск, задание значения параметра субд, выполняются на основе cran-r ф-ции system2


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


Например:


set_db_parameter
set_db_parameter=function(p1, p2) {v_module="set_db_parameter"v_cmd="/home/oracle/testingredotracе/set_db_parameter.sh"v_args=paste(p1," ",p2,sep="")x=system2(v_cmd, args=v_args, stdout=T, stderr=T, wait=T)if ( length(attributes(x)) > 0 ) { cat(paste(v_module," failed with: ",attributes(x)$status," ",v_cmd," ",v_args,sep=""), file=v_logfile, sep="\n", append=T) return (attributes(x)$status)}else { cat(paste(v_module," ok: ",v_cmd," ",v_args,sep=""), file=v_logfile, sep="\n", append=T) return (0)}}

Второй момент строка, evaluate ф-ции, с сохранением конкретного значения метрики и ей соответствующего настроечного вектора, в лог-файл:


cat( paste("result: ",v_metric," ",v_vector,sep="") , file=v_logfile, sep="\n", append=T)

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


Т.е.: можно будет провести attribute-importamce анализ.


Итак, что может получится.


В виде графика, если упорядочить тесты по возрастанию метрики, картина такая:


image


Ну. Много это, или мало, ~8тыс tps: вопрос отдельный.


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


Динамика тут хорошая.


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


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


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


Выполнить attribute-importance можно разными способами.


Мне, для этих целей, нравится алгоритм randomForest одноименного R-пакета (документация)
randomForest, как я понимаю его работу вообще и его подход к оценке важности атрибутов в частности, строит некую модель зависимости переменной-отклика, от атрибутов.


В нашем случае переменная отлика это метрика получаемая от субд, в нагрузочных тестах: tps;
А атрибуты это компоненты настроечного вектора.


Так вот randomForest оценивает важность каждого атрибута модели двумя числами: %IncMSE как наличие/отстутсвие данного атрибута, в модели, изменяет MSE-качество этой модели (Mean Squared Error);


И IncNodePurity это число, которое отображает насколько качественно, по значениям данного атрибута, можно разделить датасет с наблюдениями, так чтобы в одной части оказались данные, с каким то одним значением объясняемой метрики, а в другой с другим значением метрики.
Ну т.е.: насколько это классифицирующий атрибут (наиболее внятное, рускояз-е пояснение по рандомфорест-у видел тут).


Рабоче-крестьянский R-код, для обработки датасета с итогами нагрузочных тестов:


x=NULLv_data_file=paste('/tmp/data1.dat',sep="")x=read.table(v_data_file, header = TRUE, sep = ";", dec=",", quote = "\"'", stringsAsFactors=FALSE)colnames(x)=c('metric','rgsize','rgcount','lamp','cmtl','cmtw','lgbffr','lct','dbwrp','undo_retention','tprs','disk_async_io','filesystemio_options','db_block_checking','db_block_checksum')idxTrain=sample(nrow(x),as.integer(nrow(x)*0.7))idxNotTrain=which(! 1:nrow(x) %in% idxTrain )TrainDS=x[idxTrain,]ValidateDS=x[idxNotTrain,]library(randomForest)#mtry=as.integer( sqrt(dim(x)[2]-1) )rf=randomForest(metric ~ ., data=TrainDS, ntree=40, mtry=3, replace=T, nodesize=2, importance=T, do.trace=10, localImp=F)ValidateDS$predicted=predict(rf, newdata=ValidateDS[,colnames(ValidateDS)!="metric"], type="response")sum((ValidateDS$metric-ValidateDS$predicted)^2)rf$importance

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


Можно воспользоваться R-пакетом caret, не суть важно.


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


image


Ну. Т.о., можно приступать к глобальным осмыслениям:


  1. Получается так что наиболее значимым, в данных условиях тестирования, оказался параметр commit_wait
    Технически, он задаёт режим выполнения io-операции записи редо-данных, из лог-буфера субд, в current-журнальную группу: синхронный, или асинхронный.
    Значение nowait при котором получается практически вертикальный, кратный прирост значения tps-метрики: это включение асинк-моды io в редо-группы.
    Отдельный вопрос надо, или не надо так делать в продовой бд. Тут я ограничиваюсь только констатацией: это значимый фактор.
  2. Логично что размер лог-буффера субд: оказывается значимым фактором.
    Чем меньше размер лог-буфера, тем меньше его буферизующая способность, тем чаще случаются его переполнения и/или не возможность выделить в нём свободную область под порцию новых редо-данных.
    А значит: задержки связанные с аллоцированием пространства в лог-буффере и/или сбросом редо-данных из него в редо-группы.
    Эти задержки, конечно же должны влиять и влияют на пропускную способность субд по транзакциям.
  3. Параметр db_block_checksum: ну, тоже, в общем то понятно обработка транзакций приводит к образованию дарти-блоков в буферном кеше субд.
    Которые, при включенной проверке чексумм датаблоков, базе приходится обрабатывать вычислять эти чексуммы от тела датаблока, сверять их с тем что написано в хидере датаблока: совпадает/не совпадает.

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


Ну и глобальный вывод.


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


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


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


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


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


Практически это: размен не знания системы, на затраты по подготовке вот такого тестирования работы системы.


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


Спасибо за ваше внимание, время.

Подробнее..

FlexCube внедрение революционной бэк-офисной платформы в Росбанке

22.06.2020 20:08:04 | Автор: admin
Друзья, привет!

Я Никита Климов, Platform Owner Oracle FlexCube (FCUBS) для процессинга операций корпоративных депозитов, межбанковских кредитов, валютных операций и деривативов в Росбанке. Сегодня я расскажу, как мы внедряли платформу FCUBS и в чем уникальность этого проекта для российского рынка. Все подробности под катом.

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

Архитектура

Архитектура системы это классическая трехзвенка. В нашем случае backend это Oracle 12c (правда, он на текущий момент уже снят с поддержки, и мы переходим на 19сно это уже совсем другая история) и frontend IBM WebSphere. Искушенный читатель сразу задаст вопрос а почему не использовать нативный для Oracle Weblogic? И, безусловно, будет прав, потому что это первое, что рекомендовал нам сам Oracle. Но, так вышло, что для банка стандартом является именно сервер приложений IBM WebSphere, к тому же у нас ULA на этот продукт, и было принято решение адаптировать слой приложения под особенности WebSphere. Не сказать, что это было очень трудной задачей, однако ряд особенностей организации внутренних очередей все же имелся, и нашей команде пришлось провести немало часов на трехсторонних конф-коллах с поддержками Oracle и IBM.
image

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

Функциональность

Как я уже отметил ранее, система из коробки была совершенно не готова к российским реалиям, начиная с отсутствия плана счетов и заканчивая налоговым учетом. По сути это было просто ядро с набором событийных моделей, из которого надо строить космический корабль. Следовательно, вооружившись функциональными требованиями от бизнеса, мы приступили к разработке своего custom слоя на основе ядра системы. Мы разработали свой Accounting engine для генерации двадцатизначных счетов и приступили к реализации STP процессов. Исключить ручное вмешательство пользователей оказалось нетривиальной задачей, и не решалось лишь с помощью триггеров на уровне СУБД. Пришлось строить событийную логику на JOB и вводить расписание заданий. Этого тоже оказалось недостаточно, и мы вынуждены были использовать Quartz, на основе которого мы и автоматизировали наш workflow. В результате у нас в полностью автоматическом процессе происходит следующее:

  • Контракт попадает к нам из фронтовой системы Kondor+, и в зависимости от его суммы он либо автоматически авторизовывается, либо уходит на авторизацию к бизнесу;
  • После успешной авторизации система анализирует клиента является ли он клиентом головного офиса, а значит его счета лежат в GL1, либо это клиент регионов, а значит его счета лежат в GL2. Есть еще случай, когда это совершенно новый клиент, и тогда мы должны запросить его в нашей CRMсистеме и на основании полученной информации инициировать ему открытие необходимых счетов в соответствующей GL;
  • В результате процессинга система в режиме онлайн запрашивает остатки по счетам и при наличии таковых генерирует и передает в соответствующую GL необходимые проводки, формирует и отправляет SWIFT сообщения и платежки в ЕРЦ;
  • Внутри дня в системе происходят стандартные операции погашения, начисления процентов, досрочное закрытие и т.д;
  • Различную информацию о движениях по счету, контрагентах и контрактах мы автоматически передаем в ФНС, AML, Nostro. Также не забыли и об Интернет-Клиент Банке, через который клиенты видят, что происходит с их счетами после открытий и погашений депозитов;
  • Подготавливаем различную информацию для обязательной и управленческой отчетности и отдаем ее в DWH тут стоить отметить, что мы как делаем классические view для забора информации, так и генерируем транзакционные логи для IBM CDC, который в режиме онлайн забирает и агрегирует эту информацию.


Интеграция
image
Тут я для наглядности приложу нашу архитектуру и скажу лишь, что в связи с выбором frontend IBM WebSphere, было принято решение отказаться от стандартного для FCUBS Gateway, который разворачивается как дополнительное приложение и работает по старинке с листнерами и очередями, и перейти на работу c MDB Activation Specification. В результате чего мы разработали дополнительные интеграционные приложения, опубликовали их на нашем сервере и подключили к банковской интеграционной шине для взаимодействия с другими системами.
Кроме этого, у нас так же используется интеграция по средствам Systematica Modullar на основе TIBCO Rendezvous, общающийся с нашим фронтом и являющейся входной точкой для всех контрактов и ETL средство IBM DataStage. При этом функциональность на DataStage используется для интеграций, не связанных с DWH. Для одной из GL cпециально разработана логика батчевой загрузки\выгрузки данных, с проверкой статусов и breakpoints для ожидания вычислений.
image
ИТОГИ

  1. Заменили морально и технически устаревшую систему
  2. На основе ядра FlexCube создали свою платформу с неограниченными возможностями по параметризации и вариациям учета
  3. Минимизировали участие пользователей в процессе обработки дня
  4. Оптимизировали время выполнения EOD 15 минут вместо 3 часов ранее.
  5. Создали внутри банка центр компетенций и можем поддерживать и развивать платформу независимо от поставщика
  6. Практически неограниченно можем изменять usability пользовательского интерфейса и создавать любые проверочные экраны консолидированной информации для удобства контроля
  7. Внедрили систему мониторинга контрольных точек для беспрерывного процесса обработки
  8. Создали платформу, на которой готовы реализовать любой банковский продукт

image
Подробнее..

Категории

Последние комментарии

  • Имя: Макс
    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