Does Kong support database connection pooling

[alexande...@bitmovin.com]

We are running Kong as API gateway and it's awesome. Yet we ran into a problem concerning database connection pooling. 

As database we are using PostgreSQL 9.4.7. As we started testing our service under load using JMeter we discovered that Kong is exceeding the maximum configured connections on the database, producing this error:

2016/04/29 08:20:09 [error] 117#0: *272301 [lua] responses.lua:99: FATAL: remaining connection slots are reserved for non-replication superuser connections [...]

As it is not an option for us to just "turn up" the allowed connections, because this would lead to a performance drop [1], we would like to use some kind of connection pooling. Which brings me to my question:

Is it possible to configure connection pooling in Kong for PostgreSql?

Best regards,

Alexander 

[1] http://stackoverflow.com/questions/10419665/how-does-pgbouncer-help-to-speed-up-django/10420469#10420469

[sc...@vmfarms.com]

Hi there,

We've also had trouble with Kong's lack of connection pooling. 

We introduced Pgbouncer between Kong and Postgres but without much luck. With both session- and transaction-pooling, the number of database connections Kong uses grows unpredictably and without bounds. It also appears that Kong does not use persistent connections so, under load, it wastes a lot of time reconnecting to the database.

Does anyone have an idea how Kong manages its connections and how to ensure it doesn't create too many?

Thanks.

[Thibault Charbonnier]

The connection pool can be configured with the lua_socket_pool_size
directive:

https://github.com/openresty/lua-nginx-module#lua_socket_pool_size

As specified, the default is 30 for every pool.

Eventually, we could create a new setting under the "postgres" and
"cassandra" namespaces to override this setting for the DB pools.

> <http://stackoverflow.com/questions/10419665/how-does-pgbouncer-help-to-speed-up-django/10420469#10420469>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Kong" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to konglayer+...@googlegroups.com
> <mailto:konglayer+...@googlegroups.com>.
> To post to this group, send email to kong...@googlegroups.com
> <mailto:kong...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/konglayer.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/konglayer/ff222255-f92d-4b98-a87a-5aede97e0bc0%40googlegroups.com
> <https://groups.google.com/d/msgid/konglayer/ff222255-f92d-4b98-a87a-5aede97e0bc0%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

[sc...@vmfarms.com]

Are the pools per request or something? We're seeing more than 200 connections to Postgres or Pgbouncer established under load.

[Thibault Charbonnier]

The pools would be on a per-worker basis.

On 5/25/16 6:12 PM, sc...@vmfarms.com wrote:
> Are the pools per request or something? We're seeing more than 200
> connections to Postgres or Pgbouncer established under load.
>
> On Wednesday, May 25, 2016 at 5:16:08 PM UTC-7, Thibault Charbonnier wrote:
>
> The connection pool can be configured with the lua_socket_pool_size
> directive:
>
> https://github.com/openresty/lua-nginx-module#lua_socket_pool_size
> <https://github.com/openresty/lua-nginx-module#lua_socket_pool_size>
>
> As specified, the default is 30 for every pool.
>
> Eventually, we could create a new setting under the "postgres" and
> "cassandra" namespaces to override this setting for the DB pools.
>

> > an email to konglayer+...@googlegroups.com <javascript:>
> > <mailto:konglayer+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to kong...@googlegroups.com

> <javascript:>
> > <mailto:kong...@googlegroups.com <javascript:>>.

> > Visit this group at https://groups.google.com/group/konglayer

> <https://groups.google.com/group/konglayer>.

> > To view this discussion on the web visit
> >
> https://groups.google.com/d/msgid/konglayer/ff222255-f92d-4b98-a87a-5aede97e0bc0%40googlegroups.com
> <https://groups.google.com/d/msgid/konglayer/ff222255-f92d-4b98-a87a-5aede97e0bc0%40googlegroups.com>
>
> >

> <https://groups.google.com/d/msgid/konglayer/ff222255-f92d-4b98-a87a-5aede97e0bc0%40googlegroups.com?utm_medium=email&utm_source=footer

> <https://groups.google.com/d/msgid/konglayer/ff222255-f92d-4b98-a87a-5aede97e0bc0%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> You received this message because you are subscribed to the Google
> Groups "Kong" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to konglayer+...@googlegroups.com
> <mailto:konglayer+...@googlegroups.com>.
> To post to this group, send email to kong...@googlegroups.com
> <mailto:kong...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/konglayer.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/konglayer/54326603-f514-433f-9a9a-b10c31c23c00%40googlegroups.com
> <https://groups.google.com/d/msgid/konglayer/54326603-f514-433f-9a9a-b10c31c23c00%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Marco]

To change the size of the pool, you need to update the "lua_socket_pool_size" nginx property in the "kong.yml" file (https://github.com/openresty/lua-nginx-module#lua_socket_pool_size). By default the pool is set to "30", and its per worker. So if you have 8 workers, the number of connections opened are 30 * 8 = 240.

In Kong every connection to the datastore (Cassandra or Postgres), or to third-party systems (the logging plugins or any other connection), is using a connection pool by default.

Cheers,

Marco

 Marco Palladino | CTO @ Mashape | mashape.com | +1 (415) 361-3858

To unsubscribe from this group and stop receiving emails from it, send an email to konglayer+...@googlegroups.com.
To post to this group, send email to kong...@googlegroups.com.

Visit this group at https://groups.google.com/group/konglayer.

To view this discussion on the web visit https://groups.google.com/d/msgid/konglayer/c0ff9cd5-1ca5-82a4-6eb7-65fbca8bbdf7%40mashape.com.

[sc...@vmfarms.com]

Thanks all: your feedback really helps.

We have 2 application servers running Kong, each with 2 workers. That should only produce 120 connections but the connection limit of 180 is being exceeded on Pgbouncer. Is there some condition that might cause the number of connections to exceed the socket pool limit? We can increase the connection limit but only if we're sure there is some finite limit that Kong won't exceed.

Cheers!