Reddit reviews High Performance MySQL: Optimization, Backups, Replication, and More

Your database is (most likely) going to be your bottleneck.

Get High Performance mySQL - it's a great book that'll help you make smart design decisions. Make sure you get the 2008 edition and not the 2004 edition.


Check out Percona Server, a more performant fork of mySQL 5.1 server.

Depending on your Database load, you should look into mySQL Master/Slave Replication. Maatkit has lots of useful tools for monitoring/backing up mySQL Replication.


Like some other commenters have mentioned, check out Memcached. Make sure you use pecl-memcached for accessing Memcached from PHP. There is another extension, pecl-memcache, but it's missing CAS token support, which helps with concurrency.


Make sure you build pecl-memcached with the latest version of libmemcached. Older versions have a nasty bug that ignore timeouts, so if your memcached server goes down, your PHP processes will wait forever.


Use Nginx and PHP-FPM (it comes with the latest version of PHP). Nginx uses so much less memory than Apache. I have a Nginx Server that is pushing 250mbps of traffic and uses less than 200MB of memory. PHP-FPM uses (about) the same amount of memory as mod_php, but gives you way more control.


You also might want to check out the various NoSQL databases and see if any of them fit your work-load. It sounds like Redis might be a useful tool for you, but YMMV- you'll have to check it out and see if it fits your needs.

In this order:

1. PHP for the World Wide Web
   
2. PHP 6 and MySQL 5
   
3. PHP Objects, Patterns, and Practice and/or PHP Object-Oriented Solutions
   
4. High Performance MySQL: Optimization, Backups, Replication, and More
   
5. Introduction to PHP PDO
   
6. Model-View-Controller MVC

You don't know even half of MySQL until you you've read: http://www.amazon.com/gp/offer-listing/0596101716/ref=tmm_pap_new_olp_sr?ie=UTF8&condition=new

I'm not too sure on your level of knowledge, but this book is pretty good for the intermediate level. Plus it's an O'Reilly book, so you get a sweet picture of an animal on the cover.

http://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/dp/1558605762

http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557

Yes, the name of the first one is horrible. It's a fantastic book.

Since you're talking MySQL, you might also want

http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/0596101716

Percona has more stability and better performance. It backports many features from MySQL 5.5 and has quite a few additional features. It's developed by the authors of the MySQL Performance Blog and High Performance MySQL. It remains API compatible so you can use the standard mysql client libraries, as most of the differences are internal, making transition very easy.

We switched to Percona at work recently, after being introduced to it through the XtraBackup utility that they distribute. This is one of the best innodb hotbackup tools I've seen, particularly for the price of free.

I haven't read it yet, but this book is about building/managing large scale databases.