RocksDB & ForestDB via the ForestDB benchmark, part 1

ForestDB was announced with some fanfare last year including the claim that it was faster than RocksDB. I take comparisons to RocksDB as a compliment, and then get back to work. This year I met the author of the ForestDB algorithm. He is smart and there are more talented people on the ForestDB effort. It would be great to see a MongoDB engine for ForestDB.

ForestDB

I used the ForestDB benchmark to evaluate RocksDB and ForestDB. Results from the tests will be shared in future posts. Here I describe my experience running the tests. I forked the ForestDB benchmark as I made general and RocksDB-specific improvements to it.

ForestDB is tree-structured for the index and log-structured for the data. Everything is copy-on-write so there is one database and no log. This is kind of like Bitcask except Bitcask is hash structured for the index and the index isn't persistent. The write-amplification vs space-amplification tradeoff is configurable via the threshold parameter that sets the percentage of dead data allowed in the database file before the compaction thread will copy out live data into a new file. For a uniform distribution of writes to keys, space-amp is 2 and write-amp is 2 with threshold=50%. Reducing threshold to 25% means that space-amp is 4/3 and write-amp is 4. With leveled compaction in RocksDB we usually get higher write-amplification and lower space-amplification compared to ForestDB.

The ForestDB database can be sharded into many files per process. I am not sure if transactions can span files. There is only one compaction thread regardless of the number of files and during my tests it was easy for compaction to fall behind. Single-threaded compaction might be stalled by file reads, decompression after file reads and compression before file writes. I think that for every key read during compaction, the index must be checked to confirm the key is live, so this can be another reason for compaction to fall behind. I assume work can be done to make this better.

ForestDB benchmark client

I forked the ForestDB benchmark client. When I get more time I will try to push changes upstream. Here I describe changes I made to it using the git commit log:

• e30129 - link with tcmalloc to get better performance and change the library link order
• 1c4ecb - accept benchmark config filename on the command line
• 3b3a3c - add population:compaction_wait option to determine whether to wait for IO to settle after the load. Their benchmark results include some discussion of whether the test should wait for IO to settle after doing a load. While I don't think the test should wait for all IO to stop, I think it is important to penalize an engine that incurs too much IO debt during the load phase which will make queries that follow much slower. This is still work-in-progress for RocksDB although setting the options hard_rate_limit and soft_rate_limit make this much less of an issue.
• aac5ba - add population:load option to determine whether the population (load) step should be done. This replaces a command line flag.
• aa467b - adds the function couchstore_optimize_for_load to let an engine optimize for load performance and the RocksDB version uses the vector memtable for loads and otherwise uses the skiplist memtable. This also adds the function couchstore_set_wal to enable/disable redo logging. Finally this adds a better configuration in all cases for RocksDB. I know that it isn't easy to configure RocksDB. The changes include:
• set max_bytes_for_level_base to 512M. It is 10M by default. This is the size of the L1.
• set target_file_size_base to 32M. It is 2M by default. This is the size of the files used for levels L1 and larger.
• enable level_compaction_dynamic_level_bytes. This is a big improvement to the algorithm for leveled compaction. We need a blog post to explain it. 
• set stats_dump_period_sec to 60. The default is 600 and I want more frequent stats.
• set block_size to 8kb. The default is 4kb before compression and I don't want to do ~2kb disk reads. Nor do I want a block with only 4 1kb docs as that means the block index is too large.
• set format_version to 2. The default is 0 and the RocksDB block cache can waste memory when this is 0. This might deserve a blog post from the RocksDB team.
• set max_open_files to 50000. The default is 5000 and I want to cache all database files when possible.
• 3934e4 - set hard_rate_limit to 3.0 and soft_rate_limit to 2.5. The default is to not use rate limits. When set these limit how much IO debt a level can incur and then user writes are throttled when the debt is too large. Debt in this case is when the level has too much data. The size of L1 should be max_bytes_for_level_base. Setting the soft_limit to 2.5 means that writes are delayed when it gets 2.5X of the target size. Setting the hard rate_limit to 3.0 means that writes are stopped when it gets to 3.0X of the target size. We have work-in-progress to make throttling much better as it can lead to intermittent stalls (bad p99) for write-heavy workloads. This also disables compression for levels 0 and 1 in the LSM to reduce the chance of compaction stalls.
• a6f8d8 - this reduces the duration for which a spin lock is held in the benchmark client. I was getting odd hangs while running valgrind and this change fixed the problem. AFAIK the change is correct too.
• cfce06 - use a different RNG seed per thread. Prior to this change all threads start with the same seed and can then generate the same sequence of key values. This can be really bad when trying to get IO-bound databases as it inflates the block cache hit rate. The LevelDB benchmark client has a different version of this problem. It uses a different seed per thread, but the seed per thread is constant. So if you restart the benchmark client then thread N generates the same sequence of keys that it generated on the last run. I fixed this in RocksDB (see the --seed option for db_bench).
• 5a4de1 - add a script to generate benchmark configuration files for different workload patterns. See the next section for a description of the patterns.
• 2b85fa - disable RocksDB WAL when operation:write_type=async is used. I reverted this part of the diff later. This also sets level0_slowdown_writes_trigger to 12 and level0_stop_writes_trigger to 16. The defaults were larger.
• eae3fa - adds a helper script to run the pattern of tests. This is described in Benchmark pattern.
• 871e63, e9909a - ForestDB wasn't ever doing fsync/fdatasync during the load phase. This fixes that. With these changes write-amplification is much larger for ForestDB when periodic_commit is enabled and write_type=sync. These changes provide the following behavior for existing config options:
• population:periodic_commit - for ForestDB when set there is a commit per insert batch and when not set there is a commit at the end of the load. This has not changed. For RocksDB there is always one write batch per insert batch, but when set the WAL is used and when not set the WAL is not used. This does not control whether fsync/fdatasync are used. When periodic_commit is used then a load might be restartable when it fails in the middle. When not used, then a failure means load must start over.
• operation:write_type - when set to sync then fsync/fdatasync are done per commit. This is a change for ForestDB but not for RocksDB.

Benchmark pattern

The script rall.sh runs tests using a pattern that is interesting to me. The pattern is listed below. The first step loads N documents and this takes a variable amount of time. The steps that follow run for a fixed amount of time. All of the tests used uniform random RNG to generate keys. The script also uses "numactl --interleave=all" given that I was using dual socket servers and a lot of RAM in many cases. The script also collects iostat and vmstat to help explain some performance differences.

1. Load - this is the populate step. The PK value is hashed from an increasing counter (from 1 to N) so the load isn't in PK order. The test is configured to not wait for IO to settle when the load ends and if the engine has too much IO debt then the tests that follow can suffer.
2. Overwrite-sync-1 - uses one writer thread to update docs. It does an fsync on commit.
3. Overwrite-sync-N - like Overwrite-sync-1 but uses N writer threads.
4. Point-query-1-with-writer - uses 1 reader thread that does point queries and 1 writer thread that updates docs. There is a rate limit for the writer. Read performance with some writes in progress is more realistic than without writes in progress especially for write-optimized database engines like RocksDB. More info on that topic is here.
5. Point-query-N-with-writer - like Point-query-1-with-writer but uses N reader threads.
6. Range-query-1-with-writer - like Point-query-1-with-writer but does range queries
7. Range-query-N-with-writer - like Range-query-1-with-writer but uses N reader threads
8. Point-query-1 - like Point-query-1-with-writer, but without a writer thread
9. Point-query-N - like Point-query-N-with-writer, but without a writer thread
10. Range-query-1 - like Range-query-1-with-writer, but without a writer thread
11. Range-query-N - like Range-query-N-with-writer, but without a writer thread
12. Overwrite-async-1 - like Overwrite-sync-1 but does not do fsync-on-commit. This was moved to the end of the test sequence because it frequently made compaction get too far behind in ForestDB
13. Overwrite-async-N - like Overwrite-sync-N but does not do fsync-on-commit. Also moved to the end like Overwrite-async-1.

Benchmark configuration

My test scripts use a template for the benchmark configuration that is specialized for each run. The gen_config.sh script has a few test-specific changes especially for the load test. Changes that I made from one of the published configurations include:

• set db_config:bloom_bits_per_key to 10 to use bloom filters with RocksDB
• set threads:seed_per_thread to 1 to use different RNG seeds per thread
• set body_length:compressibility=50
• use operation:batch_distribution=uniform. Eventually I will try others.
• use operation:batchsize_distribution=uniform with batchsize 1 (read_batchsize_*_bound) for point queries and a configurable batchsize (iterate_batchsize_*_bound) for range queries. In the Benchmark debugging section I will explain why I do this.

Benchmark debugging

I started with an IO-bound configuration and data on disk and ForestDB was getting about 1.5X more QPS than RocksDB for a read-only point-query workload. I had two servers with identical hardware and this result was reproduced on both servers. So it was time to debug. The test used one reader thread, so this result wasn't ruined by using the same RNG seed for all threads.

I started by confirming that the ratio of disk reads per query were similar for RocksDB and ForestDB. Each did about 1.5 queries per disk read courtesy of cached data. However the average disk read latency reported by iostat was about 1.5X larger for RocksDB. Why was the disk faster for ForestDB?

Next I used strace to see the system calls to read blocks. I learned that page reads for data were 4kb and aligned with ForestDB. They were usually less than 4kb and not aligned with RocksDB. Both use buffered IO. From iostat the average read size was 4kb for ForestDB and about 8kb for RocksDB. Many of the unaligned almost 4kb reads crossed alignment boundaries so the unaligned ~4kb read required an 8kb read from disk. This could be a performance problem on a fast SSD but there is not much difference between 4kb and 8kb reads from a disk array and I re-confirmed that via tests with fio. I also used blktrace to get more details about the IO pattern and learned that my servers were using the CFQ IO scheduler. I switched to deadline.

At this point I am stuck. Somehow ForestDB is getting faster reads from disk or the read pattern wasn't as random as I thought it would be. So I read more of the benchmark client and saw that point reads were done in a batch where the key for the first document per batch was selected at random (call it N) and then the keys for the rest of the batch were N+1, N+2, N+3, etc. This gives ForestDB an advantage when only one database file is used because with M files the reads in one batch might use different files. The load and query code both share a function that converts an integer to a key, so N here is an integer and the key is something that includes a hash of the integer. This means that the key for N=3 is not adjacent to the key for N=4. However, the data file for ForestDB is log structured and immediately after the load the data for N=3 is adjacent to the data for N=4 unless updates were done after the load to either of the documents.

At last I understood the cause. ForestDB was benefiting from fetching data that was adjacent in the file. The benefit came from caching either in the HW RAID device or disks. I changed the test configuration to use batchsize=1 for point queries and then ForestDB and RocksDB began to experience the same average read latency from the disk array.

Comparing LevelDB and RocksDB, take 2

I previously explained problems to avoid when comparing RocksDB and LevelDB. I am back with more details and results because someone is wrong on the Internet. The purpose for the test was to determine whether we created any regressions after reading a comparison published by someone else where RocksDB had some problems. Note that the LevelDB and RocksDB projects have different goals. I expect RocksDB to be faster, but that comes at a cost in code and configuration complexity. I am also reluctant to compare different projects in public. The good news is that I didn't find any performance regression in RocksDB, it is faster as expected but the overhead from performance monitoring needs to be reduced.

I made a few changes to LevelDB before running tests. My changes are in github and the commit message has the details. Adding the --seed option for read-heavy tests is important or LevelDB can overstate QPS. The next step was to use the same compiler toolchain for RocksDB and LevelDB. I won't share the diff to the Makefile as that is specific to my work environment.

I use the following pattern for tests. The pattern was repeated for N=1M, 10M, 100M and 1000M with 800 byte values and a 50% compression rate. The database sizes were approximately 512M, 5G, 50G and 500G. The test server has 40 hyperthread cores, 144G of RAM and fast storage.

1. fillseq to load a database with N keys
2. overwrite with 1 thread to randomize the database
3. readwhilewriting with 1 reader thread and the writer limited to 1000 Puts/second. The rate limit is important to avoid starving the reader. Read performance is better when the memtable is empty and when queries are done immediately after fillseq. But for most workloads those are not realistic conditions, thus overwrite was done prior to this test.
4. readwhilewriting with 16 reader threads and the writer limited to 1000 Puts/second
5. readrandom with 1 thread
6. readrandom with 16 threads
7. overwrite with 1 thread
8. overwrite with 16 threads

Results

I ran the RocksDB tests twice, with statistics enabled and disabled. We added a lot of monitoring in RocksDB to make it easier to explain performance. But some of that monitoring needs to be more efficient for workloads with high throughput and high concurrency. I have a task open to make this better.

These are command lines for LevelDB, for RocksDB with stats and for RocksDB without stats with 1M keys. There are many more options in the RocksDB command lines. When we decide on better defaults for it then the number can be reduced. This post has more details on the differences in options between LevelDB and RocksDB. There are some differences between LevelDB and RocksDB that I did not try to avoid.

• LevelDB uses 2MB files and I chose not to change that in source before compiling. It tries to limit the LSM to 10M in L1, 100M in L2, 1000M in L3, etc. It also uses a 2M write buffer which makes sense given that L0->L1 compaction is triggered when there are 4 files in L0. I configured RocksDB to use a 128M write buffer and limit levels to 1G in L1, 8G in L2, 64 G in L3, etc.
• For the 100M and 1000M key test the value of --open_files wasn't large enough in LevelDB to cache all files in the database.
• Statistics reporting was enabled for RocksDB. This data has been invaluable for explaining good and bad performance. That feature isn't in LevelDB. This is an example of the compaction IO stats we provide in RocksDB.
• Flushing memtables and compaction is multithreaded in RocksDB. It was configured to use 7 threads for flushing memtables and 16 threads for background compaction. This is very important when the background work is slowed by IO and compression latency. And compression latency can be very high with zlib although these tests used snappy. A smaller number would have been sufficient but one thread would have been too little as seen in the LevelDB results. Even with many threads there were stalls in RocksDB. Using this output from the overwrite with 16 threads test look at the Stall(cnt) column for L0 and then the Stalls(count) line. The stalls occur because there were too many L0 files. It is a challenge to move data from the memtable to the L2 with leveled compaction because L0->L1 compaction is single threaded and usually cannot run concurrent with L1->L2 compaction. We have work in progress to make L0->L1 compaction much faster.

Details

The data below shows the QPS (ops/sec) and for some tests also shows the ingest rate (MB/sec). I like to explain performance results but the lack of monitoring in LevelDB makes that difficult. My experience in the past is that it suffers from not having concurrent threads for compaction and memtable flushing especially when the database doesn't fit in RAM because compaction will get more stalls from disk reads.

My conclusions are:

• read throughput is a bit higher with RocksDB
• write throughput is a lot higher with RocksDB and the advantage increases as the database size increases
• worst case overhead for stats in RocksDB is about 10% at high concurrency. It is much less at low concurrency.

--- 1M keys, ~512M of data

  RocksDB.stats  :  RocksDB.nostats  :     LevelDB

ops/sec  MB/sec  :  ops/sec  MB/sec  :   ops/sec  MB/sec  : test

 231641   181.1  :   243161   190.2  :    156299   121.6  : fillseq

 145352   113.7  :   157914   123.5  :     21344    16.6  : overwrite, 1 thread

 113814          :   116339          :     73062          : readwhilewriting, 1 thread

 850609          :   891225          :    535906          : readwhilewriting, 16 threads

 186651          :   192948          :    117716          : readrandom, 1 thread

 771182          :   803999          :    686341          : readrandom, 16 threads

 148254   115.9  :   152709   119.4  :     24396    19.0  : overwrite, 1 thread

 109678    85.8  :   110883    86.7  :     18517    14.4  : overwrite, 16 threads

--- 10M keys, ~5G of data

  RocksDB.stats  :  RocksDB.nostats  :     LevelDB

ops/sec  MB/sec  :  ops/sec  MB/sec  :   ops/sec  MB/sec  : test

 226324   177.0  :   242528   189.7  :   140095   109.0   : fillseq

  86170    67.4  :    86120    67.3  :    12281     9.6   : overwrite, 1 thread

 102422          :    95775          :    54696           : readwhilewriting, 1 thread

 687739          :   727981          :   513395           : readwhilewriting, 16 threads

 143811          :   143809          :    95057           : readrandom, 1 thread

 604278          :   676858          :   646517           : readrandom, 16 threads

  83208    65.1  :    85342    66.7  :    13220    10.3   : overwrite, 1 thread

  82685    64.7  :    83576    65.4  :    11421     8.9   : overwrite, 16 threads

--- 100M keys, ~50GB of data

  RocksDB.stats  :  RocksDB.nostats  :     LevelDB

ops/sec  MB/sec  :  ops/sec  MB/sec  :   ops/sec  MB/sec  : test

 227738   178.1  :   238645   186.6  :    64599    50.3   : fillseq

  72139    56.4  :    73602    57.6  :     6235     4.9   : overwrite, 1 thread

  45467          :    47663          :    12981           : readwhilewriting, 1 thread

 501563          :   509846          :   173531           : readwhilewriting, 16 threads

  54345          :    57677          :    21743           : readrandom, 1 thread

 572986          :   585050          :   339314           : readrandom, 16 threads

  74292    56.7  :    72860    57.0  :     7026     5.5   : overwrite, 1 thread

  74382    58.2  :    75865    59.3  :     5603     4.4   : overwrite, 16 threads

--- 1000M keys, ~500GB of data

Tests are taking a long time...

  RocksDB.stats  :    LevelDB

ops/sec  MB/sec  :  ops/sec  MB/sec  : test

 233126   182.3  :     7054     5.5  : fillseq

  65169    51.0  :                   : overwrite, 1 thread

   6790          :                   : readwhilewriting, 1 thread
  72670          :                   : readwhilewriting, 16 threads

Much ado about nothing

Kyle does amazing work with Jepsen and I am happy that he devotes some of his skill and time into making MongoDB better. This week a new problem was reported as stale reads despite the use of the majority write concern. Let me compress the bug report and blog post for you, but first this isn't a code bug this is expected behavior for async replication.

1. MongoDB implements asynchronous master-slave replication
2. Commits can be visible to others on the master before oplog entries are sent to the slave

The problem occurs when transaction 1 commits a change on the master, transaction 2 views that change on the master, the master disappears and a slave is promoted to be the new master where the oplog entries from transaction 1 never reached any slave. At this point transaction 1 didn't happen and won't happen on the remaining members of the replica set yet transaction 2 viewed that change. A visible commit has been lost.

When reading MongoDB source I noticed this in early 2014. See my post on when MongoDB makes a transaction visible. I even included a request to update the docs for write concerns and included this statement:

I assume the race exists in that case too, meaning the update is visible on the primary to others before a slave ack has been received.

This isn't a bug, this is async replication. You can fix it by adding support for sync replication. The majority write concern doesn't fix it because that only determines when to acknowledge the commit, it does not determine when to make the commit visible to others.  For now the problem might be in the documentation if it wasn't clear about this problem. The majority write concern is a lot like semisync replication in MySQL and then clever people added lossless semisync replication so that commits aren't visible on the master until they have been received by a slave. Finally, really clever people got lossless semisync replication running in production and we were much happier.

Big numbers for web scale MySQL

It is conference time. I haven't been at the MySQL UC as I am at another conference (ICDE) so I missed the talks but tweets make strong claims about a successful MySQL deployment.

• Tens of PB - maybe that doesn't fit in memory after all
• High availability - automated fast failover in less than 30 seconds courtesy of lossless semisync and a lot of hard work from the MySQL PE team. See Fast Master Failover without Data Loss.
• 170M QPS at peak - sorry, no tweet to share yet

How to win at IO-bound benchmarks

I spend time evaluating database performance on IO-bound workloads and have learned a few things that can help you to get much better results on benchmarks than will occur in production. But this is really about benchmarketing.

The tips are:

• Load the database in key order to avoid a fragmented B-Tree index or a randomized LSM tree. If there is a secondary index then create it after the load to avoid fragmentation there. This makes the database smaller and can make searches faster.
• Only use 10% of the storage device. This reduces the average seek latency for disks. This also reduces the overhead from flash GC because there is less live data to copy out during erase block cleaning. This also makes it less likely that erase block cleaning will be needed during the test. Erase-block cleaning can also be avoided by starting with a freshly setup or long-idle SSD.
• Don't run tests for too long. This reduces the chance that a B-Tree index will become fragmented, that an LSM will get slower from running compaction and that erase block cleaning will be started for an SSD. 
• Use a fixed number of user threads. When there is a stall there will be at most N(threads) operations that are stalled. In the real world the request arrival rate is usually steady and a stall will create a huge convoy of requests. If you are measuring p99 latency then a fixed number of threads will allow you to understate the impact from stalls. This is called coordinated omission and YCSB has been updated to support a steady arrival rate. Don't use that feature. I think fio will also be updated. I still need to fix innosim.
• Use a small number of threads to measure response time and a large number of threads to measure throughput. Make sure that writes and erase block cleaning are not in progress when measure read performance.

For more tips specific to LevelDB and RocksDB see an old post by me.

One other tip. If you are using a benchmark with more than 2B rows/keys/documents make sure that the random number generator supports that. It might not in LevelDB.

Capacity planning and QPS

As much as I like the technology from in-memory DBMS vendors like MemSQL, VoltDB and MySQL Cluster I am amused at some of the marketing that describes capacity planning as a function of QPS. It usually isn't true that when a workload has a peak QPS of X and the peak per-server QPS for the product is Y, then X/Y nodes is sufficient. The Oracle keynote from Mr. Big is a an example of this.

Work in web-scale database land is more complex courtesy of additional constraints including database size, network latency and network bandwidth. Workloads don't require X QPS, they do require X QPS over a database of Y PB with requirements for response time. When Y is large then it helps to not keep all data in RAM. This is made easier when a workload has data access skew so that the hot data can be in RAM while much more data is on disk.

With fast storage there are clever ways to keep indexes in RAM and data on SSD and with proper indexing queries are limited to one or two reads from the storage device. This can provide excellent response times without keeping everything in RAM. The latency from an SSD read might double the query response time compared to an in-memory database which is acceptable for many workloads. Aerospike seems to be good at this but this can also be done with InnoDB (covering indexes and a clustered PK FTW).

Network latency and bandwidth are additional constraints for capacity planning. While they don't determine whether data must be in RAM they might determine how many copies are required for both the database and any caches that accompany the database and by cache I mean something like memcached. With more copies of the data you are likely to have a copy closer to the clients (clients == web tier) and that reduces the network latency added to queries. Even with a database capable of 1B QPS a deployment can require caches to avoid the extra network latency as latency and bandwidth to that fast database server can be a bottleneck.

Compared to what?

It is common to share big numbers for web-scale database deployments. I do it frequently in presentations. I am not alone in this practice. Is is easy to get large values for QPS with web-scale OLTP (small data). The same is true for database size and rows read rates with web-scale data warehousing (big data).

I hope that So What? is the first reaction when these big numbers are shared. Big numbers only mean that a lot of hardware has been used. Context is what makes these big numbers more or less interesting. Note that I am not saying this to take away from the work done by my peers. I have been fortunate to work with extremely talented teams.

Compared to what? is another useful response. When considering stock mutual funds we look at the performance of the fund relative to a benchmark such as S&P 500. When considering database performance it helps to understand whether an alternative product would have done better. We usually don't have an answer for this because it can be too expensive to do the comparison, but is still something to keep in mind.

We aren't in the business of growing QPS, database size and rows read rates. We are in the business of answering questions with efficiency and quality of service. The goals include increasing availability, reducing response time, reducing response time variation and doing more work with less HW. Details about these goals are less likely to be shared -- for business reasons and sometimes because the data isn't collected -- so the context required to appreciate the big numbers might always be missing.

Fast index create

What is a fast way to create secondary indexes for a write-optimized database engine?

Back in the day the only way to create a secondary index for InnoDB was via incremental maintenance. Insert, update and delete statements would maintain secondary indexes as needed. The CREATE INDEX command for a secondary index would make a copy of the table, define all indexes (primary and secondary) and then scan the source table in PK order and insert rows from the scan into the copy table while maintaining secondary indexes after each insert.  In most cases the result from this is a secondary index subject to changes in a random sequence. That means the secondary index is fully fragmented immediately after index create and there was no way to defragment a secondary index. Fragmentation is bad as it means you are wasting space and using about 1.5X the space for the index compared to the index without fragmentation.

Today InnoDB is able to create a secondary index in the expected way via scan/sort/write and fragmentation is much better. The expected way is to scan the base table or another secondary index to get the columns for the new index, sort those columns and then write out the secondary index in key order. I will ignore the complexity of logging to allow other changes concurrent with the index create. Not only does this reduce fragmentation but it also reduces random IO -- the index is written sequentially and there are no random disk reads to get secondary index leaf pages during the read-modify-write cycle of an update for a b-tree.

The best-practice for fast index creation can change for a write-optimized database engine when the engine supports a blind-write in addition to a read-modify-write internally. By this standard a b-tree is not write-optimized whether it is update-in-place like InnoDB or copy-on-write like WiredTiger. Updates and inserts with a b-tree must read the old copy of the page, modify it and then eventually write the page back to storage. There is always a read before the write even with the InnoDB change buffer where the read is deferred. But with an LSM like RocksDB or WiredTiger and probably with a fractal tree like Tokutek updates can be done as blind-writes in many cases like when the secondary index to be updated is not unique. By blind-write I mean there isn't a read before the write. This means that the random reads that make incremental index maintenance for a b-tree slow can be avoided with a write-optimized engine. The random writes can also be avoided assuming the engine really is write-optimized when flushing changes to storage.

It might take some time for us to learn that the rules have changed. It might also take time for the rules to really change when there are robust write-optimized engines available for MySQL and MongoDB.

Update - today I learned the RocksDB storage engine for MongoDB doesn't do reads for non-unique secondary index maintenance.

A few possibly true facts about indexes in MongoDB, RocksDB and WiredTiger

These are probably correct as of April 1, 2015. They might not be correct next year. We have a lot of work to do to explain the storage engines available in MongoDB 3. I welcome corrections.

• The primary key index is not clustered in MongoDB for RocksDB or WiredTiger. I assume this is a limitation of the storage engine API and hope a clustered PK index is supported in the future. The PK index is a map from PK columns to RecordId and (I assume) RocksDB and WiredTiger have a clustered index on RecordId and the documents are in the leaf nodes of the RecordId index. The impact from not having a clustered PK index include:
• Much less RAM is required to cache the PK index because it is not clustered but if the table is huge and you want to guarantee that at most one disk read is required for a point query then a non-clustered PK requires much more RAM then a clustered PK. With the clustered PK you only need the level above the leaf in RAM and that only requires 1 (key, block pointer) pair per block in the leaf level. With the non-clustered PK you need one (key, block pointer) pair per row in the table.
• For RocksDB and WiredTiger going from RecordId to the document requires searching an index (b-tree or LSM for WT, LSM for RocksDB). An index search is not required for mmapv1 as the RecordId is a filename + offset in that case. This might make read-mostly workloads on a cached working set faster with mmapv1.
• If values adjacent in the PK index are not adjacent in the RecordId index then a scan in PK order can do a lot of random IO as it moves around in the RecordId index.
• The WiredTiger b-tree can use prefix compression for indexes and block compression for data. I think this means that block compression is used for the leaf nodes of the RecordId index. I am not sure if it is also used for the leaf nodes of the other indexes. I guess that only prefix compression is used for the non-leaf nodes of all indexes.
• With RocksDB we need to be careful about terminology. With leveled compaction the block-based table format is used for MongoDB and most of the data in an LSM level is from pages, and then the block index and bloom filter. This is true for secondary indexes, primary indexes and the RecordId index. Currently all data is in one column family and indexes are distinguished by using the index ID as the prefix of the key given to RocksDB but we can ignore that for now. Lets also ignore bloom filters and just focus on pages and the block index. The interesting details are:
• There is one key per page in the block index so this is unlikely to use much space compared to the pages. 
• Key value pairs are stored in the pages in key order and prefix compression is done for them. Prefix compression is not done for the block index. Logically the value in the secondary index is the PK columns but the PK columns are used here to make the secondary index key unique. Logically the value in the PK index is the RecordId, but I think that the implementation puts the RecordId at the end of the PK columns in the key. And for the RecordId index the value is the Document.
• Block compression, when configured, is done for all pages. So it might be possible that more data can get the benefit from block compression with RocksDB than with WiredTiger and this can be a big deal when wide (covering) secondary indexes are used. However I still have questions about where the block compressor is used for WT.

Formal methods in the real world

A lot of interesting work is done at Amazon but not much is written about it. There is an article on their use of formal methods via TLA+ and PlusCal. I haven't seen much use of formal methods in my career. I have spent a lot of time debugging code written by other people, so I wouldn't mind if such tools were used more frequently.

I used the Spin tool for two MySQL features. I hope other people try the tool and read the book. I first used it when we rewrote the InnoDB RW-lock at Google. The RW-lock had an awkward implementation that limited performance on many-core and many-socket servers. The new implementation was (mostly) lock free and inspired by the excellent work of Yasufumi Kinoshita. We used Spin to verify that the state transitions were probably correct. I use the word probably because Spin was used to explore the state space to find deadlocks and assertion failures. This wasn't a formal proof of correctness. We shared the Spin model with the InnoDB team when contributing the code. Our change made it into upstream InnoDB and that was one step in making MySQL better for many-core servers.

I used Spin a second time while implementing admission control for MySQL. I used a lock-free protocol to maintain per-user counts of running queries. This was done to avoid mutex contention and the code was more complex than using atomic increment/decrement. I re-read the Spin book prior to writing the Spin model and think I was better at it than during my first project.

Helping make MySQL better

My peers have been very busy the past few years helping to make MySQL better for web-scale deployments. Much of this effort is also good for deployments that aren't web-scale. Join us at Percona Live 2015 to learn more.

• Polyglot persistence @ FB keynote by Harrison Fisk
• WebScaleSQL keynote by Steaphan Greene
• MySQL for Facebook Messenger by Domas Mituzas
• MySQL Automation at Facebook Scale by Shlomo Priymak
• LSM Databases at Facebook by Yoshinori Matsunobu
• XDB: Shared MySQL Hosting at Facebook Scale by Evan Elias
• Online InnoDB Defragmentation by Rongrong Zhong
• Fast Master Failover without Data Loss by Yoshinori Matsunobu
• Row Based Replication and Incremental Logical Backup by Santosh Banda
• DocStore Document Database for MySQL at Facebook by Peng Tian and Tian Xia
• WebScaleSQL talk by Steaphan Greene
• WebScaleSQL BOF with Steaphan Greene. I hope other members from the WebScaleSQL effort will attend.
• RocksDB storage engine for MySQL BOF. I hope people from the core RocksDB team will attend.

Storage efficiency

Storage efficiency is a big deal. I have learned a lot about read, write and space amplification over the past few years. Tiered storage has been a part of my work life via flashcache, but tiered storage for an LSM doesn't require flashcache. This is going to get interesting. We have more choices for SSD at a variety of price points based on write endurance and performance.  We have write-optimized database engines (RocksDB, Tokutek, WiredTiger) arriving for OLTP workloads. We can use this with commodity hardware and open-source DBMS solutions like MySQL and MongoDB. A lot of interesting work remains to put the pieces together while matching the quality of service we get from MySQL+InnoDB and I hope to use RocksDB as part of MySQL and MongoDB.

We are beginning to engage with the community. Yoshinori Matsunobu has a talk at the MySQL UC. I have a talk at the CloudDM workshop at ICDE. I expect more talks, hopefully a few conference papers and if you are local then there are RocksDB meetups.

One benefit from RocksDB compared to a b-tree is much better compression and much less write-amplification. I have observed this via linkbench and real workloads. There are many other ways that an LSM can reduce the IO demand from an application. But I don't want to give away too many details until Yoshi and I have done our talks.

I don't get marketing

MongoDB 3.0 is almost here. It includes the WiredTiger storage engine which is very impressive. Alas, now we are told that the original storage engine has performance problems. I am shocked. Who knew? Not anyone reading their documentation. I don't understand why companies play this game. Be honest with your users as this goes a long way to building a solid and educated community. I hold MySQL to the same standard. Lets us not forget that awesome manual section on atomic operations.

Don't let the marketing team have editorial control over the documentation team.

Edit: an era has ended, the section on atomic operations has been removed from the MySQL manual.

malloc and MongoDB performance

I used iibench to understand the impact of malloc on MongoDB performance to compare malloc from glibc 2.17, jemalloc 3.6.0 and tcmalloc/gperftools 2.2 for an insert-only workload with 10 concurrent clients. The test server has 40 cores with hyperthread enabled. Alas, I lost a few configuration details, but think I used MongoDB 2.8.0rc4 and the WiredTiger b-tree.

The summary is that tcmalloc and jemalloc provide a similar benefit and are much better than glibc malloc. I made no attempt to tune tcmalloc or jemalloc. I ran iibench 3 times for each configuration and chose the median result. There are four metrics for each configuration:

1. Test duration in seconds
2. Address space size per the VSZ column in ps
3. Normalized context switch rate - the number of context switches per N documents inserted. I will leave N as undefined for now so the absolute value isn't interesting. The value can be compared between malloc implementations.
4. Normalized CPU utilization - CPU utilization per N documents inserted. See #3.

Results

          seconds  VSZ(GB)   context-switch(relative)   CPU(relative)

tcmalloc   1943     65.1            6.6                   26510

jemalloc   1877     64.5            7.1                   27165

glibc      2251     72.3           23.0                   32120

For this test glibc uses 1.19X more time, 1.12X more address space, 3.23X more context switches and 1.18X more CPU than jemalloc and tcmalloc is similar to jemalloc. As tcmalloc is bundled with the source distribution for MongoDB I assume it is used in the binaries they distribute. This appears to be a good thing.

Storage overhead for attribute names in MongoDB

The flexibility of dynamic schema support in MongoDB comes with a few costs. One of the costs is that extra space is required to store the database. The problem is that attribute names are repeated in every document. If sizeof(attribute names) is significant relative to sizeof(attribute values) then a lot more space will be used for MongoDB with the mmapv1 engine compared to a DBMS that doesn't support dynamic schemas. Note that dynamic schemas are valuable because they support less schema, not no schema. Indexes, required attributes and assumptions about attribute values are all examples where some schema is used.

How much extra space is used for attribute names? Does page level compression in the WiredTiger and TokuMX engines make this a non-issue? Repeated values in every document from long attribute names seem like something that is easy to compress. And the alternative of using extra short attribute names will cause pain for anyone trying to use the database so page level compression might be the preferred solution.

While page level compression can remove the bloat from long attribute names for compressed copies of pages it doesn't solve the problem for uncompressed copies of pages. So there is still a cost from dynamic schemas. Perhaps one day we will get an engine that encodes long attribute names efficiently even for uncompressed pages. The impact from this is that fewer uncompressed pages can fit in cache because of the space overhead. Note that when page level compression is used there are some database pages that are in cache in both compressed and uncompressed forms. I assume that the WiredTiger and TokuMX block caches only cache uncompressed pages, but I am not an expert in either engine, and the OS filesystem cache has copies of compressed pages. I am not sure what happens when direct IO is used with WiredTiger because that prevents use of the OS filesystem cache.

Results

I used iibench for MongoDB and loaded 2B documents for a few configurations: mmapv1 and WiredTiger without compression (wt-none), with snappy compression (wt-snappy) and with zlib compression (wt-zlib). To keep the documents small I edited the iibench test to use a 1-byte character field per document and disabled creation of secondary indexes. I used two versions of iibench. The first used the attribute names as-is (long) and the second used shorter versions (short) for a few of the attribute names:

• long attribute names: price, customerid, cashregisterid, dateandtime
• short attribute names: price, cuid, crid, ts

There are two graphs below. The first graph has the ratio of the database size with long attributes versus the size with short attributes. A larger ratio means there is more overhead from long attributes and the mmapv1 engine has the worst ratio (1.53). The ratio for WiredTiger with the zlib engine is close to one (1.01). WiredTiger with snappy wasn't as good as zlib and I think that part of the reason is that a larger page size is used by WiredTiger when zlib compression is enabled. While that improves the compression ratio it can cause other performance problems but I am waiting for documentation to catch up to the code to understand this. One problem from a larger page size is that more memory is wasted in the block cache when a page is read to get one hot document. Another problem from a larger page size is that flash devices are usually much faster at 4kb reads than at 64kb reads, while there isn't much difference with disk for 4kb versus 64kb reads. For an IO-bound workload, there is also more CPU overhead from decompressing a 64kb page versus a 4kb page.

This shows the database size in GB for each of the engine configurations. Note that WiredTiger with zlib uses about 1/8th the space compared to mmapv1 and even the uncompressed WiredTiger engine does a lot better than mmapv1. I suspect that most of the benefit for wt-none versus mmapv1 is the overhead from using power of 2 allocations in mmapv1. As a side note, I am not sure we will be able to turn off power of 2 allocation for mmapv1 in future releases.

Read-modify-write optimized

A log structured merge tree (LSM) has optimizations that reduce the amount of reads from and writes to storage for a write-heavy workload. These optimizations aren't unique to an LSM as the fractal tree algorithm used by TokuMX and TokuDB has similar benefits.

There are different kinds of writes so I will explain this workload. I used iibench for MongoDB with 3 secondary indexes. This loads a collection in primary key order. Random values are used for attributes with secondary indexes. I repeated the test for different database cache sizes to show where an LSM is faster than a b-tree. While I wouldn't call the WiredTiger btree a write-optimized database algorithm, it is much more IO efficient than the mmapv1 engine (uses less space on disk, reads less from and writes less to storage for the same workload).

Results

This displays the document insert rate from iibench with 10 loader threads inserting 200M documents each. The collection goes from 0 to 2B documents during the benchmark. The test was repeated for the B-tree and LSM file structures in WiredTiger and the mmapv1 engine. The test used 3 cache sizes - 144G, 32G and 8G. Buffered IO was used for 144G and direct IO was used for 32G and 8G. The server has fast flash storage, 144G RAM and 40 hyperthread cores. The test used MongoDB 2.8.0rc3. At test end the database was 310G for the b-tree, 290G for the LSM and 815G for mmapv1. The WiredTiger tests used snappy compression. All tests used the SAFE write concern with 1000 documents inserted per request.

The insert rate for the LSM is insensitive to cache size which is expected for this workload. The insert rate for the b-tree is sensitive to cache size and goes from being much better than the LSM when the secondary index data stays in cache to much worse than the LSM when it does not. Results for mmap1 explain why it is great that MongoDB 2.8 includes WiredTiger.

I prefer benchmark results that include enough detail to understand the results. The results below are from a test with 2.8.0rc2 and are metrics from vmstat and iostat normalized per 200k inserted documents. As each insert request was for 1000 documents then the results are also per 200 requests. They are best used to compare between the WiredTiger btree (wt btree), WiredTiger LSM (wt lsm) and mmapv1 engine. The columns are:

• vm.cs - context switches per 200 requests. Note the spike for the WiredTiger btree as the cache size is decreased. 
• vm.us+sy - user & system CPU time (sum of the us and sy columns from vmstat multiplied by 1M). The relative value is useful. Again there is a spike for the WiredTiger btree and none for the LSM but the LSM uses much more CPU when the cache isn't small.
• io.kBw - KB written per iostat per 200 requests. The WiredTiger LSM is always more efficient than the btree and the rate doesn't grow as the cache shrinks. The IO rate grows a lot for the WiredTiger btree as the cache shrink. All of that is expected. The WiredTiger btree has a much lower rate than mmapv1 for the same cache size.
• io.kBr - KB read per iostat per 200 requests. The WiredTiger btree rate grows fast as the cache shrinks while the LSM is rate does not grow as the cache is reduced from 32G to 8G. The rate for mmapv1 at 144G is much worse than for WiredTiger.
• io.r - read requests per iostat per 200 requests. The results match io.kBr.

per 200k inserts

vm.cs  vm.us+sy  io.kBw   io.kBr    io.r     engine, cache

  29     42445     417      0.49     0.02    wt btree, 144G

 306     67025     791    179.0     17.66    wt btree, 32G

1744    140560    2524   1889.0    215.0     wt btree, 8G

  76    152235     294      0.31     0.02    wt lsm, 144G

 134    148545     359     65.0      4.02    wt lsm, 32G

 137    146215     355     65.0      4.11    wt lsm, 8G 

 350     79500    2414      3.17     0.16    mmapv1, 144G 

B-tree

For a b-tree the IO pattern for the primary key index is efficient -- it is append only to grow the index to the right. If the user provides the value for the PK then the database must confirm that value is unique by reading from the database. That will not require extra reads from storage here because the database pages with that value should remain in cache.

The IO patterns for the secondary indexes are not efficient as inserted docs use random values for the secondary index attributes. Secondary index maintenance for a b-tree is a read-modify-write operation.  The page that has or will have the key must be fetched before it is written. For WiredTiger, and most other popular b-tree implementations, this is a random storage read eventually followed by a random storage write. When compression is used then a page must be decompressed as part of the read and compressed as part of the write.

InnoDB is able to avoid some of the random storage reads from secondary index maintenance via the change buffer. That avoids reads when the change buffer gets multiple changes to a secondary index page before doing the read and it can be very useful in production and on benchmarks.

A b-tree backed by a redo log defers writing back dirty pages until checkpoint or buffer pool pressure forces some pages to be written back. This reduces the IO overhead when one write to storage includes multiple changes to the page. This is less likely as the database:cache ratio increases. This occurs more frequently for workloads with skew (some keys are written much more frequently).

The size of the secondary indexes relative to cache are critical. While the database might be huge relative to cache (100X larger for example) if the secondary index fits in cache then all storage reads and many storage writes for the secondary index pages will be avoided and that makes a huge difference on the amount of IO for this workload.

For now I will ignore a b-tree that uses log-structured writes. That might reduce the cost for writing back pages but it will require storage reads and writes for garbage collection to copy-out live pages from previously written extents. It will also suffer from write-amplification for both the copy-out done during GC and from writing back a dirty page when only a fraction of the page has changed.

LSM

The popular compaction algorithms for an LSM are size-tiered and leveled. The WiredTiger LSM uses size-tiered. Without looking at the WiredTiger source, blind writes should be sufficient for secondary index maintenance because there are no unique constraints. A storage read is not required before the write.

A read before the write might be required for primary index maintenance to confirm that the PK for the inserted doc is unique. However the page with that data should almost always be in cache. An LSM can also use a bloom filter for workloads that don't load in PK order. It might be hard to define a bloom filter that is great for multiple indexes, in which case a column family is needed per index. I am not sure if WiredTiger used. I did not find references to bloom in the WiredTiger engine for MongoDB.

An LSM will do writes to storage during compaction and memtable flushes. It can be difficult to compare the overhead from these operations as they use larger IO requests (many pages at a time) compared to a b-tree. A big difference between an LSM and a b-tree is that most of the requests from the LSM are larger (many pages at a time) and amortized over many user operations. A busy LSM still does random IO:

• multi-threaded compaction means that each thread has one stream of writes and one or more stream of reads when the database is larger than cache. The request size for these is large (many pages at a time)
• another stream of writes is in progress for the redo log (WAL, write ahead log)
• user operations can require single-page reads from storage

Aurora for MySQL is coming

I am excited about Aurora for MySQL. While there aren't many details, I have two conclusions from the information that is available. First, many talented people did great work on this. Second, many customers want the features it provides and some of these features are otherwise unavailable unless you are web-scale and can afford a team of automation experts. This is a big deal and good for the MySQL community. I am not just writing this to boost my priority on the Aurora preview signup list.

I don't think it matters whether Aurora has better performance. The big story is much better availability and manageability without having to hire an expert MySQL operations team. The limiting factor might be cost but that depends on whether you come from the land where everything is free or from a commercial DBMS. And even in the land of free, the cost for an operations team is large.

Soapbox

Before describing what I learned about it I have a short editorial.

• Is Amazon the reason we need AGPL? It isn't clear to me that they improve upstream MySQL. They do benefit the community by making it easier to run MySQL in the cloud. But I don't see them in the community. Which means I also don't see their developers in the community and who wants to disappear while working on mostly open-source?
• Their marketing leads with up to 5X faster than MySQL which is translated by the tech press into 5X faster than MySQL. When I signed up for an Aurora preview the response email from Amazon also used the 5X faster than MySQL claim. I prefer up to 5X faster than MySQL.
• In the video from AWS re:Invent 2014 James Hamilton makes some interesting claims.
• I am not sure he heard about InnoDB based on this statement -- Just doing a state of the art storage engine would have been worth doing. Take Jim Gray's black book on transactions, implement it, I would be happy. Someone can tell him to be happy.
• In big print -- 3X write performance, 5X read performance. In small print -- sysbench. It will take time to determine whether similar performance & availability can be had elsewhere at a similar cost.
• In big print -- 400X less lag, 2 seconds vs 5 milliseconds. I doubt this. The slide also stated -- synchronous multi-DC replication. I don't see how that is possible except within a datacenter given network latency. Other content from Amazon claims this is async replication. But then Mr. Hamilton stated that everything is there, transactions are not lost were two data centers to instantly disappear. Again, that requires sync replication. This is confusing and I am surprised his claims don't match the other documentation from Amazon.
• It can fix torn pages. InnoDB does this for writes in progress during a crash. I assume Aurora can also do this for pages written a long time ago and that would be a big deal.

Information

What is Aurora? I don't know and we might never find out. I assume it is a completely new storage engine rather than a new IO layer under InnoDB. I encourage people to read the details page, FAQ, slides and pricing guide. The response from Clustrix is also useful. At least one answer on Quora is excellent and this overview of Amazon datacenters will convince you that they network needed to make this work with low latency.

The big deal is that the database is replicated 6X using 3 availability zones (AZs). I assume this means it uses 2 storage clusters per AZ. Documentation from Amazon states this is done using async replication and (committed) writes are available within a few milliseconds or 10s of milliseconds. I assume this is only for Aurora read replicas in the same AZ as the master. Network latency will make that impossible for some remote AZs.  In the presentation at AWS re:Invent there are claims that the replication is synchronous. That is confusing.

For now I will assume that Aurora does async replication to the two remote AZs and the non-primary storage cluster in the primary AZ -- which means that 5 of the 6 copies are updated via async replication. If this is true, then an outage at the primary storage cluster can mean that recent commits are lost. It would be great if someone were to make this clear. My preferred solution would be sync replication within the primary AZ to maintain copies in 2 storage clusters, and then async replication to the 4 copies in the 2 remote AZs. We will soon have multiple solutions in the MySQL community that can do sync replication within a datacenter and async replication elsewhere -- Galera, upstream and lossless semisync. But Aurora is much easier to deploy than the alternatives. My standard question is what commit rate can be sustained when all updates are to the same row? Sync replication with cross-country network round trips makes that slow.

The presentation also claimed this was mostly drop-in compatible. I am interested in compatibility with InnoDB. 

• What are the semantics for cursor isolation? PostgreSQL, InnoDB and Oracle all do snapshot isolation with different semantics for writes. PostgreSQL has excellent documentation to describe the behavior.
• Does Aurora support clustered indexes? 
• What is the max size of an index key?
• How are large columns supported? Is data always inline?
• How is multi-versioning implemented? InnoDB usually does updates in place so there isn't much work for purge to do except for deletes and updates to secondary index columns. 
• Does this use pessimistic or optimistic concurrency control?
• Does this support partitioning?
• What is the block size for reads?
• Does this use compression?

Features

The brief description of features is very interesting. I will summarize that here. The features sound great and I expect them to get a lot of customers.

• Unplanned failover to an Aurora read replica takes a few minutes. Unplanned failover to a new instance can take up to 15 minutes. This happens for customers who aren't spending money on Aurora read replicas. This is another feature that will make Aurora very popular. While it will be nice to make failover faster, the big deal is that they provide this. The usual MySQL deployment required some do-it-yourself effort to get something similar.
• Storage uses SSD. I assume this is based on EBS. They do background scrubbing to detect and correct corrupt pages. Storage grows in 10G increments. You don't have to provision for a fixed amount of GB or TB, they will grow as needed up to 64T (or 64T/table). I am not sure I would want 64T in one instance, but it can make it easy to archive data in place. Also automatic growth to such large database sizes will make it much easier for deployments to avoid sharding especially when an instance has many TB of cold data.
• There are interesting features for point-in-time recovery, incremental backup and snapshots. This is integrated with S3. Incremental backups make it affordable to archive data in place as you don't do full backups for data that doesn't change. But I don't understand all of their backup options. 
• Database is replicated 2 times within 3 AZs so there are 6 copies. Up to 2 copies can be lost and writes are still possible. Up to 3 copies can be lost and reads are still possible. I assume that by copies can be lost they mean storage clusters can be lost. Automatic recovery here is another big deal.
• The buffer pool survives mysqld process restart. I wonder if that is only true for planned restart. Regardless, this is a very useful feature for IO-bound workloads when it is important to have a warm cache. Percona used to have a patch for this with InnoDB.
• Replication is supported in two ways -- via Aurora (at storage level) and MySQL (binlog). My bet is that Aurora replication will be most popular but some people will use MySQL replication to replicas with locally attached storage to save money. They claim much less lag with Aurora and I agree there will be less but I am not sure there will be 400X less. However, they avoid some overhead on commit by not using the binlog and they avoid a lot of complexity by not requiring MySQL replication on the master or slave. I assume that Aurora replication ships deltas rather than page images to be efficient on the network.

Cost

Cost comparisons will be interesting. I am very uncertain about my estimates here as I don't have much experience with prices on AWS or for normal sized datacenter deployments limited to a few servers. I estimate a 3 year cost of $568,814 for a high-end Aurora deployment: largest servers, 1 master, 1 replica, backup and storage. It will be interesting to compare this to non-AWS hardware because you need to account for features that Aurora provides and for the extra availability of the Aurora storage solution. I used 3TB and 10k IOPs from storage because that can be provided easily by a high-end PCIe flash device, but that also assumes a very busy server.

• Reserved db.r3.8xlarge (32 vCPUs, 244GiB RAM) for 2 servers is $115,682 over 3 years
• 50TB of backup at 10 cents/GB/month is $53,100 over 3 years
• 3TB of storage and 10,000 IOPs per server is $400,032 for 2 servers over 3 years. But I am not sure if the storage cost includes IO done to all AZs to maintain the 6 database copies. In that case, the storage cost might be higher.

Updates with secondary index maintenance: 5.7 vs previous releases

My previous results for an update-only workload to a cached and IO-bound database used a table that did not require secondary index maintenance from the update. Prior to that I ran tests using mysqlslap and might have found a regression in InnoDB when the update requires secondary index maintenance (bug 74235). The mysqlslap test did all updates to the same row. The test I describe here chooses the row to update at random and this workload does not reproduce the worst case from bug 74235.

The results are that newer releases tend to do worse at 1 thread, 5.7.5 does best at 32 threads and 5.7.5 does slightly worse than 5.6.21 at less than 32 threads.  We need to fix the regressions in 5.7.5 at less-than-high concurrency workloads.

setup

The test here was the same as described in my previous posts with one exception -- the test tables have a secondary index on (k,c) and note that the column c is set to 0 at test start. The updates are of the form: UPDATE $table SET c = c + 1 WHERE id = $X. The test tables look like:

CREATE TABLE sbtest1 (

  id int(10) unsigned NOT NULL auto_increment,

  k int(10) unsigned NOT NULL default '0',

  c char(120) NOT NULL default '',

  pad char(60) NOT NULL default '',

  PRIMARY KEY  (id),

  KEY k (k,c)

) ENGINE=InnoDB AUTO_INCREMENT=8000001 DEFAULT CHARSET=latin1

There were 8 tables in the database with 8M rows per table. Tests were run for 1 thread with 1 table and 8, 16, 32 threads with 8 tables. The test at each concurrency level was run for 10 minutes. Tests were run for a 1G (IO-bound) and 32G (cached) InnoDB buffer pool. For the cached test the tables were read into the buffer pool at test start. The updates/second rate from these tests had more variance than previous tests. I ran tests many times and report rates here that were typical. For the 1-thread tests that use 1 table, the database (1 table) is 2G. For the 8, 16 and 32 thread tests that use 8 tables the database (8 tables) is 16G.

results at 1 thread

The rates for a cached database and 1 thread match most previous results -- newer releases are much slower than older releases and MySQL 5.7.5 is about 1.5X worse than MySQL 5.0.85. The results for the IO-bound setup don't follow that trend. Newer releases do better. I regret that I did not have the time to explain the difference between 5.6.21 and 5.7.5.

results at many threads

MySQL 5.7.5 and 5.6.21 are much better than older releases. 5.6.21 does better than 5.7.5 at 8 and 16 threads while 5.7.5 is the best at 32 threads.

configuration

Sysbench IO-bound updates: MySQL 5.7 vs previous releases

I repeated the sysbench update-only test after reducing the InnoDB buffer pool to 1G. The test database is 16G so the test should be IO-bound. MySQL 5.7.5 is 10% worse than 5.0.85 at 1 thread and much better at 8+ threads.

The previous blog post has more details on the setup. The configuration is not crash safe as the doublewrite buffer and binlog are disabled and innodb_flush_log_at_trx_commit=2. This test continued to use direct IO for InnoDB but this test requires a high rate of reads from storage. The previous test did many writes but no reads. A fast flash device is used for storage.

results at 1-thread

Only 1 table is used so there is a 2G database and 1G buffer pool. This test is less IO-bound than the many threads tests reported in the next section. Two graphs are displayed below. The first has the updates/second rates and the second has the rates normalized to the rate from 5.0.85. MySQL 5.7.5 is about 10% worse than 5.0.85 and all servers do between 3000 and 4000 updates/second.

results at many threads

These tests uniformly distribute clients across the 8 tables. MySQL 5.7.5 is the best at 16 and 32 threads. MySQL 5.0.85 and 5.1.63 saturate at 8 threads.

Sysbench cached updates: MySQL 5.7 vs previous releases

I used MySQL 5.7.5 to repeat tests I did for MySQL 5.7.2 and 5.6.10 with the sysbench client and a cached database to compare performance for low and high concurrency. My configuration was optimized for throughput rather than crash safety. The performance summary:

• MySQL 5.7.5 is 1.47X slower than 5.0.85 at 1 thread
• MySQL 5.7.5 is a bit slower than 5.6.21 at 8 and 16 threads
• MySQL 5.7.5 is faster than others at 32 threads

setup

I used my sysbench 0.4 fork and set the value of the c column to 0 in the sbtest tables as that column is incremented during the test. The test used 8 tables with 8M rows per table (about 16G total) and the InnoDB buffer pool was 32G. One table was used for the 1 thread case. For the other tests the clients were uniformly distributed across the 8 tables. All data was in the buffer pool at test start. The test was run for 10 minutes at each concurrency level (1, 8, 16, 32 threads). Throughput appeared to be stable throughout the test.

Tests were run for MySQL versions 5.0.85, 5.1.63, 5.6.21 and 5.7.5. Tests were repeated for 4k and 8k innodb_page_size with MySQL 5.7.5 (see -16k, -8k and -4k below). Tests were also repeated with changes to innodb_flush_neighbors (=0), innodb_write_io_threads (=8), innodb_page_cleaners (=4) and innodb_purge_threads (=4). Results for those changes are not provided here as there wasn't an impact on QPS. The change to flush_neighbors reduced the InnoDB page write rate, which is a good thing.

results

The regression from 5.0.85 to recent versions is visible in the single-thread results below.

MySQL 5.7.5 is the champ at 32 threads but is slightly worse than 5.6.21 at 8 and 16 threads. Recent versions are much better than old versions.

configuration

I used this my.cnf for MySQL 5.7.5 and then something similar for other versions:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

table-definition-cache=1000

table-open-cache=2000

table-open-cache-instances=8

max_connections=20000

key_buffer_size=200M

metadata_locks_hash_instances=256 

query_cache_size=0

query_cache_type=0

server_id=9

performance_schema=0

binlog_format=row

skip_log_bin

innodb_buffer_pool_instances=8

innodb_io_capacity=32000

innodb_lru_scan_depth=4000

innodb_checksum_algorithm=CRC32

innodb_thread_concurrency=0

innodb_buffer_pool_size=32G

innodb_log_file_size=1900M

innodb_flush_log_at_trx_commit=2

innodb_doublewrite=0

innodb_flush_method=O_DIRECT

innodb_thread_concurrency=0

innodb_max_dirty_pages_pct=80

innodb_file_format=barracuda

innodb_file_per_table

This was the command line for one of the tests:

./sysbench --batch --batch-delay=10 --test=oltp --mysql-host=127.0.0.1 --mysql-db=test --oltp-table-size=8000000 --max-time=600 --max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --oltp-table-name=sbtest1 --oltp-skip-trx --oltp-test-mode=incupdate --oltp-dist-type=uniform --oltp-range-size=10 --oltp-connect-delay=0 --percentile=99 --num-threads=1 --seed-rng=1413171854 run