In case anyone is interested in a copy of my slides for the two lightning talks I gave at the Open Source Days 2008 conference, I have made them available here:
- “Optimizing Large Databases Using InnoDB Clustered Indexes:” HTML and PDF.
- “Profiling with OProfile and Intel Core 2 performance counters:” HTML and PDF.
I waqs quite pleased with the benchmark that I prepared for the InnoDB mini-talk, where I measure the performance difference between clustered and auto_increment primary key, both with data that fits in memory and with data that does not. I have wanted to do this benchmark for quite some time, as I have not really seen real results for this before, though the technique of using clustered primary keys for performance is well-known.
The results are quite interesting, with clustered indexes being faster for I/O bound load with more than an order of magnitude. It is also interesting to see how dead-cheap hardware can do 23k queries/second and read 1000000 rows/second with a properly tuned database.