Re: psql is hanging

On 11/30/18 7:53 AM, John Smith wrote:
> We have a long script of sql that we run, several thousand lines of sql. 
> If I execute the script from start to finish, somewhere in the middle of 
> it, one sql command will hang and take 2 to 3 hours. During this time, 
> "htop" shows 100% cpu usage with a postgresql process, so it appears to 
> be doing something.

First, a naming note. psql is the name of the Postgres command line 
client. What you are actually referring to is the Postgres server.

Second, without the actual commands solving this is going to be 
difficult to impossible.

That being said I would suspect some sort of locking issue. I would 
start with pg_stat_activity:

https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

See what it says when you are in the 'hung' phase.

> 
> If I stop that command, then immediately rerun the rest of the script 
> starting from the command that "hung", the "hung" command then completes 
> in 5 seconds and the rest of the script just continues on perfectly. I 
> can interrupt the command within 30 mins of hanging, or 2 hours of 
> hanging; regardless of when I interrrupt the hung command, it always 
> immediately completes successully in < 5 seconds when I rerun the script 
> immediately after that.
> 
> I have noticed this in multiple scripts, multiple commands; one was an 
> "insert into", a different script hung on a "update" and a third one 
> would hang on a "create table x as (select ... )". These are all short 
> running commands normally, and we have indexes on all the columns for 
> the appropriate "where" clauses.
> 
> I can't send the sql (forbidden), but the actual command does not appear 
> to be the issue. Rerunning the same little section of sql manually which 
> includes the hung command, always takes less than a minute, but the 
> entire sql which should only take 30 minutes in total will always hang 
> at the same spot; and then interrupting it (regardless of how long it 
> was running) and rerunning immediately completes.
> 
> The "hung" command does eventually finish at times after 2 to 3 hours, 
> most often we can't afford to wait for it to run since this batch of sql 
> has to run daily.
> 
> Some other notes:
> - Environment is Ubuntu 16.04; postgresql version was originaly 9.5(?) 
> but we have upgraded to 11.1 using postgresql packages for ubuntu and 
> the problem has persisted across postgresql versions.
> - There are no other queries running on the postgresql server during 
> this time, and server load is idling (other than whatever psql is doing).
> - It is an amazon server, with ZFS as the files system, it has 50% disk 
> free on this storage partition that postgresql database is on.
> - We have 60 gigs of ram total, 8 cores.
> - We have run the entire script using "create unlogged table" everywhere 
> as well, no difference.
> - We have made tunings to the postgresql configuration to make it use 
> more RAM.
> - All sql commands are sent through a custom Java application; so we use 
> java + JDBC to connect to postgresql for this. The script has custom 
> commands which are interpreted by the Java app, so we can't test running 
> the exact script start to finish in psql native tools since the native 
> tools won't understand our custom sql. However the java app is very 
> simple. All individual sql commands are committed immediately after they 
> run so there is no long transaction in play here. The psql jdbc driver 
> is "postgresql-42.2.4".
> - One may suspect the custom java app; but again, htop shows postgresql 
> using 100% CPU usage on one core, so postgresql is chewing on the 
> command, doing something.
> 
> I have attached our current postgresql.conf file.
> 
> We're kind of pulling out our hair here, any ideas?
> 
> Thanks!


-- 
Adrian Klaver
adrian.klaver@aklaver.com