Benchmark your application and database to find out where the bottlenecks are. After fixing one bottleneck (or by replacing it with a “dummy” module), you can proceed to identify the next bottleneck. Even if the overall performance for your application currently is acceptable, you should at least make a plan for each bottleneck and decide how to solve it if someday you really need the extra performance.
For examples of portable benchmark programs, look at those in the MySQL benchmark suite. See Section 7.12.2, “The MySQL Benchmark Suite”. You can take any program from this suite and modify it for your own needs. By doing this, you can try different solutions to your problem and test which really is fastest for you.
Another free benchmark suite is the Open Source Database Benchmark, available at http://osdb.sourceforge.net/.
It is very common for a problem to occur only when the system is very heavily loaded, even if other aspects of the system were tested extensively. These performance problems are typically due to issues of basic database design (for example, table scans are not good under high load) or problems with the operating system or libraries. These problems are much easier to fix if isolated during pre-production testing.
To avoid problems like this, benchmark your whole application under the worst possible load:
The mysqlslap program can be helpful for simulating a high load produced by multiple clients issuing queries simultaneously. See Section 4.5.7, “mysqlslap — Load Emulation Client”.
You can also try Super Smack, available at http://jeremy.zawodny.com/mysql/super-smack/.
As suggested by the names of these programs, they can slow down a system tremendously, so make sure to use them only on development systems.