Thread: Thoughts on MySQL and InnoDB
Hi all; I case people on this list have not seen the slashdot discussion (http://developers.slashdot.org/article.pl?sid=05/11/23/0228258&tid=221&tid=218), it appears that MySQL has announced a commitment to provide a replacement to InnoDB to their customers. I wanted to just fill in a few gaps that came up during my research. First BDB is not a viable replacement for InnoDB for two reasons both of which stem from BDB architectural considerations (it simply wasn't designed to function well as a backend for a high concurrency RDBMS). Basically, while InnoDB uses MVCC, BDB uses page locks. BDB therefore has locking issues because you don't have the snapshot capabilities that MVCC gets you with InnoDB, and it is unlikely that one will ever be able to provide multiple transaction levels with the BDB storage engine. MaxDB/SAPDB might be a possibility but I don't know anything about how it handles things behind the scenes. My uninformed guess is that it would be no better than BDB, possibly worse. So my conclusion is that MySQL is biting off quite a bit with this commitment. One really does not know how long it would take them to create such a table architecture froms scratch or indeed whether they are able to if they try. Best Wishes, Chris Travers Metatron Technology Consulting
> First BDB is not a viable replacement for InnoDB for two reasons both > of which stem from BDB architectural considerations (it simply wasn't > designed to function well as a backend for a high concurrency RDBMS). > Basically, while InnoDB uses MVCC, BDB uses page locks. BDB therefore > has locking issues because you don't have the snapshot capabilities that > MVCC gets you with InnoDB, and it is unlikely that one will ever be able > to provide multiple transaction levels with the BDB storage engine. I don't agree. While Berkeley DB was not designed as an RDBMS backend, it's not that far from where BDB is now to being a RDBMS backend: the significant missing pieces might be MVCC, foreign key support and moving from page-level to row-level locking. Berkeley DB has had multiple transaction levels for a long time. I don't believe MVCC is that hard. I think foreign key support is more cleanly done above the backend engine -- MySQL used InnoDB's support for foreign keys because it was there, not because it's the right place to do it. RLL... well, there you have me, that's not an afternoon hack. :-) But that's the big missing chunk, if we could solve that problem, I think Berkeley DB could be a fine RDBMS backend. Regards, --keith =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Keith Bostic bostic@sleepycat.com Sleepycat Software Inc. keithbosticim (Yahoo IM) 118 Tower Rd. +1-781-259-3139 Lincoln, MA 01773 http://www.sleepycat.com
On Wednesday 23 November 2005 14:50, Chris Travers wrote: > Hi all; > > I case people on this list have not seen the slashdot discussion > (http://developers.slashdot.org/article.pl?sid=05/11/23/0228258&tid=221&tid >=218), it appears that MySQL has announced a commitment to provide a > replacement to InnoDB to their customers. I wanted to just fill in a > few gaps that came up during my research. <snip> > > So my conclusion is that MySQL is biting off quite a bit with this > commitment. One really does not know how long it would take them to > create such a table architecture froms scratch or indeed whether they > are able to if they try. > If only there was another open source database that used mvcc that was bsd-licensed, then they could use that as a starting point and have the control over it that they want. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
bostic@sleepycat.com writes:
>> First BDB is not a viable replacement for InnoDB for two reasons
>> both of which stem from BDB architectural considerations (it simply
>> wasn't designed to function well as a backend for a high
>> concurrency RDBMS). Basically, while InnoDB uses MVCC, BDB uses
>> page locks. BDB therefore has locking issues because you don't
>> have the snapshot capabilities that MVCC gets you with InnoDB, and
>> it is unlikely that one will ever be able to provide multiple
>> transaction levels with the BDB storage engine.
>
> I don't agree.
>
> While Berkeley DB was not designed as an RDBMS backend, it's not
> that far from where BDB is now to being a RDBMS backend: the
> significant missing pieces might be MVCC, foreign key support and
> moving from page-level to row-level locking.
Well, do you think those are likely to appear in version 4.5? :-)
They may not be "fiendishly difficult," but in order for a system to
even start to depend on those things, they have to be present in the
"backend engine" in the first place.
Based on what's there now, which is what people would have to
initially depend on, MySQL(tm)-atop-BDB would initially suffer from
there being a great deal more locking than any of the other 'modern'
database engines require these days.
> Berkeley DB has had multiple transaction levels for a long time.
>
> I don't believe MVCC is that hard. I think foreign key support is
> more cleanly done above the backend engine -- MySQL used InnoDB's
> support for foreign keys because it was there, not because it's the
> right place to do it.
PostgreSQL doesn't really handle FK support inside the storage engine;
it does so via triggers, which lie outside the strict scope of
"storage engine."
> RLL... well, there you have me, that's not an afternoon hack.
> :-) But that's the big missing chunk, if we could solve that
> problem, I think Berkeley DB could be a fine RDBMS backend.
The present absence of MVCC (hard or not, it's not there) would make
the result quite a lot less satisfactory than people would like.
That's not a flame against BDB; it represents something that has been
(and still is) out of the scope of what your folks have tried to
implement. For the common "use cases" for BDB, I don't imagine the
absence of MVCC has caused anyone a need to pause up until now.
I daresay that adding MVCC wouldn't necessarily make BDB forcibly more
attractive for MySQL(tm) users. In effect, the really _big_ problem
with InnoDB(tm) has turned out to be that it was owned by someone
other than MySQL AB, which made it a technology vulnerable to being
bought out from under MySQL AB.
Replacing InnoDB(tm), an engine "owned" by someone other than MySQL
AB, with Berkeley DB, another engine also "owned" by someone other
than MySQL AB, leaves MySQL AB potentially vulnerable to the same
sorts of problems.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/linuxdistributions.html
"If you give someone Fortran, he has Fortran.
If you give someone Lisp, he has any language he pleases."
-- Guy L. Steele Jr.
Chris, > Replacing InnoDB(tm), an engine "owned" by someone other than MySQL > AB, with Berkeley DB, another engine also "owned" by someone other > than MySQL AB, leaves MySQL AB potentially vulnerable to the same > sorts of problems. Yes, except that SleepyCat would be a *lot* more expensive than InnoBase. In fact, I'd guess that Oracle could buy MySQL AB cheaper than they could get SleepyCat. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On 11/26/2005 12:32 PM, bostic@sleepycat.com wrote: >> First BDB is not a viable replacement for InnoDB for two reasons both >> of which stem from BDB architectural considerations (it simply wasn't >> designed to function well as a backend for a high concurrency RDBMS). >> Basically, while InnoDB uses MVCC, BDB uses page locks. BDB therefore >> has locking issues because you don't have the snapshot capabilities that >> MVCC gets you with InnoDB, and it is unlikely that one will ever be able >> to provide multiple transaction levels with the BDB storage engine. > > I don't agree. > > While Berkeley DB was not designed as an RDBMS backend, it's not > that far from where BDB is now to being a RDBMS backend: the > significant missing pieces might be MVCC, foreign key support > and moving from page-level to row-level locking. > > Berkeley DB has had multiple transaction levels for a long time. > > I don't believe MVCC is that hard. I think foreign key support > is more cleanly done above the backend engine -- MySQL used > InnoDB's support for foreign keys because it was there, not > because it's the right place to do it. How hard MVCC is depends on where you start from. In the Postgres case, where we already had a non-overwriting storage manager that kept old row versions around. All that needed to be done was to figure out which of the versions is actually the visible one and teach vacuum to keep those that could still be seen by someone. I think that BDB is an overwriting storage engine (I could be wrong). In which case MVCC is quite a bit more hairy than what we needed. And you definitely need MVCC for any transaction isolation above read committed, because otherwise you will have shared read locks preventing updates and your performance in a concurrent environment just goes down the drain. I wholeheartedly agree that using a storage engine based foreign key solution like what InnoDB offered was wrong to begin with and should be reimplemented in the upper levels anyway. So losing that feature isn't actually what I consider bad. What they have now has neither DEFERRABLE nor ON DELETE SET DEFAULT. Not sure if they inteded to fix that in the next version. Especially while other areas of features still need a lot of attention. I was shocked to learn that functions and triggers cannot access any tables. So all a trigger can do is check/modify the values at hand. No table lookups, no audit functionality, nada. When I created PL/Tcl and PL/pgSQL it didn't even cross my mind as a possibility to release any procedural language without access to the DB ... and that was several years ago! All the above together plus rolling out a complete new release within the next 6-9 months sounds challenging, to say the least. And to be honest, cranking out a completely new storage engine from scratch in that timeframe is unrealistic. The really bad part here is that the decision what to do must be made right now, because otherwise, the time for their renewal talks with Oracle is up and they don't even have an alternative in sight. So they have to make a decision that will cost a lot of money and will pull away developers from other, important work. Plus Oracle has the same time that MySQL AB must spend on catching up with todays InnoDB - for maybe improving it significantly? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> Plus Oracle has the same time that MySQL AB must spend on catching
> up with todays InnoDB - for maybe improving it significantly?
Hmm. You're suggesting the thought that Oracle might make substantial
upgrades to InnoDB, which might lead to a concommitant increase in
price? Interesting...
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxfinances.info/info/slony.html
"Rules of Optimization:
Rule 1: Don't do it.
Rule 2 (for experts only): Don't do it yet."
-- M.A. Jackson
On Tue, Nov 29, 2005 at 11:55:56PM -0500, Christopher Browne wrote: > > Plus Oracle has the same time that MySQL AB must spend on catching > > up with todays InnoDB - for maybe improving it significantly? > > Hmm. You're suggesting the thought that Oracle might make > substantial upgrades to InnoDB, which might lead to a concommitant > increase in price? Interesting... Those upgrades also might lead to a concommitant *decrease* in price, as in giving it away for free. This would make it very difficult to come up with reasons to pay for the newfangled MySQL engine, especially should Oracle choose to spend some money on a marketing campaign for its improvements. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 415 235 3778 Remember to vote!
On 11/30/2005 1:18 AM, David Fetter wrote: > On Tue, Nov 29, 2005 at 11:55:56PM -0500, Christopher Browne wrote: >> > Plus Oracle has the same time that MySQL AB must spend on catching >> > up with todays InnoDB - for maybe improving it significantly? >> >> Hmm. You're suggesting the thought that Oracle might make >> substantial upgrades to InnoDB, which might lead to a concommitant >> increase in price? Interesting... > > Those upgrades also might lead to a concommitant *decrease* in price, > as in giving it away for free. This would make it very difficult to > come up with reasons to pay for the newfangled MySQL engine, > especially should Oracle choose to spend some money on a marketing > campaign for its improvements. I thought more in the direction of a significant *imbalance* in price vs. value. Let's assume for a few seconds that MySQL AB can manage to come up with an alternative storage engine, that the InnoDB relicense renewal fails and that Oracle does release a new InnoDB version that is way more powerfull than MySQL's alternative. Well, the last point isn't really necessary because I don't think MySQL can produce something as powerfull as the current InnoDB in the timeframe we're talking about. What happens here is that the *free* GPL version of MySQL will be more powerfull than the *commercial* licensed MySQL. How do you sell that? What Oracle really does in the above scenario is to force MySQL AB out of the software licensing business, which can scale high and produce lots of money, into a pure service business where more money equals more work. Oracle knows the power of license-dollars too well. The power of those dollars is what kept Oracle ahead so far. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On 11/29/2005 10:42 PM, Jan Wieck wrote: > [...] > of attention. I was shocked to learn that functions and triggers cannot > access any tables. So all a trigger can do is check/modify the values at > hand. This statement was based on an article found on the MySQL site. http://dev.mysql.com/tech-resources/articles/mysql-triggers.html Apparently the information is outdated. The functionality was added in 5.0.10. http://forums.mysql.com/read.php?99,33676,33676,quote=1 So I am not shocked about that any more, but more shocked that MySQL AB feels free to make significant changes in features from one patchlevel to another. One can't even rely on a list of supported features for 5.0. No wonder that there is a lot of confusion. The available set of features depends on the "release of the month". They intermix all sorts of backward incompatible feature changes with bugfixes. I consider this practice a classic example of sub standard software maintenance. It is hard to believe that anyone would put a database system into production, where every single bugfix release has to go through the full QA cycle like a major version upgrade. The full release history horror story can be found here: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > Apparently the information is outdated. The functionality was added in > 5.0.10. > > http://forums.mysql.com/read.php?99,33676,33676,quote=1 > > So I am not shocked about that any more, but more shocked that MySQL AB > feels free to make significant changes in features from one patchlevel > to another. Well, reading the changelog history apparently they call 5.0.15 from Oct.19th "Release", so all previous versions were Beta. I wonder how many MySQL 5.0 users are aware of that... Regards, Andreas
On 12/1/2005 12:13 PM, Andreas Pflug wrote: > Jan Wieck wrote: > >> Apparently the information is outdated. The functionality was added in >> 5.0.10. >> >> http://forums.mysql.com/read.php?99,33676,33676,quote=1 >> >> So I am not shocked about that any more, but more shocked that MySQL AB >> feels free to make significant changes in features from one patchlevel >> to another. > > Well, reading the changelog history apparently they call 5.0.15 from > Oct.19th "Release", so all previous versions were Beta. I wonder how > many MySQL 5.0 users are aware of that... Er ... hmmmm ... so that means they have happy feature mucking during BETA? This is unprofessional. BETA phase without feature freeze ... what kind of hobby coders did they hire? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > On 12/1/2005 12:13 PM, Andreas Pflug wrote: >> Jan Wieck wrote: >>> >>> So I am not shocked about that any more, but more shocked that MySQL >>> AB feels free to make significant changes in features from one >>> patchlevel to another. >> >> Well, reading the changelog history apparently they call 5.0.15 from >> Oct.19th "Release", so all previous versions were Beta. I wonder how >> many MySQL 5.0 users are aware of that... > > Er ... hmmmm ... so that means they have happy feature mucking during > BETA? This is unprofessional. BETA phase without feature freeze ... what > kind of hobby coders did they hire? I'd guess that it's less a feature of their technical team and more of the licencing model. The pressure to announce a new version tends to drive most companies. Even MS used to, although they seem to be doing the "semi-public beta status for two years" thing recently. -- Richard Huxton Archonet Ltd
On 12/1/05, Jan Wieck <JanWieck@yahoo.com> wrote: > > Well, reading the changelog history apparently they call 5.0.15 from > > Oct.19th "Release", so all previous versions were Beta. I wonder how > > many MySQL 5.0 users are aware of that... > > Er ... hmmmm ... so that means they have happy feature mucking during > BETA? This is unprofessional. BETA phase without feature freeze ... what > kind of hobby coders did they hire? The issue for me, personally, is that it can often be nearly impossible to know if 5.0.15 is actually a release code, or if it's beta. In addition, it's nearly impossible to know if 5.0.14 and 5.0.15 are "compatible". I'm still old-school, and believe that .z releases should *never* introduce new functionality. That's a .y release. For me, it's just a demonstration of sloppy practices in release management. Honestly, MySQL just has too damned many releases. Chris -- | Christopher Petrilli | petrilli@gmail.com
> Jan Wieck wrote: >> On 12/1/2005 12:13 PM, Andreas Pflug wrote: >>> Jan Wieck wrote: >>>> >>>> So I am not shocked about that any more, but more shocked that >>>> MySQL AB feels free to make significant changes in features from >>>> one patchlevel to another. >>> >>> Well, reading the changelog history apparently they call 5.0.15 >>> from Oct.19th "Release", so all previous versions were Beta. I >>> wonder how many MySQL 5.0 users are aware of that... >> Er ... hmmmm ... so that means they have happy feature mucking >> during BETA? This is unprofessional. BETA phase without feature >> freeze ... what kind of hobby coders did they hire? > > I'd guess that it's less a feature of their technical team and more of > the licencing model. The pressure to announce a new version tends to > drive most companies. Even MS used to, although they seem to be doing > the "semi-public beta status for two years" thing recently. This particular sloppiness isn't something I'd attribute to that. If I were to add Major New Functionality, I'd be keen on generating a new major version. The difference between 5.0.14 and 5.0.15 looks like it would warrant at least a bump to 5.1.something, if not to 6.something. You sort of expect that minor releases have pretty much equivalent functionality. Massive change should encourage publicizing it as more than just a third level version number change, particularly if the licensing/royalty model encouraged that... -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://linuxfinances.info/info/lisp.html Rules of the Evil Overlord #75. "I will instruct my Legions of Terror to attack the hero en masse, instead of standing around waiting while members break off and attack one or two at a time." <http://www.eviloverlord.com/>