Kong: Connections not reused when using postgresql backend

[sheerun]

Summary

The database provider I use accepts only max 5 concurrenct connections. Kong seems to create separate connection for each query and then leave them idle, instead of reusing them... Here's screenshot from elephantsql.com interface.

Steps To Reproduce

1. Run kong 0.9.0 with postgres database on backend
2. Wait 30 seconds to get 5+ idle connections.

Please re-use connections or close them properly.

Additional Details & Logs

• Kong version (0.9.0rc4)
• Kong configuration (default from next branch)

Log errors:

2016/08/18 14:01:09 [error] 109#0: *112 [lua] responses.lua:101: [postgres error] FATAL: too many connections for role "tgerhhuk", client: 10.255.0.2, server: kong_admin, request: "GET / HTTP/1.1", host: "163.172.165.146:8001"

• I use docker image I've created (sheerun/kong), it's based on your original docker image (I've sent PR).

[thibaultcha]

The postgres driver used by Kong leverages the cosocket API, and as such, leverages the OpenResty connection pool. The connection pool is not entirely in our control but managed by OpenResty itself. We don't have control over a specific number of connections and which ones to re-use, such internals are handled by OpenResty itself. In your case, it might be that OpenResty is waiting to open some more connections before re-using one of them. You might also very probably be hitting Kong concurrently, hence opening several connections at one.
Finally, there is no control for the maximum amount of opened connections to an upstream service, as this feature is still on the OpenResty roadmap. As such, I would suggest increasing the limit of 5 connections on your side if possible.

[sheerun]

If that's not something Kong can fix, do you know where I should file this issue instead?

[thibaultcha]

As per ngx_lua's README TODO section:

cosocket: pool-based backend concurrency level control: implement automatic connect queueing when the backend concurrency exceeds its connection pool limit.

So there is not much more to file, the lack of this feature is already known. I'm sure ngx_lua will appreciate contributions for this feature.

[sheerun]

Sadly it's not my expertise. I hope someone from @Mashape will be able to help @agentzh

[agentzh]

Well, one can use the lua-resty-limit-traffic library to limit backend concurrency level like that of Pg accesses:

https://github.com/openresty/lua-resty-limit-traffic/

It supports queueing excessive (backend or frontend) queries below a specified threshold. Not as efficient as a built-in connect() queueing in cosockets, but should be good enough for many use cases.

[thibaultcha]

Hey yeah that's very true! It should be quite doable to abstract this for both Cassandra and Postgres considering our DAO implementation, if you want to give it a try @sheerun.

I have previously seen another such request for limiting Postgres concurrent connections, so we might considering implementing this ourselves in the future too if the need is raising.

[sheerun]

I'll reopen it then. In next months I won't have time to fix it myself, unfortunately.

[thibaultcha]

Closing since this issue is about re-using connections and not limiting concurrency. We will consider it if more requests for it come in.