Friday, December 29, 2006

INNODB_FILE_PER_TABLE

So, if your a frequent reader of this blog you know that I've mentioned thatI federates data.

So, if all servers are the same, query load is the same, and the same amount of data exists on all the shards then the overall load average and IO load metrics should be the same between each server containing it's slice of the data (these are called shards).

Well, looking over some very detailed stats, I noticed that these new boxes that are in production are kicking some major ass, these new boxes are able to handle nearly 5 times more data and queries. To good to believe, since overall they are the same as my old boxes but the main difference is the CPU in them.

Then I looked at some more my.cnf.tmpl tweaks (I have a my.cnf.tmpl that generates my.cnf by replacing certain fields when /etc/init.d/mysqld start is invoked). Low and behold I added innodb_file_per_table and innodb_open_files=1024. This must be the reason right?

In the past I've bench marked innodb_file_per_table, raw device, and a single table space and found no real difference in throughput, but now I'm seeing that these new boxes with this option is giving me an order of magnitude better performance. I just can't believe that a single option enabled can do this. I start looking at what is the different between two distinct shards.

Each shard has the same amount of memory and disk layout. The newer shard has x2 DUAL CORE CPU's so it looks like 4 CPU to Linux. Additionally the new shard has a newer Linux kernel. Hmmm is the combination of this feature + the new CPU the reason for the order of magnitude better performance. This is not a good justification for this blessing.

Well the older shard has been around for a year, while the newer one has been around for a few months, but this is not Windows: the servers do not degrade over time :P. But, wait INNODB does degrade over time. There is a lot of data inside the single table space that has been removed! The table space on the older shard has been around for a year, while the new table space on the newer DUAL Core CPU shards have only been around for a few months. Thus there are more gaps in the old table space then the new one. More gaps means more disk head movement and more random io for the same operation, when compared to the newer shard. This is the reason!

In conclusion, a huge amount of performance / scale can be gained out of a old database by rebuilding an INNODB table space. Personally I'm a big fan of innodb_file_per_table, since the win is easier maintenance and space control (it can reclaim gaps of data with an optimize table.)


How to rebuild an INNODB table space and reclaim disk space:

    Single table space:
  1. Dump the data to a text file

  2. Make a backup of the old table space

  3. stop mysql, rm -f the table space and log files

  4. start mysql

  5. re-import the data




    file per table table space (4.1 syntax):
  1. ALTER TABLE TYPE=INNODB;

6 comments:

Brian said...

If you can live with MyISAM tables for a short while and want to reduce down time, you can convert all of your InnoDB tables to MyISAM tables instead of dumping them. This gives you the ability to stay up and running if you have lots of tables to convert. Then just convert them back to innodb after you remove the innodb table space.

Dathan said...

This doesn't shrink the table space. Converting to myISAM then back to INNODB gives you no wins. If I wanted to just create the file per table I can also run

ALTER TABLE [tablename] TYPE=INNODB

Anonymous said...

Interesting, I'll have to play with that. In addition to database considerations, I'll throw in that the per table approach is also much better from a system administration perspective.

MyISAM is easier for moving data around/making backups, because the files are in distinct directories, with their own files per table. Grabbing one table and putting it on another server is much easier, and faster, because you don't have use mysqldump.

With innodb_file_per_table, InnoDB can be just as easy for system administrators.

-Nick
Wikinvest & Yahoo
http://www.yahoo.com
http://www.wikinvest.com/?utm_source=ff&utm_medium=email&utm_term=nick

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

sex said...

徵信社
情趣用品
情趣用品
情趣用品
情趣
情趣


SM
充氣娃娃


SM
性感睡衣


免費視訊聊天室
aio交友愛情館
愛情公寓
情色貼圖
情色文學
情色小說
情色電影
情色論壇
成人論壇
辣妹視訊
視訊聊天室
情色視訊
免費視訊

免費視訊聊天
視訊交友網
視訊聊天室
視訊美女
視訊交友
ut聊天室
聊天室
豆豆聊天室
尋夢園聊天室
聊天室尋夢園
080聊天室
080苗栗人聊天室
女同志聊天室

上班族聊天室
小高聊天室






免費視訊聊天
免費視訊聊天室
免費視訊
ut聊天室
聊天室
豆豆聊天室 聊天室尋夢園
影音視訊聊天室


色情遊戲
寄情築園小遊戲
情人視訊網
辣妹視訊
情色交友

成人論壇
情色論壇
愛情公寓
情色
色情聊天室
色情小說
做愛
做愛影片
性愛


一葉情貼圖片區
情趣用品


辣妹視訊
美女視訊
視訊交友網
視訊聊天室
視訊交友
視訊美女

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