Re: [pgsql-ru-general] Re: [pgsql-ru-general] Re[2]: [pgsql-ru-general] индекс & автовакуум? & как понять проблему?

имеется большая таблица

id, status, ...

где status - поле enum

по некоторым причинам хотим преобразовать поле status в text

что делаем

1. добавили столбил status_text (NULL)

2. построили индекс

CREATE INDEX "upgrade_temp" ON "table" ("id") WHERE "status_text" IS NULL;

3. запустили скрипт который делает следующее

WITH "list" AS (
SELECT
"id"
FROM
"table"
WHERE
"status_text" IS NULL
LIMIT
500
)
UPDATE
"table"
SET
"status_text" = "status"::TEXT
FROM
"list"
WHERE
"list"."id" = "table"."id"
RETURNING
"list"."id"

и вот этот скрипт уже почти неделю работает вроде уже немного ему
осталось (где-то 7 млн из 40 млн переписать)

но вот такая фигня:

на реплике вижу что запрос

SELECT
min(id)
FROM
"table"
WHERE
"status_text" IS NULL

выполняется часами.

при этом EXPLAIN на этот запрос реплика показывает следующий

Result (cost=3.03..3.04 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.56..3.03 rows=1 width=4)
-> Index Scan using upgrade_temp on table (cost=0.56..31983942.31 rows=12973820 width=4)
Index Cond: (id IS NOT NULL)
(5 строк)

то есть план запроса у него вроде простой - выбрать из индекса, но
запрос тупо вешается.

При этом тот же запрос на мастере (можно сделать EXPLAIN ANALYZE):

Result (cost=3.03..3.04 rows=1 width=0) (actual time=1504.294..1504.295 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.56..3.03 rows=1 width=4) (actual time=1504.283..1504.284 rows=1 loops=1)
-> Index Scan using upgrade_temp on table (cost=0.56..31985330.81 rows=12974386 width=4) (actual time=1504.280..1504.280 rows=1 loops=1)
Index Cond: (id IS NOT NULL)
Total runtime: 1504.382 ms
(6 строк)

то есть видим

- целых полторы секунды тупит на мастере
- дополнительная информация: по мере апгрейда размер индекса не
падает, а продолжает расти

Когда начали апгрейд - размер индекса был 800 Мб, сейчас уже 900Мб,
хотя записей в нем теперь в 4 раза меньше чем в начале.

из описанной информации я заключаю следующее

1. индекс на диске пришел в какое-то состояние что он слишком
неэффективен (фрагментация или что-то еще)
2. возможно я видимо сделал ошибку что записи апдейтятся пачками по
500 штук. лучше было по 1-10. Насколько я помню автовакуум у нас не
справлялся с работой когда мы пачками обновления делали

но останавливать скрипт я сейчас не хочу (рестартить скрипт апгрейда
не хочется)

соответственно вопросы:

1. правильно ли мое предположение что с индексом что-то не то в плане
хранения на диске? как это можно посмотреть/оценить?
2. есть связь с автовакуумом итп?
3. как устроен автовакуум на репликах отличается как-то? где можно
почитать про это?

--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

насчет фрагментации мысль подтвердилась:

перестроил CONCURENTLY индекс, а старый удалил - в итоге новый индекс
уже в 6 раз меньше занимает.

вопрос тогда еще: можно ли дефрагментировать один индекс как-то?
--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

REINDEX INDEX

>Понедельник, 16 ноября 2015, 20:47 +03:00 от "Dmitry E. Oboukhov" <unera(at)debian(dot)org>:
>
>насчет фрагментации мысль подтвердилась:
>
>перестроил CONCURENTLY индекс, а старый удалил - в итоге новый индекс
>уже в 6 раз меньше занимает.
>
>вопрос тогда еще: можно ли дефрагментировать один индекс как-то?
>--
>
>. ''`. Dmitry E. Oboukhov
>: :’ : email: unera(at)debian(dot)org jabber: //UNera(at)uvw(dot)ru
>`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
>  `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

> REINDEX INDEX

это я понимаю, я думал есть какая-то возможность сделать это не
блокируя базу.

>> Понедельник, 16 ноября 2015, 20:47 +03:00 от "Dmitry E. Oboukhov"
>> <unera(at)debian(dot)org>:

>> насчет фрагментации мысль подтвердилась:

>> перестроил CONCURENTLY индекс, а старый удалил - в итоге новый индекс
>> уже в 6 раз меньше занимает.

>> вопрос тогда еще: можно ли дефрагментировать один индекс как-то?
--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

> 17 нояб. 2015 г., в 12:08, Dmitry E. Oboukhov <unera(at)debian(dot)org> написал(а):
>
>
>> REINDEX INDEX
>
> это я понимаю, я думал есть какая-то возможность сделать это не
> блокируя базу.

Используй pg_repack, Люк.

>
>
>
>>> Понедельник, 16 ноября 2015, 20:47 +03:00 от "Dmitry E. Oboukhov"
>>> <unera(at)debian(dot)org>:
>
>>> насчет фрагментации мысль подтвердилась:
>
>>> перестроил CONCURENTLY индекс, а старый удалил - в итоге новый индекс
>>> уже в 6 раз меньше занимает.
>
>>> вопрос тогда еще: можно ли дефрагментировать один индекс как-то?
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

--
Да пребудет с вами сила…
https://simply.name/ru

pg_repack требует в два раза больше места чем есть, что не всегда можно
иметь.
Есть замечательный инструмент от компании PostgreSQL-Consulting
https://github.com/PostgreSQL-Consulting/pgcompacttable
по сути это скрипт на Perl
Это позволяет сжать таблицу + индексы в online.
Я кстати его запускаю на windows (ActivePerl), единственное столкнулся с
проблемой - функция getpwuid($<) (её просто нет в реализации под windows)
Поискав.. нашел решение - заменить на getlogin()
Заработало.

вт, 17 нояб. 2015 г. в 12:52, Vladimir Borodin <root(at)simply(dot)name>:

>
> 17 нояб. 2015 г., в 12:08, Dmitry E. Oboukhov <unera(at)debian(dot)org>
> написал(а):
>
>
>
> REINDEX INDEX
>
>
> это я понимаю, я думал есть какая-то возможность сделать это не
> блокируя базу.
>
>
> Используй pg_repack, Люк.
>
>
>
>
> Понедельник, 16 ноября 2015, 20:47 +03:00 от "Dmitry E. Oboukhov"
> <unera(at)debian(dot)org>:
>
>
> насчет фрагментации мысль подтвердилась:
>
>
> перестроил CONCURENTLY индекс, а старый удалил - в итоге новый индекс
> уже в 6 раз меньше занимает.
>
>
> вопрос тогда еще: можно ли дефрагментировать один индекс как-то?
>
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
>
>
> --
> Да пребудет с вами сила…
> https://simply.name/ru
>
> --
--
Yours faithfully, Sergey Grinko
Email: sergey(dot)grinko(at)gmail(dot)com