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

#1 9.5.3 was released more than a year ago, you might be hitting one of the bugs fixed since then. >>> Do you know any bug related ? #2 What do you mean by deadlock?Moreover, autovacuum certainly should not deadlock with anything (certainly not with DELETE) >>> Please take a look of SEQ deleting TAB1 data of 50K ------ T1 autovacuum check TAB1 ------- T2 ( Since delete is running skipping table ) delete completed ---- T3 before starting next delete on same table autovacuum started on TAB1 ---- T4 DELETE statement is waiting for autovacuum to release lock. after some time later reported deadlock in log and killed DELETE process. #3 I also don't quite understand why you do the delete in batches of 50k rows, to be honest >> It is client facing application with close to 1.5 billion records and has multiple FK other and indexes removing again millions of record in single statement means putting entire online application on fire. #4 I am running following SQL as monitoring point of every 10min. Let me know if anything is missing above monitor script. SELECT COALESCE(blockingl.relation*::*regclass*::*text,blockingl.locktype) as locked_item, now() *-* blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid, blockeda.query as blocked_query, blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid, blockinga.query as blocking_query, blockingl.mode as blocking_mode FROM pg_catalog.pg_locks blockedl INNER JOIN pg_stat_activity blockeda ON blockedl.pid *=* blockeda.pid INNER JOIN pg_catalog.pg_locks blockingl ON( ( (blockingl.transactionid*=*blockedl.transactionid) OR (blockingl.relation*=*blockedl.relation AND blockingl.locktype*=*blockedl.locktype) ) AND blockedl.pid *!=* blockingl.pid) INNER JOIN pg_stat_activity blockinga ON blockingl.pid *=* blockinga.pid AND blockinga.datid *=* blockeda.datid WHERE NOT blockedl.granted AND blockinga.datname *=* current_database() #5 It is production , We will not able to attach process to gdb. #6 My main concern is not coming in pg_locks table. Is it advice to use update statement on pg_class.reloptions column rather than using ALTER TABLE SET option. -Dipesh On Fri, Dec 1, 2017 at 6:49 AM, Tomas Vondra wrote: > > 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 SET > ( > > autovacuum_enabled=false); > > 2. Delete data from table in batch of 50000 > > 3. Turn On autovacuum on table by using ALTER TABLE 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 >