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.

6 comments:

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...

that
is
awesome.

Nilesh Jethwa said...

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

Your opinion is highly regarded
Regards
Nilesh
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片,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成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

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片,情人薇珍妮,愛情公寓,情色,情色貼圖