Администраторы баз данных часто ломают голову над тем, чтобы выявить самые «прожорливые» процессы, из-за которых страдает быстродействие систем. В далеком 2017-м DBA (а теперь инженер Postgres Professional) Андрей Зубков тоже задавался этим вопросом, а в результате придумал утилиту pg_profile для PostgreSQL, которая сейчас «проросла» в pgpro_pwr.
В этой статье мы расскажем о стратегическом мониторинге БД и научим выявлять узкие места в ваших БД с помощью наших утилит.
«Как ускорить выполнение запросов к базам данных?» — извечный вопрос админов и пользователей. Казалось бы, раньше всё работало неплохо, но стоило клиентской базе вырасти в пять раз, как всё замедлилось. Знакомая ситуация? В этой статье расскажем, как найти запросы, которые можно ускорить, и оптимизировать их.
Статья подготовлена по материалам выступления на конференции PGCONF.СПБ 2024.
Выбор SQL-запроса в реляционной СУБД в основном определяется пространством поиска возможных планов и техниками поиска плана в этом пространстве. У каждой СУБД оба этих фактора имеют свои особенности, что объясняет, почему иногда при миграции с одной СУБД на другую можно наблюдать как ускорения, так и провалы во времени выполнения отдельных запросов.
Здесь я привожу четыре случая, когда SQL Server позволяет строить планы запросов значительно более оптимальные, нежели это доступно PostgreSQL используя как более широкое пространство возможных планов, так и более совершенные методы оценок эффективности планов. Эти примеры: использование тредов, расширенная статистика, кэширование промежуточных результатов запроса и внутренняя параметризация. Примеры независимы и все кроме первого содержат скрипт воспроизведения - можно сразу листать на ту часть, которая выглядит интереснее.
Полагаю, знание о таких кейсах может быть полезным. Как минимум уменьшит количество стресса при миграции на PostgreSQL и возможно заинтересует кого-то настолько, чтобы начать свой проект в open-source сообществе разработчиков СУБД.
Badass Elephant
Из январского номера мы узнаём, что Тембо - имя лихого слоника из игры Tembo the Badass Elephant. Основатель и гендир Рай Уокер (Ry Walker) объявил о доступности Tembo Cloud, а в начале января взяли на работу Дэвида Уилера (David E. Wheeler) - основателя PGXN (PostgreSQL Extension Network). Компания | кампания настолько активная и шумная (в маркетинговом смысле), что кажется: они существуют уже ... годы во всяком случае, не год с небольшим.
PG-футурология
Thoughts on PostgreSQL in 2024
В своём блоге Джонатан Кац говорит, что хотел совсем чуть-чуть попредсказывать, но увлёкся - получилась немаленькая статья. Год назад это было интересно потому, что это было тогда. А сейчас это интересно потому, что это было ... тогда, то есть год назад. Начинал он с логической репликации - ну да, здесь всё успешно развивается. А вот тема HA (High Availability) актуальна в не меньшей степени, но напомним статейку из декабрьского, свежайшего Postgresso: PG Phriday: Kubernetes Killed the High Availability Star.
Aurora DSQL
Эта Аврора наделала шуму. Откликов на анонс много. Люди говорят о сомнительной (но громко заявленной) совместимости с Postgres. Официальный анонс был такой:
Мэт Гарман (Matt Garman), гендир AWS, объявил на сборище re:Invent в Лос-Анджелесе в присутствии 60 тыс. очных и 400 тыс (это ж почти полмиллиона!) онлайновых гостей о принципиально новой, геораспределённый СУБД Aurora DSQL. Более того: это была его инаугурационная гендирская речь. Так что ставки высоки. Видео есть на сайте re:Invent. Документация Aurora DSQL здесь.
Это гео‑распределённая SQL‑база с доступностью 99.999%, практически неограниченной масштабируемостью, строгой согласованностью и нулевыми заботами об управлении инфраструктрой.
Главная целевая аудитория — те, у кого приложения, обслуживающие миллионы клиентов по всему Земному Шару.
У СУБД архитектура active‑active (т. е. примерно мультимастер), и все транзакции, записанные в одном регионе, согласованно отображаются на другие регионы. И вообще утверждается, что удалось избавиться от компромиссов «или‑или» (trade‑offs): она И быстрая, И надёжная. Для этого пришлось «переизобрести» архитектуру — отвязать обработку транзакций от хранения. Обещано, что чтение и запись будут в 4 раза быстрее, чем у конкурентов.
Aurora DSQL при синхронизации узлов использует Amazon Time Sync Service на каждом экземпляре Amazon Elastic Compute Cloud (EC2), а там всё привязано к атомным часам, связь с которыми через спутники. Этот же механизм используется для работы глобальных таблиц DynamoDB.
Много лет в комьюнити PostgreSQL никто не верил что эта СУБД в принципе может использоваться в системах с большой транзакционной нагрузкой. То есть, какие-то тестовые лаборатории, бэкенд веб-приложений средней руки и так далее — вот его типичные задачи. А когда нужна серьёзная нагрузка, это уже надо брать СУБД за много денег и не сомневаться. Ну и раз никто не верил, то и не развивал особенно его в эту сторону, оставляя всё больше повисших в воздухе вопросов.
Но на практике вышло так, что наши клиенты всё чаще сталкиваются с проблемами, которые породил этот подход. Например, в международном комьюнити постгреса считается, что 64 ядра — это предельный размер сервера, где его вообще можно запустить. А мы всё чаще видим, что это становится минимальной типовой конфигурацией. Другим таким узким местом стал счётчик транзакций, ситуация с которым намного более интересная. Поэтому о нём мы сегодня и поговорим. В чём там проблема, как мы её решили, и что на эту тему думает международное комьюнити.
В то время, как пользователи видят позитивные стороны технологий, мы, разработчики, обычно сталкиваемся с ограничениями/недоработками/багами и видим наш продукт с совсем другой стороны. Вот и в этот раз: после публикации результатов сравнительного тестирования где я прогонял запросы теста Join-Order-Benchmark на базе с партициями и без, меня не отпускало ощущение, что всё-таки что-то я не досмотрел и при наличии партиций постгрес должен строить план хуже, чем без них. И это должен быть не просто баг, а технологическое ограничение. И вот, методом разглядывания потолка удалось-таки найти тонкое место - запросы с лимитами.
Оптимизация Asymmetric Join (AJ) — это новый подход к объединению партицированных связей (partitioned relation, PR) и непартицированных связей (non-partitioned relation, NR). Она заключается в том, что каждая партиция присоединяется с помощью NR, а результаты объединяются путём APPEND. Всё это выглядит как эволюция техники partitionwise join (PWJ).
Работа над ошибками
PostgreSQL: Out-of-cycle release scheduled for November 21, 2024
Дело прошлое - уже вышла 17.2 со свитой из более почтенных версий, где всё поправили. Но история поучительная.
Итак: вышла новость, с восклицательным знаком, как обычно: PostgreSQL: PostgreSQL 17.1, 16.5, 15.9, 14.14, 13.17, and 12.21 Released!
Ура! Прикрыли дыру CVE-2024-10976: в таблицах с безопасностью на уровне строк можно было подсмотреть или изменить те строки, в которые лезть не положено. После CVE-2023-2455 и CVE-2016-2193 многое поправили, но пропустили случаи подзапросов, запросов с WITH и другие. И всё это в версиях с 12 по 17. Ещё и закрыли уязвимость CVE-2024-10979. Но:
A change to ResultRelInfo - A Near Miss with with Postgres 17.1
Пользователи PostgreSQL нередко оперируют аналитическими запросами, при выполнении которых данные сортируются и группируются по разным правилам. За счёт оптимизации вычисления агрегатов и сортировок можно значительно сократить время и стоимость выполнения запросов. Об одной из таких оптимизаций — выборе порядка колонок в выражении GROUP BY — расскажем в этой статье.
Postgres уже умеет перестраивать список группируемых выражений в соответствии с порядком колонок из условия ORDER BY, чтобы исключить дополнительную сортировку и сэкономить вычислительные ресурсы. Мы пошли дальше, реализовали свою идею в дистрибутивах Postgres Pro Standard и Enterprise и вынесли патчи на обсуждение сообщества Postgres (первое и второе) в надежде, что они войдут в ближайшую версию ванильного PostgreSQL.
Ранжирование — это уникальная разновидность задач в машинном обучении, обособленная как от классификации, так и регрессии. Заключительная статья по нейрооптимизаторам в РСУБД, как ни странно, связана именно с ней. Бум в развитии подобных моделей произошёл совсем недавно — в 2023 году, что мы с вами подробно разберём. Сначала погрузимся в ранжирование в целом, а затем увидим, как в соответствии с новой постановкой задачи адаптировались методы поиска оптимального плана исполнения запроса.
Обе конференции - PGConf.СПб 2024 и PGConf.Academy 2024 - прошли в октябре. Они очень разные, но мы решили их объединить - может, как раз поэтому. Разница между ними не географическая. Даже наоборот, вышел парадокс: питерского уклона в составе докладчиков на PGConf.СПб 2024 в глаза не бросались, зато представители СПб (особенно представительницы) были более, чем заметны на московской образовательной конференции.
PGConf.СПб 2024
Прошла 1 октября. Однодневная, но в два потока уместила 18 докладов. Приятно, что открывал её доклад Павла Лузанова, а закрывал - доклад Егора Рогова - оба мои коллеги по Отделу Образования Postgres Professional.
Эта статья открывает цикл о новостях будущей, 18-ой, версии PostgreSQL. Рассмотрим следующие возможности попавшие в июльский коммитфест.
Планировщик: поддержка правого полусоединения хешированием
Планировщик: материализация внутреннего набора строк для соединения вложенными циклами в параллельном плане
Вспомогательные функции планировщика для generate_series
EXPLAIN (analyze): статистика рабочих процессов узла Parallel Bitmap Heap Scan
Функции min и max для составных типов
Имена параметров для функций regexp*
Режим отладки в pgbench
pg_get_backend_memory_contexts: столбец path вместо parent и новый столбец type
Функция pg_get_acl
pg_upgrade: оптимизация работы pg_dump
Предопределенная роль pg_signal_autovacuum_worker
PostgreSQL: PostgreSQL 17 Released!
Новшества давно известны (в том числе из обзоров Павла Лузанова PostgreSQL 17: Часть 5, 4, 3, 2, 1), но интересно, что выбрали в сообществе как самое-самое важное. Выбрали вот что:
Производительность:
переработанное управление памятью (overhauled memory management) при вакууме,
оптимизация доступа к хранению и улучшение работы при параллельных нагрузках (high concurrency workloads),
ускорение при массовой загрузке и экспорте,
улучшения работы с индексами.
Работа с новыми типами нагрузок:
реализация SQL/JSON JSON_TABLE .
Для высококритичных систем:
улучшения в логической репликации, которые упрощают работу с нагрузками при требованиях высокой доступности и при апгрейдах на другие мажорные версии.
А вот что привлекло внимание сразу нескольких комментаторов: изменения в оформлении самих пресс-релизов:
Так исторически сложилось, что задача организации простого и понятного резервного копирования в мире PostgreSQL до сих пор не решена. Есть набор комьюнити утилит, у каждой из которых есть некие плюсы, но всегда в нагрузку будет прорва минусов (тут нет инкрементных копий, там нет внятного расписания, это может только весь сервер вместо конкретной базы увозить и так далее). Да, есть тяжёловесный энтерпрайзный софт за много денег, зачастую требующий странного и работающий по какой-то своей логике, но это тоже не панацея. А вот чтобы просто и понятно, без головных болей организовать прозрачный процесс банального бекапа с инкрементами, работающим расписанием и восстановления только того что надо - вот такого нет.
Но буквально на днях вышел PostgreSQL 17 и может там что-то изменилось? И да, и нет. Та самая мана небесная в виде pg_awesome_backup_tool так и не появилась, однако в релиз попал механизм walsummarizer, который обещает нативно отслеживать изменения в файлах баз данных, что позволит делать инкрементальные бекапы нативно и без лишних приседаний.
А чтобы не рассматривать новичка в вакууме, будем сравнивать его с ptrack - нашей (Postgres Professional) разработкой, которую наши любимые конкуренты уже расхватали в свои продукты и продают их как уникальнейшие решения.
Чтобы объяснить, что есть Postgres Pre-Commitfest party и зачем мы в это ввязались, для начала нужно объяснить, как идёт разработка ванильного постгреса. Процесс принятия новых фичей и патчей в код разделён на так называемые коммифтесты (сокращённо CF), расписание которых всегда можно посмотреть на сайте https://commitfest.postgresql.org/. Когда разработчик предлагает свой код (неважно, будь это новая фича или багофикс), для того чтобы он был рассмотрен в рамках CF, он предварительно должен пройти процедуру ревью и быть одобрен для рассмотрения. Конечно, это совершенно не гарантирует, что код будет принят на этом CFили даже следующем, но сейчас не про это.
Найти ревьюера для своего кода зачастую задача более сложная, чем написать сам код. Человека надо погрузить в решаемую проблему, объяснить, что именно ты предлагаешь изменить, почему так, а не иначе и так далее. Прямо сейчас, в статусе Needs review, находится 29 патчей. Со свойственным нам желанием помогать, мы решили кинуть клич среди коммитеров и собрать всех желающих под одной крышей, чтобы они могли представить свои патчи, обсудить их и, возможно, найти желающего провести ревью.
Так получилось, что собрались мы в рамках проходившего Highload++ в Санкт-Петербурге. Поскольку Open Source — дело добровольное, мы просто разослали всем контактам, которые у нас были, предложение примерно следующего содержания: зовём всех неравнодушных постгресистов собраться в шатре, рассказать про свои патчи, обсудить их и хорошо провести время.
Нельзя просто взять и заменить нейросетями миллионы человеко-часов, вложенных в разработку классических оптимизаторов запросов реляционных СУБД. Надёжность, гибкость и скорость — ключевые характеристики экспертных систем, которые нарабатывались и отлаживались десятилетиями.
В прошлой статье рассказали о пионерах в области нейросетевых оптимизаторов, которые создали плацдарм для развития подобных ML-систем и их последующего вывода на уровень коммерческих продуктов. В этой же — затронем относительно стабильные подходы, не требующие гигантских вычислительных кластеров и удовлетворяющие большую часть потребностей бизнеса. Серебряной пули, конечно, не существует, но с каждым из этих методов можно прийти к оптимальному решению для конкретной задачи.
Конференции
Появилось расписание [проверить!!]
Первый доклад — Павла Лузанова, возглавляющего отдел образования в Postgres Professional — (новое в) PostgreSQL 17 (а пока можно почитать его PostgreSQL 17: Часть 5 или Коммитфест 2024–03).
Андрей Бородин (Yandex Cloud) представит Необычные возможности системы резервного копирования WAL‑G, а Дарья Лепихова и Алексей Дарвин (оба Postgres Professional) — Выбор репликационного протокола при разработке pg_probackup3 (напомним, что 3-я версия не очередная, это практически переписанный с нуля pg_probackup, в отличие от 2.х).
Cиквел и приквел: занимательная археология Егора Рогова — это исторический экскурс, новый (кажется) для Егора жанр, но не сомневаюсь, что это будет информативно и увлекательно: расскажу, как работали с базами данных до Кодда и что изменилось с изобретением реляционной теории; поговорим о зарождении первых реляционных систем — System R и Ingres; о том, как появился и завоевал популярность язык SQL; о людях, которые определили наше настоящее и в какой‑то степени будущее.
От редакции: Майкл Стоунбрейкер - один из самых известных в IT мире ученых и отец-основатель Postgres. В соавторстве с Энрю Павло, недавно опубликовал большой обзор всех актуальных технологий систем управления базами данных. В этом материале — подробно обо всем, что произошло в мире баз данных за последнее время, а также прогнозы. Мы посчитали что нельзя лишать нашу аудиторию возможности ознакомиться с этим обзором, поэтому подготовили данный перевод.
В 1970-х годах известный программист Эдгар Кодд разработал математически выверенную теорию организации данных в виде таблиц (реляций). С тех пор утекло немало воды — появилось большое количество различных коммерческих и open-source реляционных систем управления базами данных (РСУБД). Скоро стало понятно, что эффективное получение данных из базы — задача далеко не тривиальная. Если говорить прямо, она нелинейная и в общем случае NP-сложная.
Когда SQL-запрос становится немного сложнее: SELECT * FROM table, у нас появляется огромная вариативность его исполнения внутри системы — и не всегда понятно, какой из возможных вариантов эффективнее как по памяти, так и по скорости. Чтобы сократить огромное количество вариантов до приемлемого, обычно используются так называемые эвристики — эмпирические правила, которые придуманы человеком для сокращения пространства поиска на несколько порядков. Понятное дело, эти правила могут отсечь и сам оптимальный план выполнения запроса, но позволяют получить хоть что-то приемлемое за адекватное время.
В последние годы в связи с активным развитием ML начали развиваться и нейронные оптимизаторы запросов —особенность которых в том, что они самостоятельно, без участия человека, находят необходимые закономерности в выполнении сложных планов исходя из обучения на огромном количестве данных. Тенденция началась приблизительно в 2017 году и продолжается до сих пор. Давайте посмотрим, что уже появилось в этой области в хронологическом порядке и какие перспективы нас ждут.