topic Re: Map service connection to PostgreSQL DB shown as "Idle in Transaction" - Normal? in ArcGIS Enterprise Questions

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

BogiBjornsson — Fri, 18 Mar 2016 13:27:49 GMT

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

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

AsrujitSengupta — Fri, 18 Mar 2016 13:54:47 GMT

Enterprise GIS GeodatabaseManaging Data ArcGIS for Server

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

DanPatterson_Retired — Fri, 18 Mar 2016 14:15:26 GMT

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

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

BogiBjornsson — Fri, 18 Mar 2016 15:29:42 GMT

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!

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

DanPatterson_Retired — Fri, 18 Mar 2016 15:35:20 GMT

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...

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

BogiBjornsson — Mon, 21 Mar 2016 10:48:34 GMT

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

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

pheede-esri — Thu, 24 Mar 2016 17:19:14 GMT

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

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

BogiBjornsson — Mon, 28 Mar 2016 11:03:30 GMT

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

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

DuarteCarreira — Mon, 28 Mar 2016 17:18:01 GMT

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...

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

pheede-esri — Tue, 29 Mar 2016 21:14:47 GMT

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

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

DuarteCarreira — Tue, 12 Apr 2016 17:02:37 GMT

Philip Heede thanks for the heads up. Can you elaborate a bit more? I have made a simple test with desktop 10.4 and pgbouncer, opening an mxd with layers from 2 different logins. Connections stay at only 1 per user, which is half of 10.3.

I haven't started on benchmarking this setup yet. But I like what I see so far.

So your cautionary warning got me worried. What kind of circumstances do you mean? Maybe with any luck we do not work in those scenarios.

Thanks.

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

pheede-esri — Sun, 17 Apr 2016 17:20:42 GMT

We don't have a full inventory of cases where state is maintained, getting that is part of the work that is ongoing. Primarily we expect this to be part of more complex types of transactions such as editing of geodatabase functionality like geometric networks, street networks, parcels, etc.

However, that should not be interpreted as the only place where this could occur and thus we cannot officially support setups using pgBouncer at this time. Any such use would be at your own risk and outside of the official scope of support.

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

DuarteCarreira — Sun, 17 Apr 2016 20:20:56 GMT

Philip thanks again for your insights. I'm glad to hear that esri is looking into this.

I had thought about things that could be broken with connection pooling. I tested 2 PCs connecting with the same user, and making large selections - everything worked fine AFAIK. But the doubt was in my head now...

One thing that would be a very big help and may be easy to support is read-only connections.

Some 80% of our connections to the gdb are read-only. At least 40 connections are read-only arcgis server services. These only seat there eating away our server's memory. So just reducing these would make a big difference.

Same can be said of desktop users. Only a few have editing privileges. Big majority only reads data, although ofcourse writing stuff like big selection sets.

What are your thoughts on this? I'm not looking for a certified answer from esri, just for a good discussion on this issue. I understand things move at their own pace.

Thanks again.

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

pheede-esri — Mon, 18 Apr 2016 16:39:35 GMT

Read-only connections should be fairly safe to experiment with, I can't envision any bad things that could happen on the order of e.g. data corruption. Again though: at your own risk, not supported, and so forth. Some types of 'read-only' operations do involve making changes in the geodatabase log tables, as you've mentioned things that involve large selection sets is a good example, so it's not out of the realm of possibility that could encounter issues.