Saturday, August 19, 2006

Unorthodox approach to database design

There are whole books on the subject about building a great design that is scalable and portable among developers and or administrators.

Then there are whole books on the subject of capacity and scalability for the database layer.

Then there are novels from developers that in many cases really don't know the tricks of the DBMS they are working with, and create elaborate abstraction layers that automatically generate SQL for the DB in question from objects and such.

But, with all these people who tell you how to do it, actually can they prove that it works under a constant high workload for many people all at the same time.

I can boast this. Flickr does over 4 BILLION+ queries per day, 2 BILLION of which are SELECTS. Most of our data is REAL TIME queries from the database layer. We don't do any fancy tricks to dedicate resources to API calls to certain servers; they hit the SAME servers that the Flickr Users do.

You may be thinking to yourself yea right say you can do 20K + transactions per seconds that must be a crap load of expensive hardware all running, where all the data is served out of memory. Nope we run our stuff on RHEL-4.0 with mySQL version 4.1.20-flickr (my little special tweaks for x86_64) and the data is only 3% HOT (meaning out of the 1 TB of user data less then 3% is in memory).

Still hard to swallow? Let me add a little more info to blow your mind and wipe away all the things that you may have read that can't be done.

All of our database connections are real time. Our load balancer for the database is written in 13 lines of PHP code.

How can this be, how does Flickr scale? How did the Flickr Engineering Team do this (6 people)?


If you’re interested let me know post a comment, and I'll write up the design that I proposed July'ish 2005 to Flickr which we use today. It's able to scale linearly based on a function of users not on content.

64 comments:

Anonymous said...

Yes, I am interested in that.

I've often wondered if the API calls were handled differently, interesting to find they aren't. Guess that's an example of "don't optimize something 'til you have to".

Tom

speno said...

Spill it please. :-)

steve minutillo said...

You have to be kidding... of COURSE we're interested!

Chris Jant said...

please do let us know your secrets to performance

Malcolm Tredinnick said...

This is some attempt to see if 200 people will post the same comment? Okay, then...

Please post the details. :-)

Anonymous said...

+1

Paul Watson said...

Please, do tell.

danofames said...

yes, go for it.

Anonymous said...

Yes please.

I bought a couple of Cal's book and while it explains a few things that I hadn't considered it doesn't mention much regarding the API.

Anonymous said...

I'm in. :)

RobC said...

This is one of those things you have to share with the rest of us!

Come on, what's your secret sauce?

Richard said...

Yea, come on, spill it!

Dathan said...

Cool, I'll post diagrams, code snippits and a few other things that will make you all go wow, that's it?

Give me about a week though.

Ronald Bradford said...

Got any jobs going, will work for peanuts (well, not actually nuts, but to get my foot into the MySQL scaleout space, not much). I've got a lot of skills to offer.

Any yes, I'm interested in your design, especially as my backgound is as a Database Modeler.

Anonymous said...

I'm highly interested in Mysql Scalability and High Availability topics. I would really appreciate if you can publish the relevant documents with complete details. That would help everyone looking for such solutions.

With best regards,
S.Mugunthan

Anonymous said...

I'm highly interested in Mysql Scalability and High Availability topics. I would really appreciate if you can publish the relevant documents with complete details. That would help everyone looking for such solutions.

With best regards,
S.Mugunthan

darkseed said...

I would love to see the load balancing code you did in php.

Loïc said...

Of course: Yes please! :o)

Sheeri said...

Much like Tom's commment of "don't optimize something 'til you have to," I'd be interested to see what has been denormalized versus what hasn't in your schema. Mostly I've found there are very few cases for denormalization, and that joining tables is not as scary as most people think it is.

Domas said...

Hi! I made few comments in my blog, but sure, I'd like to see your design documents! :-)

Anonymous said...

Color me intrigued.

I would love to read about your design ideas. And if you don't mind will you post a followup comment on this post with a link to thw writeup if/when you do so?

Lachlan said...

Chalk up another under the "yes please" column.

jonas said...

I want to know!

Anonymous said...

pleeeeeeese?

Olle Jonsson said...

+1 interested, give give!

Ian said...

Definitely interested...looking forward to finding out the details!

ingeting said...

This is interesting stuff, and I really want to learn more about tweaking MySQL to run at that capacity.

Dave Heath said...

I would love to hear your secret

johnny said...
This comment has been removed by a blog administrator.
Anonymous said...

+1

johnny said...

Yes, I would like to know what you've done. thanks.

Jared "Ren" Williams said...

Been thinking of hash based balancing across multiple slave servers. (see Cache Array Routing Protocol)

So each mysql service would be only be serving a 1 / N 'th of the data, (where N is number of slaves).

This effectively combines all the mysql's own query caches into a single cache, rather than having N query caches with roughly the same thing.

But definately would be interested in the method flickr has implemented :)

Bobby M said...

yep - another one interested to see the "Unorthodox approach to database design" details......

+1

Dathan said...

Writing the stuff up in my spare time, but with the launch of maps the query load increased by 43%; I will need more time to get this out. It's not a big deal with the system in place, but I need to figure out if we are doing more then necessary queries. So, stayed tune.

Ruturaj Vartak said...

I'm interested.
I'm myself doing a lot to make MySQL Scalable, thanks to MySQL its still cheaper and efficient than DBs, yet there are 100s of tricks, that can help you elevate MySQL's perfomance.

Vinu said...

me too :)

Anonymous said...

+1 :)

Anonymous said...

Definitely looking forward to this. I will take production examples over pseudo any time.

Venkatesh Kanchan said...

dathan, you may be having one challenging time what with surpassing 1 million geo-tags in a day compared to the expected 1 million a month. And ur DB is still standing. Good stuff.

Waiting on the followup article. Thanks.

Mimmo said...

Great! I'm really interested. Thanks for your effort.

James Lynn said...

and theeeeeeeeeennn????

lucian said...

well...what's the holy secret, oh great guru of MySQL?...

tawmas said...

Yes, please, I'd be thrilled to read that!

Anonymous said...

I'm interested, too. Please send me the secret.

odoaker@freemail.hu

Jacques said...

I'm curious to know more about your database design which you have been using at Flickr.

Anonymous said...

I'm interested, we don't have anywhere near the queries and I have some performance issues.

Thanks

Anonymous said...

I'm interested too, please send me the info to dude30m@hotmail.com

Anonymous said...

Still nothing???

Dathan said...

This is on a bit of a hold.

Have allot of work to get through, and I need to run my blog post past my manager to make sure that I don't step on Yahoo's toes.

Anonymous said...

Please tell us anything you can. Some of us need to convince those VC types that this isn't all just voodoo. We understand you can't give out trade secrets.

I guess it would cost too much to poach you now ;)

Anonymous said...

Yes I guess it was too good to be true :( darn managers !

dude30m@hotmail.com

Anonymous said...

Let's here the magic behind the voodoo.

Did you ever end up sharing this?

Anonymous said...

Hi,

Could you publish the details that would help me a lot in my work. I would highly appreciate your timely help.

Regards,
S.Mugunthan

Anonymous said...

I take it this is going nowhere...

Dathan said...

Hello all, I'm writing this up now. It will be reviewed by my manager some time next week, so check back soon.

XNeo said...

Great!

I'm interested, too...

Dathan said...

Start of it here

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

I am interested too!

Anonymous said...

Great site, I am bookmarking it!Keep it up!
With the best regards!
David

sexy said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,按摩棒,跳蛋,充氣娃娃,情境坊歡愉用品,情趣用品,情人節禮物,情惑用品性易購

免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,av dvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX

情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

sex said...

徵信社
情趣用品
情趣用品
情趣用品
情趣
情趣


SM
充氣娃娃


SM
性感睡衣


免費視訊聊天室
aio交友愛情館
愛情公寓
情色貼圖
情色文學
情色小說
情色電影
情色論壇
成人論壇
辣妹視訊
視訊聊天室
情色視訊
免費視訊

免費視訊聊天
視訊交友網
視訊聊天室
視訊美女
視訊交友
ut聊天室
聊天室
豆豆聊天室
尋夢園聊天室
聊天室尋夢園
080聊天室
080苗栗人聊天室
女同志聊天室

上班族聊天室
小高聊天室






免費視訊聊天
免費視訊聊天室
免費視訊
ut聊天室
聊天室
豆豆聊天室 聊天室尋夢園
影音視訊聊天室


色情遊戲
寄情築園小遊戲
情人視訊網
辣妹視訊
情色交友

成人論壇
情色論壇
愛情公寓
情色
色情聊天室
色情小說
做愛
做愛影片
性愛


一葉情貼圖片區
情趣用品


辣妹視訊
美女視訊
視訊交友網
視訊聊天室
視訊交友
視訊美女

sex said...

徵信社
情趣用品
情趣用品
情趣用品
情趣
情趣


SM
充氣娃娃


SM
性感睡衣


免費視訊聊天室
aio交友愛情館
愛情公寓
情色貼圖
情色文學
情色小說
情色電影
情色論壇
成人論壇
辣妹視訊
視訊聊天室
情色視訊
免費視訊

免費視訊聊天
視訊交友網
視訊聊天室
視訊美女
視訊交友
ut聊天室
聊天室
豆豆聊天室
尋夢園聊天室
聊天室尋夢園
080聊天室
080苗栗人聊天室
女同志聊天室

上班族聊天室
小高聊天室






免費視訊聊天
免費視訊聊天室
免費視訊
ut聊天室
聊天室
豆豆聊天室 聊天室尋夢園
影音視訊聊天室


色情遊戲
寄情築園小遊戲
情人視訊網
辣妹視訊
情色交友

成人論壇
情色論壇
愛情公寓
情色
色情聊天室
色情小說
做愛
做愛影片
性愛


一葉情貼圖片區
情趣用品


辣妹視訊
美女視訊
視訊交友網
視訊聊天室
視訊交友
視訊美女

Anonymous said...

A片,A片,成人網站,成人漫畫,色情,情色網,情色,AV,AV女優,成人影城,成人,色情A片,日本AV,免費成人影片,成人影片,SEX,免費A片,A片下載,免費A片下載,做愛,情色A片,色情影片,H漫,A漫,18成人

a片,色情影片,情色電影,a片,色情,情色網,情色,av,av女優,成人影城,成人,色情a片,日本av,免費成人影片,成人影片,情色a片,sex,免費a片,a片下載,免費a片下載

情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣

A片,A片,A片下載,做愛,成人電影,.18成人,日本A片,情色小說,情色電影,成人影城,自拍,情色論壇,成人論壇,情色貼圖,情色,免費A片,成人,成人網站,成人圖片,AV女優,成人光碟,色情,色情影片,免費A片下載,SEX,AV,色情網站,本土自拍,性愛,成人影片,情色文學,成人文章,成人圖片區,成人貼圖

情色視訊,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,視訊交友90739,視訊,免費視訊,情人視訊網,視訊辣妹,影音視訊聊天室,視訊交友,視訊聊天,免費視訊聊天室,成人視訊,UT聊天室,聊天室,豆豆聊天室,色情聊天室,尋夢園聊天室,聊天室尋夢園,080聊天室,080苗栗人聊天室,上班族聊天室,小高聊天室

6K聊天室,080中部人聊天室,聊天室交友,成人聊天室,中部人聊天室,情色聊天室,AV女優,AV,A片,情人薇珍妮,愛情公寓,情色,情色貼圖