[ckan-dev] DB connection pooling

Fri Jan 31 14:22:36 UTC 2014

Hello

The engine you linked to was one just for the tracking middlewhere,
which is independant of the main CKAN sqlalchemy engine handling, so
unless you are using that it is irrelevent.

The main handling is here
https://github.com/ckan/ckan/blob/master/ckan/config/environment.py#L351

This uses engine_from_config which supports lots of different config
options like you specify.

Also if you are having network issues, in your extension I would
reccommend the following that you can put anywhere in your extension

https://github.com/okfn/ckanext-geodatagov/blob/master/ckanext/geodatagov/plugins.py#L32

This pings the database with a 'select 1' at the start of a request
and if it can not be found tries to reconnect.

Thanks

David

On 31 January 2014 13:53, Emanuele Tajariol <etj at geo-solutions.it> wrote:
> Hi list,
>
> in a CKAN instance I am working on, I found a problem dealing with database
> disconnections (the DB is shut down every night for maintenance).
> I guess that connection pooling would help mitigating the disconnections
> issue.
>
> Referencing to this page
>  http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html
> I can see that SQLAlchemy offers quite a few parameters for configuring a
> connection pool when the engine is created.
>
> Looking at how SQLAlchemy is initialized here
>    https://github.com/ckan/ckan/blob/master/ckan/config/middleware.py#L344
> it seems that in CKAN you can only configure SQLAlchemy with the connection URL
> (which is of course mandatory), but other parameters can not be specified.
>
> Am I missing any piece of code where the pool is instanced and configured?
>
> If not, the idea is to open a pull request for a patch that reads optional
> properties from the ini file (e.g.:
> - sqlalchemy.pool_recycle
> - sqlalchemy.pool_size
> - sqlalchemy.max_overflow
> - sqlalchemy.pool_timeout , ...)
> and pass them as params to the sqlalchemy engine.
>
> Any pointer to anything I'm missing?
>
>
>    Thanks,
>    Emanuele
>
> --
> ==
> Our support, Your Success!
> Visit http://opensdi.geo-solutions.it for more information.
> ==
>
> Ing. Emanuele Tajariol
> Technical Lead
>
> GeoSolutions S.A.S.
> Via Poggio alle Viti 1187
> 55054  Massarosa (LU)
> Italy
> phone: +39 0584 962313
> fax:    +39 0584 1660272
> mob:   +39 380 2116282
>
> http://www.geo-solutions.it
> http://twitter.com/geosolutions_it
>
> -------------------------------------------------------
> _______________________________________________
> ckan-dev mailing list
> ckan-dev at lists.okfn.org
> https://lists.okfn.org/mailman/listinfo/ckan-dev
> Unsubscribe: https://lists.okfn.org/mailman/options/ckan-dev