Disaster: MySQL 5.5 Flushing

TLDR: on big machines with lots of memory (72GB), vanilla MySQL has great performance but suffers from minutes-long "stalls" during which throughput drops to zero. The only viable way the author found to avoid the stalls is to decrease throughput by 20x by cutting the amount of memory MySQL uses (thus wasting the hardware).

This is really terrible news for anyone trying to do high-throughput DB operations on mySQL.

The only "good" news is that the average web app isn't going to be running on hardware anywhere near as powerful as this, so you are highly likely to already be in the "slower", consistent state (e.g. anything less than 40GB of RAM).

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:

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:

  innodb_file_per_table = 1
  innodb_flush_log_at_trx_commit = 0
  innodb_buffer_pool_size = 5G
  innodb_additional_mem_pool_size = 20M
  innodb_log_file_size = 1024M
  innodb_log_buffer_size = 16M
  innodb_max_dirty_pages_pct = 90
  innodb_thread_concurrency = 4
  innodb_log_group_home_dir = /var/lib/mysql/
  innodb_log_files_in_group = 2

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.

I've switched to PostgreSQL several years ago. Bullet proof, exactly the features I need (and more), and no problems like these. Speaking of, I'm about to upgrade my installations to 9.1 now.

Yet another self-promotion article. Oh, we stuff data in memory and then when it is time to write changes on disk everything got frozen! MySQL suck, buy our solution! Nice guys.)

First, it is useless to store data in memory if you want them be committed into disk storage. The general idea here isn't about switching to some new version of mysql or SSD disks, it is about to realize that you have a data-flow inadequate for your one-server architecture.

Second - check-point intervals should be adjusted to your actual data-flow, which means they should be executed often enough. If there is situation of almost constant checkpoint - non-stop data writes, that is the sign that you need to consider sharding/multi-server solution.

The hints that there must be no other disk activity on the same hardware volume or any swapping in OS, I suppose, are obvious. People who have a /var/log and /var/db on the same volume are idiots.

There are also good idea to use one file per table storage and put a data and physical logs on a separate hardware volumes (links are your friends). One raid-X volume that fits all is a quite naive solution. Raid isn't a guaranty of reliability. Replications to a back-up servers are.

Third, when you test your configuration before put it into production, you should tune-up your servers to perform with data and log syncing, and then figure out appropriate buffer sizes and checkpoint intervals. Then, in production, when you're experiencing an increasing flow of queries, you may choice to switch into different syncing strategy and/or more often but a little bit faster checkpoints.

Configuring mysql with huge buffers and no sync means lack of understanding the basic concepts, self-delusion and misuse of software and hardware. ^_^

How is this different in MySQL 5.1?

From the article I get the impression that this is a new problem introduced with 5.5.

Why does he use flush_method = O_DIRECT?

Last time I checked that was a recipe for terrible performance. We normally leave the flush_method set to default and keep a couple slaves around to minimize the potential for data loss.

I can't see what was wrong with the second graph. Regular bursts of writes for flushing leaves the disks more idle for low-latency reads. Optimising throughput doesn't mean you have to have a smooth, flat write graph.

(For comparison, see ZFS)

I wonder if sharding in some clever way could alleviate the problem. Some way of having another instance processing queries while other one is flushing logs.

> Disaster: MySQL 5.5 Flushing

> Disaster: MySQL 5.5

> MySQL

I found your problem right here.

I have personally noticed MySQL having horrible performance for views that use joins. Every time a view does a join, it creates a temporary table and you might as well throw indexing out once that happens. I had a query that was taking 4 seconds with a view and used the same sql minus the view and got it down to 5ms. I know I am not being specific to flushing, but still it is an example of a potential performance problem.