The performance comparison test of MyISAM and InnoDB of MySQL two kinds of table storage structure

mysql| Comparison | performance
MySQL supports the two main table storage format Myisam,innodb, last month when doing a project, first used InnoDB, the result is particularly slow, 1 seconds can only insert 10. Later replaced by the MyISAM format, a second inserted tens of thousands of bars. It was decided that the performance of the two tables was too different. Later, I guess, should not be so slow, it is estimated that the inserted statement has a problem, decided to do a test:
Test environment: Redhat LINUX9,4CPU, Memory 2g,mysql version for 4.1.6-gamma-standard
Test program: Python+python-mysql module.
Test scenario:
1, MyISAM format test, transaction and no transaction two kinds of situations:
2, InnoDB format respectively test autocommit=1 (do not BEGIN TRANSACTION and use the Begin Transaction Mode),
Autocommit=0 (without BEGIN transaction and with BEGIN TRANSACTION mode) four kinds of cases.

The test method is to insert 10,000 records. In order to test does not affect each other, a separate set of test tables, the table statement is as follows:
1, MyISAM do not use the transaction table:
CREATE TABLE ' Myisam_nt ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar not NULL default '
) Engine=myisam;

2, MyISAM with the transaction table:
CREATE TABLE ' Myisam_ts ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar not NULL default '
) Engine=myisam;

3, InnoDB close autocommit, do not have to do business:
CREATE TABLE ' Innodb_na_nb ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar not NULL default '
) Engine=innodb;

4, InnoDB close autocommit, with business:
CREATE TABLE ' Innodb_na_be ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar not NULL default '
) Engine=innodb;

5, InnoDB Open autocommit, no business:
CREATE TABLE ' Innodb_au_nb ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar not NULL default '
) Engine=innodb;

6, InnoDB Open autocommit, with business:
CREATE TABLE ' Innodb_au_be ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar not NULL default '
) Engine=innodb;

The Python scripts tested are as follows:

#!/usr/bin/env Python
'''
Myisam,innodb Performance Comparison
Author: spinach (Invalid)
Time: 2004-10-22
'''
Import MySQLdb
Import Sys
Import OS
Import string
Import time

c = None

Testtables = [("Myisam_nt", none,0),
("Myisam_ts", none,1),
("Innodb_na_nb", 0,0),
("Innodb_na_be", 0, 1),
("Innodb_au_nb", 1,0),
("Innodb_au_be", 1, 1)
]

Def BeginTrans ():

print "Execsql:begin;"
C.execute ("BEGIN;")

Return

Def Commit ():

print "EXECSQL:COMMIT;"
C.execute ("COMMIT;")
Return

def autocommit (flag):

Print "Execsql:set autocommit =" +STR (flag)
C.execute ("Set autocommit =" +STR (flag))
Return

def getcount (table):
#print "Execsql:select count (*) from" +table
C.execute ("SELECT count (*) from" +table)
return C.fetchall () [0][0]


def addtable (table,tableid,tablestring):

sql = "INSERT into" +table+ (TableId, tablestring) VALUES ("+ tableid+", "" + tablestring + ")"
Try
C.execute (SQL)
Except Mysqldb.operationalerror,error:
Print "AddTable error:", error
return-1;
Return C.rowcount


def main ():

argv = sys.argv

If Len (argv) < 2:
print ' Usage: ', argv[0], ' TableId testcount \ n '
Sys.exit (1)

Global C #mysql访问cursor


Db_host = "localhost"
db_name = "Demo"
Db_user = "root"
DB_USER_PASSWD = ""

Print "config:[%s%s/%s%s] db\n"% (db_host,db_user,db_user_passwd,db_name)

If Len (argv) > 2:
TableID = argv[1]
testcount = Int (argv[2]) #

For Test in Testtables:
#每次操作前都重写建立数据库连接
Try
mdb = MySQLdb.connect (Db_host, Db_user, DB_USER_PASSWD, db_name)
Except Mysqldb.operationalerror,error:
Print "Connect mysql[%s%s/%s%s] DB error:% (db_host,db_user,db_user_passwd,db_name), Error," \ n "
Sys.exit (1)
Else
c = Mdb.cursor ()
Table,autocommit,trans = Test
StartTime = Time.time ()
Print table, "", Time.strftime ("%y-%m-%d%h:%m:%s", Time.localtime ())

If Autocommit!= None:
Autocommit (autocommit)

If trans = 1:
BeginTrans ()

For I in Xrange (testcount):
tablestring = "%020d"%i
if (addtable (table,tableid,tablestring) <1):
Print "AddTable Error", tablestring

If trans = 1:
Commit ()

Print Time.strftime ("%y-%m-%d%h:%m:%s", Time.localtime ())
Endtime = Time.time ()
Usedtime = Endtime-starttime

Print table, "Count:", getcount (table), "Used Time:", Usedtime
C.close ()
Mdb.close ()


if __name__ = = ' __main__ ':
Main ()
The test results are as follows:

Config:[localhost Root/demo] DB

Myisam_nt 04-10-22 16:33:24
04-10-22 16:33:26
Myisam_nt count:10000 used time:2.1132440567
Myisam_ts 04-10-22 16:33:26
Execsql:begin;
Execsql:commit;
04-10-22 16:33:29
Myisam_ts count:10000 used time:2.65475201607
INNODB_NA_NB 04-10-22 16:33:29
Execsql:set autocommit = 0
04-10-22 16:33:31
INNODB_NA_NB count:10000 used time:2.51947999001
Innodb_na_be 04-10-22 16:33:31
Execsql:set autocommit = 0
Execsql:begin;
Execsql:commit;
04-10-22 16:33:35
Innodb_na_be count:10000 used time:3.85625100136
INNODB_AU_NB 04-10-22 16:33:35
Execsql:set autocommit = 1
04-10-22 16:34:19
INNODB_AU_NB count:10000 used time:43.7153041363
Innodb_au_be 04-10-22 16:34:19
Execsql:set autocommit = 1
Execsql:begin;
Execsql:commit;
04-10-22 16:34:22
Innodb_au_be count:10000 used time:3.14328193665
Conclusion:
The main reason for this effect is that the AUTOCOMMIT default setting is open,
My program at the time did not explicitly call begin, and the start of the transaction resulted in an automatic commit of each insert, which severely affected the speed.
It's also a low-level mistake!

Related reference:
Http://dev.mysql.com/doc/mysql/en/COMMIT.html
Http://dev.mysql.com/doc/mysql/en/InnoDB_and_AUTOCOMMIT.html