High number of live/dead tuples in postgresql/ Vacuum not working

Question

There is a table , which has 200 rows . But number of live tuples showing there is more than that (around 60K) .

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)

But as seen from pg_stat_activity and pg_locks , we are not able to track any open connection .

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)

I also tried full vacuum on this table , Below are results :

All the times no rows are removed
some times all the live tuples become dead tuples .

Here is output .

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

and after 10 sec

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

How Our App query to this table .

Our application generally select some rows and based on some business calculation, update the row .

select query -- select based on some id

select * from subscriber_offset_manager where shard_id=1 ;

update query -- update some other column for this selected shard id
around 20 threads do this in parallel and One thread works on only one row .
app is writen in java and we are using hibernate to do db operations .
Postgresql version is 9.3.24

One more interesting observation : - when i stop my java app and then do full vacuum , it works fine (number of rows and live tuples become equal). So there is something wrong if we select and update continuously from java app . –

Problem/Issue

These live tuples some times go to dead tuples and after some times again comes to live .

Due to above behaviour select from the table taking time and increasing load on server as lots of live/deadtuples are there ..

That sounds like something is seriously wrong. Which point release of Postgres 9.3? The latest 9.3.23? What do you get for `SHOW track_counts`? — Erwin Brandstetter, Aug 14 '18 at 15:33
Postgres version is 9.3.24 . One more observation - when i stop my java app and then do full vaccum , it works fine . So there is something wrong if we select and update continuously . — Sahil Aggarwal, Aug 14 '18 at 15:36
added in question : select query -- select based on some id select * from subscriber_offset_manager where shard_id=1 ; update query -- update some other column for this selected shard id — Sahil Aggarwal, Aug 14 '18 at 15:43

score 7 · Accepted Answer · answered Aug 14 '18 at 20:41

7

I know three things that keep VACUUM from doing its job:

Long running transactions.
Prepared transactions that did not get committed.
Stale replication slots.

See my blog post for details.

answered Aug 14 '18 at 20:41

Laurenz Albe

209,280
17
206
263

1

I tried all three things , but zero rows returned , no locks , no prepared transaction, no replication slot .... – Sahil Aggarwal Aug 15 '18 at 16:47
i got the issue and posted an answer , but i still have doubt, which i mentioned on answer , please check if you can answer . Thanx . – Sahil Aggarwal Aug 16 '18 at 14:15
I was not able to apply your 1st point , as i was using postgres 9.3 and backend_xmin is not there . So, i selected all and did not found any long running transaction. So.conclusion is when there is on running transaction , then dead tuples created after that will not be cleaned up by vacuum for all the tables, because transaction id is generated globally and it check for transaction id less that transaction id of oldest transaction .Thanx. – Sahil Aggarwal Aug 16 '18 at 18:15
1

Sorry that my query didn't work. But it is easy to find long transactions in `pg_stat_activity`. Glad you could fix the problem. – Laurenz Albe Aug 17 '18 at 05:16
written a blog on what a session leak can do, It will also answer above question https://hello-worlds.in/2021/03/28/a-database-session-leak-can-slow-down-your-database/ – Sahil Aggarwal Apr 19 '21 at 06:39

Sahil Aggarwal · Answer 2 · 2018-08-16T15:16:42.037

I got the issue ☺ .

For Understanding the issue consider the following flow :

Thread 1 -

Opens a hibernate session
Make some queries on Table-A
Select from subscriber_offset_manager
Update subscriber_offset_manager .
Closes the Session .

Many Threads of Type Thread-1 running in parallel .

Thread 2 -

These type of threads are running in parallel .
Opens a hibernate session
Make some select queries on Table-A
Does not close session .(session leak .)

Temporary Solution - If i close all those connection made by Thread-2 by using pg_cancel_backend then vacuuming starts working .

Also we have recreated the issue many times and tried this solution and it worked .

Now, there are following doubts which are still not answered .

Why postgres is not showing any data related to table "subscriber_offset_manager" .
This issue is not re-creating when instead of running Thread-2 , if we run select on Table-A , using psql .
why postgres is working like this with jdbc .

Some more mind blowing observation :

event if we run queries on "subscriber_offset_manager" in different session then also issue coming ;
we found many instance here where Thread 2 is working on some third table "Table-C" and issue is coming
all these type od transactions state in pg_stat_activity is "idle_in_transaction ."

@Erwin Brandstetter and @Laurenz Albe , if you know there is bug related to postgres/jdbc .

I gor the root cause . So.conclusion is when there is one running transaction , then dead tuples created after that will not be cleaned up by vacuum for all the tables, because transaction id is generated globally and it check for transaction id less that transaction id of oldest transaction . — Sahil Aggarwal, Aug 16 '18 at 18:24

Erwin Brandstetter · Answer 3 · 2018-09-19T10:35:41.877

5

There might be locks after all, your query might be misleading:

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

pg_locks.pid can be NULL, then the join would eliminate rows. The manual for Postgres 9.3:

Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction

Bold emphasis mine. (Still the same in pg 10.)

Do you get anything for the simple query?

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

This could explain why VACUUM complains:

DETAIL:  67720 dead row versions cannot be removed yet.

This, in turn, would point to problems in your application logic / queries, locking more rows than necessary.

My first idea would be long running transactions, where even a simple SELECT (acquiring a lowly ACCESS SHARE lock) can block VACUUM from doing its job. 20 threads in parallel might chain up and lock out VACUUM indefinitely. Keep your transactions (and their locks) as brief as possible. And make sure your queries are optimized and don't lock more rows than necessary.

One more thing to note: transaction isolation levels SERIALIZABLE or REPEATABLE READ make it much harder for VACUUM to clean up. Default READ COMMITTED mode is less restrictive, but VACUUM can still be blocked as discussed.

answered Aug 14 '18 at 15:47

Erwin Brandstetter

605,456
145
1,078
1,228

Ran Your suggested query : dead tuples are aroung - 80k , count is 200 , lock is giving nothing SELECT * FROM pg_locks WHERE relation = 'subscriber_offset_manager'::regclass; 0 Rows returned – Sahil Aggarwal Aug 14 '18 at 15:58
1

The crucial point is that there are no locks at the moment `VACUUM` tries to clean up. – Erwin Brandstetter Aug 14 '18 at 16:03
Yes , this is happening , but could not get why ? – Sahil Aggarwal Aug 14 '18 at 16:07
1

You will have to study transaction handling and queries of your Java app. Transactions open for too long? 20 threads in parallel might chain up and lock out `VACUUM` indefinitely. Keep transactions (and their locks) as brief as possible. – Erwin Brandstetter Aug 14 '18 at 16:12
1

I added one more hint concerning transaction isolation level. – Erwin Brandstetter Aug 14 '18 at 16:30
i got the issue and posted an answer , but i still have doubt, which i mentioned on answer , please check if you can answer . Thanx . – Sahil Aggarwal Aug 16 '18 at 14:14
1

I gor the root cause . So.conclusion is when there is one running transaction , then dead tuples created after that will not be cleaned up by vacuum for all the tables, because transaction id is generated globally and it check for transaction id less that transaction id of oldest transaction .Thanx for the help – Sahil Aggarwal Aug 16 '18 at 18:18

High number of live/dead tuples in postgresql/ Vacuum not working

3 Answers3

Linked

Related