GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Change from MyISAM to InnoDB?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Change from MyISAM to InnoDB?

    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.34Slow 10 s 1 0.0/s 0.00 %DMS: 0.00 Log: OFFDMS 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.02Com_ 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.79Immediate 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?

  • #2
    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.

    Comment


    • #3
      debug wrote on Fri, 19 December 2008 10:05

      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.


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

      Comment


      • #4
        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.

        Comment


        • #5
          InnoDB will be slow with writes, unless:
          * you have a battery backed disk/raid controller
          * you put innodb_flush_log_at_trx_commit=0 (or 2) in your my.cnf file (which could cost you the last couple seconds of data in a crash)

          http://www.mysqlperformanceblog.com/2007/11/01/innodb-perfor mance-optimization-basics/

          Comment

          Working...
          X