A couple of weeks ago I blogged about Sharing an auto_increment value across multiple MySQL tables. In the comments, a few people wrote in to suggest alternative ways of implementing this. I just got around to benchmarking those alternatives today across two large EC2 machines:
(Measured in transactions/second – higher is better)
What is the conclusion? With the exception of my original option2, they actually all perform fairly similar. The Flickr and Option1 tests perform marginally better. Test “arjen2” is option2, but with a MyISAM table — it suffers a little because EC2 can be a little high for latency, and there’s one additional round trip. Test arjen2005 is not too dissimilar from the Flickr solution, but uses a MySQL stored function.
About the Author
Morgan TockerMorgan is a former Percona employee. He was the Director of Training at Percona. He was formerly a Technical Instructor for MySQL and Sun Microsystems. He has also previously worked in the MySQL Support Team, and provided DRBD support.
try snowflake: http://github.com/twitter/snowflake
Hi Morgan,
Thumbs up for revisiting and comparing solutions.
Hi Morgan,
thanks for the comparison.
Just one little thing – could you place add titles and measurement units to the axes so it is completely clear what the numbers mean?
TIA, Roland.
Roland, updated.
The unit of measurement is transactions/second. A transaction includes whatever work is required to generate the key + insert into the destination table.
See the “full disclosure” link for more information.
– Morgan
Morgan, thanks for the update!
Dear Sir,
Seeking your advice, can we use this method in mysql master to master replication?
As for master to master, normally we will have the duplicate primary key issues,
if we are using this method sharing the autoincrement key among all the application,
then we will solve the duplicate primary key issues.
Am I right? Is this the common practice for master to master replication?
Hope to hear from you soon.
Thanks.
regards,
jason
@Jason – it’s more useful for sharded environments than master/master.
In my post I carefully tiptoed around ‘when to use it’, since there are a lot of cases I’ve seen this done for all the wrong reasons. I just wanted to demonstrate if you do use it – here are the performance characteristics.