MySQL Two table storage structure performance comparison test process

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. 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 test autocommit=1 (do not BEGIN TRANSACTION and use the Begin TRANSACTION Mode), autocommit=0 (do not BEGIN TRANSACTION and use 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(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=MyISAM;

2, MyISAM with the transaction table:

CREATE TABLE `MyISAM_TS` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=MyISAM;

3, InnoDB close autocommit, do not have to 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, with business:

CREATE TABLE `INNODB_NA_BE` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;