Mysql – How to enable /dev/shm RAM disk for MySQL

For PHP there are 2 important things that will increase capacity:

1. Advanced PHP Caching (APC) as was mentioned. This is what we use at Yahoo!. There are other similar projects, but this one is Rasmus' baby.
2. FastCGI instead of mod_php. There is debate on this issue as mod_php is usually the fastest. However, I would assume that you have a single Apache server delivering both dynamic PHP content and static assets (JS, CSS, flash, images, PDFs, etc.). The requests for those static assets do not need to consume as much memory, but because PHP is a module it is in every Apache thread.

For Apache:

1. Use worker MPM
2. Enable KeepAlive

You may also go so far as to consider switching from Apache to Lighttpd, or Nginx. I love Apache. I use the fool out of many of it's advanced features. I accept its overhead because I need what it offers. For the common LAMP stack, it is more than is needed and a waste of resources.

For MySQL:

1. Your optimization efforts will payoff 10 fold when spent analyzing and correcting queries, instead of tweaking your my.cnf values. I'm not saying that it's not important to get your caching, connections, etc. correct... but for most people it is only 9% of the problem.
2. During your QA, turn on the general query log on your staging/dev mysqld to capture all queries sent. (Do NOT do that on your production mysql server!)
3. Use EXPLAIN to analyze the queries. Especially if you are using a framework with an ORM (abstracts away the DB and keeps you from writing your own SQL) you will need to clean out extraneous JOINs, SELECTs with no WHERE clause, ORDER BYs that induce 'using filesort', and queries that use no indexes.
4. If you are using MySQL 5.1 take advantage of the query profiler.

Other tools worth considering are mk-visual-explain

I've cited 10 great references. These things ought to get you humming. Please lets us know how it turns out.

If you're using InnoDB, the most important variable to set is innodb_buffer_pool_size. I would set it to approximately 80% of your system memory. Once you're caches warm up after some usage, your most active data (working data set) will be in memory (innodb_buffer_pool_size) and your operations on it should be very fast. With 64GB of memory, you can definitely fit a lot in there. Memory is always a good buy for DB servers.