Comments on Deva's MySQL / Linux blog: Tokutek challenge vs. 128GB RAM

Thanks Mark and Bradley for the insights. Regardin...

2009-01-19T13:14:00.000-08:00

Thanks Mark and Bradley for the insights. Regarding log configuration:

innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

I am using O_DIRECT and disabling the HW write-cache because this particular model of RAID controller (MegaRAID SAS 1078) is known to have failures in the battery backed write cache. It's a nightmare when the RAID controller fails and all the data in the write-cache is lost -- and difficult to piece back together if you have slaves that already replicated some of the lost data.

I would be very interested to see the difference Percona's patches make, but this server is now in production so I can't run another test on it at this time. If I'm able to in the future, I'll post those for comparison.

That is nice hardware. Thanks for the writeup. In ...

2009-01-19T12:33:00.000-08:00

That is nice hardware. Thanks for the writeup. In addition to the slowdown from the memory system, I wonder if there was a slowdown from flushing dirty pages. You used O_DIRECT rather than buffered IO so writes are likely to see disk latency rather being done quickly to the OS buffer cache -- ignoring the impact of a HW RAID write cache. And you used unmodified MySQL so there is 1 thread that uses sync IO to flush dirty pages. And InnoDB will try to flush ~100 pages per second, even though your hardware can do ~800 writes per second.

When there are too many dirty pages, user transactions will be delayed. There are two metrics by which there are too many dirty pages. The first is when max dirty pages is exceeded and that is not likely here. The second is when there are pages with the min LSN at which the page was dirty is near the min LSN in the transaction log file. And this is likely to be the case because the max size all tx log files is 4G.

So, how many log files do you use and how big are they?

You might be able to make this run faster by using the largest possible log files (2 X 2G), a Percona build with support for more background IO threads and setting innodb_io_capacity=1000. But a lot more remains to be done to make InnoDB use all IO capacity.

I'm no InnoDB expert, but I do know about data str...

2009-01-19T11:50:00.000-08:00

I'm no InnoDB expert, but I do know about data structures and systems. Here are a few possible reasons for the insertion rate to drop as the benchmark progresseses:

1) More nodes are traversed per insertion. The tree is getting bigger. That means the tree is getting deeper. So it is more CPU work to traverse the tree to perform a single insertion because more nodes are traversed.

2) More cache misses. As the tree gets larger it fits less well into the caches and TLB and you are getting more cache and TLB misses. Then the cost of traversing a single node becomes more expensive.

3) More instructions per node. For example, perhaps the undo information gets a little more complex as the tree gets bigger. (I'm just making this up, since I dont' fully understand InnoDB's undo logic.) Consider what happens when you insert 20 items into an empty tree. All 20 items end up in the same node. But if you insert 20 items into a big tree, all 20 items are likely to end up in 20 different nodes. So the undo information becomes "sparse" in the data structure, maybe requiring more work. Maybe there are other effects that add to the instruction count as the tree gets bigger. Thus in addition to suffering more cache misses per node, you may be executing more instructions per node.

Updated to add a few more innodb config params, an...

2009-01-19T11:14:00.000-08:00

Updated to add a few more innodb config params, and make the list more readable.