Connecting to ArcSDE on Postgres very slow

[FlorianRienäcker]

Hi everyone,

we recently set up a new ArcSDE 10.2 on a Posgres 9.2 Database and moved all our FeatureClasses (about 750) over from our Oracle based ArcSDE.

However, establishing the connection now takes a couple of minutes to complete whereas when we were still in the testing phase (about 10 FeatureClasses) everything ran rather quickly, connections taking a few seconds to establish.

So far we have analyzed that ArcGIS queries all FeatureClasses for the privilege to INSERT, UPDATE, DELETE and SELECT separately for each FeatureClass before finishing the connect. This seems to be the major time sink for us.

Is there any way to speed this up or avoid this sort of query all along? After all it would technically suffice if this query was done per FeatureClass right before the action is performed rather than do this for all FeatureClasses beforehand.

-Florian

[SimonEarnshaw]

Hi,

We are also getting this problem see my link on Stack Exchange:

Database connection times - PostGreSQL vs Oracle (ArcSDE) - Geographic Information Systems Stack Exc...

I have turned up nothing yet - but if I find out anything I will let you know.  Also if you have found out anything with this issue please let me too.

Kind regards

Simon Earnshaw

[VinceAngelo]

You didn't answer the questions in GSE's comments about hardware configuration.  It's hard to determine if these issues are even related without enough information.

- V

[SimonEarnshaw]

Hi Vince - I think I alluded to the hardware being the same for both databases by virtue of both being Hyper-V instances.  See my comments in underneath the response.  Cheers.  Simon.

[NanaDei]

Hi Simon,

Can you please log an Esri support incident for a review of the encountered performance behavior?

In addition to answers to Vince's inquiry, please include a trace file and the PostgreSQL configuration parameters.

Please let me know the case number when you receive it from the support team.

Thanks

Nana

[SimonEarnshaw]

Hi Nana,

I have attached the sql trace for a single connection via ArcCatalog to our PostgreSQL database.  Also find the db config file attached too.

In summary for the sql trace:

• At  11:13:04 the connect to ArcSDE (geology user) is issued.
• At 11:13:06 there are hundreds of simple queries on each table which amounts to 5 seconds (I think this is to confirm whether or not the tables exist  e.g SELECT 1 FROM geology.ae230s230_005m5000p01m010 LIMIT 0).  On closer inspection this tallies with number of featureclasses/rasters that we have.
• At 11:13:10 There are some different queries issued (think they are doing something with privileges.  Doesn't take long.
• At 11:13:11 the simple queries are issued again!  Taking another 5 seconds.
• At 11:13:14 Queries are made that take hardly any time but....a huge query that takes 6+ seconds that finishes at 11:13:21.
• At 11:13:21 Another huge query takes over 8 seconds, again something to do with working out privileges.
• At 11:13:33 Connection completed.

I have also logged this problem with ESRI UK - the case number for this is: CAS-09709-L6L0C8.

I am unsure whether we will get any closure on this from the UK side after speaking to our contact, so if ESRI Inc could help out on this, or anyone to be quite frank! - we would be very grateful.

Regards

Simon Earnshaw

[SimonEarnshaw]

Nana,

It's all gone a bit quiet from ESRI Inc - is anyone actively looking into this issue - it is a very important issue for us here.

Thanks

Simon

[NanaDei]

Hi Simon,

We are reviewing this case in-house and a support case will be created shortly to reference your Esri UK case number. Further review of the behavior will be handled through the support case.

Thanks

Nana

[SimonSearle]

Hi Nana,

As is sometimes the case with forums and customers on maintenance there are things moving in parallel here.  I've already drafted an incident submission from Esri UK to Redlands Support but am currently trying to get in contact with the customer to get it factually checked before I submit it.  Vince, I thinks Simon's point about hardware is fair - the two machines are both on identical Amazon AMI infrastructure and have identical GDB content but to clear up any doubt I'll give full details in my incident submission.  Thanks everyone !

[VinceAngelo]

How many feature datasets do you have?  How many total feature classes (of the 750) are in feature datasets?  What is the feature class count in the largest feature dataset?

How much RAM do you have in the database server?  Is it a physical host or a VM?  Is your disk storage fibre-attached?  Networked?  What is the network speed and network topology?  Are there and routers between the database server and the network clients?

- V