You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Christopher Schultz <ch...@christopherschultz.net> on 2012/06/01 20:37:00 UTC
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-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
>
>