| Previous · Next | |
| User | Message |
|
JT
|
Date: 9/28/2009 7:21 pm · Subject: WG8 New Database · Rating: 0
One of the tasks on the WebGUI 8 todo list is to investigate new databases for WebGUI 8. There are many reasons for this, but here are the top few:a) MyISAM is prone to corruption under high loads.b) MyISAM has no support for foreign keys, which makes certain types of joins way slower than they should be.c) The fate of Oracle/Sun MySQL is uncertain until the Oracle / Sun merger is complete and the resulting shake out has occurred. Here are the systems I'm considering and what I've learned so far. I plan to update this thread as I continue my evaluation, but I'd love to hear about your experiences with any of the databases listed here, or some other databases you think I should consider*. * Please note, for the purposes of this research I don't plan to spend a lot of time on databases other than those of MySQL lineage. The main reason is that moving to an entirely new database engine means a *much* larger development effort than time is slotted for. That said, if there are ridiculously compelling reasons to look at another database, I will have a look. Drizzle - Drizzle (http://drizzle.org/wiki/Main_Page) is a really sweet little database that is forked from MySQL and is designed to be basically the perfect database for WebGUI. I had hoped that by now Drizzle would be releasing binaries and therefore be on a path toward creating a stable release. Unfortunately it is still alpha with no stable in site, so I have to rule it out for WebGUI 8. MariaDB - MariaDB (http://askmonty.org/wiki/index.php/MariaDB) is a fork of MySQL created by Monty, the guy that created MySQL to begin with. It's goal is to create a new storage engine that is the hybrid of MyISAM and InnoDB, with all their strengths and none of their weaknesses. Unfortunately, like Drizzle, MariaDB is quite a ways away from a stable release at this point. However, like Drizzle, will definitely be a contender for WebGUI 9. OurDelta - OurDelta (http://ourdelta.org/) is basically Oracle/Sun MySQL 5.0 with a bunch of patches for performance and stability. It's made by Open Query (http://openquery.com/), which is a firm that specializes in MySQL support and training. OurDelta is here, it's fast, stable, and supported. This makes it a very strong contender. Percona MySQL- (http://www.percona.com/docs/wiki/release:start) is nearly the same thing as OurDelta released by the guys from the MySQL Performance Blog (Percona). Percona (http://www.percona.com/) is a firm that provides support and custom development for MySQL. Percona MySQL is here, it's fast, stable, and supported. That makes it a very strong contender. Oracle MySQL w/InnoDB - Using this would mean using our existing version of MySQL and just switching to InnoDB tables instead of MyISAM. It's up-side is that it's the easiest choice. It's down side is #3 above. Oracle/Sun instability not-withstanding, this is also a very strong competitor with Percona and OurDelta. Oracle MySQL w/Falcon - Falcon is supposed to be a next generation storage engine for MySQL based upon the Netfrastrucutre database. It's goal is supposed to be that it will have all or most of the features of MyISAM and InnoDB, and most importantly none of the maintenance headaches of either. At this point I've ruled out Falcon for two reasons. The first and most important is that Falcon is not out yet, and as of right now there doesn't seem to be a plan to get it out. The announcements all say that it will come with MySQL 6.0, however, Oracle/Sun has removed the MySQL 6.0 page from the MySQL site (http://dev.mysql.com/downloads/mysql/6.0.html). In addition, the benchmarks indicate that Falcon needs some work before it will perform on par with InnoDB or even MyISAM (http://www.mysqlperformanceblog.com/2007/10/12/myisam-scalability-and-innodb-falcon-benchmarks/). Postgres - Postgres is the only non-MySQL database on this list. I'm not giving it any serious consideration at this point. I've used Postgres in the past, and it's definitely fast and stable, but it's also a total pain in the ass to administer compared to MySQL. It would also mean some serious retooling of WebGUI. However, they do have a nifty new full text search engine built in, so that gives them a nice plus. As of now the 3 main competitors are OurDelta, Percona, and Oracle MySQL with InnoDB. I plan on downloading and testing all 3 to see what advantages and disadvantages I can see from each. |
| Back to Top |
Rate [ | ]
|
|
patspam
|
Date: 9/28/2009 11:31 pm · Subject: Re: WG8 New Database · Rating: 0
Go man go!Rightly or wrongly I've always considered MySQL to be the PHP of the DBMS world - crippled but immensely popular due to its ease of deployment. But then I always seem to end up working on MySQL projects so I have absolutely zero experience in any other DBMS from which to compare. And MySQL has certainly served us well up until now so I shouldn't knock it without good reason. Can the sys admin gurus among us can offer their thoughts/experience as to what DBMS will best suit our goal of becoming a carrier-grade system? Which system has the best support for clustering, replication, redundancy, performance scalability etc..? Look forward to hearing what the research reveals JT. Patrick On Tue, Sep 29, 2009 at 10:21 AM, <jt@plainblack.com> wrote: JT wrote: |
| Back to Top |
Rate [ | ]
|
|
JT
|
Date: 9/28/2009 11:56 pm · Subject: Re: WG8 New Database · Rating: 0
> Can the sys admin gurus among us can offer their thoughts/experience > as to what DBMS will best suit our goal of becoming a carrier-grade > system? Which system has the best support for clustering, > replication, redundancy, performance scalability etc..? You are welcome and encouraged to provide any opinions or information you have that can help this process. Just don't get mad at me if you say "I think Oracle DBMS is the only way to go" and I don't take your advice. We can re-evaluate what database platform to operate on again at the WebGUI 9 timeframe (with the Perl 6 rewrite), but unless there is some amazingly compelling reason to consider non-MySQL databases for WebGUI 8, it's not practical to put something else at the foundation. The amount of time we have in our budget for WebGUI 8 means dropping a bunch of other features in order to switch to Postgres, Firebird, etc. That said, if there is experience out there on things we should be looking for, caveats we should try to avoid, etc, then I want to hear it. |
| Back to Top |
Rate [ | ]
|
|
JT
|
Date: 9/29/2009 12:01 am · Subject: Re: WG8 New Database · Rating: 0
It's a bad first start. I downloaded each of the three, and the only one I could get to immediately compile was Oracle/Sun MySQL. The other two complained of libtool errors. That's one strike against Percona and OurDelta. In addition, the OurDelta distro contained symbolic links off to code on the original developer's system. |
| Back to Top |
Rate [ | ]
|
|
knowmad
|
Date: 9/29/2009 7:15 am · Subject: Re: WG8 New Database · Rating: 0
JT, Thanks for sharing your research plans and notes with us. It's been years since I did a database eval so this should be interesting to watch. At the time (around 2001), I chose PostgreSQL and tend to agree with Patrick that I've regarded MySQL as pretty but shallow. After coming up the learning curve of administering PostgreSQL, I've come to enjoy it. At this point, many of the deficiencies of MySQL (stored procedures, foreign keys) have been overcome. Given the criteria, I think sticking with the Oracle/Sun MySQL makes the most sense for a stable environment. For the future, I think PostgreSQL offers a compelling product with a strong community and dedication to Open Source. BTW, we support a client who runs his business on a Mac OS X server with a PostgreSQL backend. It works beatifully.
William ---- |
| Back to Top |
Rate [ | ]
|
|
cap10morgan
|
Date: 9/29/2009 7:45 am · Subject: Re: WG8 New Database · Rating: 0
On Mon, Sep 28, 2009 at 6:21 PM, wrote:> JT wrote: > > Oracle MySQL w/InnoDB - Using this would mean using our existing version of > MySQL and just switching to InnoDB tables instead of MyISAM. It's up-side is > that it's the easiest choice. It's down side is #3 above. Oracle/Sun > instability not-withstanding, this is also a very strong competitor with > Percona and OurDelta. Have you considered comparing Oracle/Sun MySQL 5.0 w/ 5.1 and 5.4? I know we ran into a nasty bug in the WRE release w/ 5.1, but presumably that may be fixed before 8 goes stable. And/or maybe it doesn't exist in 5.4? Just curious, I have no compelling reasons to suggest their inclusion in the test other than that 5.4 has many of the InnoDB performance patches included in the OurDelta and Percona releases (or similar patches, I'm not very familiar with the exact details of how they correlate). And from the anecdote department: I had these guys: http://dbshards.com/ do an eval of our WebGUI in the cloud setup (our first live site running in Rightscale/EC2: http://www.environmentnewmexico.org/) to see if they could help us setup a sharded database for it. The goal was to have the ability to horizontally scale the database layer as traffic dictated. After the review, their only area of concern was the MyISAM requirement. Their technology relies on InnoDB's transactional integrity. All that to say, with InnoDB, there are (expensive, proprietary) options out there if you need a database that can scale like crazy. And really, you're going to be spending a lot of money any way you slice it if that's your requirement. So InnoDB is a nice, free, open source foundation that gets even small WebGUI sites up and running and has some nice advantages over MyISAM (though the big disadvantage of losing the FULLTEXT index), but it also opens up more possibilities for scaling up down the road too. +1 from me. > > http://www.webgui.org/develop/forum/wg8-new-database > > > -- > > WebGUI > http://www.webgui.org > > -- "Small acts of humanity amid the chaos of inhumanity provide hope. But small acts are insufficient." - Paul Rusesabagina, Rwandan and former hotel manager whose actions inspired the movie Hotel Rwanda |
| Back to Top |
Rate [ | ]
|
|
JT
|
Date: 9/29/2009 9:30 am · Subject: Re: WG8 New Database · Rating: 0
> Have you considered comparing Oracle/Sun MySQL 5.0 w/ 5.1 and 5.4? I> know we ran into a nasty bug in the WRE release w/ 5.1, but presumably > that may be fixed before 8 goes stable. And/or maybe it doesn't exist > in 5.4? Just curious, I have no compelling reasons to suggest their > inclusion in the test other than that 5.4 has many of the InnoDB > performance patches included in the OurDelta and Percona releases (or > similar patches, I'm not very familiar with the exact details of how > they correlate). Actually what I've realized is that to do any useful benchmarking regarding WebGUI the first thing I need to do is write a conversion script to convert all of WebGUI's database tables to InnoDB and add the appropriate foreign keys. After I do that we can make a determination about which database looks like the best choice for WebGUI 8. To answer your question directly though, I have already downloaded 5.1 and 5.4 last night. > And from the anecdote department: I had these guys: > http://dbshards.com/ do an eval of our WebGUI in the cloud setup (our > first live site running in Rightscale/EC2: > http://www.environmentnewmexico.org/) to see if they could help us > setup a sharded database for it. The goal was to have the ability to > horizontally scale the database layer as traffic dictated. After the > review, their only area of concern was the MyISAM requirement. Their > technology relies on InnoDB's transactional integrity. All that to > say, with InnoDB, there are (expensive, proprietary) options out there > if you need a database that can scale like crazy. And really, you're > going to be spending a lot of money any way you slice it if that's > your requirement. So InnoDB is a nice, free, open source foundation > that gets even small WebGUI sites up and running and has some nice > advantages over MyISAM (though the big disadvantage of losing the > FULLTEXT index), but it also opens up more possibilities for scaling > up down the road too. +1 from me. I'm well aware of the additional scaling you can get out of InnoDB over MyISAM. That's the reason I put this todo on the list. It wouldn't do us much good to invest all this time in scalability and then leave the database as a bottleneck. =) It is interesting to hear (if I'm understanding you right), that there are proprietary versions of InnoDB with additional scaling options beyond the custom features that you can get from companies like Percona and Open Query. Are these options designed specifically for cloud computing, or could they be used in any data center? |
| Back to Top |
Rate [ | ]
|
|
cap10morgan
|
Date: 9/29/2009 9:45 am · Subject: Re: WG8 New Database · Rating: 0
On Tue, Sep 29, 2009 at 8:30 AM, wrote:> JT wrote: > >> Have you considered comparing Oracle/Sun MySQL 5.0 w/ 5.1 and 5.4? I >> know we ran into a nasty bug in the WRE release w/ 5.1, but presumably >> that may be fixed before 8 goes stable. And/or maybe it doesn't exist >> in 5.4? Just curious, I have no compelling reasons to suggest their >> inclusion in the test other than that 5.4 has many of the InnoDB >> performance patches included in the OurDelta and Percona releases (or >> similar patches, I'm not very familiar with the exact details of how >> they correlate). > > Actually what I've realized is that to do any useful benchmarking > regarding WebGUI the first thing I need to do is write a conversion > script to convert all of WebGUI's database tables to InnoDB and add > the appropriate foreign keys. > > After I do that we can make a determination about which database looks > like the best choice for WebGUI 8. To answer your question directly > though, I have already downloaded 5.1 and 5.4 last night. OK, cool. Sounds like you've got a good methodology for testing. > > >> And from the anecdote department: I had these guys: >> http://dbshards.com/ do an eval of our WebGUI in the cloud setup (our >> first live site running in Rightscale/EC2: >> http://www.environmentnewmexico.org/) to see if they could help us >> setup a sharded database for it. The goal was to have the ability to >> horizontally scale the database layer as traffic dictated. After the >> review, their only area of concern was the MyISAM requirement. Their >> technology relies on InnoDB's transactional integrity. All that to >> say, with InnoDB, there are (expensive, proprietary) options out there >> if you need a database that can scale like crazy. And really, you're >> going to be spending a lot of money any way you slice it if that's >> your requirement. So InnoDB is a nice, free, open source foundation >> that gets even small WebGUI sites up and running and has some nice >> advantages over MyISAM (though the big disadvantage of losing the >> FULLTEXT index), but it also opens up more possibilities for scaling >> up down the road too. +1 from me. > > I'm well aware of the additional scaling you can get out of InnoDB > over MyISAM. That's the reason I put this todo on the list. It > wouldn't do us much good to invest all this time in scalability and > then leave the database as a bottleneck. =) > > It is interesting to hear (if I'm understanding you right), that there > are proprietary versions of InnoDB with additional scaling options > beyond the custom features that you can get from companies like > Percona and Open Query. Are these options designed specifically for > cloud computing, or could they be used in any data center? For dbShards in particular, they insert their tech at the client library level. They're interface compatible with the official MySQL C client library (so you can link DBD::MySQL against it, for example), but they don't require modifying MySQL itself nor the clients that access it. They add in more robust failover and sharding capabilities by having that custom client library make decisions about which server to send queries to. So then the sharding strategy and the failover detection is all in the custom client library, and its all transparent to the app (WebGUI in this case). They also have some proprietary replication technology to keep everything sync'd up among your MySQL servers in a more reliable way than standard MySQL replication. So that piece probably does require a modified MySQL server. It's the first general purpose replication and sharding technology I've seen for MySQL that Just Works (or so they claim, caveat emptor). And yes, I believe it would work in any data center, not just for cloud computing. > > > http://www.webgui.org/develop/forum/wg8-new-database/6 > > > -- > > WebGUI > http://www.webgui.org > > -- "Small acts of humanity amid the chaos of inhumanity provide hope. But small acts are insufficient." - Paul Rusesabagina, Rwandan and former hotel manager whose actions inspired the movie Hotel Rwanda |
| Back to Top |
Rate [ | ]
|
|
JT
|
Date: 9/29/2009 10:20 am · Subject: Re: WG8 New Database · Rating: 0
> For dbShards in particular, they insert their tech at the client> library level. They're interface compatible with the official MySQL C > client library (so you can link DBD::MySQL against it, for example), > but they don't require modifying MySQL itself nor the clients that > access it. They add in more robust failover and sharding capabilities > by having that custom client library make decisions about which server > to send queries to. So then the sharding strategy and the failover > detection is all in the custom client library, and its all transparent > to the app (WebGUI in this case). > > They also have some proprietary replication technology to keep > everything sync'd up among your MySQL servers in a more reliable way > than standard MySQL replication. So that piece probably does require a > modified MySQL server. > > It's the first general purpose replication and sharding technology > I've seen for MySQL that Just Works (or so they claim, caveat emptor). > > And yes, I believe it would work in any data center, not just for > cloud computing. Very interesting. After we get WebGUI 8 converted over to InnoDB I might have to call them up for a technology demonstration. |
| Back to Top |
Rate [ | ]
|
|
JT
|
Date: 9/29/2009 5:04 pm · Subject: Re: WG8 New Database · Rating: 0
Well converting WebGUI's database from MyISAM to InnoDB has already been an interesting experience. I found four tables we don't even use anymore, several inaccurate table definitions, and a couple other bugs. For those of you interested with experimentation, the conversion script can be found here: http://github.com/plainblack/webgui/blob/innodb/docs/upgrades/convert-db-to-innodb.sql Note that the WebGUI code won't work with this yet, it's just tables. In addition, I haven't yet put in all the relationships between all the tables, just a basic start on it. I've attached a sample ERD (very ugly), and a dump of the database structure for those who are interested. Attached Files |
| Back to Top |
Rate [ | ]
|
