« Funding: DotCom vs Today | Main | Slides for Creating INFORMATION_SCHEMA Tables »

April 25, 2007

Federation at Flickr: Doing Billions of Queries a Day

Listening to Dathan Pattishall talk about flickr at the 2007 MySQL User Conference. Dathan worked at AuctionWatch in 1999, then in 2003 worked at Friendster, now at Flickr.

Flickr was unable to keep up with demand. Replication was not working, too much slave lag. They came up with some requirements. Needed to support a write intensive site with multiple masters. There should be no single point of failures. Need to have real-time maintenance and be able to serve pages extremely fast.

At AuctionWatch they put folks on separate boxes. At Friendster they had an algorithm that spread folks across many machines. At Flickr they use federation, which is made up of shards, a global ring, and logic to connect shards.

Shards are a slice of a main database. Flickr uses active master-master replication but externalizing the auto increment process.

The global ring is a lookup ring used for data that can't be federated. Knows where stuff is.

When you click a favorite. Pull owner id is pulled from the cache to determine shard location (shard 5), my id is also pulled from the cache (shard 13). A distributed transaction then connects to the shards, adds the information, and commits.

The users are put onto the machine that contains their shard so as they make comments, upload photos etc in real time without seeing the replication lag as data goes to another machine.

During normal operation the shards are used at 50% capacity. When they need to do maintenance they move load onto fewer shards, which increases usage up to 70-80% while doing maintenance.

A typical flickr page is 30 queries. Some old data shows about 35,000 queries per section, spikes of 3 billion queries per day.

As far as hardware. They use EMT64 running RHEL4. 16GB of RAM and 6 x 15K disks RAID 10. They currently have 12TB of user meta-data in the InnoDB which does not count photos. Each shard might contain up to ~120 GB of data.

MySQL 5.0 is used for auxiliary data (logs) and generating ticket ids (for auto increment). They are on 4.1 for everything else.

They allow 400 connections per server in a shard. With two servers in a shard that means a maximum of 800 connections for the whole shard.

Meta-data is stored in the database, the photos are stored on a filer which is in multiple data centers.

Posted by mike at April 25, 2007 12:35 PM