Description
Hi, i have some trouble.
I have query that returns 789 rows:
SELECT "brand_id", "brand_name" FROM "brands" GROUP BY "brand_id", "brand_name" ORDER BY "brand_name" ASC;
Execution Time: 2.558 ms
But sometimes i have suspension this query and failing by timeout.
In postgres in pg_stat_activity i see:
=# select state, query, now() - query_start as duration from pg_stat_activity order by duration desc limit 1;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------
state | idle
query | SELECT "brand_id", "brand_name" FROM "brands" GROUP BY "brand_id", "brand_name" ORDER BY "brand_name" ASC
duration | 00:00:57.017424
According to http://www.postgresql.org/docs/9.4/static/monitoring-stats.html
query: "Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed."
state = idle: The backend is waiting for a new client command.
That means that query was executed and postgres is waiting for a new nginx command. Then timeout (1 minute) expires and nginx returns 500 with timeout.
config:
upstream database {
postgres_server xxxxxxxxxxx;
postgres_keepalive off;
}
..................
.................
server {
location = /query {
internal;
postgres_pass database;
postgres_connect_timeout 30s;
postgres_result_timeout 1m;
postgres_query $echo_request_body;
}
}
Also i have similarly query (returns 928 rows) that also sometimes suspending.
I noticed that hang queries that return many rows.
Please help me to understand, that's going on.
Thanks.