Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint
2 years later ... OK this sounds like the best approach as it lets you scale things out as you grow and actually need more servers (then you just have to manage which database to switch too). The cons of this approach is that most ORM's don't support this. On Fri, Jun 1, 2012 at 2:37 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ahmed, On 5/31/12 9:33 AM, S Ahmed wrote: It would be easier if all databases were hosted by a single instance of MySQL -- then you could use Tomcat-pool's feature of being able to provide credentials when obtaining connections from the pool -- and get the right database. That way, a much smaller number of connections could be maintained with roughly the same semantics. Can you expand on how I could do this? Well, first you have to accept that the same MySQL instance (with perhaps different databases) will be hosting all your data. If that's not okay with you, then you can forget this suggestion (or, maybe it just changes the suggestion slightly because you could always have N databases and maybe 4 MySQL instances when them split across the 4... but then you'd have to figure out which connection pool to grab before getting a connection or you'd never get connected). Next, you'd have to switch to tomcat-pool because commons-dbcp (the default CP in Tomcat) does not support obtaining pooled connections with credentials. Next, you remove the database name from the JDBC URL (or maybe change it to something everyone can access, like the 'test' database or 'information_schema' depending on your version of MySQL). Then, you have your code call DataSource.getConnection(username,password) instead of calling DataSource.getConnection(). This gets you the right credentials for your target database. Finally, make sure you issue a query to set the database for the connection: 'USE databasename;' Then, you set your connection pool to have however many connections you want (100?) and every client (thread) shares those connections with every client database that runs in a particular MySQL instance. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk/JC8wACgkQ9CaO5/Lv0PBEpgCghI3t8gpE+SBSNV/pYjyLqqwq 2hwAoIY8mYqGGG+owxzsFPQ+CFa2cVeL =Fh/Y -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ahmed, On 5/31/12 9:33 AM, S Ahmed wrote: It would be easier if all databases were hosted by a single instance of MySQL -- then you could use Tomcat-pool's feature of being able to provide credentials when obtaining connections from the pool -- and get the right database. That way, a much smaller number of connections could be maintained with roughly the same semantics. Can you expand on how I could do this? Well, first you have to accept that the same MySQL instance (with perhaps different databases) will be hosting all your data. If that's not okay with you, then you can forget this suggestion (or, maybe it just changes the suggestion slightly because you could always have N databases and maybe 4 MySQL instances when them split across the 4... but then you'd have to figure out which connection pool to grab before getting a connection or you'd never get connected). Next, you'd have to switch to tomcat-pool because commons-dbcp (the default CP in Tomcat) does not support obtaining pooled connections with credentials. Next, you remove the database name from the JDBC URL (or maybe change it to something everyone can access, like the 'test' database or 'information_schema' depending on your version of MySQL). Then, you have your code call DataSource.getConnection(username,password) instead of calling DataSource.getConnection(). This gets you the right credentials for your target database. Finally, make sure you issue a query to set the database for the connection: 'USE databasename;' Then, you set your connection pool to have however many connections you want (100?) and every client (thread) shares those connections with every client database that runs in a particular MySQL instance. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk/JC8wACgkQ9CaO5/Lv0PBEpgCghI3t8gpE+SBSNV/pYjyLqqwq 2hwAoIY8mYqGGG+owxzsFPQ+CFa2cVeL =Fh/Y -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint
On 30 May 2012, at 23:19, André Warnier a...@ice-sa.com wrote: On Wed, May 30, 2012 at 10:07 AM, Christopher Schultz ch...@christopherschultz.net wrote: .. If my environment and requirements match yours, you'd need 1 * max_pool_size * 66KiB at peak usage. That's about 640MiB for each connection you want in 10k pools. For a (uniform) max pool size of 4, you'll need more than 2GiB of heap space just for connection pools. Is that acceptable? Taking the same hypothetical case and figures : Assuming that you need a total of (1 * 4 connections) = 4 connections. File handles anyone? Web connections n x 10k + db connections m x 10k = ? Assuming that it takes 10ms to set up one such connection, and that once it is there, you don't do anything with it and just let it be for now. Assuming we ignore such things like bandwidth, other things happening on that host etc.. It then takes a total of (10 ms * 4) = 40 ms = 400s = ~ 6.5 minutes just to set up these connections. Is that acceptable ? What I mean is that once you start playing with such numbers, you may want to look at other aspects than just required memory.. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint
On 31 May 2012, at 00:49, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 André, On 5/30/12 6:18 PM, André Warnier wrote: Taking the same hypothetical case and figures : Assuming that you need a total of (1 * 4 connections) = 4 connections. Assuming that it takes 10ms to set up one such connection, and that once it is there, you don't do anything with it and just let it be for now. Assuming we ignore such things like bandwidth, other things happening on that host etc.. It then takes a total of (10 ms * 4) = 40 ms = 400s = ~ 6.5 minutes just to set up these connections. Is that acceptable ? What I mean is that once you start playing with such numbers, you may want to look at other aspects than just required memory.. There's also the issue of open filehandles, etc. My development environment is limited to 10240 filehandles per process (though of course that can be changed). Opening 10,000 connections would use most of those, leaving very few left to accept incoming requests from clients, etc. Ah, beat me to it. p It would be easier if all databases were hosted by a single instance of MySQL -- then you could use Tomcat-pool's feature of being able to provide credentials when obtaining connections from the pool -- and get the right database. That way, a much smaller number of connections could be maintained with roughly the same semantics. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk/GsecACgkQ9CaO5/Lv0PC7HACeNXMp5KZpAHGfhK73/MPFcGOn eIwAoJ7g4P/Nz2DBgNHBhwkq0jO3HMCu �CE -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint
It would be easier if all databases were hosted by a single instance of MySQL -- then you could use Tomcat-pool's feature of being able to provide credentials when obtaining connections from the pool -- and get the right database. That way, a much smaller number of connections could be maintained with roughly the same semantics. Can you expand on how I could do this? Thanks for the insights. I think in reality there would be multiple databases per server. For example, when I purchase shared hosting, you can query for all the databases and you can see there are hundreds of db's for each customer. If a customer's traffic is high enough, I could move them to another server. Also, seeing as 2GB would be required, and the file handler issue, I was thinking one could segment sites into clusters, so each cluster would be responsible for n number of websites (thus reducing the # of pools required per application server). On Thu, May 31, 2012 at 2:56 AM, Pid * p...@pidster.com wrote: On 31 May 2012, at 00:49, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 André, On 5/30/12 6:18 PM, André Warnier wrote: Taking the same hypothetical case and figures : Assuming that you need a total of (1 * 4 connections) = 4 connections. Assuming that it takes 10ms to set up one such connection, and that once it is there, you don't do anything with it and just let it be for now. Assuming we ignore such things like bandwidth, other things happening on that host etc.. It then takes a total of (10 ms * 4) = 40 ms = 400s = ~ 6.5 minutes just to set up these connections. Is that acceptable ? What I mean is that once you start playing with such numbers, you may want to look at other aspects than just required memory.. There's also the issue of open filehandles, etc. My development environment is limited to 10240 filehandles per process (though of course that can be changed). Opening 10,000 connections would use most of those, leaving very few left to accept incoming requests from clients, etc. Ah, beat me to it. p It would be easier if all databases were hosted by a single instance of MySQL -- then you could use Tomcat-pool's feature of being able to provide credentials when obtaining connections from the pool -- and get the right database. That way, a much smaller number of connections could be maintained with roughly the same semantics. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk/GsecACgkQ9CaO5/Lv0PC7HACeNXMp5KZpAHGfhK73/MPFcGOn eIwAoJ7g4P/Nz2DBgNHBhwkq0jO3HMCu �CE -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ahmed, On 5/29/12 9:29 AM, S Ahmed wrote: If my requirement for a hosted application is to give each customer a separate instance of mysql, I am curious how feasible that would be. You can certainly do this. What is the memory footprint for a single connection to a database? That depends a lot of which database you are using (MySQL connections are fairly modest: the retained size of one of my MySQL JDBC4Connection objects is 66k in an x86-64 environment with a 64-bit Sun/Oracle JVM), what things you are having cached/pooled on the client side, etc. You'd have to give us a lot more information about your environment. Better yet, configure it the way you want it and use a profiler to see how big the connections become. If I had 10K separate mysql instances, would it be feasible to create a pool of pools? i.e. a hashmap of connection pools? That sounds like a lot of MySQL instances to be contacted by a single application server. Do you need 10k separate customers all running in the same webapp? If my environment and requirements match yours, you'd need 1 * max_pool_size * 66KiB at peak usage. That's about 640MiB for each connection you want in 10k pools. For a (uniform) max pool size of 4, you'll need more than 2GiB of heap space just for connection pools. Is that acceptable? - -chris -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk/GKb8ACgkQ9CaO5/Lv0PBmCACghH4L1ycmK1UwHHPq1aVu0Sxn X2IAnjePQzdy5iMAL5pEIe0KgxRY1q8V =Uieg -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint
Chris, Great thanks that is exactly what I was looking for, just to get an idea at this point. And yes it was for mysql. I was thinking one could create a smarter pool, one that created more connections for sites used more often, and less for others. Much appreciated. On Wed, May 30, 2012 at 10:07 AM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ahmed, On 5/29/12 9:29 AM, S Ahmed wrote: If my requirement for a hosted application is to give each customer a separate instance of mysql, I am curious how feasible that would be. You can certainly do this. What is the memory footprint for a single connection to a database? That depends a lot of which database you are using (MySQL connections are fairly modest: the retained size of one of my MySQL JDBC4Connection objects is 66k in an x86-64 environment with a 64-bit Sun/Oracle JVM), what things you are having cached/pooled on the client side, etc. You'd have to give us a lot more information about your environment. Better yet, configure it the way you want it and use a profiler to see how big the connections become. If I had 10K separate mysql instances, would it be feasible to create a pool of pools? i.e. a hashmap of connection pools? That sounds like a lot of MySQL instances to be contacted by a single application server. Do you need 10k separate customers all running in the same webapp? If my environment and requirements match yours, you'd need 1 * max_pool_size * 66KiB at peak usage. That's about 640MiB for each connection you want in 10k pools. For a (uniform) max pool size of 4, you'll need more than 2GiB of heap space just for connection pools. Is that acceptable? - -chris -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk/GKb8ACgkQ9CaO5/Lv0PBmCACghH4L1ycmK1UwHHPq1aVu0Sxn X2IAnjePQzdy5iMAL5pEIe0KgxRY1q8V =Uieg -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint
On Wed, May 30, 2012 at 10:07 AM, Christopher Schultz ch...@christopherschultz.net wrote: .. If my environment and requirements match yours, you'd need 1 * max_pool_size * 66KiB at peak usage. That's about 640MiB for each connection you want in 10k pools. For a (uniform) max pool size of 4, you'll need more than 2GiB of heap space just for connection pools. Is that acceptable? Taking the same hypothetical case and figures : Assuming that you need a total of (1 * 4 connections) = 4 connections. Assuming that it takes 10ms to set up one such connection, and that once it is there, you don't do anything with it and just let it be for now. Assuming we ignore such things like bandwidth, other things happening on that host etc.. It then takes a total of (10 ms * 4) = 40 ms = 400s = ~ 6.5 minutes just to set up these connections. Is that acceptable ? What I mean is that once you start playing with such numbers, you may want to look at other aspects than just required memory.. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 André, On 5/30/12 6:18 PM, André Warnier wrote: Taking the same hypothetical case and figures : Assuming that you need a total of (1 * 4 connections) = 4 connections. Assuming that it takes 10ms to set up one such connection, and that once it is there, you don't do anything with it and just let it be for now. Assuming we ignore such things like bandwidth, other things happening on that host etc.. It then takes a total of (10 ms * 4) = 40 ms = 400s = ~ 6.5 minutes just to set up these connections. Is that acceptable ? What I mean is that once you start playing with such numbers, you may want to look at other aspects than just required memory.. There's also the issue of open filehandles, etc. My development environment is limited to 10240 filehandles per process (though of course that can be changed). Opening 10,000 connections would use most of those, leaving very few left to accept incoming requests from clients, etc. It would be easier if all databases were hosted by a single instance of MySQL -- then you could use Tomcat-pool's feature of being able to provide credentials when obtaining connections from the pool -- and get the right database. That way, a much smaller number of connections could be maintained with roughly the same semantics. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk/GsecACgkQ9CaO5/Lv0PC7HACeNXMp5KZpAHGfhK73/MPFcGOn eIwAoJ7g4P/Nz2DBgNHBhwkq0jO3HMCu =aeCE -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
