Sharing an auto_increment value across multiple MySQL tables (revisited)

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.

Full Disclosure.

Share this post

Comments (7)

  • ryan king Reply October 26, 2010 at 9:06 pm
  • Shlomi Noach Reply

    Hi Morgan,
    Thumbs up for revisiting and comparing solutions.

    October 26, 2010 at 11:45 pm
  • Roland Bouman Reply

    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.

    October 27, 2010 at 1:52 am
  • Morgan Tocker Reply

    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

    October 27, 2010 at 6:36 am
  • Roland Bouman Reply

    Morgan, thanks for the update!

    October 27, 2010 at 10:55 am
  • jason Reply

    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.


    October 27, 2010 at 9:22 pm
  • Morgan Tocker Reply

    @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.

    October 28, 2010 at 6:35 am

Leave a Reply