PostgreSQL: Приемы на продакшене

На PGDay 15 ребята демонстрировали OKmeter довольно интересная вещь для мониторинга PostgreSQL, за 25$ в месяц с сервера.

Для анализа можно еще взять pgBadger, но тут нужно смотреть на объем логов. На том же PGDay упоминали, что 200 Gb логов в несколько потоков переваривает за 6 часов.

Можно еще pgCluu, он работает через разные view и расширения PostgreSQL, а не через лог.

Если кому-то интересно то же самое для MySQL:

1) Добавление столбца в существующую таблицу:

существует утилита от Percona под названием pt-online-schema-change, которая позволяет в неблокирующем режиме альтерить таблицы — www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

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

2) мониторинг запросов к базе

«show full processlist» или «select * form information_schema.PROCESSLIST», если нужна какая-то особенная информация

3) slow_log точно также есть в MySQL из коробки — dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

4) Узнать какие запросы в данный момент делает процесс на продакшене

точно также — в strace будут видны запросы, которые исполняются на сервере

5) Обработка дубликатов

ALTER IGNORE для удаления дубликатов из таблицы при создании уникального индекса
INSERT IGNORE / INSERT ON DUPLICATE KEY UPDATE / REPLACE для вставки с игнорированием вставки в случае дубля / обновлением строки / заменой строки целиком соответственно

Задача: сделать составной уникальный индекс, одно из полей может быть null
Проблема: уникальный индекс не сравнивает null поля
Решение: разбить индекс на 2 разных индекса.

Так же можно сделать индекс по выражению:

evgenykokovikhin=# create unique index test_unique_idx on test(a, coalesce(b, ''));
CREATE INDEX
evgenykokovikhin=# insert into test (a,b) values ('foo', null);
INSERT 0 1
evgenykokovikhin=# insert into test (a,b) values ('foo', null);
ERROR:  duplicate key value violates unique constraint "test_unique_idx"
DETAIL:  Key (a, (COALESCE(b, ''::character varying)))=(foo, ) already exists.

Мониторинг текущих запросов к базе
Хочу отметить сущестование параметра track_activity_query_size, который увеличивает размер, отводимый под хранение выполняющегося запроса

NULL и уникальные индексы
Также, если количество столбцов превышает максимально допустимое для индекса (по умолчанию, 32), уникальный индекс можно построить по хешу результата их конкатенации:

CREATE UNIQUE INDEX idx_name_unique ON foobar USING btree ( md5 ( field_one::text || field_two::text ) );

Обработка дубликатов
Если необходимо избавится от уже существующих дубликатов, может помочь такой запрос (id — PK, field_one, field_two — столбцы, по которым необходимо построить уникальный индекс):

DELETE FROM foobar
WHERE id IN
(
	SELECT unnest ( ( array_agg ( id ) ) [2:9999999] )
	FROM foobar
	GROUP BY field_one, field_two
    HAVING count ( * ) > 1
);

Случайно нашел эту статью поисковиком.

Насчет решения с gdb:

Можно просто сделать pg_cancel_backend, а потом посмотреть в логах что за STATE мы убили (будет сразу после сообщения о том, что произошел cancel).