MySQL two table storage structures: MyISAM and InnoDB performance comparison test, myisaminnodb
Reproduced http://www.jb51.net/article/5620.htm
MySQL supports two main table storage formats: MyISAM and InnoDB. InnoDB was used for a project last month. The results are very slow and only a dozen pieces can be inserted in one second. Later I changed to MyISAM format and inserted tens of thousands of entries in one second. At that time, the performance of the two tables was also significantly different. Later, I guessed that the difference should not be so slow. It was estimated that there was a problem with the insert statement, and I decided to perform a test:
Test environment: Redhat Linux9, 4 CPU, 2 GB memory, MySQL version 4.1.6-gamma-standard
Test Program: Python + Python-MySQL module.
Test Plan:
1. Test the MyISAM format in two cases: transaction and no transaction:
2. Test AutoCommit = 1 in InnoDB format (do not use begin transaction or begin transaction mode ),
AutoCommit = 0 (begin transaction and begin transaction mode are not required. The test method is to insert 10000 records. A dedicated test table is created separately for test without affecting each other. The table creation statement is as follows:
1. MyISAM does not need a transaction table:
Create table 'myisam _ NT '(
'Tableid' int (11) not null default '0 ',
'Tablestring' varchar (21) not null default''
) ENGINE = MyISAM; 2. MyISAM transaction table:
Create table 'myisam _ Ts '(
'Tableid' int (11) not null default '0 ',
'Tablestring' varchar (21) not null default''
) ENGINE = MyISAM; 3. InnoDB closes AutoCommit without transactions:
Create table 'innodb _ NA_NB '(
'Tableid' int (11) not null default '0 ',
'Tablestring' varchar (21) not null default''
) ENGINE = InnoDB; 4. InnoDB closes AutoCommit and uses transactions:
Create table 'innodb _ NA_BE '(
'Tableid' int (11) not null default '0 ',
'Tablestring' varchar (21) not null default''
) ENGINE = InnoDB; 5. InnoDB enables AutoCommit without transactions:
Create table 'innodb _ AU_NB '(
'Tableid' int (11) not null default '0 ',
'Tablestring' varchar (21) not null default''
) ENGINE = InnoDB; 6. InnoDB enables AutoCommit and uses transactions:
Create table 'innodb _ AU_BE '(
'Tableid' int (11) not null default '0 ',
'Tablestring' varchar (21) not null default''
) ENGINE = InnoDB; The Python script for testing is as follows :#! /Usr/bin/env Python
'''
Performance Comparison of MyISAM and InnoDB
By Invalid)
Time: 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.exe cute ("BEGIN ;")
Returndef Commit ():
Print "ExecSQL: COMMIT ;"
C.exe cute ("COMMIT ;")
Returndef AutoCommit (flag ):
Print "ExecSQL: Set AUTOCOMMIT =" + str (flag)
C.exe cute ("Set AUTOCOMMIT =" + str (flag ))
Return
Def getcount (table ):
# Print "ExecSQL: select count (*) from" + table
C.exe cute ("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.exe cute (SQL)
Failed t 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 # access cursor from mysql
Db_host = "localhost"
Db_name = "demo"
Db_user = "root"
Db_user_passwd = ""
Print "Config: [% 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:
# Re-create a database connection before each operation
Try:
Mdb = MySQLdb. connect (db_host, db_user, db_user_passwd, db_name)
Failed t MySQLDb. OperationalError, error:
Print "Connect Mysql [% 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_nb04-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_nb04-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 that the speed is affected is that AUTOCOMMIT is enabled by default,
At that time, my program did not explicitly call BEGIN; started the transaction, resulting in automatic Commit for each inserted entry, seriously affecting the speed.
This is also a low-level error! References:
Http://dev.mysql.com/doc/mysql/en/COMMIT.html
Http://dev.mysql.com/doc/mysql/en/InnoDB_and_AUTOCOMMIT.html
