Database selection used to be an easy topic but in current development it is common to use multiple database technologies to match the usage patterns with the database strengths.
Relational databases are great for queries and low-volume writes. The transaction rates can quickly saturate and poorly written queries can sometimes hammer them. In addition commercial database licensing schemes are not conducive to lots of active sessions or load-balancing.
The key/value databases are some of the easiest to incorporate, have high-performance, but no query capability. There are a lot of options for different performance trade-offs, eventual consistency, and persistence.
Newer document databases like MongoDB appear to be promising for highly scalable applications with some query capability.
It is better to pick representative products and use the best database for the job, but abstract the API in a way that makes it easier to migrate when the requirements or usage patterns change.
Cassandra is another extremely high performance database for key/value stores. It is excellent for loading large data-sets but lacks good query capability. Perhaps the collection based queries will someday go beyond just one dimension.
Different performance based on usage patterns and the database used. A bad match or poor implementation can bring any installation to a crawl, so the most important thing is to benchmark and monitor performance.
Berkeley DB is one of the oldest file-based key-values stores. It is good for large data sets that need to undergo frequent full table scans. Low overhead and speed are great, but better solutions exist given most any other set of requirements.
Memcached was adopted with some skepticism because how could it be an improvement and lack of persistence makes it a little odd, but it is great for storing short life temporal cache data like shared access to sessions from a load-balanced cluster of application servers.
MongoDB is currently running in our production cluster, and has been spec'd for projects where the data usage patterns were a good fit.
MySQL is an open-source database that can perform well in certain situations. Since it is one of the oldest open-source relational databases it brings some baggage but the support is pretty broad.
SQL Server is an excellent database product. The express version is free, easy to manage, and can handle a big load and rock solid. The performance and reliability is better than MySQL.