Monday, November 5, 2007

Fairness in Storage Engine Benchmarking

Is it fair to compare Innodb to MyISAM with innodb_flush_log_at_trx_commit=1 and flush=OFF?

Granted different applications have different requirements. But, when you're trying to show a graph comparing the two Engines, isn't it reasonable to try to keep the same settings as close to equal as possible?

I suppose that begs the question of how similar innodb_flush_log_at_trx_commit is to flush, and if it's even possible to get a valid comparison.

Innodb tends to be slower (I'm on 4.1 currently, ancient history), but it seems to me that it's doing a whole lot more for the money. What do you think?

[UPDATE] Additionally, I'm running sequential benchmarks back to back, for example loading the table, then turning around and running a read test on the table. For innodb, this often means that a good chunk of the buffer pool is dirty and the read test has to slog through while the dirty buffers are being flushed, potentially changing performance.

Should sequential benchmarks have some built in time to allow the database to return to a stable state, or is it better just to plow straight through them all at once?

What is Innodb doing?

I've been running a series of sysbench tests on Innodb with MySQL 4.1 My tests include doing both reads and writes on the table.

I've noticed that after the test is complete, and while there are no processes in show processlist, I see a fairly large amount of buffer pool reads and writes, as well as a large number of Modified db pages. Now, I know that it could take a while for innodb to flush out the dirty buffers after a large amount of writes, but I'm seeing the Modified db pages actually getting larger for a while, and then gradually getting to the point where it finally settles down and returns to 0.

The only thing I can think of is Innodb is doing some sort of table optimization, which begs the question of whether I should rerun my tests with the optimized table or not.

Has anyone else seen this?

UPDATE: I believe I have figured out what's happening. My sysbench test is working on a 100M row table, and the last run I do is 10M inserts with these options:


I found out that this test deletes all of my rows before it runs, so I see around 50k deletes per second for quite a while, and then the test runs. After the test finishes, I suspect Innodb is cleaning up a lot of unused pages in the data file, hence all the read and write activity on the buffer pool.