PostgreSQL *

Обновили курс DBA2 «Администрирование PostgreSQL 16. Настройка и мониторинг»

Компания Postgres Professional выпустила обновление курса DBA2 «Администрирование PostgreSQL 16. Настройка и мониторинг». Переработку и актуализацию материалов выполнили специалисты отдела образования Игорь Гнатюк и Илья Баштанов.

Обновлённая версия курса учитывает возможности, появившиеся в PostgreSQL 14, 15 и 16. Ряд тем был переработан, чтобы лучше отражать современные функции и возможности СУБД.

Этот курс – логичное продолжение DBA1. Если вы уже знакомы с основами PostgreSQL и Unix, то DBA2 – это следующий шаг. Он позволяет получить навыки, необходимые для:

• тонкой настройки конфигурационных параметров с пониманием внутренней организации сервера;

• эффективного мониторинга сервера с дальнейшей итеративной настройкой;

• работы с параметрами, связанными с локализацией, управления расширениями и обновления сервера.

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

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

Курс уже находится в свободном доступе:

https://postgrespro.ru/education/courses/DBA2

5 утра, чашка кофе, такси, самолёт ...

#PostgresPro #pgproday спасибо за интересный и познавательный день. Новые знакомства, новые знания и новые возможности. Организация мероприятий как всегда на высшем уровне.

До встречи на Pg Conf 2025.

PGConf.Russia 2025 ждёт доклады

Мы ищем тех, кто горит PostgreSQL так же, как и мы. Если вы администратор баз данных, разработчик, архитектор или DevOps-инженер, и в вашей работе PostgreSQL играет важную роль – нам есть что обсудить.

PGConf.Russia 2025 пройдёт 31 марта – 1 апреля в Центре международной торговли в Москве и онлайн. 

О чём рассказать?

Возможные темы для докладов на PGConf.Russia 2025:  

• Практический опыт администрирования PostgreSQL, оптимизация производительности и автоматизация задач.

• Архитектурные решения для обеспечения отказоустойчивости и масштабирования PostgreSQL: резервное копирование, восстановление, кластеризация, шардирование.

• Инструменты и методики эффективной миграции на PostgreSQL с других СУБД или устаревших версий.

• Опыт использования новых возможностей и расширений PostgreSQL, их применение и перспективы.

Почему сто́ит выступить?

PGConf.Russia 2025 — это:

• Большая аудитория. 1 500+ увлечённых профессионалов, готовых учиться, обмениваться опытом и задавать каверзные вопросы. 

• Признание и уважение. Ваш опыт ценен для сообщества. Выступление на PGConf.Russia – это возможность заявить о себе, повысить профессиональный статус и получить заслуженное признание.

• Бесплатное участие и размещение. Мы ценим вклад спикеров. Для докладчиков участие в конференции и размещение – за наш счёт.

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

Как подать заявку?

До 23 февраля 2025 года зайдите на сайт PGConf.Russia 2025 и нажмите кнопку «Подать доклад». Заполните название и аннотацию доклада, выберите формат, если нужно, оставьте дополнительный комментарий для программного комитета.

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

И ещё раз о внешних ключах

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

Мой тестовый пример (все имена реальных объектов заменены, планы и цифры реальные). Выполняю 3 варианта валидации FK c разными настройками сессии, план исполнения смотрю через расширение pg_query_state.

Исполнение "по умолчанию" выбирается merge join.
ALTER TABLE child_t VALIDATE CONSTRAINT fk_child_parent;

select plan from pg_query_state(1483434);
                                                          plan
------------------------------------------------------------------------------------------------------------------------
 Merge Anti Join (Current loop: actual rows=0, loop number=1)                                                          +
   Merge Cond: (fk.entity_egrn_id = pk.id)                                                                             +
   ->  Index Only Scan using child_t_idx1 on child_t fk (Current loop: actual rows=1, loop number=1)    +
         Index Cond: (entity_egrn_id IS NOT NULL)                                                                      +
         Heap Fetches: 1                                                                                               +
   ->  Index Only Scan using parent_t_pkey on parent_t pk (Current loop: actual rows=113215, loop number=1)+
         Heap Fetches: 113215
(1 row)

SET enable_mergejoin TO false;
ALTER TABLE child_t VALIDATE CONSTRAINT fk_child_parent;

 select plan from pg_query_state(1483434);
                                             plan
-----------------------------------------------------------------------------------------------
 Hash Anti Join (Current loop: actual rows=0, loop number=1)                                  +
   Hash Cond: (fk.entity_egrn_id = pk.id)                                                     +
   ->  Seq Scan on child_t fk (Current loop: actual rows=1, loop number=1)            +
         Filter: (entity_id IS NOT NULL)                                                 +
   ->  Hash (Current loop: actual rows=0, loop number=1)                                      +
         Buckets: 67108864  Batches: 8  Memory Usage: 165802kB                                +
         ->  Seq Scan on parent_t pk (Current loop: actual rows=33979819, loop number=1)

SET enable_hashjoin TO false;
SET enable_mergejoin TO false;
ALTER TABLE child_t VALIDATE CONSTRAINT fk_child_parent;

select plan from pg_query_state(1483434);
                                               plan
---------------------------------------------------------------------------------------------------
 Nested Loop Anti Join (Current loop: actual rows=0, loop number=1)                               +
   ->  Seq Scan on child_t fk (Current loop: actual rows=329042, loop number=1)           +
         Filter: (entity_id IS NOT NULL)                                                     +
   ->  Index Only Scan using parent_t_pkey on parent_t pk (actual rows=1 loops=329041)+
         Index Cond: (id = fk.entity_id)                                                     +
         Heap Fetches: 329041

Почему всё это может быть важно:
1. Вы банально могли что-то делать в своей сессии и манипулировать её состоянием, например, "форсить" вычитку через Nested Loop, а затем запустить построение/валидацию FK на громадных таблицах.
2. По каким-то причинам PG выбрал неподходящий план, и вы знаете, что другой способ будет наверняка лучше :)

И ещё раз о внешних ключах

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

Представлю мой тестовый пример (все имена реальных объектов заменены, планы и цифры реальные). Выполняю 3 варианта валидации FK c разными настройками сессии, план исполнения смотрю через расширение pg_query_state.

Исполнение по умолчанию выбирается merge join.
ALTER TABLE child_t VALIDATE CONSTRAINT fk_child_parent;

select plan from pg_query_state(1483434);
                                                          plan
------------------------------------------------------------------------------------------------------------------------
 Merge Anti Join (Current loop: actual rows=0, loop number=1)                                                          +
   Merge Cond: (fk.entity_id = pk.id)                                                                             +
   ->  Index Only Scan using child_t_idx1 on child_t fk (Current loop: actual rows=1, loop number=1)    +
         Index Cond: (entity_id IS NOT NULL)                                                                      +
         Heap Fetches: 1                                                                                               +
   ->  Index Only Scan using parent_t_pkey on parent_t pk (Current loop: actual rows=113215, loop number=1)+
         Heap Fetches: 113215
(1 row)

SET enable_mergejoin TO false;
ALTER TABLE child_t VALIDATE CONSTRAINT fk_child_parent;

 select plan from pg_query_state(1483434);
                                             plan
-----------------------------------------------------------------------------------------------
 Hash Anti Join (Current loop: actual rows=0, loop number=1)                                  +
   Hash Cond: (fk.entity_egrn_id = pk.id)                                                     +
   ->  Seq Scan on child_t fk (Current loop: actual rows=1, loop number=1)            +
         Filter: (entity_id IS NOT NULL)                                                 +
   ->  Hash (Current loop: actual rows=0, loop number=1)                                      +
         Buckets: 67108864  Batches: 8  Memory Usage: 165802kB                                +
         ->  Seq Scan on parent_t pk (Current loop: actual rows=33979819, loop number=1)

SET enable_hashjoin TO false;
SET enable_mergejoin TO false;
ALTER TABLE child_t VALIDATE CONSTRAINT fk_child_parent;

select plan from pg_query_state(1483434);
                                               plan
---------------------------------------------------------------------------------------------------
 Nested Loop Anti Join (Current loop: actual rows=0, loop number=1)                               +
   ->  Seq Scan on child_t fk (Current loop: actual rows=329042, loop number=1)           +
         Filter: (entity_id IS NOT NULL)                                                     +
   ->  Index Only Scan using parent_t_pkey on parent_t pk (actual rows=1 loops=329041)+
         Index Cond: (id = fk.entity_id)                                                     +
         Heap Fetches: 329041

Почему всё это может быть важно:
1. Вы банально могли что-то делать в своей сессии и манипулировать её состоянием, например "форсить" вычитку через Nested Loop, а затем запустить построение/валидацию FK на громадных таблицах.
2. По каким-то причинам PG выбрал неподходящий план, и вы знаете, что другой способ будет наверняка лучше :).

Module Info в бинарных файлах модулей Postgres

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

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

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

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

Гораздо проще было бы, если бы ядро содержало в себе фунцию, например, module_info(module_name), которая позволяла бы изнутри СУБД (например, в консоли psql) определить полный путь и имя файла, содержащего искомый модуль. Более того, при наличии двух версий одного модуля в ядре (да, бывает и такое!), мы получаем возможность обнаруживать потенциальные конфликты. При этом, появляется возможность автоматизировать обнаружение модулей и их версий в системе другими модулями - да, я ненавижу использовать функцию SerializeLibraryState и другие грязные хаки для этой цели!

Ещё одна причина (уже глубоко техническая) связана с тем, что теперь (с апреля 2024 г.) расширения для текущей и последующих версий Postgres могут использовать dynamic shared memory (DSM) без необходимости быть загружаемыми при старте инстанса. Это открыло путь разработки легковесных модулей, которые могут быть загружены динамически в каждом отдельном бэкенде. Помимо производительности преимущество здесь в том, что появляется возможность реализовать технику онлайн-апгрейда расширения - установив новую версию модуля в системе под другим именем и загружая такую новую версию во вновь стартующих бэкендах мы имеем обновление функциональности на лету, без остановки инстанса! - по крайней мере, у меня в голове вырисовывается именно такой сценарий.

С другой стороны, проект omnigres (автор Yurii Rashkovskii) также пришёл к идее версионирования модулей, хотя и делает это внешним, по отношению к ядру, путём.

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

Перед тем, как начинать долгий путь обсуждения кода в hackers mailing list будет очень полезно аккумулировать опыт и мнения других разработчиков и мэйнтейнеров расширений Postgres. Нужна ли такая фича? Должна ли она быть опциональной или обязательной? Какая информация о модуле нужна (или просто будет полезна) в ваших инсталляциях?

Предлагайте ваши идеи и делитесь своим мнением в комментах, или в github-дискуссии сообщества PGEDC разработчиков расширений Postgres. Каждое мнение имеет ценность!

Раз в год-два мне приходится вспоминать, что Python — не C++.

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

Конкретнее, при переходе от этого запроса:

SELECT * FROM o_relations ORDER BY id DESC LIMIT %(limit)s

к этому:

SELECT CONCAT(entry_id::text, '|', tag_id::text) AS ids FROM o_relations ORDER BY id DESC LIMIT %(limit)s

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

Причиной тому тяжёлая конвертация данных из формата С в формат Python внутри Psycopg.

За подробностями можно сходить ко мне в блог: https://tiendil.org/ru/posts/fun-case-of-speeding-up-data-retrieval-with-psycopg

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

Условия остановки стресс-теста , по условиям AND / OR:

1. Снижение метрики производительности на X%

2. Увеличение среднего времени отклика на T%

3. Среднее время отклика >= TMax

4. RAM utilization >= R%

5. CPU utilization >= C% . (Только OR)

6. Общее время стресс теста

7. Максимальное количество активных сессий >= AMax

8. Какое то дополнительное условие ?

А тем временем, постепенно, складывается вполне себе стройная концепция применения мат. статистики и методов оптимизации при сопровождении СУБД.

1) Оптимизация конфигурационных параметров СУБД при разворачивании нового кластера PostgreSQL - метод покоординатного спуска .

2) Стресс тестирование - определение предельной нагрузки на инфраструктуру СУБД при выполнении тестового сценария.

3) Бенчмарк - фиксация базовых показателей производительности

4) Передача СУБД в разработку приложения

5) Нагрузочное тестирование и корреляционный анализ производительности в процессе разработки ПО.

6) Передача СУБД в опытную эксплуатацию

7) Стресс тестирование - определение предельной нагрузки на инфраструктуру СУБД при выполнении продуктивного сценария . Протоколирование значений для использования в качестве граничных при мониторинге .

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

9) Передача СУБД в промышленную эксплуатацию. Протоколирование граничных значений для мониторинга .

10) Корреляционный анализ производительности СУБД. Управление инцидентами , проблемами , изменениями .

Цели ясны, задачи определены . За работу товарищи!

Хорошо, когда идёшь по маршруту с заранее заданными контрольными точками.

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

Репликация делается асинхронно — данные записываются на основной сервер и только после этого отправляются на реплики.

В кластер можно объединить 3 или 5 серверов. Доступные для этого регионы: Москва, Санкт-Петербург и Нидерланды.

Стоимость репликации равна стоимости конфигурации одного сервера, умноженной на 3 или 5.

Создать PostgreSQL с репликацией → 

По итогам очередных экспериментов , в ходе отработки методологии стресс тестирования СУБД , в принципе можно было бы подготовить ещё одну статью на тему "Влияние CPU Utilization = 100% на производительность СУБД".

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

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

Событие "предельная утилизация CPU" при отсутствии события "деградации производительности СУБД" - не является стартовым событием для создания инцидента .

А почтовое оповещение мониторинга "CPU High Utilization" , я давно уже фильтрую.

По итогам анализа результатов экспериментов по стресс тестированию, наверняка будет подготовлена метрика "Performance is OK"(есть пара идей) и вот по ней и будет настроено оповещение и создание инцидента по производительности СУБД.

Чем дольше копаю тему статистического анализа производительности СУБД, тем больше удивляюсь - почему никто не занимался/не занимается использованием математических методов в DBA ? Статей и материалов практически - нет. По performance engineering - можно найти, по DBA в общем то тишина.

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

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

Почему DBA не используют математику ? Риторический вопрос ....

Однако, с бенчмарками, всё совсем не просто .

По умолчанию pgbench тестирует сценарий, примерно соответствующий TPC-B, который состоит из пяти команд SELECT, UPDATE и INSERT в одной транзакции.
https://postgrespro.ru/docs/enterprise/16/pgbench

В 1995 году TPC-A и TPC-B были признаны несостоятельными, и появился более совершенный TPC-C.
https://ibs.ru/media/etalonnye-testy-subd-chto-bylo-chto-stalo-chto-budet/

2 варианта дальнейших действий:

1. Продолжать использовать pgbench.

2. Искать реализации TPC-C и/или более новых сценариев.

Вариант самостоятельной реализации TPC-C , в виду ограниченности ресурсов - не рассматривается .

PostgreSQL 17 уже в Облаке Рег.ру

Мы обновили DBaaS, добавив в него две версии PostgreSQL: 16 и 17.  PostgreSQL 17 упрощает работу с большими нагрузками и улучшает безопасность данных. Теперь ваши проекты будут работать еще быстрее и стабильнее.

Официальный релиз PostgreSQL 17 состоялся 26 сентября 2024 года. 

Ключевые моменты версии: 

• Повышена производительность

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

• Ускорена работа с массовыми данными

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

• Упрощенная работа с JSON

  Новая команда SQL/JSON JSON_TABLE делает запросы к данным в формате JSON более гибкими и удобными.

• Улучшены механизмы логической репликации

  Это упрощает управление данными в условиях высокой доступности и делает процесс обновления на новые версии более эффективным.

Попробуйте PostgreSQL 17 в Облаке Рег.ру!

Мысли вслух.

Размещать в хабе "PostgreSQL" статьи на тему статистического анализа - не имеет практического смысла. Перенес в хаб "Математика". Может быть от математиков удастся получить более-менее полезную обратную связь. Есть надежда, что хоть, что-то не на эмоциях и IMHO-х , а на цифрах и фактах , все таки получится дождаться в комментариях. Поживём увидим.

P.S. Зарегистрировался на математических форумах . Может там ответят ....

P.P.S. Ответили и на математическом форуме и в комментариях к посту в хабе "Математика". Стиль комментариев , по сравнению с хабом "PostgreSQL" различается принципиально и очень кардинально. Все следующие статьи и посты будут размещены только в хабе "Математика" и на математических форумах. Эх, сколько времени было потрачено на пустой флейм с ремесленниками или просто флудерами :-( Надо было сразу , тему статистического анализа начинать обсуждать с математиками, а не с DBA.

Update.

Математики - читают больше.

Сергей Ким, руководитель команды разработки WMS и активный пользователь Яндекс Лавки, рассказал, про внутренний мир Лавок. 

Обсудили: 

• как Яндекс управляет лавками, узнаёт о товарных остатках и рассчитывает время курьеров, чтобы вовремя доставлять все заказы; 

• о чём можно узнать с помощью проактивных пушей изменений и периодического пула всего сразу;

• как перекладывать JSON с минимальным лагом. 

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

Интересно , возможно ли определить объем данных ( количество строк * средний размер строк ), сформированных SQL запросом ?

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

Но это, как раз, тот случай когда хотелось бы ошибиться.

В продолжении https://habr.com/ru/posts/837954/

А если для проверки нормальности распределения выборки использовать функцию normal_rand ?

——————————————

normal_rand 

normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8

Функция normal_rand выдаёт набор случайных значений, имеющих нормальное распределение (распределение Гаусса).

Параметр numvals задаёт количество значений, которое выдаст эта функция. Параметр mean задаёт медиану нормального распределения, а stddev — стандартное отклонение.

—————————————

Параметры - из проверяемой выборки .

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

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

В продолжении темы https://habr.com/ru/posts/837710/

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

Проблема в том , что со статистическими функциями в PostgreSQL не очень, а критериев много :

• критерий Шапиро-Уилка,

• критерий асимметрии и эксцесса,

• критерий Дарбина,

• критерий Д'Агостино,

• критерий эксцесса,

• критерий Васичека,

• критерий Дэвида-Хартли-Пирсона,

• критерий хи-квадрат,

• критерий Андерсона-Дарлинга,

• критерий Филлибена.

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

В развитии темы

Нагрузочное тестирование СУБД в облачной среде — часть 2. Итоги и результат https://habr.com/p/837462/

Методика проведения нагрузочного тестирования .

Задача

Оценить значение производительности СУБД при постоянной нагрузке, в условиях нестабильной инфраструктуры.

Проблема

На производительность СУБД влияет множество случайных факторов.

Гипотеза

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

Из гипотезы следует решение задачи:

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

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

Дополнение по итогам анализа результатов проведенных экспериментов

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

Достаточно выполнение условия симметричность распределения: Медиана = Мода.

Полученные средние значения ( медиана/моде) производительности будут исходным набором данных.

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

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

Дополнение

Запускать нагрузку необходимо в течении суток.

pgbench для получения результирующих значений tps использует среднее арифметическое - это серьезная проблема , потенциально способная сильно исказить результат.

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

Описательная статистика перформанс-распределений https://habr.com/p/722342/

А производительность СУБД есть случайная функция.

Вывод - не стоит использовать результаты pgbench для анализа влияния каких либо изменений и сравнения производительности СУБД.

Как инструмент создания нагрузки - да, как инструмент анализа результатов - нет.

Насколько СУБД подвержено случайным факторам, например облачной среды, показано:

Нагрузочное тестирование СУБД в облачной среде — часть 1 https://habr.com/p/837216/

Нагрузочное тестирование СУБД в облачной среде — часть 2. Итоги и результат https://habr.com/p/837462/

Продолжаем улучшать наш инструмент анализа планов PostgreSQL explain.tensor.ru. Сегодня мы представляем новую подсказку-рекомендацию и расширенный вариант визуального представления хода выполнения (посмотреть пример):

Ловим кривые индексные условия

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

Зачастую оказывается, что виной тому передача в качестве параметра-массива для ключа индекса какого-то заведомо-ложного условия типа пустого массива (= ANY('{}'::integer[])) или NULL (= ANY(NULL::integer[])).

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

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

Ход выполнения запроса

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

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

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

Закрытие темы "Гипотеза о связи относительного количества ожиданий СУБД и производительности СУБД "

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

Для проверки гипотезы была проведёна серия экспериментов по 3-м сценарям:

1) Базовая(эталонная) нагрузка.

2) Дополнительная нагрузка на CPU/RAM.

3) Очередь ожидания освобождения блокировки строк/таблиц.

Итог: Гипотеза не подтверждается экспериментальными данными : разница в соотношении между ожиданиями составляет ~3% , при деградации производительности ~34%.

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

Таким образом - изменение соотношения между ожиданиями СУБД не может являться индикатором деградации производительности.

Тема закрыта. Статья снята с публикации.

P.S. Однако, в ходе экспериментов, получен интересный побочный результат:

При использовании pgbench c параметром "--connect", отношение количества ожиданий влияющих на производительность, к количеству ожиданий не влияющих на производительность - существенно отличается (~38%).

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

О способе оценки производительности отдельного SQL запроса .

В дополнении к теме:

Размышления о мониторинге производительности отдельного SQL запроса

Для возможного варианта решения задачи:

https://habr.com/ru/posts/833162/

если производительность отдельного SQL запроса в настоящее время не мониторится.

Предположение.

Для того, чтобы оценить производительность отдельного SQL запроса необходимо и достаточно получить отношение стоимости запроса (EXPLAIN ANALYSE) к актуальному времени выполнения запроса .

Важное следствие и ограничение:

Данную оценку производительности можно получить только на данный конкретный момент времени при данном конкретном состоянии СУБД в целом.

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

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

К вопросу о важности определения понятия "производительность СУБД".

Пусть имеется некий запрос к СУБД, который важно мониторить с точки зрения эффективности и качества работы.

Сценарий 1: запрос выдает N1 строк и выполняется за время T1.

Сценарий 2: запрос выдает N2 строк и выполняется за время T2.

Вопрос: можно ли утверждать о инциденте в случае Сценария 2, если "T2 > T1 И N2 > N1" ?

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

Пару месяцев назад (точнее 15 месяцев и 12 дней) я выложил статью про исходный код PostgreSQL где рассказал про инфраструктуру узлов (struct Node) - с помощью него реализуется наследование, полиморфизм и все, все, все.

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

Так вот, эти 2 месяца выдались веселыми. Кроме одного момента. Мне надоело постоянно возиться с этими нодами. Проблема в том, что есть наследование и многие переменные имеют свой базовый тип (если не самый базовый Node, который просто 1 поле тэга) - приходится постоянно лезть в (работаю в VS Code) watch панель и писать монструозные конструкции по типу ((RestrictInfo*)((RelOptInfo*)root->rtable[0])->another_field))->value (взято из головы). Причем - чем глубже спускаешься, тем громаднее и неповоротливее выражения становятся.

Я искал различные расширения или способы, чтобы облегчить себе жизнь, но ничего кроме встроенного pprint(Node *).

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

Пока у этого расширения 2 фичи:

1. Приводит наследуемые от Node * переменные к нужному типу и отображает

2. Дампит переменную-узел в stdout с помощью вызова pprint

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

Вот ссылка на само расширение.

В продолжении темы - "время отклика СУБД" https://habr.com/ru/posts/827054/

Метрику не имеет смысла использовать для алерта о деградации производительности СУБД .

Допустим имеется OLTP нагрузка на СУБД - большое количество коротких запросов. В результате, имеем некоторое значение метрики "Среднее время отклика" = sum(total_exec_time) / sum(calls).

И вот , ситуация изменилась - запросов OLTP стало меньше , но появились аналитические/долгие запросы .

В результате значение метрики увеличивается - запросов стало меньше , общее время выполнения увеличилось.

Является ли данная ситуация алертом для создания инцидента о деградации производительности СУБД ?

Конечно же - нет.

Более того - в этом случае резко увеличится количество обрабатываемых блоков разделяемой области.

Что также не является показателем деградации производительности СУБД , даже совсем наоборот .

P.S. Использование данной метрики оправдано только в одном сценарии - нагрузочное тестирование инфраструктуры при обязательном условии постоянства нагрузки на СУБД.

О мониторинге СУБД.

Классическая метрика -"Время отклика СУБД" - в реальной жизни бесполезна.

Причина - подверженность ошибке 1-го и 2-го рода.

1) Во время аварии отклик СУБД может не только не расти, но даже уменьшаться.

2) Рост времени отклика может быть вызван снижением нагрузки на СУБД.

Утилита pgpro_pwr имеет одну очень серьезную проблему - жестко установленное расписание сбора снимков с помощью take_sample.

Чтобы получить выборки со всех включённых серверов, вызовите функцию take_sample(). Обычно достаточно получать одну-две выборки в час. Для выполнения этой функции по расписанию можно воспользоваться планировщиком cron или подобным.

Последствия:

1)Невозможно собрать статистику производительности и состояния СУБД во время инцидента производительности . Например, если снимки собираются по рекомендации, указанной в документации - каждые полчаса , то практически нельзя ничего сказать о состоянии СУБД в течении 5-10 минут, в которые было зафиксировано снижение производительности в середине 30-ти минутного промежутка.

2) Ожидания в разделе "Top waits" не коррелированы с показателями производительности СУБД. Отчет показывает ожидания отсортированные по времени. И только за весь период снимка. Установить, какое именно ожидание/ожидания (и следовательно запрос/запросы) повлияло/повлияли на деградацию производительности СУБД во время инцидента - практически невозможно.

Первые результаты использования статистических методов для анализа производительности СУБД .

• 11:10 - 13:20 : Нисходящий тренд (снижение производительности )

• 13:30 - 15:40 : Восходящий тренд ( рост производительности )

Казалось бы, в период 11:10-13:20 - инцидент деградации производительности.

НО.

Корреляционный анализ событий ожиданий и показателей производительности показал – корреляция между событиями ожидания и изменением производительности в период 11:10 – 13:20 - отсутствует.

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

1. Аномалии СУБД в период 11:10 - 13:20 – отсутствуют

2. Снижение производительности СУБД вызвано внешними причинами (снижение количества подключений, изменение характера запросов, снижением числа запросов и т.д. и т.п. ) .  Т.е. причинами, находящимися вне зоны ответственности DBA.

Обновление библиотеки asyncpg-lite до версии 0.3.1.1

Рад сообщить, что библиотека asyncpg-lite обновлена до версии 0.3.1. Все предыдущие версии были удалены и больше не доступны для установки. Для корректной работы, пожалуйста, удалите старые версии с помощью следующей команды:

pip uninstall asyncpg-lite

Чтобы установить последнюю версию, используйте:

pip install --upgrade asyncpg-lite

Актуальная версия: 0.3.1.1

Вы можете найти страницу библиотеки на GitHub по следующему адресу: asyncpg-lite на GitHub.

Что нового в версии 0.3.1.1

В этой версии все методы библиотеки были переписаны. Если ранее библиотека работала исключительно с чистым asyncpg, то теперь asyncpg используется как асинхронный драйвер для взаимодействия с PostgreSQL, а все запросы выполняются через SQLAlchemy.

Причины обновления

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

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

Благодарю за ваше внимание и поддержку!

Все те кто пользовался библиотекой asyncpg-lite. Сообщаю, что завтра вечером будет выпущено обновление библиотеки asyncpg-lite. В новой версии библиотека будет полностью переписана с использованием SQLAlchemy и asyncpg (в старой версии использовался чистый asyncpg), при этом базовый синтаксис останется практически неизменным.

Такое решение принято из-за ограничений чистого asyncpg в предотвращении SQL-инъекций. Вместо разработки собственного решения я решил воспользоваться проверенными инструментами ORM, которые предоставляет SQLAlchemy, но, при этом, я постарюсь максимально сохранить ту простоту в использовании, которая была в старых версиях asyncpg-lite.

Начиная с версии asyncpg-lite 0.3, библиотека будет основываться на SQLAlchemy и драйвере asyncpg для работы с PostgreSQL. Версии ниже 0.3 будут сняты с доступности для скачивания и установки с завтрашнего дня.

С выходом asyncpg-lite 0.3 настоятельно рекомендуем удалить старые версии библиотеки и установить актуальную.

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

Благодарю за внимание! 🚀

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

1) Статистика производительности СУБД- скользящие средние , стандартное отклонение , дисперсия : полезно для оценки текущего состояния и подготовки baselines и оповещений по проблемам производительности.

2) Статистика нагрузки на СУБД-количество подключений , гистограмма подключений , коэффициент корреляции с производительностью : очень помогает быстро обнаружить аномальное поведение приложения.

3) Статистика ожиданий СУБД - количество ожиданий , гистограмма ожиданий , коэффициент корреляции с производительностью : очень помогает быстро выявить аномалии инфраструктуры и приложения.

4)Быстрые/медленные скользящие средние, тренды, свечи, уровни поддержки - это просто красиво :-) Манагерам нравятся красивые картинки.

asyncpg-lite обновлена до версии 0.2.2.1!

В новой версии:

• Убран флаг dsn_flag (теперь достаточно не передавать данные в параметр dns и состояние этого флага будет автоматически сброшено на dsn_flag = False)

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

• Добавлен флаг debug: bool во все методы (по умолчанию его значение False)

• Исрпавлены ошибки и улучшены логи (теперь там больше полезной информации)

Зачем нужен флаг debug?

Установив этот флаг в методе вы сможете вывести в консоль дополнительную информацию, такую как параметры запроса и сам SQL-запрос.

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

С библиотекой вы сможете ознакомиться тут: asynpg-lite: Простой асинхронный менеджер для PostgreSQL на Python

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

Приглашаем на новый бесплатный вебинар «PostgreSQL: один за всех?».

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

📅 Дата: 20.06.2024

⏰ Время: 14:00-15:00 (Мск)

На вебинаре рассмотрим:

✔️ Виды СУБД.

✔️ Способы расширения PostgreSQL.

✔️ Как хранить и искать документы.

✔️ Как хранить и работать с временными рядами.

✔️ Как хранить эмбеддинги и быстро их искать.

✔️ Как строит многомерные кубы.

✔️ Когда все это стоит делать, а когда нет.

👨‍🎓 Спикер: Брейман Александр — эксперт Учебного центра IBS, кандидат технических наук, доцент департамента программной инженерии ФКН ВШЭ.

💥 Зарегистрироваться 💥

Мысли вслух - DBA ремесло или наука ?

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

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

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

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

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

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

P.S.Пример из жизни - имеется пул соединений к СУБД - как определить, что характер нагрузки изменился ?

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

1. Количество событий ожидания и показателем производительности СУБД

2. Количество соединений с конкретного ip и показателем производительности СУБД

Отрицательная корреляция между количеством событий ожидания и показателем производительности СУБД в результате оказалась очень слабая:

IO ControlFileSyncUpdate -0,155750768

Client ClientWrite -0,132699875

LWLock XactSLRU -0,123296926

...

Отрицательная корреляция между количеством соединений с конкретного ip и показателем производительности СУБД - умеренная:

XX.XXX.XXX.1 -0,43280346

XX.XXX.XXX.2 -0,41262827

XX.XXX.XXX.3 -0,409033529

XX.XXX.XXX.4 -0,388472

XX.XXX.XXX.4 -0,376145747

...

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

Или я плохо ищу или действительно , тема корреляционного анализа производительности СУБД , еще не исследовалась.

Например было бы очень интересно установить - какой из факторов оказывает максимальное влияние на производительность СУБД или наоборот - как производительность СУБД влияет на показатели СУБД и инфраструктуры :

• Количество соединений

• Утилизация CPU

• Утилизация RAM

• Утилизация I/O

• Количество ожиданий СУБД

• Показатели CPU (iowait и т.п)

• Показатель Cache Hit Ratio

• Объем обработанных блоков shared_buffer

• Что-то еще из огромного списка метрик

Postgres Professional представила новую лицензию СУБД Postgres Pro для пользователей «1С: Предприятие».

Клиенты компании теперь могут приобрести СУБД Postgres Pro Enterprise для 1С без включенного первого года стандартной технической поддержки. При этом доступ к обновлениям и новым версиям сохраняется.

По словам компании, такой вариант лицензии сокращает затраты на покупку ПО до 25%.

По поводу тестирования встроенного пула Postgres Pro, ситуация складывается странная : при использовании параметра "--connect" , если размер пула меньше чем "clients" - pgbench зависает. Все соединения имеют состояние 'Idle in transaction'.

Пока не разобрался это бага или фича.

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

Update: Вопрос закрыт - pgbench не предназначен для тестирования нагрузки с использованием пула соединений. Придется сочинять свой инструмент.