Topic: Delay in connections (II)

Delay in connections (II)

1 posts in topic

Flat View

Thread View

Older Post First | Reply to Topic

TOPIC ACTIONS:

Print Topic

Posted By: Anonymous
Posted On: Thursday, November 15, 2001 02:58 AM

We've got a problem regarding access to database with Apache JServ 1.1.2 + GNUJSP 1.0.0 + Oracle 8.1.5. We start four instances of Jserv simultaneously in the same server (PIIIx2, 1 Gb RAM). At the beginning, each Servlet/JSP opened its connection with database, but we saw that when it gets up to 400 Apache instances, there are delays of several minutes to obtain the connection, when calling the DriverManager.getConnection. However, if we open a SQLPlus session, even from the same machine, time to response is null.

We decided to activate a pool of connections, in one section of our site using OracleConnectionCacheImpl with a Dynamic schema, so each one of the JServs has its own pool. Each pool is managed by a servlet that initialised when its JServ starts. We observe that most time the pool works properly, serving pages with no delays. But, very often, apparently without a logical sequence(hour, number of accesses, server load or similar), the pool receives demands for connections and the pool seems to block or queue them, until a few minutes later (sometimes even one hour), looks to accept all connections at the same time and the database is collapsed (Database configuration admitts up to 200 sessions). The traces that we've included in one of pages show this:

			

			[Sun Nov 11 01:22:50 CET 2001]
			<8131787> Pool: Obteniendo pool...
			(JSP looks for pools Servlet del pool in its context)
			

			[Sun Nov 11 01:22:50 CET 2001]
			Pool: getConnection...
			(Servlet calls to its method getConnection)
			

			[Sun Nov 11 01:22:50 CET 2001]
			ConnectionPool: getConnection
			(Call to getConnection method of OracleConnectionCacheImpl)
			

			[Sun Nov 11 01:22:50 CET 2001]
			ConnectionPool: getActiveSize: 1
			(pool size)
			

			[Sun Nov 11 01:22:50 CET 2001]
			Pool:
			Connection OK (JSP recives one connection of the pool)
			

			[Sun Nov 11 01:23:34 CET 2001]
			<5462765> Pool: Obteniendo pool...
			

			[Sun Nov 11 01:23:34 CET 2001]
			Pool: getConnection...
			

			[Sun Nov 11 01:23:34 CET 2001]
			ConnectionPool: getConnection
			

			[Sun Nov 11 01:23:34 CET 2001]
			ConnectionPool: getActiveSize: 1
			

			[Sun Nov 11 01:23:34 CET 2001]
			Pool: Conection obtenida
			

			[Sun Nov 11 01:23:35 CET 2001]
			<8131788> Pool: Obteniendo pool...
			

			[Sun Nov 11 01:23:35 CET 2001]
			Pool: getConnection...
			

			[Sun Nov 11 01:23:35 CET 2001]
			ConnectionPool: getConnection
			

			[Sun Nov 11 01:24:07 CET 2001]
			<8131789> Pool: Obteniendo pool...
			

			[Sun Nov 11 01:24:07 CET 2001]
			Pool: getConnection...
			

			[Sun Nov 11 01:24:07 CET 2001]
			ConnectionPool: getConnection
			

			[Sun Nov 11 01:24:31 CET 2001]
			<9779709> Pool: Obteniendo pool...
			

			[Sun Nov 11 01:24:31 CET 2001]
			Pool: getConnection...
			

			[Sun Nov 11 01:24:31 CET 2001]
			ConnectionPool: getConnection
			

			[Sun Nov 11 01:24:32 CET 2001]
			<5545111> Pool: Obteniendo pool...
			

			....
			

			....
			

			....
			

			[Sun Nov 11 01:47:34 CET 2001]
			Pool: Conection obtenida
			

			[Sun Nov 11 01:47:41 CET 2001]
			ConnectionPool: getActiveSize: 36
			

			[Sun Nov 11 01:47:41 CET 2001]
			Pool: Conection obtenida
			

			[Sun Nov 11 01:47:41 CET 2001]
			ConnectionPool: getActiveSize: 36
			

			[Sun Nov 11 01:47:41 CET 2001]
			Pool: Conection obtenida
			

			[Sun Nov 11 01:47:41 CET 2001]
			ConnectionPool: getActiveSize: 36
			

			[Sun Nov 11 01:47:41 CET 2001]
			Pool: Conection obtenida
			

			[Sun Nov 11 01:47:41 CET 2001]
			ConnectionPool: getActiveSize: 36

Any suggestion to this matter? We would very much appreciated your help.

<<Less

Report | Quote This | Send private message | Reply | Print

Re: Delay in connections (II)

Posted By: Bernie_Acs
Posted On: Friday, November 16, 2001 09:47 AM

One possible diagnostic would include using the setLogWriter() of the DataSource or ConnectionCache to produce a very detailed and verbose output stream of what is happening during the operation of these objects. (Be aware there can be a lot of information dumped by these objects).

My first inclination is to think that some factors related to the host may be contributing to the problem for instance Host VM handling ( disk swaps,paging, or user resource limits ) which could certainly produce some of the symptoms you have describe and allow them to materialize in a seemingly random pattern.

I have run a test creating more than 2000 concurrent connections to preform a set of relatively trival database queries and could not produce anything more than bottlenecking issues which certainly manifest themselves as delays in creating a connections, host memory system swapping/paging, increased rollback activity in db and a sigificant increase of time to preform queries. In my test each thread was infact preforming exactly the same set of operations, the delay between thread starts was minimal, they all started within a single second, some finished in about 2 seconds while some took as long as 5 minutes to complete the same operation. (the task would normally complete in about 2 seconds under normal cirrcumstances) With the LoginTimeout() value set high enough they all completed without failure. The system's strain and the dbs strain where also obviously reflected in monitoring tools showing that a significant IO was causing some thrashing of disk and user memory. Adjustments in SGA size of DB, number of processes and sessions influenced this behavior by reducing the high end of the over all completion time. The size of the SGA defined for the DB seemed to have the greatest impact on this, the high-end was reduced to about 2 minutes in this scenerio, but the SGA setting when from 32M to 256M which was all that was available in the testing enviroment.

Monitoring of some of the db instance attributes like rollback segment growth and shrinkage ( used by read queries for managing time consistant data images), redo log activity ( is db in archive mode, if so how often are arch-logs created ,what is time requirement for this operation and the impact on other operation pending on db ), db memory statistics during normal operation under a load may help to shed light on what to look into as the cause of the problem and possible remedies.

Report | Quote This | Send private message | Reply | Print