Обсуждение: Analyze command running for 2063 minutes so far

Hello,

I started an Analyze command on a database Wednesday evening at around 
9:00PM. it is now Friday morning at 8:00 and it is still running.

If I execute the query:
select max(last_analyze) from pg_stat_user_tables;

The result I receive is:
6/27/2019 8:27 AM

So it appears that no analyze information has been written to the 
pg_stat_user_tables for 24 hours.

When I execute the "top" command the postgres instance running the 
"analyze" command is using 97 to 100% of one of the cores.

Currently the overall performance of the server is not being impacted. I 
have eleven other cores and it is seldom that more than 9 have 90% or 
more utilization and the SSD appears to not be slowed much by whatever 
is happening.

I did try to execute:
SELECT pg_cancel_backend(4029);
and
SELECT pg_terminate_backend(4029);
but neither had any effect.

Since this is not currently causing any user performance issues I don't 
plan to try to do anything else until this weekend.

Since the pg_cancel_backend and pg_terminate_backend have not had any 
effect, what should I expect from shutting postgresql down and bringing 
it back up?

I am running PostgreSQL version 9.3 on Ubuntu 14.04 with 128GB of 
memory, 800GB PCIe SSD for Database files, 1TB SATA SSD for WAL, 512GB 
SATA SSD for system files.

Thank you for your assistance.

tony@exquisiteimages.com writes:
> I started an Analyze command on a database Wednesday evening at around
> 9:00PM. it is now Friday morning at 8:00 and it is still running.
> ...
> I did try to execute:
> SELECT pg_cancel_backend(4029);
> and
> SELECT pg_terminate_backend(4029);
> but neither had any effect.

Hm, that's interesting.  Can you get a stack trace from that process?

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

> I am running PostgreSQL version 9.3 on Ubuntu 14.04 with 128GB of
> memory, 800GB PCIe SSD for Database files, 1TB SATA SSD for WAL, 512GB
> SATA SSD for system files.

9.3.what exactly?

(You do know that 9.3.x is out of support, so even if this investigation
reveals a bug, we're not going to fix it in 9.3.x.  I'm willing to look
anyway on the chance that there's a bug that also affects later versions.)

            regards, tom lane

On 2019-06-28 10:15, Tom Lane wrote:
> tony@exquisiteimages.com writes:
>> I started an Analyze command on a database Wednesday evening at around
>> 9:00PM. it is now Friday morning at 8:00 and it is still running.
>> ...
>> I did try to execute:
>> SELECT pg_cancel_backend(4029);
>> and
>> SELECT pg_terminate_backend(4029);
>> but neither had any effect.
> 
> Hm, that's interesting.  Can you get a stack trace from that process?
> 
> https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
> 
>> I am running PostgreSQL version 9.3 on Ubuntu 14.04 with 128GB of
>> memory, 800GB PCIe SSD for Database files, 1TB SATA SSD for WAL, 512GB
>> SATA SSD for system files.
> 
> 9.3.what exactly?
> 
> (You do know that 9.3.x is out of support, so even if this 
> investigation
> reveals a bug, we're not going to fix it in 9.3.x.  I'm willing to look
> anyway on the chance that there's a bug that also affects later 
> versions.)
> 
>             regards, tom lane

Thanks for the offer to look at it Tom. Fortunately or unfortunately as 
the case may be, after I installed everything to get the stack trace the 
Analyze process actually finished. The max(last_analyze) did not change 
and is still showing '6/27/2019 8:27 AM', so I am not sure what it was 
doing all this time, but nothing seems the worse for it.

Thanks again.