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
Arewriteswrittentodiskimmediately,orlazily?
Writesarephysicallywrittentothejournalwithin100milliseconds.Atthatpoint,thewriteis“durable”inthesensethatafterapull-plug-from-wallevent,thedatawillstillberecoverableafterahardrestart.
Whilethejournalcommitisnearlyinstant,MongoDBwritestothedatafileslazily.MongoDBmaywaittowritedatatothedatafilesforasmuchasoneminutebydefault.Thisdoesnotaffectdurability,asthejournalhasenoughinformationtoensurecrashrecovery.Tochangetheintervalforwritingtothedatafiles,seesyncdelay.
mongodb的journal相当于innodb的redolog,这就是说,mongodb是不能保证ACID的D的(journalflush之前的突然断电会导致journal的丢失),
现在准备调整下innodb的redologflush机制,
innodb_flush_log_at_trx_commit改成了2以后,时间变成了
937
还是有一定的差距.innodb不仅有自己的redolog,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
用时为
282826252516
myisam用时为
8546,13343,15141
这很正常,因为数据越多,建立索引的时间越长
在插入前执行flushstatus可以将系统状态值清零,
然后通过showstatuslike'key%'来查看这三次插入对myisam索引的写入次数
三次插入结果如下
Key_read_requests203395
Key_reads31337
Key_write_requests74792
Key_writes74792
Key_read_requests203208
Key_reads29998
Key_write_requests78341
Key_writes78341
Key_read_requests201336
Key_reads30191
Key_write_requests67267
Key_writes67267
看到对索引进行了很多次的写入.
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
showstatuslike'key%结果是
Key_read_requests203405
Key_reads31261
Key_write_requests74937
Key_writes20519
Key_read_requests203228
Key_reads30194
Key_write_requests78402
Key_writes41929
Key_read_requests201399
Key_reads30016
Key_write_requests67432
Key_writes37548
发现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表的索引装入keybuffer,再执行插入操作
三次用时分别为
2453,2453,2390
索引读写为
Key_read_requests203364
Key_reads0
Key_write_requests74746
Key_writes0
Key_read_requests203244
Key_reads0
Key_write_requests78502
Key_writes0
Key_read_requests201357
Key_reads0
Key_write_requests67180
Key_writes0
delaykeywrite的坏处在于如果不能及时把index的改动从内存flush到硬盘,就可能造成myisam索引文件和数据文件的不一致
myisam还有种手段是INSERTDELAYED,按照文档的说法,
WhenaclientusesINSERTDELAYED,itgetsanokayfromtheserveratonce,andtherowisqueuedtobeinsertedwhenthetableisnotinusebyanyotherthread.
修改和它相关的几个变量
set GLOBAL delayed_queue_size=10000; set GLOBAL delayed_insert_limit=10000
现在时间是
2484,1406,3740
真奇怪,按理说应该是一致的才对啊.
INSERTDELAYED的坏处是一旦服务器crash掉,queue中的insert请求就会丢失.
另外吐槽下mongodb的锁居然是整个数据库的,我了个去,比myisam的表锁粒度还粗.....
HowgranulararelocksinMongoDB?
Changedinversion2.2.
Beginningwithversion2.2,MongoDBimplementslocksonaper-databasebasisformostreadandwriteoperations.Someglobaloperations,typicallyshortlivedoperationsinvolvingmultipledatabases,stillrequireaglobal“instance”widelock.Before2.2,thereisonlyone“global”lockpermongodinstance.
Forexample,ifyouhavesixdatabasesandonetakesawritelock,theotherfivearestillavailableforreadandwrite.