MySQL’s innodb_file_per_table

February 17, 2010 in , , with 227 Comments

I’m still alive – preparing my presentation for ConFoo conference (about massive scalability), writing an article on load balancer in the free software domain, doing a technical review of a book on Nginx (it should be pretty good), and driving my company at crazy speed (some new contracts and operations for existing ones). Ho, and I’m preparing a wedding (mine) – yeah, I’m alive and living my life at the fullest.

In the midst of all that, I’ve got a mandate involving MySQL databases and the import of a big chunk of data (about 8gb raw). Yeah, I know, some peoples reading my blog will know whom I speaking of. The data come with the schema, the load data queries and the CSV files. Should be pretty easy, long, but easy.

Got that one wrong. I’ve been bitten by all the bugs that were on the road:
– MySQL LoadData (from CSV) use the TMPDIR variable (which was on root filesystem) and LoadData generate a temporary queries in that directory. In the present case, the 8GB raw csv represented over 40GB of required tmpdir space.
– MySQL Replication doesn’t live really well with importing large amount of data. The first (failed) import broke all the replication (ring of masters + slaves).
– and…

MySQL’s innodb log file cannot shrink its size. That’s pretty simple as a rule, but it creates lots of problems when you have a failed import due to free space issue. Whatever you do, MySQL will NOT give back this hard drive space unless you dump all and re-import. The current case being a database of over 20GB, it’s really a no-go (and since you are lacking free space, dump is a no-go).

The important things to take away from that encounter is: always enable innodb_file_per_table on MySQL. The performance overhead will be minimal (very) – you might even get better performance depending on your file system tolerance for fragmentation – AND – you will be able to drop table and regain the disk space… even if its only for a couple hours. The drop of a 10GB database should always give you some free space ;-). Question is: why haven’t MySQL put this setting as default ? Its been around for long enough… is stable and does help the general user (a sysadmin) experience.

227 Responses to “MySQL’s innodb_file_per_table”

  1. PCSO Lotto Result May 3, 2011 at 3:24 PM #

    I just put the link of your blog on my Facebook Wall. very nice blog indeed.:..’-

  2. sports lines May 3, 2011 at 4:32 PM #

    I notice that there’s no where on your site, to where others can subscribe to your updated posts.can u comment back, letting myself, as well as other know where to subscribe exactly

  3. Jarod Arnesen May 3, 2011 at 6:21 PM #

    I will invite all my friends to your blog, you really got a great blog.::`-’

  4. cash in advance May 3, 2011 at 7:16 PM #

    http://www.thoughts.com/cheapcigarettesonlinesite/cheap-cigarettes-online-cheaper-than-regular-cigarettes cheap cigarettes online

  5. audio switch May 3, 2011 at 8:32 PM #

    I was also reading a topic like this one from another site.`-~~”

  6. outsourcing seo management May 3, 2011 at 8:55 PM #

    We prefer to claim i uncovered your blog rather usefull, that improved me fully understand this issue in front of you really. I would prefer to recognise if i can be able to write articles for ones web log.

  7. national debt relief May 3, 2011 at 11:32 PM #

    http://www.thoughts.com/discountcigarettesite/discount-cigarettes-save-money-with-scentless-electric-cigarettes discount cigarettes

  8. Miquel Indovina May 4, 2011 at 12:11 AM #

    A blog like yours should be earning much money from adsense.*..*,

  9. Chuck Sammut May 4, 2011 at 1:07 AM #

    I was also reading a topic like this one from another site.,;~,,

  10. uv face paint May 4, 2011 at 1:57 AM #

    Hi, do you have a facebook fan page for your blog?’”,”~

  11. christian louboutin white pumps May 4, 2011 at 2:53 AM #

    I’ve go through some of the web sites on the website considering that yesterday, and I genuinely like your design of running a blog. I book-marked this in order to my top features internet site listing and you will be checking rear soon. Go to my personal web site in addition along with make me aware your viewpoint.

  12. phone number lookup May 4, 2011 at 3:50 AM #

    http://www.thoughts.com/searchpeopleonline/search-people-find-anyone-today-easily search people

  13. PCSO Lotto Results May 4, 2011 at 6:25 AM #

    I think your blog is getting more and more visitors.–`.”

  14. Corner breakfast nook May 4, 2011 at 8:51 AM #

    I would really like you to become a guest poster on my blog.”;;,`

  15. PCSO Lotto Result May 4, 2011 at 9:00 AM #

    Have you tried twitterfeed on your blog, i think it would be cool.:-~`;

  16. Adelia Strano May 4, 2011 at 9:18 AM #

    I see that you are using WordPress on your blog, wordpress is the best..”~*,

  17. Charley Brull May 4, 2011 at 9:21 AM #

    I think your blog is getting more and more visitors..’;**

  18. Submersible Well Pumps May 4, 2011 at 11:09 AM #

    Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful for me.

  19. top seo company afghanistan May 4, 2011 at 11:58 AM #

    Great artical, I unfortunately had some problems printing this artcle out, The print formating looks a little screwed over, something you might want to look into.

  20. Caramoan Tour Package May 4, 2011 at 12:45 PM #

    I would really love to guest post on your blog.’*:”;

  21. decorative wall hooks May 4, 2011 at 12:47 PM #

    A blog like yours should be earning much money from adsense.”`,,.

  22. JDWilliams May 4, 2011 at 1:14 PM #

    nice one for sharing this with all of us. Of course, what a great site and informative posts, I will bookmark this site. keep doing your great job and always gain my support. cheers for sharing this beautiful article

  23. bipolar transistor May 4, 2011 at 1:24 PM #

    Your blog never ceases to amaze me, it is very well written and organized.~;-;”

  24. Raeann Beccaria May 4, 2011 at 2:27 PM #

    you are in reality a just right webmaster. The web site loading pace is incredible. It kind of feels that you are doing any distinctive trick. Also, The contents are masterpiece. you have performed a magnificent task in this topic!

  25. silica gel packets May 4, 2011 at 4:23 PM #

    This page iswhere I got the most useful information for my information gathering. cheers for posting, maybe we can see more on this. Are you aware of any other websites on this subject?

  26. Heated towel rails May 4, 2011 at 6:52 PM #

    I always visit your blog everyday to read new topics.:~*:,

  27. Disney Dining Secrets May 4, 2011 at 7:12 PM #

    I have to say i am very impressed with the way you efficiently blog and your posts are so informative. You have really have managed to catch the attention of many it seems, keep it up!

« Older Comments