Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 2
why innodb over myisam? 
 
mk2dev
Member
 
Total Posts:  42
Joined:  2008-08-07
Chicago, IL
 

Just curious on the decision to have all the tables innodb over myisam? It would seem that for many of the tables, like EAV for example, is primarily read. In a dev environment, we found a significant speed boost by changing the engine to myisam for many of the product related tables although we aren’t rolling that out to a production environment without asking here first.

obviously there are benefits to transactions, especially when it comes to committing orders, etc. but I would imagine the speed bosot from myisam on many of the tables would justify giving that up. Obviously table lock vs. row lock is an issue on writes, as well as a myriad of other tradeoffs.

Just looking for some insight, and perhaps a reason to NOT run myisam in production.

 
Magento Community Magento Community
Magento Community
Magento Community
 
actionweb
Jr. Member
 
Total Posts:  7
Joined:  2008-08-16
 

I essentially asked the same question a few weeks ago ( http://www.magentocommerce.com/boards/viewthread/15182/ )… apparently no one is listening.

We have hosting clients using Magento (not the latest version) using myisam, but since it’s not officially supported, I wouldn’t recommend it.  Until we decide whether we’ll support InnoDB, we’ll probably start steering our hosting clients away from Magento.

 
Magento Community Magento Community
Magento Community
Magento Community
 
mk2dev
Member
 
Total Posts:  42
Joined:  2008-08-07
Chicago, IL
 

I think I saw your post too… yeah, it is kind of frustrating, since the entire catalog is mostly read, rarely write… and the speed boost is significant, it could go along way in the speed improvement category…

 
Magento Community Magento Community
Magento Community
Magento Community
 
actionweb
Jr. Member
 
Total Posts:  7
Joined:  2008-08-16
 

You make a really good point about increased speed, which is another really good reason to stick with myisam.  Anyhow, I was mostly replying to your thread in hopes to keep it alive… it’d be great if a few others started to chime in… perhaps then this issue would get some attention.

 
Magento Community Magento Community
Magento Community
Magento Community
 
mk2dev
Member
 
Total Posts:  42
Joined:  2008-08-07
Chicago, IL
 

understand and agree. It seems some posts get lost in the ‘I am a noob and I can’t figure out the documentation” kind of posts. Personally, I feel their pain, as I am not in love with the documentation either, but I digress…

 
Magento Community Magento Community
Magento Community
Magento Community
 
SimpleHelixcom
Enthusiast
 
Avatar
Total Posts:  906
Joined:  2007-08-31
Huntsville, AL
 

Okay I’d like to chime in a little about this subject.

As an application developer myself, I do see the benefits of InnoDB, more importantly in the row-lock issues.
This is very important on a high-transactional databases where you simply do not want any rows to be locked which can cause significant i/o wait time on the database.

But I’ve seen many databases when managed and configured correctly with InnoDB to not have any types of performance impacts.

I know several multi-million hits/day sites that are using InnoDB and love everything about it without any problems in performance.

I think what it all comes down to is this EAV database pattern. Joins are one of the most expensive queries in any types of SQL and EAV really takes JOINs to a whole new level. I know Magento put in a lot of efforts to cache their database side but I don’t think it’s wise to rely everything per cache-level.

Personally, I don’t really see much gain in EAV but then again I’ve never really used it to say much about it. For all I know, I think EAV could be good for small datasets but not for a huge site with millions of data in it.

As for the database backup, yes true, it’s one of the first hurdles we had to overcome when we were hosting Magento. We had to pay expensive backup solutions to provide things like backups at the kernel block etc. Magento is certainly not for every hosting companies.

 
Magento Community Magento Community
Magento Community
Magento Community
 
misteroriginal
Member
 
Total Posts:  48
Joined:  2008-03-26
 
actionweb - 10 September 2008 09:32 AM

I essentially asked the same question a few weeks ago ( http://www.magentocommerce.com/boards/viewthread/15182/ )… apparently no one is listening.

Welcome to Magento! You need to learn to ask questions in a manner that draw attention. Your thread may be deleted at a later date, but you will get passionate answers and FAST!

Since the Magento team may be too embarrassed to answer this one, let MrO speculate that decisions regarding Magento development are not always based on what is best in terms of what is actually BEST. Magento is (effectively) a big fat Space Shuttle made of lead bricks that must first fly without blowing up in order to wow the crowds and not be cut from the congressional budgets in favor of sex education programs for kindergartners.

Translation: there is really no good reason for many of the decisions made (InnoDB vs MyISAM) beyond the priority is placed on having a database that is solid and simple to develop, even if it is waaaaaaay inefficient. I believe mk2dev’s instincts are correct.

Gotta get Magento off the launchpad without killing anyone before we can put someone other than monkeys on board.

 
Magento Community Magento Community
Magento Community
Magento Community
 
mrballcb
Jr. Member
 
Total Posts:  6
Joined:  2008-05-04
 
mk2dev - 18 August 2008 09:35 AM

Just curious on the decision to have all the tables innodb over myisam? It would seem that for many of the tables, like EAV for example, is primarily read. In a dev environment, we found a significant speed boost by changing the engine to myisam for many of the product related tables although we aren’t rolling that out to a production environment without asking here first.

obviously there are benefits to transactions, especially when it comes to committing orders, etc. but I would imagine the speed bosot from myisam on many of the tables would justify giving that up. Obviously table lock vs. row lock is an issue on writes, as well as a myriad of other tradeoffs.

Just looking for some insight, and perhaps a reason to NOT run myisam in production.

I’m considering switching to MyISAM as well, but not necessarily for the same reasons.  We have a master<->master replication setup.  The plan was to load balance reads and writes, but for now load is low and everything is running on one master, while the other master is used for maintenance and database backups (and there are a few other slaves for webmail).  We have mysql configured for InnoDB per table.  Since Magento wants one database per website, we currently have about 1400 magento databases (and growing), each with innodb tablespace inside of it. 

The issue that I’m having is that a couple times, mysql on the second master has crashed.  The restart time for mysql after a crash seems to be about 20 minutes (and growing) due to the redo/undo logs.  Getting rid of all that innodb table space if it’s not needed would speed up the undo/redo tremendously.  The extra speed of queries would be a bonus.

If we could make magento use one database, that would be best (but I acknowledge that would most certainly require innodb due to its row level locking--I’m not aware if this is something that is being worked on).  If we could make magento use myisam instead of innodb without issues, that would be a step in the right direction.

 
Magento Community Magento Community
Magento Community
Magento Community
 
blizzard182
Sr. Member
 
Avatar
Total Posts:  115
Joined:  2008-09-06
Argentina
 

Since I am not a DBA, I have to ask.

Is it possible to have several databases, one in Innodb and one with myisam? I mean, the catalog could be myisam and the orders a innodb. Is it possible?

Of course if will need some re writing, but it will add something to my other post about the how to delete orders.  (http://www.magentocommerce.com/boards/viewthread/1680/P90/#t65181)

Cheers

 
Magento Community Magento Community
Magento Community
Magento Community
 
mrballcb
Jr. Member
 
Total Posts:  6
Joined:  2008-05-04
 
blizzard182 - 02 October 2008 05:20 PM

Since I am not a DBA, I have to ask.

Is it possible to have several databases, one in Innodb and one with myisam? I mean, the catalog could be myisam and the orders a innodb. Is it possible?

Of course if will need some re writing…

In mysql, you can set the engine type per table (as part of the CREATE TABLE sequence, or it can be adjusted afterwards with ALTER TABLE).  You can have multiple tables using InnoDB and multiple tables using MyISAM (and multiple tables using NDB, etc) all within the same database.  This is, for example, the way that @Mail does it, a commercial webmail package.  They make address books and such, things which are most frequently read traffic, of type MyISAM.  They make logs (login errors and successes, sent messages, statistics) of type InnoDB because there is more liable to be locking issues due to frequent writes.  Your suggestion seems to be right in line with that paradigm.

Not having studied the Magento table structures and construction yet (and foreign keys, assuming there are any), it’s hard to say if this paradigm could be easily applied to Magento and what the consistency consequences are.  Internally we are just beginning to talk about it.  We have done very little looking into it, so have no results to report.  Our main motivation is the issue down the road: what will happen when there are 50000 websites, each with 194 tables that are all InnoDB?  Performance is expected to be ok, but crash recovery will be brutal.

Our issue is compounded by the fact that we chose to use InnoDB per table, which means there is an ibd file for each table (that’s set to use InnoDB) of each database.  We chose this way because the ease of emergency restorations on a per table basis has saved us in the past (with MyISAM).  At this point though, it’s more of an annoyance.

Personally, I’d like to see a small redesign where Magento shares one giant database, which would require the addition of a table for entity name and id, and fields in some tables to map to that new table (just off the top of my head, it’s liable to be much more involved than that).  But I ask for a lot sometimes grin

Anybody’s experience and expertise is welcomed on this.  Thanks for the feedback!

 
Magento Community Magento Community
Magento Community
Magento Community
 
actionweb
Jr. Member
 
Total Posts:  7
Joined:  2008-08-16
 
mrballcb - 03 October 2008 05:01 AM


Performance is expected to be ok, but crash recovery will be brutal.

Agreed.  Just for this one downside of InnoDB alone I think that the Magento developers need to re-think dropping support for MyISAM.  InnoDB isn’t a good choice for every environment.

Just my opinion.

 
Magento Community Magento Community
Magento Community
Magento Community
 
m00dawg
Jr. Member
 
Total Posts:  1
Joined:  2008-10-12
 

Ok so I am a DBA and I’m not liking all that I’m hearing.

There are plentiful reasons to using InnoDB over MyISAM. What is probably burning most of you is the task of proper tuning for InnoDB. With MyISAM you can get away with some degree of poor database tuning. Not so for InnoDB. Settings should investigate are:

innodb_buffer_pool_size=#M
innodb_log_file_size=#M <-- Changing this requires removing your ib_log* files after a clear restart - see MySQL docs!
innodb_support_xa=0 <-- If not using MySQL replication

innodb_buffer_pool_size, in a perfect world where RAM is limitless, is set to the size of your working dataset. When you can do this, that means that basically all of your data is living in RAM. Anytime you do a read, it’s from RAM. When you do a write, of course, InnoDB will push this down to disk (by default) but least the modified result in RAM. Now if you haven’t set innodb_buffer_pool_size beyond the default, then you’re working with a tiny buffer pool of only 8MB. That’s not good enough for most applications. By the way, MyISAM only buffers indexes in RAM via the key_buffer. Since InnoDB can do both indexes and data, you can see how this can really result in awesome performance (if used right).

innodb_log_file_size controls the size of the ib_logfile* files. These are used when InnoDB manages transactions. On a COMMIT, for instance, the data is first written to the log and then, periodically, do the data-files. Appending to log files is cheap. Writing to the data-files is expensive. If these files are too small, InnoDB has no choice but to flush the log out before a transaction is commited, which ends up being expensive.

innodb_support_xa controls whether are not InnoDB will participate in two-phase commit operations. This is usually only needed when using binary logs or replication. Turning this off allows InnoDB to use group commit functions which can result in greater efficiency.

InnoDB also supports transactions which is probably a big reason why Magento using it and this is a GREAT idea. You increase concurrency and make sure everything is ALWAYS consistent. I am still evaluating Magneto for my own use, so I haven’t seen whether or not it supports it, but InnoDB also can use foreign keys. That means it’s basically impossible to make one’s data relationally inconsistent and it simplifies application code.  Frankly, most, if not all, web-applications should be using transactions. The fact that other shopping carts don’t is concerning as a DBA.

Now MyISAM has great uses as well. But the use-cases are different, particularly when InnoDB is being used for transactions. In that sense, MyISAM is best used more as a data warehouse to store archived data. There are other ways to do this, but for vanilla MySQL installs, MyISAM is the way to go. InnoDB also isn’t very efficient when it comes to locking large numbers of rows. When more than 30% of the table is locked at any time, MyISAM may be the better alternative IF transactions are not needed.

Places to go for better insight into this topic would be:
http://www.mysqlperformanceblog.com/ (I recommend buying their book as well, “High Performance MySQL 2nd Edition")
http://dev.mysql.com/doc/refman/5.0/en/
http://www.planetmysql.org/

 
Magento Community Magento Community
Magento Community
Magento Community
 
ptjedi
Sr. Member
 
Avatar
Total Posts:  250
Joined:  2008-03-12
Porto, Portugal
 

In my humble opinion this performance issue should be optional and not mandatory as some of us need to make profound changes on our hosting service in order to start a fresh Magento install. It is not fair to introduce this at this point when such efforts have been made to understand and work with Magento as THE ecommerce option for some companies.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Crucial Web Host
Guru
 
Avatar
Total Posts:  364
Joined:  2007-11-08
Phoenix, AZ
 
ptjedi - 11 February 2009 08:49 AM

In my humble opinion this performance issue should be optional and not mandatory as some of us need to make profound changes on our hosting service in order to start a fresh Magento install. It is not fair to introduce this at this point when such efforts have been made to understand and work with Magento as THE ecommerce option for some companies.

It’s not a performance issue.  InnoDB has been a requirement of Magento since beta - it’s ‘always’ been a system requirement for Magento to properly perform.  As far as I know, it’s also always been listed as a sysytem requirement from the beginning.

The only real change here is that the installer now makes sure you have all the requirements met.  This hasnt always been the case and has caused a lot of people a lot of problems that could have easily been avoided had they read the system requirements.

The best thing you can do is get things fixed now and convert your tables to Innodb.

 
Magento Community Magento Community
Magento Community
Magento Community
 
actionweb
Jr. Member
 
Total Posts:  7
Joined:  2008-08-16
 

InnoDB may be better, but we don’t have the time to build the additional infrastructure to support it.  (If we were talking about manually configuring a single machine, it’d be no big deal.) The easiest solution we found was just to tell customers that magento is no longer supported and we give them a list of other options.  Perhaps one day in the future when we have time to add InnoDB support to our hosting service we’ll consider recommending magento again.  Since magento is just one solution in a sea of solutions that do support myisam, nobody seems to care so far.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ptjedi
Sr. Member
 
Avatar
Total Posts:  250
Joined:  2008-03-12
Porto, Portugal
 
Crucial Web Host - 11 February 2009 09:29 AM

It’s not a performance issue.  InnoDB has been a requirement of Magento since beta - it’s ‘always’ been a system requirement for Magento to properly perform.  As far as I know, it’s also always been listed as a sysytem requirement from the beginning.

The only real change here is that the installer now makes sure you have all the requirements met.  This hasnt always been the case and has caused a lot of people a lot of problems that could have easily been avoided had they read the system requirements.

The best thing you can do is get things fixed now and convert your tables to Innodb.

I understand what you are saying but the problem here resides on the host that may be not interested on activating an exotic option that will only slow down the server in general. As my host said “I cannot activate InnoDB on just one account - it is a global setting for every accounts and that may compromise the fast access of other applications”. So far, only Magento is requiring this among other common software.

IMHO this is not the best approach as we all want Magento to spread fast and gain reputation. This is just another wall.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2