Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Dilini Gunatilake]

[Adding Mariangela]

Hi,

I think the below points should be documented in the ES doc space to avoid
more issues until a proper solution is in place.

[1] max_connections to configure to mitigate "too many connections" issue.
It is good if we can communicate about delaying each server startups as
well.

[2] the time taken to start the servers due to indexing when there are data
in the database
Normally it takes around 10 mins to settle each server when there are
around 30,000 assets in the db

Reported [1] and [2] to track the issues.

[1] https://wso2.org/jira/browse/STORE-1424
[2] https://wso2.org/jira/browse/DOCUMENTATION-3649

Regards,
Dilini


On Fri, Jul 15, 2016 at 6:28 PM, Udara Liyanage  wrote:

>
>
> On Fri, Jul 15, 2016 at 5:39 AM, Thushara Ranawaka 
> wrote:
>
>> Hi Chanaka,
>>
>> On Fri, Jul 15, 2016 at 2:42 PM, Chanaka Fernando 
>> wrote:
>>
>>> Hi Thushara,
>>>
>>> Increase the mysql active connections to 350. Please note that this is
>>> bounded to server specs.
>>> Now start 2 servers at a time. Wait 10 mins before starting the other 2
>>> WSO2 servers.
>>> Or else you can always start 1 server at a time every 10 mins. Therefore
>>> it will take 30 mins to start all 4 nodes.
>>>
>>> Do you think we can tell something like this in front of a customer?
>>> Normally customers want to start the servers within seconds and they want
>>> to start an entire cluster within minutes (maximum). Please provide a
>>> recommendation which makes sense in a real world. We need a proper way to
>>> handle this.
>>>
>>
>> This recommendation is for ES not for ESB and specially not for customers
>> since we can automate this using config. ES uses governance features
>> therefore ES need to have solr enabled. Therefore please disable[3] solr in
>> all ESB nodes. Further more I understand they will start all the servers
>> straightaway but for ES initial start(database with data) there is a heavy
>> indexing process is happening in the background to have a smooth ride
>> afterward. Since there is 4 nodes either user have to increase database
>> connections from DB side or start servers one by one with a delay. We can
>> automate this using indexingConfiguration[4] in registry.xml. This is just
>> a one time thing, you can change it back to default values after the first
>> startup.
>>
>> [4] - {Different values in each nodes with 10
>> mins different.}
>> 3
>> 
>> 50
>> 
>> 10
>>
>> Thanks,
>> Thushara.
>>
>>
> In a different thought, this kind of work arounds will cause products  to
> hard to be run in a cloud environment where
> you can not guarantee product startup order since containers/vm come
> and go due to various reasons
> Manually changing configurations also not possible in a automated
> deployment environment.
> So it is better if we can have a solid solution.
>
>>
>>
> On Fri, Jul 15, 2016 at 1:25 PM, Thushara Ranawaka 
>>> wrote:
>>>
 Hi Dilini,


 On Thu, Jul 14, 2016 at 2:31 PM, Dilini Gunatilake 
 wrote:

> Hi,
>
> @Thushara,
> Thanks for the info. Any recommended number of max_connections to
> configure considering indexing with a high load etc? Then we can add those
> details in the docs to avoid future issues.
>

 My personal recommendation,
 Increase the mysql active connections to 350. Please note that this is
 bounded to server specs.
 Now start 2 servers at a time. Wait 10 mins before starting the other 2
 WSO2 servers.
 Or else you can always start 1 server at a time every 10 mins.
 Therefore it will take 30 mins to start all 4 nodes.

 Thanks,
 Thushara.


>
> @Sumedha,
> I was able to start all the nodes because the number of connections
> reduces after some time.
>
> As Rajith mentioned, the concern is having that number of connections
> created just for a server startup having less than 25 assets.
>
> Thanks,
> Dilini
>
>
> On Thu, Jul 14, 2016 at 12:53 PM, Rajith Vitharana 
> wrote:
>
>> Hi,
>>
>> On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe <
>> sume...@wso2.com> wrote:
>>
>>> Guys,
>>> Some food for thought.
>>>
>>> 1. A single MySQL Server has a globally defined max_connections
>>> figure (151 as Dilini mentioned)
>>>
>>> So on a clustered setup, when you point to a single MySQL Server
>>> from various WSO2 products, depending on no of keep alive connections
>>> specified, you will at some point will hit too many connections from 
>>> server
>>> side @ some point.
>>>
>> Yeah totally agree, but that much(395) is bit high AFAIU when just
>> starting up a server(without any load in 4 servers, and when it 
>> stabilize,
>> it only consumes less than 20 connections without 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Udara Liyanage]

On Fri, Jul 15, 2016 at 5:39 AM, Thushara Ranawaka 
wrote:

> Hi Chanaka,
>
> On Fri, Jul 15, 2016 at 2:42 PM, Chanaka Fernando 
> wrote:
>
>> Hi Thushara,
>>
>> Increase the mysql active connections to 350. Please note that this is
>> bounded to server specs.
>> Now start 2 servers at a time. Wait 10 mins before starting the other 2
>> WSO2 servers.
>> Or else you can always start 1 server at a time every 10 mins. Therefore
>> it will take 30 mins to start all 4 nodes.
>>
>> Do you think we can tell something like this in front of a customer?
>> Normally customers want to start the servers within seconds and they want
>> to start an entire cluster within minutes (maximum). Please provide a
>> recommendation which makes sense in a real world. We need a proper way to
>> handle this.
>>
>
> This recommendation is for ES not for ESB and specially not for customers
> since we can automate this using config. ES uses governance features
> therefore ES need to have solr enabled. Therefore please disable[3] solr in
> all ESB nodes. Further more I understand they will start all the servers
> straightaway but for ES initial start(database with data) there is a heavy
> indexing process is happening in the background to have a smooth ride
> afterward. Since there is 4 nodes either user have to increase database
> connections from DB side or start servers one by one with a delay. We can
> automate this using indexingConfiguration[4] in registry.xml. This is just
> a one time thing, you can change it back to default values after the first
> startup.
>
> [4] - {Different values in each nodes with 10 mins
> different.}
> 3
> 
> 50
> 
> 10
>
> Thanks,
> Thushara.
>
>
In a different thought, this kind of work arounds will cause products  to
hard to be run in a cloud environment where
you can not guarantee product startup order since containers/vm come
and go due to various reasons
Manually changing configurations also not possible in a automated
deployment environment.
So it is better if we can have a solid solution.

>
>
On Fri, Jul 15, 2016 at 1:25 PM, Thushara Ranawaka 
>> wrote:
>>
>>> Hi Dilini,
>>>
>>>
>>> On Thu, Jul 14, 2016 at 2:31 PM, Dilini Gunatilake 
>>> wrote:
>>>
 Hi,

 @Thushara,
 Thanks for the info. Any recommended number of max_connections to
 configure considering indexing with a high load etc? Then we can add those
 details in the docs to avoid future issues.

>>>
>>> My personal recommendation,
>>> Increase the mysql active connections to 350. Please note that this is
>>> bounded to server specs.
>>> Now start 2 servers at a time. Wait 10 mins before starting the other 2
>>> WSO2 servers.
>>> Or else you can always start 1 server at a time every 10 mins. Therefore
>>> it will take 30 mins to start all 4 nodes.
>>>
>>> Thanks,
>>> Thushara.
>>>
>>>

 @Sumedha,
 I was able to start all the nodes because the number of connections
 reduces after some time.

 As Rajith mentioned, the concern is having that number of connections
 created just for a server startup having less than 25 assets.

 Thanks,
 Dilini


 On Thu, Jul 14, 2016 at 12:53 PM, Rajith Vitharana 
 wrote:

> Hi,
>
> On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe  > wrote:
>
>> Guys,
>> Some food for thought.
>>
>> 1. A single MySQL Server has a globally defined max_connections
>> figure (151 as Dilini mentioned)
>>
>> So on a clustered setup, when you point to a single MySQL Server from
>> various WSO2 products, depending on no of keep alive connections 
>> specified,
>> you will at some point will hit too many connections from server side @
>> some point.
>>
> Yeah totally agree, but that much(395) is bit high AFAIU when just
> starting up a server(without any load in 4 servers, and when it stabilize,
> it only consumes less than 20 connections without load)
>
> @Thushara, Thanks for the info, will check what you have mentioned.
>
> Thanks,
>
>>
>> So you have to rethink the deployment and use a different database
>> server.
>>
>> 2. If ES has a tendency to perform too many database calls @ startup,
>> - You can temporally eliminate that by controlling node start up order
>>
>>
>> On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana 
>> wrote:
>>
>>> Hi,
>>>
>>> We faced the same issue in ESB latest when clustering enabled, with
>>> 4 node cluster, number of connections goes to around 395 and gradually
>>> comes down to 20 or less, even with the exception happened(mysql having
>>> default max connections 151), it seems it gets stable to 20 or less
>>> connections after some time. Created a public jira to 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Rajith Vitharana]

Hi Thushara,



On Fri, Jul 15, 2016 at 6:03 PM, Thushara Ranawaka 
wrote:

> Hi Rajith,
>
> Are you using governance aspects of registry?
>
I'm not exactly sure what you meant by this, I just tried to search an
artifact with registry browser. (with "true"
it shows correct results, with the value "false" it doesn't show any
results)

> Kindly share the pack with reproducing steps, Will have a look.
>
It's just a ESB pack with registry mounts. Pack link is [1] which I have
shared with you, (you need to create relevant databases and users to start
the pack)

Noticed that it(single server startup) creates around 103 mysql connections
each time server starts and gradually reducing to smaller number(less than
10)

[1] -
https://drive.google.com/a/wso2.com/file/d/0B11-M0E-tbv5VFNEZ0JQd3dGems/view?usp=sharing

Thanks,

>
> Thanks,
> Thushara.
>
> On Fri, Jul 15, 2016 at 3:41 PM, Rajith Vitharana 
> wrote:
>
>> Hi All,
>>
>> As advised by Thushara, tried removing solr indexing in ESB using [1],
>> but in that case searching the registry with newly added resources fails(it
>> doesn't show any results). So is it ok to do this change to ESB?
>>
>> [1] - registry.xml  false
>>
>> Thanks,
>>
>> On Fri, Jul 15, 2016 at 3:09 PM, Thushara Ranawaka 
>> wrote:
>>
>>> Hi Chanaka,
>>>
>>> On Fri, Jul 15, 2016 at 2:42 PM, Chanaka Fernando 
>>> wrote:
>>>
 Hi Thushara,

 Increase the mysql active connections to 350. Please note that this is
 bounded to server specs.
 Now start 2 servers at a time. Wait 10 mins before starting the other 2
 WSO2 servers.
 Or else you can always start 1 server at a time every 10 mins.
 Therefore it will take 30 mins to start all 4 nodes.

 Do you think we can tell something like this in front of a customer?
 Normally customers want to start the servers within seconds and they want
 to start an entire cluster within minutes (maximum). Please provide a
 recommendation which makes sense in a real world. We need a proper way to
 handle this.

>>>
>>> This recommendation is for ES not for ESB and specially not for
>>> customers since we can automate this using config. ES uses governance
>>> features therefore ES need to have solr enabled. Therefore please
>>> disable[3] solr in all ESB nodes. Further more I understand they will start
>>> all the servers straightaway but for ES initial start(database with data)
>>> there is a heavy indexing process is happening in the background to have a
>>> smooth ride afterward. Since there is 4 nodes either user have to increase
>>> database connections from DB side or start servers one by one with a delay.
>>> We can automate this using indexingConfiguration[4] in registry.xml. This
>>> is just a one time thing, you can change it back to default values after
>>> the first startup.
>>>
>>> [4] - {Different values in each nodes with 10
>>> mins different.}
>>> 3
>>> 
>>> 50
>>> 
>>> 10
>>>
>>> Thanks,
>>> Thushara.
>>>
>>>
 On Fri, Jul 15, 2016 at 1:25 PM, Thushara Ranawaka 
 wrote:

> Hi Dilini,
>
>
> On Thu, Jul 14, 2016 at 2:31 PM, Dilini Gunatilake 
> wrote:
>
>> Hi,
>>
>> @Thushara,
>> Thanks for the info. Any recommended number of max_connections to
>> configure considering indexing with a high load etc? Then we can add 
>> those
>> details in the docs to avoid future issues.
>>
>
> My personal recommendation,
> Increase the mysql active connections to 350. Please note that this is
> bounded to server specs.
> Now start 2 servers at a time. Wait 10 mins before starting the other
> 2 WSO2 servers.
> Or else you can always start 1 server at a time every 10 mins.
> Therefore it will take 30 mins to start all 4 nodes.
>
> Thanks,
> Thushara.
>
>
>>
>> @Sumedha,
>> I was able to start all the nodes because the number of connections
>> reduces after some time.
>>
>> As Rajith mentioned, the concern is having that number of connections
>> created just for a server startup having less than 25 assets.
>>
>> Thanks,
>> Dilini
>>
>>
>> On Thu, Jul 14, 2016 at 12:53 PM, Rajith Vitharana 
>> wrote:
>>
>>> Hi,
>>>
>>> On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe <
>>> sume...@wso2.com> wrote:
>>>
 Guys,
 Some food for thought.

 1. A single MySQL Server has a globally defined max_connections
 figure (151 as Dilini mentioned)

 So on a clustered setup, when you point to a single MySQL Server
 from various WSO2 products, depending on no of keep alive connections
 specified, you will at some point will hit too many connections from 
 server
 side @ some 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Thushara Ranawaka]

Hi Rajith,

Are you using governance aspects of registry?
Kindly share the pack with reproducing steps, Will have a look.

Thanks,
Thushara.

On Fri, Jul 15, 2016 at 3:41 PM, Rajith Vitharana  wrote:

> Hi All,
>
> As advised by Thushara, tried removing solr indexing in ESB using [1], but
> in that case searching the registry with newly added resources fails(it
> doesn't show any results). So is it ok to do this change to ESB?
>
> [1] - registry.xml  false
>
> Thanks,
>
> On Fri, Jul 15, 2016 at 3:09 PM, Thushara Ranawaka 
> wrote:
>
>> Hi Chanaka,
>>
>> On Fri, Jul 15, 2016 at 2:42 PM, Chanaka Fernando 
>> wrote:
>>
>>> Hi Thushara,
>>>
>>> Increase the mysql active connections to 350. Please note that this is
>>> bounded to server specs.
>>> Now start 2 servers at a time. Wait 10 mins before starting the other 2
>>> WSO2 servers.
>>> Or else you can always start 1 server at a time every 10 mins. Therefore
>>> it will take 30 mins to start all 4 nodes.
>>>
>>> Do you think we can tell something like this in front of a customer?
>>> Normally customers want to start the servers within seconds and they want
>>> to start an entire cluster within minutes (maximum). Please provide a
>>> recommendation which makes sense in a real world. We need a proper way to
>>> handle this.
>>>
>>
>> This recommendation is for ES not for ESB and specially not for customers
>> since we can automate this using config. ES uses governance features
>> therefore ES need to have solr enabled. Therefore please disable[3] solr in
>> all ESB nodes. Further more I understand they will start all the servers
>> straightaway but for ES initial start(database with data) there is a heavy
>> indexing process is happening in the background to have a smooth ride
>> afterward. Since there is 4 nodes either user have to increase database
>> connections from DB side or start servers one by one with a delay. We can
>> automate this using indexingConfiguration[4] in registry.xml. This is just
>> a one time thing, you can change it back to default values after the first
>> startup.
>>
>> [4] - {Different values in each nodes with 10
>> mins different.}
>> 3
>> 
>> 50
>> 
>> 10
>>
>> Thanks,
>> Thushara.
>>
>>
>>> On Fri, Jul 15, 2016 at 1:25 PM, Thushara Ranawaka 
>>> wrote:
>>>
 Hi Dilini,


 On Thu, Jul 14, 2016 at 2:31 PM, Dilini Gunatilake 
 wrote:

> Hi,
>
> @Thushara,
> Thanks for the info. Any recommended number of max_connections to
> configure considering indexing with a high load etc? Then we can add those
> details in the docs to avoid future issues.
>

 My personal recommendation,
 Increase the mysql active connections to 350. Please note that this is
 bounded to server specs.
 Now start 2 servers at a time. Wait 10 mins before starting the other 2
 WSO2 servers.
 Or else you can always start 1 server at a time every 10 mins.
 Therefore it will take 30 mins to start all 4 nodes.

 Thanks,
 Thushara.


>
> @Sumedha,
> I was able to start all the nodes because the number of connections
> reduces after some time.
>
> As Rajith mentioned, the concern is having that number of connections
> created just for a server startup having less than 25 assets.
>
> Thanks,
> Dilini
>
>
> On Thu, Jul 14, 2016 at 12:53 PM, Rajith Vitharana 
> wrote:
>
>> Hi,
>>
>> On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe <
>> sume...@wso2.com> wrote:
>>
>>> Guys,
>>> Some food for thought.
>>>
>>> 1. A single MySQL Server has a globally defined max_connections
>>> figure (151 as Dilini mentioned)
>>>
>>> So on a clustered setup, when you point to a single MySQL Server
>>> from various WSO2 products, depending on no of keep alive connections
>>> specified, you will at some point will hit too many connections from 
>>> server
>>> side @ some point.
>>>
>> Yeah totally agree, but that much(395) is bit high AFAIU when just
>> starting up a server(without any load in 4 servers, and when it 
>> stabilize,
>> it only consumes less than 20 connections without load)
>>
>> @Thushara, Thanks for the info, will check what you have mentioned.
>>
>> Thanks,
>>
>>>
>>> So you have to rethink the deployment and use a different database
>>> server.
>>>
>>> 2. If ES has a tendency to perform too many database calls @
>>> startup,
>>> - You can temporally eliminate that by controlling node start up
>>> order
>>>
>>>
>>> On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana >> > wrote:
>>>
 Hi,

 We faced the same issue in ESB latest when clustering enabled, with
 4 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Rajith Vitharana]

Hi All,

As advised by Thushara, tried removing solr indexing in ESB using [1], but
in that case searching the registry with newly added resources fails(it
doesn't show any results). So is it ok to do this change to ESB?

[1] - registry.xml  false

Thanks,

On Fri, Jul 15, 2016 at 3:09 PM, Thushara Ranawaka 
wrote:

> Hi Chanaka,
>
> On Fri, Jul 15, 2016 at 2:42 PM, Chanaka Fernando 
> wrote:
>
>> Hi Thushara,
>>
>> Increase the mysql active connections to 350. Please note that this is
>> bounded to server specs.
>> Now start 2 servers at a time. Wait 10 mins before starting the other 2
>> WSO2 servers.
>> Or else you can always start 1 server at a time every 10 mins. Therefore
>> it will take 30 mins to start all 4 nodes.
>>
>> Do you think we can tell something like this in front of a customer?
>> Normally customers want to start the servers within seconds and they want
>> to start an entire cluster within minutes (maximum). Please provide a
>> recommendation which makes sense in a real world. We need a proper way to
>> handle this.
>>
>
> This recommendation is for ES not for ESB and specially not for customers
> since we can automate this using config. ES uses governance features
> therefore ES need to have solr enabled. Therefore please disable[3] solr in
> all ESB nodes. Further more I understand they will start all the servers
> straightaway but for ES initial start(database with data) there is a heavy
> indexing process is happening in the background to have a smooth ride
> afterward. Since there is 4 nodes either user have to increase database
> connections from DB side or start servers one by one with a delay. We can
> automate this using indexingConfiguration[4] in registry.xml. This is just
> a one time thing, you can change it back to default values after the first
> startup.
>
> [4] - {Different values in each nodes with 10 mins
> different.}
> 3
> 
> 50
> 
> 10
>
> Thanks,
> Thushara.
>
>
>> On Fri, Jul 15, 2016 at 1:25 PM, Thushara Ranawaka 
>> wrote:
>>
>>> Hi Dilini,
>>>
>>>
>>> On Thu, Jul 14, 2016 at 2:31 PM, Dilini Gunatilake 
>>> wrote:
>>>
 Hi,

 @Thushara,
 Thanks for the info. Any recommended number of max_connections to
 configure considering indexing with a high load etc? Then we can add those
 details in the docs to avoid future issues.

>>>
>>> My personal recommendation,
>>> Increase the mysql active connections to 350. Please note that this is
>>> bounded to server specs.
>>> Now start 2 servers at a time. Wait 10 mins before starting the other 2
>>> WSO2 servers.
>>> Or else you can always start 1 server at a time every 10 mins. Therefore
>>> it will take 30 mins to start all 4 nodes.
>>>
>>> Thanks,
>>> Thushara.
>>>
>>>

 @Sumedha,
 I was able to start all the nodes because the number of connections
 reduces after some time.

 As Rajith mentioned, the concern is having that number of connections
 created just for a server startup having less than 25 assets.

 Thanks,
 Dilini


 On Thu, Jul 14, 2016 at 12:53 PM, Rajith Vitharana 
 wrote:

> Hi,
>
> On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe  > wrote:
>
>> Guys,
>> Some food for thought.
>>
>> 1. A single MySQL Server has a globally defined max_connections
>> figure (151 as Dilini mentioned)
>>
>> So on a clustered setup, when you point to a single MySQL Server from
>> various WSO2 products, depending on no of keep alive connections 
>> specified,
>> you will at some point will hit too many connections from server side @
>> some point.
>>
> Yeah totally agree, but that much(395) is bit high AFAIU when just
> starting up a server(without any load in 4 servers, and when it stabilize,
> it only consumes less than 20 connections without load)
>
> @Thushara, Thanks for the info, will check what you have mentioned.
>
> Thanks,
>
>>
>> So you have to rethink the deployment and use a different database
>> server.
>>
>> 2. If ES has a tendency to perform too many database calls @ startup,
>> - You can temporally eliminate that by controlling node start up order
>>
>>
>> On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana 
>> wrote:
>>
>>> Hi,
>>>
>>> We faced the same issue in ESB latest when clustering enabled, with
>>> 4 node cluster, number of connections goes to around 395 and gradually
>>> comes down to 20 or less, even with the exception happened(mysql having
>>> default max connections 151), it seems it gets stable to 20 or less
>>> connections after some time. Created a public jira to track this issue 
>>> at
>>> [1]
>>>
>>> [1] - https://wso2.org/jira/browse/REGISTRY-3771

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Thushara Ranawaka]

Hi Chanaka,

On Fri, Jul 15, 2016 at 2:42 PM, Chanaka Fernando  wrote:

> Hi Thushara,
>
> Increase the mysql active connections to 350. Please note that this is
> bounded to server specs.
> Now start 2 servers at a time. Wait 10 mins before starting the other 2
> WSO2 servers.
> Or else you can always start 1 server at a time every 10 mins. Therefore
> it will take 30 mins to start all 4 nodes.
>
> Do you think we can tell something like this in front of a customer?
> Normally customers want to start the servers within seconds and they want
> to start an entire cluster within minutes (maximum). Please provide a
> recommendation which makes sense in a real world. We need a proper way to
> handle this.
>

This recommendation is for ES not for ESB and specially not for customers
since we can automate this using config. ES uses governance features
therefore ES need to have solr enabled. Therefore please disable[3] solr in
all ESB nodes. Further more I understand they will start all the servers
straightaway but for ES initial start(database with data) there is a heavy
indexing process is happening in the background to have a smooth ride
afterward. Since there is 4 nodes either user have to increase database
connections from DB side or start servers one by one with a delay. We can
automate this using indexingConfiguration[4] in registry.xml. This is just
a one time thing, you can change it back to default values after the first
startup.

[4] - {Different values in each nodes with 10 mins
different.}
3

50

10

Thanks,
Thushara.


> On Fri, Jul 15, 2016 at 1:25 PM, Thushara Ranawaka 
> wrote:
>
>> Hi Dilini,
>>
>>
>> On Thu, Jul 14, 2016 at 2:31 PM, Dilini Gunatilake 
>> wrote:
>>
>>> Hi,
>>>
>>> @Thushara,
>>> Thanks for the info. Any recommended number of max_connections to
>>> configure considering indexing with a high load etc? Then we can add those
>>> details in the docs to avoid future issues.
>>>
>>
>> My personal recommendation,
>> Increase the mysql active connections to 350. Please note that this is
>> bounded to server specs.
>> Now start 2 servers at a time. Wait 10 mins before starting the other 2
>> WSO2 servers.
>> Or else you can always start 1 server at a time every 10 mins. Therefore
>> it will take 30 mins to start all 4 nodes.
>>
>> Thanks,
>> Thushara.
>>
>>
>>>
>>> @Sumedha,
>>> I was able to start all the nodes because the number of connections
>>> reduces after some time.
>>>
>>> As Rajith mentioned, the concern is having that number of connections
>>> created just for a server startup having less than 25 assets.
>>>
>>> Thanks,
>>> Dilini
>>>
>>>
>>> On Thu, Jul 14, 2016 at 12:53 PM, Rajith Vitharana 
>>> wrote:
>>>
 Hi,

 On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe 
 wrote:

> Guys,
> Some food for thought.
>
> 1. A single MySQL Server has a globally defined max_connections figure
> (151 as Dilini mentioned)
>
> So on a clustered setup, when you point to a single MySQL Server from
> various WSO2 products, depending on no of keep alive connections 
> specified,
> you will at some point will hit too many connections from server side @
> some point.
>
 Yeah totally agree, but that much(395) is bit high AFAIU when just
 starting up a server(without any load in 4 servers, and when it stabilize,
 it only consumes less than 20 connections without load)

 @Thushara, Thanks for the info, will check what you have mentioned.

 Thanks,

>
> So you have to rethink the deployment and use a different database
> server.
>
> 2. If ES has a tendency to perform too many database calls @ startup,
> - You can temporally eliminate that by controlling node start up order
>
>
> On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana 
> wrote:
>
>> Hi,
>>
>> We faced the same issue in ESB latest when clustering enabled, with 4
>> node cluster, number of connections goes to around 395 and gradually 
>> comes
>> down to 20 or less, even with the exception happened(mysql having default
>> max connections 151), it seems it gets stable to 20 or less connections
>> after some time. Created a public jira to track this issue at [1]
>>
>> [1] - https://wso2.org/jira/browse/REGISTRY-3771
>>
>> Thanks,
>>
>> On Wed, Jul 13, 2016 at 4:42 PM, Dilini Gunatilake 
>> wrote:
>>
>>> Hi All,
>>>
>>> I have set up a 4 node cluster for ES 2.1.0 Alpha2 pack with mysql
>>> 5.7. When I started one store node I got an error [3].
>>>
>>> Then, I checked the connections in the mysql server and found that
>>> the default number of max_connections (151) have been exceeded while the
>>> store node is starting. But initially without 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Chanaka Fernando]

Hi Thushara,

Increase the mysql active connections to 350. Please note that this is
bounded to server specs.
Now start 2 servers at a time. Wait 10 mins before starting the other 2
WSO2 servers.
Or else you can always start 1 server at a time every 10 mins. Therefore it
will take 30 mins to start all 4 nodes.

Do you think we can tell something like this in front of a customer?
Normally customers want to start the servers within seconds and they want
to start an entire cluster within minutes (maximum). Please provide a
recommendation which makes sense in a real world. We need a proper way to
handle this.

On Fri, Jul 15, 2016 at 1:25 PM, Thushara Ranawaka 
wrote:

> Hi Dilini,
>
>
> On Thu, Jul 14, 2016 at 2:31 PM, Dilini Gunatilake 
> wrote:
>
>> Hi,
>>
>> @Thushara,
>> Thanks for the info. Any recommended number of max_connections to
>> configure considering indexing with a high load etc? Then we can add those
>> details in the docs to avoid future issues.
>>
>
> My personal recommendation,
> Increase the mysql active connections to 350. Please note that this is
> bounded to server specs.
> Now start 2 servers at a time. Wait 10 mins before starting the other 2
> WSO2 servers.
> Or else you can always start 1 server at a time every 10 mins. Therefore
> it will take 30 mins to start all 4 nodes.
>
> Thanks,
> Thushara.
>
>
>>
>> @Sumedha,
>> I was able to start all the nodes because the number of connections
>> reduces after some time.
>>
>> As Rajith mentioned, the concern is having that number of connections
>> created just for a server startup having less than 25 assets.
>>
>> Thanks,
>> Dilini
>>
>>
>> On Thu, Jul 14, 2016 at 12:53 PM, Rajith Vitharana 
>> wrote:
>>
>>> Hi,
>>>
>>> On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe 
>>> wrote:
>>>
 Guys,
 Some food for thought.

 1. A single MySQL Server has a globally defined max_connections figure
 (151 as Dilini mentioned)

 So on a clustered setup, when you point to a single MySQL Server from
 various WSO2 products, depending on no of keep alive connections specified,
 you will at some point will hit too many connections from server side @
 some point.

>>> Yeah totally agree, but that much(395) is bit high AFAIU when just
>>> starting up a server(without any load in 4 servers, and when it stabilize,
>>> it only consumes less than 20 connections without load)
>>>
>>> @Thushara, Thanks for the info, will check what you have mentioned.
>>>
>>> Thanks,
>>>

 So you have to rethink the deployment and use a different database
 server.

 2. If ES has a tendency to perform too many database calls @ startup,
 - You can temporally eliminate that by controlling node start up order


 On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana 
 wrote:

> Hi,
>
> We faced the same issue in ESB latest when clustering enabled, with 4
> node cluster, number of connections goes to around 395 and gradually comes
> down to 20 or less, even with the exception happened(mysql having default
> max connections 151), it seems it gets stable to 20 or less connections
> after some time. Created a public jira to track this issue at [1]
>
> [1] - https://wso2.org/jira/browse/REGISTRY-3771
>
> Thanks,
>
> On Wed, Jul 13, 2016 at 4:42 PM, Dilini Gunatilake 
> wrote:
>
>> Hi All,
>>
>> I have set up a 4 node cluster for ES 2.1.0 Alpha2 pack with mysql
>> 5.7. When I started one store node I got an error [3].
>>
>> Then, I checked the connections in the mysql server and found that
>> the default number of max_connections (151) have been exceeded while the
>> store node is starting. But initially without starting any ES instances
>> there were only around 20 connections. So, what we observed was when
>> starting ES instances substantially high amount of connections will be
>> created and they will be gradually reduced after sometime. Is this an
>> acceptable behavior?
>>
>> Also, we could find the recommended max_connections to configure in
>> some products [1][2], but couldn't find anything for ES. So, what is the
>> recommended value for ES?
>> For the time being we will increase the max connections to 300 and
>> proceed with testing.
>>
>> Your earliest response is highly appreciated.
>>
>> [1] https://docs.wso2.com/display/AM200/Tuning+Performance
>> [2]
>> https://docs.wso2.com/display/Governance510/Performance+Tuning+Recommendations
>>
>> [3]
>>
>> [2016-07-13 07:50:40,014] ERROR
>> {org.wso2.carbon.registry.core.dataaccess.TransactionManager} -  Failed 
>> to
>> start new registry transaction.
>> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
>> Could not create 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Thushara Ranawaka]

Hi Dilini,


On Thu, Jul 14, 2016 at 2:31 PM, Dilini Gunatilake  wrote:

> Hi,
>
> @Thushara,
> Thanks for the info. Any recommended number of max_connections to
> configure considering indexing with a high load etc? Then we can add those
> details in the docs to avoid future issues.
>

My personal recommendation,
Increase the mysql active connections to 350. Please note that this is
bounded to server specs.
Now start 2 servers at a time. Wait 10 mins before starting the other 2
WSO2 servers.
Or else you can always start 1 server at a time every 10 mins. Therefore it
will take 30 mins to start all 4 nodes.

Thanks,
Thushara.


>
> @Sumedha,
> I was able to start all the nodes because the number of connections
> reduces after some time.
>
> As Rajith mentioned, the concern is having that number of connections
> created just for a server startup having less than 25 assets.
>
> Thanks,
> Dilini
>
>
> On Thu, Jul 14, 2016 at 12:53 PM, Rajith Vitharana 
> wrote:
>
>> Hi,
>>
>> On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe 
>> wrote:
>>
>>> Guys,
>>> Some food for thought.
>>>
>>> 1. A single MySQL Server has a globally defined max_connections figure
>>> (151 as Dilini mentioned)
>>>
>>> So on a clustered setup, when you point to a single MySQL Server from
>>> various WSO2 products, depending on no of keep alive connections specified,
>>> you will at some point will hit too many connections from server side @
>>> some point.
>>>
>> Yeah totally agree, but that much(395) is bit high AFAIU when just
>> starting up a server(without any load in 4 servers, and when it stabilize,
>> it only consumes less than 20 connections without load)
>>
>> @Thushara, Thanks for the info, will check what you have mentioned.
>>
>> Thanks,
>>
>>>
>>> So you have to rethink the deployment and use a different database
>>> server.
>>>
>>> 2. If ES has a tendency to perform too many database calls @ startup,
>>> - You can temporally eliminate that by controlling node start up order
>>>
>>>
>>> On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana 
>>> wrote:
>>>
 Hi,

 We faced the same issue in ESB latest when clustering enabled, with 4
 node cluster, number of connections goes to around 395 and gradually comes
 down to 20 or less, even with the exception happened(mysql having default
 max connections 151), it seems it gets stable to 20 or less connections
 after some time. Created a public jira to track this issue at [1]

 [1] - https://wso2.org/jira/browse/REGISTRY-3771

 Thanks,

 On Wed, Jul 13, 2016 at 4:42 PM, Dilini Gunatilake 
 wrote:

> Hi All,
>
> I have set up a 4 node cluster for ES 2.1.0 Alpha2 pack with mysql
> 5.7. When I started one store node I got an error [3].
>
> Then, I checked the connections in the mysql server and found that the
> default number of max_connections (151) have been exceeded while the store
> node is starting. But initially without starting any ES instances there
> were only around 20 connections. So, what we observed was when starting ES
> instances substantially high amount of connections will be created and 
> they
> will be gradually reduced after sometime. Is this an acceptable behavior?
>
> Also, we could find the recommended max_connections to configure in
> some products [1][2], but couldn't find anything for ES. So, what is the
> recommended value for ES?
> For the time being we will increase the max connections to 300 and
> proceed with testing.
>
> Your earliest response is highly appreciated.
>
> [1] https://docs.wso2.com/display/AM200/Tuning+Performance
> [2]
> https://docs.wso2.com/display/Governance510/Performance+Tuning+Recommendations
>
> [3]
>
> [2016-07-13 07:50:40,014] ERROR
> {org.wso2.carbon.registry.core.dataaccess.TransactionManager} -  Failed to
> start new registry transaction.
> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
> Could not create connection to database server. Attempted reconnect 3
> times. Giving up.
> at sun.reflect.GeneratedConstructorAccessor107.newInstance(Unknown
> Source)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
> at
> com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2165)

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Dilini Gunatilake]

Hi,

@Thushara,
Thanks for the info. Any recommended number of max_connections to configure
considering indexing with a high load etc? Then we can add those details in
the docs to avoid future issues.

@Sumedha,
I was able to start all the nodes because the number of connections reduces
after some time.

As Rajith mentioned, the concern is having that number of connections
created just for a server startup having less than 25 assets.

Thanks,
Dilini


On Thu, Jul 14, 2016 at 12:53 PM, Rajith Vitharana  wrote:

> Hi,
>
> On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe 
> wrote:
>
>> Guys,
>> Some food for thought.
>>
>> 1. A single MySQL Server has a globally defined max_connections figure
>> (151 as Dilini mentioned)
>>
>> So on a clustered setup, when you point to a single MySQL Server from
>> various WSO2 products, depending on no of keep alive connections specified,
>> you will at some point will hit too many connections from server side @
>> some point.
>>
> Yeah totally agree, but that much(395) is bit high AFAIU when just
> starting up a server(without any load in 4 servers, and when it stabilize,
> it only consumes less than 20 connections without load)
>
> @Thushara, Thanks for the info, will check what you have mentioned.
>
> Thanks,
>
>>
>> So you have to rethink the deployment and use a different database server.
>>
>> 2. If ES has a tendency to perform too many database calls @ startup,
>> - You can temporally eliminate that by controlling node start up order
>>
>>
>> On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana 
>> wrote:
>>
>>> Hi,
>>>
>>> We faced the same issue in ESB latest when clustering enabled, with 4
>>> node cluster, number of connections goes to around 395 and gradually comes
>>> down to 20 or less, even with the exception happened(mysql having default
>>> max connections 151), it seems it gets stable to 20 or less connections
>>> after some time. Created a public jira to track this issue at [1]
>>>
>>> [1] - https://wso2.org/jira/browse/REGISTRY-3771
>>>
>>> Thanks,
>>>
>>> On Wed, Jul 13, 2016 at 4:42 PM, Dilini Gunatilake 
>>> wrote:
>>>
 Hi All,

 I have set up a 4 node cluster for ES 2.1.0 Alpha2 pack with mysql 5.7.
 When I started one store node I got an error [3].

 Then, I checked the connections in the mysql server and found that the
 default number of max_connections (151) have been exceeded while the store
 node is starting. But initially without starting any ES instances there
 were only around 20 connections. So, what we observed was when starting ES
 instances substantially high amount of connections will be created and they
 will be gradually reduced after sometime. Is this an acceptable behavior?

 Also, we could find the recommended max_connections to configure in
 some products [1][2], but couldn't find anything for ES. So, what is the
 recommended value for ES?
 For the time being we will increase the max connections to 300 and
 proceed with testing.

 Your earliest response is highly appreciated.

 [1] https://docs.wso2.com/display/AM200/Tuning+Performance
 [2]
 https://docs.wso2.com/display/Governance510/Performance+Tuning+Recommendations

 [3]

 [2016-07-13 07:50:40,014] ERROR
 {org.wso2.carbon.registry.core.dataaccess.TransactionManager} -  Failed to
 start new registry transaction.
 com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
 Could not create connection to database server. Attempted reconnect 3
 times. Giving up.
 at sun.reflect.GeneratedConstructorAccessor107.newInstance(Unknown
 Source)
 at
 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
 at com.mysql.jdbc.Util.getInstance(Util.java:387)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
 at
 com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2165)
 at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2090)
 at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795)
 at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
 at sun.reflect.GeneratedConstructorAccessor76.newInstance(Unknown
 Source)
 at
 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
 at 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Rajith Vitharana]

Hi,

On Thu, Jul 14, 2016 at 12:44 PM, Sumedha Rubasinghe 
wrote:

> Guys,
> Some food for thought.
>
> 1. A single MySQL Server has a globally defined max_connections figure
> (151 as Dilini mentioned)
>
> So on a clustered setup, when you point to a single MySQL Server from
> various WSO2 products, depending on no of keep alive connections specified,
> you will at some point will hit too many connections from server side @
> some point.
>
Yeah totally agree, but that much(395) is bit high AFAIU when just starting
up a server(without any load in 4 servers, and when it stabilize, it only
consumes less than 20 connections without load)

@Thushara, Thanks for the info, will check what you have mentioned.

Thanks,

>
> So you have to rethink the deployment and use a different database server.
>
> 2. If ES has a tendency to perform too many database calls @ startup,
> - You can temporally eliminate that by controlling node start up order
>
>
> On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana 
> wrote:
>
>> Hi,
>>
>> We faced the same issue in ESB latest when clustering enabled, with 4
>> node cluster, number of connections goes to around 395 and gradually comes
>> down to 20 or less, even with the exception happened(mysql having default
>> max connections 151), it seems it gets stable to 20 or less connections
>> after some time. Created a public jira to track this issue at [1]
>>
>> [1] - https://wso2.org/jira/browse/REGISTRY-3771
>>
>> Thanks,
>>
>> On Wed, Jul 13, 2016 at 4:42 PM, Dilini Gunatilake 
>> wrote:
>>
>>> Hi All,
>>>
>>> I have set up a 4 node cluster for ES 2.1.0 Alpha2 pack with mysql 5.7.
>>> When I started one store node I got an error [3].
>>>
>>> Then, I checked the connections in the mysql server and found that the
>>> default number of max_connections (151) have been exceeded while the store
>>> node is starting. But initially without starting any ES instances there
>>> were only around 20 connections. So, what we observed was when starting ES
>>> instances substantially high amount of connections will be created and they
>>> will be gradually reduced after sometime. Is this an acceptable behavior?
>>>
>>> Also, we could find the recommended max_connections to configure in some
>>> products [1][2], but couldn't find anything for ES. So, what is the
>>> recommended value for ES?
>>> For the time being we will increase the max connections to 300 and
>>> proceed with testing.
>>>
>>> Your earliest response is highly appreciated.
>>>
>>> [1] https://docs.wso2.com/display/AM200/Tuning+Performance
>>> [2]
>>> https://docs.wso2.com/display/Governance510/Performance+Tuning+Recommendations
>>>
>>> [3]
>>>
>>> [2016-07-13 07:50:40,014] ERROR
>>> {org.wso2.carbon.registry.core.dataaccess.TransactionManager} -  Failed to
>>> start new registry transaction.
>>> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
>>> Could not create connection to database server. Attempted reconnect 3
>>> times. Giving up.
>>> at sun.reflect.GeneratedConstructorAccessor107.newInstance(Unknown
>>> Source)
>>> at
>>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>>> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
>>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
>>> at com.mysql.jdbc.Util.getInstance(Util.java:387)
>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
>>> at
>>> com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2165)
>>> at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2090)
>>> at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795)
>>> at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
>>> at sun.reflect.GeneratedConstructorAccessor76.newInstance(Unknown Source)
>>> at
>>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>>> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
>>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
>>> at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
>>> at
>>> com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
>>> at
>>> org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
>>> at
>>> org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
>>> at
>>> org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:701)
>>> at
>>> org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:635)
>>> at
>>> org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188)
>>> at
>>> 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Sumedha Rubasinghe]

Guys,
Some food for thought.

1. A single MySQL Server has a globally defined max_connections figure (151
as Dilini mentioned)

So on a clustered setup, when you point to a single MySQL Server from
various WSO2 products, depending on no of keep alive connections specified,
you will at some point will hit too many connections from server side @
some point.

So you have to rethink the deployment and use a different database server.

2. If ES has a tendency to perform too many database calls @ startup,
- You can temporally eliminate that by controlling node start up order


On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana  wrote:

> Hi,
>
> We faced the same issue in ESB latest when clustering enabled, with 4 node
> cluster, number of connections goes to around 395 and gradually comes down
> to 20 or less, even with the exception happened(mysql having default max
> connections 151), it seems it gets stable to 20 or less connections after
> some time. Created a public jira to track this issue at [1]
>
> [1] - https://wso2.org/jira/browse/REGISTRY-3771
>
> Thanks,
>
> On Wed, Jul 13, 2016 at 4:42 PM, Dilini Gunatilake 
> wrote:
>
>> Hi All,
>>
>> I have set up a 4 node cluster for ES 2.1.0 Alpha2 pack with mysql 5.7.
>> When I started one store node I got an error [3].
>>
>> Then, I checked the connections in the mysql server and found that the
>> default number of max_connections (151) have been exceeded while the store
>> node is starting. But initially without starting any ES instances there
>> were only around 20 connections. So, what we observed was when starting ES
>> instances substantially high amount of connections will be created and they
>> will be gradually reduced after sometime. Is this an acceptable behavior?
>>
>> Also, we could find the recommended max_connections to configure in some
>> products [1][2], but couldn't find anything for ES. So, what is the
>> recommended value for ES?
>> For the time being we will increase the max connections to 300 and
>> proceed with testing.
>>
>> Your earliest response is highly appreciated.
>>
>> [1] https://docs.wso2.com/display/AM200/Tuning+Performance
>> [2]
>> https://docs.wso2.com/display/Governance510/Performance+Tuning+Recommendations
>>
>> [3]
>>
>> [2016-07-13 07:50:40,014] ERROR
>> {org.wso2.carbon.registry.core.dataaccess.TransactionManager} -  Failed to
>> start new registry transaction.
>> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
>> Could not create connection to database server. Attempted reconnect 3
>> times. Giving up.
>> at sun.reflect.GeneratedConstructorAccessor107.newInstance(Unknown Source)
>> at
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
>> at com.mysql.jdbc.Util.getInstance(Util.java:387)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
>> at
>> com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2165)
>> at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2090)
>> at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795)
>> at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
>> at sun.reflect.GeneratedConstructorAccessor76.newInstance(Unknown Source)
>> at
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
>> at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
>> at
>> com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
>> at
>> org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
>> at
>> org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
>> at
>> org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:701)
>> at
>> org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:635)
>> at
>> org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188)
>> at
>> org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128)
>> at
>> org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCTransactionManager.beginTransaction(JDBCTransactionManager.java:80)
>> at
>> org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.beginTransaction(EmbeddedRegistry.java:443)
>> at
>> org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.get(EmbeddedRegistry.java:505)
>> at
>> org.wso2.carbon.registry.core.session.UserRegistry.getInternal(UserRegistry.java:617)
>> at
>> 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Thushara Ranawaka]

Hi Dilini,

To overcome the $subject issue you can do 2 things,

1. Increase the database max connections[1]
2. Increase the solr indexing frequency(indexingFrequencyInSeconds)[2].

Note: After the first startup you can decrease above values. This much
indexing load will happen in first startup.

@Rajith,

AFAIU many of the connections are created to carry out the solr indexing
task. Since ESB is not using Governance aspect you can simply disable[3]
solr indexing task.

[1] - http://stackoverflow.com/questions/22297773/increase-mysql-connections
[2] - registry.xml  {add value
here}
[3] - registry.xml  false

Thanks,
Thushara.

On Thu, Jul 14, 2016 at 12:04 PM, Rajith Vitharana  wrote:

> Hi,
>
> We faced the same issue in ESB latest when clustering enabled, with 4 node
> cluster, number of connections goes to around 395 and gradually comes down
> to 20 or less, even with the exception happened(mysql having default max
> connections 151), it seems it gets stable to 20 or less connections after
> some time. Created a public jira to track this issue at [1]
>
> [1] - https://wso2.org/jira/browse/REGISTRY-3771
>
> Thanks,
>
> On Wed, Jul 13, 2016 at 4:42 PM, Dilini Gunatilake 
> wrote:
>
>> Hi All,
>>
>> I have set up a 4 node cluster for ES 2.1.0 Alpha2 pack with mysql 5.7.
>> When I started one store node I got an error [3].
>>
>> Then, I checked the connections in the mysql server and found that the
>> default number of max_connections (151) have been exceeded while the store
>> node is starting. But initially without starting any ES instances there
>> were only around 20 connections. So, what we observed was when starting ES
>> instances substantially high amount of connections will be created and they
>> will be gradually reduced after sometime. Is this an acceptable behavior?
>>
>> Also, we could find the recommended max_connections to configure in some
>> products [1][2], but couldn't find anything for ES. So, what is the
>> recommended value for ES?
>> For the time being we will increase the max connections to 300 and
>> proceed with testing.
>>
>> Your earliest response is highly appreciated.
>>
>> [1] https://docs.wso2.com/display/AM200/Tuning+Performance
>> [2]
>> https://docs.wso2.com/display/Governance510/Performance+Tuning+Recommendations
>>
>> [3]
>>
>> [2016-07-13 07:50:40,014] ERROR
>> {org.wso2.carbon.registry.core.dataaccess.TransactionManager} -  Failed to
>> start new registry transaction.
>> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
>> Could not create connection to database server. Attempted reconnect 3
>> times. Giving up.
>> at sun.reflect.GeneratedConstructorAccessor107.newInstance(Unknown Source)
>> at
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
>> at com.mysql.jdbc.Util.getInstance(Util.java:387)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
>> at
>> com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2165)
>> at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2090)
>> at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795)
>> at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
>> at sun.reflect.GeneratedConstructorAccessor76.newInstance(Unknown Source)
>> at
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
>> at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
>> at
>> com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
>> at
>> org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
>> at
>> org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
>> at
>> org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:701)
>> at
>> org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:635)
>> at
>> org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188)
>> at
>> org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128)
>> at
>> org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCTransactionManager.beginTransaction(JDBCTransactionManager.java:80)
>> at
>> org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.beginTransaction(EmbeddedRegistry.java:443)
>> at
>> org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.get(EmbeddedRegistry.java:505)
>> at
>> 

Re: [Dev] [ES] Too many mysql connections when starting ES instances

[Rajith Vitharana]

Hi,

We faced the same issue in ESB latest when clustering enabled, with 4 node
cluster, number of connections goes to around 395 and gradually comes down
to 20 or less, even with the exception happened(mysql having default max
connections 151), it seems it gets stable to 20 or less connections after
some time. Created a public jira to track this issue at [1]

[1] - https://wso2.org/jira/browse/REGISTRY-3771

Thanks,

On Wed, Jul 13, 2016 at 4:42 PM, Dilini Gunatilake  wrote:

> Hi All,
>
> I have set up a 4 node cluster for ES 2.1.0 Alpha2 pack with mysql 5.7.
> When I started one store node I got an error [3].
>
> Then, I checked the connections in the mysql server and found that the
> default number of max_connections (151) have been exceeded while the store
> node is starting. But initially without starting any ES instances there
> were only around 20 connections. So, what we observed was when starting ES
> instances substantially high amount of connections will be created and they
> will be gradually reduced after sometime. Is this an acceptable behavior?
>
> Also, we could find the recommended max_connections to configure in some
> products [1][2], but couldn't find anything for ES. So, what is the
> recommended value for ES?
> For the time being we will increase the max connections to 300 and proceed
> with testing.
>
> Your earliest response is highly appreciated.
>
> [1] https://docs.wso2.com/display/AM200/Tuning+Performance
> [2]
> https://docs.wso2.com/display/Governance510/Performance+Tuning+Recommendations
>
> [3]
>
> [2016-07-13 07:50:40,014] ERROR
> {org.wso2.carbon.registry.core.dataaccess.TransactionManager} -  Failed to
> start new registry transaction.
> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
> Could not create connection to database server. Attempted reconnect 3
> times. Giving up.
> at sun.reflect.GeneratedConstructorAccessor107.newInstance(Unknown Source)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
> at
> com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2165)
> at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2090)
> at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795)
> at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
> at sun.reflect.GeneratedConstructorAccessor76.newInstance(Unknown Source)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
> at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
> at
> com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
> at
> org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
> at
> org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
> at
> org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:701)
> at
> org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:635)
> at
> org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188)
> at
> org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128)
> at
> org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCTransactionManager.beginTransaction(JDBCTransactionManager.java:80)
> at
> org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.beginTransaction(EmbeddedRegistry.java:443)
> at
> org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.get(EmbeddedRegistry.java:505)
> at
> org.wso2.carbon.registry.core.session.UserRegistry.getInternal(UserRegistry.java:617)
> at
> org.wso2.carbon.registry.core.session.UserRegistry.access$400(UserRegistry.java:61)
> at
> org.wso2.carbon.registry.core.session.UserRegistry$5.run(UserRegistry.java:597)
> at
> org.wso2.carbon.registry.core.session.UserRegistry$5.run(UserRegistry.java:594)
> at java.security.AccessController.doPrivileged(Native Method)
> at
> org.wso2.carbon.registry.core.session.UserRegistry.get(UserRegistry.java:594)
> at
> org.wso2.carbon.registry.core.jdbc.handlers.builtin.MountHandler.get(MountHandler.java:399)
> at
> org.wso2.carbon.registry.core.jdbc.handlers.HandlerManager.get(HandlerManager.java:2444)
> at
> org.wso2.carbon.registry.core.jdbc.handlers.UserDefinedHandlerManager.get(UserDefinedHandlerManager.java:200)
> at
> 

[Dev] [ES] Too many mysql connections when starting ES instances

[Dilini Gunatilake]

Hi All,

I have set up a 4 node cluster for ES 2.1.0 Alpha2 pack with mysql 5.7.
When I started one store node I got an error [3].

Then, I checked the connections in the mysql server and found that the
default number of max_connections (151) have been exceeded while the store
node is starting. But initially without starting any ES instances there
were only around 20 connections. So, what we observed was when starting ES
instances substantially high amount of connections will be created and they
will be gradually reduced after sometime. Is this an acceptable behavior?

Also, we could find the recommended max_connections to configure in some
products [1][2], but couldn't find anything for ES. So, what is the
recommended value for ES?
For the time being we will increase the max connections to 300 and proceed
with testing.

Your earliest response is highly appreciated.

[1] https://docs.wso2.com/display/AM200/Tuning+Performance
[2]
https://docs.wso2.com/display/Governance510/Performance+Tuning+Recommendations

[3]

[2016-07-13 07:50:40,014] ERROR
{org.wso2.carbon.registry.core.dataaccess.TransactionManager} -  Failed to
start new registry transaction.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could
not create connection to database server. Attempted reconnect 3 times.
Giving up.
at sun.reflect.GeneratedConstructorAccessor107.newInstance(Unknown Source)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at
com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2165)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2090)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
at sun.reflect.GeneratedConstructorAccessor76.newInstance(Unknown Source)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
at
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
at
org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
at
org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
at
org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:701)
at
org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:635)
at
org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188)
at
org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128)
at
org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCTransactionManager.beginTransaction(JDBCTransactionManager.java:80)
at
org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.beginTransaction(EmbeddedRegistry.java:443)
at
org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.get(EmbeddedRegistry.java:505)
at
org.wso2.carbon.registry.core.session.UserRegistry.getInternal(UserRegistry.java:617)
at
org.wso2.carbon.registry.core.session.UserRegistry.access$400(UserRegistry.java:61)
at
org.wso2.carbon.registry.core.session.UserRegistry$5.run(UserRegistry.java:597)
at
org.wso2.carbon.registry.core.session.UserRegistry$5.run(UserRegistry.java:594)
at java.security.AccessController.doPrivileged(Native Method)
at
org.wso2.carbon.registry.core.session.UserRegistry.get(UserRegistry.java:594)
at
org.wso2.carbon.registry.core.jdbc.handlers.builtin.MountHandler.get(MountHandler.java:399)
at
org.wso2.carbon.registry.core.jdbc.handlers.HandlerManager.get(HandlerManager.java:2444)
at
org.wso2.carbon.registry.core.jdbc.handlers.UserDefinedHandlerManager.get(UserDefinedHandlerManager.java:200)
at
org.wso2.carbon.registry.core.jdbc.handlers.HandlerLifecycleManager.get(HandlerLifecycleManager.java:914)
at
org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.get(EmbeddedRegistry.java:512)
at
org.wso2.carbon.registry.core.caching.CacheBackedRegistry.get(CacheBackedRegistry.java:203)
at
org.wso2.carbon.registry.core.session.UserRegistry.getInternal(UserRegistry.java:617)
at
org.wso2.carbon.registry.core.session.UserRegistry.access$400(UserRegistry.java:61)
at
org.wso2.carbon.registry.core.session.UserRegistry$5.run(UserRegistry.java:597)
at
org.wso2.carbon.registry.core.session.UserRegistry$5.run(UserRegistry.java:594)
at java.security.AccessController.doPrivileged(Native Method)
at