code.flickr.net Open in urlscan Pro
2a04:fa87:fffd::c000:425f  Public Scan

Form analysis
2 forms found in the DOM

GET https://code.flickr.net/

<form method="get" id="searchform" action="https://code.flickr.net/">
  <label for="s" class="assistive-text">Search</label>
  <input type="text" class="field" name="s" id="s" placeholder="Search">
  <input type="submit" class="submit" name="submit" id="searchsubmit" value="Search">
</form>

<form autocomplete="off" role="search" class="jetpack-instant-search__search-results-search-form">
  <div class="jetpack-instant-search__search-form">
    <div class="jetpack-instant-search__box"><label class="jetpack-instant-search__box-label" for="jetpack-instant-search__box-input-1">
        <div class="jetpack-instant-search__box-gridicon"><svg class="gridicon gridicons-search " focusable="true" height="24" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg" aria-hidden="false" style="height: 24px; width: 24px;">
            <title>Magnifying Glass</title>
            <g>
              <path d="M21 19l-5.154-5.154C16.574 12.742 17 11.42 17 10c0-3.866-3.134-7-7-7s-7 3.134-7 7 3.134 7 7 7c1.42 0 2.742-.426 3.846-1.154L19 21l2-2zM5 10c0-2.757 2.243-5 5-5s5 2.243 5 5-2.243 5-5 5-5-2.243-5-5z"></path>
            </g>
          </svg></div><input autocomplete="off" id="jetpack-instant-search__box-input-1" class="search-field jetpack-instant-search__box-input" inputmode="search" placeholder="Search…" type="search"><button class="screen-reader-text assistive-text"
          tabindex="-1">Search</button>
      </label></div>
  </div>
</form>

Text Content

Skip to primary content


CODE.FLICKR.COM




Search


MAIN MENU

 * Flickr
 * Flickr Blog
 * @flickr
 * @flickrapi
 * Developer Guidelines
 * API
 * Jobs


POST NAVIGATION

← Previous Next →


TICKET SERVERS: DISTRIBUTED UNIQUE PRIMARY KEYS ON THE CHEAP

Posted on February 8, 2010 by Kay Kremerskothen

This is the first post in the Using, Abusing and Scaling MySQL at Flickr series.

Ticket servers aren’t inherently interesting, but they’re an important building
block at Flickr. They are core to topics we’ll be talking about later, like
sharding and master-master. Ticket servers give us globally (Flickr-wide) unique
integers to serve as primary keys in our distributed setup.


WHY?

Sharding (aka data partioning) is how we scale Flickr’s datastore. Instead of
storing all our data on one really big database, we have lots of databases, each
with some of the data, and spread the load between them. Sometimes we need to
migrate data between databases, so we need our primary keys to be globally
unique. Additionally our MySQL shards are built as master-master replicant pairs
for resiliency. This means we need to be able to guarantee uniqueness within a
shard in order to avoid key collisions. We’d love to go on using MySQL
auto-incrementing columns for primary keys like everyone else, but MySQL can’t
guarantee uniqueness across physical and logical databases.


GUIDS?

Given the need for globally unique ids the obvious question is, why not use
GUIDs? Mostly because GUIDs are big, and they index badly in MySQL. One of the
ways we keep MySQL fast is we index everything we want to query on, and we only
query on indexes. So index size is a key consideration. If you can’t keep your
indexes in memory, you can’t keep your database fast. Additionally ticket
servers give us sequentiality which has some really nice properties including
making reporting and debugging more straightforward, and enabling some caching
hacks.


CONSISTENT HASHING?

Some projects like Amazon’s Dynamo provide a consistent hashing ring on top of
the datastore to handle the GUID/sharding issue. This is better suited for
write-cheap environments (e.g. LSMTs), while MySQL is optimized for fast random
reads.


CENTRALIZING AUTO-INCREMENTS

If we can’t make MySQL auto-increments work across multiple databases, what if
we just used one database? If we inserted a new row into this one database every
time someone uploaded a photo we could then just use the auto-incrementing ID
from that table as the primary key for all of our databases.

Of course at 60+ photos a second that table is going to get pretty big. We can
get rid of all the extra data about the photo, and just have the ID in the
centralized database. Even then the table gets unmanageably big quickly. And
there are comments, and favorites, and group postings, and tags, and so on, and
those all need IDs too.


REPLACE INTO

A little over a decade ago MySQL shipped with a non-standard extension to the
ANSI SQL spec, “REPLACE INTO”. Later “INSERT ON DUPLICATE KEY UPDATE” came along
and solved the original problem much better. However REPLACE INTO is still
supported.

> REPLACE works exactly like INSERT, except that if an old row in the table has
> the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row
> is deleted before the new row is inserted.

This allows us to atomically update in a place a single row in a database, and
get a new auto-incremented primary ID.


PUTTING IT ALL TOGETHER

A Flickr ticket server is a dedicated database server, with a single database on
it, and in that database there are tables like Tickets32 for 32-bit IDs, and
Tickets64 for 64-bit IDs.

The Tickets64 schema looks like:

CREATE TABLE `Tickets64` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `stub` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB


SELECT * from Tickets64 returns a single row that looks something like:

+-------------------+------+
| id                | stub |
+-------------------+------+
| 72157623227190423 |    a |
+-------------------+------+


When I need a new globally unique 64-bit ID I issue the following SQL:

REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();



SPOFS

You really really don’t know want provisioning your IDs to be a single point of
failure. We achieve “high availability” by running two ticket servers. At this
write/update volume replicating between the boxes would be problematic, and
locking would kill the performance of the site. We divide responsibility between
the two boxes by dividing the ID space down the middle, evens and odds, using:

TicketServer1:
auto-increment-increment = 2
auto-increment-offset = 1

TicketServer2:
auto-increment-increment = 2
auto-increment-offset = 2


We round robin between the two servers to load balance and deal with down time.
The sides do drift a bit out of sync, I think we have a few hundred thousand
more odd number objects then evenly numbered objects at the moment, but this
hurts no one.


MORE SEQUENCES

We actually have more tables then just Tickets32 and Tickets64 on the ticket
servers. We have a sequences for Photos, for Accounts, for OfflineTasks, and for
Groups, etc. OfflineTasks get their own sequence because we burn through so many
of them we don’t want to unnecessarily run up the counts on other things.
Groups, and Accounts get their own sequence because we get comparatively so few
of them. Photos have their own sequence that we made sure to sync to our old
auto-increment table when we cut over because its nice to know how many photos
we’ve had uploaded, and we use the ID as a short hand for keeping track.


SO THERE’S THAT

It’s not particularly elegant, but it works shockingly well for us having been
in production since Friday the 13th, January 2006, and is a great example of the
Flickr engineering dumbest possible thing that will work design principle.

More soon.

Belorussian translation provided by PC.

This entry was posted in Uncategorized and tagged using and abusing mysql by Kay
Kremerskothen. Bookmark the permalink.


ABOUT KAY KREMERSKOTHEN

Kay is a Community Manager for Flickr and passionate about extraordinary
photography. As an editor on Flickr Blog he loves to showcase the beauty and
diversity of Flickr in his posts. When he's not blogging or making Flickr more
awesome (in front of and behind the scenes), you can find him taking pictures
with his beloved Nikon and iPhone, listening to Hans Zimmer's music or playing
board games. | On Flickr you can find him at https://flic.kr/quicksilver
View all posts by Kay Kremerskothen →

© 2024 Flickr, Inc. All rights reserved. | Powered by WordPress VIP


SEARCH RESULTS

Magnifying Glass
Search
Close search results
FiltersShow filters
Sort by:
Relevance•Newest•Oldest


NO RESULTS FOUND


FILTER OPTIONS


Close Search
Search powered by Jetpack