Script using too much resources

[c4n]

I have this Perl script and my server admin said it is using WAY too much resources. Any suggestions on how to improve the script so it will run faster and better (uses MySQL database)?

What this script does is count the number of impressions of a banner. So when it is called like this:
<img src=&quot;path/banner.cgi?BANNER_ID&quot;>
the script adds +1 to the number of impressions, selects URL (&quot;src&quot of the banner from the database and redirects to the image.



### VARIABLES ###
$database_name=&quot;database_name&quot;;
$database_user=&quot;database_username&quot;;
$database_pass=&quot;password&quot;;
### END VARIABLES ###

use DBI;

# Get banner ID
$id = $ENV{'QUERY_STRING'};

$dbh = DBI->connect( &quot;dbi:mysql:$database_name&quot;, &quot;$database_user&quot;, &quot;$database_pass&quot;, {RaiseError=>1,AutoCommit=>1} ) or die(&quot;Can't connect to the MySQL database. Please check your database host, username and password&quot;

# add +1 to impressions
$sth = $dbh->do(&quot;update banners set impressions=impressions+1 where id=\&quot;$id\&quot;&quot or db_err(&quot;Unable to execute query&quot;, $dbh->errstr);

# Get the src parameter of the banner
$sth = $dbh->prepare(&quot;select `src` from `banners` where id=\&quot;$id\&quot;&quot;
$sth->execute or db_err(&quot;Unable to execute query&quot;, $dbh->errstr);
($src) = $sth->fetchrow_array;

# Print the image
print &quot;Location: $src\n\n&quot;;


Any ideas? Thanks!

 

[icrf]

Nothing overly inefficent jumps out at me, but it does look like you're vulnerable to sql injections. Instead of a simple banner id, a malicious user could pass in sql code that you'd execute for them. Take a read over at this FAQ (off-site) as to why and how you should be using placeholders instead:

http://faq.geekuprising.com/databases

Did the admin say what resources are being hammered? The problem might just be that you're logging into the database so many times. The only real way around that would be to use something like mod_perl and use a cached database handle.

________________________________________
Andrew - Perl Monkey

 

[c4n]

Thanks for the SQL injections heads-up, will take care of it when I get over the resources problem.

The reason I'm having problem with server overload is because this script is called about 1.000.000 times per day. I really shoud get mod_perl, but need a solution for the time being, so if anyone has any tweaks to recommend, please do.

Thanks!

 

[icrf]

Wow, I'm not sure there's much you can do. The SQL and Perl are simple enough that I don't think there's anything to optimize. That script is getting called, compiled, opening a new connection to the database over 11 times a second, on average. I don't think there's anything you can do to the code to help. mod_perl can help bring the load down a lot by caching the code and the DB handle (and if the server already has mod_perl installed, would be relatively trivial to setup).

You could move the data to a local file or into the script itself, but giving up the database is almost never a good option (and in reality, the DB is almost always the bottleneck of web scripts).

Or you could implement a little caching yourself with one of the IPC modules. Maybe you could use IPC::ShareLite and Storable's freeze/thaw to keep database and prepared statement handled (would need placeholders). I don't know if serializing and storing the dbh keeps the connection (I would actually think not) but it may be worth a shot if mod_perl proves unavailable. Maybe you could have a persistent process that keeps the dhb and sth's, then through IPC, new cgi instances query that process...lots of dirty options.

Is that normal/expected hit count? Have you checked other usage logs to see? Granted I've only run small sites, so my idea of high is a bit skewed.

________________________________________
Andrew - Perl Monkey

 

[c4n]

I really appreciate your replies to my post! I posted a similar one on several other forums and didn't get a response yet (my guess is because, as you noticed yourself, the code really is clean and simple).

1.000.000 hits per day is the normal hit count (this script is used to display banners on a high traffic site). I also don't have experience with such traffic so I thought I'd ask around.

I'll check mod_perl first and if it is not available I'll see what I can do. If I find a good solution I'll post it here.

Again thanks for your time!

 

[icrf]

Yeah, I saw you over at SP, but not DS or EE. Sometimes I think I follow too many of these forums.

Best of luck to you. I'm interrested to see what you find out.

________________________________________
Andrew - Perl Monkey