Reducing the overhead of secondary index maintenance with write-optimized database engine

There was a question about the performance of RocksDB for large objects during a recent RocksDB AMA. I used the insert benchmark to get a better answer to that question and understand the impact of secondary index maintenance on the insert rate. I repeated the benchmark for documents with a padding field of size 256 bytes, 10kb and 1mb. The padding field compresses by 50%.

The insert benchmark workload uses one or more threads to insert documents. There are optionally 1, 2 or 3 secondary indexes and the indexes must be maintained by inserting new index entries. For a b-tree the disk page with a leaf node from each secondary index must be read before it can be written (call this read-before-write). I assume that non-leaf nodes of the index are in the database or OS cache. When the database is large then leaf nodes might not be cached and a disk read is done as part of read-before-write.

Non-unique secondary indexes with a write-optimized database don't, or shouldn't, require read-before-write. With RocksDB a Put is done to the memstore to add the new index entry. Were this an update there might be a delete of the old key, which is implemented as a Put of a tombstone into the memstore, and again no read is required. Were this a unique secondary index then the database must do a read to confirm that the unique constraint is supported.

The key conclusions are:

• A write-optimized database can make secondary index maintenance much faster whether using slow (disk) or fast (PCIe flash) storage. Results for disk are in progress. Today I share results for PCIe flash.
• There isn't a penalty for RocksDB with large documents. While RocksDB and TokuMX were faster than WiredTiger and mmapv1 for small documents, they were not faster for 10kb and 1mb documents. There are two reasons for this. First, a b-tree might not suffer from higher write-amplification for large documents when the document is a multiple of the disk page size. Second, the secondary index data was small enough to remain in cache during the tests with 10kb and 1mb documents. While read-before-write was still done, the reads were done from cache.

Results

This is the performance data for all tests. The test server has PCIe flash, 144G of RAM and 40 hyperthread cores. The mongod process was configured to use a 32G block cache for RocksDB, WiredTiger and TokuMX. For RocksDB, WiredTiger and mmapv1 I used MongoDB 3.0.X from a special branch. For TokuMX I used 2.0.1 which is based on MongoDB 2.4. The oplog was configured for all servers. The oplog was 2G for RocksDB, WiredTiger and mmapv1. Oplog retention is time-based for TokuMX and set to 2 hours. More configuration options are listed at the end of this post. Snappy compression was used for RocksDB and WiredTiger while QuickLZ was used for TokuMX.

The test with a 256 byte pad column inserted 2B documents. The test with a 10kb pad field inserted 100M documents. The test with a 1MB pad field inserted 1M documents. All tests used 10 client threads.

This shows the insert rate by number of secondary indexes for 2B documents with a 256 byte pad field. WiredTiger did great with 0 secondary indexes but it and mmapv1 suffer once reads from storage are done during secondary index maintenance. TokuMX and RocksDB lose much less performance with more indexes. The insert rate at 3 secondary indexes is 77% & 67% of the rate at 0 secondary indexes for TokuMX and RocksDB. The rate at 3 indexes is 8% and 7% of the rate at 0 indexes for mmapv1 and WiredTiger. The iostat section at the end of the blog has more details on the performance overhead of secondary index maintenance. With 3 secondary indexes and WiredTiger or mmapv1 the number of disk reads per insert is more than 100X the rate for TokuMX and RocksDB.

I repeated the test for RocksDB and TokuMX with the oplog disabled. The insert rate increased from 45651 to 49322 for TokuMX and from 29913 to 39953 for RocksDB. I suspect that TokuMX loses less performance from the oplog because they partitioned it.

This is the database size for the tests with 2B documents and a 256 byte pad field. RocksDB uses the least disk space and mmapv1 uses the most.

This is the result for 100M documents with a 10kb pad field. While TokuDB and RocksDB lose the least performance as indexes are added, the results for all engines are closer than for the 256 byte documents. The secondary index data remains in cache for this test so WiredTiger and mmapv1 suffer less from read-before-write.

This is the result for 1M documents with a 1mb pad field. Results were close for all engines.

Perfomance Data

The collection used in this test always has a primary key index and tests were done with 0, 1, 2 and 3 secondary indexes. For each test there are 4 numbers below: ipsAvg-ipsP95-dbSize-rssSize. For some database sizes I don't have resolution for all digits (see 13XX below). The four values are:

• ipsAvg - average rate of inserts/second during the test
• ipsP95 - 95th percentile rate for inserts/second from 10-second samples
• dbSize - database size in GB at test end
• rssSize - mongod process size in GB at test end

2B documents with 256 byte pad field

        PK-only                 PK, 1 secondary         PK, 2 secondaries       PK, 3 secondaries

tokumx  59499-56576-433g-25g    54530-50836-483g-34g    49092-44445-529g-34g    45641-40115-608g-34g

rocks   44136-33342-363g-42g    37587-25271-394g-45g    33330-23267-435g-50g    29913-21191-478g-53g

wt      100484-85026-383g-6.5g  24448-59.0-461g-39g     11776-68.9-534g-36g     6604-997-607g-36g

mmap    30063-27652-1023g-130g  13684-11689-11XXg-133g  5709-2617-13XXg-79g     2393-1188-14XXg-72g

100M documents with 10kb pad field

        PK-only                 PK, 1 secondary         PK, 2 secondaries       PK, 3 secondaries

tokumx  6897-4509-986g-33g      6960-5338-14XXg-35g     7053-5536-14XXg-35g     7043-5488-14XXg-35g

rocks   8489-6933-515g-4.3g     7976-73-517g-5.3g       7813-6184-518g-5.4g     7475-5786-521g-5.6g

wt      9087-7444-640g-5.2g     5159-1137-654g-18g      5619-714-651g-27g       4204-546-663g-34g

mmap    7434-4307-16XXg-106g    6039-3466-16XXg-98g     5046-2853-16XXg-111g    4299-2711-16XXg-111g

1M documents with 1mb pad field

        PK-only                 PK, 1 secondary         PK, 2 secondaries       PK, 3 secondaries

tokumx  100.1-70.1-864g-34g     100.9-72.8-1022g-33g    100.5-69.8-1019g-33g    99.7-67.9-1019g-33g

rocks   100.0-72.2-514g-34g     94.1-68.0-517g-34g      95.8-69.9-517g-34g      94.9-69.1-518g-34g

wt      106.7-101.3-518g-2.7g   107.1-101.7-518g-4.2g   107.7-101.5-519g-2.9g   108.0-101.7-520g-3.0g

mmap    79.2-32.2-20xxg-117g    93.9-49.2-20XXgb-117g   95.2-57.0-20XXg-118g    95.2-57.7-20XXg-117g

iostat

I examined iostat data to explain the differences in insert rates for the test with 2B documents and a 256 byte pad field. There are four lines for each engine with data for 0, 1, 2 and 3 secondary indexes. Note that with 3 secondary indexes the b-tree engines (WiredTiger, mmap) are writing more to storage per insert and reading a lot more per insert.  The output is:

• r/s - average rate for r/s (storage reads/second)
• rmb/s - average rate for read MB/second 
• wmb/s - average rate for write MB/second
• r/i - storage reads per insert
• rkb/i - KB of storage reads per insert
• wkb/i - KB of storage writes per insert 
• #indexes - number of secondary indexes

TokuMX

r/s     rmb/s   wmb/s   r/i             rkb/i   wkb/i   #indexes

13.5    0.3     71.6    0.000226        0.005   1.233   0

116.4   5.0     79.6    0.002135        0.093   1.495   1

182.4   9.7     86.9    0.003715        0.202   1.812   2

320.9   16.4    98.0    0.007031        0.367   2.199   3

RocksDB

r/s     rmb/s   wmb/s   r/i             rkb/i   wkb/i   #indexes

11.1    0.3     228.9   0.000251        0.008   5.310   0

18.9    0.4     230.4   0.000503        0.010   6.276   1

114.7   3.9     234.3   0.003441        0.119   7.198   2

185.0   8.3     240.1   0.006186        0.284   8.218   3

WiredTiger

r/s     rmb/s   wmb/s   r/i             rkb/i   wkb/i   #indexes

3.8     0.0     128.3   0.000038        0.000   1.308   0

37.2    0.2     129.2   0.001522        0.009   5.413   1

2681.9  24.4    172.9   0.227747        2.123   15.033  2

5913.2  58.2    174.5   0.895392        9.021   27.053  3

mmapv1

r/s     rmb/s   wmb/s   r/i             rkb/i   wkb/i   #indexes

7.5     0.1     41.3    0.000250        0.003   1.407   0

28.7    0.2     126.6   0.002101        0.013   9.477   1

5534.8  70.7    94.7    0.969479        12.679  16.987  2

11116.2 160.2   57.5    4.645290        68.554  24.607  3

Using TokuMX as the base case, the normalized r/i rates are listed below. When using 3 secondary indexes,  mmapv1 does 660X more disk reads per insert and WiredTiger does 127X more disk reads per insert compared to TokuMX. That can be a problem even with fast storage.

	0 indexes	1 index	2 indexes	3 indexes
tokumx	1.000	1.000	1.000	1.000
rocksdb	1.111	0.236	0.926	0.880
wiredtiger	0.168	0.713	61.305	127.349
mmap	1.106	0.984	260.963	660.687

Configuration

This is the configuration file for RocksDB, WiredTiger and mmapv1:

processManagement:

  fork: true

systemLog:

  destination: file

  path: /mongo.30/log

  logAppend: true

storage:

  syncPeriodSecs: 60

  dbPath: /mongo.30/data

  journal:

    enabled: true

  mmapv1:

    journal:

      commitIntervalMs: 100

operationProfiling.slowOpThresholdMs: 2000

replication.oplogSizeMB: 2000

storage.wiredTiger.engineConfig.cacheSizeGB: 32

storage.wiredTiger.collectionConfig.blockCompressor: snappy

storage.wiredTiger.engineConfig.journalCompressor: none

storage.rocksdb.cacheSizeGB: 32

storage.rocksdb.compression: snappy

And this is the configuration file for TokuMX

dbpath = /tokumx201/data

logpath = /tokumx201/log

logappend = true

fork = true

slowms = 2000

oplogSize = 2000

expireOplogHours = 2

It was then started using this shell script with "32G" as the argument:

numactl --interleave=all \

bin/mongod \

    --config $PWD/mongo.conf \

    --setParameter="defaultCompression=quicklz" \

    --setParameter="defaultFanout=128" \

    --setParameter="defaultReadPageSize=16384" \

    --setParameter="fastUpdates=true" \

    --cacheSize=$1 \

    --replSet foobar \

    --checkpointPeriod=900

sleep 5

echo "rs.initiate()" | bin/mongo

echo "show collections" | bin/mongo local

Write-heavy workload vs the MongoDB optimizer

We experienced wasted disk IO from the MySQL query optimizer a few years back and fixed it via FORCE INDEX hints. I think I am experiencing wasted disk IO from the MongoDB optimizer during recent tests with the insert benchmark.

The problem with MySQL requires a query for which more than one index can be used. The problem query was a single table SELECT statement. The optimizer must figure out the best index and calls the records_in_range method to determine the selectivity of the query predicates for each relevant index. For InnoDB tables the records_in_range method reads the b-tree leaf node that contains the first and last index entries that satisfy the predicates and then estimates the amount of data between them. Assuming all non-leaf nodes are cached, then this does 2 disk reads in the worst case. This disk IO isn't wasted when it is done for the index to be used by the query. It is wasted when done for other indexes. By using FORCE INDEX we limited the optimizer to only considering the correct index and stopped wasting disk reads for a frequent query.

The MongoDB query planner is different than the DBMS optimizers I have known (Informix, Oracle, MySQL).

1. To find a new plan it runs plans from candidate indexes in parallel and then chooses the plan that finishes first. I assume but have not confirmed that in parallel means that multiple cursors are open but all processing is done in one thread. Dynamic query optimization was used in the SQL optimizer for RDB and I suspect has been used elsewhere.
2. Plans (per collection) are invalidated after a collection gets 1000 write operations. You can see how this is used by searching the source code for internalQueryCacheWriteOpsBetweenFlush. The method PlanCache::notifyOfWriteOp is called per document, not per operation. Plans are likely to be invalidated much too soon because a busy application can quickly perform 1000 write operations on a collection.
3. This will change for the better in the next release (3.2) thanks to server-17895 and server-15225 but my tests are based on 3.0 and I didn't notice the pending change until this weekend.
4. The value 1000 can be changed at runtime by this command:

db.runCommand({setParameter:1, internalQueryCacheWriteOpsBetweenFlush:100000})

I am running the insert benchmark for MongoDB using RocksDB, mmapv1, WiredTiger and TokuMX on a server with 15 SATA disks and SW RAID 0. The test is run for four configurations: 10 insert threads and 1 query thread, 10 insert threads and 0 query threads, 1 insert thread and 10 query threads, 0 insert threads and 10 query threads.

I will focus on two configurations: 0 insert threads and 10 query threads, 1 insert thread and 10 query threads. The database is 127GB with MongoRocks and the server has 64GB of RAM. The QPS from 10 insert threads was ~93 with 1 insert thread and ~7685 with 0 insert threads. That is a huge difference.

I then ran a shorter test with 1 insert thread and got 317 QPS from the 10 query threads. Finally I changed internalQueryCacheWriteOpsBetweenFlush from 1,000 to 1,000,000 and repeated the shorter test with 1 insert thread and got 3248 QPS from the 10 query threads. Alas, I have confirmed there is a big impact from too-frequent plan invalidation.

Linkbench, MongoDB and a disk array, now with TokuMX

I repeated the linkbench tests described in a previous post for TokuMX 2.0.1. This uses MongoDB, LinkbenchX and different storage engines. The test server has 144 GB of RAM, 6 SAS disks with HW RAID 0 and 24 hyperthread cores. The benchmark was run with 12 concurrent clients.

Cached database

The first test is with a cached database. The test pattern is to load the database and then do 12 1-hour query tests in a loop. The database always fit in RAM. At the end of the 12th hour the database size was 40 GB for WiredTiger, 22 GB for RocksDB, 30 GB for TokuMX and 78 GB for mmapv1. I used Snappy compression for WiredTiger/RocksDB and QuickLZ for TokuMX.

The graph below has the average QPS per 1-hour interval.

This is the data for the graph:

wiredtiger,18279,17715,16740,16585,16472,15924,15703,15632,15783,15401,15872,15654

rocksdb,10892,9649,9580,9639,9860,9981,9316,9535,9578,9682,9437,9689

tokumx,11078,6881,5832,5132,5864,5434,5495,5340,5168,5505,4763,4924

mmapv1,5066,4918,4821,4758,4629,4666,4589,4613,4663,4626,4563,4642

I then looked at the output from the 12th 1-hour run to understand why QPS was much better for WiredTiger. The table below has the average response time in milliseconds for the 3 most frequent operations. WiredTiger has the best times, mmapv1 has the worst times for writes (per database or per collection writes are single threaded) and TokuMX has the worst time for get_links_list. The get_links_list operation requires a short range query.

                add_link        update_link     get_links_list

wiredtiger      1.361           1.422           0.768

rocksdb         1.702           1.789           1.460

tokumx          1.538           1.674           3.929

mmapv1          4.788           5.230           2.657

Database larger than RAM

The test was repeated with a database that does not fit in RAM. The test was not run for mmapv1 because I didn't have enough disk space or patience to wait for the load to finish. At the end of the 12th hour the database size was 728 GB for WiredTiger, 632 GB for RocksDB and 588 GB for TokuMX. It is interesting that the TokuMX database was smaller than RocksDB here but larger than RocksDB for the cached test.

The graph below has the average QPS per 1-hour interval.

This is the data for the graph:

tokumx,439,580,622,625,638,617,598,613,631,609,610,611

rocksdb,387,448,479,468,468,477,471,483,475,473,471,477

wiredtiger,297,343,345,333,333,331,320,335,326,339,324,333

I then looked at the output from the 12th 1-hour run to understand why QPS was much better for TokuMX. The table below has the average response time in milliseconds for the 3 most frequent operations. TokuMX has is faster on get_links_list while RocksDB is faster on add/update link and the get_links_list operation is done about 5 times per add/update. WiredTiger is the slowest on all of the operations.

                add_link        update_link     get_links_list

tokumx          23.499          25.903          22.987

rocksdb         21.704          23.883          25.835

wiredtiger      47.557          51.122          35.648

TokuMX is the most IO efficient for this workload based on the data below. That explains why it sustains the highest QPS because disk IO is the bottleneck. I used data from iostat (r/s, w/s, rKB/s and wKB/s) and divided those rates by the average QPS with all data taken from the 12th 1-hour run. I assume that disk reads done by queries dominate reads done from compaction. TokuMX does less IO per query than RocksDB and WiredTiger. Both TokuMX and RocksDB write much less data per query than WiredTiger.

                read/query      read-KB/query   write-KB/query

tokumx          1.612           14.588          2.495

rocksdb         2.135           20.234          2.512

wiredtiger      2.087           26.675          12.110

Configuration

This has a few more details on the MongoDB configuration I used. The oplog was enabled for all engines. This is the configuration file and startup script for TokuMX. The block cache was ~70G for all engines.

dbpath = /home/mongo/data

logpath = /home/mongo/log

logappend = true

fork = true

slowms = 2000

oplogSize = 2000

expireOplogHours = 2

numactl --interleave=all \

bin/mongod \

    --config $PWD/mongo.conf \

    --setParameter="defaultCompression=quicklz" \

    --setParameter="defaultFanout=128" \

    --setParameter="defaultReadPageSize=16384" \

    --setParameter="fastUpdates=true" \

    --cacheSize=$1 \

    --replSet foobar \

    --checkpointPeriod=900

And this is the configuration file for other engines:

processManagement:

  fork: true

systemLog:

  destination: file

  path: /home/mongo/log

  logAppend: true

storage:

  syncPeriodSecs: 60

  dbPath: /home/mongo/data

  journal:

    enabled: true

  mmapv1:

    journal:

      commitIntervalMs: 100

operationProfiling.slowOpThresholdMs: 2000

replication.oplogSizeMB: 2000

storage.wiredTiger.collectionConfig.blockCompressor: snappy

storage.wiredTiger.engineConfig.journalCompressor: none

storage.rocksdb.compression: snappy

storage.rocksdb.configString: "write_buffer_size=16m;max_write_buffer_number=4;max_background_compactions=6;max_background_flushes=3;target_file_size_base=16m;soft_rate_limit=2.9;hard_rate_limit=3;max_bytes_for_level_base=128m;stats_dump_period_sec=60;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=12;level0_stop_writes_trigger=20;max_grandparent_overlap_factor=8;max_bytes_for_level_multiplier=8"

Performance impact from too-small RAID stripes

What is the impact from the RAID stripe size? That depends on the workload. Many years ago there was an excellent paper from Oracle with the title Optimal Storage Configuration Made Easy. It is still worth reading today. One of the points in the paper is that you don't want individual storage read requests to use more than one disk for workloads with high concurrency to reduce the number of disk seeks.

RocksDB is used for OLTP and supports workloads with high concurrency from user requests and from compaction done in the background. There might be 8 threads doing compaction on a busy server and a typical compaction step done by a thread is to read from ~11 SST files and then write ~11 SST files. While each SST file is read sequentially, all of the files are read at the same time. The SST files are written in sequence. So we have ~12 streams of IO per compaction thread (11 for reads, 1 for write) and with 8 compaction threads then we can have ~96 streams of concurrent IO. The reads are done a block at a time (for example 16kb) and depend on file system readahead to get larger requests and reduce seeks. The write requests are likely to be large because the files are multi-MB and fsync is called when all of the data to the file has been written or optionally when sync_file_range is called after every N MB of writes.

Even if we limit our focus to writes there is a lot of concurrency as there is likely 1 write stream in progress for each compaction thread and another for the WAL. The reads done by compaction are interesting because many of them will get data from the OS filesystem cache. When level style compaction is used, the small levels near the top of the LSM are likely to be in cache while the largest level is not.

Reads vs stripe size

Enough about RocksDB, this post is supposed to be about the performance impact from RAID stripe sizes. I ran a simple IO performance test on two servers that were identical except for the RAID stripe size. Both had 15 disks with HW RAID 0, one used a 256KB stripe and the other a 1MB stripe. While the disk array is ~55TB I ran tests limited to the first 10TB and used the raw device. Domas provided the test client and it was run for 1 to 128 threads doing 1MB random reads.

The array with a 1MB stripe gets more throughput when there are at least 2 threads doing reads. At high concurrency the array with a 1MB stripe gets almost 2X more throughput.

Read MB/second by concurrency
1       2       4       8       12      16      24      32      64      128     threads
67      125     223     393     528     642     795     892     891     908     1MB stripe
74      115     199     285     332     373     407     435     499     498     256KB stripe

Summary of Linkbench for MongoDB & MySQL

I published many benchmark reports last week for Linkbench using MongoDB and MySQL with several storage engines: RocksDB, InnoDB, WiredTiger and mmapv1. My goal from running tests like this is to find things we can make better. From these tests the thing to make better is load performance for MongoDB. MySQL did better on the load and query tests but the difference on the load tests was much larger. I suspect that much of the problem can be blamed on the need for 9 indexes in MongoDB versus 4 indexes in MySQL to support the Linkbench workload as more indexes means there is more index maintenance during the load. If we get support for clustered primary key indexes into a future MongoDB release then we can reduce this from 9 to 6 indexes. The extra two indexes might be here forever because there is no composite PK index in MongoDB. The results from last week are:

• Linkbench for a cached database
• Linkbench on a small server
• Linkbench for an uncached database
• Linkbench with a disk array

MySQL schema

From the MySQL Setup section in the Linkbench README file there are 4 indexes created across 3 tables:

• linktable has a primary key index on (link_type, id1, id2) and a secondary index on (id1, link_type, visibility, time, id2, version, data). The secondary index is covering for the most frequent query. While it almost doubles the size of linktable it also greatly reduces the worst-case random IO required for that query from a few thousand to a few. This can be a good tradeoff even when using PCIe flash.
• counttable has a primary key index on (id, link_type)
• nodetable has a primary key index on (id)

Why 5 extra indexes?

Why does MongoDB need 5 more indexes than MySQL? From the MongoDB driver in LinkbenchX there are 3 indexes created by the user and then 3 implicit primary key indexes on _id:

• the link collection has a primary key index on _id, a unique secondary index on (link_type, id1, id2) and a non-unique secondary index on (id1, link_type, visibility, time, id2, version, data)
• the count collection has a primary key index on _id and a unique secondary index on (id, link_type)
• the node collection has a primary key index on _id

I have described 6 indexes but claimed that 9 were used. The other 3 are used for WiredTiger and RocksDB but not for mmapv1 and are indexes on DiskLoc (key is DiskLoc, value is the document). for each collection. While WiredTiger and RocksDB support a clustered primary key that is not supported (yet) in MongoDB because the storage engine API is still evolving from what was needed for mmapv1. With mmapv1 the base table is heap organized and the value in a primary key index is a DiskLoc which I assume is a filename and offset where the document resides. WiredTiger and RocksDB are copy-on-write and the location for a document will change one or more times after an update so they can't use DiskLoc to point to the one place on disk where the document exists, thus they need the extra index. Server-14569 and server-15354 are open for this. 

If we get proper support for a clustered primary key index, then MongoDB would use 6 indexes for Linkbench versus 4 for MySQL. How do we reduce that from 6 to 4? The unique secondary indexes for the link and count collections are not needed in MySQL. The problem here is that MongoDB doesn't support a composite PK. One workaround is to concatenate the fields (link_type | id1 | id2 -> _id for the link collection, (id | link_type -> _id) for the count collection. I don't recommend that because it must be done by the client, and done correctly, on every insert and update. It is even more work to make the concatenated value order preserving, which is easy in code, but makes it harder to construct the _id value when manually querying the database. This also means that some fields will be stored multiple times: once in the _id field, once separately. 

Linkbench, MongoDB and a disk array

This has results for Linkbench with MongoDB and a server with a disk array. The Linkbench configurations are similar to the tests done for cached and uncached databases, but the server here uses a disk array with 6 10K RPM SAS disks and HW RAID 0. The previous tests used PCIe flash. The server with a disk array has 24 hyperthread cores and is at least one generation older than the PCIe flash server which has 40 hyperthread cores. Both have 144G RAM.

Results for cached database

The test client is LinkbenchX and the configuration is described in a previous post. The test used 10 threads for loading and 20 for queries. After loading there were 12 1-hour runs of the query test and results are reported for the 2nd and 12th hour.

The results below are for the test with maxid1=20M set in FBWorkload.properties for both servers (disk array, PCIe flash from a previous post). The load rate is similar between the servers with a disk array and PCIe flash. The query rate is better for the server with PCIe flash, but that might be due more to more cores and newer CPUs than to storage performance. The load and query rates are better for WiredTiger than for RocksDB on both servers.

The server names are explained in a previous post. The oplog was enabled for all tests.

--- results for disk array

load    load    load    2h      2h      12h     12h     24h     24h

time    rate    size    qps     size    qps     size    qps     size    server

5333    16519   14g     9649    17g     9689    22g                     mongo.rocks.log

3015    29223   16g     17715   30g     15654   40g                     mongo.wt.log

35253   2499    65g     4918    68g     4642    78g                     mongo.mmap.log

--- results for PCIe flash

5015    17565   14g     14442   17g     13925   24g     13506   29g     mongo.rocks.log

3601    28020   16g     25488   34g     22992   45g                     mongo.wt.log

Results for uncached database

The results below are for the test with maxid1=1B for both servers (disk array, PCIe flash from a previous post). The load rate is similar between disk and flash and is also similar to the rates above for the cached database. Multiple secondary indexes are maintained during the load but IO latency does not have a significant impact on the load rate, even for WiredTiger which does more random IO than RocksDB.

The query rates are significantly lower for the disk array than for PCIe flash. IO latency is significant for queries. However, RocksDB does better than WiredTiger on the disk array possibly because it uses less random IO for writes which leaves more random IO capacity to serve reads.

Update - I repeated tests for the disk-array server with different configurations and the results are better. For WiredTiger I set storage.syncPeriodSecs=600 which changes the default checkpoint interval from 60 to 600 seconds. The benefit should be fewer disk writes and QPS improved by more than 30% with that change. For RocksDB I used the default configuration and QPS improved by more than 20% compared to the non-default configuration I had been using (Igor did a good job choosing the defaults). For all engines I used a smaller block cache -- 32G rather than 70G -- to save more space for compressed blocks in the OS filesystem cache. Results for all engines improved with a smaller block cache.

--- results for disk array

load    load    load    2h      2h      12h     12h     24h     24h     48h     48h

time    rate    size    qps     size    qps     size    qps     size    qps     size    server

298901  14625   606g    580     585g    611     588g    604     590g    600     596g    mongo.tokumx.log, 70G
297159  14711   597g    731     585g    786     588g    782     592g    736     598g    mongo.tokumx.log, 32G

178923  24432   694g    343     704g    333     728g                                    mongo.wt.log, default, 70G

176432  24777   696g    449     709g    434     738g    423     749g    418     757g    mongo.wt.log, non-default, 32G

271569  16097   631g    448     631g    477     632g    452     633g    471     635g    mongo.rocks.log, non-default, 70G

274780  15909   628g    458     628g    592     629g    574     631g    569     633g    mongo.rocks.log, default, 32G

--- results for PCIe flash

251688  17368   630g    9670    633g    6762    644g    6768    656g    mongo.rocks.log

175740  24874   695g    8533    766g    8019    791g    7718    806g    mongo.wt.log

This has the mean response time in milliseconds for each of the query types in Linkbench. The data below is from the 12th 1-hour run for the disk array (the first pair of numbers) and then PCIe flash (the second pair of numbers. The most frequent operation is GET_LINKS_LIST followed by MULTIGET_LINK. On the disk array because the response time for these two operations is better for RocksDB and that explains why it gets more QPS than WiredTiger. For PCIe flash the response time for GET_LINKS_LIST is lower for WiredTiger which explains the better QPS. The response time for all of the write operations is better for RocksDB than WiredTiger on disk and flash, but those operations are less frequent. WiredTiger does more reads from disk & flash during writes as b-tree leaf pages must be read before being written.

The QPS for RocksDB is higher than WiredTiger for the 2nd 1-hour run with the PCIe server but lower after the 12th 1-hour run. The mean response time for the GET_LINKS_LIST operation almost doubles and the cause might be the range read penalty from an LSM.

                12th 1-hour run         2nd 1-hour run  12th 1-hour run

                disk    disk    -       flash   flash   flash   flash   

                wired   rocks   -       wired   rocks   wired   rocks   

ADD_NODE        0.717   0.444           0.327   0.300   0.324   0.276

UPDATE_NODE     23.0    21.5            1.217   1.083   1.240   0.995

DELETE_NODE     22.9    21.6            1.260   0.675   1.285   1.018   

GET_NODE        22.7    20.9            0.913   2.355   0.941   0.622

ADD_LINK        47.6    23.5            2.988   1.610   3.142   2.255

DELETE_LINK     31.9    21.6            2.063   1.610   2.407   1.701

UPDATE_LINK     51.1    25.8            3.238   2.507   3.407   2.395   

COUNT_LINK      16.7    10.9            0.686   0.571   0.739   0.547

MULTIGET_LINK   22.6    18.9            1.599   1.195   1.603   1.136

GET_LINKS_LIST  35.6    27.6            1.910   2.181   2.056   3.945

And the data below is example output from the end of one test, RocksDB on flash for the 12 1-hour run.

ADD_NODE count = 627235  p25 = [0.2,0.3]ms  p50 = [0.2,0.3]ms  p75 = [0.2,0.3]ms  p95 = [0.3,0.4]ms  p99 = [1,2]ms  max = 263.83ms  mean = 0.276ms

UPDATE_NODE count = 1793589  p25 = [0.7,0.8]ms  p50 = [0.8,0.9]ms  p75 = [0.9,1]ms  p95 = [1,2]ms  p99 = [4,5]ms  max = 301.453ms  mean = 0.995ms

DELETE_NODE count = 246225  p25 = [0.7,0.8]ms  p50 = [0.8,0.9]ms  p75 = [0.9,1]ms  p95 = [1,2]ms  p99 = [4,5]ms  max = 265.012ms  mean = 1.018ms

GET_NODE count = 3150740  p25 = [0.4,0.5]ms  p50 = [0.5,0.6]ms  p75 = [0.5,0.6]ms  p95 = [0.9,1]ms  p99 = [3,4]ms  max = 301.078ms  mean = 0.622ms

ADD_LINK count = 2189319  p25 = [1,2]ms  p50 = [2,3]ms  p75 = [2,3]ms  p95 = [3,4]ms  p99 = [7,8]ms  max = 317.292ms  mean = 2.255ms

DELETE_LINK count = 727942  p25 = [0.4,0.5]ms  p50 = [0.6,0.7]ms  p75 = [2,3]ms  p95 = [3,4]ms  p99 = [6,7]ms  max = 320.13ms  mean = 1.701ms

UPDATE_LINK count = 1949970  p25 = [1,2]ms  p50 = [2,3]ms  p75 = [2,3]ms  p95 = [3,4]ms  p99 = [7,8]ms  max = 393.483ms  mean = 2.395ms

COUNT_LINK count = 1190142  p25 = [0.3,0.4]ms  p50 = [0.4,0.5]ms  p75 = [0.5,0.6]ms  p95 = [0.8,0.9]ms  p99 = [2,3]ms  max = 296.65ms  mean = 0.547ms

MULTIGET_LINK count = 127871  p25 = [0.7,0.8]ms  p50 = [0.9,1]ms  p75 = [1,2]ms  p95 = [1,2]ms  p99 = [4,5]ms  max = 272.272ms  mean = 1.136ms

GET_LINKS_LIST count = 12353781  p25 = [0.5,0.6]ms  p50 = [1,2]ms  p75 = [1,2]ms  p95 = [3,4]ms  p99 = [38,39]ms  max = 2360.432ms  mean = 3.945ms

REQUEST PHASE COMPLETED. 24356814 requests done in 3601 seconds. Requests/second = 6762

Linkbench for MySQL and MongoDB with a large database

This has results for Linkbench where the database is much larger than RAM. I previously shared results for a database that can be cached in RAM.

I used Linkbench for MySQL and LinkbenchX for MongoDB as described in the Configuration section in a previous post. The value of maxid1 was set to 1,000,000,001 in the FBWorkload.properties file. For the cached database test that was set to 20,000,001. The test server has 144G of RAM, 40 hyperthread cores and PCIe flash. The tests were configured to use 10 threads for loading and 20 threads for queries. I disabled the binlog during the load for MySQL and enabled it during the query tests. The oplog was always enabled for MongoDB.

I don't report results for mmapv1 because the load would take too long and I might not have enough storage. I used WebScaleSQL for MySQL and the v3.0-fb MongoDB partners branch for MongoDB. The following configurations were tested:

• mysql.rocks.log - MySQL with RocksDB and the binary log enabled
• mysql.inno.log - MySQL with InnoDB and the binary log enabled
• mongo.rocks.log - MongoDB with RocksDB and the oplog enabled
• mongo.wiredtiger.log - MongoDB with WiredTiger and the oplog enabled
• mongo.mmap.log - MongoDB with mmapv1 and the oplog enabled

Results

The test pattern is to load the data then do 24 1-hour runs of the query test. The results below include:

• load time - the number of seconds for the load test
• load rate - the average insert rate during the load test
• load size - the database size in GB when the load ended
• 2h qps - the average QPS during the 2nd 1-hour query test
• 2h size - the database size in GB after the 2nd 1-hour query test
• 12h qps - the average QPS during the 12th 1-hour query test
• 12h size - the database size in GB after the 12th 1-hour query test
• 24h qps - the average QPS during the 24th 1-hour query test
• 24h size - the database size in GB after the 24th 1-hour query test

load    load    load    2h      2h      12h     12h     24h     24h

time    rate    size    qps     size    qps     size    qps     size    server

43820   99759   707g    22039   748g    25696   805g    25947   831g    mysql.inno.log

24261   180184  368g    32469   372g    30180   383g    29321   394g    mysql.rocks.log

251688  17368   630g    9670    633g    6762    644g    6768    656g    mongo.rocks.log

175740  24874   695g    8533    766g    8019    791g    7718    806g    mongo.wt.log

Update - I ran a test for mmapv1 with maxid1=200M rather than 1B, so it has about 20% of the data compared to the others. I didn't have enough time or disk space to use the larger value with mmap. The data is below:

load    load    load    2h      2h      12h     12h     24h     24h

time    rate    size    qps     size    qps     size    qps     size    server

276350  3165    553g    3222    555g    3084    568g    2969    573g    mongo.mmap.log

Some conclusions from the results:

• the load rates are similar to the rates for the cached database and IO when the database is larger than RAM doesn't have a big impact on performance. But this might depend on fast storage and these results are for a server with PCIe flash.
• relative to the query rates for the cached database, RocksDB suffers the least and WiredTiger suffers the most when the database is larger than RAM.
• the size for RocksDB was similar between MySQL and MongoDB for the cached database. That is not the case here as MongoRocks uses almost 1.7X more space than MyRocks. I have yet to debug this. For MongoRocks all data is stored in one column family. For MyRocks I used one column family per index and if nothing else this makes it easy to determine how much space and IO is consumed per index. There are also more optimizations in MyRocks to remove tombstones earlier but the space difference shows up immediately during the load and there shouldn't be tombstones at that point. MongoDB also uses more indexes than MySQL (9 vs 4) for Linkbench and that might be the reason but this is still a mystery.
• I restarted MySQL after the load test to enable the binlog prior to the query tests. InnoDB was configured to use direct IO so I lost the contents of the buffer pool during the restart. Performance suffered for the early query tests until the buffer pool warmed up and that includes the 2h test I reported above. This explains why QPS was better for the 12h and 24h results with InnoDB. Buffered IO is used for MySQL+RocksDB so less data is lost during a restart (block cache must be warmed up but OS filesystem cache is not wiped).

Comparison with the cached database results

The table below shows the ratio of the rates from this test with the rates for the cached database. The goal is to determine how much performance degrades when the database is larger than RAM. The ratio is expressed as a percentage and 50% means that the rate for the uncached database was 50% of the rate for the cached database. Note that the load rates are close to 100% for RocksDB while InnoDB degrades a bit and WiredTiger degrades more. This is expected from a write-optimized algorithm like RocksDB and b-trees like InnoDB and WiredTiger.

The results for 2h for InnoDB are less accurate because mysqld was restarted as described in the previous section. For the 24h results MySQL degrades less than MongoDB and RocksDB degrades less than b-trees (WiredTiger & InnoDB).

load    2h      24h

rate    qps     qps     server

97.6%   67.6%   63.0%   mysql.rocks.log

95.0%   42.9%   52.0%   mysql.inno.log

98.8%   66.9%   50.1%   mongo.rocks.log

88.7%   33.4%   34.8%   mongo.wiredtiger.log

And the same data in a chart...