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
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 |