What do people use to gather metrics for their site? Do you have any open source recommendations?
I would like something based on php + mysql, easily extensible and in addition to getting UX usage information, I would also need to know how my product gets used in the backend. Database and web server metrics is also something I would be interested in garnering.
Please advice.
Pretty much only crap out if you compare it to non-open-source solutions (that can also be free). I think so far dominant is google analytics - which is pretty nice and has an API. It is not a full replacement, though. I also use (non-free, windows based) smartertools SmarterStats - mostly to be able to get processing times, as well as error code references. Google analytics, by virtue of being page embedded, can not register errors appearing on the server side.
I would suggest you have a look at google analytics.
Regarding database and server metrics, you are quite out of luck. I know a number of tools for that, but they all are non-open AND quit expensive and primarily target larger installations. They also dont integrate with every technology - mostly based on some for example database servers not really making a lot of information available to work with.
The most popular open source web analytics software suite is Piwik, which is based on PHP/MySQL.
But in their own words "Piwik aims to be an open source alternative to Google Analytics."
Google Analytics (GA) is very mature and supports enterprise-grade analytics. You should definitely start with GA due to its ease of implementation and given the rich range of analytics reports you'll get in return.
Some users prefer to analyze their web server logs and there are various applications available to do this (like your SmarterStats). AWStats is the most popular open source package used to analyze web server log files.
For monitoring MySQL, check out mtop and kontrolbase.
Regarding web server metrics, assuming you're using Apache, you could try installing mod_status, which provides a useful stats page.
Advice: You are asking for a lot, so no single solution will meet your needs. Try Google Analytics + mtop + mod_status to get a balanced view of front and back end.