One of first things I noticed, when I tried to run some 5.0 tests, was that INSERT speed was much slower (no benchmarking at that time, purely subjective speed that loading the dump was somewhat too slow).
After discussing at evening, and some coding in the morning I built paramy – a simple C++ utility, that reads .sql stream, and spreads execution of statements over multiple worker threads.
For benchmarking I took a simple 13m row, 1.6G data, 1.7G index table. Loading it with standard mysql client took 30min (7200r/s). Paramy unfortunately hit auto-inc lock, and didn’t provide too much of performance – data load took 25min (8700r/s). The win here was mostly because everything until InnoDB being parallel, as instead of 100% core use, mysqld went up to ~130%, so it obviously managed to get additional cycles from SMP.
Dropping AUTO-INC from the field description changed everything. Standard mysql load wasn’t much faster, but under paramy load mysqld managed to use ~320% of CPU core, and did the load in 10min (~21500r/s).
The interesting part – no INSERT asks for AUTO-INC values, so in theory one can just disable the locking during the load, and re-enable it same way as unique or foreign key constraint checks are re-enabled after import of data – that way loading of data into InnoDB would be much much faster, especially with many more cores – though then contention moves away to transaction log mutex.
Additional way to reduce contention inside InnoDB may be removing both reading and writing checksums- they have quite big CPU load share. Disabling this at least during bulk imports can be quite useful.
Oh, I mentioned the subjective feeling that 5.0 was slower. I finally benchmarked – 4.0 did the data load in five minutes, and went over 40000 rows/s. I hope this is the last test where 4.0 is twice faster than 5.0.
domas mituzas 
5.0 uses much more CPU for insert statements because of the parser. At least half of the regression was fixed in 5.0.54.
Mark, this was 5.0.64 – and profiling was showing mostly innodb internals. I’ll experiment a bit more with profiles.
Looks slow from my tests too, and it’s v5.1.23 in my case.
By waiting for 5.0.64, you fortunately have avoided the pain that ruined a weekend for me. I haven’t tried to compare MySQL4 with MySQL5 since then. There is more overhead in InnoDB because of the COMPACT row format, and even if you don’t use it, you pay the CPU cost. The best way to see that cost is to run without concurrent inserts so that you don’t get any overhead from SMP issues in InnoDB. On the bright side, the COMPACT row format reduced my database size by 20%.
I spent another weekend removing support for the COMPACT row format from InnoDB, but I decided to not submit the patch. I was able to reduce the CPU overhead.
Mark,
My profiles showed a lot of work done in checksums (calculating both old and new checksum). I’d gladly have entirely disabled checksums during initial data load – and enable calculation of them later on.
Do you still have your COMPACT patch? };-) I’m not sure we should care about 20% win for our largest tables, as they sit quite cold anyway.
How many CPU cores and how many concurrent sessions did you use? pthread functions should use the most time on a 4 or 8 core server with many sessions.
4 core server, 8 sessions. pthread was nowhere near a bottleneck.