Большое количество живых/мертвых кортежей в postgresql/Вакуум не работает

Существует таблица, которая имеет 200 строк. Но количество живых кортежей, показывающих там больше, чем (около 60 тыс.).

select count(*) from subscriber_offset_manager;
 count 
-------
   200
(1 row)


 SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
;
 schemaname |          relname          | n_live_tup | n_dead_tup 
------------+---------------------------+------------+------------
 public     | subscriber_offset_manager |      61453 |          5
(1 row)

Но, как видно из pg_stat_activity и pg_locks, мы не можем отслеживать любое открытое соединение.

SELECT query, state,locktype,mode
FROM pg_locks
JOIN pg_stat_activity
  USING (pid)
WHERE relation::regclass = 'subscriber_offset_manager'::regclass
  ;
 query | state | locktype | mode 
-------+-------+----------+------
(0 rows)

Я также попробовал полный вакуум на этой таблице, Ниже приведены результаты:

Все строки, которые не удаляются
несколько раз все живые кортежи становятся мертвыми кортежами.

Вот вывод.

vacuum FULL VERBOSE ANALYZE subscriber_offset_manager;
INFO:  vacuuming "public.subscriber_offset_manager"
INFO:  "subscriber_offset_manager": found 0 removable, 67920 nonremovable row versions in 714 pages
DETAIL:  67720 dead row versions cannot be removed yet.
CPU 0.01s/0.06u sec elapsed 0.13 sec.
INFO:  analyzing "public.subscriber_offset_manager"
INFO:  "subscriber_offset_manager": scanned 710 of 710 pages, containing 200 live rows and 67720 dead rows; 200 rows in sample, 200 estimated total rows
VACUUM

 SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
;
 schemaname |          relname          | n_live_tup | n_dead_tup 
------------+---------------------------+------------+------------
 public     | subscriber_offset_manager |        200 |      67749

и через 10 сек

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
;
 schemaname |          relname          | n_live_tup | n_dead_tup 
------------+---------------------------+------------+------------
 public     | subscriber_offset_manager |      68325 |        132

Как наш запрос приложения к этой таблице.

Наше приложение обычно выбирает некоторые строки и основывается на некоторых бизнес-расчетах, обновляет строку.

select query - выберите на основе некоторого id

выберите * from subscriber_offset_manager, где shard_id = 1;

update query - обновить другой столбец для этого выбранного идентификатора осколков
около 20 потоков выполняют это параллельно, а один поток работает только с одной строкой.
приложение написано в java, и мы используем hibernate для выполнения операций db.
Версия Postgresql - 9.3.24

Еще одно интересное замечание: когда я останавливаю свое приложение java, а затем выполняю полный вакуум, он отлично работает (количество строк и живых кортежей становится равным). Таким образом, есть что-то неправильно, если мы постоянно выбираем и обновляем приложение java. -

Проблема/Выпуск

Эти живые кортежи несколько раз переходят к мертвым кортежам и через некоторое время снова оживают.

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

Ответ 1

Я знаю три вещи, которые заставляют VACUUM выполнять свою работу:

Долгосрочные транзакции.
Подготовленные транзакции, которые не были совершены.
Слоты статической репликации.

См. Мое сообщение в блоге для подробностей.

Ответ 2

У меня проблема.

Для понимания проблемы рассмотрите следующий поток:

Тема 1 -

Открывает сеанс спящего режима
Сделайте несколько запросов на Table-A
Выберите из подписчика_ффсет_manager
Обновите подписчик.
Закрывает сессию.

Многие потоки типа Thread-1 работают параллельно.

Тема 2 -

Эти типы потоков работают параллельно.
Открывает сеанс спящего режима
Сделайте несколько запросов на Table-A
Не закрывает сеанс. (Утечка сеанса.)

Временное решение. Если я закрываю все эти соединения, созданные Thread-2, используя pg_cancel_backend, тогда начнет работать пылесос.

Кроме того, мы неоднократно воссоздавали проблему и пробовали это решение, и это сработало.

Теперь есть следующие сомнения, на которые все еще не ответил.

Почему postgres не показывает никаких данных, связанных с таблицей " subscriber_offset_manager ".
Эта проблема не воссоздается, если вместо выполнения Thread-2, если мы запускаем select в таблице A, используя psql.
почему postgres работает так, как с jdbc.

Еще одно замечание:

событие, если мы запускаем запросы на " subscriber_offset_manager " в разных сеансах, а затем выходим;
мы нашли много примеров здесь, где Thread 2 работает над некоторой третьей таблицей " Table-C ", и проблема идет
все эти операции типа od od в pg_stat_activity являются " idle_in_transaction ".

@Erwin Brandstetter и @Laurenz Albe, если вы знаете, что есть ошибка, связанная с postgres/jdbc.

Ответ 3

В конце концов, могут быть блокировки, ваш запрос может ввести в заблуждение:

SELECT query, state,locktype,mode
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE relation = 'subscriber_offset_manager'::regclass

pg_locks.pid может быть NULL, тогда объединение будет удалять строки. Руководство для Postgres 9.3:

Идентификатор процесса сервера, выполняющий или ожидающий эту блокировку, или null, если блокировка удерживается подготовленной транзакцией

Смелый акцент мой. (Тем не менее в pg 10.)

Получаете ли вы что-нибудь для простого запроса?

SELECT * FROM pg_locks
WHERE relation = 'subscriber_offset_manager'::regclass;

Это может объяснить, почему VACUUM жалуется:

DETAIL:  67720 dead row versions cannot be removed yet.

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

Моей первой идеей были бы длительные транзакции, когда даже простой SELECT (приобретая низкий блокировку ACCESS SHARE) может заблокировать VACUUM от выполнения своей работы. 20 потоков параллельно могут цепляться и блокировать VACUUM бесконечно. Держите транзакции (и их блокировки) как можно более краткими. И убедитесь, что ваши запросы оптимизированы и не блокируют больше строк, чем необходимо.

Еще одно замечание: уровни изоляции транзакций SERIALIZABLE или REPEATABLE READ значительно SERIALIZABLE REPEATABLE READ VACUUM. Режим READ COMMITTED умолчанию менее ограничительный, но VACUUM все еще может быть заблокирован, как обсуждалось.

Связанные с: