2017-01-12 13:41 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com>:
> On 01/11/2017 04:31 PM, Patrick B wrote: > >> 2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@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.kla...@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.kla...@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.
