You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by S Ahmed <sa...@gmail.com> on 2012/05/29 15:29:09 UTC
tomcat jdbc pool, creating a pool of pools, single connection memory footprint
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.
What is the memory footprint for a single connection to a database?
If I had 10K separate mysql instances, would it be feasible to create a
pool of pools? i.e. a hashmap of connection pools?
Re: tomcat jdbc pool, creating a pool of pools, single connection
memory footprint
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----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 (10000 * 4 connections) = 40000
> 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 * 40000) = 400000 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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection
memory footprint
Posted by Pid * <pi...@pidster.com>.
On 30 May 2012, at 23:19, "André Warnier" <aw...@ice-sa.com> wrote:
>> On Wed, May 30, 2012 at 10:07 AM, Christopher Schultz <
>> chris@christopherschultz.net> wrote:
> ..
>>>
>>> If my environment and requirements match yours, you'd need 10000 *
>>> 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 (10000 * 4 connections) = 40000 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 * 40000) = 400000 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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection
memory footprint
Posted by S Ahmed <sa...@gmail.com>.
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 <
chris@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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: tomcat jdbc pool, creating a pool of pools, single connection
memory footprint
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection
memory footprint
Posted by S Ahmed <sa...@gmail.com>.
>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 * <pi...@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 (10000 * 4 connections) = 40000
> >> 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 * 40000) = 400000 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-unsubscribe@tomcat.apache.org
> > For additional commands, e-mail: users-help@tomcat.apache.org
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: tomcat jdbc pool, creating a pool of pools, single connection
memory footprint
Posted by Pid * <pi...@pidster.com>.
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 (10000 * 4 connections) = 40000
>> 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 * 40000) = 400000 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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection
memory footprint
Posted by André Warnier <aw...@ice-sa.com>.
>
> On Wed, May 30, 2012 at 10:07 AM, Christopher Schultz <
> chris@christopherschultz.net> wrote:
..
>>
>> If my environment and requirements match yours, you'd need 10000 *
>> 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 (10000 * 4 connections) = 40000 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 * 40000) = 400000 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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: tomcat jdbc pool, creating a pool of pools, single connection
memory footprint
Posted by S Ahmed <sa...@gmail.com>.
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 <
chris@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 10000 *
> 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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: tomcat jdbc pool, creating a pool of pools, single connection
memory footprint
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----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 10000 *
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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org