Achieving max write throughput with InnoDB clustered index?

[Nicolas Turgeon]

Hi,

I'm using InnoDB to store user information, with the primary key on the user, so that all entries for a user are clustered together. A user can have up to a few hundred rows. This clustering makes reading all the rows for a user much faster than, say, MyISAM, where the information is scattered randomly in the table.

What I'm seeing is that write throughput is slowed down because InnoDB performs a lot of reads to be able to insert a row at the right place in the file. I assume it would be different if it was always writing at the end of the file, e.g. if the primary key was on an auto_increment column. In this case, the last page written would always be in the cache, so it would most likely be faster. In my case however, the information has to be written in the user's page, somewhere within the table, and it may well not be in the cache, hence the need to read. Rows are added to users pretty much randomly.

I am understanding this right? Anything I could do to increase write throughput, while maintaining the user clustering?

Thanks,

Nicolas