Friday, February 01, 2008

Adding Capacity is FUN

At Flickr adding capacity is easy. Since we are able to scale at a function of user growth, independent of hardware classes. This makes adding new hardware easy. I'm able to adjust the server weight on the FLY to give more users to a certain class of hardware over another.

Changing schema is easy as well and now I do it much safer. In the pass I would turn off a master for each partitioned dataset all at the same time: then stay up for 20+ hours and execute an alter across the entire server farm, that was off line.

Now I do it in stages and do it throughout the week. Its a little slower but I get to sleep. Additionally with the new method I am able to do more all at the same time-like rebuild the entire dataset for a partition.

Here are my steps for doing a change that takes more then 10 hours:

remove the servers from the site config
push my.cnf.maintenance to the servers not taking on site traffic
- this starts mysql on port 3307 and turns off replication and binary logging
- this step is crucial to tell our backend bots not to do stuff on the servers in maintenance.

mkdir -p /var/lib/mysql/restore
restart mysql
DUMP all data using mysqldump and SELECT *,[NEW FIELD DEFAULT VALUE1...VALUE-N] INTO OUTFILE '/var/lib/mysql/restore/[TABLE].txt 8 processes at a time

stop mysql
remove all of innodb datafiles
copy data with 8-20 processes to the filer to sustain 50MB a sec. Pretty nify script
umount /var/lib/mysql
mkfs.ext3 -Tlargefile4 -LMYSQL [device]
mount -a

copy some of the data back (everything except the txt files)
start mysql
LOAD data in parallel about 8 processes at a time

verify that utf8 data was loaded properly
push my.cnf.production
restart mysql
wait about 45 mins for 12 hours of events to catch up.
put it back into the site config

I do these steps across 6-10 servers at a time-using a dsh like application

Your question may be why are you rebuilding the filesystem and using ext3? Well EXT-3 is my only option and it fragments really bad-messing with performance. So if I am going to do a massive alter-I throw that in there for good measure-I plan on doing this once a year.

I do not do this procedure for alters that last less then 10 hours, but if I alter Flickr's main table which holds most of the data-I take the opportunity to shrink all the datafiles.


Paul McCullagh said...

Hi Dathan,

Very interesting blog! It makes me wonder what the affect of a schema update is. For example:

- Does the application have to deal with different schemas on different servers during the update?

- Does taking a partition offline mean data is unavailable for certain users?

veganloveburger said...


Nilesh Jethwa said...

I have been working on an MySQL DBA Dashboard. Would like your opinion to improve and make it useful

Your opinion is highly regarded
MySQL Dashboards

Dathan said...

@paul mccullagh

Does the application have to deal with different schemas on different servers during the update?

Sometimes yes, but mosttimes no. We do not change column names or add the complete insert or update until the schema is live.

Does taking a partition offline mean data is unavailable for certain users?

No downtime at all

sexy said...



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