ERROR: canceling statement due to statement timeout

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?

Patrick

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian.klaver@aklaver.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?

<adrian.klaver@aklaver.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:

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.

On 01/11/2017 04:31 PM, Patrick B wrote:

2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.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.klaver@aklaver.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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/11/2017 04:31 PM, Patrick B wrote:

2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>:

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.klaver@aklaver.com>

Same database, different database servers; server1 is the old Master
server and I'm using it to compare.

Are you actually running 9.2 on AWS, as the lowest version I see is 9.3.

Might be helpful to run the below on server2:

select * from pg_settings where name='statement_timeout';

and see what source says:

https://www.postgresql.org/docs/9.2/static/view-pg-settings.html

It is not the client, because if I run the query manually using explain
analyze i get the error:

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2017-01-12 13:41 GMT+13:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 01/11/2017 04:31 PM, Patrick B wrote:

2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.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.klaver@aklaver.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.klaver@aklaver.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.