Всем привет! Помогите, пожалуйста, разобраться с таким вот случаем... Сервер: Debian, PostgreSQL

12, 64 RAM

Есть запрос на удаление данных из таблицы (около 550млн строк) по частям вида:
with batch(id) as(
select id from table where t_date < '2022-01-01 00:00:00'
order by id limit 1000
for update skip locked
),
del_from_table as (
delete from table where id in (select id from batch) returning id
)
select count(*) from del_from_table;

Периодически наблюдаю, как он зависает с wait_event_type=IO и wait_event=DataFileRead или wait_event=ClientRead от пары минут до десятков часов.

Блокировок в lslocks для этого процесса нет, взаимных блокировок тоже нет.

Еще не сталкивалась с проблемами IO... Диски SSD с пропускной до 5GB, текущие скорости чтения 50MB/s, записи 8MB/s.

Что можно проверить/помониторить в такой ситуации?

Пересоздать можно по условию. Исходную удалить

Если Вы про таблицу, то так не выйдет. Задача стоит в периодическом удалении данных, старше некоторой даты. В основном за день набегает 20млн записей. Хранить надо как раз около 500 млн

Порезать и на партиции? И с партициями дальше упражняться - не?

сделайте партиции по полю с датой и грохайте партиции

Пробовали. Почему-то резко увеличилось время select-ов к БД. К тому же есть небольшая особенность в удалении данных из зависимых таблиц по ключу. FK отключили, тк с ними вообще все плохо было

Индексы, это не только ценный мех, но ещё и место под ними))) Если запрос не оптимален по своему содержанию, будет тупить

потому что в селектах нет условия по ключу секционирования, надо добавить

Я понимаю. А как быть, когда запрос идет на поиск данных по определенному полю за весь период? В любом случае, обращение будет ко всем партициям. А такие запросы - подавляющее большинство...

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

Тут уже сложнее будет изъясняться абстрактно, не затрагивая бизнес-логику)) А по поводу IO ожиданий все-таки. Предполагая сценарий, что мы не меняем схему БД, на что стоит обратить внимание?

в системе параллельно много процессов? может быть что-то другое интенсивно использует диск. в постгрис как в оракле так просто не узнаешь в чем дело

Порядка 60 писателей со скоростью записи 20-100 зап/сек. Если активные коннекты мониторить, то единовременно активных около 5. Есть еще пара процессов, которые читают данные для обработки и пишут результаты в БД. Но эти запросы довольно легкие и выполняются меньше чем за 1 сек.

во время подвисаний в других процессах проблем не наблюдается? подвисания возникают в случайное время? и еще по партициям. какого размера партиции делали?

только один этот процесс себя так ведет. Делали по дням (но раньше и скорости были другие, за день около 2-3млн)

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

> Есть запрос на удаление данных надо начинать с explain этого запроса, анализировать его и думать над тем в каких местах его можно ускорить - возможно индексом где-то можно подпереть. может параллельных воркеров добавить. > wait_event_type=IO и wait_event=DataFileRead возможно у вас недостаточный размер shared buffers, или вообще недостаточно RAM для того чтобы удерживать "горячие" данные в памяти (что в проче не отменяет и совсем холодное чтение). Посмотрите в интернетах запросы на предмет "cache hit ratio" и посмотрите насколько хорошо утилизируется shared buffers > wait_event=ClientRead это чаще всего вообще не проблема (даже если там и часы), обычно на таком ожидании висят idle сессии, и просто ждут когда клиент/приложение отправит запрос > текущие скорости чтения 50MB/s, записи 8MB/s нормальные SSD должны выдавать до 450-500MB/s, возможно стоит проверить производительность дисков с pg_test_fsync (в нормальном случае вы там должны увидеть латенси характерные для SSD - от сотен микросекунд до единиц миллисекунд)

Ну а план-то есть у этого запроса, статистика выполнения - в быстром варианте и в медленном? Первое куда надо смотреть, что там делается на каких шагах и в каком количестве строк на каждом шаге. wait_event_type=IO и wait_event=DataFileRead очевидно показывают что идет чтение таблицы, вопрос насколько оно птимально по плану. К примеру можно делать delete фуллсканом table в цикле по числу id в batch и тогда наверное это действительно займет десятки часов ровно с такими ожиданиями. Второй вопрос - сколько индексов на таблице. Если их там десятки то объем вашего ИО будет космическим за счет обслуживания индексов. И третий вопрос - вы делаете удаление в один поток? У постгреса все плохо с readahead, поэтому ожидать чудес пропускной способности от одного потока не приходится, надо распараллеливать задачу. Выше вам писали про партиционирование, это обычная практика для удаления архивных данных по времени. Из вашего ответа не ясно по какому ключу была партиционирована таблица. Может он вообще не подходит к вашим задачам. "Замедление селектов" из-за партиционирования может быть связано разве что с индексами. Постгрес не умеет в глобальные индексы на партиционированных таблицах , поэтому все индексы стали локальными, т.е. по сути составными - "ключ партиционирования"+"столбецX", что влияет на их эффективность. Решение одно - включение ключа партиционирования в фильтр селекта.

> Ну а план-то есть у этого запроса, статистика выполнения - в быстром варианте и в медленном? Знать бы еще как отловить это. Выполняя периодически запрос - план неизменен (Index Scan по дате, выборка записей для удаления по id также Index Scan + само удаление). В основном удаление 1к записей занимает ~1сек. Индексов и правда на таблице навешено много. Многие из которых тяжелые, и редко используются. Но их просто запретили удалять по причины надобности в редких случаях >И третий вопрос - вы делаете удаление в один поток? Да, в один. По сути pgagent периодически запускает bash, где в цикле с коммитом по limit удаляются данные. При параллельной работе надо учитывать, чтобы множества ключей не пересекались, пока руки не дошли до этого, если честно

> Знать бы еще как отловить это auto_explain

О, спасибо большое!)

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

Как будто будет хоть на копейку легче!

Средний размер записи 1,5MB, максимум - 3MB. Но мысль дельная была)

Удалять по периодам да, легче, хотя я тоже считаю это костылем

Обычно, 3 сек функция выполнялась.Там выборка из нескольких таблиц,самая большая ~3Гб.Есть подрзрение,что выполение было прервано,в режиме отладчика,если это имеет значение.

Каков подход без костылей, не поделитесь?

Я из всех вашых слов понял только "3Гб". Да и то как-то неправильно: таблица на 500 миллионов записей не можэт занимать 3Гб.

У них нет особых проблем с удалением, есличо. Операцыя делается неспеша и в фоне.

По всей видимости были спутаны ответы на два разных вопроса)

Ну, тогда отладчик/strace — и понимать хоть куда он долбится. А, ещё из такого — анализ долгих запросов можно включить. На случай, если планировщик хандрит — будет видно, что план отличается.

Strace по процессу выдает только recvfrom(29,"",4,0,null,null).Кажется, не сильно информативно..

Так потом глянуть, что за 29 дескриптор. Плюс — посмотреть steace -tt или как-то так — скллько времени этот recvfrom занимает... Впрочем, я дочитал тут в чате до правильного соображэния — ведь ClientRead это со стороны клиента... Получается, это у пгадмин сэссия подвисает как-то.