Professional Web Applications Themes

MyISAM vs. InnoDB - MySQL

Forgive me if this is an overly-asked question. I have heard that InnoDB is faster than MyISAM. Unfortunately, all of my personal benchmarks seem to show that MyISAM is much, much faster than InnoDB. I'm wondering if this is correct or if I'm doing something wrong. Could someone please direct me at a site with benchmarks comparing InnoDB to MyISAM. Thanks....

  1. #1

    Default MyISAM vs. InnoDB

    Forgive me if this is an overly-asked question.

    I have heard that InnoDB is faster than MyISAM. Unfortunately, all of
    my personal benchmarks seem to show that MyISAM is much, much faster
    than InnoDB. I'm wondering if this is correct or if I'm doing
    something wrong. Could someone please direct me at a site with
    benchmarks comparing InnoDB to MyISAM. Thanks.

    jinxidoru Guest

  2. #2

    Default Re: MyISAM vs. InnoDB

    "jinxidoru" <net> wrote: 

    This may be true in some cases. But not in general.

    Here is a refined statement: InnoDB may be faster if there are many
    independent connections to the database. InnoDB will probably be
    faster if there are concurrent reads/writes. In a single-thread
    scenario MyISAM will almost always be faster than InnoDB.

    But: InnoDB provides some important features that MyISAM lacks
    - transactions
    - foreign key constraints
    - row level locks
     

    Maybe you ran your InnoDB benchmarks with AUTO_COMMIT=On. This will
    kill all performance.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  3. #3

    Default Re: MyISAM vs. InnoDB

    I have seen just the opposite. For us, InnoDB is much faster. Our
    setup involves the data directory mounted on a NetApp 3020 filer. Under
    highly concurrent load tests (100+ clients) we were able to support
    approximately 950 transactions / second with InnoDB. Running the same
    test will all tables converted to MyISAM we were able to sustain
    approximately 420 /sec.

    Chris Back
    http://www.theusermanualsite.com


    Axel Schwenke wrote: 
    >
    > This may be true in some cases. But not in general.
    >
    > Here is a refined statement: InnoDB may be faster if there are many
    > independent connections to the database. InnoDB will probably be
    > faster if there are concurrent reads/writes. In a single-thread
    > scenario MyISAM will almost always be faster than InnoDB.
    >
    > But: InnoDB provides some important features that MyISAM lacks
    > - transactions
    > - foreign key constraints
    > - row level locks

    >
    > Maybe you ran your InnoDB benchmarks with AUTO_COMMIT=On. This will
    > kill all performance.
    >
    >
    > XL
    > --
    > Axel Schwenke, Senior Software Developer, MySQL AB
    >
    > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    > MySQL User Forums: http://forums.mysql.com/[/ref]

    Chris Guest

  4. #4

    Default Re: MyISAM vs. InnoDB

    It seems to me, please correct me if I'm wrong, that MyISAM is better
    with more tables or single query transactions. Whereas, InnoDB is
    better with multi-query transactions and fewer tables (due to the
    row-level locking). Is this correct?

    Chris Back wrote: 
    > >
    > > This may be true in some cases. But not in general.
    > >
    > > Here is a refined statement: InnoDB may be faster if there are many
    > > independent connections to the database. InnoDB will probably be
    > > faster if there are concurrent reads/writes. In a single-thread
    > > scenario MyISAM will almost always be faster than InnoDB.
    > >
    > > But: InnoDB provides some important features that MyISAM lacks
    > > - transactions
    > > - foreign key constraints
    > > - row level locks
    > > 
    > >
    > > Maybe you ran your InnoDB benchmarks with AUTO_COMMIT=On. This will
    > > kill all performance.
    > >
    > >
    > > XL
    > > --
    > > Axel Schwenke, Senior Software Developer, MySQL AB
    > >
    > > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    > > MySQL User Forums: http://forums.mysql.com/[/ref][/ref]

    jinxidoru Guest

  5. #5

    Default Re: MyISAM vs. InnoDB

    First things first: please don't top post!

    (quoting corrected)

    "Chris Back" <com> wrote: [/ref]
     

    You don't.
     

    That's exactly what I said: InnoDB will probably be faster for
    concurrent workload. Especially if you mix reads and writes.


    But again:
     [/ref]

    So in most cases choice of storage engine is based on features,
    not on performance.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  6. #6

    Default Re: MyISAM vs. InnoDB

    "jinxidoru" <net> wrote: 

    There are *no* transactions with MyISAM. If you need transactions,
    go InnoDB.

    MyISAM always locks whole tables. That is: reads and writes are
    mutually exclusive. Concurrent reads are fine, concurrent writes
    are sometimes possible. If you expect concurrent reads and writes
    on your tables -> go InnoDB. OTOH: MyISAM table locks are really,
    really fast. As long as you don't need hundreds of writes per
    second (along with reads on the same table) you can still use
    MyISAM. Reads and writes will be serialized internally, but you
    won't notice.

    Again: performance is not the only - not even the most important -
    criteria for chosing a storage engine.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

Similar Threads

  1. Dubbio problema InnoDB - MyIsam
    By Plaguebreath in forum MySQL
    Replies: 2
    Last Post: October 16th, 09:37 AM
  2. InnoDB VS MyISAM
    By howachen@gmail.com in forum MySQL
    Replies: 2
    Last Post: July 10th, 11:12 AM
  3. MyISAM VS InnoDB VS Memory
    By howachen@gmail.com in forum MySQL
    Replies: 0
    Last Post: June 11th, 02:07 PM
  4. Myisam Table size
    By alfred wallace in forum MySQL
    Replies: 0
    Last Post: June 3rd, 10:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139