Hi Andreas, So, lets have a look. I assume the InnoDB table causing most of performance problems is oxarticles. As I said the only specific InnoDB feature used in eShop is a transaction in oxOrder::recalculateOrder().
The purpose of this transaction is assuring consistent data in oxorder and oxorderarticle tables. However oxarticles table is also changed during this transaction leaving the possibility to fully rollback all its changes on order fault. Particularly product stock count is reduced during order finalization here. The worst think what could happen after converting table to MyISAM is eShop ending up with not reduced stock information after some order processing exception (and this should never happen in normal case). SO while I can not give you an "official" advice to convert the tables, maybe theoretical risk of having inconsistent stock information is acceptable for your case? Somebody, please correct me if I am wrong. Or do you think this issue is too massive to be treated individually? Regards Tomas Liubinas > -----Original Message----- > From: [email protected] [mailto:dev-general- > [email protected]] On Behalf Of anzido GmbH > Sent: Tuesday, May 11, 2010 9:22 PM > To: [email protected] > Subject: Re: [oxid-dev-general] storage enginges [T-W1V47SNBVS-96] > > Hi Tomas, > > I understand why you did it - and on servers well optimized for InnoDB > this certainly will help. But unfortunately in the real world you do not > have these servers. On Enterprise shops you may have the possibility to > have the server configured as you want it - but in the PE world this in > most cases is NOT possible. And we DO have serious problems here ... > > > > Beste Grüße aus Dortmund! > Andreas Ziethen | Geschäftsführung > > -- > > anzido GmbH > Kirchhörder Str. 12 > 44229 Dortmund > Tel.: 0231 - 60 71 079 > Fax.: 0231 - 60 71 081 > Mobil:0176 - 8325 1488 > Email: [email protected] > Web: http://www.anzido.com ( http://www.anzido.com/ ) > > USt-ID: DE257982972 > Geschäftsführung: Andreas Ziethen > Amtsgericht Dortmund HRB 20883 > -----Ursprüngliche Daten----- > Datum: 11.05.2010 17:58:15 > Von: Tomas Liubinas <[email protected]> > An: <[email protected]> > Betreff: Re: [oxid-dev-general] storage enginges > Vorgang: T-W1V47SNBVS-96 > > > Hi everyone, > > > > The main advantage of InnoDB is engine specific row-locking feature, as > opposed to table-locking way in MyISAM. Meaning while one query is busy > inserting or updating a row, another query can update a different row at > the same time in InnoDB. > > > > There were some cases when due to too many update requests MyISAM tables > were locked fully under heavy load. Switching to InnoDB helped to increase > multi-user concurency and performance. So now we follow a simple rule - > all writable tables are InnoDB. > > > > Additional factor - InnoDB tables support DB transactions and eShop uses > transactions in order saving process (oxorder::recalculateOrder()). So > while theoretically possible to switch back to MyISAM for some tables I > would not recommend doing that. > > > > It might be helpful I found some quick info on InnoDB configuration: > > http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance- > optimization-basics/ > > > > Regards > > Tomas Liubinas > > > > > -----Original Message----- > > > From: [email protected] [mailto:dev-general- > > > [email protected]] On Behalf Of Kliebenstein Sven > > > Sent: Tuesday, May 11, 2010 5:42 PM > > > To: [email protected] > > > Subject: Re: [oxid-dev-general] storage enginges [T-W1V47SNBVS-96] > > > > > > Hi Andreas, > > > > > > We're currently hosting the shops on our own, so we're not facing > these > > > problems, but I suppose you're right when it comes to external hosting > > > partners. > > > The answers to your questions would also be interesting for us...so > OXID, > > > what do you say? > > > > > > > > > Cheers, Sven > > > > > > > > > -----Ursprüngliche Nachricht----- > > > Von: [email protected] [mailto:dev-general- > > > [email protected]] Im Auftrag von anzido GmbH > > > Gesendet: Dienstag, 11. Mai 2010 16:01 > > > An: [email protected] > > > Betreff: Re: [oxid-dev-general] storage enginges [T-W1V47SNBVS-96] > > > > > > Hi Sven, > > > > > > yes - that's exactly what we found out, too. But with customers on > managed > > > servers it's not always that easy to have those settings changed ... - > so > > > before I start blaiming the hosters I'd rather like to know OXID's > reasons > > > for switching storage engines before. Cause I suspect that the gain of > > > performance might be that small that a simple back-switch to MyISAM in > > > most cases might be the easier and better way. > > > > > > And for that reason I'd also like to know if there ar any other > reasons > > > apart from performance which make InnoDB eg. for oxarticles necessary. > > > > > > > > > Beste Grüße aus Dortmund! > > > Andreas Ziethen | Geschäftsführung > > > > > > -- > > > > > > anzido GmbH > > > Kirchhörder Str. 12 > > > 44229 Dortmund > > > Tel.: 0231 - 60 71 079 > > > Fax.: 0231 - 60 71 081 > > > Mobil:0176 - 8325 1488 > > > Email: [email protected] > > > Web: http://www.anzido.com ( http://www.anzido.com/ ) > > > > > > USt-ID: DE257982972 > > > Geschäftsführung: Andreas Ziethen > > > Amtsgericht Dortmund HRB 20883 > > > -----Ursprüngliche Daten----- > > > Datum: 11.05.2010 15:47:31 > > > Von: Kliebenstein Sven <[email protected]> > > > An: [email protected] <[email protected]> > > > Betreff: Re: [oxid-dev-general] storage enginges > > > Vorgang: T-W1V47SNBVS-96 > > > > > > > Hi, > > > > > > > > We have faced the same problem as you mentioned when using InnoDB > > > > tables in OXID. If you're using a Not-InnoDB-Optimized-MySQL-Server, > the > > > default settings are definitely not usable. The usage of InnoDB tables > in > > > an non-optimized MySQL-Server makes things more worse than using > standard > > > MyISAM tables. For us, we're always optimizing the settings of your > > > servers before installing the shop. You can get a list of all > available > > > InnoDB settings from MySQL by executing this query: show variables > like > > > 'innodb%'; The documentation for these options is available from the > mysql > > > website: > > > > http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html > > > > http://dev.mysql.com/doc/refman/5.0/en/innodb-buffer-pool.html > > > > > > > > There are also some tuning tools available, just have a look for: > > > > - tuning-primer > > > > - mysqltuner (http://mysqltuner.pl/mysqltuner.pl) > > > > > > > > > > > > Just my 2 cents... > > > > Sven > > > > > > > > > > > > > > > > > > > > -----Ursprüngliche Nachricht----- > > > > Von: [email protected] > > > > [mailto:[email protected]] Im Auftrag von > anzido > > > > GmbH > > > > Gesendet: Dienstag, 11. Mai 2010 12:57 > > > > An: [email protected] > > > > Betreff: [oxid-dev-general] storage enginges [T-W1V47SNBVS-96] > > > > > > > > Hi, > > > > > > > > since a while I notice that OXID does change storage engines for > more > > > and more tables from MyISAM to InnoDB. > > > > I'd like to get some explanation why this is done, presumably due to > > > performance issues, I guess ... but some more detailed information > would > > > be nice. > > > > > > > > I'd also like to know if and which tables really HAVE to be InnoDB - > e. > > > g. for transaction reasons or whatever. > > > > > > > > The background for my question is that we discover more and more > > > performance problems with those InnoDB tables cause many hosters do > use > > > MyISAM optimized MySQL-Settings which often lead to big problems on > large > > > InnoDB tables. ProfiHost for example is one of those. > > > > > > > > Beste Grüße aus Dortmund! > > > > Andreas Ziethen | Geschäftsführung > > > > > > > > -- > > > > anzido GmbH > > > > Kirchhörder Str. 12 > > > > 44229 Dortmund > > > > Tel.: 0231 - 60 71 079 > > > > Fax.: 0231 - 60 71 081 > > > > Mobil:0176 - 8325 1488 > > > > Email: [email protected] > > > > Web: http://www.anzido.com ( http://www.anzido.com/ ) > > > > > > > > USt-ID: DE257982972 > > > > Geschäftsführung: Andreas Ziethen > > > > Amtsgericht Dortmund HRB 20883 > > > > _______________________________________________ > > > > dev-general mailing list > > > > [email protected] > > > > http://dir.gmane.org/gmane.comp.php.oxid.general > > > > _______________________________________________ > > > > dev-general mailing list > > > > [email protected] > > > > http://dir.gmane.org/gmane.comp.php.oxid.general > > > > > > _______________________________________________ > > > dev-general mailing list > > > [email protected] > > > http://dir.gmane.org/gmane.comp.php.oxid.general > > > _______________________________________________ > > > dev-general mailing list > > > [email protected] > > > http://dir.gmane.org/gmane.comp.php.oxid.general > > _______________________________________________ > > dev-general mailing list > > [email protected] > > http://dir.gmane.org/gmane.comp.php.oxid.general > > _______________________________________________ > dev-general mailing list > [email protected] > http://dir.gmane.org/gmane.comp.php.oxid.general _______________________________________________ dev-general mailing list [email protected] http://dir.gmane.org/gmane.comp.php.oxid.general
