To get the best locking performance out of InnoDB in 5.1, you’ll want to set the following options (“My Favorite New Feature of MySQL 5.1: Less InnoDB Locking” explains why):
This is beautiful! Check out this post on Bret Taylor’s blog:
We use MySQL for storing all of the data in FriendFeed. Our database has grown a lot as our user base has grown. We now store over 250 million entries and a bunch of other data, from comments and “likes” to friend lists.
As our database has grown, we have tried to iteratively deal with the scaling issues that come with rapid growth. We did the typical things, like using read slaves and memcache to increase read throughput and sharding our database to improve write throughput. However, as we grew, scaling our existing features to accomodate more traffic turned out to be much less of an issue than adding new features.
After some deliberation, we decided to implement a “schema-less” storage system on top of MySQL rather than use a completely new storage system. This post attempts to describe the high-level details of the system. We are curious how other large sites have tackled these problems, and we thought some of the design work we have done might be useful to other developers.
Hello World of blogging!
I just wanted to quickly mention something about how MySQL handles out-of-disk-space situations when importing an SQL file. Here’s my situation:
- I have an 80GB hard disk,
- 7GB free space,
- and am loading a 2GB SQL database dump .
No problem, right? Well not always. I started it importing and went to bed. In the morning it hadn’t finished and I was getting out-of-space notifications on my taskbar.
Thankfully, I’d seen this before and knew all I had to do was run this simple query:
While I don’t fully understand all the ins-and-outs of how it works, MySQL has a binary log that can grow considerably larger than your database. This simple query empties that log.
I ran that, and voila, 5GB free instantly. But here’s the interesting thing that I discovered. After stopping the import on the command line, MySQL gave this error message:
ERROR 20 (HY000) at line 2373: Dis is full writing '.\mydatabase\mytable.MYD' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs
So apparently, it had been waiting all night rather than crashing. Unfortunately, I stopped it before freeing disk space and restarted it from the beginning. But I guess that’s good, because otherwise I would have never known that MySQL did this!