Блокировки в Postgres: 7 советов по работе с блокировками

И снова здравствуйте! Уже в следующий вторник стартует новый поток по курсу «Реляционные СУБД», поэтому мы продолжаем публиковать полезный материал по теме. Поехали.

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

1. Никогда не добавляйте столбец со значением по умолчанию

Золотое правило PostgreSQL: при добавлении столбца в таблицу в производственной среде никогда не указывайте значение по умолчанию.

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

Не делайте так:

-- блокировка чтения и записи таблицы, пока она не будет полностью переписана (часы?)
ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now();

Сделайте лучше так:

-- select, update, insert и delete заблокированы, пока каталог не будет обновлен (миллисекунды)
ALTER TABLE items ADD COLUMN last_update timestamptz;
-- select и insert проходят, некоторые update и delete заблокированы, пока таблица переписывается
UPDATE items SET last_update = now();

Или, что еще лучше, избегайте блокировок update и delete на долгое время, обновляя небольшими порциями, например:

do {
  numRowsUpdated = executeUpdate(
    "UPDATE items SET last_update = ? " +
    "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)",
    now);
} while (numRowsUpdate > 0);

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

2. Остерегайтесь очередей блокировок, используйте таймауты

Каждая блокировка в PostgreSQL имеет очередность. Если транзакция B пытается завладеть блокировкой, которая уже удерживается транзакцией A с конфликтующим уровнем блокировки, транзакция B будет ожидать в очереди блокировок. Теперь происходит кое-что интересное: если поступит другая транзакция C, ей придется проверять не только конфликт с A, но также с транзакцией B и любой другой транзакцией в очереди блокировки.

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

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

ALTER TABLE items ADD COLUMN last_update timestamptz;

Лучше сделайте так:

SET lock_timeout TO '2s'
ALTER TABLE items ADD COLUMN last_update timestamptz;

При установленном lock_timeout DDL-команда не будет выполнена, если она окажется в ожидании блокировки и, таким образом, заблокирует запросы более чем на 2 секунды. Недостатком является то, что ваш ALTER TABLE может быть не выполнен, но вы можете повторить попытку позже. Вы можете запросить pg_stat_activity, чтобы увидеть, есть ли у вас длительные запросы перед запуском DDL-команды.

3. Используйте неблокирующие создание индексов

Еще одно золотое правило PostgreSQL: всегда используйте неблокирующее создание индексов.
Создание индекса для большого набора данных может занять часы или даже дни, и обычная команда CREATE INDEX блокирует все записи на время выполнения команды. Несмотря на то, что она не блокирует SELECT-ы, это все же довольно плохо, и есть лучший способ:CREATE INDEX CONCURRENTLY.

Не делайте так:

-- блокирует все записи
CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops);

Вместо этого делайте так:

-- блокирует только другие DDL
CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops);

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

DROP INDEX CONCURRENTLY items_value_idx

и попробуйте создать его снова.

4. Используйте агрессивные блокировки как можно позже

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

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

BEGIN;
-- чтение и запись заблокированы отсюда:
TRUNCATE items;
- длительная операция:
COPY items FROM 'newdata.csv' WITH CSV 
COMMIT; 

Вместо этого загрузите данные в новую таблицу, а затем замените старую:

BEGIN;
CREATE TABLE items_new (LIKE items INCLUDING ALL);
-- длительная операция:
COPY items_new FROM 'newdata.csv' WITH CSV
-- чтение и запись заблокированы отсюда:
DROP TABLE items;
ALTER TABLE items_new RENAME TO items;
COMMIT; 

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

BEGIN;
LOCK items IN EXCLUSIVE MODE;
...

Иногда лучше взять блокирование в свои руки.

5. Добавление первичного ключа с минимальной блокировкой

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

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

ALTER TABLE items ADD PRIMARY KEY (id); -- блокирует запросы на длительное время

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

CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); -- занимает много времени, но не блокирует запросы
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk;  -- блокирует запросы, но ненадолго

Разбиение создания первичного ключа на два этапа практически не отражается на пользователе.

6. Никогда не используйте VACUUM FULL

Юзер экспириенс postgres иногда может быть самую малость удивительным. Хотя VACUUM FULL звучит как то, что вы бы хотели сделать, чтобы вычистить “пыль” вашей базы данных, более подходящей командой была бы:

PLEASE FREEZE MY DATABASE FOR HOURS;

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

7. Избегайте взаимных блокировок, упорядочивая команды

Если вы используете PostgreSQL уже какое-то время, скорее всего, вы видели такие ошибки, как:

ERROR:  deadlock detected
DETAIL:  Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483.
Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661.

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

BEGIN;
UPDATE items SET counter = counter + 1 WHERE key = 'hello'; -- захватывает блокировку на hello
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- блокировка в ожидании world
END;

Одновременно другая транзакция может выдавать те же команды, но в другом порядке.

BEGIN
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- захватывает блокировку на world
UPDATE items SET counter = counter + 1 WHERE key = 'hello';  -- блокировка в ожидании hello
END; 

Если эти блоки транзакций выполняются одновременно, есть вероятность, что они застрянут в ожидании друг друга и никогда не завершатся. Postgres распознает эту ситуацию примерно через секунду и отменит одну из транзакций, чтобы завершить другую. Когда это произойдет, вы должны взглянуть на свое приложение, чтобы узнать, сможете ли вы сделать так, чтобы ваши транзакции всегда выполнялись в одном и том же порядке. Если обе транзакции сначала изменяют hello, затем world, то первая транзакция заблокирует вторую на hello, прежде чем она сможет захватить любые другие блокировки.
Поделитесь своими советами!

Мы надеемся, что вы нашли эти рекомендации полезными. Если у вас есть другие советы, не стесняйтесь писать в Твиттере @citusdata или в нашем активном сообществе пользователей Citus в Slack.

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

Автор: Дмитрий

Источник