On Jul 14, 2008, at 6:32 AM, Jay Pipes wrote:
Brian, you understate the importance of the above statement.
Clustered index organizations (especially ones with page-level block
organization) generate a hot-spot in memory and on-disk when
auto-incrementing primary keys are used. Essentially, with an
auto-incremented primary key, the page's fill factor is typically 2-3x
higher than with non-incrementing keys, which reduces or eliminates
the
need and costs of page-splitting and tree-rebalancing that is needed
for
the clustered index.
If an engine with a clustered index organization knows that primary
keys
will always be higher than the last used key value, then many
optimizations can be achieved, which is partly why InnoDB insert
speeds
on auto-incrementing primary keys are quite good (especially after the
removal of certain locks and improvement the adaptive hash index in
5.1.21).
Just thought I would mention this fact. And, Brian, I know you will
respond that the cloud-computing space totally alters the above
current
situation, but I don't think it does. Having a cluster of InnoDB
machines running with a UUID primary key would mean a dramatic
increase
in storage space used by InnoDB and, IMHO, a reduction in
performance of
modification statements. Our current system of replication with the
auto_increment_increment method helps to increase insert speeds while
ensuring uniqueness of primary keys across multiple servers. It's not
ideal, of course, but it does have its performance advantages.
Time-based UUIDs increment on generation... This might be useful for
that application.
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
