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
s
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 post
We're kind of pulling out our hair here, any ideas?
You might try issuing the command
analyze;
right *before* the command that hangs.
The rationale behind this idea is that your script changed data
and the "hung" command uses a wrong plan based on outdated statistics.
By the time you run
We're kind of pulling out our hair here, any ideas?
You might try issuing the command
analyze;
right *before* the command that hangs.
Clarification: I meant to say: "put it into the script at the location right before
the command, that hangs".
Bye,
Chris.
On 11/30/2018 09: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 p
On Fri, Nov 30, 2018 at 8:05 AM Chris Mair wrote:
>
> > We're kind of pulling out our hair here, any ideas?
>
> You might try issuing the command
>
>analyze;
>
> right *before* the command that hangs.
>
>
You might consider trying the "auto_explain" module (
https://www.postgresql.org/docs/cu
Ron writes:
> We have a seemingly identical problem in v9.6 on our big prod databases. It
> worked perfectly in 8.4 *and works perfectly in 9.6 our small staging
> servers*.
> A giant sql script that drops the oldest child tables from a large set of
> partitioned tables (by dropping all releva
On 11/30/2018 10:38 AM, Tom Lane wrote:
Ron writes:
We have a seemingly identical problem in v9.6 on our big prod databases. It
worked perfectly in 8.4 *and works perfectly in 9.6 our small staging servers*.
A giant sql script that drops the oldest child tables from a large set of
partitioned
On Fri, 30 Nov 2018 at 15:53, 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
Thanks Chris and Steve for the analyze suggestion; That will be my next
test!
On Fri, Nov 30, 2018 at 11:27 AM Steve Crawford <
scrawf...@pinpointresearch.com> wrote:
>
>
> On Fri, Nov 30, 2018 at 8:05 AM Chris Mair wrote:
>
>>
>> > We're kind of pulling out our hair here, any ideas?
>>
>> You m
I like the "add an analyze" idea; the two most likely causes of the
phenomenon (to my mind) are either:
a) Something's getting locked and Tom Lane's idea of checking pg_locks
when you notice it's stopped can help track down the problem.
Further to that, the thing I'd be expecting to see if the pr
On 11/30/2018 09: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 p
12 matches
Mail list logo