BUG #14938: ALTER TABLE hang/ poor performance

From: dipesh(dot)kamdar(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Cc: dipesh(dot)kamdar(at)gmail(dot)com
Subject: BUG #14938: ALTER TABLE hang/ poor performance
Date: 2017-11-30 23:30:56
Message-ID: 20171130233056.27101.86374@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

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.

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.
Found article on net regarding vacuum stuck

http://rhaas.blogspot.com/2011/03/troubleshooting-stuck-vacuums.html

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?

Thanks!
Dipesh

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bogdan Stepanenko 2017-12-01 09:36:48 defaut value '1' in smallint column is a string value '1' in pgsql >= 9.5
Previous Message 陈欣 2017-11-30 14:24:04 Fwd: Error about save extracted data