Before I describe my situation, let me mention that I am not a web developer or a professional coder and my site is a hobby.
I run a large auction site. I wrote the entire engine from scratch -- 15,000 lines of PERL with HTML::Template as the presentation layer, PostgreSQL on the backend, served by Apache 1.3.29 on Debian (testing).
My code is somewhat of a mess because I've been learning as I go over the years and without coding experience prior to perl, grasping OO is a huge leap. I'm rewriting my code so in an OO way bit by bit so that I can eventually use mod_perl.
I serve approximately 30,000 pages per day (filled with photos and thumbnails for auctions) on a dual 1.5ghz AMD Athlon machine with 2gb RAM and have an average server load of perhaps 25%. During extreme peak times, it might reach 60% but only for a short duration.
There's really no reason not to use mod_perl unless you're just not sure that your code is up to snuff. Even then, you can run in a modified mode which will still give you some speed benefits to the tune of four or five times.