I am interested in finding solution for visualizing the performance of our web servers and database servers in real-time (or as real time as possible). I have seen some stuff for the MS Stack but I am actually interested in finding a nice solution for the standard LAMP stack.

While I have no advice on the database side, there are cricket scripts to pick information directly from 'mod_info' apache module. For databases, there are a large number of metrics of interest, and likely depend heavily on vendor (oracle vs mysql etc). On the OS side, cricket has plenty of scripts to support basic CPU, Memory, etc too.

While cricket seems to have a large installed base, there doesn't seem to have been any major core development in a while, so try comparing it to other solutions before you commit to a solution.

Depending on what information you are looking to collect and how you'd like to report on it, collectd (http://collectd.org/) and a decent rrdgraph wrapper (I use my own slightly modified version of http://haroon.sis.utoronto.ca/rrd/scripts/) may provide what you are looking for. The rrd.cgi pages contain decent examples of the output.

The setup is very manual, but once configured I've found it to be solidly stable.

Maintaining your DB can be a full time job. I wouldn't try finding a single tool for the LAMP stack. You should focus on getting a great tool for MySQL and a great tool for Apache.

I am a MySQL Enterprise Gold subscriber and I love love love their MySQL Enterprise Monitor. It really makes my job a pleasure. You also get excellent customer support. They are very fast to respond to any question, and the level of expertise is great. Until I found serverfault.com, I used to create low priority tickets just to "bounce ideas off of them" the response time was far better than any mailing list, and unlike IRC you can trust the advise.

We also purchase RedHat support, but I never use it. The MySQL support is money well invested.

Zenoss (www.zenoss.com/) is a great solution for small shops ( <= 50 servers). You just have to install an snmp agent on your machines and simply point zenoss there. It is part of the new generation monitoring systems and it is largely based on Nagios and Cacti. So, very simple to manage from GUI and you can get graphs, alerts and syslog in-a-box. For larger server installs , where one has to deal with complicated configuration management and inventory integration, you might want to take a look at Munin or Collectd.
Worth mentioning that Collectd is focused on high resolution collection (i.e. near real time ).

Most people, including me, have some sort of data collection tool and a web presentation interface for data points pulled from the equipment every 5 minutes to see long-term trends and use tools like top, htop, apachetop, mytop , foo-top , dtrace/systemtap for live profiling.