|
|
| Menu |
This document will describe the uses of parameters used to control the Dynamo database pool. Using these parameters in conjunction should be able to handle the issue of a slow database, a database down, a database that went down and came back up leaving invalid connections in the pool, a table lock, heavy traffic on the application or a slow down in the network. They have been tested in a production environment against Oracle, Sybase and MS SQL-Server.
We have a Dynamo server running over ten different applications. All of these are each using a different Dynamo connection pool. There is a major problem. Every week the number of Dynamo processes increases up to more than 40 and then the server slows to a crawl and Dynamo has to be stopped and restarted to clear things up. Sometimes this has to be done repeatedly until the problem goes away. The problem seems to only be caused by one application or another but we end up bringing down all of the applications because of it. Every time it seems that a different application knocks down Dynamo. Why should one application bring down everything?Once the problem was traced to a slow database and extra traffic on the application and we started seeing thread deaths. At another time one of the application's databases went down and was restarted. Soon after that Dynamo had to be restarted. In another instance a table became locked from a long running index creation on one of the applications.
Symptoms of the problem are thread death messages from the database pool and watching over 40 threads start up on each cpu or DRP. Possible causes were reduced to one of the applications having its database restarted, a slow running database, a table lock, a slow network or heavier than normal traffic.
After gathering statistics on when Dynamo would fail and the causes of the failure it was determined that the problem was with the behavior of the database connection pool. The pool parameters are controlled by the JTDataSource properties file. The following is the result of research into the ATG Dynamo manuals and JavaDoc's on the ResourcePool and MonitoredDataSource classes. Experiments were performed to validate the findings by purposely locking tables and shutting down and restarting the database in the middle of a query.
The parameters for ATG Dynamo are contained in a file with the name of JTDataSource.properties in the file. One can have many of these for each application that runs on the server and point each one at a different database or schema.Below are the default settings that are setup in Dynamo.
JTDataSource.properties$class=atg.service.jdbc.MonitoredDataSource
dataSource=/atg/dynamo/service/jdbc/JTDataSourceFakeXA
blocking=true
maxFree=4
min=1
max=10
logDebugStacktrace=true
loggingSQLInfo=false
loggingSQLDebug=false
loggingSQLWarning=true
loggingSQLError=trueParameters that help with database problems, with sample values for one of our applications. Make sure and tune these settings to fit your environment and your particular application.
blocking=false
queryTimeout=10
creationTimeLimit=10000
maxPendingCreations=4
loginTimeout=13
checkoutBlockTime=13000
blocking
It is recommended that blocking be set as false. Or the checkoutBlockTime should be set to a value if blocking is set to true. This parameter is used when the database pool fills up. When it fills up the choice is to let everyone else wait for a connection or throw an exception.Benefits of blocking = false:
Risks:
- prevents threads from waiting for a thread death at 5 minutes or whatever time your server is set to for thread death.
- prevents Dynamo drp's from reaching a standstill at 40+ threads
- if pool is not large enough users will get exceptions
- if exceptions are not handled properly in the code there could be additional problems
queryTimeout
Set in seconds and it applies to each query.
This will cause all queries that use the connection pool to throw an exception if they run longer than the time specified. The application code can then handle the exception. If you let a query sit too long you end up with a thread sitting out there just waiting. If the connection is bad because the database went down your query thread will just sit there waiting. If too many threads sit waiting it will bring Dynamo to a stand still. If this is not set the thread will run until Dynamo kills it with at thread death. Thread Death is a global setting and it is set at about five minutes.Benefits:
- queries will timeout allowing resources to be freed
Risks:
- as data in database grows or if indexes are not analyzed over time users could get exceptions when the query takes longer than the parameter specifies
For the application tested we timed our queries and found that the users typically will only wait up to 1 minute before calling for support. The application uses 6 - 7 queries before it returns results so the queryTimeout was set to 10 seconds. Later this application was converted to use SQL-Server over a slow wide area network and the timeout had to be increased to prevent queries from timing out.
If an exception happens Dynamo is supposed to take that connection and remove it from the pool.
Exception:
java.sql.SQLException: JZ006: Caught IOException: java.io.InterruptedIOException: Read timed out
java.sql.SQLException: JZ006: Caught IOException: Read timed outIn Oracle you may get this one instead:
java.lang.Exception: java.sql.SQLException: ORA-01013: user requested cancel of current operation at . . .Supporting documentation:
http://www.atg.com/repositories/ContentCatalogRepository_en/manuals/D5.1/pguide/pguide2705.html
Subject: case #49001 - unresponsive databaseDear Mr. Johnson: I am taking ownership of this support case.
You wrote:
> The manual makes it sound like these parameters are designed to be used with an unresponsive database. Well, yes, but these parameters only apply when new connections are being created. If the pool is already fully populated with connections, then this won't help.
There is the 'queryTimeout' parameter of the JTDataSource component that might be more to the point. You can force queries to timeout which will cause an exception to be thrown and the connection object to become invalidated. After the connections in the pool have been depleted, the pool will attempt to recreate them, which will bring 'creationTimeLimit', etc. back into relevance.
Please also see the section titled "Monitoring Connection Activity" in Chapter 24 of the Dynamo Programmer's Guide. That whole chapter is brief and addresses your subject of interest.
Respectfully,
atg Dynamo technical support
creationTimeLimit
Set in milliseconds.
This one is used upon initial creation of a pool trying to make connections. If the creation of the pool fails because it cannot get to the database it will put the pool into a disabled state. This disabled state allows only one thread to try and get a connection to the database at a time. The rest of the threads just wait depending on the values of the parameters blocking and checkoutBlockTime. If blocking is set to false the threads will not wait, they will just get an exception. If checkoutBlockTime is set it will wait the amount of time set in that parameter before throwing an exception.Benefits:
- it limits the number of threads that can attempt to access the pool if the database is not responding
- keeps threads from stacking up
Risks:
- if set to low it would cause problems getting database access
Exception:
java.sql.SQLException: atg.service.resourcepool.ResourcePoolException: atg.service.resourcepool.ResourcePoolResources->creationTimeout : pool may be dead : maximum resource creation time has been reachedSupporting Documentation:
http://www.atg.com/repositories/ContentCatalogRepository_en/manuals/D5.1/pguide/pguide2704.htmlmaxPendingCreations
This sets the number of connection creations. If not set the default is unlimited. If connection requests start stacking up because the database is not allowing connections this will disable the pool.
Dynamo Dcumentation:
A common cause of performance problems is when request handling threads get hung up waiting for a database connection from a database that has become unresponsive. Since the MonitoredDataSource extends the atg.service.resourcepool.ResourcePool class, it inherits the ResourcePool's properties that handle these situations. The creationTimeLimit and maxPendingCreations properties can disable a ResourcePool if the pool is unable to create new connections. See ResourcePool: Checking the Health of a ResourcePool in the Dynamo Foundation Classes chapter for more information about these properties. -http://www.atg.com/repositories/ContentCatalogRepository_en/manuals/D5.1/apidoc/atg/service/resourcepool/ResourcePool.html
loginTimeout and checkoutBlockTime
loginTimeout set in seconds
checkoutBlockTime set in millisecondsBoth of these do the same thing. The Dynamo documentation says that they both set the same value.
This one sets the amount of time the pool will wait to get a valid connection to the database. It also sets the amount of time the pool will block a query if there is not an open connection to use. So if you have a disabled pool the threads will wait that amount of time and then throw an exception.
Be careful about setting your max parameter. If max is too low for the number of users you will cause them to get exceptions.
Exception:
java.lang.Exception: java.sql.SQLException: atg.service.resourcepool.ResourcePoolException: atg.service.resourcepool.ResourcePoolResources->blockTimeout : checkout block has timed outDocumentation:
http://www.atg.com/repositories/ContentCatalogRepository_en/manuals/D5.1/apidoc/atg/service/jdbc/MonitoredDataSource.html http://www.atg.com/repositories/ContentCatalogRepository_en/manuals/D5.1/apidoc/atg/service/resourcepool/ResourcePool.html
Subject: case #49001 - blocking connectionsDear Mr. Johnson:
You wrote:
> When a pool is disabled do the threads that want to use it just get queued up or is an exception thrown?
You have your MonitoredDataSource's 'blocking' property set to 'true', so the threads that need a connection and can't get one will block. If you set this to 'false', then an exception would be thrown immediately if a connection was requested from the pool and no connection is available.
There is another intermediate possibility between the binary states of 'blocking'. There is another property named 'checkoutBlockTime' that will allow a thread to wait for a free connection some finite amount of time, and then throw an exception. I think that you can relieve your hung threads by adjusting the value of this property.
Database going down:
The database goes down. The queries that were in the middle of running get a query time out error and their connections are deleted from the pool. The pool tries to make new connections. They get a creation time out exception, which disables the pool and disables all of the invalid connections. Now only one query runs, waiting the time in the checkoutBlockTime and then throws an exception. One query at time is allowed to try and make a connection and gets a creation time out.
Now the database comes back up. One query is allowed to try the connection pool. It makes a connection successfully. The pool is now enabled. The pool fills back up to the number of requested connections and everything starts working again.
Table lock or slowness:
Threads start filling up the pool with running queries. When the pool is full the other threads wait for the checkoutBlockTime and then throw an exception. The queries in the pool run until the queryTimeout setting and then throw an exception. After they timeout, another request starts a query and it runs until the timeout. Users of this individual application will not be able to do anything involving a database query. But the errors will be localized to that single application and not effect other applications running on the server.
|
|
| Menu |