Yes, it's definitely better to not leave connections open longer than necessary.
Although 100 is too low a limit; we'll have to get that increased. Looks like it did
max out at 100 connections to the server the conditions db is running on around
midnight, suggesting it was just the 100 HPS jobs:
https://cc.jlab.org/systems/ganglia2/?r=day&cs=&ce=&c=Database+Cluster&h=jmysql2013.jlab.org&tab=m&vn=&mc=2&z=small&metric_group=ALLGROUPS
Was the HPS code just inactively sitting on those 100 connections (looks like maybe
it was based on the CPU plots)? Is it ready to deal with a dropped connection and
reconnect? If so, we can have the server/firewall drop inactive connections to
alleviate the problem too.
-Nathan
On Dec 26, 2014, at 6:30 AM, Maurik Holtrop <[log in to unmask]> wrote:
> Hi Jeremy,
>
> I think that closing the connection after the information is retrieved would be the right course of action. I recall a similar think needed to be done for the CLAS software. The database at Jlab is also used by other experiments, so we need to keep that in account as well.
> If we close the connection after the information is retrieved, and then later data needs to be stored or more needs to be retrieved, it should be easy to re-connect.
>
> Best,
> Maurik
>
> On Dec 26, 2014, at 7:08 AM, McCormick, Jeremy I. <[log in to unmask]> wrote:
>
>> I believe that should be tunable on the MySQL db via max_connections setting, but would need to talk to the admin about changing it. Around 100 is probably the default. How many simultaneous connections would we need?
>>
>> Also, it should be possible to close the connection in the db manager after conditions setup is done or make this an optional part of the configuration.
>>
>> On Dec 25, 2014, at 11:48 PM, Graham, Mathew Thomas <[log in to unmask]> wrote:
>>
>>>
>>> We’re getting a "Too many connections” error when running production…there are only ~100 jobs running. Is this a real limit or just a parameters we can increase (or something else).
>>>
>>> Thanks, Matt
>>>
>>>
>>> Fri Dec 26 00:41:28 EST 2014 :: DatabaseConditionsManager :: CONFIG :: host jmysql.jlab.org
>>> Fri Dec 26 00:41:28 EST 2014 :: DatabaseConditionsManager :: CONFIG :: port 3306
>>> Fri Dec 26 00:41:28 EST 2014 :: DatabaseConditionsManager :: CONFIG :: user hpsuser
>>> Fri Dec 26 00:41:28 EST 2014 :: DatabaseConditionsManager :: CONFIG :: database hps_conditions
>>> Exception in thread "main" java.lang.RuntimeException: Failed to connect to database: jdbc:mysql://jmysql.jlab.org:3306/
>>> at org.hps.conditions.database.ConnectionParameters.createConnection(ConnectionParameters.java:122)
>>> at org.hps.conditions.database.DatabaseConditionsManager.openConnection(DatabaseConditionsManager.java:150)
>>> at org.hps.conditions.database.DatabaseConditionsManager.initialize(DatabaseConditionsManager.java:293)
>>> at org.hps.conditions.database.DatabaseConditionsManager.setDetector(DatabaseConditionsManager.java:240)
>>> at org.hps.evio.EvioToLcio.run(EvioToLcio.java:272)
>>> at org.hps.evio.EvioToLcio.main(EvioToLcio.java:99)
>>> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
>>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
>>> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
>>> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>>> at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
>>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
>>> at com.mysql.jdbc.Util.getInstance(Util.java:386)
>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
>>> at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1112)
>>> at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486)
>>> at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519)
>>> at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)
>>> at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
>>> at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
>>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
>>> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
>>> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>>> at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
>>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
>>> at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
>>> at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
>>> at java.sql.DriverManager.getConnection(DriverManager.java:579)
>>> at java.sql.DriverManager.getConnection(DriverManager.java:190)
>>> at org.hps.conditions.database.ConnectionParameters.createConnection(ConnectionParameters.java:119)
>>>
>>
>> Use REPLY-ALL to reply to list
>>
>> To unsubscribe from the HPS-SOFTWARE list, click the following link:
>> https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=HPS-SOFTWARE&A=1
>>
>
>
> Use REPLY-ALL to reply to list
>
> To unsubscribe from the HPS-SOFTWARE list, click the following link:
> https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=HPS-SOFTWARE&A=1
>
########################################################################
Use REPLY-ALL to reply to list
To unsubscribe from the HPS-SOFTWARE list, click the following link:
https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=HPS-SOFTWARE&A=1
|