Here is the quick notes from the session Scaling MySQL - Up or Out ? moderated by Kaj Arno as part of the todays keynote.

Here is the list of panelists are ordered by Alexa ranking.

  1. Monty Taylor (MySQL)
  2. Matt Ingerenthron (Sun)
  3. John Allspaw (Flickr)
  4. Farhan Mashraqi (Fotolog)
  5. Domas Mituzas (Wkipedia)
  6. Jeff Rotheschild (Facebook)
  7. Paul Tuckfield (YouTube)

Here is the list of questions and answers from panelists:

  How many servers Number of DBAs How many web servers Number of caching servers Version of MySQL Language, platform Operating System
MySQL

1 M, 3 S

1/10

2

2

5.1.23

Perl,php and bash

Linux fedora

Sun

2 clustered, 2 individual

1.5

160+

8

5.0.21

Lots of stuff (java mostly)

Open Solaris

Flickr

166

At present 0

244

14

5.0.51

Php and some Java

Linux

Fotolog

140 databases on 37 instances

10 instances a DBA

70

40 ( 2 on each, 80 total)

4.11 and 4.4

Php, 90% Java

Solaris 10

Wikipedia

20

None, but everybody is kind of a  DBA

70+200

40 ( 2 on each, 80 total)

 

Php, c++, python

Fedora / Ubuntu

Facebook

30000 (1800 db servers)

2

1200

805

5.0.44 with relay log corruption patch

Php, python, c++ and enlang

Fedora / RHEL

Youtube

I can’t say

3

I can’t say

I can’t say

5.0.24

python

SuSE 9

Few more misc questions ...

Number of times re-architected ?

  • MySQL: 2 - 1 time slave, 1 time memcached
  • Sun: site depend (many times over the year)
  • Flickr: Ummm...2.5 (various clusters federated)
  • Fotolog: many cached replacements (about to do one change now)
  • Wiped: Never (Spaghetti)
  • Facebook: Every Tuesday, continual
  • Youtube: Pretty continual, 2-3 times (replication, sharding, federation)

What happens if server fails ? what actions you will generally take ..

  • Flickr: All of our 7 are federated, pairs of servers, we can loose any one side of shard, we can loose boxes.. traffic goes to either side of shard, now it goes to one, and we will get another one (very transparent to user) .. for offline, sites are affected
  • Wikipedia: Users shout at them on IRC then they moderate fixed in seconds
  • Facebook: one of 1800-1900 will always fail, just operate well, minor impact, with data going away for a while...we restore from binlog and start the server quickly, promote slave to master and number of ways
  • Fotolog: we simply mount the snapshots to different servers and get
  • Youtube: SAN etc, very important data.. recover the server, mirrored disk ...mirrored hard drive is crucial

Any recommendation of scaling technology that you wanted to bring

  • Fotolog: UltraSPARC-T1 (excellent master, multi threaded) and UltraSPARC-T2 for slave (single threaded)
  • Wikipedia: good n/w switch
  • Facebook: cheap switch, we dont use SAN, neatly partitioned, they scale independently and fail independently
  • mysql: cluster very sad

Server virtualization ?

  • nobody uses at this time
  • ETL cluster, we may run more than one in the future (facebook)

Anything to worry at present ?

  • Facebook: app design is the key to use resources, data center power supply and consumption
  • Fotolog: Google has to approve it for our power (cut app servers by 1/2 by moving from php to java)
  • Youtube: not at all

Any reco, lessons to DBA

  • better you know what the systems are, then you can
  • performance, scaling taking it serious
  • nothing more permanent than temp solutions (if u don't know when u will fail, then u will )
  • architect properly in start, schema, cost of serving data
  • 10 mts biggest architectural change
  • memory, resource