[gdal-dev] A performance issue when connecting to pg through PgPool.

[CULOS]

a solution which seems to work: quite simply to use the variables PRELUDE_STATEMENTS, CLOSING_STATEMENTS.

The gdal doc mentions pg_bouncer, pgpool looks like it..

For QGis, add the following lines to the .env file:
   PRELUDE_STATEMENTS = "BEGIN;"
   CLOSING_STATEMENTS = "COMMIT;"

and to the .vars file:
   PRELUDE_STATEMENTS
   CLOSING_STATEMENTS

From Pyhon, : 
  gdal.SetConfigOption("PRELUDE_STATEMENTS", "BEGIN;")
  gdal.SetConfigOption("CLOSING_STATEMENTS", "COMMIT;")


do you confirm this?


-----Message d'origine-----
De : gdal-dev [mailto:gdal-dev-bounces at lists.osgeo.org] De la part de CULOS
Envoyé : mardi 11 août 2020 11:39
À : 'gdal-dev at lists.osgeo.org'
Objet : [gdal-dev] A performance issue when connecting to pg through PgPool.

Hi,

is this the correct list for this issue ? It is about a problem of performence which concerns the couple GDAL / PgPool.
I am posting here because our other tools (talend, php, psycopg ...) which connect to the pool do not have this problem.

A trio of servers (pool, master, slave) have been working well so far, but...

under QGis, the following script launches a simple ogr.Open.

      import osgeo.ogr as ogr
      uri = "host=pool port=5432 dbname=dbname user=user password=password"
      QgsMessageLog.logMessage("CONNECT", 'perfs', level=Qgis.Info)
      conn = ogr.Open("PG:{}".format(uri))
      QgsMessageLog.logMessage("OK", 'perfs', level=Qgis.Info)

And the result : a connection that lasts 8s.
	2020-08-11T09:53:28     INFO    CONNECT
	(.. 8s ..)
	2020-08-11T09:53:36     INFO    OK

We find this delay of 8s when opening layers (first opened) in QGis. 9/10 times.

When you connect directly to the 'master' or to the 'slave', or when the pool is connected to only one node, the connection is immediate.

The ogrinfo command has the same behavior (ogrinfo -ro PG:"host=pool port=5432 dbname=my_db schemas=a_schema tables=a_table").

The pg servers logs are very different, depending on the case.
What is happening ? do you have an idea ?


Thanks,

Xavier.