MySQL Performance: MySQL => Change from MyISAM to InnoDB?

Members

Help

Home

Home » Performance » MySQL » Change from MyISAM to InnoDB?

Show: Today's Messages :: Show Polls :: Message Navigator

Change from MyISAM to InnoDB? [message #3704]

Fri, 07 November 2008 08:58

armakuni
Messages: 4
Registered: July 2008

Junior Member

Usually people are talking about big databases but we have a very small database (~40MB, ~20 tables) where all tables are using MyISAM engine. My beginner question is: does it make sense to change to InnoDB engine? Current ratio between writes and reads from mysqlreport (last 80 days):

__ Questions ___________________________________________________________
Total         891.67M   128.0/s
  DMS         685.86M    98.4/s  %Total:  76.92
  Com_        111.21M    16.0/s           12.47
  COM_QUIT     97.60M    14.0/s           10.95
  -Unknown      3.00M     0.4/s            0.34
Slow 10 s           1     0.0/s            0.00  %DMS:   0.00  Log: OFF
DMS           685.86M    98.4/s           76.92
  SELECT      373.76M    53.6/s           41.92         54.50
  REPLACE     202.12M    29.0/s           22.67         29.47
  UPDATE      107.40M    15.4/s           12.04         15.66
  DELETE        2.42M     0.3/s            0.27          0.35
  INSERT      169.72k     0.0/s            0.02          0.02
Com_          111.21M    16.0/s           12.47
  set_option  102.20M    14.7/s           11.46
  stmt_prepar   2.99M     0.4/s            0.34
  stmt_execut   2.99M     0.4/s            0.34

and table lock status:

__ Table Locks _________________________________________________________
Waited          9.49M     1.4/s  %Total:   0.79
Immediate       1.19G   170.1/s

We are only interested about performance (speed) and reliability is not so important. Because there are lot of read and writes InnoDB should in theory be good if I understand correctly differences between MyISAM and InnoDB. But does the engine really matter in such a small database?

Report message to a moderator

Re: Change from MyISAM to InnoDB? [message #3826 is a reply to message #3704 ]

Fri, 19 December 2008 03:05

debug
Messages: 128
Registered: March 2008

Senior Member

Well, InnoDB provides better concurrency, because of row-based locking. MyISAM has only table-based locking. So you should get better performance if you have to run multiple updates/selects at the same time.

Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting

Report message to a moderator

Re: Change from MyISAM to InnoDB? [message #3829 is a reply to message #3826 ]

Sat, 20 December 2008 08:42

armakuni
Messages: 4
Registered: July 2008

Junior Member

debug wrote on Fri, 19 December 2008 10:05

Thank you for your answer. I definitely need to make good tests because with quick test MyISAM was faster than InnoDB.

Report message to a moderator

Re: Change from MyISAM to InnoDB? [message #3830 is a reply to message #3829 ]

Sat, 20 December 2008 09:02

debug
Messages: 128
Registered: March 2008

Senior Member

Yes, it really needs to be tested. I don't know how much do your queries wait until lock releases. Basically MyISAM is faster, but for many concurrent queries InnoDB is indeed better.

Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting

Report message to a moderator

Previous Topic:	Disabling Queries
Next Topic:	winmysqladmin restore problem

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 7 17:55:22 EST 2009

Total time taken to generate the page: 0.03917 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.