We've recently run into similar problems. Actually we moved a site from CGI to mod_perl using Apache::Registry. Anyway, some of our scripts caused the memory usage to skyrocket, thus triggering extreme swapping to disk, thus making the server unusable. We basically had to turn off mod_perl, then turn the scripts on one-by-one to find out which one was using the memory. When we found that, we manually looked through the script looking for suspicious bits of code. What we found was shocking (Disclaimer: I didn't write the code).

The guy before me had written a wrapper around DBI so he called functions like sql_arrayref() which would prepare, execute, and fetch all the rows returned into a huge array reference and return that. So yeah, he was copying to memory the entire result set. Obviously the solution in this case was to buffer the results and use them one by one.

So yeah, our solution was to find the scripts that were causing the problems and manually examine them for whacky memory usage.

Another problem we had was severe memory leaking in some scripts. So the memory wouldn't be released back to the OS (only back to the current httpd process if I understand correctly) after it used a whole bunch of memory. So if a few of these scripts were hit around the same time, the memory usage would skyrocket and the processes would still hang around, thus tying up the memory. Disclaimer: I'm not a sysadmin, but from what I gather, he temporarily reduced the time an http process could stick around, thus getting the memory back more quickly.