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

On 12/06/2017 12:13 AM, Dipesh Kamdar wrote:
> Here is  few day back log msg.  Removing actual table name and SQL but
> keeping actual msg.
>
> log file msg:
>
> 2017-11-30 13:24:34.513 GMT]: LOG:  *duration: 49082304.526 ms*  execute
> <unnamed>: ALTER TABLE  *tablename1  *SET (autovacuum_enabled= FALSE ,
> toast.autovacuum_enabled=FALSE )
> [2017-11-30 13:24:35.517 GMT]: LOG:  *duration: 30496621.974 *ms  parse
> <unnamed>: SELECT  column list FROM tablename2 INNER JOIN *tablename1*
> ON condation1.
> [2017-11-30 13:37:10.106 GMT]: LOG:  duration: *31239025.847* ms 
> execute <unnamed>: ANALYZE
>

Obviously, you have other long-running queries (e.g. the join query),
not just the ALTER TABLE.

>
> We have following setting enable in postgres config file.
> *log_min_duration_statement = 300000    # 5min *
> *deadlock_timeout = 1s   # default*
> *

Perhaps try also "log_lock_waits = on".

> *
> database monitoring script is not reporting any process is waiting for
> lock.  In this case deadlock is not occurring but it hangs almost 12
> hours. I am seeing this pattern once a day in log file with other table
> during same batch job run. Not any other SQL reported in log that say
> took more than 5 min.
>
> Why don't we see deadlock? Why don't we see any lock waiting in pg_locks
> table ?
>

Well, that's really hard to say without you showing us the pg_locks
contents (instead of just telling us there's nothing suspicious in it).
Also, pg_stat_activity collected at the same time would be useful.

Other than that, you can attach gdb to the waiting process, and see
where exactly it's waiting (collect backtrace using "bt").

regards

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