[SERVER-32730] WiredTiger read-write performance for the insert benchmark Created: 17/Jan/18  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Storage
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Mark Callaghan Assignee: Backlog - Performance Team
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Assigned Teams:
Product Performance
Participants:

 Description   

This covers read-write performance for the insert benchmark. Other issues are open for load performance (SERVER-32707) and full scan performance (SERVER-32711).

I will start with results from inMemory-16 (database in memory, 16 clients, 16 collections) and the test where each writer is limited to 100 inserts/second, there are 16 collections, 16 query clients and 16 writer clients with a query/writer pair per collection.

The first table has throughput and HW efficiency metrics:

  • ips.av - average insert rate, the target is 1600 given the rate limits, but because of artifacts in my Python code the max is ~1584.
  • qps.av - the average query rate
  • wkb/i - KB written to storage per insert
  • Mcpu/q - CPU overhead per query, alas this includes CPU used for queries and inserts
  • size - database size in GB at test end
  • rss - mysqld/mongod process size at test end in GB
  • wmb/s - storage write MB/s
  • cpu - average CPU utilization from vmstat us and sy columns

From these results:

  • both engines sustain the target write rate
  • InnoDB gets ~2.2X more QPS
  • WiredTiger uses ~2.3X more CPU per query

ips.av  qps.av  wkb/i   Mcpu/q  size    rss     wmb/s   cpu     engine
1584    31386   36.09   1184    116     103.5   57.2    37.2    WiredTiger-3.6.0
1584    67772   42.50    518    113     121.9   67.3    35.1    InnoDB-5.7.17

The worst case response times are:

  • WiredTiger : ~0.5 seconds for insert, ~0.38 seconds for query
  • InnoDB: ~0.01 seconds for insert, ~0.009 seconds for query


 Comments   
Comment by Ramon Fernandez Marina [ 18/Jan/18 ]

Thanks for the detailed information mdcallag, adding to the backlog for investigation.

Comment by Mark Callaghan [ 17/Jan/18 ]

And for ioBound-none (16 clients, 16 collections, no compression, database larger than RAM)

The metrics are:

  • ips.av - average insert rate, the target is 1600 given the rate limits, but because of artifacts in my Python code the max is ~15845
  • qps.av - average query rate
  • wkb/i - KB written to storage per insert
  • r/q - iostat r/s divided by QPS, iostat
  • rkb/q - KB read from storage per query
  • Mcpu/q - CPU overhead per query, alas this includes CPU used for queries and inserts
  • size - database size in GB at test end
  • rss - mysqld/mongod process size at test end in GB
  • r/s - iostat r/s
  • rmb/s - MB read from storage per second
  • wmb/s - storage write MB/s
  • cpu - average CPU utilization from vmstat us and sy columns

Summary:

  • WiredTiger gets ~30% of the target insert rate, InnoDB gets the target insert rate
  • InnoDB gets ~7X more QPS than WiredTiger
  • WiredTiger does ~3.5X more reads from storage per query
  • WiredTiger uses ~6.6 more CPU per query

ips.av  qps.av  wkb/i   r/q     rkb/q   Mcpu/q  size    rss     r/s     rmb/s   wmb/s   cpu     engine
 4852   1328    44.11   19.62   371.95  10989    500    15.1    26045   493.8   214.0   14.6    WiredTiger-3.6.0
15845   9541    41.54    5.55    88.80   1656    413    40.0    52953   847.3   658.2   15.8    InnoDB-5.7.17

Worst-case response time:

  • WiredTiger - ~1.1 seconds for insert, ~0.16 seconds for query
  • InnoDB - ~0.12 seconds for insert, ~0.09 seconds for query

I haven't provided it for previous tests but my insert benchmark client maintains response time histograms. There are per client, so there are 16 histograms in the test with 16 clients.

For this test, this is one of the histograms for WiredTiger and then InnoDB

256us       1ms       4ms      16ms      64ms     256ms        1s        4s       16s        gt         max
 
# WiredTiger
    0         0         0         0       136      3348     46516         0         0         0    0.946401
 
# InnoDB
    0         0         0     28496     21480        24         0         0         0         0    0.098741

Comment by Mark Callaghan [ 17/Jan/18 ]

And now for inMemory-1 (16 clients, 1 collection, in-memory database) the results with each writer limited to 1000/second.

Summary:

  • WiredTiger doesn't sustain the target insert rate, InnoDB almost does
  • InnoDB gets ~2.4X more QPS
  • WiredTiger uses ~2.9X more CPU/query

ips.av  qps.av  wkb/i   Mcpu/q  size    rss     wmb/s   cpu     engine
15829   27935   15.96   1697    130     158.3   252.7   47.4    WiredTiger-3.6.0
15842   65617   19.89    577    131     133.8   315.1   37.9    InnoDB-5.7.17

Worst case response times:

  • WiredTiger : ~1.12 seconds for insert, ~0.40 seconds for query
  • InnoDB : ~0.05 seconds for insert, ~0.05 seconds for query
Comment by Mark Callaghan [ 17/Jan/18 ]

Still using inMemory-16, results for the test with each writer limited to 1000 inserts/second.

Summary:

  • neither engine sustains the target insert rate, but both are close as the max is ~15845
  • InnoDB writes ~1.5X more to storage per insert
  • WiredTiger uses ~3.5X more CPU per query
  • InnoDB sustains ~2.6X more queries/second

ips.av  qps.av  wkb/i   Mcpu/q  size    rss     wmb/s   cpu     engine
15038   25995   21.11   1931    120     114.0   317.5   50.2    WiredTiger-3.6.0
15838   67111   23.67    558    111     120.5   374.8   37.4    InnoDB-5.7.17

Worst case response times:

  • WiredTiger : ~0.65 seconds for insert, ~0.42 seconds for query
  • InnoDB : ~0.01 seconds for insert, ~0.008 seconds for query
Generated at Thu Feb 08 04:31:07 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.