I caught Facebook - Needle in a Haystack: Efficient Storage of Billions of Photos on Flowgram. First up, I’m not a big fan of Flowgrams - the format is sensible, slide and voice, is excellent, but the delivery in a web browser isn’t optimal… make downloadable videos!

The talk however, was excellent. Do watch it, and learn a bit more about Facebook’s infrastructure. Anyway, some notes I took from the talk:

• “We’re one of the largest MySQL installations in the world”
• Use memcache - “We have memcache because databases aren’t fast” (later on in the questions)
• Separate team focusing on APE (Apache, PHP and Extensions that they work on)
• 6.5 billion total images, 4-5 sizes stored for each, so 30 billion files, of about 540TB total… During peak? 475,000 images served per second, and growing by …

The VM for Linux prefers system cache over application memory. What does this mean? The best way I can explain is by example.

Imagine you have 32 GB of RAM
MySQL is set to take 20 GB of RAM for a process based buffer and up to 6M for the various thread buffers.

Over a period of time the box swaps. The only thing that is running is mysql and its memory size is around 21GB for resident memory. Why does swap grow when there is plenty of memory? The reason is when a memory alloc is needed (thread based buffer is tickled) the VM will choose to use swap over allocating from the system cache, when there is not enough free memory.

DO NOT TURN OFF SWAP to prevent this. Your box will crawl, kswapd will chew up a lot of the processor, Linux needs swap enabled, lets just hope its not used.

So how do you stop Nagios pages because of swap usage? Well if you have a few choices.

…

Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.

I should point out that using the hints incorrectly will most likley cause your queries to perform worse, so be sure that it actually makes sense to use them before you go nuts. This means use EXPLAIN and read the documentation on each hint before using.

It's also a good idea to enclose the hints within a SQL comment, for example SELECT /*! SQL_NO_CACHE */ columns FROM table. This can help to make your application a bit more portable.

Let's take a look at some MySQL Optimization Hints:

SQL_NO_CACHE

The SQL_NO_CACHE hint turns off MySQL's …