For my uses, mod_perl has always been primarily a source of performance increases in applications. I personally, haven't used some of it's more Apache-related aspects. So with that said, I won't really comment on the administration side of using mod_perl.

What it does for application programmers, which seems to have been largely overlooked in your statement, is greatly increase the efficiency of using Perl for web applications. Rather than having a perl process started, loaded, etc etc each time a request is made to the webserver, mod_perl allows the perl interpretter to be embedded in Apache. This also provides an effective way to cache scripts and modules, and allows for persistant data.

Just as one may write an application acting as if there were only only database, one could do the same for CGI frameworks and only use mod_perl. However, this is unfair to those on other OSes or platforms.

(Assuming you meant "only one database" ;) ... The only real dependancy mod_perl draws on is Apache. A web application can be easily written to use either CGI or mod_perl (that is, if it doesn't need to directly use Apache's resources with mod_perl.. in which case you don't have many other options anyway ;). In most (of the few) cases, it is simply a matter of something like if ($ENV{MOD_PERL}) {} else {}. A classic example of this was the printing of HTTP headers. When using mod_perl, the programmer would make use of Apache::* methods such as send_http_header(), and header() with CGI. CGI.pm is getting more and more mod_perl friendly though. Using this same example, CGI.pm now checks in certain circumstances whether or not it is in a mod_perl environment... and you can use CGI::header() either way.

Well, i've probably strayed far from what you are asking... and I'm sure it's obvious that I am a mod_perl advocate ;) but I hope this somewhat helps you to decide whether or not mod_perl is suitable for your needs.