Re: BUG #14938: ALTER TABLE hang/ poor performance - Mailing list pgsql-bugs

On 12/01/2017 12:30 AM, dipesh.kamdar@gmail.com wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      14938
> Logged by:          Dipesh Kamdar
> Email address:      dipesh.kamdar@gmail.com
> PostgreSQL version: 9.5.3
> Operating system:   Linux
> Description:        
> 
> Postgres : 9.5.3
> Kernal : 3.10.0-327.13.1.el7.x86_64
> Linux :  x86_64 x86_64 x86_64 GNU/Linux
> RAM 128GB
> DISK : 1.5TB
> 

9.5.3 was released more than a year ago, you might be hitting one of the
bugs fixed since then.

> 
> We have nightly job that removed millions of records from multiple table. 
> 
> We had following approach.
> 1.    Delete data from table in batch of 50000
> 
> Problem with above approach many time autovacuum on table and delete
> statement on table create deadlock. 
> In order to avoid above problem, we have taken following approach.
> 

What do you mean by deadlock? Moreover, autovacuum certainly should not
deadlock with anything (certainly not with DELETE). In the worst case
autovacuum should cancel itself automatically.

I also don't quite understand why you do the delete in batches of 50k
rows, to be honest.

> 1    Turn off autovacuum on table by using ALTER TABLE <tablename>  SET (
> autovacuum_enabled=false);
> 2.    Delete data from table in batch of 50000 
> 3.    Turn On autovacuum on table by using ALTER TABLE <tablename>  SET (
> autovacuum_enabled=true);
> 
> 
> Problem with second approach ALTER TABLE tablename SET ( 
> autovacuum_enabled=FALSE) get hang very often. I am not seeing
> anything pg_lock that is waiting for resource and any other process
> blocking. Process manytime take 12hour , 13hours etc.
Are you sure there's nothing in pg_locks? What does

  SELECT pg_backend_pid();
  ALTER TABLE tablename SET (autovacuum_enabled=FALSE);

and then in ahother session (when the ALTER TABLE gets stuck)

  SELECT * FROM pg_locks WHERE pid = $PID; <- pg_backend_pid
  SELECT * FROM pg_locks WHERE NOT granted;

show?

If it really does not show any waiting locks, then you'll need to
inspect it using gdb. Install debuginfo packages, and then do

  gdb -p $PID
  (gdb) bt

FWIW I really doubt you really neet this process of disabling/enabling
autovacuum. This should work just fine with autovacuum running.

>
> Found article on net regarding vacuum stuck 
> 
> http://rhaas.blogspot.com/2011/03/troubleshooting-stuck-vacuums.html
>

That is entirely unrelated. The article is about the autovacuum process
getting stuck, while you're observing ALTER TABLE getting stuck.

> Database monitoring script is not reporting any waiting, pg_log is not
> reporting any error or deadlock.
> Is there anyway we can figure out any process is blocking or waiting for
> resource etc.
> My basic understanding about this ALTER TABLE SET command it updated record
> in pg_classs.reloptions column. 
> Does ALTER TABLE  SET option block complete table? 
> 

It does need a lock on the table, yes. So if there are any long-running
queries accessing that table, it may need to wait for them to complete.
But that should be visible in pg_locks, and you claim there's nothing
(no locks waiting).

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services