I recently replaced my old Core2duo Mac with 4GB and OS X 10.6.8 with a 4-core i5 with 16GB, an SSD and 10.7.2.
The new system is wonderfully fast for everything except mysqld. It's OK with light loads but when I give it some real work to do, it behaves in a very peculiar way.
I've tried mysql-5.1.59-osx10.6-x86_64 and mysql-5.5.16-osx10.6-x86_64 with similar results.
The workload is a batch file that takes a large database we get from a supplier and manipulates the data into a form we can use in our app on the production servers. On the old Mac it took 3 to 4 hours to run.
On the new one it runs fast for a while and then bogs down, after which each query runs amazingly slowly with trivial IO and the CPU pegged at 100%, on one core (the batch file runs only one query at a time through the regular mysql cli client). Other apps also slow down badly and use a lot of CPU even though, according to top, there's 10GB inactive memory.
For example, right now mysqld is running
select t.t_id, a.a_id from t inner join a on a.x=t.x into outfile;
This query has been running for about an hour while it took only 4 minutes on the old system. It's using 100% cpu on one core and it writes a 1MB block to the outfile every minute or so. Using iosnoop I can't see any reads from either table a or t (both MyISAM) so I guess they are cached in VM. The relevant index was preloaded in the MyISAM key buffer. So there's definitely no IO bottleneck. And yet this new system is much slower (~20x) than the old one.
I've got the 5.5 performance schema but I don't understand it. And I have dtrace but I'm not really competent to drive it other than using the given utilities like iosnoop.
What could possibly be going on? What could I do to uncover relevant info?
EDIT: This is all my.cnf has for mysqld—defaults for everything else.
[mysqld] datadir = /Users/fsb/mysql port = 3306 socket = /tmp/mysql.sock key_buffer_size = 1536M performance_schema = ON