Performance Comparison Test of MyISAM and InnoDB table storage structures in MySQL

Source: Internet
Author: User

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 ;")

Return

Def 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. rowcountdef 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] 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_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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.