I have broken my promise to stop writing about this. Sorry, but I had to correct my mistake. I ran three micro-benchmarks: get by primary key, get by secondary key and update by primary key. MySQL had a higher peak QPS for all of them. Alas, the results for get by primary key were skewed because pymongo, the Python driver for MongoDB, uses more CPU than MySQLdb, the Python driver for MySQL. The client host was saturated during the test and this limited peak QPS to 80,000 for MongoDB versus 110,000 for MySQL.

I repeated one test using two 16-core client hosts with 40 processes per host. For that test the peak QPS on MongoDB improved to 155,000 while the peak for MySQL remained at 110,000. That is an impressive result. The results for get by secondary key and update by primary key are still valid as the server host saturated on those tests.

Now I must consider rewriting the test harness in Java, C or C++ or I could add MongoDB support to sysbench. I prefer Python. In addition to under-reporting MongoDB peak performance it also under-reports MySQL performance. I am able to get 180,000 peak QPS using sysbench on one 16-core client host and mysqld on another versus 110,000 using the Python equivalent.

This is the end of my public performance comparison of MongoDB versus MySQL, at least for the next few weeks. For these tests I used a 16-core x86_64 client host and an 8-core x86_64 server host, MySQL 5.1.50 with the Facebook patch, MongoDB 1.7.0 and Python clients. Ping between the client and server hosts was ~200us.

I tested 5 configurations:

• inno.5150fb.b0.s0 - MySQL 5.1.50, the Facebook patch, binlog disabled
• inno.5150fb.b1.s0 - MySQL 5.1.50, the Facebook patch, binlog enabled, sync_binlog=0
• inno.5150fb.b1.s1 - MySQL 5.1.50, the Facebook patch, binlog enabled, sync_binlog=1
• mongo.170.safe - MongoDB 1.7.0 and safe updates
• mongo.170.unsafe - MongoDB 1.7.0 and unsafe updates

InnoDB has much better throughput when the binlog is disabled.  The clients update rows selected at random from 2M rows. InnoDB is able to handle more of that load concurrently than MongoDB as a reader-writer lock is used that prevents concurrency within the database. Note that while InnoDB allows more of the update to be done concurrently, it isn't perfect as there are several global mutexes in MySQL/InnoDB including LOCK_open, kernel_mutex and the InnoDB buffer pool mutex. When the binlog is enabled by sync_binlog is disabled even more global mutexes are used. Finally, when the binlog is enabled and sync_binlog=1 then group commit is not enabled and updates are rate limited by the performance of fsync. In this case fsync is fast as a HW RAID card with battery backed cache was used.
This is output from PMP that demonstrates one source of mutex contention in mongod. The pileup occurs at mongo::MongoMutex which apprently implements the reader-writer lock.

     48 mongo::connThread,thread_proxy,start_thread,clone

     37 pthread_cond_wait@@GLIBC_2.3.2,boost::condition_variable::wait,mongo::MongoMutex::lock,mongo::receivedUpdate,mongo::assembleResponse

     10 recv,mongo::MessagingPort::recv,mongo::MessagingPort::recv

      2 

      1 select,mongo::Listener::initAndListen,mongo::listen,mongo::_initAndListen,mongo::initAndListen,main,select

      1 select,mongo::Listener::initAndListen

      1 pthread_cond_wait@@GLIBC_2.3.2,mongo::FileAllocator::Runner::operator(),thread_proxy,start_thread,clone

      1 nanosleep,mongo::DataFileSync::run,mongo::BackgroundJob::thr,thread_proxy,start_thread,clone

      1 nanosleep,mongo::ClientCursorMonitor::run,mongo::BackgroundJob::thr,thread_proxy,start_thread,clone

      1 nanosleep

      1 mongo::webServerThread,thread_proxy,start_thread,clone

      1 mongo::SnapshotThread::run,mongo::BackgroundJob::thr,thread_proxy,start_thread,clone

      1 mongo::interruptThread,thread_proxy,start_thread,clone

      1 mongo::BtreeBucket::findSingle,mongo::ModSetState::createNewFromMods,mongo::_updateObjects,mongo::updateObjects,mongo::receivedUpdate,mongo::assembleResponse

def query_mongo(host, port, pipe_to_parent, requests_per, dbname, rows, check, testname, worst_n, id):
  conn = pymongo.Connection(host, port)
  db = conn[dbname]
  signal.signal(signal.SIGTERM, sigterm_handler)

  gets = 0
  stats = SummaryStats(worst_n)

  while True:
    for loop in xrange(0, requests_per):

      target = random.randrange(0, rows)

      s = time.time()
      try:
        r = db.c.update({'_id': target}, {'$inc': {'k': 1 }}, safe=True)
        assert r['updatedExisting'] == True
        assert r['ok'] == 1

        stats.update(s)

        gets += 1

      except:
        assert got_sigterm

This continues the silly benchmark series and compares performance from concurrent clients that fetch by secondary key. The previous post compared fetch by primary key. The test setup was the same as before. Clients were run on a 16-core x86 server. The servers (mongod, mysqld) were run alone on 8-core and 16-core x86 servers. The tests were run for servers that were 1ms and 200us apart according to ping. The database server saturates earlier when the client is only 200us away. That is to be expected.

InnoDB tables are clusters on the primary key and a query that fetches all columns by PK only has to read data from one leaf block of the PK index. When all columns are fetched by secondary key then the secondary index leaf node and PK index leaf node must be read. As all data was cached for this test that does not make a big difference. Were data not cached the extra IO used to read the PK index leaf node would be significant.

This displays throughput on the 16-core server. MongoDB saturates earlier on the 16-core server than on the 8-core server. From vmstat output this appears to be mutex contention on the server but I cannot provide more details on where that occurs as the mongod binary I downloaded has been stripped.
This displays throughput on the 8-core server. Peak QPS for MongoDB is much better than on the 16-core server.
This displays response time for the 16 core server.
The test was repeated using a hosts that were 200us apart according to ping. The database host was an 8-core server in this test. The peak QPS is similar to the previous tests but the servers saturate with fewer concurrent clients. The results are here and have been updated to include results for MongoDB 1.6.2 and 1.7.0.

Source code to setup the MySQL table:

def setup_mysql(host, db, user, password, engine, rows):
  filterwarnings( 'ignore', category = MySQLdb.Warning )
  conn = connect_mysql(host, db, user, password)
  conn.autocommit(True)
  cursor = conn.cursor()
  cursor.execute('drop table if exists bm')
  cursor.execute('create table bm (id int primary key, sid int, k int, c char(120), pad char(60), key sidx(sid)) engine=%s' % engine)

  vals = []
  for x in xrange(0, rows):
    sx = str(x)
    lsx = len(sx)
    row = '(%d, %d, %d, "%s", "%s")' % (x, x, x, sx+'x'*(120-lsx), sx+'y'*(60-lsx))
    vals.append(row)

    if len(vals) == 1000:
      r = cursor.execute('insert into bm values %s' % ','.join(vals))
      vals = []
      print '... row %d, result %s' % (x, r)

  if vals:
    r = cursor.execute('insert into bm values %s' % ','.join(vals))
    vals = []
    print '... row %d, result %s' % (x, r)

Source code to query the MySQL table:


def query_mysql(host, db, user, password, pipe_to_parent, requests_per, rows, check, testname, worst_n, id):
  conn = connect_mysql(host, db, user, password)
  conn.autocommit(True)
  cursor = conn.cursor()

  gets = 0
  stats = SummaryStats(worst_n)

  while True:
    for loop in xrange(0, requests_per):

      target = random.randrange(0, rows)

      s = time.time()
      cursor.execute('select id, k, c, pad from bm where sid = %d' % target)
      sel_rows = cursor.fetchall()
      stats.update(s)

      if len(sel_rows) != 1:
        print 'No rows for %d' % target
        assert False
      if sel_rows[0][0] != target:
        print 'id is %s and should be %s' % (sel_rows[0][0], target)
        assert False

      gets += 1

Source code to setup the MongoDB collection:


def setup_mongo(host, port, dbname, rows):
  conn = pymongo.Connection(host, port)
  conn.drop_database(dbname)
  db = conn[dbname]

  for x in xrange(0, rows):
    sx = str(x)
    lsx = len(sx)
    db.c.save({'_id':x, 'sid':x, 'k':x, 'c':sx+'x'*(120 - lsx), 'pad':sx+'y'*(60 - lsx)})
    if x % 1000 == 0:
      print '... row %d' % x

  db.c.create_index('sid')