Regenerating right assignments exhausts connection pool

Hi all,

I discovered a bug in referencedata service which prevents us from bulk updating facilities:

https://openlmis.atlassian.net/browse/OLMIS-4041

My suggestion is that we implement a queue and execute this method only once at a time. It’s easily configurable for @Async operations - we could set the pool size and queue capacity of ThreadPoolTaskExecutor:

https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/concurrent/ThreadPoolTaskExecutor.html

What do you think?

Best,

Jakub

Hi Jakub,

Nice catch. When Chongsun and I sketched the async regeneration of right assignments out we thought about how to make the operation more efficient for bulk updates. We didn’t fully appreciate that not implementing better efficiency today would impact the ref data seed tool.

Using a ThreadPoolTaskExecuter sounds like a good solution. Can we limit it to specific async operations - i.e. only have it effect the regeneration of right assignments?

In terms of efficiency and bulk operations we thought that folding the operations down would make a lot of sense: essentially if you push a new async job onto the queue, remove/fold/reduce it’s addition down to any preexisting queued tasks. Obviously you’d need to do this by type of task, which is where some complexity lies (sometimes it’s by user, sometimes it effects all users with rights at a facility). Would using your proposed solution also allow us to use the type of queue we’d want (i.e. to support folding)?

Best,
Josh

···

On Monday, January 29, 2018 at 10:11:03 AM UTC-8, jkondrat@soldevelo.com wrote:

Hi all,

I discovered a bug in referencedata service which prevents us from bulk updating facilities:

https://openlmis.atlassian.net/browse/OLMIS-4041

My suggestion is that we implement a queue and execute this method only once at a time. It’s easily configurable for @Async operations - we could set the pool size and queue capacity of ThreadPoolTaskExecutor:

https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/concurrent/ThreadPoolTaskExecutor.html

What do you think?

Best,

Jakub

Hi Josh,

yes, you can limit it to specific operations by adding executor’s name in @Async annotation like so:

@Async(“RightAssignmentExecutor”)

``

I believe we could override submit() method in order to fold down tasks, I would have to play with it a little more through. Another solution would be to use one executor per task type and limit the queue size to 1 - it would reject tasks if we already had one in queue.

Thanks,

Jakub

···

On Tuesday, January 30, 2018 at 12:59:06 AM UTC+1, Josh Zamor wrote:

Hi Jakub,

Nice catch. When Chongsun and I sketched the async regeneration of right assignments out we thought about how to make the operation more efficient for bulk updates. We didn’t fully appreciate that not implementing better efficiency today would impact the ref data seed tool.

Using a ThreadPoolTaskExecuter sounds like a good solution. Can we limit it to specific async operations - i.e. only have it effect the regeneration of right assignments?

In terms of efficiency and bulk operations we thought that folding the operations down would make a lot of sense: essentially if you push a new async job onto the queue, remove/fold/reduce it’s addition down to any preexisting queued tasks. Obviously you’d need to do this by type of task, which is where some complexity lies (sometimes it’s by user, sometimes it effects all users with rights at a facility). Would using your proposed solution also allow us to use the type of queue we’d want (i.e. to support folding)?

Best,
Josh

On Monday, January 29, 2018 at 10:11:03 AM UTC-8, jkon...@soldevelo.com wrote:

Hi all,

I discovered a bug in referencedata service which prevents us from bulk updating facilities:

https://openlmis.atlassian.net/browse/OLMIS-4041

My suggestion is that we implement a queue and execute this method only once at a time. It’s easily configurable for @Async operations - we could set the pool size and queue capacity of ThreadPoolTaskExecutor:

https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/concurrent/ThreadPoolTaskExecutor.html

What do you think?

Best,

Jakub

Hi all,

I investigated this issue a little more and it seems that regenerating right assignments takes about 3-4 minutes, which means we would have to heavily throttle down ref data seed tool to prevent connection pool exhaustion. Updating 800 facilities would then take over 2 days - I don’t think this is an acceptable workaround.

I wonder if it would be possible to make a patch release of referencedata service with this fix. If so, I could work on it right away.

Please let me know your thoughts about the patch release and proposed solution.

Thanks,

Jakub

···

On Tuesday, January 30, 2018 at 2:01:13 PM UTC+1, jkon...@soldevelo.com wrote:

Hi Josh,

yes, you can limit it to specific operations by adding executor’s name in @Async annotation like so:

@Async(“RightAssignmentExecutor”)

``

I believe we could override submit() method in order to fold down tasks, I would have to play with it a little more through. Another solution would be to use one executor per task type and limit the queue size to 1 - it would reject tasks if we already had one in queue.

Thanks,

Jakub

On Tuesday, January 30, 2018 at 12:59:06 AM UTC+1, Josh Zamor wrote:

Hi Jakub,

Nice catch. When Chongsun and I sketched the async regeneration of right assignments out we thought about how to make the operation more efficient for bulk updates. We didn’t fully appreciate that not implementing better efficiency today would impact the ref data seed tool.

Using a ThreadPoolTaskExecuter sounds like a good solution. Can we limit it to specific async operations - i.e. only have it effect the regeneration of right assignments?

In terms of efficiency and bulk operations we thought that folding the operations down would make a lot of sense: essentially if you push a new async job onto the queue, remove/fold/reduce it’s addition down to any preexisting queued tasks. Obviously you’d need to do this by type of task, which is where some complexity lies (sometimes it’s by user, sometimes it effects all users with rights at a facility). Would using your proposed solution also allow us to use the type of queue we’d want (i.e. to support folding)?

Best,
Josh

On Monday, January 29, 2018 at 10:11:03 AM UTC-8, jkon...@soldevelo.com wrote:

Hi all,

I discovered a bug in referencedata service which prevents us from bulk updating facilities:

https://openlmis.atlassian.net/browse/OLMIS-4041

My suggestion is that we implement a queue and execute this method only once at a time. It’s easily configurable for @Async operations - we could set the pool size and queue capacity of ThreadPoolTaskExecutor:

https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/concurrent/ThreadPoolTaskExecutor.html

What do you think?

Best,

Jakub

Hey Jakub,

It seems strange to me that the regeneration task is taking minutes to finish. On our performance test server, the right assignment regeneration took ~10 seconds on ~15,000 rows. How many rows does the right_assignments table have?

Shalom,

Chongsun

– ​

There are 10 kinds of people in this world; those who understand binary, and those who don’t.

Chongsun Ahn | chongsun.ahn@villagereach.org

Software Development Engineer

Village****Reach* ** Starting at the Last Mile*

2900 Eastlake Ave. E, Suite 230, Seattle, WA 98102, USA

DIRECT: 1.206.512.1536 **CELL: **1.206.910.0973 FAX: 1.206.860.6972

SKYPE: chongsun.ahn.vr

www.villagereach.org

Connect on Facebook****, Twitter** ** and our Blog

···

On Jan 31, 2018, at 11:21 AM, > jkondrat@soldevelo.com wrote:

Hi all,

I investigated this issue a little more and it seems that regenerating right assignments takes about 3-4 minutes, which means we would have to heavily throttle down ref data seed tool to prevent connection pool exhaustion. Updating 800 facilities would then take over 2 days - I don’t think this is an acceptable workaround.

I wonder if it would be possible to make a patch release of referencedata service with this fix. If so, I could work on it right away.

Please let me know your thoughts about the patch release and proposed solution.

Thanks,

Jakub

On Tuesday, January 30, 2018 at 2:01:13 PM UTC+1, jkon…@soldevelo.com wrote:

Hi Josh,

yes, you can limit it to specific operations by adding executor’s name in @Async annotation like so:

@Async(“RightAssignmentExecutor”)

``

I believe we could override submit() method in order to fold down tasks, I would have to play with it a little more through. Another solution would be to use one executor per task type and limit the queue size to 1 - it would reject tasks if we already had one in queue.

Thanks,

Jakub

On Tuesday, January 30, 2018 at 12:59:06 AM UTC+1, Josh Zamor wrote:

Hi Jakub,

Nice catch. When Chongsun and I sketched the async regeneration of right assignments out we thought about how to make the operation more efficient for bulk updates. We didn’t fully appreciate that not implementing better efficiency today would impact the ref data seed tool.

Using a ThreadPoolTaskExecuter sounds like a good solution. Can we limit it to specific async operations - i.e. only have it effect the regeneration of right assignments?

In terms of efficiency and bulk operations we thought that folding the operations down would make a lot of sense: essentially if you push a new async job onto the queue, remove/fold/reduce it’s addition down to any preexisting queued tasks. Obviously you’d need to do this by type of task, which is where some complexity lies (sometimes it’s by user, sometimes it effects all users with rights at a facility). Would using your proposed solution also allow us to use the type of queue we’d want (i.e. to support folding)?

Best,

Josh

On Monday, January 29, 2018 at 10:11:03 AM UTC-8, jkon...@soldevelo.com wrote:

Hi all,

I discovered a bug in referencedata service which prevents us from bulk updating facilities:

https://openlmis.atlassian.net/browse/OLMIS-4041

My suggestion is that we implement a queue and execute this method only once at a time. It’s easily configurable for @Async operations - we could set the pool size and queue capacity of ThreadPoolTaskExecutor:

[https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/concurrent/ThreadPoolTaskExecutor.html

](https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/concurrent/ThreadPoolTaskExecutor.html)

What do you think?

Best,

Jakub

**

SolDevelo** Sp. z o.o. [LLC] / www.soldevelo.com

Al. Zwycięstwa 96/98, 81-451, Gdynia, Poland

Phone: +48 58 782 45 40 / Fax: +48 58 782 45 41

You received this message because you are subscribed to the Google Groups “OpenLMIS Dev” group.

To unsubscribe from this group and stop receiving emails from it, send an email to openlmis-dev+unsubscribe@googlegroups.com.

To post to this group, send email to openlmis-dev@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/openlmis-dev/51e5e187-49dd-4340-ab4d-3600d489ea7b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Hi Jakub,

The proposed solution looks great and it’ll need to make it into 3.3, which we’re getting close to. For 3.3 we also would like to deliver the mechanism and process for doing patch releases. Since we don’t have the tooling or that process, and we’re quite close to the major release, we can’t do a patch release now and instead favor putting our efforts into fixing the bug you identified as well as getting our CI/CD tooling ready to support git flow and patch releases.

In the meantime I think I’ve seen a number of workarounds being discussed in slack which sound like they should work until the release.

Best,
Josh

···

On Wednesday, January 31, 2018 at 11:21:43 AM UTC-8, jkondrat@soldevelo.com wrote:

Hi all,

I investigated this issue a little more and it seems that regenerating right assignments takes about 3-4 minutes, which means we would have to heavily throttle down ref data seed tool to prevent connection pool exhaustion. Updating 800 facilities would then take over 2 days - I don’t think this is an acceptable workaround.

I wonder if it would be possible to make a patch release of referencedata service with this fix. If so, I could work on it right away.

Please let me know your thoughts about the patch release and proposed solution.

Thanks,

Jakub

On Tuesday, January 30, 2018 at 2:01:13 PM UTC+1, jkon...@soldevelo.com wrote:

Hi Josh,

yes, you can limit it to specific operations by adding executor’s name in @Async annotation like so:

@Async(“RightAssignmentExecutor”)

``

I believe we could override submit() method in order to fold down tasks, I would have to play with it a little more through. Another solution would be to use one executor per task type and limit the queue size to 1 - it would reject tasks if we already had one in queue.

Thanks,

Jakub

On Tuesday, January 30, 2018 at 12:59:06 AM UTC+1, Josh Zamor wrote:

Hi Jakub,

Nice catch. When Chongsun and I sketched the async regeneration of right assignments out we thought about how to make the operation more efficient for bulk updates. We didn’t fully appreciate that not implementing better efficiency today would impact the ref data seed tool.

Using a ThreadPoolTaskExecuter sounds like a good solution. Can we limit it to specific async operations - i.e. only have it effect the regeneration of right assignments?

In terms of efficiency and bulk operations we thought that folding the operations down would make a lot of sense: essentially if you push a new async job onto the queue, remove/fold/reduce it’s addition down to any preexisting queued tasks. Obviously you’d need to do this by type of task, which is where some complexity lies (sometimes it’s by user, sometimes it effects all users with rights at a facility). Would using your proposed solution also allow us to use the type of queue we’d want (i.e. to support folding)?

Best,
Josh

On Monday, January 29, 2018 at 10:11:03 AM UTC-8, jkon...@soldevelo.com wrote:

Hi all,

I discovered a bug in referencedata service which prevents us from bulk updating facilities:

https://openlmis.atlassian.net/browse/OLMIS-4041

My suggestion is that we implement a queue and execute this method only once at a time. It’s easily configurable for @Async operations - we could set the pool size and queue capacity of ThreadPoolTaskExecutor:

https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/concurrent/ThreadPoolTaskExecutor.html

What do you think?

Best,

Jakub

Hi Chongsun,

Currently, we have 321190 records in right_assignments table.

Best,

Jakub

···

On Wednesday, January 31, 2018 at 9:37:07 PM UTC+1, chongsun.ahn wrote:

Hey Jakub,

It seems strange to me that the regeneration task is taking minutes to finish. On our performance test server, the right assignment regeneration took ~10 seconds on ~15,000 rows. How many rows does the right_assignments table have?

Shalom,

Chongsun

– ​

There are 10 kinds of people in this world; those who understand binary, and those who don’t.

Chongsun Ahn | chongs...@villagereach.org

Software Development Engineer

Village****Reach* ** Starting at the Last Mile*

2900 Eastlake Ave. E, Suite 230, Seattle, WA 98102, USA

DIRECT: 1.206.512.1536 **CELL: **1.206.910.0973 FAX: 1.206.860.6972

SKYPE: chongsun.ahn.vr

www.villagereach.org

Connect on Facebook****, Twitter** ** and our Blog

On Jan 31, 2018, at 11:21 AM, > > jko...@soldevelo.com wrote:

Hi all,

I investigated this issue a little more and it seems that regenerating right assignments takes about 3-4 minutes, which means we would have to heavily throttle down ref data seed tool to prevent connection pool exhaustion. Updating 800 facilities would then take over 2 days - I don’t think this is an acceptable workaround.

I wonder if it would be possible to make a patch release of referencedata service with this fix. If so, I could work on it right away.

Please let me know your thoughts about the patch release and proposed solution.

Thanks,

Jakub

On Tuesday, January 30, 2018 at 2:01:13 PM UTC+1, jkon…@soldevelo.com wrote:

Hi Josh,

yes, you can limit it to specific operations by adding executor’s name in @Async annotation like so:

@Async(“RightAssignmentExecutor”)

``

I believe we could override submit() method in order to fold down tasks, I would have to play with it a little more through. Another solution would be to use one executor per task type and limit the queue size to 1 - it would reject tasks if we already had one in queue.

Thanks,

Jakub

On Tuesday, January 30, 2018 at 12:59:06 AM UTC+1, Josh Zamor wrote:

Hi all,

I discovered a bug in referencedata service which prevents us from bulk updating facilities:

https://openlmis.atlassian.net/browse/OLMIS-4041

My suggestion is that we implement a queue and execute this method only once at a time. It’s easily configurable for @Async operations - we could set the pool size and queue capacity of ThreadPoolTaskExecutor:

[https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/concurrent/ThreadPoolTaskExecutor.html

](https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/concurrent/ThreadPoolTaskExecutor.html)

What do you think?

Best,

Jakub

Hi Jakub,

Nice catch. When Chongsun and I sketched the async regeneration of right assignments out we thought about how to make the operation more efficient for bulk updates. We didn’t fully appreciate that not implementing better efficiency today would impact the ref data seed tool.

Using a ThreadPoolTaskExecuter sounds like a good solution. Can we limit it to specific async operations - i.e. only have it effect the regeneration of right assignments?

In terms of efficiency and bulk operations we thought that folding the operations down would make a lot of sense: essentially if you push a new async job onto the queue, remove/fold/reduce it’s addition down to any preexisting queued tasks. Obviously you’d need to do this by type of task, which is where some complexity lies (sometimes it’s by user, sometimes it effects all users with rights at a facility). Would using your proposed solution also allow us to use the type of queue we’d want (i.e. to support folding)?

Best,

Josh

On Monday, January 29, 2018 at 10:11:03 AM UTC-8, jkon...@soldevelo.com wrote:

**

SolDevelo** Sp. z o.o. [LLC] / www.soldevelo.com

Al. Zwycięstwa 96/98, 81-451, Gdynia, Poland

Phone: +48 58 782 45 40 / Fax: +48 58 782 45 41

You received this message because you are subscribed to the Google Groups “OpenLMIS Dev” group.

To unsubscribe from this group and stop receiving emails from it, send an email to openlmis-dev...@googlegroups.com.

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

To view this discussion on the web visit https://groups.google.com/d/msgid/openlmis-dev/51e5e187-49dd-4340-ab4d-3600d489ea7b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.