Performance testing for MySQL and MongoDB

Hardware and Software Environment

MySQL version: 5.1.50, driver version: 5.1.6 (the latest 5.1.13 has a lot of assorted problems)

MongoDB version: 1.6.2, driver version: 2.1

Operating system: Windows XP SP3 (This effect should not be small)

Cpu:intel Core2 E6550 2.33G

Memory: 2G (Enough)

MySQL startup parameters: Bin\mysqld--no-defaults--console--character-set-server=utf8--max_connections=1000--max_user_ connections=1000

MongoDB Startup parameters: Bin\mongod--dbpath data\--directoryperdb--rest--maxconns 1000–quiet

Use default parameters in addition to increasing the maximum number of connections

Test

This test is mainly to improve the current system of log storage and query performance to provide a reference, so the creation of tables also take the actual case, the following is the MySQL table statement:

CREATE TABLE ' flt_evecurrent ' ( ' NodeID ' int (one) not NULL DEFAULT ' 0 ', ' Fltid ' int (one) not NULL DEFAULT ' 0 ', ' ObjID ' int (one) DEFAULT NULL, ' StationID ' int (one) DEFAULT NULL, ' Evetype ' int (one) DEFAULT NULL, ' Severity ' int (one) DEFAULT NULL, ' Reporttime ' Date DEFAULT NULL, ' Createtime ' Date DEFAULT NULL, ' evecontent ' varchar (1024x768) DEFAULT NULL, ' evedesc ' varchar DEFAULT NULL, PRIMARY KEY (' NodeID ', ' Fltid ') );

Like MongoDB, the index is created according to the query field of the query statement, which creates an index for ObjID and Createtime two fields.

Insert 1 million records separately, and do 100 user concurrent query operations on them.

MySQL drops the table every time, MongoDB deletes the data directory every time.

Query, from the second query start, continuous record three times.

Results

	Insertion time	Query time
MySQL InnoDB Engine No Index	10 minutes, 33 seconds.	39.516 seconds, 35.907 seconds, 39.907 seconds
MySQL InnoDB engine has index	11 minutes, 16 seconds.	Very unstable: 22.531 seconds, 13.078 seconds, 23.078 seconds, 26.047 seconds, 21.234 seconds, 28.469 seconds, 20.922 seconds, 13.328 seconds
MySQL MyISAM Engine No Index	3 minutes, 21 seconds.	22.812 seconds, 23.343 seconds, 23.125 seconds
MySQL MyISAM Engine has index	3 minutes, 50 seconds.	10.312 seconds, 10.359 seconds, 10.296 seconds
MongoDB No Index	37 seconds	59.531 seconds, 60.063 seconds, 59.891 seconds
MongoDB has an index	50 seconds	3.484 seconds, 3.453 seconds, 3.453 seconds

Disk space consumption (in the case of index time, no index is the same):

MySQL MYISAM:57MB

MySQL INNODB:264MB

Mongodb:464mb

In addition to the test also found an interesting phenomenon, if the MongoDB query, if the query ObjID field alone, takes about 1 seconds, if the query Createtime field alone, takes about 10 seconds, if two fields together to check, is the above result, about 3 seconds, It is estimated that the query order is optimized within MONGODB.

Performance testing for MySQL and MongoDB