DBA2. «Администрирование PostgreSQL 9.5. Расширенный курс». Снимки и блокировки. Тема №04

DBA2. «Администрирование PostgreSQL 9.5. Расширенный курс». Снимки и блокировки. Тема №04
Занятие проведено 16 февраля 2016 в офисе компании Yandex
Лекторы: Егор Рогов, Павел Лузанов
Вопросы и пожелания: www.POSTGRESPRO.ru
Данная видеозапись произведена компанией «Постгрес Профессиональный»
и является интеллектуальной спобственностью компании.
Видеозапись доступна для свободного просмотра.
При копировании и публикации необходимо указать владельца (компания
«Постгрес Профессиональный») и активную ссылку на сайт компании.
Внесение изменений в видеозапись, коммерческое использование запрещено.
О проведении учебных курсов и всем прочим вопросам обращаться: www.POSTGRESPRO.ru

[@МихаилКулагин-х3ю]

Вопрос про RowExclusiveLock (в видео 52:18): При выполнении запроса, который будет использовать этот индекс, всё равно нужно будет видимость проверять и индекс тут никак не поможет. Тогда зачем при создании индекса определяться с видимостью строк?

[@egor-rogov]

А как нам строить индекс, если там ключ индексирования меняется? (Не хочется ждать - есть create index concurrently.)

[@vladnevsky9195]

в лекции указывается, что в postgresql нет аналога оракл-флэшбек , но это не совсем так... потенциально наличие версионности позволяет реализовать флешбек (вероятно необходима доработка алгоритма вакуумирования)... например для версии postgres 9.5 некий аналог flashback-query можно проиллюстрировать следующим примером: --включаем параметр сохранения времени транзакций alter system set track_commit_timestamp to 'on'; --устанавливаем расширение анализа страниц CREATE EXTENSION pageinspect; --создаем таблицу и делаем несколько апдейтов db11=# create table t (n numeric); CREATE TABLE Time: 144,480 ms db11=# insert into t values(13); INSERT 0 1 Time: 52,304 ms db11=# update t set n=25; UPDATE 1 Time: 17,214 ms db11=# update t set n=44; UPDATE 1 Time: 18,587 ms db11=# update t set n=61; UPDATE 1 Time: 16,987 ms --читаем исторические данные SELECT t_xmin, t_xmax ,pg_xact_commit_timestamp(t_xmin) dt_xmin ,('x' || lpad((SELECT substring(get_raw_page::text from lp_off*2+57 for 2) FROM get_raw_page('t', 0)),8,'0'))::bit(32)::int val from heap_page_items(get_raw_page('t','main', 0)); t_xmin | t_xmax | dt_xmin | val --------+--------+-------------------------------+----- 112253 | 112254 | 2016-06-02 10:35:56.96801+03 | 13 112254 | 112255 | 2016-06-02 10:36:28.669865+03 | 25 112255 | 112256 | 2016-06-02 10:36:37.032635+03 | 44 112256 | 0 | 2016-06-02 10:36:44.270829+03 | 61 (4 rows) Time: 5,293 ms

[@egor-rogov]

Как бы да, но тут две бооольшие проблемы. Первая - хотя мы и можем заглянуть внутрь страницы и найти там исторические данные, но при этом мы видим все подряд. А согласованную на определенный момент времени картинку восстановить не можем. У Оракла снимок определяется только и исключительно SCN-ом, но в случае Постгреса надо знать не только номер транзакции, но и список активных транзакций на тот момент. А этот список, увы, взять неоткуда. Подробнее вот тут можно почитать: postgrespro.ru/blog/pgsql/17758 Вторая - это, конечно, vacuum. Исторические данные видны до тех пор, пока они не будут вычищены из страницы. А если их не вычищать - будет бяда, таблицы и индексы будут пухнуть, запросы будут тормозить. И ладно бы только те, которым нужны исторические данные, но нет - страдать будут все. В общем, флэшбека в Постгресе нет, и сделать его сложно - надо какие-то архитектурные решения пересматривать.

[@vladnevsky9195]

Объективности ради, под "флешбеком" в Оракле понимается целая группа фич, которые реализованы через различные механизмы. С практической точки зрения фича flashback query достаточно актуальна, т.к. зачастую требуется оперативно (бекап - долго) посмотреть на исторические значения данных одной таблицы (например при случайном изменении/удалении), согласованные в рамках одной строки. Пока достаточно слабо знаком с Postgresql, но по-моему, имея время, доп. признаки в строках и список текущих транзакций вполне возможно с незначительными архитектурными изменениями (реализовать в вакуум как опцию аналог undo_retention) добиться реализации хотя бы этой "фичи". Считаю, что возможность иметь хотя бы несколько гарантированных часов версий данных для многих было бы более чем достаточно. PS: ..а на счет консистентности - тут и у Оракла далеко не все однозначно... например есть скрытые "миниоткаты" www.interface.ru/home.asp?artId=18342

[@egor-rogov]

Спору нет, такая возможность была бы полезна. Но увы. Пока можно разве что развернуть реплику с задержкой воспроизведения wal-ов, но... "песок - неважная замена овсу" (:

[@МихаилКулагин-х3ю]

Егор, первая проблема: а зачем нам знать множество открытых на тот момент транзакций, а не множество зафиксированных? По-моему, достаточно хранить только отметку времени фиксации каждой из транзакций и этой информации будет достаточно, чтобы определить видимость строк на определённый момент времени (по времени узнаём множество номеров транзакций уже зафиксированных на интересующий нас момент времени, и выбираем только строки, у которых xmax не входит в это множество, а xmin -- наоборот входит). Если сохранять ещё и время начала транзакции, то можно решить и задачу восстановления снимка для заданной транзакции. Я где-то ошибся? вторая бяда: для vacuum'а вводим аналог параметра undo_retention и "платим" замедлением ровно столько, сколько может администратор позволить. Autovacuum же уже умеет учитывать открытые транзакции на реплике...

[@МихаилКулагин-х3ю]

Перечитал внимательно ещё раз, я практически повторил Влада. Вопрос тогда только про необходимость восстановления множества открытых транзакций (непонятно зачем они нужны). Ну ещё и третья проблема. Предположим, что такой механизм существует. Непонятно как должна себя вести СУБД в следующей ситуации: Допустим база настроена выполнять flashback запросы с историей 15 минут, можно ли допускать разные результаты повторных запросов (в одной и той же транзакции) выполненных в разное время (с интервалом в 20 минут например)?

[@ИНФОРМАЦИЯДЛЯУСПЕШНЫХ]

спс