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

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

Не раз наша команда в Каруне сталкивались с задачей, связанной с хранением и использованием 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;$$;

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

Волшебный 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 раз.

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

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

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

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

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

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

• цветовая и текстовая кодировка разных видов

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

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

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

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

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

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

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

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

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

Иногда при выполнении длительных или плохо написанных запросов в 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

Сервер быстро упал - быстро поднялся. Но место на диске у нас убыло почти на 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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результаты

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

Это вольный перевод поста одного из сильных разработчиков 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.

Перевод материала подготовлен в рамках курса Экспресс-курс по управлению миграциями (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)

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).

Пример:

Как видите, при "_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

В большинстве серверов 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.

(статья обновлена в мае 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/

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

Если вы работаете с сайтом, который постепенно растет, - увеличивается количество товаров, трафик с рекламы - то рано или поздно придется перейти в режим работы 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-загрузки данных;

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

Вывод

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

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

Если вы посмотрите на то, как ваш скрипт решает нужно или нет перестраивать индексы, и тоже самое касается планов обслуживания (я запускал ПРОФАЙЛЕР ДА ЗДРАВСТВУЕТ ПРОФАЙЛЕР ВПЕРЁД ПРОФАЙЛЕР чтобы проверить), вы увидите, что они выполняют запрос к 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 на Хабре - принимайте участие в опросе.

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

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

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

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

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

Множество записей данных, собранных вместе, образуют базу данных. Базы данных обычно создаются для того, чтобы пользователи могли обращаться к большому количеству данных и массово выполнять с ними определенные операции.База данных может хранить что угодно: представьте себе, например, блокнот вашей бабушки со всеми ее вкусными рецептами, учетную книгу ваших родителей, куда они записывают все доходы и расходы, или свою страницу в 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 пользователям иногда приходится прописывать собственную логику, чтобы добавить уникальный ключ к каждой записи и тем самым обеспечить доступ к записям. В большинстве стандартных баз данных NoSQL, таких как Firebase и MongoDB, для хранения данных используется формат JSON. Благодаря этому очень легко и удобно выполнять операции с данными из веб-приложений, используя JavaScript, Python, Ruby и т.д.

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

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

Работа с РСУБД

Основное внимание мы уделим РСУБД, так как именно эти системы в большинстве случаев мы будем использовать для хранения пространственных данных и работы с ними. В качестве примера мы будем использовать PostgreSQL, поскольку это самая перспективная реляционная база данных с открытым исходным кодом, а ее расширение PostGIS позволяет работать и с пространственными данными. Вы можете установить PostgreSQL, следуя инструкциям из документации. Помимо PostgreSQL рекомендуется также загрузить и установить pgAdmin. Платформа pgAdmin предоставляет веб-интерфейс для взаимодействия с базой данных. Также для этого можно загрузить и установить какое-либо другое совместимое ПО или использовать командную строку.

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

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

В инструменте запросов (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, который вызывается следующим образом:

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. Практика применения. Цели занятия: настроить бэкапы; восстановить информацию после сбоя. Регистрация здесь.

Добрый день, коллеги. В этой статье я бы хотел затронуть тему таблиц с типом Row. Этот тип таблиц для многих администраторов баз данных, долгое время оставался наиболее естественным типом, так сказать типом по умолчанию. Таблицы типа COLUMN в основнов встречались в хранилищах данных (Data Warehouse), то есть базах данных с преобладающей нагрузкой типа OLAP.

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

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

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

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

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

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

Эти данные перемещаются из основной памяти (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 продолжают использоваться и строковые таблицы. В основном такие таблицы используются как технические или настроечные с относительно небольшим количеством записей, при этом основная роль отдана таблицам с колоночным типом хранения.