Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Database Connection Question

Database Connection Question

Database Connection Question

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?  
Chad Sanders

RE: Database Connection Question


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('dbi:Pg: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:


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.


RE: Database Connection Question

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:

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

Thanks for your help,

RE: Database Connection Question

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.

RE: Database Connection Question

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:

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="javascript:deleteMedia('media.cgi?mediaid=$mediaid&action=delete');">DELETE</a>|        if (-d "/home/rgardner/html/$folder");

                if ($total > 0) {($crate) = ($total / $viewcount);}
                 else {($crate=0.00)}
                if ($conversionrate > 18){$conversionrate = qq|<font color="#ff0000">$conversionrate%</font>|;}
                 {$conversionrate = qq|<font color="#000000">$conversionrate%</font>|;}

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

                          <td ALIGN="center" bgcolor="#EEEEEE">$delete_option</td>

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

        $template{'current_media'} = qq|
                          <td colspan="6" align="center"><b>No Data</b></td>
                |        if $template{'current_media'} eq '';
        print parse("include/tpl/media", \%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("DBI:Pg:dbname=$dbname") or die("Could not connect to the $dbname database on $dbhost.");
    return $dbh;

sub dbh_close

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.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close