MySQL Forums
Forum List  »  Performance

Achieving max write throughput with InnoDB clustered index?
Posted by: Nicolas Turgeon
Date: April 20, 2005 03:01PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Achieving max write throughput with InnoDB clustered index?
3678
April 20, 2005 03:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.