Magento Forum

   
Page 1 of 2
why innodb over myisam? 
 
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
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

InnoDB is not an exotic option. MyISAM is the exotic option. MyISAM is meant to be used for log tables. Any serious data should be in InnoDB. EVERY site that accepts money should be in InnoDB. This is why many other databases (PostgreSql, Oracle) all default to table types just like InnoDB.

The real reason why your host does not want to enable InnoDB is because then your DB would not count against your disk usage, so it makes it very hard for him to charge you more if you have a large DB.

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

I think that depends on your work environment. As a designer, among CMS, e-commerce platforms and other PHP applications, I never had to choose anything beyond MyISAM and I thought that was a standard.

My host has already activated InnoDB so I am not complaning for my situation as it is solved I just say, and I repeat, this is another barrier for magento’s healthy spread. But we’ll get over it, I am sure of that.

 
Magento Community Magento Community
Magento Community
Magento Community
 
conticreative
Member
 
Total Posts:  38
Joined:  2007-12-13
 

I have to agree with the last poster. I have just been contacted by a client currently hosted on Hostgator whom is experiencing some issues with his cart. He wants to move over to Bluehost or move over to my dedicated server. However, after reading these posts, I am not so sure that installing innoDB on my dedicated server is such a good idea as I have plenty of Web Sites hosted there that could not suffer a slowdown in performance.

In addition, I am not so sure that my client’s issues are related to the lack of innoDB on the server. I am still investigating that issue but I am starting to think that’s not the problem.

In any event, I think it is short sighted of Magento to require innoDB considering that support among the run of the mill shared hosting providers is at best sketchy.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Forward Gear
Jr. Member
 
Total Posts:  22
Joined:  2009-01-17
 

As someone who is researching Magento and compatible hosts, I derived the most value from m00dawg’s post. My overview interpretation is that Magento’s designers prioritized smart architecture over speeding up Magento adoption. Kudos then to the Magento team.

Chris

 
Magento Community Magento Community
Magento Community
Magento Community
 
Twirrim
Jr. Member
 
Total Posts:  3
Joined:  2009-08-02
 

I know this is a bit of a bump, but I fancy adding my own two cents.

InnoDB comes enabled as standard with MySQL.  For it not to be there involves the host specifically disabling it (skip-innodb in the my.cnf file)

There is no major skills adjustment necessary for coping with InnoDB over MyISAM unless you’re getting to the level of doing nitty gritty performance tweaks. For the most part InnoDB will perform well out-of-the-box.

InnoDB has huge advantages over myisam, and you’ll find pretty much most major databases and software will use it.  Refusal to support it would indicate a hosting company you maybe ought to be thinking twice about.

Some of the advantages of innodb:

Transactional - every transaction is logged.  In the event of a crash the chances of having corrupted data in the database is much, much lower.  The database runs over the transaction log contents to try to be sure everything got in safely and correctly.  InnoDB is nigh on bullet proof, and MyISAM is far, far from it.  It really doesn’t take all that much to corrupt a MyISAM database.  Shockingly little in fact.
Personally even with low load stuff I wan the peace of mind InnoDB provides in regards to data safety.

Row Level Locking - When MySQL writes to a table, be it updating, adding or deleting rows, or whatever, it has to lock things first to ensure nothing else can write data. Given MySQL will execute queries in parallel that’s critical.  MyISAM only offers table level locking.  So if you’ve got two queries that come in that update completely different rows the second one has to wait for the first one to finish before it can do it’s work.  InnoDB supports row level locking.  In that scenario both queries could be executed simultaneously because only the row was locked for editing rather than the entire table.
Under any kind of transaction load row level locking will provide a significant performance and data consistency boost.

Backups.. well if you’re stingy enough to fret about the cost of innodb over myisam, I’m going to guess you’re “once a day” backing up types.  In which case try adding this to a cron job about an hour before your file backup is due to take place:
mysqldump --opt -u <username> -p <password> magento-db-name > magento.sql
That’ll leave you a nice flat file you can re-import into MySQL easily:
mysql -u <username> -p <password> < magento.sql

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