Status of pool-based concurrency control for sockets?

aloisbarreras · 2018-10-10T03:02:42+00:00

Hello!The lua-resty-request v0.4 has been released,now you can install it from OPM, LuaRocks or by your hands.This version supplemented more test cases and s...

Status of pool-based concurrency control for sockets?

aloisbarreras

I see this in the TODO list of the readme on the lua-nginx-module:

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

A pretty big pain point for me is libraries like lua-resty-mysql and pgmoon can easily overload a database with too many connections with high traffic. It would be great if this could be implemented so we could enable pooling for higher level libraries like database clients.

Is there any concrete timeline of when this feature will be implemented? If not, I can start working on it, but I'd love some help from people more familiar with the code base to give me a starting point of relevant files and sections.

thibaultcha

Hi,

It has been implemented:

https://github.com/openresty/lua-nginx-module/pull/1270

Although not merged yet, I recall the patch has been tested and seems to
do well so far. Hopefully we get some info as to what the next steps for
it would be.

On 10/9/18 12:02 PM, aloisbarreras via openresty-en wrote:
> I see this in the TODO list of the readme on the lua-nginx-module:
> 
>   * /cosocket: pool-based backend concurrency level control: implement
>     automatic |connect| queueing when the backend concurrency exceeds
>     its connection pool limit./
> 
> A pretty big pain point for me is libraries like lua-resty-mysql and
> pgmoon can easily overload a database with too many connections with
> high traffic. It would be great if this could be implemented so we could
> enable pooling for higher level libraries like database clients.
> 
> Is there any concrete timeline of when this feature will be implemented?
> If not, I can start working on it, but I'd love some help from people
> more familiar with the code base to give me a starting point of relevant
> files and sections..

aloisbarreras

Hey thanks for the response Thibault. I'll keep an eye on that pull request.

In the meantime, does anyone have any workarounds or strategies for limiting the number of concurrent connections to a database? I could implement a solution myself with maybe a semaphore for obtaining connections but I'd rather not reinvent the wheel if someone already has a good solution.

On Tuesday, October 9, 2018 at 4:30:09 PM UTC-4, Thibault Charbonnier wrote:

Hi,

It has been implemented:

https://github.com/openresty/lua-nginx-module/pull/1270

Although not merged yet, I recall the patch has been tested and seems to
do well so far. Hopefully we get some info as to what the next steps for
it would be.

On 10/9/18 12:02 PM, aloisbarreras via openresty-en wrote:
> I see this in the TODO list of the readme on the lua-nginx-module:
>
> * /cosocket: pool-based backend concurrency level control: implement
> automatic |connect| queueing when the backend concurrency exceeds
> its connection pool limit./
>
> A pretty big pain point for me is libraries like lua-resty-mysql and
> pgmoon can easily overload a database with too many connections with
> high traffic. It would be great if this could be implemented so we could
> enable pooling for higher level libraries like database clients.
>
> Is there any concrete timeline of when this feature will be implemented?
> If not, I can start working on it, but I'd love some help from people
> more familiar with the code base to give me a starting point of relevant
> files and sections..

rpaprocki

I haven't tried this personally, but I've heard that pgpool (http://www.pgpool.net/mediawiki/index.php/Main_Page) can help with connection pooling on psql hosts. I might guess (no idea though) if something similar exists for MySQL, but I'd have to imagine there is.

On Tue, Oct 9, 2018 at 6:44 PM aloisbarreras via openresty-en <openresty-en@googlegroups.com> wrote:

Hey thanks for the response Thibault. I'll keep an eye on that pull request.

In the meantime, does anyone have any workarounds or strategies for limiting the number of concurrent connections to a database? I could implement a solution myself with maybe a semaphore for obtaining connections but I'd rather not reinvent the wheel if someone already has a good solution.

On Tuesday, October 9, 2018 at 4:30:09 PM UTC-4, Thibault Charbonnier wrote:
Hi,

It has been implemented:

https://github.com/openresty/lua-nginx-module/pull/1270

Although not merged yet, I recall the patch has been tested and seems to
do well so far. Hopefully we get some info as to what the next steps for
it would be.

On 10/9/18 12:02 PM, aloisbarreras via openresty-en wrote:
> I see this in the TODO list of the readme on the lua-nginx-module:
>
> * /cosocket: pool-based backend concurrency level control: implement
> automatic |connect| queueing when the backend concurrency exceeds
> its connection pool limit./
>
> A pretty big pain point for me is libraries like lua-resty-mysql and
> pgmoon can easily overload a database with too many connections with
> high traffic. It would be great if this could be implemented so we could
> enable pooling for higher level libraries like database clients.
>
> Is there any concrete timeline of when this feature will be implemented?
> If not, I can start working on it, but I'd love some help from people
> more familiar with the code base to give me a starting point of relevant
> files and sections..

aloisbarreras

Yeah I'm aware of pgbouncer as well, which seems popular. It would just be nice to have one less thing to run. In Node.js, there is a great postgres client that includes connection pooling, and I was hoping something existed for Lua as well. I looked into recreating the Node client, but it uses timers and that doesn't work as well within the openresty paradigm because timers will run in a different context than the request.

I'm going to throw together some code that pools connections and see how it works while waiting for the Openresty PR to get merged.

On Tuesday, October 9, 2018 at 9:48:40 PM UTC-4, rpaprocki wrote:

I haven't tried this personally, but I've heard that pgpool (http://www.pgpool.net/mediawiki/index.php/Main_Page) can help with connection pooling on psql hosts. I might guess (no idea though) if something similar exists for MySQL, but I'd have to imagine there is.

On Tue, Oct 9, 2018 at 6:44 PM aloisbarreras via openresty-en <openre...@googlegroups.com> wrote:
Hey thanks for the response Thibault. I'll keep an eye on that pull request.

In the meantime, does anyone have any workarounds or strategies for limiting the number of concurrent connections to a database? I could implement a solution myself with maybe a semaphore for obtaining connections but I'd rather not reinvent the wheel if someone already has a good solution.

On Tuesday, October 9, 2018 at 4:30:09 PM UTC-4, Thibault Charbonnier wrote:
Hi,

It has been implemented:

https://github.com/openresty/lua-nginx-module/pull/1270

Although not merged yet, I recall the patch has been tested and seems to
do well so far. Hopefully we get some info as to what the next steps for
it would be.

On 10/9/18 12:02 PM, aloisbarreras via openresty-en wrote:
> I see this in the TODO list of the readme on the lua-nginx-module:
>
> * /cosocket: pool-based backend concurrency level control: implement
> automatic |connect| queueing when the backend concurrency exceeds
> its connection pool limit./
>
> A pretty big pain point for me is libraries like lua-resty-mysql and
> pgmoon can easily overload a database with too many connections with
> high traffic. It would be great if this could be implemented so we could
> enable pooling for higher level libraries like database clients.
>
> Is there any concrete timeline of when this feature will be implemented?
> If not, I can start working on it, but I'd love some help from people
> more familiar with the code base to give me a starting point of relevant
> files and sections..

moseleymark

On Tue, Oct 9, 2018 at 7:31 PM aloisbarreras via openresty-en <openresty-en@googlegroups.com> wrote:

Yeah I'm aware of pgbouncer as well, which seems popular. It would just be nice to have one less thing to run. In Node.js, there is a great postgres client that includes connection pooling, and I was hoping something existed for Lua as well. I looked into recreating the Node client, but it uses timers and that doesn't work as well within the openresty paradigm because timers will run in a different context than the request.

I'm going to throw together some code that pools connections and see how it works while waiting for the Openresty PR to get merged.

On Tuesday, October 9, 2018 at 9:48:40 PM UTC-4, rpaprocki wrote:
I haven't tried this personally, but I've heard that pgpool (http://www.pgpool.net/mediawiki/index.php/Main_Page) can help with connection pooling on psql hosts. I might guess (no idea though) if something similar exists for MySQL, but I'd have to imagine there is.

On Tue, Oct 9, 2018 at 6:44 PM aloisbarreras via openresty-en <openre...@googlegroups.com> wrote:
Hey thanks for the response Thibault. I'll keep an eye on that pull request.

In the meantime, does anyone have any workarounds or strategies for limiting the number of concurrent connections to a database? I could implement a solution myself with maybe a semaphore for obtaining connections but I'd rather not reinvent the wheel if someone already has a good solution.

On Tuesday, October 9, 2018 at 4:30:09 PM UTC-4, Thibault Charbonnier wrote:
Hi,

It has been implemented:

https://github.com/openresty/lua-nginx-module/pull/1270

Although not merged yet, I recall the patch has been tested and seems to
do well so far. Hopefully we get some info as to what the next steps for
it would be.

On 10/9/18 12:02 PM, aloisbarreras via openresty-en wrote:
> I see this in the TODO list of the readme on the lua-nginx-module:
>
> * /cosocket: pool-based backend concurrency level control: implement
> automatic |connect| queueing when the backend concurrency exceeds
> its connection pool limit./
>
> A pretty big pain point for me is libraries like lua-resty-mysql and
> pgmoon can easily overload a database with too many connections with
> high traffic. It would be great if this could be implemented so we could
> enable pooling for higher level libraries like database clients.
>
> Is there any concrete timeline of when this feature will be implemented?
> If not, I can start working on it, but I'd love some help from people
> more familiar with the code base to give me a starting point of relevant
> files and sections..

I'd be pretty interested to hear from this list what people use for MySQL connection pooling. proxysql? something else?

aloisbarreras

I threw together an example of limiting concurrent database connections. Basically there is a Pool class that just uses a semaphore to limit the number of concurrent connections. It seems to be working really well with some initial testing. Obviously it's not battle tested and something like pg bouncer should probably be used for large deployments, but I think this example can work for simpler uses cases or teams that don't want an extra piece of infrastructure to run.

I'm going to more heavily test this over the next couple months and see how it holds up. I will report back with my findings, but in the meantime, I would love for others to test it out as well or tell me what you think/suggestions.

On Wednesday, October 10, 2018 at 1:23:02 PM UTC-4, Mark Moseley wrote:

On Tue, Oct 9, 2018 at 7:31 PM aloisbarreras via openresty-en <openre...@googlegroups.com> wrote:
Yeah I'm aware of pgbouncer as well, which seems popular. It would just be nice to have one less thing to run. In Node.js, there is a great postgres client that includes connection pooling, and I was hoping something existed for Lua as well. I looked into recreating the Node client, but it uses timers and that doesn't work as well within the openresty paradigm because timers will run in a different context than the request.

I'm going to throw together some code that pools connections and see how it works while waiting for the Openresty PR to get merged.

On Tuesday, October 9, 2018 at 9:48:40 PM UTC-4, rpaprocki wrote:
I haven't tried this personally, but I've heard that pgpool (http://www.pgpool.net/mediawiki/index.php/Main_Page) can help with connection pooling on psql hosts. I might guess (no idea though) if something similar exists for MySQL, but I'd have to imagine there is.

On Tue, Oct 9, 2018 at 6:44 PM aloisbarreras via openresty-en <openre...@googlegroups.com> wrote:
Hey thanks for the response Thibault. I'll keep an eye on that pull request.

In the meantime, does anyone have any workarounds or strategies for limiting the number of concurrent connections to a database? I could implement a solution myself with maybe a semaphore for obtaining connections but I'd rather not reinvent the wheel if someone already has a good solution.

On Tuesday, October 9, 2018 at 4:30:09 PM UTC-4, Thibault Charbonnier wrote:
Hi,

It has been implemented:

https://github.com/openresty/lua-nginx-module/pull/1270

Although not merged yet, I recall the patch has been tested and seems to
do well so far. Hopefully we get some info as to what the next steps for
it would be.

On 10/9/18 12:02 PM, aloisbarreras via openresty-en wrote:
> I see this in the TODO list of the readme on the lua-nginx-module:
>
> * /cosocket: pool-based backend concurrency level control: implement
> automatic |connect| queueing when the backend concurrency exceeds
> its connection pool limit./
>
> A pretty big pain point for me is libraries like lua-resty-mysql and
> pgmoon can easily overload a database with too many connections with
> high traffic. It would be great if this could be implemented so we could
> enable pooling for higher level libraries like database clients.
>
> Is there any concrete timeline of when this feature will be implemented?
> If not, I can start working on it, but I'd love some help from people
> more familiar with the code base to give me a starting point of relevant
> files and sections..

I'd be pretty interested to hear from this list what people use for MySQL connection pooling. proxysql? something else?

aloisbarreras

Forgot to post the link :D https://github.com/aloisbarreras/lua-resty-pool-example

On Thursday, October 11, 2018 at 3:36:29 PM UTC-4, aloisb...@icloud.com wrote:

I threw together an example of limiting concurrent database connections. Basically there is a Pool class that just uses a semaphore to limit the number of concurrent connections. It seems to be working really well with some initial testing. Obviously it's not battle tested and something like pg bouncer should probably be used for large deployments, but I think this example can work for simpler uses cases or teams that don't want an extra piece of infrastructure to run.

I'm going to more heavily test this over the next couple months and see how it holds up. I will report back with my findings, but in the meantime, I would love for others to test it out as well or tell me what you think/suggestions.

On Wednesday, October 10, 2018 at 1:23:02 PM UTC-4, Mark Moseley wrote:
On Tue, Oct 9, 2018 at 7:31 PM aloisbarreras via openresty-en <openre...@googlegroups.com> wrote:
Yeah I'm aware of pgbouncer as well, which seems popular. It would just be nice to have one less thing to run. In Node.js, there is a great postgres client that includes connection pooling, and I was hoping something existed for Lua as well. I looked into recreating the Node client, but it uses timers and that doesn't work as well within the openresty paradigm because timers will run in a different context than the request.

I'm going to throw together some code that pools connections and see how it works while waiting for the Openresty PR to get merged.

On Tuesday, October 9, 2018 at 9:48:40 PM UTC-4, rpaprocki wrote:
I haven't tried this personally, but I've heard that pgpool (http://www.pgpool.net/mediawiki/index.php/Main_Page) can help with connection pooling on psql hosts. I might guess (no idea though) if something similar exists for MySQL, but I'd have to imagine there is.

On Tue, Oct 9, 2018 at 6:44 PM aloisbarreras via openresty-en <openre...@googlegroups.com> wrote:
Hey thanks for the response Thibault. I'll keep an eye on that pull request.

In the meantime, does anyone have any workarounds or strategies for limiting the number of concurrent connections to a database? I could implement a solution myself with maybe a semaphore for obtaining connections but I'd rather not reinvent the wheel if someone already has a good solution.

On Tuesday, October 9, 2018 at 4:30:09 PM UTC-4, Thibault Charbonnier wrote:
Hi,

It has been implemented:

https://github.com/openresty/lua-nginx-module/pull/1270

Although not merged yet, I recall the patch has been tested and seems to
do well so far. Hopefully we get some info as to what the next steps for
it would be.

On 10/9/18 12:02 PM, aloisbarreras via openresty-en wrote:
> I see this in the TODO list of the readme on the lua-nginx-module:
>
> * /cosocket: pool-based backend concurrency level control: implement
> automatic |connect| queueing when the backend concurrency exceeds
> its connection pool limit./
>
> A pretty big pain point for me is libraries like lua-resty-mysql and
> pgmoon can easily overload a database with too many connections with
> high traffic. It would be great if this could be implemented so we could
> enable pooling for higher level libraries like database clients.
>
> Is there any concrete timeline of when this feature will be implemented?
> If not, I can start working on it, but I'd love some help from people
> more familiar with the code base to give me a starting point of relevant
> files and sections..

I'd be pretty interested to hear from this list what people use for MySQL connection pooling. proxysql? something else?