RE: Connection pooling - why bother?
D'oh. *And* he knows I was silly enough to set all my usernames and passwords to "xxx". Wait - now *everyone* knows! Jaime > -Original Message- > From: Brad Wood [mailto:b...@bradwood.com] > Sent: Thursday, 5 February 2009 3:33 PM > To: cf-talk > Subject: Re: Connection pooling - why bother? > > > Oh man, you fell for it. Now Jochem has all your datasource > passwords. :) > > His plan for total world annihilation is one step closer now... > > ~Brad > > - Original Message - > From: "Jaime Metcher" > > > > Sent to you off-list. Thanks for having a look. > > > > Jaime > >> Could you grab the full settings from neo-datasource.xml so we can > >> see what is configured there? > >> > >> Jochem > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318920 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Connection pooling - why bother?
Oh man, you fell for it. Now Jochem has all your datasource passwords. :) His plan for total world annihilation is one step closer now... ~Brad - Original Message - From: "Jaime Metcher" > > Sent to you off-list. Thanks for having a look. > > Jaime >> Could you grab the full settings from neo-datasource.xml so >> we can see what is configured there? >> >> Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318919 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Connection pooling - why bother?
Jochem, Sent to you off-list. Thanks for having a look. Jaime > -Original Message- > From: Jochem van Dieten [mailto:joch...@gmail.com] > Sent: Wednesday, 4 February 2009 10:19 PM > To: cf-talk > Subject: Re: Connection pooling - why bother? > > > On Tue, Feb 3, 2009 at 11:46 PM, Jaime Metcher wrote: > > And for the CF datasource: > > > class="coldfusion.server.ServiceFactory" > > factory-method="getDataSourceService"/> > > factory-bean="CFDataSourceService" > > factory-method="getDatasource"> > > value="xxx"/> > > > > > > And then I go into the CF admin and turn "maintain > connections" on or off. > > Could you grab the full settings from neo-datasource.xml so > we can see what is configured there? > > Jochem > > -- > > Jochem van Dieten > http://jochem.vandieten.net/ > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318912 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Connection pooling - why bother?
On Tue, Feb 3, 2009 at 11:46 PM, Jaime Metcher wrote: > And for the CF datasource: > class="coldfusion.server.ServiceFactory" > factory-method="getDataSourceService"/> > factory-method="getDatasource"> > > > > And then I go into the CF admin and turn "maintain connections" on or off. Could you grab the full settings from neo-datasource.xml so we can see what is configured there? Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318843 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Connection pooling - why bother?
@Cutter: thanks for the info - that's what I would have expected, so good to know. @Jochem: The config for the Spring provider looks like this: net.sourceforge.jtds.jdbc.Driver jdbc:jtds:sqlserver://xxx.xxx.xxx.xxx:1433/college_test xxx xxx And for the CF datasource: And then I go into the CF admin and turn "maintain connections" on or off. This is a clean CF8.01 developer install, so pool settings would be default (but not sure where to check that). Jaime > -Original Message- > From: Jochem van Dieten [mailto:joch...@gmail.com] > Sent: Wednesday, 4 February 2009 5:00 AM > To: cf-talk > Subject: Re: Connection pooling - why bother? > > > On Fri, Jan 30, 2009 at 10:12 AM, Jaime Metcher wrote: > > Just wondering if anyone has benchmarked CF's connection > pooling. I'm > > getting results suggesting that turning on "maintain > connections" does > > basically nothing for performance, and that using an alternative > > unpooled connection provider (in this case Spring's > > DriverManagerDataSource with > > jTDS) is way faster. > > > However, testing on CF8.01 with 10 simultaneous requests, > I'm getting > > about 50% better throughput with Spring's setup than going > via CF's datasources. > > How did you configure both? Can you provide full JDBC URLs > and pool settings? > > Jochem > > > -- > > Jochem van Dieten > http://jochem.vandieten.net/ > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318793 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Connection pooling - why bother?
On Fri, Jan 30, 2009 at 10:12 AM, Jaime Metcher wrote: > Just wondering if anyone has benchmarked CF's connection pooling. I'm > getting results suggesting that turning on "maintain connections" does > basically nothing for performance, and that using an alternative unpooled > connection provider (in this case Spring's DriverManagerDataSource with > jTDS) is way faster. > However, testing on CF8.01 with 10 simultaneous requests, I'm getting about > 50% better throughput with Spring's setup than going via CF's datasources. How did you configure both? Can you provide full JDBC URLs and pool settings? Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318773 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Connection pooling - why bother?
Not using connection pooling, in our environment, significantly slow down all process, and increases overhead to an order that eventually will bring a server to a complete non-responsive state. Basically, you end up tying up every available active thread request with long running DB requests, and the inactive thread pool 'overflows', to the point that active requests will complete, but inactive threads never transfer to active status. Steve "Cutter" Blades Adobe Certified Professional Advanced Macromedia ColdFusion MX 7 Developer Co-Author of "Learning Ext JS" http://www.packtpub.com/learning-ext-js/book _ http://blog.cutterscrossing.com Jaime Metcher wrote: > Hey Cutter, > >> in our environment we would be insane not to use connection pooling. > > ...unless it's actually faster to not use connection pooling. So is it? > > Jaime > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318771 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Connection pooling - why bother?
It certainly isn't for us. Connection pooling always gives us quicker queries; this is even more true now that we use dedicated servers, but it was true back when we used shared servers too (we're on Oracle). mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/2/3 Jaime Metcher : > > Hey Cutter, > >> in our environment we would be insane not to use connection pooling. > > ...unless it's actually faster to not use connection pooling. So is it? > > Jaime > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318739 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Connection pooling - why bother?
Hey Cutter, > in our environment we would be insane not to use connection pooling. ...unless it's actually faster to not use connection pooling. So is it? Jaime ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318725 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Connection pooling - why bother?
Good points. It seems to be an ORACLE thing. You can make the Global Temporary table either transaction or session specific using the ON COMMIT clause (DELETE ROWS or PRESERVE ROWS). No, we don't drop tables since there is only one type of temporary table. You just specify how it responds to a commit. We found that we had to use the session specific one in order to make things work with CF. When we chose maintain connection in CF admin, we effectively created one session in ORACLE for all users at once. And a big mess since the GTT was now shared across all the users. Maybe there is some combination of CF settings and code that would allow us to maintain connections and have the GTT be specific to each stored proc call but I haven't found it yet. It is possible I was doing something wrong in the stored proc that is making the transaction type not work properly. I guess I'll have to try and set up some test cases if I can find the time. -- Warren ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318703 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Connection pooling - why bother?
I don't know Oracle, but in SQL Server global temp tables are by design available to all connections. (##table_name) If you were using them, wouldn't you expect them to be shared? On the other hand, regular temp tables (#table_name) are per connection, but isn't it proper to drop them after usage if for no other reason than to not have them laying around later if a connection is reused? ~Brad - Original Message - From: "Craigsell" To: "cf-talk" Sent: Friday, January 30, 2009 10:16 PM Subject: Re: Connection pooling - why bother? > > There is one other thing to consider in using connection pooling. I admit > it is a bit off the beaten path but it bit me pretty good. I use Oracle > stored procedures as much as I can. You cannot use connection pooling if > your stored proc uses Global Temporary Tables. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318701 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Connection pooling - why bother?
There is one other thing to consider in using connection pooling. I admit it is a bit off the beaten path but it bit me pretty good. I use Oracle stored procedures as much as I can. You cannot use connection pooling if your stored proc uses Global Temporary Tables. Since the connection is maintained, the table doesn't clear between calls. We turned on connection pooling in an effort to increase performance and quickly made a huge GTT (and correspondingly bad, voluminous data back to each user) which brought oracle and the web server down. Warren Koch ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318700 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Connection pooling - why bother?
I guess it depends on your application, and the traffic you expect. One of our servers will process 500k db requests in a 4 hour period (according to FusionReactor), so in our environment we would be insane not to use connection pooling. Steve "Cutter" Blades Adobe Certified Professional Advanced Macromedia ColdFusion MX 7 Developer Co-Author of "Learning Ext JS" http://www.packtpub.com/learning-ext-js/book _ http://blog.cutterscrossing.com jaime.metc...@medeserv.com.au (Jaime Metcher) wrote: > Hi all, > > Just wondering if anyone has benchmarked CF's connection pooling. I'm > getting results suggesting that turning on "maintain connections" does > basically nothing for performance, and that using an alternative unpooled > connection provider (in this case Spring's DriverManagerDataSource with > jTDS) is way faster. > > The context to this is that Spring's doc says you shouldn't use their > DriverManagerDataSource for production. Because it doesn't use pooling, > it's not up to the job. So they say. > > However, testing on CF8.01 with 10 simultaneous requests, I'm getting about > 50% better throughput with Spring's setup than going via CF's datasources. > Now, benchmarking is a perilous undertaking, but this is my real-world app > I'm using for testing, so the numbers are certainly meaningful for me. > Seeing Spring's "do not use this - it is a toy" connection provider > outperform CF, and not otherwise display any signs of being a toy, was a bit > of a shock. > > Where I'm really going with this is trying to work out whether > DriverManagerDataSource actually is a toy - I'm no mad performance tweaker, > but a 50% hit is something I can't ignore - but I'll ask that on a Java > list. > > Jaime > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318674 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
