Thanks Justin,

Yeah, I'd agree it would be much easier to show you direct command output but 
I'm prohibited due to the security and sensitive nature of my work environment.


A few observations I had earlier this evening.

I created a CTAS copy of the table in order to mirror it from its original in 
terms of constraints, indexes etc. I was able to query this copy table (using 
my "problematic" query) in under 1 second (Actually even before I had applied 
indexes). So I was also able to check the explain plan and based on the index 
makeup, the access path should be using the appropriate index correctly. So I 
was somewhat pleased that the issue was not a query optimisation difficulty as 
such.


I also dug deeper into vacuum, and discovered that even though a vacuum 
appeared to be completing, a verbose vacuum was actually erring out at the 
point of truncation at the end and not shrinking back the table to reclaim 
space. It could not obtain an exclusive lock to do this...


So I decided to intervene and terminate some active sessions (Which were active 
and not waiting) which were all querying this table with a similar query, by 
using pg_backend_terminate(pid). Some of these sessions were in an active state 
for a few days executing this query. However, the termination of the PIDs [The 
command succeeded and returned 't']has not closed down the sessions. They are 
still present, and still active and still executing. Those current sessions are 
for whatever reason completely "hung", locking out exclusive access to the 
table (from vacuum) and cannot be killed. I have not yet resorted to trying to 
kill the process PID's from the OS perspective. Instead, I will try to schedule 
an outage over the next few days, restart the server, implement a vacuum 
execution and see where I am at then.


Regards,

Ruan


________________________________
From: Justin Pryzby <pry...@telsasoft.com>
Sent: 03 November 2017 21:33
To: Rhhh Lin
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] EXPLAIN <query> command just hangs...

On Fri, Nov 03, 2017 at 09:12:02PM +0000, Rhhh Lin wrote:
> I checked for dead tuples against that particular table initially as I have 
> seen performance problems before in a related 'busy' environment which needed 
> its frequency of vacuuming to be increased. So I have a query to check for 
> table with dead tuples and this table is not showing any.
>
> I also came across the suggestion that bloat might be an issue on the 
> database and how to identify and address it and it does not appear to be 
> evident here also, so thats my thinking as to why these are not factors.

I'd be helpful if you'd paste the commands+output as you run them "\dt+, \di+,
ps, vacuum, dead tuples, etc"

> I have vacuumed. I have not reindexed as it is a prod environment and I see 
> that... "REINDEX locks out writes but not reads of the index's parent 
> table.", so I may have to arrange this to avoid any interruptions (Although 
> currently, accessing this table seems completely problematic anyway!).

Perhaps you could look into pg_repack?  Note that by default it will kill
longrunning transaction if it needs in order to (briefly) obtain a
super-exclusive lock.

> The table is 691MB and the composite index(PK) is 723 MB.

It'd be useful to see the pg_stat_user_tables.* and pg_class.reltuples and
relpages for that table.  Also output from VACUUM VERBOSE or autovacuum logs,
if you have them (but note that vacuum does different work every time it's
re-run).

> My thinking now is I may need to export this data out to a staging area 
> whereby I can attempt to "play" with it without any repercussions...

I imagine that maybe this is related to the pattern of activity on that table
(specifically around the extremes values of its indexed columns).  So it'll be
hard to reproduce, and dumping and reloading the table (or just reindexing it
without reloading it at all) will probably temporarily improve or resolve the
issue.

You could try *adding* a new index on the timestamp column alone (CREATE INDEX
CONCURRENTLY).  Re-creating the index might conceivably be the solution in
the end, and it's what pg_repack does behind the scenes.

Justin

Reply via email to