Почему SQL запрос выполняется с разной скоростью?

Question

[beduin01]

Есть запрос:

SELECT COUNT(*) FROM xml_files t1

    WHERE NOT EXISTS (SELECT 1
              FROM xml_files t2
              WHERE t1."id" = t2."id" AND
                t1."mydate" < t2."mydate"
            )
    AND t1.insert_status IS NULL
   AND t1.section_name='users' AND t1.region IN ('Moscow') AND t1.xml_date >= to_date('2016', 'YYYY') AND t1.parsing_status IS NULL

Когда выполняю этот запрос в SQL то 10 раз он может выполняться по 90 секунд, а на 11 может выполниться за 5 и потом продолжить выполняться за 5.

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

Там какие-то оптимизаторы есть в БД или другая хитрая логика? Просто по прошествии часа история повторяется и запрос опять начинает медленно выполняться.

Оперативки 16GB.
Другой нагрузки нет т.к. поиски причины почему микросеврис получающий COUNT зависает привели именно к тому что периодически COUNT начинает слишком долго обрабатываться.

Размер БД 100GB
shared_buffers = 2048MB
maintenance_work_mem = 256MB

PostgreSQL 13

Comments

[Илья]

А на базе есть еще какая-то нагрузка кроме вашего запроса?

[Илья]

И сколько там оперативки?

[beduin01]

Илья, дополнил вопрос

[Slava Rozhnev]

Попробуйте посмотреть план запроса (EXPLAIN [ ANAL...

[Developer]

Сколько свободного места на диске?
В любом случае, надо начинать с плана запроса

[beduin01]

Developer, места с запасом. План запроса. Вот как раз нашел очередной момент когда тормозит:
https://imgur.com/NwV3EAy
После этого несколько раз попробовал выполнить запрос с EXPLAIN и без -- выполняется по минуте вместо 5 секунд

https://i.stack.imgur.com/A8G7F.png

Answer 1

[galaxy]

Когда выполняю этот запрос в SQL то 10 раз он может выполняться по 90 секунд, а на 11 может выполниться за 5 и потом продолжить выполняться за 5.
...
Там какие-то оптимизаторы есть в БД или другая хитрая логика? Просто по прошествии часа история повторяется и запрос опять начинает медленно выполняться.

ну эта картина неудивительная. Почти любой кеш работает по принципу вытеснения LRU (least recently used) данных. Вы повторяете запрос - ваши данные вытесняют то, что было в кеше. Вы прекращаете - другие запросы вытесняют ваши данные.

Далее, судя по плану, вам стоит сделать на таблицы ANALYZE (и, может быть, увеличить статистику - ALTER TABLE SET STATISTICS), т.к. реальное число строк и оценка расходятся весьма сильно.

И, наконец, self-join'а тут можно не делать. Нужен по сути последний по дате документ, так что, с поправкой на составление запросов в уме:

SELECT * FROM (
     SELECT row_number() OVER (PARTITION BY id ORDER BY mydate DESC) as rn,
          t1.* FROM xml_files t1
         AND t1.insert_status IS NULL
        AND t1.section_name='users' AND t1.region IN ('Moscow') AND t1.xml_date >= to_date('2016', 'YYYY') AND t1.parsing_status IS NULL
     ) t
 WHERE rn = 1

Comments

[beduin01]

Спасибо большое, но вот с кешем не совсем понятно. Бывают моменты когда я запрос могу неопределенное количество раз выполнять и он выполняется медленно. Один и тот же. ничего не кешируется.

Закономерности не увидел. Один раз попробовал свой сервис грохнуть висящий на запросе. Или так совпало или почему-то запрос снова стал за 5 секунд выполняться. Но сам по себе сервис ничего не делает кроме как этот же запрос выполняет.

Сам сервис кроме получения COUNT еще INSERT делает, но он на Count зависает и не понятно может ли каким-то образом INSERT где-то подвиснуть и на это повлиять.

Причем работать может 2-3 часа нормально.

[galaxy]

beduin01, кеш - штука непростая, тем более, что постгрес в основном полагается на кеш ОС при работе с диском, а он общий для всех процессов.
Не вижу смысла гадать на кофейной гуще, тем более, что повлиять как-то на поведение кеша способов особо нет.
БД в любом случае не помещается в оперативную память. Ваш запрос в любом случае из тяжелых (200к+ запросов к индексу ~ 1м рандом чтений с диска). Ищите способы его оптимизировать: ставьте более ограниченные условия, попробуйте добавить индекс на наиболее селективные поля из фильтра. Вообще, начните с того, что попробуйте мой вариант без селф-джойна.

[beduin01]

galaxy, так самое интересное в том, что я не могу понять как зависающий сервис может тормозить запрос. Картинка следующая. В сервис прилетают INSERT и UPDATE запросы (не тяжелые) в виде текста (не prepared) и я их выполняю. Некоторые если INSERT падает с ошибкой, то я вызываю DELETE для указанных данных и повторяю вставку.

Каждые 30 минут выполняется COUNT запрос. И он отрабатывает нормально до какого-то момента пока не начинает так сильно тормозить.

Грохаю висящий микросервис -- время исполнение снова падает.

У меня просто реально даже мыслей нет как такое может происходить.

Кстати, ваш запрос выполняется чуть дольше моего. На невисячей базе 5.2 секунды против (в среднем) моего 4.9 секунды.

Может у вас есть идеи по первой части вопроса. Я реально даже не знаю в какую сторону думать. Транзакции что ли как-то заглючивают?

[galaxy]

beduin01, я тоже не знаю.
Проверяйте, что именно происходит в момент торможения (pg_stat_activity), блокировки (pg_locks). Просто состояние системы в этот момент (ps, top, etc...)

[beduin01]

galaxy, дождался нового зависания

https://freeimage.host/i/oY2wGt

Тут есть что-то подозрительное?
Вижу только один COUNT запрос (не тот который выполнял, а более простой который когда-то проверял) и UPDATE:

UPDATE "xml_files" SET "processed_date" = '2021-07-14 20:54', "parsing_status" = 'success', "insert_status" = 'success' WHERE "id" = '24604928'

[beduin01]

select pg_blocking_pids(pid) as blocked_by
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Не дает никакого результата

[galaxy]

beduin01,

Тут есть что-то подозрительное?

да вроде нет. Все процессы в idle, никаких запросов не выполняют и ждут команды от клиента.
В этот момент у вас что, запрос COUNT якобы висит?

[beduin01]

galaxy, да, именно в этот момент у меня он выполняется полторы минуты против 5 секунд. Если сейчас грохну сервис который по факту только соединение видимо держит (данные не вставляет никакие) то скорость запроса снова станет нужной - 5 секунд. Если после перезапуска COUNT сделаю то тоже все будет быстро, пока не пройдет несколько часов и ситуация не повторится.

[beduin01]

galaxy, дал ему постоять ночь. До сих пор тупит. Я просто ума не приложу как http сервер своим коннектом к БД все может портить. Сейчас грохну его и все норм станет.

[galaxy]

beduin01, ну COUNT у вас на скриншоте был уже завершен (и старый, и новый). Так что вы либо опоздали со скриншотом, либо тупит оно где-то еще.
COUNT через что вызывается (клиент)? Не может быть проблем с соединением?

[galaxy]

И включите лог долгих запросов (log_min_duration_statement в postgresql.conf), действительно ли полторы минуты выполняются эти count'ы

[beduin01]

galaxy, оказалось что это баг драйвера postgrsql для dart. Очень удивлен. Не думал что в этом причина. В любом случае большое спасибо за все советы.