Friday, March 09, 2007

Putting mySQL 5.0.33 into production

So, I'm playing with 5.0.33 and there are a few gotcha:

If you use innodb and like to backup your databases using ibbackup, make sure your license is for ibackup-3.0. 2.0 will report that the iblog files are corrupt.

Otherwise it's a straight forward upgrade for me, so far.

I have a mysql 4.0 database that was about 200GB in size, most of that data was removed yet the ibdata file is still 200GB. So, what I did was

mysqldump --single-transaction -uroot -p --all-databases --master-data=1 > all_databases.sql

once backed up I trashed the old datafile and rebuilt it into an empty 5.0 datafile.

Next I added the option innodb_file_per_table so, I can shrink data on a table basis in the future.

mysql -uroot < all_databases.sql

wala done.

In a few hours I turned that 200 GB datafile into 27 GB data, and reclaimed a bunch of resources.

So, why upgrade? Well I wanted to try the innodb scalability patch in production, and 4.1.23 is not in bitkeeper yet (last time I checked). Also there is a cool feature in mysql 5, that turns simple SQL-OR statements internally into UNIONS to get the most out of indexes. I'd like to see how this will help me from day to day uses.

1 comment:

sexy said...



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