Database Connection Question 1

[chedder123]

I'm having a problem with the database connection using PostgreSQL.
Everytime I open a connection, am I supposed to close it in the Perl CGI script? For example:

$dbh = &dbh_connect();
$sql = "SELECT mediaid, folder, view_count FROM media ORDER BY folder;";
$sth = $dbh->prepare($sql) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
$sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped";

Is what I'm using to open the connection and collect the information. However, on my unix server processes it lists a HUGE portion being taken up by Postgres. For example:

root 27979 0.0 1.3 81308 3396 ? S 16:39 0:00 /usr/sbin/httpd -DHAVE_ACCESS -DHAVE_PROXY -DHAVE_AUTH_ANON -DHAVE_AC
apache 30719 0.0 1.4 81444 3776 ? S 16:47 0:01 /usr/sbin/httpd -DHAVE_ACCESS -DHAVE_PROXY -DHAVE_AUTH_ANON -DHAVE_AC
postgres 5205 1.4 1.1 8308 3020 ? S 17:12 0:00 postgres: apache media [local] UPDATE waiting
apache 5222 0.4 1.5 6228 3948 ? S 17:12 0:00 /usr/bin/perl /home/rgardner/html/admin/cycle.cgi


About 50 of those, which causes the database to reject more connections because it already has too many and slows the access time considerably. What would be causing the problem and how can I fix it? I tried using a

$dbh = &dbh_close();

at the end of the script to close it, but I actually have no idea if that works. Any help?
Thanks,
Chad Sanders

http://www.successmm.com

chad@successmm.com

 

[dwhalen]

Hey,

Yes, you should disconnect from the database after you are finished using it. I use the perl DBI to connect up and this is the code I use:

first connect up to the database:
my $database_connect = DBI->connect('dbig:dbname=$databaseName','username','password' || warn ("Non Encrypted Connect_Error: Can't Connect username"

And then once you are finished disconnect, but only if you are connected:

if(defined($database_connect)){$database_connect->disconnect();}

Plus make sure that your database connections are local, because weird things can happen when you have global database connections floating around...trust me, I had a perl error that would only happen once every 3 or 4 times I ran the program, then I found out I was using a global database connection. So always pass you database connections to subs, it will save you a lot of headaches.


Hope this helps.

Later

 

[chedder123]

I went ahead and added that code in and at least it didn't give me an error. However, when I check the current processes running on the server, I still see a lot of:

0:00 /usr/sbin/httpd -DHAVE_ACCESS -DHAVE_PROXY -DHAVE_AUTH_ANON -DHAVE_AC
apache 22195 0.1 1.5 6232 3948 ? S 15:40 0:00 /usr/bin/perl /home/rgardner/html/admin/cycle.cgi
postgres 22197 0.8 1.1 8320 2968 ? S 15:40 0:00 postgres: apache media [local] UPDATE waiting
apache 22205 0.1 1.5 6232 3948 ? S 15:40 0:00 /usr/bin/perl /home/rgardner/html/admin/cycle.cgi
postgres 22206 0.8 1.1 8308 2960 ? S 15:40 0:00 postgres: apache media [local] UPDATE waiting

Anywhere between 15-25 of this are listed in the processes. I'm still getting the "unable to connect - too many connections" error about half the time I pull up the script. What is the difference in opening the database globally and locally? Is that simply putting the open comamnd inside of a sub routine instead of at the start of the script file?

This is the actual code I use to connect and disconnect the database:

sub dbh_connect
{	
	use DBI;
	my $dbh = DBI->connect("DBI:Pg:dbname=$dbname") or die("Could not connect to the $dbname database on $dbhost."); 
	return $dbh;
}

sub dbh_close
{	
	if(defined($dbh)){$dbh->disconnect();}	
}

Thanks for your help,
Chad

 

[dwhalen]

Hey,

Sorry I forgot to check up on this post!

I am not an expert on all of this but I will try to make some suggestions that seem to work for me.

First, to get rid of all the database connections maybe you should stop postgres and stop apache (if you are running apache). Hopefully this will clear out any open connections...however I am not positive.

Secondly, as to the global versus local....here is what I do. At the top of a file, after all my apache stuff and setting my variables I open a database connection. Then I do all the stuff I have to, building up querys and such, use my database connection, get my results or whatever, and then at the very bottom of the page I put the disconnect code (same as you have above).

Now, if I need to use a database connection in a sub I send my database connection into the sub by reference ex:
$results = &someSub(\@array,\$dbh);

This works for me. The only problems I have ever had with the database connections was when I opened a connection at the top of the file and then used it in a sub without actually sending it in, that was what I meant by not using global connections. The other problem was forgetting to disconnect.

If this doesn't work then maybe post some more code, like how you interact with the connect in the entire file.

Later

 

[chedder123]

I appreciate all your help with this. I actually did have it like that, opening the database globally and then just using it in the sub without passing it. I've tried to eliminate that. Here's the code where' I'm having some problems with. It's just incredibly incredibly slow that it's driving me nuts. It's just recently started doing this too, so I'm not sure what it is. I'll just post the whole script:

#!/usr/bin/perl
use strict;                                                # strict setting for added security
use CGI qw/:all :cgi-lib/;                                # load the CGI library
use CGI::Carp 'fatalsToBrowser';                        # report errors to browser
require "include/conf.cgi";                                # include the scriptng configuration file

my($q, %in, %template, $dbh, $sql, $sth, $stha, $sthb, $mediaid, $media_type, $folder, $viewcount, $MIMEmail, $nscmd, $output, $ncount, $ocount, $total, @bgpage, $bgline, $crate);

$q         = new CGI;                                        # create the new CGI handle
%in        = $q->Vars;                                        # get all the variables passed to this script

# open the database connection
$dbh    = &dbh_connect();                               

        print header(-type=>'text/html',  -expires=>'now'), start_html(-title=>"Success Multimedia");
        $template{'current_media'} = '';
        # get the media statistics for every item in the database
        $sql = "SELECT mediaid, folder, view_count FROM media ORDER BY folder;";
        $sth = $dbh->prepare($sql) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
        $sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped";
        while (($mediaid, $folder, $viewcount) = $sth->fetchrow_array ) {
                # get the new order stats for this media
                $sql = "SELECT COUNT(leadid) FROM leads WHERE export_counter = '0' AND mediaid = '$mediaid';";
                $stha = $dbh->prepare($sql) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
                $stha->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped";
                ($ncount) = $dbh->selectrow_array($sql);

                # get the total hits
                $sql = "SELECT COUNT(leadid) FROM leads WHERE export_counter > '0' AND mediaid = '$mediaid';";
                $sthb = $dbh->prepare($sql) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
                $sthb->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped";
                ($ocount) = $dbh->selectrow_array($sql);

                # grand total leads
                ($total) = $ncount + $ocount;

                my($delete_option) = " ";
                # check for the existance of the directory
                $delete_option = qq|<a href=&quot;javascript:deleteMedia('media.cgi?mediaid=$mediaid&action=delete');&quot;>DELETE</a>|        if (-d &quot;/home/rgardner/html/$folder&quot;);

                if ($total > 0) {($crate) = ($total / $viewcount);}
                 else {($crate=0.00)}
                my($conversionrate)=(substr($crate,0,5));
                ($conversionrate)=$conversionrate*100;
                if ($conversionrate > 18){$conversionrate = qq|<font color=&quot;#ff0000&quot;>$conversionrate%</font>|;}
                else
                 {$conversionrate = qq|<font color=&quot;#000000&quot;>$conversionrate%</font>|;}

                my($table) = qq|
                        <tr>
                          <td><a href=&quot;media.cgi?mediaid=$mediaid&action=edit_media&quot;>$folder</a></td>
                          <td ALIGN=&quot;right&quot; bgcolor=&quot;#EEEEEE&quot;><a href=&quot;[URL unfurl="true"]http://www.successmm.com/$folder/index.html&quot;>$viewcount</a></td>[/URL]
                          <td ALIGN=&quot;right&quot;>$ncount</td>
                          <td ALIGN=&quot;right&quot; bgcolor=&quot;#EEEEEE&quot;>$ocount</td>
                          <td ALIGN=&quot;right&quot;><a href=&quot;details_media.cgi?mediaid=$mediaid&action=details&fday=12&fmonth=02&fyear=2003&tday=26&tmonth=02&tyear=2003&quot;>$total</a></td>
                          <td ALIGN=&quot;right&quot;>$conversionrate</td>

                          <td ALIGN=&quot;center&quot; bgcolor=&quot;#EEEEEE&quot;>$delete_option</td>
                        </tr>
                |;

                $template{'current_media'} .= $table;
        }

        $template{'current_media'} = qq|
                        <tr>
                          <td colspan=&quot;6&quot; align=&quot;center&quot;><b>No Data</b></td>
                        </tr>
                |        if $template{'current_media'} eq '';
        print parse(&quot;include/tpl/media&quot;, \%template);

# close database connection
$dbh    = &dbh_close();


The conf.inf files just has the two database functions in it:

sub dbh_connect
{	
	use DBI;
	my $dbh = DBI->connect(&quot;DBI:Pg:dbname=$dbname&quot;) or die(&quot;Could not connect to the $dbname database on $dbhost.&quot;); 
	return $dbh;
}

sub dbh_close
{	
	if(defined($dbh)){$dbh->disconnect();}	
}

The database "media" has about only 50 rows of data in it, however the second database, "leads" has about 15,000 rows of data in it. Would that much data really cause that slow response time? I believe I might also be doing a lot of the querying redundantly. Is there a more efficient way of doing this? Again, thanks for all your help.