Performance comparison tests of MySQL and InnoDB table storage structures

MySQL supports two major table storage formats, MyISAM and InnoDB. Last month, when I was working on a project, I first used InnoDB, and the result was extremely slow, with only a few inserts per second. It was later converted to MyISAM format, with tens of thousands of inserts per second. It was decided that the performance of the two tables was too different. Later, I speculated that the difference should not be so slow. I estimated that there was something wrong with the written insert statement, so I 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 plan:
1. MyISAM format is tested in two cases: transaction and non-transaction:
2. Test AutoCommit = 1 in InnoDB format (begin transaction is not used and begin transaction mode is used),
is used         AutoCommit=0 (not begin transaction and begin transaction mode). The test method is to insert 10,000 records. In order not to affect each other, a special test table was established 1. MyISAM does not use transaction table:
CREATE TABLE `MyISAM_NT` (
  `TableId` int(11) NOT NULL default '0',
  `TableString` varchar(21) NOT NULL default ''
) ENGINE=MyISAM; 2. Transaction table for MyISAM :
CREATE TABLE `MyISAM_TS` (
  `TableId` int(11) NOT NULL default '0',
  `TableString` varchar(21) NOT NULL default ''
) ENGINE=MyISAM; 3, InnoDB close AutoCommit, no transaction :
CREATE TABLE `INNODB_NA_NB` (
  `TableId` int(11) NOT NULL default '0',
  `TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB; 4. InnoDB closes AutoCommit with 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, no transaction :
CREATE TABLE `INNODB_AU_NB` (
  `TableId` int(11) NOT NULL default '0',
  `TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB; 6. InnoDB opens AutoCommit, using transaction :
CREATE TABLE `INNODB_AU_BE` (
  `TableId` int(11) NOT NULL default '0',
  `TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB; The Python script for the test is as follows: #!/usr/bin/env Python
'''
MyISAM,InnoDB performance comparison
Water 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)

      c             # esmysql access 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:
            # rewrite the 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
                      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 is that AUTOCOMMIT is on by default,
My program didn't explicitly call BEGIN; Start the transaction, causing each insert to automatically Commit, seriously affecting the speed.
It was a stupid mistake! Related reference:
http://dev.mysql.com/doc/mysql/en/COMMIT.html
http://dev.mysql.com/doc/mysql/en/InnoDB_and_AUTOCOMMIT.html