Members Members   Search Search   Help Help   Register Register   Login Login   Home Home


Home » Performance » MySQL » Storage engine for a datmart
icon5.gif  Storage engine for a datmart [message #3457] Fri, 22 August 2008 02:52 Go to next message
vgatto is currently offline vgatto
Messages: 25
Registered: November 2006
Junior Member
I'm currently designing a data mart using MySQL. My updates and inserts will occur rarely (at most hourly) and they will always be made in bulk by first loading the data into temporary tables and then merging it with the destination table. The selects will be far more numerous, coming from a set of tightly defined end-user reporting queries. Right now I'm using MyISAM, mostly because I don't need the transaction support of InnoDB, and I'm hoping to gain a performance advantage when loading data.

I've read some things that suggest that as the tables and indexes grow large, InnoDB may perform better at index caching and table joins than MyISAM. Anyone out there have any experience using either MyISAM or InnoDB for a data mart or data warehouse?
Report message to a moderator
Re: Storage engine for a datmart [message #3459 is a reply to message #3457 ] Sat, 23 August 2008 09:38 Go to previous messageGo to next message
Speeple is currently offline Speeple
Messages: 91
Registered: August 2006
Member
Data warehousing is a storage heavy practice. InnoDB consumes substantially more storage per than MyISAM for the same data.

The reason InnoDB can perform faster in some cases is due to the adaptive hash indexes, which creates a hash index smartly depending on the frequency of data hits.

Unless you can store a good percentage of your data in RAM I doubt it will be much benefit. Not to forget that InnoDB not only caches index data in its buffer, but also normal row data.

Martin Gallagher | Speeple: The latest news
Report message to a moderator
Re: Storage engine for a datmart [message #3474 is a reply to message #3457 ] Thu, 28 August 2008 18:37 Go to previous message
brooksaix is currently offline brooksaix
Messages: 3
Registered: February 2008
Junior Member
One of the main advantages InnoDB has over MyISAM is clustered indexes, which is often underestimated as there is often (usually?) one prime access path for reports, such as dates.

Plus, with sensible table optimization, the size advantage of MyISAM over InnoDB is often more like 60% to 100%.

For more information, and to completely plug my own blog:

http://dbscience.blogspot.com/2008/08/innodb-suitability-for -reporting.html

[Updated on: Fri, 29 August 2008 16:26]

Report message to a moderator
Previous Topic:MYSQL - Dynamic Column Type Change
Next Topic:Innodb and Partitioning
Goto Forum:
  

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


Current Time: Fri Dec 5 11:26:53 EST 2008

Total time taken to generate the page: 0.00978 seconds
.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software

MySQL Performance | Forum authority Badge