[Qgis-developer] Re: Connection reusing in postgres provider

[icosa atropa]

If I'm interpreting postgresql docs correctly, there's not a whole lot
of server-side overhead from individual connections.  The default
max_connections in pg8.3 is 100, and easily adjustable, while the
default max_locks_per_transaction is 64.  The memory consumption from
this, in bytes, is max_connections * (1800 + 270 *
max_locks_per_transaction), i.e. less than 20K per connection, or less
than 2MB for 100 connections (default) or 20MB for 1000 connections.

A final connection related tidbit directly from the docs:

"shared_buffers (integer)
    Sets the amount of memory the database server uses for shared
memory buffers. The default is typically 32 megabytes (32MB), but
might be less if your kernel settings will not support it (as
determined during initdb). This setting must be at least 128 kilobytes
and at least 16 kilobytes times max_connections. (Non-default values
of BLCKSZ change the minimum.) However, settings significantly higher
than the minimum are usually needed for good performance. Several tens
of megabytes are recommended for production installations. This
parameter can only be set at server start."

In short:
1. Postgresql server admin usually takes a little tweaking for max performance
2. Postgresql servers are memory-hungry for good performance
3. The memory footprint of individual connections doesn't look,
comparatively, very high.

I'm not clear on the network traffic benefits of connection pooling,
but it looks like for "most uses" that using discrete postgresql
connections in qgis would not place an undue burden on
postgis/postgresql servers.

the relevant postgresql docs are here:
http://www.postgresql.org/docs/8.3/interactive/kernel-resources.html#SYSVIPC
http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html#GUC-MAX-CONNECTIONS

best,
christian

>  Do you think connection sharing in postgres provider should be disabled?
>  Or is there a possibility to prevent the binary cursor problem with keeping
>  connection sharing?