How can I tune MySQL to behave better under load We have a typical master-slave set up. When we find ourselves under considerable load, the master grinds to a halt. The slave continues to march along all happy like.

So during a recent slow-down, I took a VERY simple query (no joins, nicely indexed, not many results) and ran it on the slave and master, with profiling turned on.

Notice how different the results of the profiling are.

Master


Slave


I fear someone might tell me: "Of course your database will perform worse when it's under heavy load." And to them, I say: "You're right!" 

But I am really curious if the results from the profile would suggest anything like:


Your DB server needs more memory, or
It's writing to disk too much, or
Change this setting and everything will be fixed, or
...


EDIT 1
The following graph shows how queries slow down when we're having a problem. The gold line is average write time and the green line is average read time. As you can see writes get really slow. The average write peaks at around 500 ms. When things are running smoothly, average writes peaks are down around 2ms.
If you stay with MySQL 5.1
Since you cannot upgrade at this time, you need to install the InnoDB Plugin. It was available since MySQL 5.1.38 (See the Release Notes). I wrote a post about it : MySQL - Installing InnoDB Plugin
Surprising fact: Percona already ahd the InnoDB in Percona Server 5.0.45 long before
Once you do, you need to set the following

Set innodb_read_io_threads
Set innodb_write_io_threads
Set innodb_thread_concurrency to 0

You can increase the innodb_buffer_pool_size, but do not exceed half the installed RAM. Set it to 20G.
When you upgrade to MySQL 5.6
#1: Tune Read and Write I/O
InnoDB has new options to engage multiple CPUs. I wrote about this over the past 3 years

Jun 01, 2012 : I&#39;ve got 16GB of ram, how should I configure MySQL Server?
May 07, 2012 : MySQL Server Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

#2: Partition the InnoDB Buffer Pool
Since MySQL 5.5, you can partition the InnoDB Buffer Pool to reduce I/O thread contention and swapping

Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?

#3: You can snapshot the InnoDB Buffer Pool to Disk
You can use the following

innodb_buffer_pool_dump_at_shutdown : MySQL dumps the map of the Buffer Pool on Shutdown
innodb_buffer_pool_load_at_startup : MySQL load the Buffer Pool on Startup
Setup a script to run SET GLOBAL innodb_buffer_pool_dump_now= 1; in a crontab job to dump the Buffer Pool map every 5 minutes or so. See innodb_buffer_pool_dump_now

There is so much more I could write, but I'll stop here.
CAVEAT : Why is this tuning necessary ?
See my post Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
I have mentioned the Plugin for InnoDB Before : Performance settings for MyISAM tables - keep everything in memory
GIVE IT A TRY !!!