Как решить проблему с индексами в timescale?

Question

[tttttv]

Мы предоставляем услуги GPS-мониторинга. Держим сервер, который принимает большое количество координат от устройств.

На проекте используется postgres 14.5 и timescale 2.8.1

Есть таблица locations, в которой эти координаты хранятся, в ней создан индекс по imei (айди устройства) и времени пакета

CREATE TABLE locations (imei BIGINT, dt TIMESTAMP, lat REAL, long REAL, las BOOL, los BOOL, velocity INT, course INT, data VARBIT(128));
SELECT create_hypertable('locations','dt');
CREATE INDEX ix_imei_dt ON locations (imei, dt DESC);

Мы в одном запросе выгружаем последние координаты по списку устройств запросом

SELECT distinct on (imei) * FROM locations WHERE imei IN (…) order by imei, dt desc

Какое-то время все было нормально, но когда объем записей в базе вырос до 5М, в один день база легла. При заходе на сервер, там load average рисовало 40, хотя обычно и 1 нет

pg_stat показывал много запросов одинаковых в state=idle

SELECT distinct on (imei) * FROM locations WHERE imei IN (867232054978003, 867232054980835, 867232054976544, 867232054978474, 867232054980538, 867232054980769, 867232054978268, 867232054980157, 867232054978664, 867232054978102, 867232054980173, 867232054978235, 867232054981015, 867232054981411, 867232054977989, 867232054978367, 867232054977864, 867232054980876, 867232054981544, 867232054981296, 867232054981213) order by imei, dt desc;

Если пытались этот запрос напрямую в постгрес закинуть через клиент, висел и не отдавал ответ минутами (хотя обычно за 1 сек считает)

Проблема почему-то была с этим набором трекеров, если отсюда выбрать любые 19 - все было ок, добавляешь до 20 - виснет

Причем по другим трекерам хоть 60 за раз выгружай - все нормально

В итоге проблема решилась через удаление индекса и создания его еще раз

DROP INDEX ix_imei_dt
CREATE INDEX ix_imei_dt ON locations (imei, dt DESC);

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

Вот ответ EXPLAIN на запрос:

Unique  (cost=9.22..2863.56 rows=331 width=51)
        ->  Merge Append  (cost=9.22..2844.52 rows=7613 width=51)
         Sort Key: _hyper_1_4602_chunk.imei, _hyper_1_4602_chunk.dt DESC
         ->  Custom Scan (SkipScan) on _hyper_1_4602_chunk  (cost=0.42..0.42 rows=331 width=41)
               ->  Index Scan using _hyper_1_4602_chunk_ix_imei_dt on _hyper_1_4602_chunk  (cost=0.42..28.70 rows=1 width=41)
                     Index Cond: (imei = ANY ('{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}'::bigint[]))
         ->  Custom Scan (SkipScan) on _hyper_1_4603_chunk  (cost=0.42..0.42 rows=331 width=41)
               ->  Index Scan using _hyper_1_4603_chunk_ix_imei_dt on _hyper_1_4603_chunk  (cost=0.42..28.42 rows=1 width=41)
                     Index Cond: (imei = ANY ('{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}'::bigint[]))
         ->  Custom Scan (SkipScan) on _hyper_1_4606_chunk  (cost=0.29..0.29 rows=331 width=41)
               ->  Index Scan using _hyper_1_4606_chunk_ix_imei_dt on _hyper_1_4606_chunk  (cost=0.29..25.84 rows=1 width=41)
                     Index Cond: (imei = ANY ('{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}'::bigint[]))
         ->  Custom Scan (SkipScan) on _hyper_1_4607_chunk  (cost=0.43..0.43 rows=331 width=42)
               ->  Index Scan using _hyper_1_4607_chunk_ix_imei_dt on _hyper_1_4607_chunk  (cost=0.43..28.74 rows=1 width=42)
                     Index Cond: (imei = ANY ('{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}'::bigint[]))
         ->  Custom Scan (SkipScan) on _hyper_1_4608_chunk  (cost=0.43..433.66 rows=331 width=42)
               ->  Index Scan using _hyper_1_4608_chunk_ix_imei_dt on _hyper_1_4608_chunk  (cost=0.43..1681.96 rows=1911 width=42)
                     Index Cond: (imei = ANY ('{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}'::bigint[]))
         ->  Custom Scan (SkipScan) on _hyper_1_4609_chunk  (cost=0.43..0.43 rows=331 width=42)
               ->  Index Scan using _hyper_1_4609_chunk_ix_imei_dt on _hyper_1_4609_chunk  (cost=0.43..28.43 rows=1 width=42)
                     Index Cond: (imei = ANY ('{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}'::bigint[]))
         ->  Custom Scan (SkipScan) on _hyper_1_4610_chunk  (cost=0.29..0.29 rows=331 width=41)
               ->  Index Scan using _hyper_1_4610_chunk_ix_imei_dt on _hyper_1_4610_chunk  (cost=0.29..23.36 rows=1 width=41)

Кто-то сталкивался с похожим?

Comments

[Melkij]

Для начала, конечно, поставить минорные обновления и проверить, сохраняется ли проблема на актуальных версиях.

pg_stat показывал много запросов одинаковых в state=idle

state=idle по своему определению не выполняет никакого запроса. Показывает последний выполненный когда-то в прошлом.

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

[mayton2019]

Несколько вопросов. Или мыслей.

1) 5 Мегабайт это ниочем. Это какой-то учебный размер на котором можно учить школьников.
По идее все данные ложаться в страничный кеш и мы могли-бы исключить влияние дисков.

2) Со своего опыта работы с реляционками. Мне никогда не удавалось добиться гарантий
времени отклика для запросов которые содержали orderby, group, distinct. Эти ключевые
слова - враги стабилизаци времени отклика. Хотя можно стабилизировать план но трудно
стабилизировать сами данные. Вы никогда Не знаете как пойдет бизнес. Сколько завтра
зайдет строк за день. Сегодя 1000. Завтра 100 000 а через месяц зайдут миллионы. И время
отклика не линейно от объема. (Это бизнес думает что оно линейно) а мы то знаем
что там не то что полином а там бывают всякие эффекты. График - ломаная линия выходит.

Как работает этот disctinct on. Напомните пожалуйста. Выбирается самый последний imei по дате
с атрибутами?

SELECT distinct on (imei) * FROM locations WHERE imei IN (…) order by imei, dt desc

Может индекс здесь и помогает чорт его знает. Но я-бы "двигал" этот запрос в сторону key-value.
Взял IMEI. Получил все его последний снапшот. И все. И никаких исторических сканов. История
пускай будет но нам нужно нечто быстрое как редис.

3) Игры с ребилдом индекса я вообще никак не могу комментировать. Это не supportable система.
Нужно делать так чтобы индекс был стационарный и все нормально работало.

4) Вы используете IN (867232054978003, 867232054980835...). Это динамический запрос? Может
сделать с bind-variables и может лучше дернуть 20 раз по 1 EMAI с готовым запросом чем каждый
раз вызывать оптимизатор для 1 запроса с 20 EMAI. Это я не знаю ответа. Надо просто
поставить эксперимент.

[Димон Мартовский]

Апдейты есть или только инсерты в таблицы?
Как выглядит обычно план запроса и как, когда возникает проблема?