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

Крейг Рингер впервые сообщил о проблеме в список рассылки pgsql-hackers в конце марта. Короче говоря, PostgreSQL предполагает, что успешный вызов fsync() указывает на то, что все данные, записанные с момента последнего успешного вызова, безопасно перешли в постоянное хранилище. При сбое буферизованной записи ввода-вывода из-за аппаратной ошибки файловые системы реагируют по-разному, но такое поведение обычно включает удаление данных на соответствующих страницах и пометку их как чистых. Поэтому чтение блоков, которые были только что записаны, скорее всего, вернет что-то другое, но не записанные данные.

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

В примерах я буду использовать набор данных, содержащий 52 608 000 записей.

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

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

Если читатель без труда может расшифровать аббревиатуры OLTP и OLAP, возможно есть смысл перейти сразу к разделу Columnstore

Два подхода к работе с данными

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

Итак, на самом верхнем уровне существует всего два подхода к работе с данными: OLTP и OLAP.

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

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

Перевод статьи подготовлен специально для студентов курса «Базы Данных». Интересно развиваться в данном направлении? Приглашаем вас на День Открытых Дверей, где мы подробно рассказываем о программе, особенностях онлайн-формата, компетенциях и карьерных перспективах, которые ждут выпускников после обучения.

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

Введение

В своей работе я уже некоторое время использую Flask-Potion — фреймворк, основными достоинствами которого являются: весьма удобная интеграция с SQLAlchemy моделями, автогенерация crud-эндпоинтов, наличие клиента potion-client (весьма удобного, если пишешь API сервиса, использование которого понадобится в другом сервисе).

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

Вместо простой обзорной статьи на этот фреймворк я решил написать несколько статей о создании системы контроля для библиотеки "Furfur" на основе Flask-Potion.

Данная система должна уметь делать следующее:

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

В этой системе мы воспользуемся следующими инструментами:

• PostgreSQL
• Flask, Flask-SQLAlchemy, Flask-JWT, Flask-Potion, Flask-Migrate

Доброго дня.

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

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

Мы продолжаем знакомить вас с самыми интересными новостями по PostgreSQL.

Главные новости

Релиз-кандидат PostgreSQL 12

В релизе-кандидате вся функциональность идентична грядущему официальному релизу. Если вновь выявленные и недоисправленные баги будут закрыты в срок, то официальный релиз выйдет 3 октября. По сравнению с PG 12 beta 4 исправлено несколько багов, в основном связанных с ECPG — SQL, встраиваемом в C. Релиз-кандидат доступен.

• Подробно о релизе здесь;
• а здесь изменения в PG относительно PG 11;
• нерешенные проблемы (Open Issues) PG 12;
• сообщить о найденном баге.

Приглашаем вас на открытый митап, организованный совместно Райффайзенбанком и компанией Postgres Professional. Ждем вас 8 октября в нашем офисе в Нагатино.

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

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

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

Небольшой спойлер: на слабой машине при нагрузке в 200 тысяч параметров в минуту нам удалось снизить показатель CPU iowait с 20% до 2%, уменьшить время записи порциями в таблицы первичных данных в 250 раз и в таблицы агрегированных данных в 32 раза, уменьшить размер индексов в 5-10 раз и ускорить получение исторических выборок в некоторых случаях до 18 раз.

UPD: Заметка утратила свою актуальность, с выходом релиза 0.8.0. Со всеми нововведениями можно ознакомиться в статье: Новые возможности postgres_exporter для мониторинга PostgreSQL

Добрый день, читатели habr!

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

Продолжение статьи "Попытка создать аналог ASH для PostgreSQL ".

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

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

Продолжение статьи "Попытка создать аналог ASH для PostgreSQL ".

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

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

В качестве короткого дополнения к статье Попытка создать аналог ASH для PostgreSQL.

Задача

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

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

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

Для оптимизации запросов PostgreSQL, очень требуется возможность анализировать историю активности, в частности – ожидания, блокировки, статистика таблиц.

Имеющиеся возможности

Инструмент анализа исторической нагрузки или «AWR для Postgres»: очень интересное решение, но, нет истории pg_stat_activity и pg_locks.

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

Добавляется поле queryid — тот самый queryid из расширения pg_stat_statements (требуется предварительная установка)."

Это конечно сильно бы помогло, но самая неприятность именно первый пункт “Вся накопленная информация хранится только в оперативной памяти ”, т.е. имеем место импакт на целевую базу. К тому, же нет истории блокировок и статистики таблиц. Т.е. решение вообще говоря неполное: “Готового пакета для установки пока нет. Предлагается скачать исходники и собрать библиотеку самостоятельно. Предварительно требуется установить «devel»-пакет для своего сервера и в переменную PATH прописать путь до pg_config.”.

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

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

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

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

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

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

Взаимоблокировки

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

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

Time series данные или временные ряды — это данные, которые изменяются во времени. Котировки валют, телеметрия перемещения транспорта, статистика обращения к серверу или нагрузки на CPU — это time series данные. Чтобы их хранить требуются специфичные инструменты — темпоральные базы данных. Инструментов — десятки, например, InfluxDB или ClickHouse. Но даже у самых лучших решений для хранения временных рядов есть недостатки. Все time series хранилища низкоуровневые, подходят только для time series данных, а обкатка и внедрение в текущий стек — дорого и больно.



Но, если у вас стек PostgreSQL, то можете забыть о InfluxDB и всех остальных темпоральных БД. Ставите себе два расширения TimescaleDB и PipelineDB и храните, обрабатываете и проводите аналитику time series данных прямо в экосистеме PostgreSQL. Без внедрения сторонних решений, без недостатков темпоральных хранилищ и без проблем их обкатки. Что это за расширения, в чем их преимущества и возможности, расскажет Иван Муратов (binakot) — руководитель отдела разработки в «Первой Мониторинговой Компании».

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

Блокировки строк

Устройство

Напомню несколько важных выводов из прошлой статьи.

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

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

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

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

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

В этом цикле мы поговорим о блокировках (locks). Я буду придерживаться этого термина, но в литературе может встретиться и другой: замóк.

Цикл будет состоять из четырех частей:

1. Блокировки отношений (эта статья);
2. Блокировки строк;
3. Блокировки других объектов и предикатные блокировки;
4. Блокировки в оперативной памяти.

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

Читайте и другие серии.

Индексы:

1. Механизм индексирования;
2. Интерфейс метода доступа, классы и семейства операторов;
3. Hash;
4. B-tree;
5. GiST;
6. SP-GiST;
7. GIN;
8. RUM;
9. BRIN;
10. Bloom.

Изоляция и многоверсионность:

1. Изоляция, как ее понимают стандарт и PostgreSQL;
2. Слои, файлы, страницы — что творится на физическом уровне;
3. Версии строк, виртуальные и вложенные транзакции;
4. Снимки данных и видимость версий строк, горизонт событий;
5. Внутристраничная очистка и HOT-обновления;
6. Обычная очистка (vacuum);
7. Автоматическая очистка (autovacuum);
8. Переполнение счетчика транзакций и заморозка.

Журналирование:

1. Буферный кеш;
2. Журнал предзаписи — как устроен и как используется при восстановлении;
3. Контрольная точка и фоновая запись — зачем нужны и как настраиваются;
4. Настройка журнала — уровни и решаемые задачи, надежность и производительность.

Неожиданное поведение приложения в отношении работы с базой приводит к войне между DBA и разработчиками: DBA кричат: «Ваше приложение роняет базу», разработчики — «Но ведь до этого всё работало!». Хуже всего, что DBA и разработчики не могут помочь друг другу: одни не знают про нюансы работы приложения и драйвера, другие не знают про особенности, связанные с инфраструктурой. Было бы неплохо такой ситуации избежать.

Надо понимать, часто недостаточно полистать go-database-sql.org. Лучше вооружиться чужим опытом. Еще лучше, если это будет опыт, полученный кровью и потерянными деньгами.

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

Поиск медленных запросов

Первый очевидный способ начать тюнинг — это найти конкретные операторы, которые работают плохо.

pg_stats_statements

Модуль pg_stats_statements — отличное место для начала. Он просто отслеживает статистику выполнения операторов SQL и может быть простым способом поиска неэффективных запросов.

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

SELECT *
FROM
  pg_stat_statements
ORDER BY
  total_time DESC;