Is your MySQL Database using INNODB or MyISAM?

Discussion in 'Community Forum Software' started by Wayne Luke, Jun 9, 2009.

  1. Wayne Luke

    Wayne Luke Regular Member

    Joined:
    Apr 2, 2009
    Messages:
    992
    Likes Received:
    276
    As the title says. Is your Database using INNODB Or MyISAM tables? And why do you use the tables you do? Do you know? Do you know the difference between the two?
     
  2. FullMetalBabe

    FullMetalBabe Zealot

    Joined:
    May 30, 2009
    Messages:
    2,912
    Likes Received:
    339
    I do not know, nor do I know the differences. First time I've read the terms.
     
  3. Lynne

    Lynne Regular Member

    Joined:
    May 26, 2009
    Messages:
    333
    Likes Received:
    32
    Location:
    Home Sweet Home!
    Our tables are MyISAM, but not because of any conscious choice I made. I think it is just the default. I've seen the two types mentioned, usually because of some problem that comes up because of a mix of the two in a database, but I really don't know the difference. I'd love to hear what you have to say about them.
     
  4. Wayne Luke

    Wayne Luke Regular Member

    Joined:
    Apr 2, 2009
    Messages:
    992
    Likes Received:
    276
    Once there are a few more answers, I'll post the differences and make a recommendation on which on you should be using.

    phpMyAdmin shows you what type of table you're using in the interim.
     
  5. Bundy

    Bundy Admin Talk Staff

    Joined:
    Apr 19, 2005
    Messages:
    842
    Likes Received:
    36
    Location:
    Boston
    I use MyISAM. Simply because it is the default.
     
  6. Soliloquy

    Soliloquy Regular Member

    Joined:
    Jun 3, 2009
    Messages:
    2,402
    Likes Received:
    66
    Location:
    New York City
    all my databases use MyISAM tables simply because it's the default, but I confess I don't know the difference. Please enlighten us!
     
  7. Nick

    Nick Regular Member

    Joined:
    Jul 27, 2008
    Messages:
    7,444
    Likes Received:
    219
    Mine are MyISAM as well, as that is the default, apparently. I honestly have no clue what the difference is.

    I look forward to your information. :)
     
  8. Lynne

    Lynne Regular Member

    Joined:
    May 26, 2009
    Messages:
    333
    Likes Received:
    32
    Location:
    Home Sweet Home!
    I think I recall reading that there are problems with searching if you run INNODB, but that INNOBD is faster and suggested for larger forums? Off to search this out cuz I'm impatient at times.... :D
     
  9. Wayne Luke

    Wayne Luke Regular Member

    Joined:
    Apr 2, 2009
    Messages:
    992
    Likes Received:
    276
    Functionally, MyISAM is an indexed database but it is not relational. By this, I mean you cannot define relationships within MyISAM.

    With INNODB you can define relationships so that when you create a new user record, it can create additional records in other tables if needed. Or when you create a new thread, the database verifies that the User ID is correct and exists in the database.

    That is the base difference. There are more though.

    My tables are INNODB except for some special cases as I will get to in a minute. There are specific reasons why. The first has to do with what is called locking.

    Locking
    MyISAM and INNODB deal with locking in different ways. Besides being relational INNODB tables are also transactional. What tht means is that different users can update records at the same time without worrying about data collision.

    When you update or create a record on a MyISAM table, MySQL will lock the table and prevent anyone else from writing to it. So if two people post a message at the exact same millisecond, one will have to wait until the other is finished. On large forums this causes problems and can cause data loss.

    When you update or create a record on an INNODB table, it locks the row that you are working with. This means that others can update or create additional rows at the same time. The result is faster performance for the end user and the application.

    Crash Recovery
    We've all had the dreaded MySQL 145 error. This signifies that your MyISAM table has crashed and needs to be repaired. This has to do with how the files are handled and your server handles them. With a MyISAM table, MySQL will make bulk writes to the disk when its buffers are full. Basically MyISAM doesn't have any kind of crash recovery built into it. When you repair a table, it checks every record and makes sure the indexes in the index file matches. If they do not, than it rewrites the index file. This can result in data loss because a record without its appropriate indexes is inaccessible.

    I liken INNODB to a journaling file system. What is does is it writes each transaction to disk when it is completed. This is more reliable in that if the server fails (i.e. complete power failure) you don't lose the buffers. This makes crash recovery a lot easier to deal with. Also INNODB is designed to be self-repairing and constantly checks the integrety of its filespace.

    Storage
    MyISAM stores each table in a series of three files. There is a file for the table structure, one for the data and one for the indexes. The plus side is that MyISAM tables can be larger, up to 256 terabytes. That is one of the only benefits though as MyISAM doesn't support clustered indexes or transactions.

    InnoDB offers the use of compressed file storage so your tables take up less space on the drive. However InnoDB is limited to tables of 64 Terabytes or smaller. InnoDB also supports pages file support and clustered indexing for faster lookups and queries.

    Indexing
    This is where the main differences are that would affect most people today. MyISAM supports two types of indexing that InnoDB does not. Both types would be beneficial for today's community owners. The first is Geospatial Indexing. That is latitude and longitude lookups. This is probably not well used at the moment but can be useful for geo-targetting advertising or mapping. The second is the big one and that is Full Text indexing. This is what people use for searching a lot of times.

    Unfortunately, InnoDB does not support either of these at this time. It does support Clustered Indexing though. This allows the database to store similar records together. This should allow for faster retrieval in most cases.

    Summary
    Those are the basic differences between the two. On Windows Machines, InnoDB is the default database storage type. In order versions of the Linux version, MyISAM is the default. Some systems set InnoDB to be the default. There are other differences but they aren't as significant. Another thing about InnoDB is that you can download a plugin for MySQL at the InnoDB website that gives more features and better performance. It just overlays the InnoDB engine in MySQL.

    I use InnoDB personally because it gives better performance and allows for better programming practices. That and I absolutely love Oracle databases and InnoDB has been owned by Oracle for five years. Unfortunately in vBulletin 3.X, you have to set the thread and post tables to use MyISAM for searching purposes. Luckily this will be changed with the new search in vBulletin 4.0.
     
    5 people like this.
  10. Abomination

    Abomination Zealot

    Joined:
    Jun 1, 2009
    Messages:
    1,514
    Likes Received:
    102
    When I go into phpMyAdmin there is a button titled "information_schema" and it brings up things like "INNODB_CMP" so I'll assume INNODB is what the database is using (hostgator).


    Nice to hear 4.0 search might be better.
     
  11. Nick

    Nick Regular Member

    Joined:
    Jul 27, 2008
    Messages:
    7,444
    Likes Received:
    219
    Lol, another great article of a post, Wayne. ;)
     
  12. Soliloquy

    Soliloquy Regular Member

    Joined:
    Jun 3, 2009
    Messages:
    2,402
    Likes Received:
    66
    Location:
    New York City
    This is probably a stupid question, Wayne Luke, but can you have different table types in the same database? Is there an ideal set-up for vB?
     
  13. Bendo

    Bendo Novice

    Joined:
    Jun 9, 2009
    Messages:
    32
    Likes Received:
    2
    First Name:
    Ben
    Yes you can.
     
  14. Wayne Luke

    Wayne Luke Regular Member

    Joined:
    Apr 2, 2009
    Messages:
    992
    Likes Received:
    276
    As Bendo said, "yeah, you can have multiple table types in the same database".

    Speaking for vBulletin (not sure how other systems handle it), there are 4 tables that shouldn't be InnoDB. When you install vBulletin it will make those four tables have the appropriate table type. The rest will use the database default. The four tables in question are session, cpsession, post and thread. The first two should be memory or heap tables and the latter two should be MyISAM.
     
  15. Soliloquy

    Soliloquy Regular Member

    Joined:
    Jun 3, 2009
    Messages:
    2,402
    Likes Received:
    66
    Location:
    New York City
    Well, I'm not about to attempt changing my vB table types without reading up on them a lot more, but if I wanted to do that would it require any finagling with the vB script? I remember you're supposed to set your default table type in the config file; would changing that be enough to handle a change to mixed table types?
     
  16. Wayne Luke

    Wayne Luke Regular Member

    Joined:
    Apr 2, 2009
    Messages:
    992
    Likes Received:
    276
    You don't set your table type in the config.php you set your database type. vBulletin currently supports MySQL and MySQLi. You don't need to make any changes to vBulletin's code if you change table types.
     
  17. Soliloquy

    Soliloquy Regular Member

    Joined:
    Jun 3, 2009
    Messages:
    2,402
    Likes Received:
    66
    Location:
    New York City
    I see. Well, as I said I'm not going to try it until I understand it better, but you've given me the impetus to read up on it some more. Thank you :)
     
  18. Vekseid

    Vekseid Regular Member

    Joined:
    Jun 2, 2009
    Messages:
    393
    Likes Received:
    13
    ...why should your thread table by MyISAM?

    In SMF, phpBB, and MyBB, at least, thread tables are among those you most want to be InnoDB. I know vB is stricter about view counter updates but...

    My general recommendation is: transient data (sessions, flood control in SMF) goes MEMORY/HEAP, mass inserted data (private messages and posts) goes to MyISAM (with concurrent_insert=2), everything else to InnoDB (with a nice buffer pool and preferably large pages).
     
  19. Wayne Luke

    Wayne Luke Regular Member

    Joined:
    Apr 2, 2009
    Messages:
    992
    Likes Received:
    276
    Fulltext searching.

    Users have the option to search the post text or thread title alone. Posts can have different titles than threads.
     
  20. Vekseid

    Vekseid Regular Member

    Joined:
    Jun 2, 2009
    Messages:
    393
    Likes Received:
    13
    ...vB still uses fulltext searching? O_O

    *twitch* Alright then.
     

Share This Page