Two main table storage formats MYISAM, InnoDB supported by MySQL, first used INNODB, and the result speed is particularly slow, and 10 can be inserted in 1 second. Later, changed to myisam format, inserted for one second. At that time, the performance of these two tables was too big. Later, I speculated, should not be like this slow, it is estimated to write the insert statement has a problem, decided to do a test: Redhat Linux9, 4CPU, memory 2G, mysql version is 4.1.6-gamma-standard test program: Python Python- MySQL module. Test Scheme: 1, MyISAM Formats Test, Transaction and No Transaction: 2, InnoDB Format Test AutoCommit = 1 (No Begin Transaction and with Begin Transaction Mode), AutoCommit = 0 (no Begin Transaction and use Begin Transaction mode Four cases. The test method is to insert 10,000 records. In order to test the unaffective influence, a dedicated test table is created separately, and the table statements are as follows: 1, Myisam does not have the transaction table: Create Table `Myisam_nt` (` Tableid` int (11) Not Null Default '0', `TableString` Varchar (21) NOT NULL Default '') Engine = Myisam;
2, Myisam with transaction table: Create Table `Myisam_ts` (` Tableid` int (11) Not null default '0', `TableString` VARCHAR (21) NOT NULL DEFAULT '') ENGINE = MyISAM;
3, INNODB turns off AutoCommit, don't do business: Create Table `InnoDB_NA_NB` (` Tableid` int (11) Not null default '0', `TableString` VARCHAR (21) Not null default '') Engine = InnoDB;
4, INNODB Close AutoCommit, transaction: Create Table `Innodb_na_be (` Tableid` int (11) Not null default '0', `TableString` VARCHAR (21) NOT NULL DEFAULT '') Engine = InnoDB;
5, InnoDB opens AutoCommit, don't do business: Create Table `InnoDB_au_nb` (` Tableid` int (11) Not null default '0', `TableString` VARCHAR (21) NOT NULL Default ') Engine = InnoDB;
6, INNODB opens AutoCommit, uses transactions: Create Table `Innodb_au_be (` Tableid` int (11) Not null default '0', `TableString` Varchar (21) Not null default '') Engine = InnoDB; test Python script as follows:! # / usr / bin / env Python '' 'MyISAM, InnoDB performance comparison of: spinach (Invalid) time: 2004-10-22' '' import MySQLdb import sysimport osimport stringimport timec = 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 autocummit =" 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 "" 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 Access Cursordb_host = "LocalHost" DB_NAME = "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: # Rewrive the created database connection before each operation 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 I 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 () test results are as follows: config: [localhost root / demo] dbmyisam_nt 04-10-22 16: 33: 2404-10-22 16: 33: 26myisam_nt count: 10000 used time: 2.1132440567 Myisam_TS 04-10-22 16: 33: 26EXECSQL: key; execSql: commit; 04-10-22 16: 33: 29myisam_ts count: 10000 used time: 2.65475201607innodb_na_nb 04-10-22 16: 33: 29EXECSQL: set autocmit = 004-10-22 16: 33: 31INNODB_NA_NB count: 10000 used time: 2.51947999001INNODB_NA_BE 04-10-22 16: 33: 31ExecSQL: Set AUTOCOMMIT = 0ExecSQL: BEGIN; ExecSQL: COMMIT; 04-10-22 16: 33: 35INNODB_NA_BE count: 10000 used time: 3.85625100136INNODB_AU_NB 04-10-22 16: 33: 35ExecSQL: Set AUTOCOMMIT = 104-10-22 16: 34: 19INNODB_AU_NB count: 10000 used time: 43.7153041363INNODB_AU_BE 04-10-22 16: 34: 19ExecSQL : Set autonommit = 1execsql: recommit; 04-10-22 16: 34: 22INNDB_AU_BE COUNT: 10000 Used Time: 3.1432819365 Conclusion: The main reason for this influence is that AutoCommit default settings are open, I The program did not explicitly call Begin; starting a transaction, resulting in automatic commits per insertion, seriously affecting the speed. Calling is also a low-level error! Related reference: http://dev.mysql.com/doc/mysql/en/commit.htmlhttp: //dev.mysql.com/doc/mysql/en/innodb_and_autocommit.html
