Re: BUG #14938: ALTER TABLE hang/ poor performance

On 12/01/2017 12:30 AM, dipesh(dot)kamdar(at)gmail(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14938
> Logged by: Dipesh Kamdar
> Email address: dipesh(dot)kamdar(at)gmail(dot)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