0

I have a large Perl app that I need to make faster; on the basis that it spends most of its running time talking to the DB I wanted to know how many well written SQL statements I could run and meet the performance targets. To do this I wrote a very simple handler that does a SELECT and an INSERT, when I benchmarked it on 300 concurrent requests (10,000 in total) the results were quite poor (1900ms average).

The performance target we've been given by the client is based on another app they use written in PHP, so I wrote a quick PHP script that does functionally the same thing as my simple mod_perl test handler and it gave a 400ms average!

The PHP code is:

$cs = "//oracle.ourdomain.com:1521/XE";
$oc = oci_pconnect("hr","password",$cs);
if(!$oc) { print oci_error(); }
$stid = oci_parse($oc, 'SELECT id FROM zz_system_options WHERE id = 1');
oci_execute($stid);
$stmt = oci_parse($oc, "INSERT INTO zz_system_options (id,option_name) VALUES (zz_system_optionsids.nextval,'load testing')");
oci_execute($stmt);
echo "hello world";

The Perl code is:

use strict;
use Apache2::RequestRec ();
use Apache2::RequestIO  ();
use Apache2::Const -compile => qw(:common);
use DBI;
our $dbh;
sub handler
{
    my $r = shift;

    # Connect to DB
    $dbh = DBI->connect( "DBI:Oracle:host=oracle.ourdoamin.com;port=1521;sid=XE", "hr", "password" ) unless $dbh;  
    my $dbi_query_object = $dbh->prepare("SELECT id FROM zz_system_options");
    $dbi_query_object->execute();
    $dbi_query_object =
      $dbh->prepare("INSERT INTO zz_system_options (id,option_name) VALUES (zz_system_optionsids.nextval,?)");
    $dbi_query_object->execute("load testing");
    # Print out some info about this...
    $r->content_type('text/plain');
    $r->print("Errors: $err\n");
    return Apache2::Const::OK;
}

The mod_perl has a startup.pl script called with a PerlRequire in the apache config that loads all the 'use'd modules. If all is working correctly, and I've no reason to think it isn't, then each request should only run the lines in 'sub handler' - meaning the Perl and PHP should be doing pretty much the same thing.

Server details:- The hardware node is a Quad Core Xeon L5630 @ 2.13GHz with 24Gb RAM, the OS for the Apache virtual machine is Gentoo, the OS for Oracle is Centos 5,.

Versions: OSes both updated within last 2 weeks, Apache version 2.2.22, mod_perl version 2.0.4, DBI Version 1.622, DBD::Oracle version 1.50, Oracle instant client version 10.2.0.3, Oracle Database 10g Express Edition Release 10.2.0.1.0, PHP version 5.3

Apache MPM config is ServerLimit 2000, MaxClients 2000 and MaxRequestsPerChild 300

Things I checked: during the testing the only load was from the test app/oracle, neither virtual machine hit any of its bean counter limits, e.g., memory, Oracle showed 1 session per Apache child at all times, inserts had been done after each run.

So, my question is; Can I make the mod_perl version faster and if so how?

Alex M
  • 1
  • 1
    You should first compare the same things as both your SELECT and INSERT queries are not the same beetween you PHP and your Perl scripts : `WHERE id =1` on the PHP SELECT and you are using _place-holders_ on the Perl INSERT (_place-holders_ are known to affect performances - better or worse). – Ouki Aug 27 '12 at 08:57
  • Test repeated with no placeholder (the missing WHERE was a cut and paste from a different version). New result is 1810ms average for the Perl - PHP the same as unedited. – Alex M Aug 27 '12 at 09:30
  • Found the solution. In my PHP testing I'd used http instead of https. Switching to https gave very similar results to the Perl. Doh! – Alex M Aug 27 '12 at 10:37
  • @Ouki is right, why select only "WHERE id = 1" for PHP and select everything for Perl? – tbone Aug 27 '12 at 12:37

1 Answers1

0

If you changed the PHP code and the timing didn't change then clearly you're not measuring the code time, are you?

The important question is - why are you repeatedly connecting in the Perl script and not in the PHP script?

Finally, this test probably isn't going to tell you anything useful unless all your queries are simple single-table single-row selects and inserts.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • Sorry, I'm not sure where I said I changed the PHP code - the PHP code has been the same throughout (and aside from fixing the differences in the SQL so has the Perl). Also, not the 'if' after the connect in perl - $dbh is declared as 'our' so will be shared between requests to the same apache child. – Alex M Aug 27 '12 at 09:41
  • As for whether it tells me anything useful, it tells me either mod_perl is way slower than php or my set-up is wrong. I'm really hoping its the latter. And by 'way slower' I mean to get set-up and respond to a request our app then has lots of other things to do and they may or may not be quicker in perl/php, but 1700 odd milliseconds to do very little is quite a lot to have to then 'save' in the rest of the code. – Alex M Aug 27 '12 at 09:43
  • 1. "Test repeated with...", 2. "it tells me either...I'm hoping" - you're guessing, which means it isn't telling you anything useful is it? Even if you restructured your test, this is unlikely to tell you anything useful about your application. – Richard Huxton Aug 27 '12 at 10:00
  • That was a change to the Perl code not the PHP, but I think you're right about; if a code change doesn't change timings then its not code. Also, I absolutely agree, that this is telling me nothing useful about my application - however, it is telling me that there is something in the set-up of my server/stack that is causing mod_perl to be much slower than an equiv. PHP script. I'd really like to understand or eliminate that difference before I get too much further working on the application - in case there is a nice set-up change we can make to make our app run faster with no code change! – Alex M Aug 27 '12 at 10:17
  • Just went back and checked. I assumed you'd put added the placeholder to the PHP. Do the testing with placeholders - it'll be a relevant part of the query parsing/setup/sending time. Did you miss the bit in my answer about the number of connections in php vs perl? – Richard Huxton Aug 27 '12 at 10:32
  • No, the answer to the connection issue is that there is an 'unless' after the connect and the $dbh is a global; the manual way of doing PHP's oci_pconnect. – Alex M Aug 27 '12 at 10:44
  • However, in the end I spotted something in the timings which was that the bulk of the time was in the 'connect', i.e., outside of the code - turns out I'd dropped https from the PHP URL... Thanks for your answers though, its not until you start thinking critically about your assumptions that you spot things - like it could only be down to set-up... – Alex M Aug 27 '12 at 10:46
  • I was going to say "where?" re the connect - then noticed I needed to scroll the code panel. Not sure what SO could do to make that better, but it's caught me out before. Glad you've found your discrepancy. – Richard Huxton Aug 27 '12 at 10:49