Menu
▾
▴
firebird-net-provider
|
From: Alessandro P. <pet...@in...> - 2004-09-08 23:03:23
|
Hola Carlos, I've got an asp.net application that is usually used by 40 users. I see there are 5 connections in use. Now, due to heavy load or batch processing, the db server starts running slowly and so more connections are created to satisfy my 40 users, let's say at this point I have 10 connections. After a while the db server gets back his full power. At this point I expect that the additionally pooled connections should be released after they are expired but my tests shows that this is not true... it seems they are someway used until all the users stops working and pool expires... Any idea? I reviewed the MaxPoolSize parameters but it doesn't fit my needs.. shouldn't this queue the connection-open request until one of the pooled connection will be available, instead of opening a non-pooled one? Thanks in advance. Ciao, Alessandro Petrelli. |
|
From: <mik...@my...> - 2004-09-09 04:13:09
|
By chance can you leave the "FIELD_TYPE" column in for Column and Paramater types? We use this field and end up having to write our own stuff because you remove it? It's okay with me if the answer is no, just a request, that's all. |
|
From:
<car...@te...> - 2004-09-09 07:05:19
|
Hello: >By chance can you leave the "FIELD_TYPE" column in for Column and >Paramater types? > In first place no. >We use this field and end up having to write our own >stuff because you remove it? > Hope you can do the same using the *_DATA_TYPE columns that holds the data type name and doing a search in the result (DataTable) of the DataTypes schema. What is what you can't do this way ?? -- Best regards Carlos Guzmán Álvarez Vigo-Spain One ring to rule them all (The lord of the rings - J.R.R.Tolkien) |
|
From: <mik...@my...> - 2004-09-09 14:16:51
|
I will look at it, but your DATA_TYPE doesn't truly map back to the data type of the column in the TABLE. Using the "FIELD_TYPE" we are able to map things back exactly as they appear say in Database Workbench. Your data types are massaged a little, ie, things going to Decimal or Numeric when they aren't really defined that way. > Hello: > >>By chance can you leave the "FIELD_TYPE" column in for Column and >>Paramater types? >> > In first place no. > >>We use this field and end up having to write our own >>stuff because you remove it? >> > Hope you can do the same using the *_DATA_TYPE columns that > holds the data type name and doing a search in the result > (DataTable) of the DataTypes schema. What is what you can't > do this way ?? > > > -- > Best regards > > Carlos Guzmán Álvarez > Vigo-Spain > > One ring to rule them all > (The lord of the rings - J.R.R.Tolkien) > > |
|
From:
<car...@te...> - 2004-09-09 14:22:39
|
Hello: >I will look at it, but your DATA_TYPE doesn't truly map back to the data >type of the column in the TABLE. Using the "FIELD_TYPE" we are able to >map things back exactly as they appear say in Database Workbench. Your >data types are massaged a little, ie, things going to Decimal or Numeric >when they aren't really defined that way. > Can you send a sample ?? -- Best regards Carlos Guzmán Álvarez Vigo-Spain One ring to rule them all (The lord of the rings - J.R.R.Tolkien) |
|
From:
<car...@te...> - 2004-09-09 12:42:48
|
Hello: >At this point I expect that the additionally pooled connections should be released after they are expired but my tests shows that this is not true... it seems they are someway used until all the users stops working and pool expires... > Huummm Can you send a sample of connection string ?? With that i will try to run a test and see if they are being released correctly,maybe it will be nice to add something for see how many pools are opened and how many connections are opened for each pool or for the pool of a specific connection instance, opinions are welcome here :) >I reviewed the MaxPoolSize parameters but it doesn't fit my needs.. >shouldn't this queue the connection-open request until one of the pooled >connection will be available, instead of opening a non-pooled one? > I don't think so. -- Best regards Carlos Guzmán Álvarez Vigo-Spain One ring to rule them all (The lord of the rings - J.R.R.Tolkien) |
|
From: Wendell W. <wjw...@a-...> - 2004-09-09 15:03:58
|
If I understand, the problem is that extra connections greater than MinPoolSize are not expiring after the user load decreases. A solution would be to change FbConnectionPool.CheckOut() to always select the connection with the greatest remaining lifetime, so that connections which are about to expire are left alone long enough to actually expire and be removed from the pool before they are reused and have their Created time reset. Sorry if I have misunderstood something ... - Wendell |
|
From:
<car...@te...> - 2004-09-09 15:19:53
|
Hello: >If I understand, the problem is that extra connections greater than >MinPoolSize are not expiring after the user load decreases. > >A solution would be to change FbConnectionPool.CheckOut() to always select >the connection with the greatest remaining lifetime, so that connections >which are about to expire are left alone long enough to actually expire and >be removed from the pool before they are reused and have their Created time >reset. > Huummmm maybe but given he's connection string, it's no using the MinPoolSize facility >Sorry if I have misunderstood something ... > All comments and ideas are more than welcome !!!! :-D -- Best regards Carlos Guzmán Álvarez Vigo-Spain One ring to rule them all (The lord of the rings - J.R.R.Tolkien) |
|
From: Wendell W. <wjw...@a-...> - 2004-09-09 15:57:53
|
> >If I understand, the problem is that extra connections greater than > >MinPoolSize are not expiring after the user load decreases. > > > >A solution would be to change FbConnectionPool.CheckOut() to always select > >the connection with the greatest remaining lifetime, so that connections > >which are about to expire are left alone long enough to actually expire and > >be removed from the pool before they are reused and have their Created time > >reset. > > > Huummmm maybe but given he's connection string, it's no using > the MinPoolSize facility >"Server=127.0.0.1;Database=d:\db\MYDB.FDB;User=MYUSER;Password=MYPWD;Dialec t >=3;Charset=ISO8859_1;Pooling=true;Connection Lifetime=600;PacketSize=16384" Right, so the pool should shrink back to a size of 0 as quickly as possible because there is no MinPoolSize. In Alessandro's example: * pool starts out at size of 0, * regular user load creates and maintains 5 connections in use, * temporary load raises pool to 10 connections, * return to regular load only requires 5 connections, but all 10 are being used randomly, so none ever expire, * eventually all users go home for the night, and the entire pool goes unused long enough for all of the connections to expire, and pool size returns to 0. If CheckOut() always returns the most recently used available connection (longest remaining lifetime), then connections which are no longer needed will be allowed to expire and the pool will reduce back down to the minimum size required to support the current user load. Unless I've missed something, I think this is exactly the problem. - Wendell |
|
From:
<car...@te...> - 2004-09-09 16:22:22
|
Hello: >If CheckOut() always returns the most recently used available connection >(longest remaining lifetime), then connections which are no longer needed >will be allowed to expire and the pool will reduce back down to the minimum >size required to support the current user load. > >Unless I've missed something, I think this is exactly the problem. > Huummm correct i think that could be something to review. Another thing to review can be the frequency the cleanup theread is fired .. now it's using the lifetime, but maybe that isn't fully correct. -- Best regards Carlos Guzmán Álvarez Vigo-Spain One ring to rule them all (The lord of the rings - J.R.R.Tolkien) |
|
From:
<car...@te...> - 2004-09-10 09:37:15
|
Hello: > Huummm correct i think that could be something to review. I have an initial implementation for this in my localtree (1.7), hope i have understood you ok :) we will know it when i commit the changve into the CVS :) > Another thing to review can be the frequency the > cleanup theread is fired .. now it's using the lifetime, > but maybe that isn't fully correct. Huummm i'm thinking on launch the cleanup using lifetime/2 when the lifetime is > 1 minute, opinions ?? -- Best regards Carlos Guzmán Álvarez Vigo-Spain Three Rings for the Elven-Kings under the sky, Seven for the Dwarf-lords in their halls of stone, Nine for Mortal Men doomed to die, One for the Dark Lord on his dark throne In the land of Mordor where the Shadows lie. One ring to rule them all. One ring to find them, One ring to bring them all And in the Darkness bind them In the land of mordor where the Shadows lies. (The lord of the rings - J.R.R.Tolkien) |
|
From: Alessandro P. <pet...@in...> - 2004-09-10 09:09:22
|
""Wendell Wilkie"" <wjw...@a-...> ha scritto nel messaggio news:chps42$b31$1...@ne...... Hello Wendell, > In Alessandro's example: > * pool starts out at size of 0, > * regular user load creates and maintains 5 connections in use, > * temporary load raises pool to 10 connections, > * return to regular load only requires 5 connections, but all 10 are being > used randomly, so none ever expire, > * eventually all users go home for the night, and the entire pool goes > unused long enough for all of the connections to expire, and pool size > returns to 0. > > If CheckOut() always returns the most recently used available connection > (longest remaining lifetime), then connections which are no longer needed > will be allowed to expire and the pool will reduce back down to the > minimum > size required to support the current user load. You got the point. We have a lot of intranet users (~800, from 8am to 10pm) and sometimes I can count about 250 and more active connections instead of the usual 30-40... heavy-load usually occurs for no more of 10 minutes and once per day and so this bunch of active connections is really unneeded... ;) Ciao, Alessandro Petrelli. |
|
From:
<car...@te...> - 2004-09-10 09:34:41
|
Hello: >You got the point. > >We have a lot of intranet users (~800, from 8am to 10pm) and sometimes I can >count about 250 and more active connections instead of the usual 30-40... >heavy-load usually occurs for no more of 10 minutes and once per day and so >this bunch of active connections is really unneeded... ;) > I have committed an initial implementation for this in the 1.7 sources, hope i have understood well the idea, but if no, let me know :D -- Best regards Carlos Guzmán Álvarez Vigo-Spain Three Rings for the Elven-Kings under the sky, Seven for the Dwarf-lords in their halls of stone, Nine for Mortal Men doomed to die, One for the Dark Lord on his dark throne In the land of Mordor where the Shadows lie. One ring to rule them all. One ring to find them, One ring to bring them all And in the Darkness bind them In the land of mordor where the Shadows lies. (The lord of the rings - J.R.R.Tolkien) |
|
From: Alessandro P. <pet...@in...> - 2004-09-10 21:47:43
|
""Carlos Guzmán Álvarez"" <car...@te...> ha scritto nel messaggio news:414...@te...... > Hello: > >>You got the point. >> >>We have a lot of intranet users (~800, from 8am to 10pm) and sometimes I >>can count about 250 and more active connections instead of the usual >>30-40... heavy-load usually occurs for no more of 10 minutes and once per >>day and so this bunch of active connections is really unneeded... ;) >> > I have committed an initial implementation for this in > the 1.7 sources, hope i have understood well the idea, > but if no, let me know :D Thanks, can I compile it against the 1.6 sources? Ciao, Alessandro Petrelli. |
|
From:
<car...@te...> - 2004-09-11 07:50:08
|
Hello: >Thanks, can I compile it against the 1.6 sources? > It's no in 1.6 yet, but now than 1.6.2 is released i can backport it from 1.7. -- Best regards Carlos Guzmán Álvarez Vigo-Spain One ring to rule them all. One ring to find them, One ring to bring them all And in the Darkness bind them In the land of mordor where the Shadows lies. (The lord of the rings - J.R.R.Tolkien) |
|
From:
<car...@te...> - 2004-09-11 08:01:44
|
Hello: >Thanks, can I compile it against the 1.6 sources? > I backported the change to the 1.6 in my local tree. Can i send to you the FbConnectionPool.cs file ?? If you told me that it works for you i will commit the change into the CVS :) -- Best regards Carlos Guzmán Álvarez Vigo-Spain One ring to rule them all. One ring to find them, One ring to bring them all And in the Darkness bind them In the land of mordor where the Shadows lies. (The lord of the rings - J.R.R.Tolkien) |
|
From: Alessandro P. <pet...@in...> - 2004-09-11 08:15:18
|
""Carlos Guzmán Álvarez"" <car...@te...> ha scritto nel messaggio news:414...@te...... > Hello: > >>Thanks, can I compile it against the 1.6 sources? >> > I backported the change to the 1.6 in my local tree. > > Can i send to you the FbConnectionPool.cs file ?? > If you told me that it works for you i will commit the > change into the CVS :) Sure, thanks. Ciao, Alessandro Petrelli. |
|
From: Alessandro P. <pet...@in...> - 2004-09-17 09:31:04
|
""Alessandro Petrelli"" <pet...@in...> ha scritto nel messaggio news:cho0m0$qfh$1...@ne...... > I reviewed the MaxPoolSize parameters but it doesn't fit my needs.. > shouldn't this queue the connection-open request until one of the pooled > connection will be available, instead of opening a non-pooled one? It seems this is the ms sql way, any clue? I got this explanation googling around: "Setting the MaxPoolSize property causes the request to be queued until a connection becomes available or until the timeout period is reached" Ciao, Alessandro Petrelli. |
|
From:
<car...@te...> - 2004-09-17 09:47:01
|
Hello: >It seems this is the ms sql way, any clue? > >I got this explanation googling around: > >"Setting the MaxPoolSize property causes the request to be queued until a >connection becomes available or until the timeout period is reached" > I was not aware of that in SqlClient, but i will try to take a look for see how to implement this in the provider .... -- Best regards CArlos Guzmán Álvarez Vigo-Spain |
|
From:
<car...@te...> - 2004-09-17 11:01:03
|
Hello: >I got this explanation googling around: > Can you send to me the link ?? :) Thanks in advance. -- Best regards Carlos Guzmán Álvarez Vigo-Spain |
|
From: Alessandro P. <pet...@in...> - 2004-09-17 11:22:20
|
> Hello: > > >I got this explanation googling around: > > > Can you send to me the link ?? :) Damn I didn't bookmarked the link! :( I find out it looking around for exams questions practice (I saved it!): --- You create an XML web service that retrieves data from Microsoft SQL Server database. You instantiate a SqlConnection object named Connection and set the Max Pool Size property of the connectionString to 50. All 50 connections are now in use. However, a request for connection number 51 is received. What is the most likely result? A. An exception is immediately thrown. B. The current connection pool is expanded by 50 additional connections. C. A new connection pool is created that has the same maximum number of connections. D. The request is queued until a connection becomes available or until the timeout limit is reached. Correct answer: D. The request is queued until a connection becomes available or until the timeout limit is reached. Explanation: Setting the MaxPoolSize property causes the request to be queued until a connection becomes available or until the timeout period is reached. --- And in fact googling in ngs (http://groups.google.it/groups?sourceid=navclient&hl=it&ie=UTF-8&q=sqlconne ction++Max+Pool+Size) there are a lot of messages... HTH Ciao, Alessandro Petrelli. |
|
From:
<car...@te...> - 2004-09-17 12:55:51
|
Hello: >Damn I didn't bookmarked the link! :( > :) >And in fact googling in ngs >(http://groups.google.it/groups?sourceid=navclient&hl=it&ie=UTF-8&q=sqlconne >ction++Max+Pool+Size) there are a lot of messages... > Thanks, i have something thinking for this, i need to implement it and test if it's the way to go :) -- Best regards Carlos Guzmán Álvarez Vigo-Spain |
|
From: Alessandro P. <pet...@in...> - 2004-09-17 13:30:47
|
""Carlos Guzmán Álvarez"" <car...@te...> ha scritto nel messaggio news:414...@te...... > Thanks, i have something thinking for this, i need to implement it and > test > if it's the way to go :) I have to restart this thread due to the connection pooling problem not completely gone :( Now the connections get correctly pooled but I have again the problem of the not expired one. Seems that the newly created connections, which are not used anymore, won't expire until the whole pool expires... If I understood code correctly I can see the following situation, let's name connections with letters: I have A and B which are serving my users: A B now A is in use: B now B is in use: . now B returns to the pool: B and so does A: B A now A and B are both in use and C gets created: <empty> now C is returned to the pool: C A, B are returned to the pool: C A B but I would expect: A B C so if the list is not ordered someway by the creation date C will take place first, and so on the others... Am I wrong? Ideas? Carlos, don't hate me :) Ciao, Alessandro Petrelli. |
|
From:
<car...@te...> - 2004-09-17 14:05:38
|
Hello: >so if the list is not ordered someway by the creation date C will take place first, and so on the others... > >Am I wrong? Ideas? > Huummmmmmm ... now the GetConnection method should be searching for the connection with the higher remaining life time, the list is not ordered but anyway it's comparing the remaining lifetime of each connection with the actual selected one, so it should be selecting always the one with the higher remaining lifetime (maybe i have understood bad the Wendell Wilkie suggestion :P) Comments ¿?¿?¿?¿? :) >Carlos, don't hate me :) > No :) I think it's important to left this clear, and your help on this is very appreciated :) -- Best regards Carlos Guzmán Álvarez Vigo-Spain |
|
From: Alessandro P. <pet...@in...> - 2004-09-17 15:21:15
|
""Carlos Guzmán Álvarez"" <car...@te...> ha scritto nel messaggio news:414...@te...... > Huummmmmmm ... now the GetConnection method should be searching > for the connection with the higher remaining life time, the list is > not ordered but anyway it's comparing the remaining lifetime of each > connection with the actual selected one, so it should be selecting always > the one with the higher remaining lifetime (maybe i have understood bad > the Wendell Wilkie suggestion :P) > > Comments ¿?¿?¿?¿? :) Just tested again with Microsoft Application Center Test. I run a first load test with 5 concurrent users and I see 5 connections. Then I start using the app normally, as a single user... well the other connections are all being used! Ciao, Alessandro Petrelli. |
Thanks for helping keep SourceForge clean.
X