arcgis server connection pooling

[DuarteCarreira]

Hi there.

I'm looking at reducing number of connections from ArcGIS Server 10 services to a PostgreSQL 8.4 geodatabase.

I'm seeing 2 connections per map service. These connections appear as "<IDLE> in transaction", ie they are not doing anything.

My question is how to reduce the number of connections. Is there a configuration setting I can use?

Also, has anyone used PgBouncer with ArcGIS Server?

Thanks,
Duarte

[DuarteCarreira]

Reporting my findings - might be useful to someone else.

In fact it seems impossible to reduce the connections used by arcgis server, or arcgis desktop for that matter.

Connections are always kept open with a transaction. So PgBouncer cannot optimize these connections. Why ESRI chooses to keep a transaction is unknown to me.

Also, arcgis server opens 2 connections to PgSQL for every mapservice. So if you have 20 mapservices you'll have 40 connections open in PgSQL. Only when a service is shutdown due to innactivity timeout do you get its connections closed.

This does not seem scalable at all to me. Maybe things will change in 10.2??

[DuarteCarreira]

Better answers here:

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

It seems 10.4 changed to IDLE connections instead. But pooling is not a supported scenario since it could in some scenarios create unexpected results - worse case data corruption.

Still, read-only connections could be a good candidate for pooling, even though it's not supported.