… “estimate” method reduces the impact of traditional InnoDB log flushing, which can cause downward spikes in performance. Other then that,…ENGINE = InnoDB, PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ While the structure of the table with secondary indexes is as follows: CREATE TABLE …

… think I can explain the performance you are seeing. But first I’d like to note that this experiment isn’t really… is lower with only the primary key because the entire table fits in main memory. Each row is on the order…. With a 55GB buffer pool, we would expect the primary table to simply fit. But with all 4 indexes the data…

Aaron, No particular reasons, it doesn’t matter if you have 4 1G or 2 2GB log files, as they are all concatenated together by InnoDB and written to in a circular fashion.

Justin, I would be doing another benchmark and use ‘innodb_ibuf_accel_rate’ together with tuning other IO related parameters that I can tune to good affect, and I will post my results.

@Time Callaghan, I did not test with innodb_flush_log_at_trx_commit=1 because the durability requirements are not that stringent. And following is how I invoked iiBench: iibench -T $tbl_name -D iiBench -r 200000000 -M 1 -s 1000000 -t 100000 -I 1 -a -S

… stringent and I can afford to loose 1 second worth of data, hence I did not test with innodb_flush_log_at…=1. I would also not recommend running on EBS with innodb_flush_log_at_trx_commit=1, because IO times on…

This benchmark doesn’t test the IO performance of EBS though as you’re not flushing to disks on each commit. What numbers do you get when you set innodb_flush_log_at_trx_commit to 1?