Обсуждение: Idle transactions in PostgreSQL 9.2.4
Hi all,
I have some idle transactions in PostgreSQL 9.2.4 server which never end. My application was working fine on version 9.1 (BSD) but the problem appeared immediately as we ported it to 9.2.4 on Linux. The idle operations would frequently appear as COMMITs but sometimes I observed INSERTS as well.
Please note that each commit below refers to *very light* inserts of single rows which are supposed to last milliseconds. However some of the just never end even if they are not waiting on other operations. See below:
SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM pg_stat_activity;
datname | usename | pid | client_addr | waiting | query_start | query
----------+-----------+-------+-------------+---------+-------------------------------
AppQoSDB | appqosusr | 17015 | 127.0.0.1 | f | 2013-10-15 10:21:38.501435+01 | select AppQoSConfig.get_ip_currentnode()
AppQoSDB | appqosusr | 17016 | 127.0.0.1 | f | 2013-10-15 10:21:38.502346+01 | COMMIT
AppQoSDB | appqosusr | 17017 | 127.0.0.1 | f | 2013-10-15 10:21:38.584794+01 | select AppQoSConfig.get_ip_currentnode()
AppQoSDB | appqosusr | 17018 | 127.0.0.1 | f | 2013-10-15 10:21:38.586073+01 | COMMIT
AppQoSDB | appqosusr | 17019 | 127.0.0.1 | f | 2013-10-15 09:20:32.724517+01 | select AppQoSConfig.get_ip_currentnode()
AppQoSDB | appqosusr | 17021 | 127.0.0.1 | f | 2013-10-15 10:21:38.622651+01 | select AppQoSConfig.get_ip_currentnode()
AppQoSDB | appqosusr | 17020 | 127.0.0.1 | f | 2013-10-15 09:20:32.724433+01 | select AppQoSConfig.get_ip_currentnode()
AppQoSDB | appqosusr | 17022 | 127.0.0.1 | f | 2013-10-15 10:21:37.368831+01 | select AppQoSConfig.get_ip_currentnode()
AppQoSDB | appqosusr | 17024 | 127.0.0.1 | f | 2013-10-15 09:20:32.828307+01 | COMMIT
AppQoSDB | appqosusr | 17026 | 127.0.0.1 | f | 2013-10-15 10:21:38.624378+01 | COMMIT
AppQoSDB | appqosusr | 17023 | 127.0.0.1 | f | 2013-10-15 09:20:32.828302+01 | COMMIT
AppQoSDB | appqosusr | 17025 | 127.0.0.1 | f | 2013-10-15 10:21:37.369869+01 | COMMIT
AppQoSDB | appqosusr | 17027 | 127.0.0.1 | f | 2013-10-15 10:21:38.633244+01 | SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM pg_stat_activity;
This is the current configuration:
name | current_setting
------------------------------+------------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.7.2-2ubuntu1) 4.7.2, 64-bit
autovacuum | on
autovacuum_analyze_threshold | 500000
autovacuum_max_workers | 1
autovacuum_naptime | 1h
autovacuum_vacuum_threshold | 500000
checkpoint_completion_target | 0.7
checkpoint_segments | 128
checkpoint_warning | 30s
client_encoding | UTF8
effective_cache_size | 16GB
lc_collate | en_GB.UTF-8
lc_ctype | en_GB.UTF-8
listen_addresses | *
log_destination | syslog, stderr
log_min_duration_statement | 2min
log_rotation_age | 10d
log_rotation_size | 100MB
logging_collector | on
max_connections | 200
max_stack_depth | 2MB
server_encoding | UTF8
shared_buffers | 6GB
synchronous_commit | off
TimeZone | GB
wal_buffers | 128kB
work_mem | 18MB
Thank you,
Svetlin Manavski
2013/10/15 Svetlin Manavski <svetlin.manavski@itrinegy.com>
In 9.2 there's an extra column in the view that you're missing in your query: state.
I have some idle transactions in PostgreSQL 9.2.4 server which never end. My application was working fine on version 9.1 (BSD) but the problem appeared immediately as we ported it to 9.2.4 on Linux. The idle operations would frequently appear as COMMITs but sometimes I observed INSERTS as well.Please note that each commit below refers to *very light* inserts of single rows which are supposed to last milliseconds. However some of the just never end even if they are not waiting on other operations. See below:SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM pg_stat_activity;
In 9.2 there's an extra column in the view that you're missing in your query: state.
If state='active', then `query` shows _currently running_ query.
Otherwise it shows _last query_ executed by the session.
Check here: http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
--
Victor Y. Yegorov
Victor Y. Yegorov
Thanks Victor. The states were idle indeed but my application was not getting the results from that connections back. I have just found that there were 2 of the threads issuing queries to the same connection in parallel.
That was the origin of the problem indeed.
Best Regards,
Svetlin Manavski
On 17 October 2013 15:26, Victor Yegorov <vyegorov@gmail.com> wrote:
2013/10/15 Svetlin Manavski <svetlin.manavski@itrinegy.com>In 9.2 there's an extra column in the view that you're missing in your query: state.I have some idle transactions in PostgreSQL 9.2.4 server which never end. My application was working fine on version 9.1 (BSD) but the problem appeared immediately as we ported it to 9.2.4 on Linux. The idle operations would frequently appear as COMMITs but sometimes I observed INSERTS as well.Please note that each commit below refers to *very light* inserts of single rows which are supposed to last milliseconds. However some of the just never end even if they are not waiting on other operations. See below:SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM pg_stat_activity;If state='active', then `query` shows _currently running_ query.Otherwise it shows _last query_ executed by the session.Check here: http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW--
Victor Y. Yegorov