From users-return-234611-apmail-tomcat-users-archive=tomcat.apache.org@tomcat.apache.org Fri Jun 1 18:37:36 2012 Return-Path: X-Original-To: apmail-tomcat-users-archive@www.apache.org Delivered-To: apmail-tomcat-users-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 6A9B8998A for ; Fri, 1 Jun 2012 18:37:36 +0000 (UTC) Received: (qmail 42553 invoked by uid 500); 1 Jun 2012 18:37:33 -0000 Delivered-To: apmail-tomcat-users-archive@tomcat.apache.org Received: (qmail 42478 invoked by uid 500); 1 Jun 2012 18:37:33 -0000 Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: "Tomcat Users List" Delivered-To: mailing list users@tomcat.apache.org Received: (qmail 42469 invoked by uid 99); 1 Jun 2012 18:37:33 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 01 Jun 2012 18:37:33 +0000 X-ASF-Spam-Status: No, hits=0.7 required=5.0 tests=RCVD_IN_DNSWL_NONE,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (nike.apache.org: local policy) Received: from [76.96.62.32] (HELO qmta03.westchester.pa.mail.comcast.net) (76.96.62.32) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 01 Jun 2012 18:37:25 +0000 Received: from omta07.westchester.pa.mail.comcast.net ([76.96.62.59]) by qmta03.westchester.pa.mail.comcast.net with comcast id H6Zl1j0061GhbT8536d41N; Fri, 01 Jun 2012 18:37:04 +0000 Received: from Christophers-MacBook-Pro.local ([69.143.109.145]) by omta07.westchester.pa.mail.comcast.net with comcast id H6d01j00P38FjT13T6d23e; Fri, 01 Jun 2012 18:37:04 +0000 Message-ID: <4FC90BCC.3060908@christopherschultz.net> Date: Fri, 01 Jun 2012 14:37:00 -0400 From: Christopher Schultz User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7; rv:12.0) Gecko/20120428 Thunderbird/12.0.1 MIME-Version: 1.0 To: Tomcat Users List Subject: Re: tomcat jdbc pool, creating a pool of pools, single connection memory footprint References: <4FC629BF.9010703@christopherschultz.net> <4FC69CC6.3070104@ice-sa.com> <32527565.5963.1338421793271.JavaMail.mobile-sync@vcok17> <8159094285948234855@unknownmsgid> In-Reply-To: X-Enigmail-Version: 1.4.1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit -----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