Postgresql как запустить что то вне транзакции

Эй, запрос! Ты живой? Как легко обработать блокировки в PostgreSQL

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

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

Чтобы разобраться в сложившейся ситуации, администратору БД необходимо понять, какой процесс блокирует и какой процесс является блокируемым, а также иметь возможность отменить или «убить» блокирующий процесс и в конце проверить результат.

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

Немного теории: ликбез о блокировках

Что же такое блокировки в БД? Википедия предлагает следующее определение:“Блокировка (англ. lock) в СУБД — отметка о захвате объекта транзакцией в ограниченный или исключительный доступ с целью предотвращения коллизий и поддержания целостности данных.”

PostgeSQL поддерживает целостность данных, реализуя модель MVCC. MVCC (MultiVersion Concurrency Control) — один из механизмов обеспечения параллельного доступа к БД, заключающийся в предоставлении каждому пользователю так называемого «снимка» БД. Особое «свойство» такого снимка в том, что вносимые пользователем изменения в БД невидимы для других пользователей до момента фиксации транзакции.

PostgreSQL гарантирует целостность даже для самого строгого уровня изоляции транзакций, используя инновационный уровень изоляции SSI (Serializable Snapshot Isolation, Сериализуемая изоляция снимков).

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

Непредвиденные ситуации

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

Например, если запустить долго обрабатываемый запрос к таблице c 1000 записей, к которой в секунду происходит 100 UPDATE запросов, то за 5-6 часов размер таблицы увеличится до 1.8 миллионов записей, соответственно, физический размер таблицы тоже увеличивается (так как БД хранит все версии строк, пока длинная транзакция не завершит свою работу.

Рассмотрим такую ситуацию подробнее.

Пример с возникающей блокировкой

Пусть в некоторой БД у нас есть таблица pgsqlblocks_testing и у неё есть правило rule_pgsqlblocks_testing. Эмулируем к нему “долгий” запрос на 10 минут, к примеру, с помощью SQL редактора pgAdmin:

Pid процесса 16728

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

Pid процесса 16726

И вот DROP RULE блокируется SELECT запросом. MVCC в данном случае не смог обойтись без явной блокировки таблицы pgsqlblocks_testing.

Инструменты для работы с блокировками

Как же нам просмотреть имеющиеся блокировки? Можно самому писать запрос для таблицы блокировок pg_locks и представления pg_stat_activity или использовать встроенный в pgAdmin инструмент.

Состояние сервера в pgAdmin

pgAdmin представляет собой достаточно удобное и простое ПО для работы с БД PostgreSQL. На данный момент актуальными версиями являются pgAdmin III и вышедший только в конце сентября pgAdmin IV.

pgAdmin III

Отображение информации о блокировках и активных процессах в pgAdmin III требует наличия расширения adminpack в базе данных. После установки этого расширения нужное нам окно открывается через меню Инструменты — Состояние сервера.

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

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

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

Итак, pgAdmin III может быть использован как инструмент для работы с блокировками, но обладает парой минусов: требует предварительной настройки БД и показывает блокировки в плоском виде (без древовидного отображения блокирующих-блокируемых процессов), что осложняет поиск проблемных процессов и оценку их терминирования. Это делает его не самым удобным инструментом для наших задач.

pgAdmin IV

После установки и запуска pgAdmin IV мы сможем посмотреть существующие блокировки в том же виде, как это было в pgAdmin III.

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

Запросы в БД

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

Первый же результат в поисковике по запросу “pg_locks monitoring” выдает ссылку с вариантом запроса:

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

Выглядит достаточно сложно, но результат приятен для глаз. Вообще, сообщество PostgreSQL создало и поддерживает достаточно много ресурсов, которые помогают и облегчают поиск информации рядовым администраторам БД. Например, та же вики wiki.postgresql.org

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

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

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

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

Чтобы проверить результат, снова запускаем Запрос 1 или SELECT * FROM pg_catalog.pg_stat_activity WHERE pid=16728; .

Всё просто и удобно с pgSqlBlocks!

Хочу показать вам ещё один инструмент и поделиться, чем он так удобен, — pgSqlBlocks. Инструмент pgSqlBlocks написан нами для себя, и создан именно для того, чтобы облегчить решение проблем с блокировками в PostgreSQL, которым мы пользуемся уже больше года.

Вот так выглядит окно pgSqlBlocks в случае нашего примера с двумя процессами (здесь они имеют pid 29981 (SELECT) и 28710 (DROP RULE)).

В левой части окна имеется список баз данных, в котором отображается информация о состоянии подключения к БД (соединен, отключен, обновление информации, ошибка соединения, имеются блокировки в БД).

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

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

Наглядно видим, что процесс с pid 29981 с долгим SELECT-запросом блокирует процесс с pid 28710.

При необходимости можно послать сигнал отмены или уничтожении любого процесса. Например, если уничтожить блокируемый процесс 28710, то информация в дереве процессов тут же обновится и мы увидим результат — процесс 29981 с долгим SELECT-запросом больше никого не блокирует. Быстро и удобно.

Еще из мелких и приятных фич приложения можно отметить:

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

Как установить pgSqlBlocks и чем он удобен по сравнению с описанными выше вариантами?

Установка и настройка

В системе должна быть предустановлена JRE 8.

Заходим по адресу pgcodekeeper.ru/pgsqlblocks и выбираем последнюю актуальную версию программы. В папке будут лежать 4 jar-файла. Выбираем тот, который подходит под ОС и разрядность Вашей системы. Скачиваем, запускаем и вуаля!

Это всё, что нужно для запуска приложения. Всё работает “из коробки”.

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

Протестировано на версиях 9.2-9.6 PostgreSQL.

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

Заключение

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

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

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

PostgreSQL-как запустить вакуум из кода вне блока транзакций?

Я использую Python с psycopg2, и я пытаюсь запустить полный VACUUM после ежедневной операции, которая вставляет несколько тысяч строк. Проблема в том, что когда я пытаюсь запустить VACUUM команда в моем коде я получаю следующую ошибку:

как запустить это из кода вне блока транзакций?

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

6 ответов

после дополнительного поиска я обнаружил свойство isolation_level объекта подключения psycopg2. Оказывается, изменив это на 0 выведет вас из блока транзакций. Изменение метода вакуума вышеуказанного класса на следующий решает его. Обратите внимание, что я также установил уровень изоляции на то, что он ранее был на всякий случай (кажется 1 по умолчанию).

в этой статье (в конце этой страницы) предоставляет краткое описание объяснение уровней изоляции в этом контексте.

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

к сожалению, прокси-сервер соединения, предоставляемый django, не предоставляет доступ к set_isolation_level.

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

эта команда печатает список с сообщением журнала запросов, таких как Vacuum и Analyse:

Это может быть полезно для DBAs ^^

Примечание Если вы используете Django с South для выполнения миграции, вы можете использовать следующий код для выполнения VACUUM ANALYZE .

Я не знаю psycopg2 и PostgreSQL, но только apsw и SQLite, поэтому я думаю, что не могу дать помощь «psycopg2».

но мне кажется, что PostgreSQL может работать так же, как SQLite, он имеет два режима работы:

вне блока транзакций: это семантически эквивалентно иметь блок транзакций вокруг каждой операции SQL
внутри блока транзакций, который отмечен «начать транзакцию» и заканчивается «конец Сделка»

когда это так, проблема может быть внутри уровня доступа psycopg2. Когда он обычно работает таким образом, что транзакции имплицитно вставляются до фиксации, не может быть «стандартного способа» создать вакуум.

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

когда таких возможностей не существует, там остается один единственный вариант (без изменения уровня доступа 😉 ):

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

Не делайте этого — вам не нужен полный вакуум. На самом деле, если вы запустите несколько последнюю версию Postgres (скажем, > 8.1), вам даже не нужно запускать обычный вакуум вручную.

PostgreSQL — как запустить VACUUM из кода вне транзакционного блока?

Я использую Python с psycopg2, и я пытаюсь запустить полный VACUUM после ежедневной операции, которая вставляет несколько тысяч строк. Проблема в том, что когда я пытаюсь запустить команду VACUUM в моем коде, я получаю следующую ошибку:

Как я могу запустить это из кода вне транзакционного блока?

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

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

После большего поиска я обнаружил свойство isol_level объекта соединения psycopg2. Оказывается, изменение этого параметра на 0 выведет вас из блока транзакций. Изменение вакуумного метода вышеуказанного класса к следующему разрешает его. Обратите внимание, что я также установил уровень изоляции на то, что ранее было на самом деле (по умолчанию это 1 ).

В этой статье (в конце на этой странице) приводится краткое объяснение уровней изоляции в этом контексте.

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

К сожалению, прокси-сервер, предоставленный django, не предоставляет доступ к set_isolation_level.

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

эта команда распечатает список с сообщением журнала таких запросов, как «Вакуум» и «Анализ»:

Это может быть полезно для администраторов баз данных ^^

Я не знаю psycopg2 и PostgreSQL, но только apsw и SQLite, поэтому я не могу дать помощь «psycopg2».

Но мне кажется, что PostgreSQL может работать аналогично SQLite, он имеет два режима работы:

Если это так, проблема может быть внутри уровня доступа psycopg2. Когда он обычно работает таким образом, что транзакции неявно вставляются до фиксации, не может быть «стандартного способа» для создания вакуума.

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

Если таких возможностей не существует, остается одна опция (без изменения уровня доступа;-)):

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