MySQL versus MongoDB - fetch by secondary index

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')

Source code to query the MongoDB table:

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]

  gets = 0

  stats = SummaryStats(worst_n)

  while True:

    for loop in xrange(0, requests_per):

      target = random.randrange(0, rows)

      s = time.time()

      o = db.c.find_one({'sid': target})

      stats.update(s)

      assert o['_id'] == target

      if check:

        assert o['k'] == target

        sx = str(o['_id'])

        lsx = len(sx)

        assert o['c'] == sx+'x'*(120-lsx)

        assert o['pad'] == sx+'y'*(60-lsx)

      gets += 1