ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Пухнет индекс
15 сообщений из 15, страница 1 из 1
Пухнет индекс
    #39628264
gav21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gav21
Участник
Привет.

Есть таблица с колонкой:
"timestamp" date NOT NULL DEFAULT now(),

Есть индекс на эту колонку:

CREATE INDEX idx_timestamp
ON table
USING btree
("timestamp");

Размер таблицы 1,7 ГБ, размер данного индекса 19 ГБ
Профиль нагрузки - Insert/Update (1 к 30 примерно)
С вакуумом проблем нет, таблица регулярно подчищается - autovacuum_vacuum_scale_factor = 0.05

pgstattupple вот что показывает:
Код: plsql
1.
2.
3.
4.
SELECT * FROM pgstatindex('idx_timestamp');
version | tree_level | index_size  | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+-------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
       2 |          3 | 20166123520 |         65763 |          24318 |    2434026 |           0 |          3340 |             1.69 |              96.26



Ручной вакум:

Код: sql
1.
2.
3.
4.
5.
vacuum verbose table
INFO:  index "idx_timestamp" now contains 13328864 row versions in 2463777 pages
DETAIL:  1579162 index row versions were removed.
4654 index pages have been deleted, 1835 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.



Я изучил основные запросы - данное поле не участвует в операциях update.
Я правильно понимаю механизм изменения данных в индексе:
update = delete+insert, соответственно по факту создается новая строка, то куда она помещается в индекс? Если ключ индекса не изменился и в странице есть место(или страница совсем пустая должна быть?), данные вставляются на ту же самую страницу.
Если ключ изменился (поле timestamp изменили), то новое значение должно быть помещено в соответствии с новым значением на другую страницу индекса.

В моем случае поле timestamp не апдейтится, и как я полагаю, индекс не должен вообще меняться (кроме как наращиваться за счет INSERT).
Подозреваю все же, при апдейте, даже не измененный ключ индекса не вставится в полу-пустую страницу.
Я прав?
...
Рейтинг: 0 / 0
11.04.2018, 09:48
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628270
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
gav21,

индекс при update не будет изменяться, в случае если сработает hot update, для которого необходимо чтобы ни одна из изменяемых колонок не входила ни в какой индекс. какая доля от всех update является hot можно посмотреть в pg_stat_user_tables.
а точно не бывает никаких долгих транзакций? pg_dump многочасовой например?
...
Рейтинг: 0 / 0
11.04.2018, 10:07
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628278
gav21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gav21
Участник
Alexius,
hot_update для данной таблицы равен нулю, т.к. есть еще индекс по полю которое изменяется.
Длинных транзакций нет, idle_in_transaction_session_timeout = 10 min
pg_dump один раз в неделю дампит около 3 часов, не более.
...
Рейтинг: 0 / 0
11.04.2018, 10:19
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628288
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
gav21,

при интенсивных update'ах может и 3х часов достаточно чтобы индекс так распух. попробуйте перестроить индекс и понаблюдать за его размером и долгими транзакциями. возможно как-то можно убрать из индекса колонку, которая часто обновляется чтобы hot updates заработали.
...
Рейтинг: 0 / 0
11.04.2018, 10:51
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628364
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Alexiusgav21,

индекс при update не будет изменяться, в случае если сработает hot update, для которого необходимо чтобы ни одна из изменяемых колонок не входила ни в какой индекс. какая доля от всех update является hot можно посмотреть в pg_stat_user_tables.
а точно не бывает никаких долгих транзакций? pg_dump многочасовой например?
наверное "дерево не будет меняться" (баланситься и т.п.). а вот цтидов на листе индекса должно появиться новых. нет ?

а то как-то неясно получается -- цтид сменился, а указывающий на него через IndexTupleData (т-тид) индекс -- нет.

чота я туплю.
/* 2 вар-та -- прокладка (вирт тиды в цтиды), и неточность форм-ки.
...
Рейтинг: 0 / 0
11.04.2018, 13:21
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628388
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
qwwq,

при hot update индекс может не меняться совсем (проверил тестом на всякий случай с
Код: sql
1.
2.
3.
4.
5.
create table test_hot_update(id int primary key, p text);
insert into test_hot_update select id, 'test ' || id from generate_series(1,10000) gs(id);
select md5(string_agg(get_raw_page('test_hot_update_pkey', id)::text, '')) from generate_series(0,29) gs(id);
update test_hot_update set p = 'test 0' where id = 1;
select md5(string_agg(get_raw_page('test_hot_update_pkey', id)::text, '')) from generate_series(0,29) gs(id);

с первым hot update хеш сменился - возможно какой-то флаг выставился, не уверен; в последующих - не менялся).

указатель в индексе идет на старую версию строки и в самой строке есть указатель на более свежую строку на той же странице, таким образом выстраивается hot цепочка. вот тут подробнее про hot , в документации сходу не нашел ничего почему-то.
...
Рейтинг: 0 / 0
11.04.2018, 14:00
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628404
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Alexiusqwwq,
...
указатель в индексе идет на старую версию строки и в самой строке есть указатель на более свежую строку на той же странице, таким образом выстраивается hot цепочка. вот тут подробнее про hot , в документации сходу не нашел ничего почему-то.
т.е. прокладка
через олд-запись.

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

ага, всё предусмотрено:
авторIf an update changes any indexed column, or there is not room on the
same page for the new tuple , then the HOT chain ends

т.е. при отсутствии возможности вакуумиться маленькие плотно апдейтуемые таблички (например текущей активности и логлоков) обречены пухнуть индексами. т.к. хот насыщается. кейс убера, или часть кейса.
...
Рейтинг: 0 / 0
11.04.2018, 14:20
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628446
gav21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gav21
Участник
Alexius,
индекс пухнет ежедневно, а дамп снимается только ночью
длинные транзакции исключены (в т.ч в idle in tran), это мониторится
Убрать из индекса колонку пока нет возможности

Нашел похожую ситуацию у Максима
https://www.postgresql.org/message-id/flat/CAK-MWwSmR7Tb2%2Bj__d5vb99%2Bk45rT%3DxmoRPFxmnW8drb4H87PQ%40mail.gmail.com#CAK-MWwSmR7Tb2+j__d5vb99+k45rT=xmoRPFxmnW8drb4H87PQ@mail.gmail.com]https://www.postgresql.org/message-id/flat/CAK-MWwSmR7Tb2 j__d5vb99 k45rT=xmoRPFxmnW8drb4H87PQ@mail.gmail.com#CAK-MWwSmR7Tb2 j__d5vb99 k45rT=xmoRPFxmnW8drb4H87PQ@mail.gmail.com

Так понимаю особенность профиля нагрузки, высокофрагментированные данные.
...
Рейтинг: 0 / 0
11.04.2018, 15:04
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628450
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
qwwqт.е. при отсутствии возможности вакуумиться маленькие плотно апдейтуемые таблички (например текущей активности и логлоков) обречены пухнуть индексами. т.к. хот насыщается. кейс убера, или часть кейса.

Лечится через комбинацию
1)установку fillfactor у таблицы в 50%-25% (если она разумного размера)
и
2)установку кастомных предельно агрессивных параметров autovacuum для конкретных проблемных таблиц (вплоть до autovacuum_vacuum_scale_factor=0 и регулировку срабатывания autovacuum через autovacuum_vacuum_threshold)

- пока хватало во всех случаях.

PS: а если workers у autovacuum не хватает - это уже должно мониторится и если больше 10 минут все autovacuums заняты были то это повод или delay уменьшить у них или больше workers сделать.

PPS: а автору топика - если такое возникает то перестроить индекс руками через конкурентное создание нового индекса и конкурентный дроп старого... если через неделю индекс опять распух - поставить pg_repack по этой таблице в крон с нужной частотой (да бывают ситуации когда индексы пухнут и ничего особо не сделать кроме как перестраивать время от времени).
...
Рейтинг: 0 / 0
11.04.2018, 15:07
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628461
gav21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gav21
Участник
Maxim Boguk,
Максим спасибо, за совет.
Мне непонятна механика распухания в данном случае.
Почему он пухнет если изменений по ключу данного индекса не происходит?
...
Рейтинг: 0 / 0
11.04.2018, 15:18
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628480
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
gav21Maxim Boguk,
Максим спасибо, за совет.
Мне непонятна механика распухания в данном случае.
Почему он пухнет если изменений по ключу данного индекса не происходит?
потому, что рано или поздно при частом апдейте версия меняет блок. а тогда ссылка по ХОТ становится неэффективной, и надо писать новый цтид в сам лист индекса. (а не в прокладку по хот)

отсутствие долго простаивающих транз и отсутствие долгих транз -- немного разные вещи.

кстати, хот мешает вовремя вакуумиться самой табле -- т.е. снижает собственную эффективность.
...
Рейтинг: 0 / 0
11.04.2018, 15:28
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628520
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
qwwqgav21Maxim Boguk,
Максим спасибо, за совет.
Мне непонятна механика распухания в данном случае.
Почему он пухнет если изменений по ключу данного индекса не происходит?
потому, что рано или поздно при частом апдейте версия меняет блок. а тогда ссылка по ХОТ становится неэффективной, и надо писать новый цтид в сам лист индекса. (а не в прокладку по хот)

отсутствие долго простаивающих транз и отсутствие долгих транз -- немного разные вещи.

кстати, хот мешает вовремя вакуумиться самой табле -- т.е. снижает собственную эффективность.

У автора топика нет вообще HOT updates. Т.е. все update - реально delete+insert.
Индекс распухший в 2 раза - это нормальное поведение (т.е. я в такой ситуации даже не перестраиваю обычно), больше 2х раз - уже выгоднее перестроить.
Даже если значение не изменилось - то надо новую запись в индекс добавить, и надо понимать что добавлять новые записи в те страницы индекса где были удаленные данные (полупустые) - нельзя (если там хоть 1 активная запись на 1 страницу индекса осталась - это место не будет reused).
Можете считать что у вас каждый update меняет поле индекса (физика процесса именно такая получается).
"Подозреваю все же, при апдейте, даже не измененный ключ индекса не вставится в полу-пустую страницу. " - именно, поэтому предельное распухание индекса - по 1 живой записи на 8kb страницу индекса (т.е. где то в 400 раз в пределе наверное ;)).
...
Рейтинг: 0 / 0
11.04.2018, 15:56
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628550
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
gav21,

если у вас hot update вообще нет т.к. постоянно изменяется один из проиндексированных столбцов (n_tup_upd на мастере 0), то при каждом update в индекс происходит вставка.
работы в этом направлении вроде велись, чтобы не обновлять все индексы, кроме тех, в которых меняются поля ( https://www.postgresql.org/message-id/flat/CABOikdMNy6yowA%2BwTGK9RVd8iw%2BCzqHeQSGpW7Yka_4RSZ_LOQ%40mail.gmail.com#CABOikdMNy6yowA+wTGK9RVd8iw+CzqHeQSGpW7Yka_4RSZ_LOQ@mail.gmail.com]WARM ), но чем это закончилось - я не в курсе.

qwwq кстати, хот мешает вовремя вакуумиться самой табле -- т.е. снижает собственную эффективность.
он откладывает обычный автовакуум, но в тоже время при обычной работе может происходить мини вакуум на странице, при котором схлопывание цепочек происходит.
...
Рейтинг: 0 / 0
11.04.2018, 16:19
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39628714
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Alexiusqwwq кстати, хот мешает вовремя вакуумиться самой табле -- т.е. снижает собственную эффективность.
он откладывает обычный автовакуум, но в тоже время при обычной работе может происходить мини вакуум на странице, при котором схлопывание цепочек происходит.

неправильно они, дядя федор, прокладку сделали.индекс должен показывать на страницу данных по псевдотиду. страница должна содержать сегментик--прокладку разадресации --( псевдотид -- лист тидов) той же странички. например. тогда факуумить записи можно в любое время. и разадресатор курочить, ессно. никто никого не держит. но, не для 30:1 это дорого, да. и уж больно кардинально. хммм.
...
Рейтинг: 0 / 0
11.04.2018, 20:25
| Ответить | Цитировать | Написать  
Пухнет индекс
    #39633101
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
https://habrahabr.ru/company/devconf/blog/353682/

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

полезное чтиво про механизмы опухания.
...
Рейтинг: 0 / 0
19.04.2018, 12:46
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Пухнет индекс
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=56&tid=1995823]:
 0ms
get settings:
 11ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 32ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 2ms
others: 290ms
total:  423ms
созд. / загр.: 423ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]