Jason Czerak wrote to [EMAIL PROTECTED]:
> >
> > In fact, if I use this cache system and disable all parsing (i.e.,
> > just use it to include straight HTML into mod_perl apps), I can serve
> > 150-200 requests/second on the same system.
> >
> > With my parsing regexps enabled, it drops to 50-60 requests/second.
>
> First. I would love to know what kinda machine your using here. :)
It's part of a testing environment. Nothing fancy... Lightly loaded
single CPU PII-400MHz, 512MB RAM, Adaptec Ultra160 SCSI (no RAID),
FreeBSD 4.4-RELEASE, with some in-house kernel patches applied, latest
MySQL, mod_perl, modules.. It's great for testing, because when we
move things to the REAL server (more power, more load), I know they'll
be at least as fast over there and won't crack under pressure :-)
The database this runs off of has several tables. The smallest has
about 15K rows. The largest is pushing 300K rows.
> 3rd. my sql queries are not the most optimized and mostly tossed
> together. But then again my developemnt tables are not that big at
> all.
This might be one of your largest areas to improve upon. Thanks to
practical and theoretical experience with RDBMS', I write fairly well
optimized SQL when I "throw things together", but I recently rewrote a
JOIN in one of my applications that was "fast enough" and doubled the
observed performance (oops.. should have noticed that the first time).
Check your table design and indexing, too, as these are areas which
are tremendously important, that many people overlook.
Even a development table that is "not that big" might be a few
thousand rows. If your query is poorly designed, and your DBMS has to
read every row in the table, you're going to take a noticeable hit
over a number of queries. Worse yet, if, for example, you're taking
the cartesian product by mistake (this is easy enough to do), pretty
quickly you've got hundreds of thousands of rows to deal with. :-)
I guess what I'm saying is, never underestimate the importance of
carefully constructed SQL.
> Keep in mind that my times are based also on the time it takes to
> make the transmission of the data from server to client as well as
> the time it takes to compile the template and do sql queries. I"m
> not sure if your times are factoring that in. Locally times are
> .08/sec but remotely from T1 to cable times are .16/sec to .20/sec
> depending on network conditions and size of html file at the time
> naturally. Some of the html files can be about 50K in size we
> found.
Generally when testing application performance, it is wise to
eliminate highly variable things like network performance. Network
importance might be significant in your application deployment, but
you can test that in isolation with the many great network tools out
there.
> Also, pages will do about 3 sql queries on average plus queries to
> the Apache::Session state information.
Take advantage of query caching... Instead of
$cursor = $dbh->prepare('SELECT * FROM yourtable WHERE foo = bar');
use
$cursor = $dbh->prepare('SELECT * FROM yourtable WHERE foo = ?');
$cursor->bind_param(1,"bar");
This way, your SQL database will cache the queries so that they do not
have to be parsed and examined each time. Note that this does NOT
cache the results of the queries. You can do that, as well, but can
lead to nasty surprises.
Also, try to limit the total number of SQL queries you make per
request. Take advantage of joins, group by, etc, if you can.
> Do these numbers sound right? There are places were I could move
> from doing SQL to keeping some data in Apache::Session. but it
> would be small speed improments unless the catagories are very
> deep, I do have recursive SQL queries inplace for some things.
>
> Your talking 50 to 60/sec as slow. So I dunno what is 'fast'.
I "optimized" a few things wrt. the parsing and am now seeing
110-115/sec pretty consistently. IIRC, this server won't handle much
more than that without SQL.
- Ryan
--
Ryan Thompson <[EMAIL PROTECTED]>
Network Administrator, Accounts
SaskNow Technologies - http://www.sasknow.com
#106-380 3120 8th St E - Saskatoon, SK - S7H 0W2
Tel: 306-664-3600 Fax: 306-664-1161 Saskatoon
Toll-Free: 877-727-5669 (877-SASKNOW) North America
