I personally use mod_php on all my servers because,
Fast. Unlike CGI, it does not spawn another process to serve the request, which everyone in the Un*x world knows is an expensive task. Not just forking and context switching, but also initialise the environment and PHP parser, etc.
Persistent DB connection. It might not be that noticable using MySQL, but with other DB backend, making the connection is actually time consuming. PHP module provides a DB connection pool on each Apache process.
However, PHP module does have some problems. Sometimes the garbage collection is not working properly, and I end up have 20Mb Apache processes lying around. CGI version can also work with suexec so that your PHP scripts can run using your own user ID. It provides more security I guess because some sensitive data (password to the DB for example) does not have to be readable by other users.