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.