[Novalug] OT: MySQL storage engines

Joel Fouse joel@fouse.net
Mon Jul 21 15:20:25 EDT 2008


On Mon, 2008-07-21 at 19:15 +0000, Jeff Stoner wrote:

> On Mon, 21 Jul 2008, Daniel Copeland wrote:
> 
> > I have been trying to understand the differences between MySQL storage
> > engines, specifically InnoDB and MyISAM.  What I am asking for is maybe an
> 
> MyISAM uses table-locking. An insert, update or delete will lock the 
> entire table until the statement completes. The exception to this are 
> concurrent inserts which a) must be configured and b) the table cannot 
> have any holes in the data. No holes allow the inserts at the end of the 
> table so reads can still happen. MyISAM does not support transactions 
> (START/COMMIT, ROLLBACK.)
> 
> InnoDB is a MVCC engine which supports row-level locking. It supports 
> transactions.
> 
> MyISAM has a key cache for buffering indexes. It has no cache for row 
> data. This means it relies on the OS for caching row data. InnoDB has 
> buffers for index and row data. A consequence of this is InnoDB can use 
> Direct I/O (O_DIRECT) while MyISAM doesn't.
> 
> To get a consistent backup of the data in MyISAM tables using mysqldump, 
> you must flush buffers and obtain a read lock on all tables being backed 
> up. Because InnoDB is MVCC, you can easily get a consistent backup of the 
> data using mysqldump without locking tables. If you mix the engines, you 
> will have headaches getting consistent dumps of data, especially for high 
> volume databases.
> 
> MyISAM uses 2 files, one holds indexes, the other holds data. InnoDB puts 
> indexs and data into the same file. InnoDB can hold all indexes and data 
> in one large, monolith file or use one file per table. This is 
> configurable.
> 
> InnoDB uses clustered indexes - the primary key index is stored with the 
> data, not as a separate index.
> 
> There are some considerable scalability issues with both engines when you 
> have large amounts numbers of CPUs (8+) and/or high volumes of traffic. 
> See http://www.mysqlperformanceblog.com for lost of good stuff.
> 
> > opinion on what would be best for a site a friend and me are setting up.  It
> > will have multiple users, and will basically be an image hosting site (kind
> > of like Flickr).  The MySQL database will be storing things like the image
> 
> Flickr uses a LOT of caching, like memcached. There are papers, articles 
> and blogs out there that describe how big sites scale their operations. 
> The architectures they use are very different from what most people would 
> consider normal or standard. A big one that's making headway is 
> denormalization of data - storing the same data in multiple places. 
> Scalability is about the end-to-end architecture, not about a few features 
> of database X, libraries for programming language Y or whiz-bang packge 
> for operating system Z.
> 
> > filename, the image's owner, rating info, and comments on the image.  Since
> > I am not storing critical information on the site (like credit cards or SS
> 
> Your definition of "critical information" is going to very different from 
> your users. If you loose the titles and descriptions of photos, your users 
> may end up abandoning you like rats fleeing a sinking ship. A better term 
> might be "regulated information."
> 
> > numbers), and this will be a (mostly) free service to the users, should I go
> > with MyISAM for lower resource overhead, or InnoDB for database
> 
> Lower resource overhead? In what sense? The differences between MyISAM and 
> InnoDB have nothing to do with resources. They were designed for 
> different end goals. A soccer ball and a football both have leather 
> covered air bladders. We'll assume the football uses less thread 
> to sew up the leather pieces (I don't know - I'm making this up.) This 
> would mean the football uses fewer resources. While you *can* use a 
> football to play a game of soccer, that isn't what it was designed for.
> 
> > reliability?  Regular (weekly) backups of the database will take place, so
> > even with MyISAM, I will have redundant copies.
> 
> Do weekly restores *and verification* of the data to test your backups. 
> Backups don't mean squat if you can't restore them. The restore is 
> pointless of the data is what it should be.
> 
> --Jeff
> 
> "I am not available for comment"


...And THAT summary was more than worth the price of admission.  Thanks
for the write-up, Jeff -- that was quite handy.

i must say, though, that in reading your compare/contrast, I have a hard
time thinking of when and why I would want to choose MyISAM over InnoDB.
In what kind of situations or setups does it make more sense?

- Joel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.firemountain.net/pipermail/novalug/attachments/20080721/b6186e7f/attachment.htm>


More information about the Novalug mailing list