mysql大战mongodb

nosql真是风起云涌,其中mongodb号称是比较像传统关系型数据库的,现在用mysql和mongodb进行一些简单评测.
mongodb建立名为status的collection,并且添加uid这个列上的索引.
db.createCollection("status");
db.status.ensureIndex( { uid: 1} )

建立结构相似的innodb类型的表

show create table 20130306innodb
CREATE TABLE `20130306innodb` (
  `id` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `content` char(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `20130306t_idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

测试分别插入1000条数据,主键递增,uid随机,content为同一个字符串"database"

插入innodb的方法为

 Class.forName("com.mysql.jdbc.Driver").newInstance();
  String url = "jdbc:mysql://localhost/crap";
  Connection conn = DriverManager.getConnection(url, "root", "root");

  PreparedStatement ps = conn
    .prepareStatement("insert into 20130306innodb values(?,?,?)");

  Random random = new Random();

  int count = 0;
  long begin = System.currentTimeMillis();
  while (count < 10000) {
   ps.setInt(1, count);
   ps.setInt(2, random.nextInt());
   ps.setString(3, "database");
   ps.execute();
   count++;
  }
  long end = System.currentTimeMillis();
  System.out.println(end - begin);

结果为
61719.

插入mongodb的方法为

  MongoClient mongoClient = new MongoClient("localhost", 27017);
  DB db = mongoClient.getDB("mydb");
  DBCollection coll = db.getCollection("status");

  Random random = new Random();

  int count = 0;
  long begin = System.currentTimeMillis();
  while (count < 10000) {

   BasicDBObject doc = new BasicDBObject("uid", random.nextInt())
     .append("content", "database")
     .append("_id", count);
   coll.insert(doc);
   count++;
  }
  long end = System.currentTimeMillis();
  System.out.println(end - begin);

结果为
375

看来innodb似乎比mongodb慢多了,不过这是因为innodb为了保证ACID特性,做了很多牺牲.
现在我们看看mongodb的一些牺牲.
摘自mongodb的FAQ

引用

Are writes written to disk immediately, or lazily?

Writes are physically written to the journal within 100 milliseconds. At that point, the write is “durable” in the sense that after a pull-plug-from-wall event, the data will still be recoverable after a hard restart.
While the journal commit is nearly instant, MongoDB writes to the data files lazily. MongoDB may wait to write data to the data files for as much as one minute by default. This does not affect durability, as the journal has enough information to ensure crash recovery. To change the interval for writing to the data files, see syncdelay.

mongodb的journal相当于innodb的redo log,这就是说,mongodb是不能保证ACID的D的(journal flush之前的突然断电会导致journal的丢失),
现在准备调整下innodb的redo log flush机制,
innodb_flush_log_at_trx_commit改成了2以后,时间变成了
937
还是有一定的差距.innodb不仅有自己的redo log,mysql也有用于replication的log
在mysql的配置文件注释掉log-bin=mysql-bin,同时设置了innodb_doublewrite=0

现在innodb时间成了610

然后看看mysql另一种表引擎MyISAM

show CREATE table 20130306myisam

CREATE TABLE `20130306myisam` (
  `id` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `content` char(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `20130306t_idx_uid` (`uid`)
) ENGINE=MyISAM ROW_FORMAT=FIXED

用上面的插入方法插入数据到myisam表中,

时间是250.居然比mongodb还小一些.

下面试下表里已经有很多数据的情况下的插入.这种情况下索引的更新一般都是插入的瓶颈.因此这次在表上建立了4个索引,先插入100w数据,然后看再插入1w数据时候的情况

在mongodb的collection上建立多个索引
db.status.ensureIndex({uid2:1});
db.status.ensureIndex({uid3:1});
db.status.ensureIndex({uid4:1});

myisam表结构如下

CREATE TABLE `20130306mysiam2` (
  `id` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `uid2` int(11) DEFAULT NULL,
  `uid3` int(11) DEFAULT NULL,
  `uid4` int(11) DEFAULT NULL,
  `content` char(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `20130306t_idx_uid` (`uid`),
  KEY `20130306t_idx_uid2` (`uid2`),
  KEY `20130306t_idx_uid3` (`uid3`),
  KEY `20130306t_idx_uid4` (`uid4`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

修改上面的插入方法,在uid2,uid3,uid4上都插入随机数.
在有100w数据的表中连续3次插入1w条数据
mongodb
用时为
2828 2625 2516

myisam用时为
8546,13343,15141
这很正常,因为数据越多,建立索引的时间越长

在插入前执行flush status可以将系统状态值清零,
然后通过show status like 'key%'来查看这三次插入对myisam索引的写入次数

三次插入结果如下

引用

Key_read_requests 203395
Key_reads 31337
Key_write_requests 74792
Key_writes 74792

Key_read_requests 203208
Key_reads 29998
Key_write_requests 78341
Key_writes 78341

Key_read_requests 201336
Key_reads 30191
Key_write_requests 67267
Key_writes 67267

看到对索引进行了很多次的写入.



myisam有个特性,可以进行索引的延迟写入,
叫delay-key-write
http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_delay-key-write
现在将20130306mysiam2开启delay-key-write

alter table 20130306mysiam2 DELAY_KEY_WRITE=1

三次插入时间分别为
8032,15188,14203

show status like 'key%结果是

引用

Key_read_requests 203405
Key_reads 31261
Key_write_requests 74937
Key_writes 20519

Key_read_requests 203228
Key_reads 30194
Key_write_requests 78402
Key_writes 41929

Key_read_requests 201399
Key_reads 30016
Key_write_requests 67432
Key_writes 37548

发现Key_writes的数量并没有变少,观察下20130306mysiam2表的索引文件,
即20130306mysiam2.MYI文件,大小为 74M,当前的key_buffer_size太小,不能将索引文件完全装入

执行

set GLOBAL key_buffer_size=128*1024*1024

调整key_buffer_size的大小


然后

load index into cache 20130306mysiam2

将20130306mysiam2表的索引装入key buffer,再执行插入操作
三次用时分别为
2453,2453,2390

索引读写为

引用

Key_read_requests 203364
Key_reads 0
Key_write_requests 74746
Key_writes 0

Key_read_requests 203244
Key_reads 0
Key_write_requests 78502
Key_writes 0

Key_read_requests 201357
Key_reads 0
Key_write_requests 67180
Key_writes 0

delay key write的坏处在于如果不能及时把index的改动从内存flush到硬盘,就可能造成myisam索引文件和数据文件的不一致


myisam还有种手段是INSERT DELAYED,按照文档的说法,

引用

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

修改和它相关的几个变量

set GLOBAL  delayed_queue_size=10000;
set GLOBAL delayed_insert_limit=10000

现在时间是
2484,1406,3740

真奇怪,按理说应该是一致的才对啊.

INSERT DELAYED的坏处是一旦服务器crash掉,queue中的insert请求就会丢失.


另外吐槽下mongodb的锁居然是整个数据库的,我了个去,比myisam的表锁粒度还粗.....

引用

How granular are locks in MongoDB?

Changed in version 2.2.

Beginning with version 2.2, MongoDB implements locks on a per-database basis for most read and write operations. Some global operations, typically short lived operations involving multiple databases, still require a global “instance” wide lock. Before 2.2, there is only one “global” lock per mongod instance.

For example, if you have six databases and one takes a write lock, the other five are still available for read and write.

评论

4 楼 kabike 2013-04-04  

finallygo 写道

mongodb不是也需要维护索引么,而且听说也是用b tree实现的,难道它没有flush到磁盘??

不是很清楚...

3 楼 kabike 2013-04-04  

MrLee23 写道

数据贴，技术贴，不错，谢谢了

thanks

2 楼 finallygo 2013-04-04  

mongodb不是也需要维护索引么,而且听说也是用b tree实现的,难道它没有flush到磁盘??

1 楼 MrLee23 2013-04-04  

数据贴，技术贴，不错，谢谢了