Map service connection to PostgreSQL DB shown as "Idle in Transaction" - Normal?

4393
13
Jump to solution
03-18-2016 06:27 AM
BogiBjornsson
Occasional Contributor

We have a dynamic map service based on an mxd map document containing a Query Layer from a spatially (PostGIS) enabled PostgreSQL table (uses geometry data type for shape column). The service works fine both in ArcGIS clients and JavaScript browser applications through REST, no problems.

Our PostgreSQL DBA contacted me however and told me that the service was leaving open and idle queries in the database which he says is not acceptable. He sent me a report as an example, see below.

POSTGRES_TXN_IDLE WARNING: DB "postgres" (host:******) longest idle in
  txn: 56384s (15 hours 39 minutes 44 seconds) PID:32278
  database:****** username:********* address:***.****.***
  port:**** query:BEGIN

My understanding is that this is normal behavior for ArcGIS server map service connections but I would like to verify if this is the correct understanding or not (we have AGS 10.3.1 and PostgreSQL 9.3.x). So is this normal behavior or not?

The only viable solution that I can think of to meet the DBAs demand is to set the minimum number of service instances to zero so that idle services (and their connections to the DB) are closed after x minutes of inactivity. This however would result in a bit of a performance hit for the first user that comes along, and no instances are running, and he has to wait for at least one instance of the service to start up.

UPDATE 20160321: Found a post by another user which seems to confirm this behavior on PostgreSQL connections. He had some interesting findings and our DBA seems to agree with him that this is not a good way to make connections to a database. Here is the post, check it out.

arcgis server connection pooling

0 Kudos
1 Solution

Accepted Solutions
pheede-esri
Esri Contributor

Bogi Bjornsson​ There were changes made in ArcGIS 10.4 to address the concerns that you raise here. Beginning with ArcGIS 10.4, connections to PostgreSQL databases no longer stay in an open and idle transaction.

Cheers,

Philip

ArcGIS for Server Product Manager

View solution in original post

13 Replies
AsrujitSengupta
Regular Contributor III
DanPatterson_Retired
MVP Esteemed Contributor

To add to Asrujit SenGupta​ 's post...

pick a place ... GeoNet Community Structure

GIS is too broad and your question seems targeted. 

options:

  • It will probably languish here in the GIS Place, alone, unnoticed, when it could find a home where the question would be warmly welcomed  with people eager to answer it.
  • move it

BogiBjornsson
Occasional Contributor

Moved, thanks for the tip! Only problem is I can only move it to one place so it's very hard to choose which one!

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

but...there is a Share option that appears when you have finished posting.. top right by the thread... now don't share tooo much... pick carefully...

BogiBjornsson
Occasional Contributor

Aha ... didn't realize that was what it was for, thought it was only for sharing with individual users. Live and learn

0 Kudos
pheede-esri
Esri Contributor

Bogi Bjornsson​ There were changes made in ArcGIS 10.4 to address the concerns that you raise here. Beginning with ArcGIS 10.4, connections to PostgreSQL databases no longer stay in an open and idle transaction.

Cheers,

Philip

ArcGIS for Server Product Manager

View solution in original post

BogiBjornsson
Occasional Contributor

Great, thanks for the feedback Philip. My DBA will be happy to hear that

0 Kudos
DuarteCarreira
Occasional Contributor II

Hi all. Yes this is potentially very good news. I am planning to test how now pgbouncer can be used with arcgis 10.4, desktop and server. This has the potential to drastically reduce the number of connections to more sane amounts. On the other hand, it may have significant impact on performance... thus the need to test.

arcgis is very sensible to lan bandwidth. you can easilly see this if you put side-by-side a 100 Mbps and a 1Gbps machines with arcmap opening the same map. Don't even need to time it...

0 Kudos
pheede-esri
Esri Contributor

Duarte,

pgBouncer is not explicitly supported even after this change. Under some circumstances we tie specific geodatabase (sde) connection settings to a specific database connection. Using pgBouncer can violate the built-in assumptions that ArcGIS sometimes makes about the connection state.

Philip

0 Kudos