Wow !
I never thought about that ! It would sure be a big performance boost
(even if its already reallly fast) !
A big thanks you for making me realize this David,
I also just modified my webapp to only initiate a new connection if its down:
dbh{'client'} = Console::SQL->connect( $server{'ip_addr'},
$server{'db_user'}, $server{'db_pass'}, $session{'db_client'});
$dbh{'archives'} = Console::SQL->connect( $server{'ip_addr'},
$server{'db_user'}, $server{'db_pass'}, $session{'db_archives'});
is now:
# (Re)connect to client database
if (!defined $dbh{'client'} or !$dbh{'client'}->ping)
{
$dbh{'client'} = Console::SQL->connect( $server{'ip_addr'},
$server{'db_user'}, $server{'db_pass'}, $session{'db_client'});
}
# (Re)connect to archives database
if (!defined $dbh{'archives'} or !$dbh{'archives'}->ping)
{
$dbh{'archives'} = Console::SQL->connect( $server{'ip_addr'},
$server{'db_user'}, $server{'db_pass'}, $session{'db_archives'});
}
'Console::SQL' is a module of my own and the connect sub is just a
wrapper around DBI->connect, nothing weird here.
This modification seem to have reduced my number of connection drasticly.
Thanks again David, as the same logic would apply with mysql, now its
only a matter of compiling mysql 4.1 on my new OpenBSD 3.6, the port
is still at 4.0 so it will be a pain in the a** but nothing is
possible :)
Any others, tips, comments, pointers or recommendations for
multi-database application with DBI is welcome :)
L. Waterhouse
On Mon, 1 Nov 2004 17:26:06 -0800 (PST), David Goodman
<[EMAIL PROTECTED]> wrote:
> Hello Lawrence:
>
> If I were doing this with Sybase or MS SQL Server,
> using SQL syntax like:
>
> insert targetdb.tablename select * from
> sourcedb.tablename
>
> would have the performance advantage of avoiding
> network traffic and avoiding passing your data through
> the client libraries. Everything would be done within
> the database server.
>
> David
> --- Lawrence Waterhouse
>
>
> <[EMAIL PROTECTED]> wrote:
>
> > Hello everyone,
> >
> > First I would like to apologise for this long mail
> > but I really need
> > some advices on a web application I am currently
> > working on. This
> > application makes extensive use of DBI and interacts
> > with many
> > databases at the same time.
> >
> > I will try to resume my setup the best way I can,
> > English is not my
> > native language so I hope everyone will understand
> > me well even if my
> > English is not perfect :P
> >
> > Server:
> > Apache 1.3.29 + mod_perl 1.29 + Mason 1.26
> > Perl 5.85 / DBI 1.45 + DBD::mysql-2.9004 +
> > Apache::DBI 0.94
> >
> > This is a multi-client web application, each client
> > can have more than
> > one user, there is a 'master' database which is
> > global to everyone and
> > contain among other things a 'users' table used to
> > store credentials
> > of the users.
> >
> > Here is the complex part: each client has 2
> > databases
> > ('client-clientname' and 'archives-clientname')
> > which are specific to
> > the client. So basically, each client has access to
> > three databases,
> > the master and the 2 client specific database.
> >
> > At the moment, I use a global hash to aggregate all
> > my dbh, and I am
> > not sure if this is the right way to do it. The
> > master dbh is
> > initiated in my handler.pl, while the two client
> > specific database
> > connections are initiated after the user log-in to
> > the web
> > application.
> >
> > Here are the relevant portion of my handler.pl
> >
> > use Apache::DBI;
> > use DBI;
> >
> > ÃâÂ
>
>
> >
> > # DSN declaration
> > my $dsn =
> > "DBI:$db_type:host=127.0.0.1;dbname=master";
> >
> > # Debug mode
> > $Apache::DBI::DEBUG = 2;
> >
> > # Connect to master database
> > Apache::DBI->connect_on_init( $dsn, 'db_user',
> > 'db_pass' ) or die
> > "Cant connect to master database: $DBI::errstr!\n";
> > #Apache::DBI->setPingTimeOut( $dsn, 0 );
> >
> > sub handler
> > {
> > # Get the Apache request object
> > my $r = shift;
> >
> > # Only handle text
> > return -1 if $r->content_type and $r->content_type
> > !~ m|^text/|i;
> >
> > # (re)-Connect to the master database
> > $HTML::Mason::Commands::dbh{'master'} =
> > DBI->connect($dsn, 'db_user',
> > 'db_pass') or die "Cannot connect to master
> > database:
> > $DBI::errstr!\n";
> >
> > # Return to the browser
> > return $ah->handle_request($r);
> > }
> >
> >
> > The application is used by the users to move data
> > between is two
> > client specific databases.
> >
> > Here an example:
> >
> > 1. A user log-in, the credentials are taken from the
> > 'master' database
> > and the user is authenticated, I use
> > Apache::Session::MySQL and
> > cookies to store session information (user_id,
> > client_id and othersÃâÂ).
>
>
> > 2. If the user is authenticated, I initiate a
> > connection to the
> > client-specific databases based on the value of
> > client_id. User now
> > have access to the three databases, all dbh are
> > stored in a global
> > %dbh hash.
> > 3. User move data between is two table, (clientdb =>
> > archivesdb).
> >
> > This setup is working but it is giving me some
> > problems:
> >
> > Since the Apache::DBI module don't share dbh between
> > the apache child
> > processes, each user might end up using a total of
> > 11 connections! One
> > for the master and five for each client specific
> > databases (my setup
> > use 5 apache child processes, the apache default).
> >
> > The Apache::DBI recycle the 'master' dbh without
> > problem but a new
> > connection is initiated to each client specific
> > database at every user
> > request, thus not recycling it. I have seen a patch
> > (http://mathforum.org/epigone/modperl/stroblangnerm)
> > for Apache::DBI
> > but I am not sure if I should use it.
> >
> > Here is a sanitized sample of my error_log with
> > $Apache::DBI::DEBUG =
> > 2 in my handler.pl :
> >
> > 16629 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 29081 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 12403 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 7125 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 24346 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 26657 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 29081 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 29081 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 29081 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 29081 Apache::DBI new connect to
> >
> 'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 29081 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 29081 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 29081 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> > 20332 Apache::DBI already connected to
> >
> 'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> >
> > So basically I am wondering if I should modify my
> > application behaviour.
> >
> > I know that with DBI I can initiate a connection to
> > the server instead
> > of a database if I don't specify a tablename in my
> > DSN and modify my
> > code to use SQL query like the following:
> >
> > SELECT * FROM database.table ÃâÂ
> >
> > Instead of specifying a database in my DSN (like I
> > do now) and using
> > query like this:
> >
> > SELECT * FROM table ÃâÂ
> >
> > This approach could also allow me to move data
> > between my databases in
> > a more 'sexy' way using the new mysql 4.1 'INSERT
> > Ãâ SELECT' syntax,
>
>
> > like this:
> >
> > INSERT INTO archives-clientname.table (*) SELECT *
> > FROM client-clientname.table
> >
> > Since this would require a lot of change to my code,
> > I am wondering if
> > I am heading in the right direction or if there a
> > better solution
> > (modyfing my Apache::DBI setup or application
> > behaviour). I am also
> > wondering if modifying my code to connect directly
> > to the server and
> > use database.table syntax would cause security
> > issues since it would
> > require me to use a mysql user who has access to all
> > databases.
> >
> > Any idea, tips, pointers or recommendations will be
> > much appreciated ÃÂÅ
>
>
> > It is the first multi-databases application I am
> > writing and I am
> > still learning!
> >
> > And thanks in advance !
> >
> > Ps: I apologise for my not-so-perfect English, if
> > something is not
> > clear or if I need to provide more information's
> > about my setup, just
> > tell me.
> >
>
>
