This is awesome data as usual from Vadim and team. I always thought the upper bound of InnoDB performance was limited by memory and how much you can afford. Now I know different.
I wanted to share some of our data to help in your decision making:
A typical server in our cluster gets 5000 to 7500 TPS:
This is a 24 hour graph and as you can see there are no multi-minute lockups. We've been extremely happy with the performance our servers are delivering.
The config is 8GB of memory, RAID 1, 2X 15K RPM disks, single Intel E5410 quad core CPU.
Total DB size is around 12GB with around 15 million rows. Our TPS is higher than Vadim's but our data is smaller than the benchmark he uses which is 200 million rows in a 58 GB database.
So my approach to avoiding this issue will be to shard to finer granularity and avoid architectures with monolithic DB's. Keep in mind that this only affects your ability to cache greater than around 16GB, so spreading massive data across multiple servers with fast drives and moderate memory will help bring your TPS back up. I would also add that servers with memory > 32GB are very expensive - especially the memory itself. So it may not cost much more to have 5 servers with fast disk and moderate memory vs one server with massive memory.
Having said all that this is obviously a very serious problem and perhaps an opportunity for a talented computer scientist to branch InnoDB and solve it if Oracle doesn't want to.
A value of 1 is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.
Just depends whether or not you can deal with a small amount of lost data in the event of a crash.
I wanted to share some of our data to help in your decision making:
A typical server in our cluster gets 5000 to 7500 TPS:
http://i.imgur.com/oT26G.png
This is a 24 hour graph and as you can see there are no multi-minute lockups. We've been extremely happy with the performance our servers are delivering.
The config is 8GB of memory, RAID 1, 2X 15K RPM disks, single Intel E5410 quad core CPU.
Total DB size is around 12GB with around 15 million rows. Our TPS is higher than Vadim's but our data is smaller than the benchmark he uses which is 200 million rows in a 58 GB database.
With the following for innodb:
So my approach to avoiding this issue will be to shard to finer granularity and avoid architectures with monolithic DB's. Keep in mind that this only affects your ability to cache greater than around 16GB, so spreading massive data across multiple servers with fast drives and moderate memory will help bring your TPS back up. I would also add that servers with memory > 32GB are very expensive - especially the memory itself. So it may not cost much more to have 5 servers with fast disk and moderate memory vs one server with massive memory.Having said all that this is obviously a very serious problem and perhaps an opportunity for a talented computer scientist to branch InnoDB and solve it if Oracle doesn't want to.