Reprint Http://www.jb51.net/article/5620.htm
MySQL supports the two main table storage format Myisam,innodb, last month to do a project, the first use of InnoDB, the result is particularly slow, 1 seconds can only insert 10. Later into the MyISAM format, one second to insert tens of thousands of bars. It was decided that the performance of the two tables was too different. Later, I guess, should not be different so slow, it is estimated that the INSERT statement has a problem, decided to do a test:
Test environment: Redhat LINUX9,4CPU, Memory 2g,mysql version 4.1.6-gamma-standard
Test procedure: Python+python-mysql module.
Test scenario:
1, MyISAM format test, transaction and no transaction two cases:
2, InnoDB format test autocommit=1 (without begin transaction and with the Begin Transaction mode),
Autocommit=0 (without BEGIN transaction and with BEGIN TRANSACTION mode) four cases. The test method is to insert 10,000 records. In order to test the non-interaction, a dedicated test table was created, with the following statement:
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 Transaction table:
CREATE TABLE ' Myisam_ts ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) Engine=myisam;3, InnoDB close the autocommit without a transaction:
CREATE TABLE ' Innodb_na_nb ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) engine=innodb;4, InnoDB close autocommit, with transaction:
CREATE TABLE ' Innodb_na_be ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) engine=innodb;5, InnoDB open autocommit, no transaction:
CREATE TABLE ' Innodb_au_nb ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) engine=innodb;6, InnoDB open autocommit, with transaction:
CREATE TABLE ' Innodb_au_be ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) Engine=innodb; The test Python script is as follows: #!/usr/bin/env python
‘‘‘
Myisam,innodb Performance Comparison
Water Spinach (Invalid)
Date: 2004-10-22
‘‘‘
Import MySQLdb
Import Sys
Import OS
Import string
Import Timec = Nonetesttables = [("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;")
Returndef Commit ():
print "EXECSQL:COMMIT;"
C.execute ("COMMIT;")
Returndef 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] Dbmyisam_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 is that the AUTOCOMMIT default setting is open,
My program did not explicitly call begin, and started a transaction, causing each insert to be automatically commit, seriously affecting the speed.
It's a low-level mistake too! Related references:
Http://dev.mysql.com/doc/mysql/en/COMMIT.html
Http://dev.mysql.com/doc/mysql/en/InnoDB_and_AUTOCOMMIT.html
MySQL Two table storage structure MyISAM and InnoDB performance comparison test
