Re: ERROR: canceling statement due to statement timeout

2017-01-12 13:41 GMT+13:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 01/11/2017 04:31 PM, Patrick B wrote:
>
>> 2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
>>
>> On 01/11/2017 04:08 PM, Patrick B wrote:
>>
>> Hi guys,
>>
>> I'm using PostgreSQL 9.2 in two different servers.
>>
>> server1 (Master Postgres DB server, running Postgres 9.2 / 128GB
>> ram) -
>> RAID 10 Magnetic disks
>> server2 (Master Postgres DB server, running Postgres 9.2 / 128GB
>> ram) -
>> EBS (AWS) io2 10k IOPS
>>
>> When I run a query, I get this error:
>>
>> ERROR: canceling statement due to statement timeout
>>
>> statement_timeout is 0 in both servers.
>>
>> However, on server1 I am able to run the query. Only on server2
>> that I
>> get that error.
>>
>> Why? If it is same DB???
>>
>>
>> It is not the same DB if it is on two different servers not
>> connected by replication. More to the point statement_timeout is a
>> client connection setting, so is the client you use to connect to
>> server2 the same as the one you use for server1?
>>
>> Is AWS being 'helpful' and setting a timeout?
>>
>> Is there anything in the log before the ERROR shown above that
>> indicates something is setting statement_timeout?
>>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>> Same database, different database servers; server1 is the old Master
>> server and I'm using it to compare.
>>
>> It is not the client, because if I run the query manually using explain
>> analyze i get the error:
>>
>
> Well that is a client also.
>
> Are you sure there is not something in your AWS setup that is doing this?
>
>
>
>> live_db=> explain analyze
>>
>> SELECT DISTINCT id0
>> FROM
>> (SELECT
>>
>> [...]
>>
>> ERROR: canceling statement due to statement timeout
>>
>>
>> just a remind that on server1 works, but on server2 it doesn't.
>>
>
> Server1 is not on AWS and server2 is, see above.
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

the statement_timeout was settled to user level. The user I was using to
run the query had 10s statement_timeout. I changed it to 0 and the query
worked.

The query is taking 20s to run. I know it need to be improved and I will do
it.

I think it was working on server1 but not on server2, because as we are
using AWS there is the EBS latency that we didn't have before on slave1.