monsieur_champs has asked for the wisdom of the Perl Monks concerning the following question:
Felows
I'm trying to win a benchmark against a
Tomcat application server, and choosed my
weapons from ModPerl,
Catalyst,
Class::DBI,
DBD::Oracle,
Apache,
Template Toolkit and a
Session
Plugin for Catalyst. The database was
chosen by the client, is an Oracle 9i
(9.0.2.0.4) running on Debian Linux 3.3
stable/testing.
UPDATE:Thanks to all prayers and cerimonials indicated to me by the honoured and wise monks that looked at me on this moment of pain, the application is performing quite well now: today's benchmark just hit 2500 answers per minute, or 41.7 answers per second (!!).
UPDATE #2:Unfortunatelly, the project will became a Java application, since the client was afraid about the riscs. I can't blame them, because they allways used Java, and this would be the first Perl Application running there. Anyway, my reputation as a programmer was preserved (thanks to all Monastery Inhabitants that helped me) and so is Perl's reputation too.
The test consists on a simple task: just insert lines on a table,
and fetch them out (in a two tasks test).
The table is:
Nome Nulo? Tipo
---------------------- -------- ---------------
COD_SIS NOT NULL NUMBER
IND_EXCL NOT NULL CHAR(1)
VAL_SGL NOT NULL VARCHAR2(4)
DTH_CRCO NOT NULL DATE
DESC_SIS NOT NULL VARCHAR2(450)
NOM_PRPT NOT NULL VARCHAR2(120)
IND_CGLD NOT NULL CHAR(1)
DTH_VRFC_NOTE DATE
IND_REQR_COMP_DESENV NOT NULL CHAR(1)
IND_REQR_DIRT_CTLG NOT NULL CHAR(1)
I just can't express my frustration in words, and need to humbly
declare my incompetence to build a fast application than the
Java/Tomcat. The benchmark
was made with JMeter, a nice and
fair tool for web application benchmarking, IMHO. Java is answering 10
times better than ModPerl, and I don't know what to do about this.
For the number-driven monks, Java is answering (on the same
machine, while Apache/Perl is not running, and vice-versa for
Apache/Perl benchmarking) at 100 queries per minute ratio. With Perl,
I don't even hit 27 queries per minute.
Thrusting this is just a matter of choosing the right weapons and
fighting well, I put my fate into my (Brothers|Sisters)-in-Perl,
toghether with the outputs from
Apache::DProf
and
DBI::ProfileDumper::Apache,
hoping a enlighted monk can help me out of this Devel::Prof::Hell
again to Perl::Heaven.
Any suggestions and tips are welcome. I need to win this battle, so
I can keep this job and create more opportunities at this company.
##################################################
DBI Profile Data (DBI::ProfileDumper::Apache 1.0)
##################################################
Program : /dev/null
Path : [ DBIprofile_Statement ]
Total Records : 5 (showing 5, sorted by total)
Total Count : 3898
Total Runtime : 1.368898 seconds
#####[ 1 ]###########################################################
Count : 2348
Total Time : 0.625598 seconds
Longest Time : 0.066659 seconds
Shortest Time : 0.000004 seconds
Average Time : 0.000266 seconds
Key 1 :
#####[ 2 ]###########################################################
Count : 1151
Total Time : 0.461348 seconds
Longest Time : 0.109629 seconds
Shortest Time : 0.000022 seconds
Average Time : 0.000401 seconds
Key 1 :
SELECT ind_excl, ind_cgld, val_sgl, desc_sis, dth_vrfc_note,
dth_crco, nom_prpt, ind_reqr_comp_desenv, ind_reqr_dirt_ctlg
FROM TBGF0037
WHERE cod_sis=?
#####[ 3 ]###########################################################
Count : 3
Total Time : 0.225426 seconds
Longest Time : 0.225036 seconds
Shortest Time : 0.000083 seconds
Average Time : 0.075142 seconds
Key 1 :
INSERT INTO TBGF0037 (cod_sis, ind_excl, ind_reqr_dirt_ctlg, val_sgl,
ind_cgld, ind_reqr_comp_desenv, dth_crco, nom_pr
+pt,
dth_vrfc_note, desc_sis)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
#####[ 4 ]###########################################################
Count : 5
Total Time : 0.045541 seconds
Longest Time : 0.024365 seconds
Shortest Time : 0.000024 seconds
Average Time : 0.009108 seconds
Key 1 : SELECT TBGF0037_seq.NEXTVAL from DUAL
#####[ 5 ]###########################################################
Count : 391
Total Time : 0.010985 seconds
Longest Time : 0.001773 seconds
Shortest Time : 0.000012 seconds
Average Time : 0.000028 seconds
Key 1 :
SELECT cod_sis
FROM TBGF0037
##################################################
##################################################
# TMON Insert Profile
##################################################
Total Elapsed Time = 26.59694 Seconds
User+System Time = 1.656940 Seconds
Exclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c Name
33.5 0.556 1.325 3443 0.0002 0.0004 Class::Accessor::__ANON__
10.3 0.172 0.172 28807 0.0000 0.0000 Class::DBI::Column::__ANO
+N__
9.60 0.159 0.159 391 0.0004 0.0004 DBD::Oracle::st::_prepare
8.39 0.139 0.139 387 0.0004 0.0004 DBI::st::execute
7.79 0.129 0.909 3 0.0431 0.3029 Catalyst::Engine::Apache:
+:MP13::Base::handler
4.59 0.076 0.233 387 0.0002 0.0006 DBIx::ContextualFetch::st
+::_untaint_execute
4.53 0.075 0.140 2 0.0376 0.0701 Class::DBI::retrieve_all
3.98 0.066 1.532 3471 0.0000 0.0004 Template::Stash::XS::get
2.72 0.045 0.295 387 0.0001 0.0008 DBD::Oracle::db::ping
2.35 0.039 0.039 384 0.0001 0.0001 DBI::st::fetchrow_array
2.29 0.038 0.038 391 0.0001 0.0001 DBI::_new_sth
1.81 0.030 0.030 3 0.0100 0.0100 Template::Context::includ
+e
1.75 0.029 0.029 383 0.0001 0.0001 Class::Date::string
1.75 0.029 0.029 384 0.0001 0.0001 DBI::st::finish
1.69 0.028 0.028 774 0.0000 0.0000 DBI::common::DESTROY
##################################################
##################################################
DBI Profile Data (DBI::ProfileDumper::Apache 1.0)
##################################################
Program : /dev/null
Path : [ DBIprofile_Statement ]
Total Records : 3 (showing 3, sorted by total)
Total Count : 1957
Total Runtime : 0.615580 seconds
#####[ 1 ]###########################################################
Count : 1181
Total Time : 0.322572 seconds
Longest Time : 0.052478 seconds
Shortest Time : 0.000003 seconds
Average Time : 0.000273 seconds
Key 1 :
#####[ 2 ]###########################################################
Count : 579
Total Time : 0.286691 seconds
Longest Time : 0.109583 seconds
Shortest Time : 0.000024 seconds
Average Time : 0.000495 seconds
Key 1 :
SELECT ind_excl, ind_cgld, val_sgl, desc_sis, dth_vrfc_note,
dth_crco, nom_prpt, ind_reqr_comp_desenv, ind_reqr_dirt_ctlg
FROM TBGF0037
WHERE cod_sis=?
#####[ 3 ]###########################################################
Count : 197
Total Time : 0.006317 seconds
Longest Time : 0.001895 seconds
Shortest Time : 0.000009 seconds
Average Time : 0.000032 seconds
Key 1 :
SELECT cod_sis
FROM TBGF0037
##################################################
# TMON List Profile
##################################################
Total Elapsed Time = 9.674284 Seconds
User+System Time = 0.934284 Seconds
Exclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c Name
44.3 0.414 0.774 1726 0.0002 0.0004 Class::Accessor::__ANON__
16.7 0.156 0.156 14395 0.0000 0.0000 Class::DBI::Column::__ANO
+N__
8.56 0.080 0.080 194 0.0004 0.0004 DBD::Oracle::st::_prepare
5.03 0.047 0.944 1 0.0475 0.9443 Catalyst::Engine::Apache:
+:MP13::Base::handler
4.28 0.040 0.040 193 0.0002 0.0002 DBI::st::execute
4.17 0.039 0.087 1 0.0388 0.0875 Class::DBI::retrieve_all
2.14 0.020 0.020 1 0.0200 0.0200 DynaLoader::bootstrap
2.14 0.020 0.020 1 0.0200 0.0200 DBD::Oracle::db::_login
2.14 0.020 0.020 193 0.0001 0.0001 DBD::_::db::prepare_cache
+d
2.03 0.019 0.068 193 0.0001 0.0004 DBIx::ContextualFetch::st
+::_untaint_execute
1.82 0.017 0.878 1731 0.0000 0.0005 Template::Stash::XS::get
1.07 0.010 0.010 1 0.0100 0.0100 Template::Context::includ
+e
1.07 0.010 0.010 5 0.0020 0.0020 base::import
1.07 0.010 0.010 192 0.0001 0.0001 DBI::st::finish
1.07 0.010 0.010 192 0.0001 0.0001 Class::Date::string
##################################################
Re: Help prevent a ModPerl application from replacement by Java
by perrin (Chancellor) on Aug 30, 2005 at 03:01 UTC
|
A few pieces of advice:
- Sort your profiles by wall clock time if you want to get something useful from them.
- Class::DBI will hurt you a lot on this test. It's at least 5 times slower than straight DBI code using prepare_cached, bind_columns and bind parameters.
- A straight mod_perl handler would be faster than Catalyst and is more equivalent to a vanilla servlet environment. You'd have to make sure you set the TT part up right though, so don't try this if you aren't familiar with TT's caching.
- DBD::Oracle has a parameter for tuning how many rows to fetch at once. It makes a big difference. Crank it up high.
- Make sure you have MaxClients set at something that prevents your server from using up all the memory and going into swap. That would kill your performance.
Finally, don't feel bad if you don't win. Java is fast these days when used in a lightweight environment like Tomcat. | [reply] [Watch: Dir/Any] |
Re: Help prevent a ModPerl application from replacement by Java
by Joost (Canon) on Aug 30, 2005 at 01:38 UTC
|
ModPerl, Catalyst, Class::DBI, DBD::Oracle, Apache, Template Toolkit and a Session Plugin for Catalyst.
(snip)
For the number-driven monks, Java is answering (on the same machine, while Apache/Perl is not running, and vice-versa for Apache/Perl benchmarking) at 100 queries per minute ratio. With Perl, I don't even hit 27 queries per minute.
Are you actually using mod_perl, or are you running the perl scripts via CGI? Are you pre-loading the modules? How much "ramp-up" time do you allow (mod_perl and java servlets need some time to get up to their maximum speed).
Also: are you using a similar setup in java vs perl. Example questions about the java code: do you use an object-relational mapping system similar to Class::DBI, or are you just pumping out straight JDBC queries? Are you using a complex templating system or pure java servlets? Are you using serialisable sessions? update: do you re-use database connections between requests in both instances (oracle is terribly slow if you don't)
| [reply] [Watch: Dir/Any] |
|
Fellow Joost
I'm actually using mod_perl, as Catalyst provides a content handler.
The Java test was made with just 5 seconds of "ramp-up" time. Perl was running under the same conditions. Is that fair?
My machine shares the Intel Pentium 4 Processor 2.8 GHz and 512Mb Ram with Oracle (nicely tunned to use less memory as usual) with the Tomcat (only when running Java) or Apache/ModPerl (only when running Perl). I know this is not the best situation, but this is what I have.
We're using the same machine to run tests, just shutdown the application (Java or Perl) that will not be tested and run tests against the other.
I don't know a lot about the Java code. I guess its simmilar to the Perl code, but this is only a guess. I'll ask about this to the Java programmer tomorrow moring.
I don't know about the templating system, also. Maybe just servlets, the java application was made on sunday, only for the benchmarks.
My Catalyst::Plugin::Session::Flex is trully serializable (and just hold some integers, anyway).
I choosen to use Apache::DBI to handle the database connections, and load it from my httpd.conf file.
Guess Java is using just one or two database connections, while I'm forced (not sure about "forced") to use a connection per server process.
You gave me nice tips on what to look for at the Java code so I can state if this is a fair benchmark. Thank you very much and may the gods bless you!
| [reply] [Watch: Dir/Any] [d/l] |
|
| [reply] [Watch: Dir/Any] |
|
be suspicious about Template::Toolkit too. Because it gives you the option of flow control in your templates, I wonder you are getting the full advantages of pre-compiled code that mod_perl should give.
Try CGI::FastTemplate instead - it's substitutions are pure regex subs, not as powerful, but plenty good enough for many purposes (you have to shift the flow control logic, if you are using it, somewhere into your application where it will get pre-compiled).
I think that if you are really using mod_perl to full advantage, you will be in a much better situation.
One problem you have is : are you comparing like with like? You have so many modules in there, if you really want to do a language comparison for raw speed, you should start with a very simple, raw perl app that connects to the db, and it's equivalent in java, compare them, then add layers onto each. Otherwise it is impossible to know what you are measuring.
http://perl.apache.org/docs/tutorials/tmpl/comparison/comparison.html is well worth a read.
| [reply] [Watch: Dir/Any] |
|
Template Toolkit is really pretty fast. Since it compiles everything to perl opcodes, it should be about as fast as doing the same operation somewhere else in your code. The only big speed trap with it is using the dot notation to call methods, which will certainly be slower than simple variable lookups. People often do that when using Class::DBI with TT.
| [reply] [Watch: Dir/Any] |
Re: Help prevent a ModPerl application from replacement by Java
by samtregar (Abbot) on Aug 30, 2005 at 05:45 UTC
|
| [reply] [Watch: Dir/Any] |
|
| [reply] [Watch: Dir/Any] |
|
I'd be happy to put Java's JNI head-to-head with Inline::C! Inline::C is superior in every way, in my experience.
-sam
| [reply] [Watch: Dir/Any] |
Re: Help prevent a ModPerl application from replacement by Java
by pg (Canon) on Aug 30, 2005 at 02:34 UTC
|
You might want to give a second thought whether you should use Class::DBI. Even with language like Java (which is famous for its layers of abstration), the new trend of thought is to move away from "too much abstraction and temp object".
I have observed many Java applications, and many of us are moving away from the traditional DAO/DCO model. The level of abstraction of Class::DBI is even a bit higher than Java's DAO/DCO.
Especially with a database SQL-sensitive like Oracle, Class::DBI is a performance hit. Oracle cares a lot how you form your query, and you really should not leave that to a generic tool like Class::DBI.
| [reply] [Watch: Dir/Any] |
|
The profiler data (posted with the original question) states this. But I'll have a hard and dull drawback on not using it: my column names and table names are not intuitive nor easy to guess. This would be a pain to write SQL to them. But it seems better than dropping Perl in favor of Java.
My hope is not need to do this. Maybe someone have a good suggestion that allows my Perl to fly...
| [reply] [Watch: Dir/Any] |
|
Maybe it's just me, but I do think that (as soon as the database server allows that) all SQL should be in stored procedures. Snippets of SQL scattered throughout the Perl (or VB or C or Java or whatever you use) code are a major PITA as soon as you have to make some changes in the database schema. Plus this allows the server to store the execution plans which is even better than caching them.
Jenda
XML sucks. Badly. SOAP on the other hand is the most powerfull vacuum pump ever invented. |
| [reply] [Watch: Dir/Any] |
Re: Help prevent a ModPerl application from replacement by Java
by strat (Canon) on Aug 30, 2005 at 06:56 UTC
|
if your code always connects to the database with the same user credentials, try (instead of Class::DBI) Apache::DBI + DBD::Oracle; if not, try DBI + DBD::Oracle
Best regards,
perl -e "s>>*F>e=>y)\*martinF)stronat)=>print,print v8.8.8.32.11.32"
| [reply] [Watch: Dir/Any] |
Re: Help prevent a ModPerl application from replacement by Java
by jacques (Priest) on Aug 30, 2005 at 06:48 UTC
|
I must also express surprise at your use of Class::DBI in a speed race. Not a good idea. | [reply] [Watch: Dir/Any] |
Re: Help prevent a ModPerl application from replacement by Java
by nothingmuch (Priest) on Aug 30, 2005 at 14:54 UTC
|
Two things:
- CDBI is slowing you down
- You should run the benchmark once, throw away the results, and then run it again on the already running server (for both mod_perl and the java stuff)
To deal with the first point...
Rose::DB::Object is supposed to be a good performer for when you really need it. I know nothing more about it.
44.3 0.414 0.774 1726 0.0002 0.0004 Class::Accessor::__ANON__
16.7 0.156 0.156 14395 0.0000 0.0000 Class::DBI::Column::__ANO
+N__
I think that Class::DBI's multitude of helper's triggers, accessors, meta data and what not is just too high latency for you.
If you are inserting data plain and simple, and you already did the form validation or whatever, then is there any reason (since you're working on performance) not to prepare an insert operation, and then just execute the statement handle on the values directly, without going through Class::DBI's abstractions?
For creating objects this may be a smart move, since Class::DBI is not designed for efficient aggregate operations, but for convenient use of a single objects and it's relations.
Class::DBI has the concept of essential columns. This could help you a lot with your fetches. Instead of runnning:
select id from table where id = ?;
to check if the object exists (what's your primary key column, btw?), and then running
select field_name from table where id = ?;
for each field, it would simply do
select id, field1, field2.... from table where id = ?;
to begin with. The only downside is that if you don't use those columns eventually, the fetch will be for nothing (wasting memory and sending some unnecessary data on the socket to oracle, and making oracle fetch the data without need). The benefit is that if you do need it, the number of queries is reduced by an order of magnitude, and this usually means a huge speed increase.
Class::DBI::Sweet has prefetching by means of joins, which is a bit like making a relationship essential. This considerably helps with fetching latency. You didn't mention relationships, but this could help if you do have them.
These two together may help reduce this:
4.28 0.040 0.040 193 0.0002 0.0002 DBI::st::execute
Using transactions might help make inserts and updates faster (or slower) by changing the way data is synched to the disk. CDBI's docs have a nice snippet for doing this easily. When AutoCommit is on I think that DBI might be starting a transaction and finishing it for every operation, and that might be hard on the DB.
Class::DBI's autoupdate may be causing you griefe - try turning it off.
As for the second part:
nearly 15% of the app's time is profiled as purely startup time. If you add the way catalyst lazy loads some objects, the template toolkit compiles templates (Did you give it the COMPILE_DIR option, btw?), and so on and so forth, I think you could safely double that:
8.56 0.080 0.080 194 0.0004 0.0004 DBD::Oracle::st::_prepare
...
2.14 0.020 0.020 1 0.0200 0.0200 DynaLoader::bootstrap
2.14 0.020 0.020 1 0.0200 0.0200 DBD::Oracle::db::_login
My point is that if so much time (relatively) is just startup, you are not running the benchmark late enough in the program's life.
I suspect that '_prepare' is taking very long for the initiail queries (parse, validate against schema, create sth, etc etc), and that cached queries are actually taking maybe 5% of those 8%. I would look into it. Maybe dprof can let you see the max time, and maybe group the calls into time scales (under 0.0001, under 0.001, under 0.01, etc) - and if you have many fast calls and a few slow calls, then this means you just need to run it a bit more.
Furthermore, IMHO 'prepare' is being called way too much if all you are doing is two operations on the same table, a number of times - select and insert. Using your own SQL should solve this problem, as I mentioned above.
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
Rose::DB::Object is supposed to be a good performer for when you really need it. I know nothing more about it.
I do :) Yes, Rose::DB::Object is many times faster than Class::DBI during almost all operations, but plain DBI is, in turn, many times faster than Rose::DB::Object.
I recently posted the results of a comparative benchmark of several RDBMS-OO mapper modules, plus "plain DBI." Although Rose::DB::Object is the fastest RDBMS-OO mapper module by a significant margin, plain DBI is the fastest alterative by an even larger margin. The fact that DBI is the fastest is probably no surprise, but the size of the margin may be.
| [reply] [Watch: Dir/Any] |
|
|