A friend of mine runs a hosting business, and has a number of clients with data centric web applications. This isn't a problem yet, but, could grow to be one. He asked for advice about how to deal with the situation, and unfortunately, I'm lacking in advice - my background is in MS SQL Server.

Obviously, the best lever available for better perf, is better code. But that's not an option. He can't just tell his customers to rewrite their database architecture, especially since a lot of this is 3rd party scripts. And the next thing I would normally suggest is buying 40 drives and building out some nice RAIDs, but, again, that's not really an option in this case.

Some Googling brought up memcached (wiki), and it sounds promising, sound of principal, etc, but with no hands on experience, I don't know if it really works, how difficult to set up, if there are better options, etc. Can anyone suggest any tricks that might eek better performance and scale out of MySQL when queried by PHP?

If you have MySQL installed on it's own server (by itself, meaning just MySQL, and the O.S itself) that will render the best performance, bar none. A fast server (speaking of the hardware in the pc) also helps as well.

If you would host applications that are potentially badly written you better use the most powerful hardware available rather than trying to implement software solutions.

Memcache is useful only when it is controlled by the application. If you use it to store results of all queries made it may (and most probably it will) do more harm than good.

I agree with Mike here.
Memcached can help, but it's more towards the application layer than the data storage.

Which version of mysql does your friend use ? And with which table engine?
On which platform (win32, or linux/bsd? And if linux, which distribution ?).
If the server(s) are on linux/bsd, start by installing mytop [ http://jeremy.zawodny.com/mysql/mytop/ ] to monitor the server, and enable the "slow queries" loggin in the mysql configuration file my.cnf [ http://dev.mysql.com/doc/refman/5.0/...query-log.html ]
Look here for tips on identifying the slow queries sent to the db: http://techgurulive.com/2008/08/25/h...-slow-queries/

And what kind of disk subsystem does the server (servers?) uses? sata, scsi160, fiber channel?
I know for example that partitions support was introduced only in 5.1

If a multiple servers solution could be envisaged, and given that there are more select than update/inserts, a couple of server could be clustered and replication could be implemented.
This would allow your friend to level the load on several machines, and give him a bit of redundancy.

I don't know if it really works, how difficult to set up

Simply, you start the service, and then you implement in your application a call to the server in the form of this pseudo code:

if(memcacheSrv::check(key) exists){
  data=memcacheSrv::fetch(key)
}
else{
  data=query_db
  memcacheSrv::put(key, data, ttl)
}

This would mean that all the application of your friend customer should be modified to implement this.
Not really feasible in my mind.

Last edit :-)
I just found this site: http://www.mysqlperformanceblog.com/ when I was looking in a mysql inc slide about performances.
It might gives you pointer and help your friend too.