MySQL MyISAM INSERT performance slow on Mac OS X

Question

I am suffering from abysmal INSERT times on my MacPro (quad-core 6G memory) running mysql 5.1.49 on snow leopard 10.6.4. I tried to figure out where the problem is and finally came to the conclusion that it must have to do with Mac OS X.

Here is the test I ran. I have an old dell with a single pentium cpu and 256M memory and mysql 5.1.44. The ip2nation.sql file contains distinct insert lines like this

INSERT INTO ip2nation (ip, country) VALUES(0, 'us');
INSERT INTO ip2nation (ip, country) VALUES(687865856, 'za')
...

Inserting those over the network works pretty well

time mysql -h dell test < ip2nation.sql

real    0m22.352s
user    0m0.587s
sys     0m1.556s

running the same test against my mysql database on my mac and the result is mind boggling

time mysql test < ip2nation.sql 

real    23m46.254s
user    0m1.199s
sys     0m1.808s

Both commands are run from OSX. When I change the format of the SQL INSERT statements to

INSERT INTO ip2nation (ip, country) VALUES
(0, 'us')
,(687865856, 'za')
...

times on OSX were a lot better

time mysql test < ip2nation.sql 

real    0m1.183s
user    0m0.021s
sys     0m0.009s

Can anybody explain this to me? I can't rewrite all my applications to use this form of INSERT statement.

Answer 1

Sounds like table locks. Did you check?

Answer 2

It's obviously doing something 'bad' on every row - and given the slow speed almost certainly disk related (which would explain the lack of any improvement from old Pentium). You might be better off with mySQL support forums on this one. Gut feel - you may be better off putting your data files onto a more classic Unix filesystem.

Answer 3

The solution to my problem was the config in /etc/my.cnf

sync_binlog=1

I have no idea how it got there, but taking it out or setting it sync_binlog=0 certainly helped.