Analysis of reading and writing performance of InnoDB and MyISAM

Premise: MySQL before 5.0, read and write performance difference is very big, read performance: MyISAM very strong

MySQL after 5.0, the gap is not very big

http://passover.blog.51cto.com/2431658/507265

http://blog.csdn.net/cchaha/article/details/1782723

http://www.taobaodba.com/

Since there is a recent project on system performance requirements, technology selection for a variety of reasons have been determined to use the MySQL database, the next step is to determine exactly which storage engine to use. Our application is a typical write-read less, write content is also very short, the stability of the system requires a high. So the storage engine is definitely set in the widely used InnoDB and MyISAM.

As for the comparison of the two online there are many, but after all, this thing is not complicated, decided to do it yourself, to verify in our scenario who better.

The beta version is MySQL Ver 14.14 distrib 5.1.49, for Debian-linux-gnu (i686), using InnoDB plugin 1.0.8 (officially known as better than built-in version performance) and the default MyISAM.

The test machine is my notebook, configured as follows: Intel Core 2 Duo P8600,2g*2 DDR3 1066 Ram, 320G HDD 5400 rpm.

Test one: Data insertion performance test, here I separately on the Innodb_flush_log_at_trx_commit parameter open and close are measured, each test is run 40s, the number of the table is the actual number of inserted bars.

MyISAM Innodb (Open) Innodb (off)

Single thread, insert 120000 6000060000

4 threads, inserting 40000*4 20000*440000*4

Single thread, batch 100/time Insert 3600*100800*1003000*100

Single thread, batch 200/time Insert 1800*200400*2001600*200

You can see that bulk insert performance is much higher than a single insert, but the size of one batch has little effect on performance. The parameters of whether each record refreshes the log have a significant impact on InnoDB performance. Overall, MyISAM performance is a bit more superior. One thing to note here is that during the insertion test, I monitored the system resources and found that MyISAM is very low on system resources, but InnoDB is very high on disk, and should be a lot more logs to log for transaction control.

Test two: Data read performance test. Reads 1000 records at random and reads them repeatedly.

MyISAM Innodb

Single thread, 200 reads 5.7s16.7s

4 threads, 200 reads 12s40.8s

It can be seen that the read performance of MyISAM is very scary, the performance gap is 3 times times the appearance.

The above two tests found that MyISAM almost outright win-win without the need for business, but to know that it is a table lock, InnoDB is a row lock, then the concurrent read and write simultaneous existence, the result will be what?!

Test three: Two threads concurrent write, 2 threads concurrently read.

MyISAM Innodb

Insert Write 40s:10000*2 one at a time read 200 *2:14s write 40s:60000*2 read 200 times *2:50s

Batch 100/Time Insert write 40s:1000*100*2 read 200 *2:10s write 40s:1500*100*2 read 200 times *2:50s

This immediately shows the InnoDB's strong performance in concurrency, with little performance degradation. The MyISAM single insertion speed has become very slow, and bulk insertion has also dropped 40% performance.

Summing up, in the application of writing more read less or InnoDB insert performance more stable, in the case of concurrency can also be basic, if the reading speed requires a relatively fast application or select MyISAM.

Also mention, here need to use to InnoDB of hot backup, in addition to use Master-slave way, also can choose Xtrabackup this open source software.

Analysis of reading and writing performance of InnoDB and MyISAM (RPM)